COLLECT Function in Oracle SQL

SQL> set lines 150
SQL> set pages 50
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> column names_list format a80
SQL> select deptno,collect(ename) as names_list from emp group by deptno;

    DEPTNO NAMES_LIST
---------- --------------------------------------------------------------------------------
        10 SYSTPTuDBVpKQTfitWChB4kLTxg==('CLARK', 'KING', 'MILLER')
        20 SYSTPTuDBVpKQTfitWChB4kLTxg==('SMITH', 'JONES', 'SCOTT', 'ADAMS', 'FORD')
        30 SYSTPTuDBVpKQTfitWChB4kLTxg==('ALLEN', 'WARD', 'MARTIN', 'BLAKE', 'TURNER', 'JAM
           ES')


SQL> create type t_varchar2 as table of varchar2(4000);
  2  /

Type created.

SQL> break on deptno skip 1
SQL> select deptno,cast(collect(ename) as t_varchar2) as names_list from emp group by deptno;

    DEPTNO NAMES_LIST
---------- --------------------------------------------------------------------------------
        10 T_VARCHAR2('CLARK', 'KING', 'MILLER')

        20 T_VARCHAR2('SMITH', 'JONES', 'SCOTT', 'ADAMS', 'FORD')

        30 T_VARCHAR2('ALLEN', 'WARD', 'MARTIN', 'BLAKE', 'TURNER', 'JAMES')



SQL> select deptno,cast(collect(ename order by ename) as t_varchar2) as names_list from emp group by deptno;

    DEPTNO NAMES_LIST
---------- --------------------------------------------------------------------------------
        10 T_VARCHAR2('CLARK', 'KING', 'MILLER')

        20 T_VARCHAR2('ADAMS', 'FORD', 'JONES', 'SCOTT', 'SMITH')

        30 T_VARCHAR2('ALLEN', 'BLAKE', 'JAMES', 'MARTIN', 'TURNER', 'WARD')


SQL> select deptno,cast(collect(ename order by sal) as t_varchar2) as names_list from emp group by deptno;

    DEPTNO NAMES_LIST
---------- --------------------------------------------------------------------------------
        10 T_VARCHAR2('MILLER', 'CLARK', 'KING')

        20 T_VARCHAR2('SMITH', 'ADAMS', 'JONES', 'SCOTT', 'FORD')

        30 T_VARCHAR2('JAMES', 'WARD', 'MARTIN', 'TURNER', 'ALLEN', 'BLAKE')


SQL> select deptno,cast(collect(ename order by sal desc) as t_varchar2) as names_list from emp group by deptno;

    DEPTNO NAMES_LIST
---------- --------------------------------------------------------------------------------
        10 T_VARCHAR2('KING', 'CLARK', 'MILLER')

        20 T_VARCHAR2('SCOTT', 'FORD', 'JONES', 'ADAMS', 'SMITH')

        30 T_VARCHAR2('BLAKE', 'ALLEN', 'TURNER', 'WARD', 'MARTIN', 'JAMES')


SQL> select deptno,cast(collect(job) as t_varchar2) as names_list from emp group by deptno;

    DEPTNO NAMES_LIST
---------- --------------------------------------------------------------------------------
        10 T_VARCHAR2('MANAGER', 'PRESIDENT', 'CLERK')

        20 T_VARCHAR2('CLERK', 'MANAGER', 'ANALYST', 'CLERK', 'ANALYST')

        30 T_VARCHAR2('SALESMAN', 'SALESMAN', 'SALESMAN', 'MANAGER', 'SALESMAN', 'CLERK')


SQL> select deptno,cast(collect(distinct job) as t_varchar2) as names_list from emp group by deptno;

    DEPTNO NAMES_LIST
---------- --------------------------------------------------------------------------------
        10 T_VARCHAR2('CLERK', 'MANAGER', 'PRESIDENT')

        20 T_VARCHAR2('ANALYST', 'CLERK', 'MANAGER')

        30 T_VARCHAR2('CLERK', 'MANAGER', 'SALESMAN')



SQL> select deptno,cast(collect(unique job) as t_varchar2) as names_list from emp group by deptno;

    DEPTNO NAMES_LIST
---------- --------------------------------------------------------------------------------
        10 T_VARCHAR2('CLERK', 'MANAGER', 'PRESIDENT')

        20 T_VARCHAR2('ANALYST', 'CLERK', 'MANAGER')

        30 T_VARCHAR2('CLERK', 'MANAGER', 'SALESMAN')


SQL> select deptno,cast(collect(all job) as t_varchar2) as names_list from emp group by deptno;

    DEPTNO NAMES_LIST
---------- --------------------------------------------------------------------------------
        10 T_VARCHAR2('MANAGER', 'PRESIDENT', 'CLERK')

        20 T_VARCHAR2('CLERK', 'MANAGER', 'ANALYST', 'CLERK', 'ANALYST')

        30 T_VARCHAR2('SALESMAN', 'SALESMAN', 'SALESMAN', 'MANAGER', 'SALESMAN', 'CLERK')


SQL> select deptno,cast(collect(distinct job order by job) as t_varchar2) as names_list from emp group by deptno;

    DEPTNO NAMES_LIST
---------- --------------------------------------------------------------------------------
        10 T_VARCHAR2('CLERK', 'MANAGER', 'PRESIDENT')

        20 T_VARCHAR2('ANALYST', 'ANALYST', 'CLERK', 'CLERK', 'MANAGER')

        30 T_VARCHAR2('CLERK', 'MANAGER', 'SALESMAN', 'SALESMAN', 'SALESMAN', 'SALESMAN')


SQL> --using complex types
SQL> create type empsal_ot as object (ename varchar2(100),sal number);
  2  /

Type created.

SQL> create type empsal_ntt as table of empsal_ot;
  2  /

Type created.

SQL> col empsals format a50 wrap
SQL> break on deptno skip 1

SQL> select deptno,cast(collect(empsal_ot(ename,sal)) as empsal_ntt) as empsals from emp group by deptno;

    DEPTNO EMPSALS(ENAME, SAL)
---------- --------------------------------------------------
        10 EMPSAL_NTT(EMPSAL_OT('CLARK', 2450), EMPSAL_OT('KI
           NG', 5000), EMPSAL_OT('MILLER', 1300))


        20 EMPSAL_NTT(EMPSAL_OT('SMITH', 800), EMPSAL_OT('JON
           ES', 2975), EMPSAL_OT('SCOTT', 3000), EMPSAL_OT('A
           DAMS', 1100), EMPSAL_OT('FORD', 3000))


        30 EMPSAL_NTT(EMPSAL_OT('ALLEN', 1600), EMPSAL_OT('WA
           RD', 1250), EMPSAL_OT('MARTIN', 1250), EMPSAL_OT('
           BLAKE', 2850), EMPSAL_OT('TURNER', 1500), EMPSAL_O
           T('JAMES', 950))



SQL> select deptno,cast(collect(empsal_ot(ename,sal) order by ename) as empsal_ntt) as empsals from emp group by deptno;

    DEPTNO EMPSALS(ENAME, SAL)
---------- --------------------------------------------------
        10 EMPSAL_NTT(EMPSAL_OT('CLARK', 2450), EMPSAL_OT('KI
           NG', 5000), EMPSAL_OT('MILLER', 1300))


        20 EMPSAL_NTT(EMPSAL_OT('ADAMS', 1100), EMPSAL_OT('FO
           RD', 3000), EMPSAL_OT('JONES', 2975), EMPSAL_OT('S
           COTT', 3000), EMPSAL_OT('SMITH', 800))


        30 EMPSAL_NTT(EMPSAL_OT('ALLEN', 1600), EMPSAL_OT('BL
           AKE', 2850), EMPSAL_OT('JAMES', 950), EMPSAL_OT('M
           ARTIN', 1250), EMPSAL_OT('TURNER', 1500), EMPSAL_O
           T('WARD', 1250))




SQL> select deptno,cast(collect(empsal_ot(ename,sal) order by empsal_ot(ename,sal)) as empsal_ntt) as empsals from emp group by deptno;
select deptno,cast(collect(empsal_ot(ename,sal) order by empsal_ot(ename,sal)) as empsal_ntt) as empsals from emp group by deptno
                                                         *
ERROR at line 1:
ORA-22950: cannot ORDER objects without MAP or ORDER method


SQL> select deptno,cast(collect(distinct empsal_ot(ename,sal)) as empsal_ntt) as empsals from emp group by deptno;
select deptno,cast(collect(distinct empsal_ot(ename,sal)) as empsal_ntt) as empsals from emp group by deptno
                                    *
ERROR at line 1:
ORA-22950: cannot ORDER objects without MAP or ORDER method


SQL> drop type empsal_ntt;

Type dropped.


SQL> create or replace type empsal_obj as object(ename varchar2(50),sal number,
  2  map member function sort_key return varchar2);
  3  /

Type created.

SQL> edit
Wrote file afiedt.buf

  1  create or replace type body empsal_obj as
  2  map member function sort_key return varchar2 is
  3  begin
  4  return ename||to_char(sal,'fm0000');
  5  end;
  6* end;
SQL> /

Type body created.

SQL> create type empsal_ntt is table of empsal_obj;
  2  /

Type created.

SQL> select deptno,cast(collect(distinct empsal_obj(ename,sal)) as empsal_ntt) as empsals from emp group by deptno;

    DEPTNO EMPSALS(ENAME, SAL)
---------- --------------------------------------------------
        10 EMPSAL_NTT(EMPSAL_OBJ('CLARK', 2450), EMPSAL_OBJ('
           KING', 5000), EMPSAL_OBJ('MILLER', 1300))


        20 EMPSAL_NTT(EMPSAL_OBJ('ADAMS', 1100), EMPSAL_OBJ('
           FORD', 3000), EMPSAL_OBJ('JONES', 2975), EMPSAL_OB
           J('SCOTT', 3000), EMPSAL_OBJ('SMITH', 800))


        30 EMPSAL_NTT(EMPSAL_OBJ('ALLEN', 1600), EMPSAL_OBJ('
           BLAKE', 2850), EMPSAL_OBJ('JAMES', 950), EMPSAL_OB
           J('MARTIN', 1250), EMPSAL_OBJ('TURNER', 1500), EMP
           SAL_OBJ('WARD', 1250))



SQL> INSERT INTO emp (empno, ename, sal) VALUES (9999, 'ALLEN', 1600);

1 row created.

SQL> select deptno,cast(collect(distinct empsal_obj(ename,sal)) as empsal_ntt) as empsals from emp group by deptno;

    DEPTNO EMPSALS(ENAME, SAL)
---------- --------------------------------------------------
        10 EMPSAL_NTT(EMPSAL_OBJ('CLARK', 2450), EMPSAL_OBJ('
           KING', 5000), EMPSAL_OBJ('MILLER', 1300))


        20 EMPSAL_NTT(EMPSAL_OBJ('ADAMS', 1100), EMPSAL_OBJ('
           FORD', 3000), EMPSAL_OBJ('JONES', 2975), EMPSAL_OB
           J('SCOTT', 3000), EMPSAL_OBJ('SMITH', 800))


        30 EMPSAL_NTT(EMPSAL_OBJ('ALLEN', 1600), EMPSAL_OBJ('
           BLAKE', 2850), EMPSAL_OBJ('JAMES', 950), EMPSAL_OB
           J('MARTIN', 1250), EMPSAL_OBJ('TURNER', 1500), EMP
           SAL_OBJ('WARD', 1250))


           EMPSAL_NTT(EMPSAL_OBJ('ALLEN', 1600))

SQL> select cast(collect(distinct empsal_obj(ename,sal)) as empsal_ntt) as empsals from emp;

EMPSALS(ENAME, SAL)
--------------------------------------------------
EMPSAL_NTT(EMPSAL_OBJ('ADAMS', 1100), EMPSAL_OBJ('
ALLEN', 1600), EMPSAL_OBJ('BLAKE', 2850), EMPSAL_O
BJ('CLARK', 2450), EMPSAL_OBJ('FORD', 3000), EMPSA
L_OBJ('JAMES', 950), EMPSAL_OBJ('JONES', 2975), EM
PSAL_OBJ('KING', 5000), EMPSAL_OBJ('MARTIN', 1250)
, EMPSAL_OBJ('MILLER', 1300), EMPSAL_OBJ('SCOTT',
3000), EMPSAL_OBJ('SMITH', 800), EMPSAL_OBJ('TURNE
R', 1500), EMPSAL_OBJ('WARD', 1250))


SQL> rollback;

Rollback complete.

SQL> select cast(collect(distinct empsal_obj(ename,sal)) as empsal_ntt) as empsals from emp;

EMPSALS(ENAME, SAL)
--------------------------------------------------
EMPSAL_NTT(EMPSAL_OBJ('ADAMS', 1100), EMPSAL_OBJ('
ALLEN', 1600), EMPSAL_OBJ('BLAKE', 2850), EMPSAL_O
BJ('CLARK', 2450), EMPSAL_OBJ('FORD', 3000), EMPSA
L_OBJ('JAMES', 950), EMPSAL_OBJ('JONES', 2975), EM
PSAL_OBJ('KING', 5000), EMPSAL_OBJ('MARTIN', 1250)
, EMPSAL_OBJ('MILLER', 1300), EMPSAL_OBJ('SCOTT',
3000), EMPSAL_OBJ('SMITH', 800), EMPSAL_OBJ('TURNE
R', 1500), EMPSAL_OBJ('WARD', 1250))


SQL> -- http://www.oracle-developer.net/display.php?id=514

SQL *Plus Commands

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> break on deptno skip 1
SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON


***********************************************************************************

SQL Server

Database (DB):
    Organized collection of data stored in an electronic format.
  
Database Management System (DBMS):
    DBMS is a system software for creating and managing database.
  
Types of Database Architecture:
        File-Server --> Files are located on your Local system
        Client-Server --> Files are located on server system which will be accessed by multiple client who requests
      
Structured Query Language (SQL):
    SQL stands for Structured Query Language which is a standard language for accessing and manipulating database.In simple terms SQL used to communicate with database.
  
Categories of SQL Commands:
    Data Query/Retrieval Language (DRL) --> SELECT
    Data Definition Language (DDL) --> CREATE, ALTER, TRUNCATE, DROP, RENAME
    Data Manipulation Language (DML) --> INSERT, UPDATE, DELETE
    Data Control Language (DCL) --> GRANT, REVOKE
    Transaction Control Language (TCL) --> COMMIT, ROLLBACK, SAVEPOINT
  
Table :
    A Table is a database object which comprises(consists/contains) of rows and columns.
  
Records:
    The rows known as Records. Each individual entry of a table is called the record.
  
Fields:  
    The columns known as Fields(attributes). A Field provides specific information about data in the table.
  
Create A Database:
    CREATE DATABASE databasename; Ex:  create database scott;  
Use A Database:  
    USE databasename;   Ex: use scott;
  
Drop A Database:
    DROP DATABASE databasename; Ex: drop database scott;
  
Data Types:
    Data Types define what type of data a column can hold
    Numeric Data Types --> bigint, int,    smallint, tinyint, decimal(s,d)
    Character Data Types --> char(s) - max length 255 chars,, varchar(s) - max length 255 chars, text - max length 65,535 chars
    Date & Time Data Types --> date, time, year (YYYY-MM-DD, HH:MI:SS, YYYY)

Constraints:
    Constraints are used to specify rules for data in table
    NOT NULL --> ensures that the column cannot have a NULL value
    DEFAULT --> sets a default value for a column when no value is specified
    UNIQUE --> ensures that all values in a column are different
    PRIMARY KEY --> uniquely identifies each record in a table (i.e Not Null+Unique)
   
Create a Table:

    CREATE TABLE table_name
    (
    column1 datatype,   
    column2 datatype,   
    column3 datatype,   
    ------
    -------
    columnN datatype,
    PRIMARY KEY(column_X)
    );
    Note: Table cannot have more than one PRIMARY KEY.

CREATE TABLE EMP
(
   EMPNO      INT NOT NULL,
   ENAME      VARCHAR (10),
   JOB        VARCHAR (9),
   MGR        INT,
   HIREDATE   DATE,
   SAL        DECIMAL (7, 2),
   COMM       DECIMAL (7, 2),
   DEPTNO     INTEGER
);

CREATE TABLE DEPT
(
   DEPTNO  INTEGER,
   DNAME    VARCHAR (14),
   LOC      VARCHAR (13)
);


   
Insert values into a Table:
    INSERT INTO table_name(column1,column2,....,columnN) VALUES(value1,value2,.........valueN);   


INSERT INTO EMP VALUES (7369, 'SMITH',  'CLERK',     7902,'17-DEC-1980',  800, NULL, 20);
INSERT INTO EMP VALUES (7499, 'ALLEN',  'SALESMAN',  7698,'20-FEB-1981' , 1600,  300, 30);
INSERT INTO EMP VALUES (7521, 'WARD',   'SALESMAN',  7698, '22-FEB-1981' , 1250,  500, 30);
INSERT INTO EMP VALUES (7566, 'JONES',  'MANAGER',   7839, '2-APR-1981' ,  2975, NULL, 20);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN',  7698, '28-SEP-1981' , 1250, 1400, 30);
INSERT INTO EMP VALUES (7698, 'BLAKE',  'MANAGER',   7839, '1-MAY-1981' ,  2850, NULL, 30);
INSERT INTO EMP VALUES (7782, 'CLARK',  'MANAGER',   7839, '9-JUN-1981' ,  2450, NULL, 10);
INSERT INTO EMP VALUES (7788, 'SCOTT',  'ANALYST',   7566, '09-DEC-1982' , 3000, NULL, 20);
INSERT INTO EMP VALUES (7839, 'KING',   'PRESIDENT', NULL, '17-NOV-1981' , 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN',  7698, '8-SEP-1981' ,  1500,    0, 30);
INSERT INTO EMP VALUES (7876, 'ADAMS',  'CLERK',     7788, '12-JAN-1983' , 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, 'JAMES',  'CLERK',     7698, '3-DEC-1981' ,   950, NULL, 30);
INSERT INTO EMP VALUES (7902, 'FORD',   'ANALYST',   7566, '3-DEC-1981' ,  3000, NULL, 20);
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK',     7782, '23-JAN-1982' , 1300, NULL, 10);

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
    
Select values from Table:
    SELECT * FROM table_name;
    SELECT column1,column2,....columnN FROM table_name;
    SELECT DISTINCT column1 FROM table_name;

SELECT GETDATE() AS TODAY_DATE;
SELECT * FROM EMP;
SELECT EMPNO,ENAME,JOB,SAL FROM EMP;
SELECT DISTINCT DEPTNO FROM EMP;

   
    WHERE clause is used to extract records which satisfy a condition.
        SELECT column1,column2,....columnN FROM table_name WHERE [condition];   
SELECT * FROM EMP WHERE DEPTNO=30;

   
AND --> Operator displays records if all conditions seperated by AND are TRUE
    SELECT column1,column2,....columnN FROM table_name WHERE [condition1] AND [condition2] AND [conditionN];
OR --> Operator displays records if any of the conditions seperated by OR are TRUE
    SELECT column1,column2,....columnN FROM table_name WHERE [condition1] OR [condition2] OR [conditionN];
NOT --> Operator displays a record if the condition is NOT TRUE.
    SELECT column1,column2,....columnN FROM table_name WHERE NOT [condition1];
LIKE --> Operator used to extract records where a particular pattern is present. (% Represents zero,one or multiple characters , _ Represents Single character)
    SELECT column1,column2,....columnN FROM table_name WHERE column_N like '_XXXX%';
BETWEEN --> Operator used to select values within a given range
    SELECT column1,column2,....columnN FROM table_name WHERE column_N BETWEEN val1 and val2;

SELECT * FROM EMP WHERE DEPTNO=30 AND JOB='CLERK';
SELECT * FROM EMP WHERE DEPTNO=30 OR JOB='CLERK';
SELECT * FROM EMP WHERE NOT DEPTNO=30 ;
SELECT * FROM EMP WHERE DEPTNO<>30;
SELECT * FROM EMP WHERE DEPTNO!=30 ;
SELECT * FROM EMP WHERE ENAME LIKE 'S%';
SELECT * FROM EMP WHERE ENAME LIKE '_M%';
SELECT * FROM EMP WHERE ENAME LIKE '%K';
SELECT * FROM EMP WHERE DEPTNO BETWEEN 10 AND 20;

   
Aggregate Functions:

MIN() function gives you the smallest value SELECT MIN(column) FROM table_name;
MAX() function gives you the largest value SELECT MAX(column) FROM table_name;
COUNT() function returns the number of rows that match a specific criteria. SELECT COUNT(*) FROM table_name;
SUM() function gives you the total sum of a numeric column SELECT SUM(column) FROM table_name;
AVG() function gives you the average value of a numeric column SELECT AVG(column) FROM table_name;

SELECT MAX(SAL) FROM EMP;
SELECT MIN(SAL) FROM EMP;
SELECT SUM(SAL) FROM EMP;
SELECT AVG(SAL) FROM EMP;
SELECT COUNT(*) FROM EMP;

String Functions:

LTRIM() Removes blanks on the left side of the character expression
LOWER()  Converts all characters to lower case letters
UPPER()  Converts all characters to upper case letters
REVERSE() Reverse all characters in the string
SUBSTRING()  Gives a substring from the original string


SELECT LTRIM(' RAJASEKHAR'),RTRIM('RAJASEKHAR   '),TRIM('  RAJA  ');
SELECT UPPER(ENAME) FROM EMP;
SELECT LOWER('RAJASEKHAR');
SELECT REVERSE('I LOVE SQL');
SELECT SUBSTRING('RAJASEKHAR',5,6);


GROUP BY clause is used to aggregate result with respect to a group
      SELECT column_list FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY  column_name(s) ; 
HAVING BY clause is used in combination with GROUP BY to impose conditions on groups.
      SELECT column_list FROM table_name WHERE condition GROUP BY column_name(s)  HAVING condition ORDER BY column_name(s); 
ORDER BY clause is used to sort the data in ascending or descending order
      SELECT column1,column2,....columnN FROM table_name ORDER BY col1,col2,..ASC|DESC;
TOP clause is used to fetch TOP N records.
       SELECT TOP x column_list FROM table_name;

SELECT * FROM EMP ORDER BY DEPTNO ;
SELECT * FROM EMP ORDER BY DEPTNO ASC ;
SELECT * FROM EMP ORDER BY DEPTNO DESC;
SELECT * FROM EMP WHERE DEPTNO IN (10,20) ORDER BY DEPTNO ;
SELECT DEPTNO,SUM(SAL) AS TOTAL FROM EMP GROUP BY DEPTNO ;
SELECT DEPTNO,SUM(SAL) AS TOTAL FROM EMP GROUP BY DEPTNO  ORDER BY DEPTNO;
SELECT DEPTNO,SUM(SAL) AS TOTAL FROM EMP GROUP BY DEPTNO HAVING SUM(SAL)>10000;
SELECT DEPTNO,SUM(SAL) AS TOTAL FROM EMP WHERE DEPTNO IN (10,20,30) GROUP BY DEPTNO HAVING SUM(SAL)>9000 ORDER BY DEPTNO ;


UPDATE:
    Update is used to modify existing records in a table,
    UPDATE table_name SET col1=val1col2=val2,.... [WHERE condition];
    UPDATE EMP SET SAL=SAL+1000 WHERE DEPTNO=30;

DELETE:
 Delete statement is used to delete existing records in a table..
 DELETE FROM table_name [WHERE condition];
 DELETE FROM EMP WHERE DEPTNO=20;

TRUNCATE:
 Truncate statement is used to remove all existing records in a table but structure of the table still exists..
 TRUNCATE TABLE table_name;
 TRUNCATE TABLE EMP;


Inner Join:
     Returns records that have matching values in both the tables. It is also known as simple join.
     SELECT columns FROM table1 INNER JOIN table2 ON table1.column_x=table2.column_Y;
     SELECT E.EMPNO,E.ENAME,E.SAL,E.DEPTNO,D.DNAME,D.LOC FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO;

Left Join;
    Returns all records from the left table, and the matched records from the right table.
    SELECT columns FROM table1 LEFT JOIN table2 ON table1.column_x=table2.column_Y;
SELECT E.EMPNO,E.ENAME,E.SAL,E.DEPTNO,D.DNAME,D.LOC FROM EMP E LEFT JOIN DEPT D ON E.DEPTNO=D.DEPTNO;
  
Right Join;
    Returns all records from the right table, and the matched records from the left table.
    SELECT columns FROM table1 LEFT JOIN table2 ON table1.column_x=table2.column_Y;
SELECT E.EMPNO,E.ENAME,E.SAL,E.DEPTNO,D.DNAME,D.LOC FROM EMP E RIGHT JOIN DEPT D ON E.DEPTNO=D.DEPTNO;

Full Join;
    Returns all records from the LEFT table and the RIGHT table with null values in place where join condition is not met.
    SELECT columns FROM table1 FULL JOIN table2 ON table1.column_x=table2.column_Y;
SELECT E.EMPNO,E.ENAME,E.SAL,E.DEPTNO,D.DNAME,D.LOC FROM EMP E FULL JOIN DEPT D ON E.DEPTNO=D.DEPTNO;

Update using Join;
     UPDATE  table1 SET column11=value1 FROM table1 JOIN table2 ON table1.column_x=table2.column_Y [WHERE condition];

UPDATE EMP SET SAL=SAL+100
FROM EMP E JOIN DEPT D ON E.DEPTNO=D.DEPTNO WHERE D.DNAME='RESEARCH';

Delete using Join;
     DELETE table1 FROM table1 JOIN table2 ON table1.column_x=table2.column_Y [WHERE condition];

DELETE EMP
FROM EMP E JOIN DEPT D ON E.DEPTNO=D.DEPTNO WHERE D.DNAME='RESEARCH';

Set Operators:  

SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 1;

SELECT 1
UNION
SELECT 2
UNION
SELECT 1;

(SELECT 1
UNION
SELECT 2)
EXCEPT
SELECT 1;

(SELECT 1
UNION
SELECT 2)
INTERSECT
SELECT 1;   

TOP:
    SELECT TOP 2 * FROM DEPT ORDER BY DEPTNO DESC;

Views:
    View is a Virtual Table is based on the result of SQL statment.
    CREATE VIEW EMP_V AS SELECT EMPNO,ENAME,SAL,JOB,DEPTNO FROM EMP;
    SELECT * FROM EMP_V;
    DROP VIEW EMP_V;

ALTER Statement:
    ALTER TABLE EMP ADD TEST INT;
    SELECT * FROM EMP;
    ALTER TABLE EMP DROP COLUMN TEST;

CREATE TABLE EMP_S(EMPNO INT,ENAME VARCHAR(200));
INSERT INTO EMP_S VALUES(7499,'ALLEN');
SELECT * FROM EMP;

MERGE Statement:
     MERGE is the combination of INSERT, DLEETE and UPDATE statements.
     MERGE EMP AS T
     USING EMP_S AS S ON T.EMPNO=S.EMPNO
     WHEN MATCHED THEN
         UPDATE SET T.ENAME=S.ENAME
     WHEN NOT MATCHED BY TARGET THEN
         INSERT(EMPNO,ENAME)VALUES(S.EMPNO,S.ENAME)
     WHEN NOT MATCHED BY SOURCE THEN
         DELETE;

Scalar Valued Function:
    Scalar Valued Function always returns a scalar value.
    CREATE FUNCTION add_five(@num INT)
    RETURNS INT
    AS
    BEGIN
    RETURN (@num+5);
    END;
    SELECT dbo.add_five(50);
    DROP FUNCTION add_five;

Table Valued Function:
    Table Valued Function returns a table.
    CREATE FUNCTION display_emp(@num INT)
    RETURNS table
    AS
    RETURN(SELECT * FROM EMP WHERE DEPTNO=@num);
    SELECT * FROM dbo.display_emp(30);

Temporary Table:
    Temporary Tables are created in tempDB and deleted as soon as the session is terminated.
    CREATE TABLE #student(sid int,sname varchar(100));
    INSERT INTO #student VALUES(1,'raj');
    SELECT * FROM #student;

Case Statement:   
    Case statement helps in multi way decision making
    select
    case
        when 10>20 then '10 is greater than 20'
        when 10<20 then '10 is less than 20'
        else '10 is equal to 20'
    end;

IIF() Function:
    IIF() function is an alternative for the case statement.
    select     iif(10>20,'10 is greater than 20','10 is less than 20');

Stored Procedure in sql:
    Strored Procedure is a prepared sql code which can be saved and reused.
    CREATE PROCEDURE display_ename
    AS
    --select statement
    SELECT ENAME FROM EMP
    GO;
    EXEC display_ename;
    DROP PROCEDURE display_ename;

    CREATE PROCEDURE display_empno @i_deptno int
    AS
    SELECT * FROM EMP WHERE DEPTNO=@i_deptno
    GO

    EXEC display_empno @i_deptno=10;
    DROP PROCEDURE display_empno;

Exception Handling:
    SQL Provides try/catch blocks for exception handling
    declare @val1 int
    declare @val2 int
    begin try
    set @val1=9;
    set @val2=@val1/0;
    end try
    begin catch
    print 'catching exception'
    print error_message()
    end catch
    go

Transactions in SQL:

    Transaction is group of commands that change data stored in a database
    begin transaction
    update emp set sal=sal+201 where deptno=20;
    commit transaction;
    rollback transaction;

    begin try
    begin transaction
    update emp set sal=0 where deptno=20
    update emp set sal=1/0 ;
    commit transaction
    print 'commit transaction'
    end try
    begin catch
    rollback transaction
    print 'rollback transaction'
    print error_message()
    end catch
    go

    select * from emp where deptno=20;

Database Administrator:
  A Database Administrator (DBA) directs or performs all activities related to maintaining a successful database environment.
  Software Installation and Maintenance
  Taking care of Database backup & Recovery
  Maintaing Security of the database
  Take care of Access control for different users
  Monitor databases for performance issues

Types of DBA:
    Production DBA
    Application DBA
    Development DBA
    UAT DBA
    Data warehouse DBA

Intro to MS SQL Server:
    SQL Server is Microsoft's relational database management system (RDBMS). It is a full featured database primarly designed to compete
    against competitors Oracle Database and MYSQL.It is also known as ORDBMS.It is both GUI and command based software.

Evolution of SQL Server:
    SQL Server 2000,2005,2008,2008 R2,2012,2014,2016,2017,.....

Editions of SQL Server:
    Enterprise,Standard,Web,Developer,Express
    https://www.youtube.com/watch?v=JTDK6r1GuUU&list=LLJ2jlYQyxwtWSG_5RzfBBjQ&index=2&t=555s

Instances of SQL Server:
    An instance is collection of SQL Server Databases run by single server service
    Default and Named Instances
    If you plan to install a single instance of SQL Server on database server, go with default instance
    Use named instance for situations where you planned to have multiple instances on the same server computer
    A computer can host only one default instance; all other instances must be named

SQL Server System Databases:
    System databases ae defined by Microsoft and are needed for SQL Server to operate. These databases includes
    Master --> The master database records all the system level information for a SQL Server system. This includes instance wide metadata such as
    logon accounts,endpoints,liked servers and system configuration settings
    Model --> The model database is used as a template whenever a new user database is created.You can change most database properties,create users,
    stored procedures,tables,views,etc-whatever you do will be applied to any  new databases.
    MSDB --> The msdb databse is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Managment Studio,
    Service Broker and Database Mail.
    TempDB --> TempDB is the workhorse of the system database.It is the workspace that SQL Server uses to store the intermediate results of
    query processing and sorting.

GOTO in PL/SQL

SQL> SET SERVEROUTPUT ON;
SQL>
SQL> DECLARE
  2     k   NUMBER := 10;
  3     v   NUMBER := 1;
  4  BEGIN
  5     GOTO hai;
  6
  7     IF k = NULL
  8     THEN
  9       <<hai>>
 10        v := v + 21;
 11     END IF;
 12
 13     /*<<hai>>
 14      v := v + 2;*/
 15
 16     DBMS_OUTPUT.put_line ('v=' || v);
 17  END;
 18  /
   GOTO hai;
   *
ERROR at line 5:
ORA-06550: line 5, column 4:
PLS-00375: illegal GOTO statement; this GOTO cannot branch to label 'HAI'
ORA-06550: line 8, column 4:
PL/SQL: Statement ignored


SQL>

Scope of Variable in PL/SQL

SQL> SET SERVEROUTPUT ON;
SQL>
SQL> DECLARE
  2     k   NUMBER := 10;
  3     v   NUMBER := 1;
  4  BEGIN
  5     v := v + 21;
  6  /* Inner block */
  7     DECLARE
  8        s   NUMBER := 0;
  9     BEGIN
 10        s := v;
 11        DBMS_OUTPUT.put_line ('s=' || s); -- can't use outside of this Inner block
 12     END;
 13  /* End Inner block */
 14     DBMS_OUTPUT.put_line ('s=' || s);
 15  END;
 16  /
   DBMS_OUTPUT.put_line ('s=' || s);
                                 *
ERROR at line 14:
ORA-06550: line 14, column 34:
PLS-00201: identifier 'S' must be declared
ORA-06550: line 14, column 4:
PL/SQL: Statement ignored

SQL> SET SERVEROUTPUT ON;
SQL>
SQL> DECLARE
  2     k   NUMBER := 10;
  3     v   NUMBER := 1;
  4  BEGIN
  5     v := v + 21;
  6  /* Inner block */
  7     DECLARE
  8        s   NUMBER := 0;
  9     BEGIN
 10        s := v;
 11        DBMS_OUTPUT.put_line ('s=' || s); -- can't use outside of this Inner block
 12     END;
 13  /* End Inner block */
 14     DBMS_OUTPUT.put_line ('v=' || v);
 15  END;
 16  /
s=22
v=22

PL/SQL procedure successfully completed.

ORA-20000: Unable to analyze Table insufficient privileges or does not exist

C:\Users\rajam>sqlplus edr/welcome

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 6 11:16:52 2019

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> create table student (id number,name varchar2(200));

Table created.

SQL> grant all on student to load_sys;

Grant succeeded.


C:\Users\rajam>sqlplus load_sys/welcome

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 6 11:30:53 2019

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> /* Formatted on 6/6/2019 11:30:14 AM (QP5 v5.256.13226.35510) */
SQL> SET SERVEROUTPUT ON;
SQL>
SQL> CREATE OR REPLACE PROCEDURE p_load
  2  AS
  3  BEGIN
  4     FOR i IN 1 .. 10
  5     LOOP
  6        INSERT INTO edr.student
  7             VALUES (i, 'NAME-' || I);
  8     END LOOP;
  9
 10     COMMIT;
 11
 12     dbms_stats.gather_table_stats('EDR','STUDENT',estimate_percent=>100,cascade=>true,degree=>4);
 13  EXCEPTION
 14     WHEN OTHERS
 15     THEN
 16        DBMS_OUTPUT.put_line ('error=' || SQLERRM);
 17  END;
 18  /

Procedure created.

SQL> exec p_load;
error=ORA-20000: Unable to analyze TABLE "EDR"."STUDENT", insufficient
privileges or does not exist

PL/SQL procedure successfully completed.



C:\Users\rajam>sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 6 11:37:09 2019

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> GRANT ANALYZE ANY TO LOAD_SYS;

Grant succeeded.

SQL>

------------- after analyze grant ----------------------------------------

SQL> exec p_load;

PL/SQL procedure successfully completed.

SQL>

dbms_flashback in Oracle

SQL> select * from t1;

         A        SAL
---------- ----------
         1        300
         2        500
         3        800

SQL> delete from t1;

3 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from t1;

no rows selected

SQL> exec dbms_flashback.enable_at_time(sysdate-2/1440);

PL/SQL procedure successfully completed.

SQL> select * from t1;

         A        SAL
---------- ----------
         1        300
         2        500
         3        800

SQL> exec dbms_flashback.disable;

PL/SQL procedure successfully completed.

RANK and DENSE_RANK in Oracle

SQL> SELECT * FROM EMP;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> SELECT EMPNO,ENAME,DEPTNO,SAL,
  2  RANK()OVER(ORDER BY SAL) AS RANK,
  3  DENSE_RANK()OVER(ORDER BY SAL) AS DENSE_RANK
  4  FROM EMP;

     EMPNO ENAME          DEPTNO        SAL       RANK DENSE_RANK
---------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH              20        800          1          1
      7900 JAMES              30        950          2          2
      7876 ADAMS              20       1100          3          3
      7521 WARD               30       1250          4          4
      7654 MARTIN             30       1250          4          4
      7934 MILLER             10       1300          6          5
      7844 TURNER             30       1500          7          6
      7499 ALLEN              30       1600          8          7
      7782 CLARK              10       2450          9          8
      7698 BLAKE              30       2850         10          9
      7566 JONES              20       2975         11         10
      7788 SCOTT              20       3000         12         11
      7902 FORD               20       3000         12         11
      7839 KING               10       5000         14         12

14 rows selected.

SQL> SELECT EMPNO,ENAME,DEPTNO,SAL,
  2  RANK()OVER(PARTITION BY DEPTNO ORDER BY SAL) AS RANK,
  3  DENSE_RANK()OVER(PARTITION BY DEPTNO ORDER BY SAL) AS DENSE_RANK
  4  FROM EMP ORDER BY DEPTNO;

     EMPNO ENAME          DEPTNO        SAL       RANK DENSE_RANK
---------- ---------- ---------- ---------- ---------- ----------
      7934 MILLER             10       1300          1          1
      7782 CLARK              10       2450          2          2
      7839 KING               10       5000          3          3
      7369 SMITH              20        800          1          1
      7876 ADAMS              20       1100          2          2
      7566 JONES              20       2975          3          3
      7788 SCOTT              20       3000          4          4
      7902 FORD               20       3000          4          4
      7900 JAMES              30        950          1          1
      7654 MARTIN             30       1250          2          2
      7521 WARD               30       1250          2          2
      7844 TURNER             30       1500          4          3
      7499 ALLEN              30       1600          5          4
      7698 BLAKE              30       2850          6          5

14 rows selected.

SQL> SELECT EMPNO,ENAME,DEPTNO,SAL,DENSE_RANK()OVER(ORDER BY SAL DESC) AS DENSE_RANK FROM EMP;

     EMPNO ENAME          DEPTNO        SAL DENSE_RANK
---------- ---------- ---------- ---------- ----------
      7839 KING               10       5000          1
      7902 FORD               20       3000          2
      7788 SCOTT              20       3000          2
      7566 JONES              20       2975          3
      7698 BLAKE              30       2850          4
      7782 CLARK              10       2450          5
      7499 ALLEN              30       1600          6
      7844 TURNER             30       1500          7
      7934 MILLER             10       1300          8
      7521 WARD               30       1250          9
      7654 MARTIN             30       1250          9
      7876 ADAMS              20       1100         10
      7900 JAMES              30        950         11
      7369 SMITH              20        800         12

14 rows selected.

SQL> SELECT * FROM (
  2  SELECT EMPNO,ENAME,DEPTNO,SAL,DENSE_RANK()OVER(ORDER BY SAL DESC) AS RNK FROM EMP
  3  ) WHERE RNK<=5;

     EMPNO ENAME          DEPTNO        SAL        RNK
---------- ---------- ---------- ---------- ----------
      7839 KING               10       5000          1
      7788 SCOTT              20       3000          2
      7902 FORD               20       3000          2
      7566 JONES              20       2975          3
      7698 BLAKE              30       2850          4
      7782 CLARK              10       2450          5

6 rows selected.

SQL>

Queries:

SELECT * FROM EMP;
SELECT EMPNO,ENAME,DEPTNO,SAL,
RANK()OVER(ORDER BY SAL) AS RANK,
DENSE_RANK()OVER(ORDER BY SAL) AS DENSE_RANK
FROM EMP;
SELECT EMPNO,ENAME,DEPTNO,SAL,
RANK()OVER(PARTITION BY DEPTNO ORDER BY SAL) AS RANK,
DENSE_RANK()OVER(PARTITION BY DEPTNO ORDER BY SAL) AS DENSE_RANK
FROM EMP ORDER BY DEPTNO;
SELECT EMPNO,ENAME,DEPTNO,SAL,DENSE_RANK()OVER(ORDER BY SAL DESC) AS DENSE_RANK FROM EMP;
SELECT * FROM (
SELECT EMPNO,ENAME,DEPTNO,SAL,DENSE_RANK()OVER(ORDER BY SAL DESC) AS RNK FROM EMP
) WHERE RNK<=5;

 

ROLLUP and CUBE in Oracle

SQL> set lines 100
SQL> set pages 50
SQL> SELECT * FROM EMP;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> SELECT DEPTNO,SUM(SAL) AS SUM_OF_SAL FROM EMP GROUP BY DEPTNO ORDER BY DEPTNO;

    DEPTNO SUM_OF_SAL
---------- ----------
        10       8750
        20      10875
        30       9400

SQL> SELECT DEPTNO,SUM(SAL) AS SUM_OF_SAL FROM EMP GROUP BY ROLLUP(DEPTNO) ORDER BY DEPTNO;

    DEPTNO SUM_OF_SAL
---------- ----------
        10       8750
        20       10875
        30       9400
                   29025

SQL> SELECT DEPTNO,SUM(SAL) AS SUM_OF_SAL FROM EMP GROUP BY CUBE(DEPTNO) ORDER BY DEPTNO;

    DEPTNO SUM_OF_SAL
---------- ----------
        10       8750
        20       10875
        30       9400
                   29025

SQL> SELECT DEPTNO,JOB,SUM(SAL) AS SUM_OF_SAL FROM EMP GROUP BY DEPTNO,JOB ORDER BY DEPTNO;

    DEPTNO JOB       SUM_OF_SAL
---------- --------- ----------
        10 CLERK               1300
        10 MANAGER         2450
        10 PRESIDENT       5000
        20 ANALYST          6000
        20 CLERK               1900
        20 MANAGER         2975
        30 CLERK                  950
        30 MANAGER         2850
        30 SALESMAN        5600

9 rows selected.

SQL> SELECT DEPTNO,JOB,SUM(SAL) AS SUM_OF_SAL FROM EMP GROUP BY ROLLUP(DEPTNO,JOB) ORDER BY DEPTNO;

    DEPTNO JOB       SUM_OF_SAL
---------- --------- ----------
        10 CLERK               1300
        10 MANAGER         2450
        10 PRESIDENT       5000
        10                              8750
        20 ANALYST         6000
        20 CLERK               1900
        20 MANAGER         2975
        20                             10875
        30 CLERK                  950
        30 MANAGER         2850
        30 SALESMAN        5600
        30                              9400
                                         29025

13 rows selected.

SQL> SELECT DEPTNO,JOB,SUM(SAL) AS SUM_OF_SAL FROM EMP GROUP BY CUBE(DEPTNO,JOB) ORDER BY DEPTNO;

    DEPTNO JOB       SUM_OF_SAL
---------- --------- ----------
        10 CLERK                1300
        10 MANAGER         2450
        10 PRESIDENT       5000
        10                              8750
        20 ANALYST          6000
        20 CLERK                1900
        20 MANAGER         2975
        20                             10875
        30 CLERK                  950
        30 MANAGER         2850
        30 SALESMAN        5600
        30                              9400
           ANALYST          6000
           CLERK                4150
           MANAGER         8275
           PRESIDENT       5000
           SALESMAN        5600
                                       29025

18 rows selected.

SQL> SELECT DEPTNO,JOB,SUM(SAL) AS SUM_OF_SAL ,GROUPING_ID(DEPTNO,JOB) AS SUB_TOTAL FROM EMP
  2  GROUP BY ROLLUP(DEPTNO,JOB) ORDER BY DEPTNO;

    DEPTNO JOB       SUM_OF_SAL  SUB_TOTAL
---------- --------- ---------- ----------
        10 CLERK           1300          0
        10 MANAGER         2450          0
        10 PRESIDENT       5000          0
        10                 8750          1
        20 ANALYST         6000          0
        20 CLERK           1900          0
        20 MANAGER         2975          0
        20                10875          1
        30 CLERK            950          0
        30 MANAGER         2850          0
        30 SALESMAN        5600          0
        30                 9400          1
                          29025          3

13 rows selected.

SQL> SELECT DEPTNO,JOB,SUM(SAL) AS SUM_OF_SAL,
  2  CASE GROUPING_ID(DEPTNO,JOB)
  3  WHEN 1 THEN 'DEPT SUBTOTAL'
  4  WHEN 2 THEN 'JOB SUBTOTAL'
  5  WHEN 3 THEN 'GRAND TOTAL'
  6  END AS SUB_TOTAL
  7  FROM EMP
  8  GROUP BY ROLLUP(DEPTNO,JOB) ORDER BY DEPTNO;

    DEPTNO JOB       SUM_OF_SAL SUB_TOTAL
---------- --------- ---------- -------------
        10 CLERK           1300
        10 MANAGER         2450
        10 PRESIDENT       5000
        10                 8750 DEPT SUBTOTAL
        20 ANALYST         6000
        20 CLERK           1900
        20 MANAGER         2975
        20                10875 DEPT SUBTOTAL
        30 CLERK            950
        30 MANAGER         2850
        30 SALESMAN        5600
        30                 9400 DEPT SUBTOTAL
                          29025 GRAND TOTAL

13 rows selected.

SQL> SELECT DEPTNO,JOB,SUM(SAL) AS SUM_OF_SAL,
  2  CASE GROUPING_ID(DEPTNO,JOB)
  3  WHEN 1 THEN 'DEPT SUBTOTAL'
  4  WHEN 2 THEN 'JOB SUBTOTAL'
  5  WHEN 3 THEN 'GRAND TOTAL'
  6  END AS SUB_TOTAL
  7  FROM EMP
  8  GROUP BY CUBE(DEPTNO,JOB) ORDER BY DEPTNO;

    DEPTNO JOB       SUM_OF_SAL SUB_TOTAL
---------- --------- ---------- -------------
        10 CLERK           1300
        10 MANAGER         2450
        10 PRESIDENT       5000
        10                 8750 DEPT SUBTOTAL
        20 ANALYST         6000
        20 CLERK           1900
        20 MANAGER         2975
        20                10875 DEPT SUBTOTAL
        30 CLERK            950
        30 MANAGER         2850
        30 SALESMAN        5600
        30                 9400 DEPT SUBTOTAL
           ANALYST         6000 JOB SUBTOTAL
           CLERK           4150 JOB SUBTOTAL
           MANAGER         8275 JOB SUBTOTAL
           PRESIDENT       5000 JOB SUBTOTAL
           SALESMAN        5600 JOB SUBTOTAL
                          29025 GRAND TOTAL

18 rows selected.

SQL>

Queries:

SELECT * FROM EMP;
SELECT DEPTNO,SUM(SAL) AS SUM_OF_SAL FROM EMP GROUP BY DEPTNO ORDER BY DEPTNO;
SELECT DEPTNO,SUM(SAL) AS SUM_OF_SAL FROM EMP GROUP BY ROLLUP(DEPTNO) ORDER BY DEPTNO;
SELECT DEPTNO,SUM(SAL) AS SUM_OF_SAL FROM EMP GROUP BY CUBE(DEPTNO) ORDER BY DEPTNO;
SELECT DEPTNO,JOB,SUM(SAL) AS SUM_OF_SAL FROM EMP GROUP BY DEPTNO,JOB ORDER BY DEPTNO;
SELECT DEPTNO,JOB,SUM(SAL) AS SUM_OF_SAL FROM EMP GROUP BY ROLLUP(DEPTNO,JOB) ORDER BY DEPTNO;
SELECT DEPTNO,JOB,SUM(SAL) AS SUM_OF_SAL FROM EMP GROUP BY CUBE(DEPTNO,JOB) ORDER BY DEPTNO;
SELECT DEPTNO,JOB,SUM(SAL) AS SUM_OF_SAL ,GROUPING_ID(DEPTNO,JOB) AS SUB_TOTAL FROM EMP
GROUP BY ROLLUP(DEPTNO,JOB) ORDER BY DEPTNO;
SELECT DEPTNO,JOB,SUM(SAL) AS SUM_OF_SAL,
CASE GROUPING_ID(DEPTNO,JOB)
WHEN 1 THEN 'DEPT SUBTOTAL'
WHEN 2 THEN 'JOB SUBTOTAL'
WHEN 3 THEN 'GRAND TOTAL'
END AS SUB_TOTAL
FROM EMP
GROUP BY ROLLUP(DEPTNO,JOB) ORDER BY DEPTNO;
SELECT DEPTNO,JOB,SUM(SAL) AS SUM_OF_SAL,
CASE GROUPING_ID(DEPTNO,JOB)
WHEN 1 THEN 'DEPT SUBTOTAL'
WHEN 2 THEN 'JOB SUBTOTAL'
WHEN 3 THEN 'GRAND TOTAL'
END AS SUB_TOTAL
FROM EMP
GROUP BY CUBE(DEPTNO,JOB) ORDER BY DEPTNO;