SQL SET Operations

SQL SET Operations enables to combine the results from two or more SELECT statements.The result of each SELECT statement can be treated as a set,and SQL set operations can be applied on those sets to arrive at a final result.All set operators have equal precedence.If a SQL statement contains multiple set operators,Oracle evaluates them from the left to right if no parentheses explicitly specify another order.

Types of set operations:

• UNION ALL
• UNION
• MINUS
• INTERSECT

UNION ALL
Combines the results of two SELECT statements into one result set.

EX:select * from sales2000 UNION ALL select * from sales2001 ;

UNION
Combines the results of two SELECT statements into one result set, and then eliminates any duplicate rows from that result set.

EX:select * from sales2000 UNION select * from sales2001;

MINUS
Takes the result set of one SELECT statement, and removes those rows that are also returned by a second SELECT statement.

EX:select * from sales2000 MINUS select * from sales2001;

INTERSECT
Returns only those rows that are returned by each of two SELECT statements

EX:select * from sales2000 INTERSECT select * FROM sales2001;

SQL GROUP BY Functions

GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.
EX:select customer,sum(order) from order group by customer;
This gives the sum of all the orders customer wise.

ROLLUP enables a SQL statement to calculate multiple levels of subtotals across a specified group of dimensions and also calculates a grand total.ROLLUP is a simple extension to the GROUP BY clause.
EX:select deptno,job,count(*),sum(sal) from emp GROUP BY ROLLUP(deptno,job);

CUBE is used with the GROUP BY only.CUBE creates a subtotal of all possible combinations of the set of column in its argument.For GROUP BY CUBE,there will be 2^n sets of totals,where n is the number of columns in the GROUP BY.
EX:select deptno,job,count(*) from emp GROUP BY CUBE(deptno,job);
In the above example,GROUP BY CUBE clause produces a subtotal line for each department number, and calculates a total for each job in each department,a grand total at the end of the query,and a total for each job.

GROUPING returns 1 when it encounters a NULL value created by a ROLLUP or CUBE operation.GROUPING = 0 is a stored NULL not created by ROLLUP or CUBE and GROUPING = 1 is a NULL value created by ROLLUP or CUBE.
EX:select deptno,job,count(*),sum(sal),GROUPING(Job) FROM emp GROUP BY ROLL UP(deptno,job);

GROUP BY GROUPING SETS is an alternative to GROUP BY CUBE.It lets to specify just the combinations of totals you're interested in.It's more efficient because it doesn't have to compute aggregations that are not needed.
Syntax:GROUP BY GROUPING SETS ( (list), (list) ... )where (list) is a list of columns in parentheses,the combination of which is to generate a total.To add a grand total,add (NULL) as one of the grouping sets.

Analytic Functions and Miscellaneous

RANK and DENSE_RANK both provide rank to the records based on some column value or expression.In case of a tie of 2 records at position N,RANK declares 2 positions N and skips position N+1 and gives position N+2 to the next record.While DENSE_RANK declares 2 positions N but does not skip position N+1. Dense_rank avoids skipping ranks when there is a tie.
ROW_NUMBER( ) gives a running serial number to a partition of records.It avoids the duplicate ranks.

EX:For the given sal as below,basing on ascending order of salaries,rank,dense_rank and row_number are as follows:

SAL RANK DENSE_RANK ROW_NUMBER
1000 1 1 1
3000 2 2 2
3000 2 2 3
6000 4 3 4
9000 5 4 5


LEAD has the ability to compute an expression on the next rows (rows which are going to come after the current row) and return the value to the current row.
LEAD provides access to a row at a given physical offset beyond that position.
EX:SELECT ename,hire_date,LEAD(hire_date, 1) OVER (ORDER BY hire_date) from emp;
Output gives the hire date of the employee hired just after,for each employee in emp table.

LAG provides access to a row at a given physical offset prior to that position.
EX:select ename,hire_date,salary,LAG(salary, 1, 0) OVER (ORDER BY hire_date) from emp;
Output gives the salary of the employee hired just before,for each employee in emp table.

FIRST_VALUE analytic function picks the first record from the partition after doing the ORDER BY.It returns the first value in an ordered set of values.If the first value in the set is null, then the function returns NULL unless you specify IGNORE NULLS.
EX:SELECT ename,salary,FIRST_VALUE(last_name) OVER (ORDER BY salary ASC) FROM emp;
Output gives the name of the employee with the lowest salary for all the employees.

LAST_VALUE function acts on the last record of the partition. It returns the last value in an ordered set of values. If the last value in the set is null, then the function returns NULL unless you specify IGNORE NULLS.
EX:select ename,salary,hire_date,LAST_VALUE(hire_date) OVER (ORDER BY salary) from emp;
Output gives the hire date of the employee earning the highest salary,for all employees in emp table.

SQL Aggregate Functions

Most commonly used Aggregate Functions:

SUM returns the sum of values of expr.
EX:select SUM(sal) from emp;Output gives the sum of all salaries in emp table.

AVG returns average value of expr.
EX:select AVG(salary) from employees;Output gives average of all salaries.

COUNT returns the number of rows returned by the query.
EX:select COUNT(*) from emp;Output returns the number of rows in emp table.

MAX returns maximum value of expr.
EX:select MAX(salary) from emp;Output gives the maximum salary in emp table.

MIN returns minimum value of expr.
EX:select MIN(salary) from emp;Output gives the minimum salary in emp table.

SQL Miscellaneous Functions

TO_DATE converts char datatype to a value of DATE datatype.

TO_CHAR converts to database character set.

DECODE compares expr to each search value one by one.If expr is equal to a search, then Oracle Database returns the corresponding result.If no match is found, then Oracle returns default.If default is omitted, then Oracle returns null.
EX:Select decode(comm,null,sal,comm) from emp;
In the above example,it compares the comm column of emp table and if its null,it returns sal else returns comm.

GREATEST returns the greatest of the list of one or more expressions.Oracle Database uses the first expr to determine the return type.If the first expr is not numeric, then each expr after the first is implicitly converted to the datatype of the first expr before the comparison.If the first expr is numeric, then Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype before the comparison, and returns that datatype.

LEAST returns the least of the list of exprs. All exprs after the first are implicitly converted to the datatype of the first expr before the comparison.

NVL allows to replace null with a string in the results of a query.If expr1 is null,then NVL returns expr2.If expr1 is not null,then NVL returns expr1.
EX:Select nvl(comm,sal) from emp;
In the above example,it returns sal for all those whose comm is null.If comm is not null,NVL returns comm.

NVL2 allows to determine the value returned by a query based on whether a specified expression is null or not null. If expr1 is not null,then NVL2 returns expr2. If expr1 is null,then NVL2 returns expr3.
EX:Select nvl(comm,comm,1000) from emp;
In the above example,if the comm is not null,NVL2 returns comm.If comm is null,NVL2 returns 1000.

USER returns the name of the session user with the VARCHAR2 datatype.
EX:select user,uid from dual;

ORDER BY clause is used to sort rows returned by a query.By default,Order by clause orders in ascending order.If it has to sort in descencing order,DESC command has to be used.
EX:select ename,sal from emp order by sal desc;

EX:select ename,sal from emp order by sal;

Date and Time related Functions

ADD_MONTHS returns the date adding given integer months to the date.The return type is always DATE.

CURRENT_DATE returns the current date in the session time zone.

CURRENT_TIMESTAMP returns the current date and time in the session time zone.The time zone offset reflects the current local time of the SQL session.

MONTHS_BETWEEN returns number of months between 2 dates date1 and date2. If date1 is later than date2, then the result is positive. If date1 is earlier than date2, then the result is negative.

NEXT_DAY returns the date of the first weekday named by char that is later than the date date. The return type is always DATE.
EX:SELECT NEXT_DAY('02-APR-2001','FRIDAY') FROM DUAL;Output is 09-APR-2001.
Above example returns the date of the next friday after April 2, 2001.

SYSDATE returns the current date and time set for the operating system on which the database resides.

Character Functions Returning Number Values

Character Functions Returning Number Values:

ASCII returns the decimal representation of the char given.
EX:SELECT ASCII('Q') FROM DUAL;Output is 81

INSTR function search for a given substring in a string.It returns an integer indicating the position of the first occurence of the character in string.
EX:SELECT INSTR('My Home Is Good','o',4,2) FROM DUAL;Output is 9.

In the above example searches the string 'My Home is good',beginning with the third character,for the string "O". It returns the position in 'My Home is good' at which the second occurrence of "o" begins.

LENGTH functions return the length of the string given.
EX:SELECT LENGTH('Home') from dual;Output is 4.