BETWEEN Condition in SQL

Use the BETWEEN condition to display rows based on a range of values.

Example: (from emp table)

Note:

• You can display rows based on a range of values using BETWEEN range condition. The range that you display contains a lower limit and upper limit.
• Value specified with the BETWEEN condition are inclusive. 
• You must specify the lower limit first.

WHERE Clause

• Restrict the rows returned by using the WHERE Clause.

Syntax:

    SELECT *|{[DISTINCT] column|expression [alias],...}

   FROM table

   [WHERE condition(s)];

• The WHERE clause follows the FROM clause.

WHERE     Restricts the query to rows that meet a condition.

conditions   is composed of column names, expressions, constants and a comparison operator.

Using the WHERE Clause

In the example, the SELECT statement retrieves the employee name, name, job and department number of all employees whose department number is 10.

Another Example:

In the above example the SELECT statement retrieves data according to string conditions and string data is enclosed with single quotes.

NOTE:

• Character strings and data values are enclosed in single quotation marks.
• Character values are case sensitives and date values are Format Sensitives.
• The default date format is DD-MON-RR.

Unit 2 (RESTRICTING AND SORTING DATA )

TOPIC Objective:-

•  Limit the rows retrieved by a Query
•  Sort the rows retrieved by a Query

Through SELECT statement we can do a lot of selection and filtration to our search query, so we need to use some CLAUSES like (WHERE, ORDER BY), CONDITIONS like (BETWEEN, IN, LIKE, NULL) and some OPERATORS like (AND, OR, NOT).

So This Section covers following topics:

1) WHERE Clause
2) Comparison Operator
3) BETWEEN Condition
4) IN Condition
5) LIKE Condition
6) NULL Condition
7) Logical Conditions  ( AND, OR, NOT Operators)
8) AND Operator
9) OR Operator
10) NOT Operator
11) Operator Precedence
12) ORDER BY Clause
13) SORTING

TWO Tables is used for all operations (emp and dept)

EMP TABLE:

DEPT TABLE: 

DISTINCT - Eliminating Duplicate Rows

The default display of queries is all rows, including duplicate rows.

Eliminating Duplicate Rows

Eliminate duplicate rows by using the DISTINCT keyword in the SELECT clause.

Note:

You can specify multiple columns after the DISTINCT clause. The DISTINCT clause affects all the selected columns, and the result is every distinct combination of the columns.

EX: 

Without DISTINCT the above output:  (Notice the difference)

Literal Character String in SELECT statement

• A literal is a character, a number or a date included in the SELECT statement list.
• Date and character literal values must be enclosed within single quotation marks.
• Each character string is output once for each row returned.

Using Literal Character String:

The Example above displays the names and job codes of all employees. The column has the heading Employee Details. Notice the spaces between the single quote marks in the select statement. The spaces improves the readability of the output. 

Concatenation Operator in SELECT statement

A concatenation operator:

• Concatenates columns or character strings to other columns.
• is represented by two vertical bar ( || )
• Creates a resultant column that is character expression.

Using the Concatenation Operator:

In the above example ename and job are concatenated, and they are given alias Employees. Notice that the ename and job code are combined to make a single output column.

The AS keyword before the alias name makes the SELECT clause easier to read.

ALIAS - Column Aliases in SELECT statement

A column alias:

• Renames a column heading.
• Is useful with calculations.
• AS keyword is optional between the column name and alias.
• Requires double quotation marks if it contains spaces or special characters or is case sensitive

Using Column Aliases (with example)

Operator Precedence in SELECT statement

*      /     +     -

• Multiplication and division take priority over addition and subtraction
• Operators of the same priority are evaluated from left to right 
• Parentheses are used to force prioritized evaluation and to clarify statements

Operator Precedence with example

SELECT ename,sal,12*sal+100 

FROM emp;

SELECT ename,sal,12*(sal+100)

FROM emp;

In the above examples parentheses makes a lot of differences in arithmetic calculation.

Arithmetic Expressions in SELECT statement

You may need to modify the way in which data is displayed, perform calculations, etc.

   Operator       Description

     +                     Add
      -                   Subtract
      *                  Multiply
      /                   Divide

Using Arithmetic Operators 

If we need to show Annual salary of employes then;

Another example:

Selecting Specific Columns

In the above example we 've selected only 3 columns from the table.

Errors: 

If we want to select column that is not in that table then SQL returns an error. 

Example:

Basic Select Statement or Selecting ALL Columns

Syntax of SELECT Statement

SELECT *|{[DISTINCT] column|expression [alias],....}
FROM table;

> SELECT identifies what columns
> FROM identifies which table

Selecting All Columns

SELECT * 

FROM dept;

Result:

Unit 1 :- SELECT Staments (With Example)

A SELECT statement retrieves information from the database. Using a SELECT statement you can do the following things:

> Projection: You can choose as few or many columns of the table as you required. (Column filtration)
> Selection: You can use various criteria to restrict the rows that you see. (Row filtration)
> Joining: You can use the join capability in SQL to bring together data that is stored in different tables by creating a link between them.

In this section I am going to use two table (emp and dept) shown below.
Here are the topics that I'll cover in SELECT Statement Section.

1) Basic Select Statement or Selecting ALL Columns
2) Selecting Specific Columns
3) Use of Arithmetic Expressions in SELECT statement.
4) Operator Precedence in SELECT statement and using Parenthesis
5) Column Aliases in SELECT statement. (With Example)
6) Concatenation Operator in SELECT statement (With Example)
7) Literal Character String in SELECT statement (With Example)
8) DISTINCT - Eliminating Duplicate Rows (With Example)

In this Tutorial I've used TWO TABLES named: emp and dept.
Here are the Structure of Two Table:  ( CLICK on the image to see better)

Data in EMP and DEPT Table: ( CLICK on the image to see better)