SORTING in SQL

The default sort order is ascending:

• Numeric values are displayed with the lowest value first e.g. 1-10
• Date values are displayed with the earliest value first e.g. 01-JAN-12 before 12-FEB-13
• Character values are displayed in alphabetical order e.g. A first Z last
• Null values are displayed last for ascending sequence and first for descending sequence.

Sorting in DESCENDING Order

Sorting by Column Alias

We can use a column alias in the ORDER BY clause. The below example sorts the data by Annual Salary.

Example:

Sorting By Multiple Columns:

We can sort query by more than one column. The sort limit is the number of columns in the given table.

Example:

ORDER BY Clause in SQL

• Sort rows with the ORDER BY clause
• ASC:  ascending order (Default)
• DESC:  descending order
• The ORDER BY clause comes last in the SELECT statement.
• We can Sort the rows by the name of the column which may not be present in the sorted list. (see second example)

Example:

Example: Here names of employees are sorted according to salary but salary column is not present in the sorting list. 

Operator Precedence in SQL

• The rules of precedence determines the order in which expressions are evaluated and calculated.
• The table below lists the default order of precedence.
• You can override the default order by using parentheses around the expressions you want to calculate first.

   ORDER EVALUATED                     OPERATOR

                   1                                        Arithmetic Operator

                   2                                        Concatenation Operator

                   3                                        Comparison Conditions

                   4                                        IS [NOT] NULL, LIKE, [NOT] IN

                   5                                        [NOT] BETWEEN

                   6                                        NOT logical condition

                   7                                        AND logical condition

                   8                                        OR logical condition

NOTE:

Override rules of precedence by using parentheses.

Example:  (Carefully study both the scenario in the example)

1) In the first scenario first AND operator is calculated and then the result is operated with OR.

2) In the second scenario first OR operator is calculated because of Parentheses and then result is operated with AND.

NOT Operator in SQL

Example:

The above example displays the name,job of employee whose job is not MANAGER or ANALYST.

Note:

The NOT operator can also be used with other SQL operators, such as BETWEEN, LIKE and NULL

-------- WHERE sal NOT BETWEEN 1000 AND 3000
-------- WHERE ename NOT LIKE '%K%'
-------- WHERE comm IS NOT NULL

OR Operator in SQL

• OR requires either conditions to be TRUE. (i.e. any one condition should be satisfied)

Example:

In the above example either conditions can be true for any record to be selected. The employee can have sal >=2000 or his/her department number may be 10 or 20.

AND Operator in SQL

• AND requires both conditions to be TRUE.

Example:

In the above example the results shown satisfies both the conditions to be displayed. 

Logical Conditions ( AND, OR, NOT Operators)

• A Logical condition combines the result of two component conditions to produce a single based on them or inverts the result of a single condition.
• A row is returned only if the overall result of the condition is true.
• Three logical operators are available in SQL:

OPERATOR                             MEANING

AND                                         Returns TRUE if both component conditions are true.
OR                                            Returns TRUE if either component condition is true.
NOT                                         Returns TRUE if the following condition is false.

(For examples see next posts)

NULL Condition in SQL

1. IS NULL
2. IS NOT NULL

NOTES:

• The NULL conditions includes the IS NULL condition and IS NOT NULL conditions.
• The IS NULL conditions tests for nulls.
• A null value means the value is unavailable, unassigned, unknown or inapplicable. (null and zero both are different)
• Therefore, you cannot test with = because a null cannot be equal or unequal to any value.

Example:

The above example retrieves the last names and managers of all employees who do not have a manager.

Example:
To retrieve the names, salary of employees who don't receive commission we can write the query like:

Example:

The above example shows the name,sal and commission of employees who commission is not null, i.e.

the employee receives commission, it may be zero but not null.

So, remember: null and zero both are different. 

LIKE Condition in SQL

• Use the LIKE condition to perform wildcard searches of valid search string values.
• Search conditions can contain either literal characters or number:
• % denotes zero or many characters.
• _ denotes one character.

Example:

Example: 

We can combine Pattern-Matching characters.

The % and _ symbols can be used in any combination with literal characters. The example on the slide displays the name of all employees whose name have an O as the second character. (in upper example)

The lower example displays the names of employees who has job ending with ER.

IN Condition

Use the IN membership condition to test for values in a list.

Example:

To test for values in a specified set of values, use the IN condition. The IN condition is also known as the "membership condition"

The above example displays employee no, name, salary, deptno from emp table for all employees whose salary is 1000,2000,3000, or 5000.

The IN condition can be used with any datatype.

Example:

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.

Comparison Operator

OPERATOR             MEANING

      =                         Equal to
      >                         Greater Than
      >=                       Greater than or equal to
      <                         Less than
      <=                       Less than or equal to
      <>                       Not equal to

Using Comparison Conditions

In the above example, the SELECT statement retrieves the name,salary from emp table whose salary is greater than or equal (>=) 2500.

 Other Comparison Conditions

OPERATOR                        MEANING

BETWEEN....AND...        Between two values (inclusive)

IN (set)                              Match any of a list of values

LIKE                                 Match a character pattern

IS NULL                           Is a null value

These examples will be continued to next post. 

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: