A) Analytical Functions 

It computes on aggregate value based on a group of rows.

Analytical Functions / Clauses are:-

a) RANK():- Rank function calculate the rank of a value in a group of values.

b) ROW_NUMBER():- It is used to give unique number to rows.

c) DENSE_RANK():- It computes the rank of a row in an ordered group of rows.

d) NTILE():- It divides an ordered data set into number of buckets indicated by expression.

e) FIRST:- It can be used to return the first value FROM an ordered sequence.

f) LAST:- It can be used to return the last value FROM an ordered sequence.

g) LEAD ():- It returns offset(incrementally increased) value of an argument column.

h) LAG ():- This function is used to access data FROM a previous row.

EXAMPLES:-

Create following table.

1. EMPLOYEES(eid,ename,salary,dname)

SQL> CREATE TABLE EMPLOYEES (

eno NUMERIC(3), ename VARCHAR(10), esal NUMERIC(6),

dname varchar(10)

);

Table created.

• SQL>INSERT INTO EMPLOYEES VALUES(200,’Vinay’,20000,’IT’);
  • 1 row created.
  • SQL>INSERT INTO EMPLOYEES VALUES(201,’Anand’,30000,’IT’);
  • 1 row created.
  • SQL>INSERT INTO EMPLOYEES VALUES(202,’Anil’,25000,’Marketing’);
  • 1 row created.
  • SQL>INSERT INTO EMPLOYEES
  • VALUES(203,’Yogita’,20000,’Marketing’);
  • 1 row created.
  • SQL>INSERT INTO EMPLOYEES
  • VALUES(204,’Priyanka’,20000,’Management’);
  • 1 row created.
  • SQL>INSERT INTO EMPLOYEES
  • VALUES(205,’Likhita’,25000,’Management’);
  • 1 row created.
  • SQL>INSERT INTO EMPLOYEES
  • VALUES(206,’Ankit’,30000,’Management’);
  • 1 row created.
  • SQL>INSERT INTO EMPLOYEES
  • VALUES(207,’Aniket’,35000,’Accountant’);
  • 1 row created.
  • SQL> SELECT * FROM EMPLOYEES;

Perform the following queries on above table.

1) Display employees information FROM Employee table and give unique number to each row.

SQL> SELECT eno,ename,esal,dname,ROW_NUMBER() OVER(ORDER BY ENO)”ID”

FROM Employees;

2) Display employees information and give unique number to each row in ascending order of salary.

SQL> SELECT eno,ename,esal,dname,ROW_NUMBER() OVER(ORDER BY ESAL)”ID”

FROM Employees;

3) Assign the ranks to employees in ascending order of salary.

SQL> SELECT eno,ename,esal,dname,RANK() OVER(ORDER BY ESAL)”Rank”

FROM Employees;

4) Assign the ranks to employees in descending order of salary.

SQL> SELECT eno,ename,esal,dname,RANK() OVER(ORDER BY ESAL DESC)”Rank”

FROM Employees;

5) Assign the ranks to employees in ascending order of salary using dense rank and point out the difference and display record in descending order of salary.

SQL> SELECT eno,ename,esal,dname,DENSE_RANK() OVER(ORDER BY ESAL)”Dense Rank”

FROM Employees ORDER BY ESAL DESC;

6) Write a Query for finding highest and lowest salary of each department.

SQL> SELECT deptid,MIN(salary) KEEP(DENSE_RANK FIRST ORDER BY Salary)”Lowest Sal”, MAX(salary) KEEP(DENSE_RANK LAST ORDER BY Salary)”Highest Sal”

FROM Employees GROUP BY deptid;

7) Write a Query to find information of employee who were joined first in each department.

SQL> SELECT deptid,MIN(DOJ) KEEP(DENSE_RANK FIRST ORDER BY Salary)”Hired First”

FROM Employees GROUP BY deptid;

8) Write a Query which returns a salary FROM previous row name it as Sal_Prev. Calculate the difference between salary of current row and previous row.

SQL> SELECT empid,fname,salary,LAG(Salary,1,0) OVER(ORDER BY Salary)”SAL_PREV”, Salary-LAG(Salary,1,0) OVER(ORDER BY Salary)”SAL_DIFF”

FROM Employees;

9) Write a Query which returns a salary FROM next row name it as Sal_Next. Calculate the difference between salary of current row and following row.

SQL> SELECT empid,fname,salary,LEAD(Salary,1,0) OVER(ORDER BY Salary)”SAL_NEXT”, Salary-LEAD(Salary,1,0) OVER(ORDER BY Salary)”SAL_DIFF”

FROM Employees;

B) ROLLUP AND CUBES 

Description:

ROLLUP is an extension of GROUP BY Clause. This option allows us to include extra rows that represent the subtotal and grand-total which are known as super aggregate row.

CUBE returns row containing a subtotal for all combination of columns.

ROLLUP EXAMPLES:-

Create following table.

CREATE TABLE sales(

time number(4), region varchar2(15), dept varchar2(20), profit number(10,2)

);

INSERT INTO sales values(1996,’central’,’pen-sales’,75000); INSERT INTO sales values(1996,’central’,’book_sales’,74000); INSERT INTO sales values(1996,’east’,’pen_sales’,89000); INSERT INTO sales values(1997,’central’,’pen_sales’,82000); INSERT INTO sales values(1997,’central’,’pen-sales’,75000); INSERT INTO sales values(1997,’east’,’book_sales’,74000); INSERT INTO sales values(1997,’east’,’pen_sales’,89000); INSERT INTO sales values(1997,’west’,’book_sales’,115000);

1) Find total profit department wise.

SQL> SELECT dept,SUM(profit)”Profit” FROM sales

GROUP BY dept;

2) Find total profit department wise along with Grand Total.

SQL> SELECT dept,SUM(profit)”profit” FROM sales

GROUP BY ROLLUP(dept);

3) Find the total profit time and region wise.

SQL> SELECT time,region,SUM(profit)”profit” FROM sales

GROUP BY time,region;

4) Find the total profit time and region wise along with Time wise total and Grand Total.

SQL> SELECT time,region,SUM(profit)”profit” FROM sales

GROUP BY ROLLUP(time,region);

5) Find the total profit, time,region wise along with region wise total and grand total.

SQL> SELECT time,region,SUM(profit)”profit” FROM sales

GROUP BY ROLLUP(region,time);

6) Find the total profit, time,region wise along with time wise only.

SQL> SELECT time,region,SUM(profit)”profit” FROM sales

GROUP BY time, ROLLUP(region);

7) Find the total profit, region, time wise along with region wise only.

SQL> SELECT time,region,SUM(profit)”profit” FROM sales

GROUP BY region, ROLLUP(time);

8) Find the total profit, time, region, dept wise along with time wise total, time & dept wise total and grand total.

SQL> SELECT dept,time,region,SUM(profit)”profit” FROM sales

GROUP BY ROLLUP(time,dept,region);

9) Find the total profit, time, region, department wise along with department wise total, department & region wise total and grand total.

SQL> SELECT time,dept,region,SUM(profit)”profit” FROM sales

GROUP BY ROLLUP(dept,region,time);

10) Find the total profit, time, region, department wise along with department wise total, department & time wise total and grand total.

SQL> SELECT time,dept,region,SUM(profit)”profit” FROM sales

GROUP BY ROLLUP(dept,time,region);

CUBES EXAMPLES

1) Find out total sales {time,region,dept}wise,{time,region}wise, {time,dept}wise, {region,dept}wise,

{time}wise, {dept}wise,{region}wise total sales irrespective of time, region and department.

SQL> SELECT time,dept,region, SUM(profit)as profit FROM sales

GROUP BY CUBE(time,region,dept);

2) Find out total sales {time,region,dept}wise,{time,region}wise, {time,dept}wise total sales irrespective of region and department.

SQL> SELECT time,dept,region, SUM(profit)as profit FROM sales

GROUP BY time, CUBE(region,dept);

C) WINDOWING FUNCTIONS 

Description:

Windowing Function operates on a set of rows and return a single value for each row FROM the underline query.

The ‘OVER’ Clause defines the partitioning and ordering of row for the function.

The ‘OVER’ accepts the following 3 arguments to define a window for the function to operate on:

- Order By

- Partition By

- Rows OR Range Clause

Examples:

Create following table CREATE TABLE Employee1(e_id number(4),

first_name varchar2(15), last_name varchar2(15), dept_id number(3), hire_date date,

salary number(10,2)

);

INSERT INTO Employee1 values(101,’Steven’,’Patil’,10,’29-Jan-2001’,20000); INSERT INTO Employee1 values(107,’Michael’,’Singh’,10,’19-Jan-2001’,30000); INSERT INTO Employee1 values(106,’Peter’,’Patil’,10,’16-Feb-2001’,50000); INSERT INTO Employee1 values(102,’James’,’Patil’,10,’10-Mar-2001’,200000); INSERT INTO Employee1 values(103,’Alexis’,’Patil’,10,’03-Mar-2001’,70000); INSERT INTO Employee1 values(104,’David’,’Patil’,10,’25-Jun-2001’,40000); INSERT INTO Employee1 values(115,’Prashanti’,’Patil’,20,’16-Jul-2001’,6000); INSERT INTO Employee1 values(120,’Nagraju’,’Patil’,40,’14-Jun-2001’,10000); INSERT INTO Employee1 values(116,’Nagesh’,’Patil’,20,’29-Jan-2001’,2500); INSERT INTO Employee1 values(118,’Sreenivas’,’Patil’,20,’29-Jan-2001’,20000); INSERT INTO Employee1 values(119,’Ashok’,’Singh’,30,’19-Jan-2001’,300000); INSERT INTO Employee1 values(114,’Raghav’,’Patil’,80,’16-Feb-2001’,5000); INSERT INTO Employee1 values(113,’Chandana’,’Patil’,50,’10-Mar-2001’,20000);

1) Display empno,ename,dept,sal,cumulative SUM of salary.

SQL> SELECT e_id,first_name,dept_id,salary,SUM(salary) OVER (order by e_id)”Cumulative_Sal”

FROM Employee1;

2) Display empno,ename,dept,sal,cumulative SUM of salaries of all preceding rows dept wise.

SQL> SELECT e_id,first_name,dept_id,salary,SUM(salary)

OVER (partition by dept_id order by salary Rows unbounded preceding)”Cumulative_Sal” FROM Employee1;

3) Display empno,ename,dept,sal,cumulative SUM of salaries of current and previous 2 rows.

SQL> SELECT e_id,first_name,dept_id,salary,SUM(salary)

OVER (order by e_id Rows between 2 preceding and current row)”Cumulative_Sal” FROM Employee1;

4) Display empno,ename,dept,sal,cumulative SUM of salaries of previous 3 rows and 1 next row dept wise.

SQL> SELECT e_id,first_name,dept_id,salary,SUM(salary)

OVER (order by dept_id Rows between 3 preceding and 1 following)”Cumulative_Sal” FROM Employee1;

5) Display empno,ename,dept,sal,cumulative SUM of salaries of previous 2 rows and 2 next rows.

SQL> SELECT e_id,first_name,dept_id,salary,SUM(salary)

OVER (order by e_id Rows between 2 preceding and 2 following)”Cumulative_Sal” FROM Employee1;

6) Display last 7 days hire_date employee.

SQL> SELECT e_id,first_name,hire_date,count(e_id)

OVER(order by hire_date range interval ‘15’ day preceding)”Days” FROM Employee1;

7) write a query for finding average of sal, if sal is less than 20000, then consider it as 20000 for finding average.

SQL> SELECT AVG(case when salary<20000 then 20000 else salary end)”Average” FROM Employee1;

8) write a query for finding average of sal, if sal is greater than 20000, then consider it as 20000 for finding average.

SQL> SELECT first_name,dept_id,AVG(case when salary>20000 then 20000 else 0 end) OVER(partition by dept_id)”Average” FROM Employee1;