Oracle

SQL COMMANDS

Languages:

1. DQL  Data query language(Select)
2. DML ( Insert, Update, Delete, Marge)
3. DDL ( Create, Alter, Drop, Rename, Truncate)
4. TCL ( Commit, Rollback, SavePoint)
5. DCL (Grant, Revoke)

SELECT STATEMENT

Select *|Distinct Col name from table;

Arithmetic Expression: (+, -,*, /)

Precedence (*, /, +, -)

Null value

1. A null is a value that is unavailable, unassigned, unknown, inapplicable.
2. A NULL is not the same as Zero or a Blank.
3. Arithmetic expression containing a null value evaluate to null.

Defining Column Alias:

1. Rename as a column heading.
2. It is useful with Calculation
3. Immediately follows the column name there can also be the optional AS keyword between the column name and alias.
4. Requires double quotations marks if it contains space or special characters or is case sensitive.
5. Example: select  last_name AS(optional) name,commission  comm. From employee.

                                                     Or

     Select last_name “Name”,salary*12 “Annul salary” from employee.

Concatenation Operator:

1. Concate columns or characters string to other column.
2. It is represented by two vertical bars (||).
3. Create a resultant column that is a character expression.
4. Example: select lastname || job_id AS “Employees” from employee;

Order By: Sort rows with Order By Clause (ASC / DESC)

1.  Order By clause comes last in the SELECT Statement.

2.  Example: select expr from table where condition(s) order by [column exp][ASC /  DESC]

Sorting By column Alias

1. Example: Select emp_id,Last_name,salary*12 AS AnnualSal from emp order by 

                       AnnualSal;

Sorting By multipul columns

1. Example: Select last_name,salary from emp order by dept_id,salary DESC;

Functions:

Functions are very powerful feature of SQL

1.      Perform calculation on data.

2.      Modify individual data items.

3.      Manipulate output for group of rows.

4.      Format dates and numbers for display.

5.      Convert column data types.

Type of SQL Functions:

    There are two type of SQL Functions

1.      Single Row Function.

2.      Multiple Row Function.

1.      Single Row Functions:

These function operate on single row only and return one result per row.

1.      Manipulate data items.

2.      Accept arguments and return one value.

3.      Act on each row returned.

4.      Return one result per row.

5.      May modify the data type.

6.      can be nested.

7.      Accept arguments which can be a column or an expression.

           Note: An argument in single row function can be one of the following:

1.      User-supplied constant

2.      Variable value

3.      Expression.

           Features of single row functions:

1.      Acting on each row returned in the query.

2.      Returning one result per row.

3.      Possibly returning a data value of a different type than that referenced.

4.      Possibly expecting one or more arguments.

5.      Can be used in SELECT, WHERE and ORDER BY clause can be nested.

Type of Single Row Function

1.  Character

2.  Number

3.  Date

4.  Conversion

5.  General (NVL, NVL2, NULLIF, COALSECE, CASE, DECODE)