Conditional Expression in SQL

• Provide the use of IF-THEN-ELSE logic within a SQL statement.
• Use two methods:
• CASE expression.
• DECODE function.

The CASE Expression

• Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement.

Syntax:

CASE expr WHEN comparison_expr1 THEN return_expr1

                    [ WHEN comparison _expr2 THEN return_expr2

                     WHEN comparison_exprn THEN return_exprn

                     ELSE else_expr ]

END

• In a simple CASE expression, Oracle searches for the first WHEN --- THEN pair for which expr is equal to comparison_expr and returns return_expr. 
• If none of the WHEN --- THEN pairs meet this condition, and ELSE clause exists, then Oracle returns else_expr.
• Otherwise, Oracle returns null.
• You can't specify the literal NULL for all the return_exprs and the else_expr.

Example:

The DECODE Function

Facilitates conditional inquiries by doing the work of a CASE or IF-THEN-ELSE statement:

Syntax:

DECODE(col|expression, search1, result1

                                        [,search2,result2,...]

                                        [,default])

NOTE:

• The DECODE function decodes an expression in a way similar to the IF-THEN-ELSE logic used in various languages. 
• The DECODE function decodes expression after comparing it to each search value.
• If the expression is the same as search, result is returned.
• If the default value is omitted, a null value is returned where a search value does not match any of the result value.

Example:

General Functions in SQL

These functions work with any data type and pertain to using nulls.

• NVL (expr1,expr2): Converts a null value to an actual value
• NVL2 (expr1,expr2,expr3): If expr1 is not null, NVL2 returns expr2. If expr1 is null, NVL2 returns expr3. The argument expr1 can have any data type.
• NULLIF (expr1,expr2): Compares two expressions and returns null if they are equal, or the first expression if they are not equal.
• COALESCE ( expr1,expr2,....., expr n): Returns the first non-null expression in the expression list.

Using NVL Function

• Data types that can be used are date, character, and number.
• Data types must match:
• NVL ( comm,0 )
• NVL (hiredate,'01-JAN-13')
• NVL(job,'No Job Yet')

Example:

Using the NVL2 function

• The NVL2 function examines the first expression. 
• If the first expression is not null, then the NVL2 function returns the second expression. 
• If the first expression is null, then the third expression is returned.

Syntax:

NVL2(expr1,expr2,expr3)

In the syntax:

expr1 : is the source value or expression that may contain null.

expr2: is the value returned if exp1 is null.

expr3: is the value returned if expr2 is null.

Example:

Using the NULLIF Function

• The NULLIF function compares two expressions. 
• If they are equal, the function returns null. 
• If they are not equal the function returns the first expression. 
• You cannot specify the literal NULL for first expression.

Syntax: 

 NULLIF(expr1,expr2)

In the syntax:

expr1  is the source value compared to expr2.

expr2  is the source value compared with expr1. (if it is not equal to expr1, expr1 is returned.)

Example:

In the above example, no employee having salary=their annual commision. 

So everytime in the result tab, first value is returned. 

If in the case any employee having sal= his annual commission (12*comm) then we should have noticed null in RESULT tab.

Using the COALESCE Function

• The advantage of the COALESCE function over the NVL function is that the COALESCE function can take multiple alternate values.
• If the first expression is not null, it returns that expression; Otherwise, it does a COALESCE of the remaining expressions.
• This function finds the first non null expression and returns. Thats it. 

Syntax:

              COALESCE(expr1,expr2,......exprn)

In the Syntax:

expr1  returns this expression if it is not null.

expr2  returns this expression if the first expression is null and this expression is not null.

exprn  returns this expression if the preceding expression are null.

Example:

Conditional Expression  covers in Next post.

• CASE expression
• DECODE function

 

Conversion Function in SQL

In some cases, Oracle server uses data of one data type where it expects data of a different data type. When this happens, Oracle server can automatically convert the data to the expected data type. This data type conversion can be done implicitly by Oracle server, or explicitly by the user.

Implicit Data Type Conversion

For assignments, the Oracle server can automatically convert the following:

                    From                                           To

        VARCHAR2 OR CHAR                   NUMBER

        VARCHAR2 OR CHAR                   DATE

        NUMBER                                         VARCHAR2

        DATE                                                VARCHAR2

The assignment succeed if the Oracle server can convert the data type of the value used in the assignment to that of the assignment target.

Explicit data type conversion covers in Next post.

Explicit Data Type Conversion

SQL Provides 3 functions to convert a value from one data type to another:

TO_CHAR (number | date, [, fmt] ) 

This function converts a number or date value to a VARCHAR2 character string with format model fmt.

TO_NUMBER (char, [fmt] )

This function converts a character string containing digits to a number in the format specified by the optional format model fmt.

TO_DATE (char, [fmt] )

This function converts a character string representing a date to a date according to the fmt specified. If fmt is omitted, the format is DD-MON-YY.

Using the TO_CHAR Function with Dates

TO_CHAR(date,'format_model')

The format model:

• Must be enclosed in single quotation marks and is case sensitive.
• Can include any valid date format element.

Example: 

TO_CHAR ( number,'format_model')

Example: here you can notice the both sal and salary printed with formatted result.

Note:

TO_NUMBER()

and TO_DATE()  

I'll upload the example if any one ask for me. In the comment section ask for me, and I'll upload it. 

Date Function - Working with Dates

SYSDATE is a function that returns the current database server date and time.

Example:

SELECT SYSDATE                                  * DUAL is a dummy table.
FROM DUL;

Result
----------------
30-MAY-13

Arithmetic with Dates

• Add or subtract a number to or from a date for a resultant date values.
• Subtract two dates to find the number of days between those dates.
• Add hours to a date by dividing the number of hours by 24.

Example:

The example on the slide displays the last name and the number of weeks worked by the employee department number 10.

Date Functions

• MONTHS_BETWEEN(date1,date2): Finds the number of months between date1 and date2.
• ADD_MONTHS(date,n): Adds n number of calender months to date.
• NEXT_DAY(date,'char'): Finds the date of the next specified day of the week.
• LAST_DAY(date): Finds the date of the last day of the month that contains date.
• ROUND(date [, 'fmt']): Returns date rounded to the unit specified by the format model fmt.
• TRUNC(date [, 'fmt']): Returns date with the time portion of the day truncated to the unit specified by the format model fmt.

Using Date Functions

MONTHS_BETWEEN

 ADD_MONTHS

NEXT_DAY

LAST_DAY

ROUND

TRUNC

Number Functions in SQL

• ROUND : Rounds values to specified decimal.

                   ROUND(45.878,2)       ------------------->   45.88

• TRUNC  :  Truncates value to specified decimal.

                   TRUNC(45.878,2)       -------------------->   45.87

• MOD : Returns remainder of division.

                   MOD(1600,300)          -------------------->  100

Example:

ROUND

The Round Function rounds the column, expression, or value to n decimal place.

If the second argument is 0 or is missing, the value is round to zero decimal place.

If the second argument is 2, the value is rounded to two decimal places.

If the second argument is -2, the value is rounded to two decimal places to the left.

TRUNC

The TRUNC function truncates the column, expression, or value to n decimal places.

If the second argument is 0 or is missing, the value is truncated to zero decimal places.

If the second argument is 2, the value is truncated to two decimal places.

If the second argument is -2, the value is truncated to two decimal places to the left.

Character Functions in SQL

• Case-manipulation functions
• LOWER   :- Converts alpha character values to lowercase.
• UPPER   :-  Converts alpha character values to uppercase.
• INITCAP  :-  Converts alpha character values to uppercase for the first letter of               each word.
• Character-manipulation functions
• CONCAT  :-  Concatenates the first character values to second character value.
• SUBSTR  :-  Return specified characters from character value.
• LENGTH  :-  Returns the number of character in the expression.
• INSTR  :-  Returns the numeric position of a named string.
• LPAD  :-  Pads the character value right justified to a total width of n.
• RPAD  :- Pads the character value left justified to a total width of n.
• TRIM  :-  Enables you to trim heading or trailing characters from a character string.
• REPLACE  :-  Searches a text expression for a character string and if found, replaces it with a specified replacement string.

Example:

LOWER

UPPER & INITCAP

CONCAT

SUBSTR

LENGTH

INSTR

LPAD & RPAD

TRIM

Unit 3 :- Single-Row Functions

Functions are very powerful feature of SQL and can be used to do the following:

• Perform calculations on data.
• Modify individual data items.
• Manipulate output for groups of rows.
• Format dates and numbers for display.
• Convert column data types.

SQL functions sometimes take arguments and always return a value.

There are Two distinct types of functions:

• Single-row functions.
• Multiple-row functions.

Single-Row Functions

These functions operate on single rows only and return one result per row. There are different types single-row functions. This section covers the following ones:

• Character
• Case-manipulation functions
• LOWER
• UPPER
• INITCAP
• Character-manipulation functions
• CONCAT
• SUBSTR
• LENGTH
• INSTR
• LPAD   |   RPAD
• TRIM
• REPLACE
• Number
• ROUND
• TRUNC
• MOD
• Date
• MONTHS_BETWEEN
• ADD_MONTHS
• NEXT_DAY
• LAST_DAY
• ROUND
• TRUNC
• Conversion
• Implicit data type conversion
• VARCHAR2 OR CHAR -----   TO -----   NUMBER
• VARCHAR2 OR CHAR -----   TO -----   DATE
• NUMBER                       -----    TO -----  VARCHAR2
• DATE                              -----    TO ----    VARCHAR2
• Explicit data type conversion
• TO_CHAR
• TO_DATE
• TO_NUMBER
• General
• NVL
• NVL2
• NULLIF
• COALESCE
• CASE
• DECODE

Multiple-Row Functions

Functions can manipulate groups of rows to give one result per group of rows. These functions are known as group functions. This is covered in next Unit.

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:

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.