SQL- Data Query Language

DQL SELECT

Here is the basic syntax for a SELECT query

SELECT [ col1,col2.... or * ] 
FROM table_name;

Now let us see a few examples to understand SQL. We will consider an employee table - emp that contains all the employee data for an organization. Without knowing much about what are the data in store in this table, let us start with some SQL examples. Let's try to display all the columns and records in the Employee table:

SELECT * 
FROM emp;

Example : List the Empno and Ename from Employee table

SELECT empno AS Employee Number, ename AS Employee Name 
FROM emp;

WHERE Clause

The WHERE Clause is used to specify the criteria of selection.

Example: List all employees who belong to department number 40

SELECT empno, ename, deptno 
FROM emp 
WHERE deptno = 40;

DISTINCT Clause

The DISTINCT Clause is used to eliminate duplicate values from the dataset and to display only the unique values.

Example: To list the unique jobs in the employee table

SELECT DISTINCT job FROM emp;

Operators

• Arithmetic

+,-,*,/

• Relational

=,<,>,<=,>=,<>,!=,^=

• <li> Logical or Boolean

AND,OR,NOT

• Set Operators

UNION,UNION ALL,INTERSECT, MINUS

• Others

IN, BETWEEN, LIKE , IS NULL

Arithmetic Operator

SELECT sal, sal * 0.5 AS Bonus 
FROM emp;

SELECT sysdate - 1 AS Yesterday, sysdate Today, sysdate + 1 Tomorrow 
FROM DUAL;

Relational Operator

Example: List the employee name whose employee number is 7900

SELECT ename 
FROM emp 
WHERE empno = 7900;

Example: List the employees whose hire date is before 31-DEC-84

SELECT empno, ename, hiredate, deptno 
FROM emp 
WHERE Hiredate < = 31-DEC-84;

Logical Operators

Example: List the employees who are getting salary in the range of 1500 and 3000

SELECT empno, ename, sal 
FROM emp 
WHERE sal >= 1500 AND sal <= 3000;

Example: List the employee number and names of department 10, department 20

SELECT empno, ename, sal FROM emp WHERE deptno = 10 OR deptno = 20;

Set Operators

Each of these operations combines the results of two select statements into a single result set.
**data types should be same of both the tables

• The UNION operator returns the records retrieved by either of the queries
• By default, the UNION operator eliminates duplicate records
  
• To retain duplicates, we use <i>UNION ALL</i> instead of UNION
  
• The INTERSECT operator returns those rows which are retrieved by both the queries
  
• The MINUS operator returns all rows retrieved by the first query but not by the second query
  

Example: List distinct employees in Kolkata and Mumbai location

SELECT * FROM emp_kol
UNION
SELECT * FROM emp_mum;

Note: The structure of emp_kol and emp_mum table must be exactly the same.

Example: List <b>all</b> employees in Kolkata and Mumbai location

SELECT * FROM emp_kol
UNION ALL
SELECT * FROM emp_mum;

Example: List employees having entry in both Kolkata and Mumbai location

SELECT * FROM emp_kol
INTERSECT
SELECT * FROM emp_mum;

Example: List employees having entry in Kolkata but not in Mumbai location

SELECT * FROM emp_kol
MINUS
SELECT * FROM emp_mum;

Other Operators

Examples:

SELECT empno, ename, deptno FROM emp WHERE sal BETWEEN 1500 AND 2500;

SELECT empno, ename, sal FROM emp WHERE deptno IN ( 10, 20 );

SELECT empno, ename FROM emp WHERE ename LIKE S%;

SELECT empno, ename,comm FROM emp WHERE comm IS NULL;

*NULL means absence of information

Order By clause

Sorts records using specified criteria in ascending or descending order

select * from table_name ORDER BY col1 ASC, col2 DESC;

Examples:

SELECT * FROM employees ORDER BY Last_Name ASC; 

SELECT empno,ename,sal FROM emp ORDER BY ename;

SELECT ename FROM emp ORDER BY ename DESC;

SELECT job,ename FROM emp ORDER BY job,ename;

SELECT job,ename FROM emp ORDER BY job, ename DESC;

SELECT ename,job FROM emp ORDER BY 1 DESC;

Single Row Functions

• Manipulate data items
• Accept arguments and return one value
  
• Act on each row returned
  
• Return one result per row
  
• May modify the data type
  
• Can be nested
  

Single Row Function Types

• Numeric
• Character
  
• Date
  
• Conversion
  
• Others
  

Single Row Functions: Numeric

NVL: NVL will convert the NULL value to the required value

SELECT sal + NVL( comm, 0 ) FROM emp;

ABS: ABS(n): Returns the absolute value of n

SELECT ABS( -10 ) FROM dual;

o/p: 10

CEIL: CEIL(n): The ceil function returns the largest integer value that is greater than or equal to a number

SELECT CEIL( 23.2 ) FROM dual;

o/p: 24

FLOOR: FLOOR(n): The floor function returns the smallest integer value that is greater than or equal to a number

SELECT FLOOR( 56.99 ) FROM dual;

o/p: 56

MOD: MOD(m,n): Returns remainder of division

SELECT MOD( 5, 2 ) FROM dual;

o/p: 1

POWER: POWER(m,n): The power function returns m raise to nth power

SELECT POWER( 3, 2 ) FROM dual:

o/p: 9

ROUND: ROUND(m,n): Rounds value to specified decimal

SELECT ROUND( 52.5 ) FROM dual;

o/p: 53

SQRT: SQRT(n): Returns square root of n

SELECT SQRT( 4 ) FROM dual;

o/p: 2

TRUNC: TRUNC(m,n): Truncates value to specified decimal

SELECT TRUNC( 56.223, 1 ) FROM dual;

o/p : 56.2

SIGN: SIGN(n): The sign function returns a value indicating the sign of a number

SELECT SIGN( -10 ), SIGN( 0 ), SIGN( 10 ) FROM dual;

o/p -1 0 1

Single Row Functions: Character

CONCAT: Concatenates two character literals

SELECT CONCAT( ename, job ) FROM emp;

UPPER, LOWER, INITCAP: Convert case of character literals

SELECT UPPER( ename ), LOWER( ename ), INITCAP( ename ) FROM emp;

RPAD, LPAD: Padding spaces/character's before/after character literals

SELECT LPAD( ename, 30, '*' ), RPAD( ename, 30, '-' ) FROM emp;

LTRIM, RTRIM: Trim leading/trailing blank spaces/characters

SELECT LTRIM( ename ), RTRIM( ename ) FROM emp;

LENGTH: Determine the length or number of characters in a string literal- LENGTH( STRING )

SELECT LENGTH ( ename ) FROM emp;

INSTR: Search characters in a string literal- INSTR( STRING, search strng, start position )

SELECT INSTR( ename, 'S', 1 ) FROM emp;

SUBSTR: Returns a portion of the string literal- SUBSTR( STRING, start pos, no. of characters )

SELECT SUBSTR( ename, 1, 3 ) FROM emp;

SOUNDEX: SOUNDEX(string)

SELECT ename FROM emp WHERE SOUNDEX( ename ) = SOUNDEX( 'SMEETH' );

Single Row Functions: Date

TO_CHAR: Converts date into a required character format

SELECT TO_CHAR( hiredate, 'day-month-year' ) FROM EMP;

ADD_MONTHS: Adds months to a date

SELECT ADD_MONTHS( hiredate, 11 ) FROM emp;

MONTHS_BETWEEN: Finds the number of months between two dates

SELECT MONTHS_BETWEEN( sysdate, hiredate ) FROM emp;

LAST_DAY: Finds the last date of the month for the input date

SELECT LAST_DAY( hiredate ) FROM emp;

NEXT_DAY: Finds the date of next day after a specific date

SELECT NEXT_DAY( hiredate, 'friday' ) from emp;

Conversion Functions

TO_CHAR(X): Converts the value of X to a character or converts a date to a character

SELECT TO_CHAR( 1981 ) FROM dual;

SELECT TO_CHAR( hiredate, 'YYYYMMDD' ) FROM dual;

TO_NUMBER(X): Converts non-numeric value X to a number

SELECT TO_NUMBER( '1221' ) FROM dual;

TO_DATE(X,[Y]): Converts non-date value X to a date using the format specified by Y

SELECT TO_DATE( '12-FEB-2007' ) FROM dual;

Aggregate Functions

SUM: Returns the sum of the column values provided as parameters to the function

SELECT SUM( sal ) "Total Sal" FROM emp WHERE deptno = 20;

AVG: Returns average value of the column values provided as parameters to the function

SELECT AVG( Sal ) "Average Sal" FROM emp;

COUNT: Returns the no. of rows for the specified column

SELECT COUNT( * ) "Total Record Count" FROM emp;

-count( 1 ): faster;

-count( comm )- will omit records with NULL.

-count( * ): slow from performance standpoint; can use COUNT( 1 ) instead.

SELECT COUNT( empno ) "Total Employees" FROM emp;

MIN: Returns minimum value of expression

SELECT MIN( sal ) "Minimum Salary" FROM emp;

MAX: Returns maximum value of expression

SELECT MAX( sal ) "Maximum Salary" FROM emp;

Note: Group functions ignore NULL values by default.

Group by and Having Clause

The GROUP BY clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns

HAVING is very similar to WHERE except the statements within it are of an aggregate nature

SELECT deptno, COUNT( 1 )
FROM emp
GROUP BY deptno;

SELECT deptno, count( 1 )
FROM emp
GROUP BY deptno 
HAVING COUNT( 1 ) > 1;

Miscellaneous Functions

decode: Facilitates conditional inquiries by doing the work of a CASE or IF-THEN-ELSE statement. Syntax:

DECODE(col|expression, search1, result1 
[, search2, result2,...,]
[, default])

decode compares <exp/col> to the search and if exp_x matches, returns result_x. If not, returns default, or, if default is left out, null.

SELECT empno, ename,
DECODE( deptno, 10, 'Ten', 20, 'Twenty', 'Other' ) as department
FROM emp;

SELECT empno, ename,
DECODE( sign( sal - 2000 ), 1, 'Above target', -1, 'Below target', 'On Target' )  
FROM emp;

Case: A CASE expression selects a result and returns it. To select the result, the CASE expression uses a selector, an expression whose value is used to select one of several alternatives. Allows a more compact notation and some elimination of repetition with respect to what otherwise would be expressed with an IF construct. Syntax:

case n
when 1 then Action1; 
when 2 then Action2;
when 3 then Action3; 
else ActionOther;
end case;

SELECT 
COUNT( CASE WHEN sal < 2000 THEN 1 ELSE NULL END ) count1,
COUNT( CASE WHEN sal BETWEEN 2001 AND 4000 THEN 1 ELSE NULL END ) count2,
COUNT( CASE WHEN sal > 4000 THEN 1 ELSE NULL END ) count3
FROM emp;

OLAP(Online Analytical processing) Features in Oracle

ROLLUP: is used to produce subtotals at any level of aggregation needed. These subtotals then "roll up" into a grand total. e.g.

SELECT deptno, job, SUM( sal ) AS salary
FROM emp
GROUP BY ROLLUP( deptno, job );

Note: NULL values in the output of rollup operations typically mean that the row contains subtotal or grand total information. Output of the ROLLUP function:

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

CUBE: The cube operation calculates all levels of subtotals on horizontal lines across spreadsheets of output and create cross-tab summaries on multiple vertical columns in those spreadsheets. e.g.

SELECT deptno,job,SUM( sal ) AS salary
FROM emp
GROUP BY CUBE( deptno, job );

Output of the CUBE Function:

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

If we require data from more than one table in the database, a join is used.

Table Joins

Types of Join

• EQUI
• NON-EQUI
  
• OUTER
  
• SELF
  

EQUI JOIN

• The columns compared by = operator are called join columns and the join operation is called an EQUI JOIN
• ANSI/ISO syntax for EQUI join
  

SELECT ename, deptno, dname
FROM emp NATURAL JOIN dept;

For Cartesian product

SELECT col1, col2
FROM table1 CROSS JOIN table2;

SELF JOIN

• It is possible using table labels (aliases) to join a table to itself, as if they were two separate tables
• This allows rows in a table to be joined to rows in the same table
  

Example: List the employees working in the same department as that of employee 7900

SELECT b.ename
FROM emp a, emp b
WHERE a.deptno = b.deptno
AND a.empno = 7900;

OUTER JOIN

• If a row does not satisfy a join condition, then the row will not appear in the query result
• The missing row(s) can be returned if an outer join operator is used in the join condition
  
• The operator is plus sign enclosed in parentheses (+), and is placed on the side of the join(table) which is deficient in information
  

ANSI/ISO syntax for outer join

SELECT e. ename, d.dname
FROM emp e LEFT OUTER JOIN dept d
ON d.deptno = e.deptno;
SELECT e. ename, d.dname
FROM emp e RIGHT OUTER JOIN dept d
ON d.deptno = e.deptno;
SELECT e. ename, d.dname
FROM emp e FULL OUTER JOIN dept d
ON d.deptno = e.deptno;

Sub-queries

• A sub-query is a form of an SQL statement that appears inside another SQL statement
• Also termed as nested query
  
• The statement containing a sub-query is called a parent statement. The parent statement uses the rows returned by the sub-query.

Types of Sub-queries

Single-row Sub-queries

SELECT ename FROM emp
WHERE deptno = (
SELECT deptno FROM dept
WHERE dname = 'ACCOUNTING' );

Multi-row Sub-queries

SELECT ename FROM emp
WHERE deptno IN (
SELECT deptno FROM dept );

Types of Sub-queries Using ANY and ALL

ANY: the condition evaluates to true, if there exist at least one row selected by the sub-query for which the comparison holds.

SELECT * FROM emp WHERE sal >= ANY
( SELECT sal FROM emp WHERE deptno = 30 )
and deptno = 10;

ALL: the condition evaluates to true, if there exist all the rows selected by the sub-query for which the comparison holds.

SELECT * FROM emp WHERE sal >= ALL
(SELECT sal FROM emp WHERE deptno = 30)
and deptno <> 30;

Inline Views: The inline view is a construct in Oracle SQL where you can place a query in the SQL FROM clause, just as if the query was a table name

e.g.

SELECT ename, sal FROM ( 
select * from emp ORDER BY sal desc ) 
WHERE ROWNUM < 6;

Multiple-column Sub-queries: When you want to compare more than one columns in a sub-query. e.g.

SELECT ename, job FROM emp
WHERE ( deptno, sal ) IN ( 
SELECT deptno, max( sal ) 
FROM emp GROUP BY deptno );

Co-related Sub-queries

• A co-related query is a form of query used in SELECT, Update or Delete commands to force the DBMS to evaluate the query once per row of the parent query rather than once for the entire query
• A co-related query is used to answer questions whose answers depends on the values in each row of the parent query
  

EXISTS: The EXISTS condition is considered "to be met" if the sub-query returns at least one row. e.g.

SELECT deptno, dname
FROM dept a WHERE EXISTS ( 
SELECT empno FROM emp e
WHERE a.deptno = e.deptno );
SELECT deptno, dname
FROM dept a WHERE NOT EXISTS ( 
SELECT empno FROM emp e  
WHERE a.deptno = e.deptno );
SELECT ename
FROM emp a
WHERE EXISTS ( 
SELECT * FROM emp b 
WHERE b.mgr = a.empno );

With Clause and Sub-queries

• With clause lets us factor out the sub-query, give it a name, then reference that name multiple times within the original complex query
• This improves the performance of this query by having Oracle 9i execute the query only once, then simply reference it at the appropriate points in the main query. e.g.
  

WITH summary AS
( SELECT dname, SUM( sal ) AS dept_total
FROM emp, dept
WHERE emp.deptno = dept.deptno
GROUP BY dname )
SELECT dname, dept_total
FROM summary
WHERE dept_total > ( 
SELECT SUM( dept_total ) * 1 / 3
FROM summary )
ORDER BY dept_total desc;