SQL DCL, Clauses and Aggregate Functions

DCL – Data Control Language

Commit :- To save the database.

Rollback :- Used to restore the database from start to the last commit.

Grant :- Giving the permission to user.

Revoke :- taking back the permissions from user.

SQL Clauses:

Select :- To retrieve data / rows from table

From  :- List table and join information

Where :- Filter result

Order By :- Sort query result

Group By :- Group by one or more column

Like :- Compare values to similar values using wildcard operator      (%),(_)

Having :- Restrict the groups of returned rows

1. WHERE :- To Filter Result

You can specify the condition by using logical operator (<,>,<=,>=,!=)

 Syntax:

 SQL> SELECT <COLUMN_NAME> 

        2 FROM <TABLE_NAME>

        3 WHERE <CONDITION>

        4 ;

Example:

 SQL> SELECT EMP_NAME, ADD

        2 FROM EMPLOYEE

        3 WHERE EMP_NO = ‘4567’

        4 ;

2. ORDER BY :- To Sort Query Result

Syntax:

 SQL> SELECT <COLUMN_LIST>

        2 FROM <TABLE_NAME>

        3 WHERE <CONDITION>

        4 ORDER BY <COLUMN1>

        5 ;

Example:

 SQL> SELECT <EMP_NAME>

        2 FROM <TABLE_NAME>

        3 WHERE <>

        4 ORDER BY <EMP_NAME>

        5 ;

3. GROUP BY :- Arrange Same Data Into Group

The GROUP BY clause must follow the conditions in the WHERE clause and must precede the ORDER BY clause if one is used.

Syntax:

 SQL> SELECT <COLUMN1>

        2 FROM <TABLE_NAME>

        3 WHERE <CONDITION>

        4 GROUP BY <COLUMN1>

        5 ORDER BY <COLUMN1>

        6 ;

Example: 

 SQL> SELECT EMP_NAME

        2 SUM(SALARY)

        3 FROM EMPLOYEE

        4 GROUP BY EMP_NAME

        5 ;

4. HAVING :-Which Group result Appear In Result

Syntax:

 SQL> SELECT <COLUMN1>

        2 FROM <TABLE_NAME>

        3 WHERE <CONDITION>

        4 GROUP BY <CONDITION>

        5 HAVING <CONDITION>

        6 ORDER BY <CONDITION>

        7 ;

Example :

 SQL> SELECT > EMP_NAME

        2 FROM EMPLOYEE

        3 GROUP BY EMP_ID

        4 HAVING SAL>10000

        5 ;

5. LIKE :- Compare Values To Similar values

Syntax:

 SQL> SELECT * 

        2 FROM <TABLE_NAME>

        3 WHERE <COLUMN_NAME>

        4 LIKE ‘%XX%’

        5 ;

Example:

 SQL> SELECT * 

        2 FROM <TABLE_NAME>

        3 WHERE SALARY

        4 LIKE ‘%20%’

        5 ;

 (Will show value which have 20 in any position)

SQL AGGREGATE FUNCTIONS:

COUNT() :-

 Returns the number of rows in the result. Does not count null values.

 Syntax:

 SQL> SELECT COUNT(*) 

        2 FROM <TABLE_NAME>

        3 WHERE <CONDITION>

        4 ;

SUM():

 SUM UP THE VALUES IN GIVEN COLUMN.

Syntax:

 SQL> SELECT <COLUMN_NAME>

        2 FROM <TABLE_NAME>

        3 ; 

MIN():

 Returns smallest value in column from table.

Syntax:

 SQL> SELECT MIN <COLUMN_NAME>

        2 FROM <TABLE_NAME>

        3 ;

MAX():

 Returns largest value from selected column in table.

Syntax:

SQL>SELECT  MAX <COLUMN_NAME>

       2 FROM<TABLE NAME>

       3 ;