SQL

Please practice all the SQL concepts given below  this gives basic idea of SQL scripts we use daily in Oracle Apps development process.

SQL Introduction
	SQL:
	Stands for Structured Query language. It is pronunced as Sequel. This language is used to communicate to Oracle Database. This languaged is developed in the year 1972 by IBM.
	Features of SQL: 1) It is a command based language 2) This language is similar to English 3) Every command should end with; 4) Commands are not case sesitive.
	Sub Languages: SQL is divided in 5 sub languages 1) DDL ( Data Definition Language ) 2) DML ( Data Manipultation Language) 3) DRL / DQL ( Data Retrieval Language / Data Query Language ) 4) TCL ( Transaction Control Language ) 5) DCL ( Data Control Language )
	Commands of SQL: 1) DDL ( Data Definition Language ) : ---------------------------------------------- This language is used to create and manage database objects. Commands of DDL are 1) Create 2) Alter 3) Drop 4) Truncate and 5) Rename 2) DML ( Data Manipulation Language ) ------------------------------------------------- This language is used to manage data present in the table Commands of DML are 1) Insert, 2) Update , 3) Delete and 4) Merge 3) DRL ( Data Retrieval Language ) ---------------------------------------------- This language is used to retrive the data from the table. It is only one command ie 1) Select. 4) TCL ( Transaction control Language ) ----------------------------------------------------- This language is used to maintain transactions of the database. It is collection of three comands 1) Commit, 2) Rollback and 3)Savepoint 5) DCL ( Data Control Language ) -------------------------------------------- It is used to control the data and maintain security. It is collection of two commands Grant and Revoke
	SQL *Plus:It is environment which used to write the queries and submit the queries for execution. To connect to the database SQL> Conn username - scott password - tiger Connected.
	Table: ---------------- Table is an object which is used to store the data into the database. It is collection of rows and columns.
	Datatypes: ---------------- The following are the datatypes which are used in Oracle   1) Char(size): --------------------- Used to store alphanumeric values. This datatype is of fixed length. Maximum size 2000 bytes. 2) Varchar2 (size): ------------------------ Used to store aplhanumeric values. This datatype is of variable length. Maximum size 4000 bytes. 3) Number(p,s): ---------------------- Used to store number values. P -stands for precision S -stands for scale Maximum size , precision or scale can range from 1 to 38 4) Date: ----------- Used to store date values. Range 01-jan-4712 BC to 31-dec-9999 AD 5) CLOB: -------------- Used to store alphanumeric values. maxsize - 4 GB 6) BLOB: ------------ Used to store binary data Maxsize - 4GB 7) Bfile: -------------- Used to store binary files. Maxsize - 4 GB if internal to the database. A file > 4 GB is stored external to the database at OS level.

SQL Commands
	Create: ----------- Create command is used to create a table. Syntax: --------------- Create table < table_name> ( col_name1 datatype(size), col_name2 datatype(size), ,, ,, , col_namen datatype(size) ); ex: ------ create table student ( sno number(3), sname varchar2(20), marks number(3));Output: --------- Table Created. Insert commad:  -------------------- Insert command is used to isert the rows into the table. Syntax: ---------- Insert into < table_name> values ( val1, val2, ....., valn ); Ex: ----- insert into student values (101, 'arun', 80); insert into student values (102, 'kiran', 85); insert into student values (103, 'vinay', 66); For every insert command , the response we get is " 1 row created. " Using null keyword:  -------------------------- Null keyword is used , if we do not have value for a column. Ex: --- insert into student values (104,'amit', null); insert into student values (105,null, null); 2nd syntax of insert command: ---------------------------------------- insert into student( col1, col2, ..coln ) values ( val1, val2, ..., valn ); ex: ----- insert into student ( sno, sname ) values (106,'vijay'); The leftover column will have null value. Selecting the rows from the table:  -------------------------------------------- To retrieve the rows from the table select command is used. Basic Syntax: ---------------- select * from < table_name>; Ex: ----- select * from student; In the above command, * is a special character which is used to display all the information from the table. To select specific columns:  ----------------------------------- select col1, col2... , coln from the <table_name>; ex: ----- select sno, sname from student; select sname from student; Selecting specific rows: ------------------------------- Where clause is used to filter the rows from the table. Syntax: --------- select * from < table_name> where < condition >; ex: ------ select * from student where marks > 70; Combination of selecting specific row and selecting specific columns:  ------------------------------------------------------------------------------------------ select sno from student where marks > 70; Using Arithmetic operations with select command: ----------------------------------------------------------------- select empno, ename , sal, sal* 12 , deptno from emp; Using column Alias: ------------------------- Column alias is process of providing user definied column heading. select empno, ename , sal, sal* 12 annual_sal , deptno from emp; In the above query annual_sal is the column alias. Using Distinct keyword: ------------------------------- Distinct keyword is used to get the distinct ( unique ) values. Duplicates will be supressed. ex: ------ select distinct deptno from emp; Update command:  ----------------------- This command is used to change the data present in the table. Syntax: --------- Update < table_name> set <col_name> = < value> where < condition> ; ex: ---- update student set marks =95 where sno =101; Output: --------- 1 row updated. Updating multiple columns:  ----------------------------------- update student set sname='uday' , marks =48 where sno =102; Note: ------ Update command without where clause , will update all the rows. ex: --- update student set marks=80; Using Delete command: ------------------------------ Delete command is used to delete the rows from the table. Syntax: --------- delete from < table_name> where < condition >; ex: ----- delete from student where sno=103; Output: 1 row deleted. Note: ------ Delete command without where clause will delete all the rows. ex: ---- delete from student; ( All the rows are deleted ) ++++++++ Alter command: -------------------- Alter command is used to change the structure of the table. We can perform following activities using Alter command 1) Adding a new column 2) Droping an existing column 3) Modifying a column 4) Renaming a column Adding a new column: ---------------------------- Alter table < table_name> add ( col_name1 datatype(size), col_name2 datatype(size), ,, ,, ,, col_namen datatype(size) ); ex: ----- Alter table student add ( city varchar2(10), state varchar2(10)); Table altered. Note: The new columns will have null values. Droping a column: ----------------------- Alter table < table_name > drop ( col_name1, col_name2,..., col_namen); Ex: ---- Alter table student drop ( city, state); Table Altered. Modifying a column: ------------------------- By using modifying we can increase and decrease the size of the column. ex: --- Alter table student modify ( sname varchar(20)); Table Altered. Decreasing the size : -------------------------- Alter table student modify ( sname varchar(15)); Table altered. Note: ------- To decrease the size , the existing table data should fit into new size. By using Modify keyword , we can also change the datatype of the column. Ex: ----- Alter table student modify ( sname number(15)); Note: ------- To change the datatype, column should be empty. Renaming a column: -------------------------- Syntax: ----------- Alter table <table_name> rename column <old_name> to <new_name>; Ex: ---- Alter table student rename column sno to roll_no; Table Altered. Truncate command: ------------------------- This command is used to remove all the rows from the table. Truncate table < table_name>; Truncate table student; Rename command: ------------------------ This command is used to change the table name. Syntax: -------- Rename <old_table_name > to < new_table_name>; Syntax: -------- Rename student to stu1; Table renamed. From now, we need to access the table by using the new name. Drop command: -------------------- This command is used to remove the table from the database. Syntax: ---------------- Drop table < table_name >; ex: ---- Drop table stu1; Table dropped.

SQL Functions
	Functions:  ------------- Functions act of data items and returns a value. Types Of Functions  ------------------------- 1) Group Functions / Aggregate Functions 2) Scalar Functions / Single row Functions 1) Group Functions ------------------------- These functions act on group of rows. Hence they are called as group functions. The following are group functions ------------------------------------------ AVG( ) SUM( ) MAX( ) MIN( ) COUNT(*) COUNT(EXPR) AVG( ): --------- Returns average value of the column SQL> select AVG(sal) from emp; SUM( ): ---------- Returns sum of valu of the column SQL> select SUM(sal) from emp; MAX( ): --------- Returns Maximum value of the column SQL> select MAX(sal) from emp; MIN( ): --------- Returns minimum value of the column It ignores numm values COUNT(*): -------------- Returns no of rows in the table. SQL> select COUNT(*) from emp; COUNT(EXPR): -------------------- Returns no of values present in the column. It Ignores null values in the column. SQL> Select COUNT(COMM) from emp; 2) Scalar Functions: -------------------------- These functions act on every row of the table These functions are divided in three types 1) Character Functions 2) Number Functions 3) Date Functions 4) Conversion Functions 1) Character Functions  ------------------------------ These functions accepts Character as input. LOWER( ): -------------- It converts upper case letters to lower case SQL> select LOWER('ORACLE') from dual; SQL> select empno, ename, LOWER(ename) from emp; UPPER( ): ------------- It converts lower case letters to upper case SQL> select UPPER ('oracle') from dual; INITCAP( ): --------------- It converts first letter of each word in upper case, keeling all other letters in lower case. SQL> select INITCAP('free training') from dual; SQL> select INITCAP(ename) from emp; CONCAT( ): --------------- It concatenates two strings It accepts only two parameters SQL> select CONCAT ( 'sunil,'karthik') from dual; SQL> select CONCAT ( ename, job ) from emp; SUBSTR(value, m, n) ---------------------------- Returns characters from the starting position 'm' to 'n' characters long It 'n' is less than 1 ot 0 , NULL is returned It 'n' is omitted , all the characters to the end of the string is returned SQL> select SUBSTR('oracle' , 2, 3) from dual; SQL> select SUBSTR('oracle' , 2, 4) from dual; SQL> select SUBSTR('oracle' , 3, 2) from dual; SQL> select SUBSTR('oracle' , 4, 0) from dual; SQL> select SUBSTR('oracle' , 2) from dual; LENGTH( ): -------------- Returns no of characters in the value. SQL> select length('oracle') from dual; SQL> select * from emp where length(ename) = 4; INSTR ( string, char ): ---------------------------- Returns position of the character in the string. Returns first occurance of the character in the string. Returns 0 when the character does not exist. 2nd parameter can be string. SQL> select INSTR('oracle', 'a' ) from dual; SQL> select INSTR('database' ,'a') from dual; SQL> select INSTR('oracle' ,'h') from dual; SQL> select INSTR('oracle', 'acl') from dual; LPAD( string, n, char ): ------------------------------ Pads the char value towards left, to a total width of n character positions. SQL> select LPAD ( 'oracle', 10 , '*') from dual; SQL> select LPAD ( ename , 10 , '-') from dual; RPAD ( string, n, char ): ------------------------------- Pads the char value towards right, to a total width of n character positions. SQL> select RPAD ( 'oracle', 10 , '*') from dual; SQL> select RPAD ( ename , 10 , '-') from dual; LTRIM ( string , char ): ------------------------------ Removes the specified char towards left side SQL> select LTRIM ( 'zzzzoracle' , 'z') from dual; RTRIM ( string , char ): ----------------------------- Removes the specified char towards right side SQL> select RTRIM ( 'oraclezzzz' , 'z') from dual; Number Functions: ------------------------ These functions accept numeric input and return numeric values. 1) ABS(n): -------------- It returns Absolute value of 'n' SQL> select ABS(-40) from dual; 2) SQRT(n): ---------------- It returns square root of 'n' as real value. The value 'n' cannot be negative. SQL> select SQRT(25) from dual; 3) POWER(m,n): ---------------------- Returns 'm' raised to the power of 'n' SQL> select POWER(2,5) from dual; 4) MOD(m,n): ------------------ It returns remainder of 'm' divided by 'n' SQL> select mod(11,4), mod(10,2) from dual; 5) FLOOR(n): ------------------ Returns largest integer less than or equal to 'n' SQL> select FLOOR(40.8) from dual; SQL> select FLOOR(40.2) from dual; SQL> select FLOOR(40.5) from dual; 6) CEIL(n): --------------- Returns smallest integer greater than or equal to 'n' SQL> select CEIL(40.8) from dual; SQL> select CEIL(40.2) from dual; SQL> select CEIL(40.5) from dual; 7) TRUNC(n,m): --------------------- IF 'm' is omitted, 'n' is truncated to 0 decimal places It returns 'n' truncated to 'm' decimal places. SQL> select TRUNC(40.637) from dual; SQL> select TRUNC(40.637,2) from dual; 8) ROUND(n,m): ---------------------- Rounds off to the nearest integer. It returns 'n' rounded to 'm' places right to the decimal point. SQL> select ROUND(40.8) from dual; SQL> select ROUND(40.2) from dual; SQL> select ROUND(40.5) from dual; SQL> select ROUND(40.634,2) from dual; SQL> select ROUND(40.637,2) from dual; Working with Dates: -------------------------- Oracle stores dates in internal numeric format. Dates can range from 01-january-4712 BC to 31-december-9999 AD Default display format will be in the form of DD-MON-YY SYSDATE: ------------------------------ It is a date functions that returns current DATE and TIME. SQL> select SYSDATE from dual; DATE Functions: -------------------- 1)ADD_MONTHS(D,m): ------------------------------- It returns the Date 'D' plus or minus 'n' months The Argument 'n' can be positive or negative number SQL> select SYSDATE, ADD_MONTHS(SYSDATE,2) from dual; 2) MONTHS_BETWEEN ( D1, D2 ): --------------------------------------------- It returns number of months between dates D1 and D2 It D1 is later than D2, the result is positive number, else negative. SQL> select ename , hiredate, SYSDATE, MONTHS_BETWEEN ( SYSDATE, hiredate ) from emp; 3) NEXT_DAY(D, day) ----------------------------- It returns date of the specified day. That is later than the date 'D'. day can be full name or the abbreviation. SQL> select SYSDATE, NEXT_DAY(SYSDATE, 'WED') from dual; SQL> select SYSDATE, NEXT_DAY('11-jan-81' , 'MONDAY') from dual; 4) LAST_DAY(D): ----------------------- It returns date of the last day of the month specified. SQL> select SYSDATE, LAST_DAY(SYSDATE) from dual; Conversion Functions: ----------------------------- These functions converts values from one datatype to other. SQL provides three conversion functions 1) TO_CHAR 2) TO_DATE 3) TO_NUMBER 1) TO_CHAR: ----------------- This functions has two functionalities. -> Can convert number to characters -> Can convert date to characters Converting number to characters: -------------------------------------------- SQL> select empno, ename , TO_CHAR(sal, '$9,999') from emp; Converting number to characters: -------------------------------------------- SQL> select empno, ename, sal, TO_CHAR(hiredate,'dd-month-yyyy') from emp; 2) TO_DATE: ----------------- It can convert characters to date values. SQL> select ADD_MONTS( TO_DATE('17-january-1981 06:45:22 A.M.' , 'DD-MONTH-YYYY HH:MI:SS A.M.') , 2) FROM DUAL; 3) TO_NUMBER: --------------------- It can convert characters to numbers. SQL> select 100 + TO_NUMBER(LTRIM('$200', '$')) from dual;

Clauses

Group By clause:
---------------------

Group by clause is used to divide the rows in table to different groups.
So, that we can apply group functions on each group.

Ex1:
-------

SQL> select deptno, sum(sal)
from emp
group by deptno;

As the standard emp tables has 3 types of deptno ( 10, 20, 30), 14 rows of the table are divided 3 groups. SUM( ) function is applied on each group.

Ex2:
---------

SQL> select job, avg(sal)
from emp
group by job;

Ex3:
------

SQL> select deptno, SUM(sal), MAX(sal), MIN(sal), AVG(sal), COUNT(*)
from emp
group by deptno;

Ex4:
------

SQL> select deptno, job, SUM(sal)
from emp
group by deptno, job;

In the above query, first grouping is done based on deptno and sub grouping is done based on job.

SQL> select deptno, SUM(sal), ename
from emp
group by deptno;

The above query will result in error.
Error: Not a group by expression.

The rule of GROUP BY clause:
-------------------------------

All the columns in the select list should use GROUP functions or should be included in the GROUP BY clause.

In the above query, ename is not satisfying the rule, we get the error.

HAVING Clause:
--------------

HAVING clause is used to filter the results of GROUP BY clause.

Ex:
------

SQL> Select deptno, SUM(sal)
from emp
GROUP BY deptno
HAVING SUM(sal) > 9000;

Query execution process:
---------------------------
--> First the rows are grouped
--> Second the group function is applied on the identified groups
--> Third the groups that match the criteria in the HAVING clause are displayed.

Existance of GROUP BY clause does not gaurantee the existance of HAVING clause, But
the existance of HAVING clause demands the existance of GROUP BY caluse.

Few more examples:
--------------------

SQL> Select deptno, AVG(sal), SUM(sal)
from emp
group by deptno
having AVG(sal) > 2500;

SQL> Select deptno, MIN(sal), MAX(sal)
from emp
where job ='CLERK'
GROUP BY deptno
HAVING MIN(sal) < 1000;

ORDER BY clause:
-------------------

ORDER BY clause is used to arrange the rows in ascending or in the descending order.
ORDER BY clause should be the last clause of the query.
An expression or column alias can be specified in the ORDER BY clause.
The default ordering of the data is ascending.

Numbers --> 0 - 9
Dates ---> Earliest - Latest
Strings --> A - Z
NULLS --> Last

Ex:
-----------
SQL> select * from emp
ORDER BY sal;

SQL> select * from emp
ORDER BY sal desc;

SQL> Select ename , job, deptno, hiredate
from emp
ORDER BY hiredate;

SQL> Select * from emp
ORDER BY job, ename;

SQL> Select ename , job, sal
from emp
where sal > 2500
ORDER BY JOB, ename DESC;

SQL> Select empno, ename, sal, sal*12 annual_sal
from emp
ORDER BY annual_sal;

Constraints

Constraints are rules appled on tables.
Constraints increase in integrity or quality of the database.

Types of Constraints:
----------------------
1) NOT NULL
2) UNIQUE
3) PRIMARY KEY
4) FOREIGN KEY (or) Referential Integrity Constraint
5) CHECK

Declaration Style:
-------------------
Constraint can be created in two levels
1) Column Level
2) Table Level

Column Level:
---------------
They are declared as part of the definition of the individual column.

Table Level:
----------------
They are declared as part of the table definition.

1) NOT NULL Constraint:
------------------------
NOT NULL constraint does not accept NULL values.
To satisfy this constraint, every row in the table must contain a value.
NOT NULL constraint can be created only at column level.

Syntax:
-----------

Create table < table_name> ( col_name1 datatype(size) NOT NULL,
col_name2 datatype(size) ,
col_namen datatype(size) );

Ex:
---

Create table student1 ( sno number(3) NOT NULL,
sname varchar2(10),
marks number(3));

insert into student1 values ( 101,'arun', 40); -- valid
insert into student1 values ( 102,'kiran', 75); -- valid
insert into student1 values ( null,'arun', 40); -- error

UNIQUE COnstraint:
-----------------------

UNIQUE constraint does not accept duplicate value.
UNIQUE constraint can be created at column level or at table level.
UNIQUE constraint will accept NULL value.

Syntax of UNIQUE constraint at column level:
----------------------------------------------

Create table < table_name> ( col_name1 datatype(size) UNIQUE,
col_name2 datatype(size),
col_namen datatype(size));

Ex:
----

Create table student2 ( sno number(3) UNIQUE,
sname varchar2(10),
marks number(3));

insert into student2 values ( 101, 'arun',40); -- valid
insert into student2 values ( 102, 'kiran',75); -- valid
insert into student2 values ( 101, 'vijay',55); -- error
insert into student2 values ( null,'ashok',85); -- valid
insert into student2 values ( null,'vinay',81); -- valid

Note:
------
UNIQUE constraint will accept any no of NULL values

Syntax of UNIQUE constraint at table level:
-------------------------------------------------

Create table < table_name> ( col_name1 datatype(size),
col_name2 datatype(size),
col_namen datatype(size),
UNIQUE ( col_name1));

Ex:
-----
Create table student3 ( sno number(3) ,
sname varchar2(10),
marks number(3),
UNIQUE ( sno ));

insert into student3 values ( 101, 'arun',40); -- valid
insert into student3 values ( 102, 'kiran',75); -- valid
insert into student3 values ( 101, 'vijay',55); -- error
insert into student3 values ( null,'ashok',85); -- valid
insert into student3 values ( null,'vinay',81); -- valid

There is no difference practically , when a constraint is created at column level
or table level. You can follow any syntax.

PRIMARY KEY:
------------------

A PRIMARY KEY constraint is combination of NOT NULL and UNIQUE constraint.
A PRIMARY KEY column will not accept NULL values and DUPLICATE values.
A PRIMARY KEY column is used to uniquely identify every row in the table.
A Table can have only one PRIMARY KEY
A PRIMARY KEY Constraint can be created at column level or at table level.

Syntax of PRIMARY KEY at Column Level:
-----------------------------------------

Create table < table_name> ( col_name1 datatype(size) PRIMARY KEY,
col_name2 datatype(size),
col_namen datatype(size));

Ex:
--------

Create table student4 ( sno number(3) PRIMARY KEY,
sname varchar2(10),
marks number(3));

insert into student4 values ( 101, 'arun',40); -- valid
insert into student4 values ( 102, 'kiran',75); -- valid
insert into student4 values ( 101, 'vijay',55); -- error
insert into student4 values ( null,'ashok',85); -- valid

Syntax of PRIMARY KEY at table level:
---------------------------------------

Create table < table_name> ( col_name1 datatype(size),
col_name2 datatype(size),
col_namen datatype(size),
PRIMARY KEY ( col_name1));

Ex:
-----
Create table student5 ( sno number(3) ,
sname varchar2(10),
marks number(3),
PRIMARY KEY ( sno ));

insert into student5 values ( 101, 'arun',40); -- valid
insert into student5 values ( 102, 'kiran',75); -- valid
insert into student5 values ( 101, 'vijay',55); -- error
insert into student5 values ( null,'ashok',85); -- valid
insert into student5 values ( null,'vinay',81); -- valid

There is no difference practically , when a constraint is created at column level
or table level. You can follow any syntax.

FOREIGN KEY Constraint:
----------------------------

A Foreign key constraint establishes relationship between tables.
This relationship is called as Parent-child relationship.
It is also called as Master-Detail relationship.
A Foreign Key column in child table will only accept the values present in the Primary Key or Unique column of the parent table.
A foreign key constraint can be created at column level or at table level.

TO understand this FOREIGN KEY Constraint , have a look at this scenario:
-------------------------------------------------------------------------------

I have started a school.
Assume only 3 students joined in my school.
I create a table with name school and enter the student details in it. Every student will have student no. So, sno is primary Key

SCHOOL
Sno	Sname	Gender	Age
101	arun	M	5
102	kiran	M	5
103	sreeja	F	4

I have appointed librarian for my school. If any student borrows book, librarian should enter the sno and book name .
I create a table with name library . Every sunday i visit my school to check whether it is running properly or not, surprisingly , i have the following data in my library table.

Library
sno	book_name
102	oracle
103	java
103	c++
108	unix

If you observe, there is no student 108 in our school.
So the last row is invalid row. Probably this could be data entry mistake.
I do not want these kind of inavlid data into my library table.
That means, i should create library table in such a way thay it should accept only the sno values which are in school table.
So, we should create a relationship between these table. This relationship is called as parent-child relationship.

In our scenario,
School is the parent table.
Library is the child table.
sno column in the library table is called as FOREIGN KEY column.
Note: A Foreign key column in the child table will only accept the values present in the PRIMARY KEY column of the parent table.

Lets practically workout this scenario
---------------------------------------------------
Step 1: Creating the parent table
Step 2: Inserting rows into the parent table
Step 3: Creating child table which contains FOREIGN KEY
Step 4: Inserting rows into the child table and understand the behaviour of the FOREIGN KEY column

Step 1: Creating parent table
-----------------------------------------
create table school ( sno number(3) PRIMARY KEY,
sname varchar2(10),
gender varchar2(10),
age number(2));

Step 2: Inserting rows into the parent table
-----------------------------------------------------------
insert into school values ( 101, 'arun', 'M' , 5);
insert into school values ( 102, 'kiran', 'M' , 5);
insert into school values ( 103, 'sreeja', 'M' , 4);

Step 3: creating child table
Syntax of foreign key constraint at column level
----------------------------------------------------------------
create table <table_name> ( col_name1 datatype(size),
col_name2 datatype(size),
col_name3 datatype(size) REFERENCES <parent table > ( primary key col_name));

Ex:
------
create table library ( sno number(3) REFERENCES school ( sno),
book_name varchar2(10));

Step 4: Insertingrow into the child table
----------------------------------------------------
insert into library values ( 102, 'oracle'); -- valid
insert into library values ( 103, 'java'); -- valid
insert into library values ( 103, 'c++'); -- valid
insert into library values ( 108, 'unix'); -- error
Note:
As we have established relationship, library table is not accepting sno 108.
This relationship helps in improving the accuracy of the database.
++++++++++++++++++++++++

Syntaxof Foreign key constraint at table level
create table <table_name> ( col_name1 datatype(size),
col_name2 datatype(size),
FOREIGN KEY ( col_name1) REFERENCES < parent table> ( primary key col name ) );

Ex:
------
create table library ( sno number(3),
book_name varchar2(10) REFERENCES school ( sno));

Note:
A FOREIGN KEY column will accept duplicate values
A FOREIGN KEY column will accept null values.

CHECK Constraint:
-------------------------------
Check constraint is used to define values a column can store.

Ex:
----------
Create table student6 ( sno number(3),
Sname varchar2(10),
Marks number(3) CHECK ( marks between 0 and 100) );

Insert into student6 values ( 101,’arun’, 60);
Insert into student6 values ( 102,’vijay’, 55);
Insert into student6 values ( 103,amit’, 120); -- error -- check constraint violated
Insert into student6 values ( 104,amit’, -10); -- error -- check constraint violated

Joins are used to retrieve data from multiple tables.
Consider two tables emp and dept.
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	1,400	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

Dept table

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

I want the following output:

EMPNO	ENAME	DNAME	LOC
7369	SMITH	RESEARCH	DALLAS
7499	ALLEN	SALES	CHICAGO
7521	WARD	SALES	CHICAGO
7566	JONES	RESEARCH	DALLAS
7654	MARTIN	SALES	CHICAGO
7698	BLAKE	SALES	CHICAGO
7782	CLARK	ACCOUNTING	NEW YORK
7788	SCOTT	RESEARCH	DALLAS
7839	KING	ACCOUNTING	NEW YORK
7844	TURNER	SALES	CHICAGO
7876	ADAMS	RESEARCH	DALLAS
7900	JAMES	SALES	CHICAGO
7902	FORD	RESEARCH	DALLAS
7934	MILLER	ACCOUNTING	NEW YORK

Look at the above output, 
Empno, ename  are from  emp table,
Dname ,loc are from dept  table.
So, we need to get the data from two table, which can be done by using joins.

Types of Joins:
1) Equi Join / Inner Join
2) Non Equi Join
3) Self Join
4) Outer Join ----  Right Outer Join
                          Left Outer Join         
                          Full Outer Join

Equi Joins:
-------------
Consider emp and dept tables, there is a common column i.e.  deptno.
When tables are joined basing on common column , it is called Equi join.

In Equi Joins , we always use  = ( equality operator ) in join condition.

Ex:
Select empno,ename , dname, loc 
from  emp,  dept
where emp.deptno = dept.deptno;

In the above query  emp.deptno = dept.deptno    is called as join condition.
We mention join condition in where clause.

Ex2:
I want the following output.

EMPNO	ENAME	JOB	SAL	DNAME	LOC
7369	SMITH	CLERK	800	RESEARCH	DALLAS
7499	ALLEN	SALESMAN	1600	SALES	CHICAGO
7521	WARD	SALESMAN	1250	SALES	CHICAGO
7566	JONES	MANAGER	2975	RESEARCH	DALLAS
7654	MARTIN	SALESMAN	1250	SALES	CHICAGO
7698	BLAKE	MANAGER	2850	SALES	CHICAGO
7782	CLARK	MANAGER	2450	ACCOUNTING	NEW YORK
7788	SCOTT	ANALYST	3000	RESEARCH	DALLAS
7839	KING	PRESIDENT	5000	ACCOUNTING	NEW YORK
7844	TURNER	SALESMAN	1500	SALES	CHICAGO
7876	ADAMS	CLERK	1100	RESEARCH	DALLAS
7900	JAMES	CLERK	950	SALES	CHICAGO
7902	FORD	ANALYST	3000	RESEARCH	DALLAS
7934	MILLER	CLERK	1300	ACCOUNTING	NEW YORK

Try writing query by yourself.
In select clause , list out all the columns you want.
In from clause, mention the table from which you want to pull the data.
In where clause, write the join condition.

You query should look like this,

Select empno, ename , job, sal, dname , loc
From emp, dept
Where emp.deptno = dept.deptno;

Ex 3:
I want the following output.

EMPNO	ENAME	JOB	SAL	DEPTNO	DNAME	LOC
7369	SMITH	CLERK	800	20	RESEARCH	DALLAS
7499	ALLEN	SALESMAN	1600	30	SALES	CHICAGO
7521	WARD	SALESMAN	1250	30	SALES	CHICAGO
7566	JONES	MANAGER	2975	20	RESEARCH	DALLAS
7654	MARTIN	SALESMAN	1250	30	SALES	CHICAGO
7698	BLAKE	MANAGER	2850	30	SALES	CHICAGO
7782	CLARK	MANAGER	2450	10	ACCOUNTING	NEW YORK
7788	SCOTT	ANALYST	3000	20	RESEARCH	DALLAS
7839	KING	PRESIDENT	5000	10	ACCOUNTING	NEW YORK
7844	TURNER	SALESMAN	1500	30	SALES	CHICAGO
7876	ADAMS	CLERK	1100	20	RESEARCH	DALLAS
7900	JAMES	CLERK	950	30	SALES	CHICAGO
7902	FORD	ANALYST	3000	20	RESEARCH	DALLAS
7934	MILLER	CLERK	1300	10	ACCOUNTING	NEW YORK

It you look at the above requirement, it is almost similar to previous query

Try writing query by yourself.
In select clause , list out all the columns you want.
In from clause, mention the table from which you want to pull the data.
In where clause, write the join condition.

Select  empno, ename , job, sal, deptno, dname , loc
From emp , dept
Where emp.deptno = deptno.deptno;    --  error

But, the above query will give an error.
The problem with the column  deptno.

As deptno column is present in both emp and dept tables.
There will be ambiguity, from which table it needs to pull the data.

There is no ambiguity problem for columns like empno, ename, job, dname ,loc
As it is either available from emp or from dept table.
How can we resolve the ambiguity?

We can resolve the ambiguity by mentioning the <table_name> . <col_name> in the select clause.

Ex:

Select  empno, ename , job,  sal,  emp.deptno, dname , loc
From emp , dept
Where emp.deptno = deptno.deptno;   

The common column ie deptno can also be retrieved from dept table also.

Ex:
Select  empno, ename , job,  sal, emp.deptno, dname , loc
From emp , dept
Where emp.deptno = deptno.deptno;   

The above two queries will give the same result.

Remember:
--------------------

We need to mention <table_name>. < col_name> in select clause to resolve the ambiguity.

Can we mention <table_name>  . < col_name> for all the columns  in the select clause.

Why not, definitely Yes

You query will look like this:

Select emp.empno, emp.ename, emp.job,  emp.sal, emp.deptno, dept.dname , dept.loc
From emp, dept
Where emp.deptno = dept.deptno;

So, compare the following two queries

Query A
Select  empno, ename , job, sal, emp.deptno, dname , loc
From emp , dept
Where emp.deptno = deptno.deptno;   

Query B
Select emp.empno, emp.ename, emp.job, emp.sal, emp.deptno, dept.dname , dept.loc
From emp, dept
Where emp.deptno = dept.deptno;

Both Query A and Query B will give the same result.
Developer tend to prefer writing Query A rather than Query B, as the length of the query is small.

But from performance point of view
Query B will run faster than Query A

So, according to coding standards, we should mention < table_name> . < col_name> for all the columns which helps in performance.

But when we mention < table_name> . <col_name> for all the columns , the length of the query will be long.

To overcome the length problem, we use the table alias.

Table alias helps in reducing the length of the query and at the same time, performance is maintained.
Table alias are created in from clause, can be used in select and where clause.

Ex:
---
Select e.empno, e.ename , e.job, e.sal, e.deptno, d.dname , d.loc
From emp e, dept d
Where e.deptno = d.deptno;

e  is table alias for emp table.
d is table alias for  dept  table.

Not only two tables , we can join three for n table .

Consider  the following tables

EMP Table                                          

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	1,400	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

Dept table

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

Areas table

CITY	STATE
NEW YORK	NEW YORK
DALLAS	TEXAS
CHICAGO	ILLINOIS
BOSTON	MASSACHUSETTS

I want the following output,

EMPNO	ENAME	DEPTNO	DNAME	LOC	STATE
7369	SMITH	20	RESEARCH	DALLAS	TEXAS
7499	ALLEN	30	SALES	CHICAGO	ILLINOIS
7521	WARD	30	SALES	CHICAGO	ILLINOIS
7566	JONES	20	RESEARCH	DALLAS	TEXAS
7654	MARTIN	30	SALES	CHICAGO	ILLINOIS
7698	BLAKE	30	SALES	CHICAGO	ILLINOIS
7782	CLARK	10	ACCOUNTING	NEW YORK	NEW YORK
7788	SCOTT	20	RESEARCH	DALLAS	TEXAS
7839	KING	10	ACCOUNTING	NEW YORK	NEW YORK
7844	TURNER	30	SALES	CHICAGO	ILLINOIS
7876	ADAMS	20	RESEARCH	DALLAS	TEXAS
7900	JAMES	30	SALES	CHICAGO	ILLINOIS
7902	FORD	20	RESEARCH	DALLAS	TEXAS
7934	MILLER	10	ACCOUNTING	NEW YORK	NEW YORK

Total no of columns in the output : 6

Empno, Ename, deptno   --  from  Emp table
Dname , Loc                      -- from Dept  table
State                                 --  from  Areas  table

We know, we can join emp and dept  tables by using the common column deptno.
We can join dept and Areas  table  by using the common column Loc and city.

Remember:
For joining table, it is not the column name which should match.
The column values should match.

The Query to get the above output.

Select e.empno, e.ename, e.deptno, d,dname, d.loc, a.state
From emp e, dept d, areas a
Where e.deptno = d.deptno and d.loc = a.city.

Note:
To join 2 tables, we need  1 condition
To join 3 tables, we need 2 conditions
To join n tables,  we need n-1 conditions.

Note:
Equi joins always use = ( Equality operator)  in join condition.

Non Equi-Join
-------------------------
When tables are joined without using = ( equality operator ) , it is called Non Equi-join.

Consider following tables

Emp Table

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	1,400	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

Salgrade Table

GRADE	LOSAL	HISAL
1	700	1200
2	1201	1400
3	1401	2000
4	2001	3000
5	3000	9999

All the employees are categorized into grades basing on sal.

What is the grade of SMITH?
SMITH sal is 800.  His sal is falling between 700  and 1200, he is grade 1 employee.

What is the grade of CLARK?
CLARK sal is 2450. His sal is falling between 2001 and 3000, so he is grade 4 employee.

I want the following output.

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

Empno, ename and sal   we need to get it from EMP table.
Grade we need to get it from SALGRADE table.

To retrieve the data from multiple tables , we need joins.
So, we need to join EMP and SALGRADE table.
Observe, there is no common column between EMP and SALGRADE.
Hence we cannot use = ( equality )  operator.

When sal is falls between LOSAL and HISAL, we can get the GRADE,
We need to use between operator.

Ex:
----------

Select e.empno, e.ename , e.sal, s.grade
From emp e, salgrade s
Where e.sal between s.losal and s.hisal;

Note:
When = ( equality operator ) is not used, it is called NON EQUI-JOIN

3) Self join:

When a table is joined to itself, it is called Self join.

I want the following output

EMPNO	ENAME	SAL	MGR	MGR NAME
7369	SMITH	800	7902	FORD
7499	ALLEN	1600	7698	BLAKE
7521	WARD	1250	7698	BLAKE
7566	JONES	2975	7839	KING
7654	MARTIN	1250	7698	BLAKE
7698	BLAKE	2850	7839	KING
7782	CLARK	2450	7839	KING
7788	SCOTT	3000	7566	JONES
7844	TURNER	1500	7698	BLAKE
7876	ADAMS	1100	7788	SCOTT
7900	JAMES	950	7698	BLAKE
7902	FORD	3000	7566	JONES
7934	MILLER	1300	7782	CLARK

Empno, ename , sal,mgr  we can straight forward get from EMP table.
Mgr name,  are also available in EMP table.
We can get Mgr name by comparing mgr with empno  column.

We need to join EMP table with EMP table.
When a table is joined to itself, it is called SELF JOIN.

Ex:
Select e.empno, e.ename , e.sal, e.mgr, m.ename
From emp e, emp m
where e.mgr = m.empno;

Note:
In self joins, we create two table aliases for the  same table.

Outer Joins:

Outer joins are extensions of equi-join.
In equi-joins, we get only the matching data.

In outer joins, we get matching and non matching data.

Look at the equi-join query
Select e.empno, e.ename , e.job, e.sal, e.deptno, d.dname , d.loc
From emp e, dept d
Where e.deptno = d.deptno;

Output of the above query:

EMPNO	ENAME	JOB	SAL	DEPTNO	DNAME	LOC
7369	SMITH	CLERK	800	20	RESEARCH	DALLAS
7499	ALLEN	SALESMAN	1600	30	SALES	CHICAGO
7521	WARD	SALESMAN	1250	30	SALES	CHICAGO
7566	JONES	MANAGER	2975	20	RESEARCH	DALLAS
7654	MARTIN	SALESMAN	1250	30	SALES	CHICAGO
7698	BLAKE	MANAGER	2850	30	SALES	CHICAGO
7782	CLARK	MANAGER	2450	10	ACCOUNTING	NEW YORK
7788	SCOTT	ANALYST	3000	20	RESEARCH	DALLAS
7839	KING	PRESIDENT	5000	10	ACCOUNTING	NEW YORK
7844	TURNER	SALESMAN	1500	30	SALES	CHICAGO
7876	ADAMS	CLERK	1100	20	RESEARCH	DALLAS
7900	JAMES	CLERK	950	30	SALES	CHICAGO
7902	FORD	ANALYST	3000	20	RESEARCH	DALLAS
7934	MILLER	CLERK	1300	10	ACCOUNTING	NEW YORK

In the output, we are not getting dname OPERATIONS and loc  BOSTON.
As  deptno 40 is not present  in the emp table.

Compare EMP and DEPT tables with respect to distinct values of the common column deptno.

EMP table	DEPT table
deptno no column	deptno column
10	10
20	20
30	30
	40

As there is no value 40 in emp table,  we are not getting OPERATIONS and BOSTON in the output of equi join.

Outer Joins will give 14 + 1 = 15 rows in the output.
(+) is called  OUTER JOIN OPERATOR.

As we have deficiency of data in EMP table, we use the outer join operator towards the deficiency side.
Ex of OUTER JOINS

Select e.empno, e.ename , e.job, e.sal, e.deptno, d.dname , d.loc
From emp e, dept d
Where e.deptno (+) = d.deptno;

Compare equi-join and outer-join queries, the only difference is
the OUTER JOIN OPERATOR i.e. (+)

output of the above OUTER JOIN query

EMPNO	ENAME	JOB	SAL	DEPTNO	DNAME	LOC
7369	SMITH	CLERK	800	20	RESEARCH	DALLAS
7499	ALLEN	SALESMAN	1600	30	SALES	CHICAGO
7521	WARD	SALESMAN	1250	30	SALES	CHICAGO
7566	JONES	MANAGER	2975	20	RESEARCH	DALLAS
7654	MARTIN	SALESMAN	1250	30	SALES	CHICAGO
7698	BLAKE	MANAGER	2850	30	SALES	CHICAGO
7782	CLARK	MANAGER	2450	10	ACCOUNTING	NEW YORK
7788	SCOTT	ANALYST	3000	20	RESEARCH	DALLAS
7839	KING	PRESIDENT	5000	10	ACCOUNTING	NEW YORK
7844	TURNER	SALESMAN	1500	30	SALES	CHICAGO
7876	ADAMS	CLERK	1100	20	RESEARCH	DALLAS
7900	JAMES	CLERK	950	30	SALES	CHICAGO
7902	FORD	ANALYST	3000	20	RESEARCH	DALLAS
7934	MILLER	CLERK	1300	10	ACCOUNTING	NEW YORK
<

Subqueries

When we write a query inside another query, the inner query is called subquery.
Outer query is called parent query.
Subquery is executed first and the parent query will be executed by using the result
of the sub query.

Sub queries are used to get the results based on unknown values.

Types of Sub queries:

1. Single row subquery
2. Muiltiple row subquery
3. Multiple column subquery
4. Co-related subquery
5. Scalar subquery
6. Inline view

Single row subquery
When subquery returns one row ( one value ), it is called single-row subquery.

Ex:
I want to display all the employees who are having sal  greater than ALLEN  sal.

Step 1: we need to find ALLEN sal
Query to get ALLEN sal

Select  sal from emp
Where ename=’ALLEN’ ;
Output: 1600

Step 2:
Now, we want all the rows who are having sal > 1600

Query:
Select * from emp
Where sal >  (Select  sal from emp
Where ename=’ALLEN’ );

Subquery is highlighted in blue color.

Always subquery should be used in parenthesis.
As we know, subquery is executed first , it returns 1600.
And then parent query  will display all the rows who are having sal >  ALLEN SAL

Ex 2:
I want to display the rows who are having sal >  ALLEN sal and  job same as JONES job.

Select * from emp
Where sal > ( select sal from emp
                        Where ename=’ALLEN’ )
    And job =  ( select job from emp
                          Where ename=’JONES’);
          
One parent query can have any multiple subqueries.

Ex 3:
Write a query to display  details of an employee who is having highest sal.

Step 1: Find the highest sal.
Select max(sal) from emp;   --  output  5000

Step 2: 
Select * from emp
Where  sal = ( select max(sal) from  emp );

In all the above examples, subquery is returning only one row ( one value ).
Hence they are called  single-row subquery.

2) Multiple-row subquery
When subquery returns more than one row (   more than one value ),
They are called multiple-row subquery.

Ex:
Select * from emp
Where  sal >  ( select  sal from emp
                          Where deptno = 30);
As there are six employees in deptno 30,  the subquery returns six values.

It is something like

Select * from emp
Where sal >  (  2850, 1600, 1250, 1250,1500, 950 );

But, the above query will fail.
Operators like  = , >  , >=  , < , <= , <>  expects only one value in right hand side.
Here our subquery is returning 6 values.  Hence the query will give us error.

Note:
For multiple row subqueries, we need to use multiple row operators.

There are three multiple-row operators

1. IN
2. ANY
3. ALL

First I want to discuss about ALL operator.

ALL Operator:

Select * from emp
Where sal >ALL ( select sal from emp
                              Where deptno=30);

When we run the subquery, we know it returns six values.

It is something like

Select * from emp
Where sal >ALL ( 1600, 1250, 2850,  1250,1500, 950 );

Do you think KING  who is having sal 5000 is displayed?
Definitely YES, as 5000 is greater than all the  six values returned by the subquery.

Do you think FORD who is having sal 3000 is displayed?
Definitely YES, as 3000 is greater than all the six values returned by the subquery.

Do you think CLARK who is having sal 2450 is displayed?
No, 2450 may be greater than 1600 and 1250 , but not greater than 2850.

We get the following four rows in the output, as they are having sal greater than all the six
 values returned by the subquery.

ENAME	SAL
JONES	2975
FORD	3000
SCOTT	3000
KING	5000

Note: In other words, we get the above four rows are they are having sal greater than maximum value of the subquery.

ANY Operator

Select * from emp
Where sal >ANY ( select sal from emp
                              Where deptno=30);

When we run the subquery, we know it returns six values.

It is something like

Select * from emp
Where sal >ANY ( 1600, 1250, 2850,  1250,1500, 950 );

Do you think KING  who is having sal 5000 is displayed?
Definitely YES, as 5000 is greater than any of the  six values returned by the subquery.

Do you think CLARK who is having sal 2450 is displayed?
Definitely YES, as 2450 is  greater than any of the six values returned by the subquery.

Do you think WARD who is having sal 1250 is displayed?
Definitely YES, as 1250 is  greater than any of the six values returned by the subquery.
We get the following twelve rows in the output, as they are having sal greater than any of  the six  values returned by the subquery.

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7839	KING	PRESIDENT		11/17/1981	5000		10
7788	SCOTT	ANALYST	7566	12/9/1982	3000		20
7902	FORD	ANALYST	7566	12/3/1981	3000		20
7566	JONES	MANAGER	7839	4/2/1981	2975		20
7698	BLAKE	MANAGER	7839	5/1/1981	2850		30
7782	CLARK	MANAGER	7839	6/9/1981	2450		10
7499	ALLEN	SALESMAN	7698	2/20/1981	1600	300	30
7844	TURNER	SALESMAN	7698	9/8/1981	1500	0	30
7934	MILLER	CLERK	7782	1/23/1982	1300		10
7654	MARTIN	SALESMAN	7698	9/28/1981	1250	1400	30
7521	WARD	SALESMAN	7698	2/22/1981	1250	500	30
7876	ADAMS	CLERK	7788	1/12/1983	1100		20

Note: In other words, we get the above twelve rows are they are having sal greater than minimum  value of the subquery.

IN Operator

In Operator will display the rows who are which are matching with the list of values provided.

Ex:

Select * from emp where job in ( ‘CLERK’, ‘MANAGER’);

In the output, we get all the employees who are CLERK’s and MANAGER’s.

Similarly,

Select * from emp
Where sal IN ( select sal from emp
                              Where deptno=30);

When we run the subquery, we know it returns six values.

It is something like

Select * from emp
Where sal  IN  ( 1600, 1250, 2850,  1250,1500, 950 );

We get the row who are having sal matching with any of the value of the subquery.

Multiple-column subquery:

When subquery returns more than one column, it is called multiple-column subquery.
IN operator is used with multiple-column subquery.

Ex:

Select * from emp
Where  ( sal, job ) IN  ( select sal, job from emp
                                        Where deptno=30);

In the above example, as subquery is returning two columns, parent query should also compare both the columns. Pair-wise comparisons are done.

The following are the values returned  by the subquery.

SAL	JOB
2850	MANAGER
1600	SALESMAN
1250	SALESMAN
1250	SALESMAN
1500	SALESMAN
950	CLERK

Now, the parent query will return the rows when combination of values are matching.

To be more clear,  consider employee  JONES
His sal and job values are

2975

MANAGER

JONES will not be displayed in the output, as combination of values ( 2975, MANAGER )
Is not present in the subquery.
We get the following output,

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7698	BLAKE	MANAGER	7839	5/1/1981	2850		30
7499	ALLEN	SALESMAN	7698	2/20/1981	1600	300	30
7654	MARTIN	SALESMAN	7698	9/28/1981	1250	1400	30
7521	WARD	SALESMAN	7698	2/22/1981	1250	500	30
7844	TURNER	SALESMAN	7698	9/8/1981	1500	0	30
7900	JAMES	CLERK	7698	12/3/1981	950		30

1. Co-related subquery

When subquery is executed in-relation to parent query, it is called co-related subquery.

Views

View are logical representation of data from one or more than one table

Types of view

• Simple views
• Complex views
• Read-only views
• With check-option views

Syntax to create view:

Create view < view_name>
 As  < select  stmt >;

What is the need for view?

Lets say I want to display the rows from emp which satisfies following conditions
Condition 1:  employee should be working in deptno 30
Condition 2:  job should be SALESMAN
Condition 3: sal should be greater than 1400

So, your query should be
Select * from emp
Where deptno =30  AND  job =’SALESMAN’ AND  sal > 1400;

If you always want to retrieve the data which satisfies the above three conditions, better to create a view  so that your work is simplified.

Ex:
Create view  v1
As  select  * from emp
       Where deptno =30  AND  job =’SALESMAN’ AND   sal >  1400; 

View created.
In the above example , the name of the view is V1
Now, when you want to retrieve the data which satisfies  the above three conditions,
You query will be very simple ie

Select  *  from v1;

Table which is used for creating the view is called as base table.
In the example, name of the view is v1 and the table emp is called base table.

• Simple  views

When views are created using one base table it is called simple view.
The above example is a simple view.

Few more examples

Create view v10
As select  *  from emp
     Where deptno = 10;

Create view v20
As  select  *  from emp
      Where deptno = 20;

Create view v30
As  select * from emp
       Where deptno =30;

I am logically classifying the data of emp table into 3  views ie v10, v20, v30

So, when I write 
Select * from v10 ;  
I get the data of employees working in deptno 10.

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7782	CLARK	MANAGER	7839	9-Jun-81	2450		10
7839	KING	PRESIDENT		17-Nov-81	5000		10
7934	MILLER	CLERK	7782	23-Jan-82	1300		10

Similary 

Select  *  from v20;

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-Dec-80	800		20
7566	JONES	MANAGER	7839	2-Apr-81	2975		20
7788	SCOTT	ANALYST	7566	9-Dec-82	3000		20
7876	ADAMS	CLERK	7788	12-Jan-83	1100		20
7902	FORD	ANALYST	7566	3-Dec-81	3000		20

Select  *  from   v30;

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7499	ALLEN	SALESMAN	7698	20-Feb-81	1600	300	30
7521	WARD	SALESMAN	7698	22-Feb-81	1250	500	30
7654	MARTIN	SALESMAN	7698	28-Sep-81	1250	1400	30
7698	BLAKE	MANAGER	7839	1-May-81	2850		30
7844	TURNER	SALESMAN	7698	8-Sep-81	1500	0	30
7900	JAMES	CLERK	7698	3-Dec-81	950		30

All the above views as they are created using one base table, it is Simple views.

Can we perform DML operations on simple view?
Lets try

Insert into v30 values ( 1111, ‘AAA’, ‘CLERK’ , 7698 , ’11-jan-1981’, 1000, 100, 30);

The response is
1 row created.

So, we can perform DML operations on simple views.
When we perform DML operations on simple views, the base tables will be effected.
The above row which we have inserted into the view v30 , will be available in the emp table.

Similarly, when we update the data in the simple view , the base table is updated.
When we delete from the simple view, the data is deleted from the base table.

Note:

• Views does not occupy  memory.
• When we write a select stmt on a view, we get the data from the base table.

Can we see the list of all the view?
Yes, the query is

Select  view_name  from  user_views;
User_views  is an example of data dictionary tables.

Look at this example

Create view  v35
As  select empno, ename , sal  from emp
        Where deptno =30;

Select * from v35;
Output

EMPNO	ENAME	SAL
7499	ALLEN	1600
7521	WARD	1250
7654	MARTIN	1250
7698	BLAKE	2850
7844	TURNER	1500
7900	JAMES	950

Now, I want to add new column JOB  in the view  V35.

Can we add or drop columns in a view?
Yes, it is possible by using create or replace clause

Ex
Create or replace view v35
As  select  empno, ename , sal, job
From emp
Where deptno = 30;

Select * from v35;

Output

EMPNO	ENAME	SAL	JOB
7499	ALLEN	1600	SALESMAN
7521	WARD	1250	SALESMAN
7654	MARTIN	1250	SALESMAN
7698	BLAKE	2850	MANAGER
7844	TURNER	1500	SALESMAN
7900	JAMES	950	CLERK

By using create or replace,  we can  add columns, remove columns as well as change where conditions of the view.

1. Complex Views

When views are created using multiple base tables, it is called complex views.

Ex:

Create view  v40
As   select e.empno, e.ename, e.sal, e.deptno, d.dname , d.loc
from emp e, dept d
where e.deptno = d.deptno;

When we write 
Select  *  from v40;

EMPNO	ENAME	SAL	DEPTNO	DNAME	LOC
7782	CLARK	2450	10	ACCOUNTING	NEW YORK
7839	KING	5000	10	ACCOUNTING	NEW YORK
7934	MILLER	1300	10	ACCOUNTING	NEW YORK
7566	JONES	2975	20	RESEARCH	DALLAS
7902	FORD	3000	20	RESEARCH	DALLAS
7876	ADAMS	1100	20	RESEARCH	DALLAS
7369	SMITH	800	20	RESEARCH	DALLAS
7788	SCOTT	3000	20	RESEARCH	DALLAS
7521	WARD	1250	30	SALES	CHICAGO
7844	TURNER	1500	30	SALES	CHICAGO
7499	ALLEN	1600	30	SALES	CHICAGO
7900	JAMES	950	30	SALES	CHICAGO
7698	BLAKE	2850	30	SALES	CHICAGO
7654	MARTIN	1250	30	SALES	CHICAGO

The view v40, is created using more than one base table, it is a complex view.

Note:
We cannot perform DML operations on complex views.

When a view is created using arithmetic operations or functions or group by clause, it is also called as complex views.

Ex:
Create view v50
As  select  empno, ename , sal, sal*12 annual_sal , deptno from  emp;

Select *  from  v50;

EMPNO	ENAME	SAL	ANNUAL_SAL	DEPTNO
7369	SMITH	800	9600	20
7499	ALLEN	1600	19200	30
7521	WARD	1250	15000	30
7566	JONES	2975	35700	20
7654	MARTIN	1250	15000	30
7698	BLAKE	2850	34200	30
7782	CLARK	2450	29400	10
7788	SCOTT	3000	36000	20
7839	KING	5000	60000	10
7844	TURNER	1500	18000	30
7876	ADAMS	1100	13200	20
7900	JAMES	950	11400	30
7902	FORD	3000	36000	20
7934	MILLER	1300	15600	10

Annual_Sal  column is not present in the base table, we are calculating it in the view.

Lets have another example of complex view
Ex
Create view v60
As  Select deptno, sum(sal)  sum_sal  
From emp
Group  by  deptno;

Select * from v60;
Output:

DEPTNO	SUM_SAL
30	9400
20	10875
10	8750

V60, is also an example of complex views. As we have used group functions / group by clause.

Note:
We cannot perform DML operations on complex views.

1. Read only views:

We can only read the view.
Reading is executing select stmt on the view.
We cannot perform write operations (  DML Operations) on these views.

Ex:
Create view v70
As  select empno, ename , sal 
from emp
with read only;

Now, we cannot perform insert, update and delete operations on View v70.
We can execute only select stmt on the view.

1. With Check-option view

DML operations are allowed only when where clause is satisfied.

Ex:
Create view v80
As select  empno, ename , sal 
From emp
Where sal > 2000
With check option;

Select  * from  v80;

Output

EMPNO	ENAME	SAL
7566	JONES	2975
7698	BLAKE	2850
7782	CLARK	2450
7788	SCOTT	3000
7839	KING	5000
7902	FORD	3000

Consider the two insert commands
Insert into v80  values ( 1111, ‘AAAA’, 1200);  --error  with check option – where clause violation
Insert into  v80  values ( 2222, ‘BBBB’, 2100);  --  valid

Consider the two update commands

Update  v80 set sal = 3100
Where empno = 7566;  --  valid

Update v80  set  sal = 1000
Where empno = 7698;  --  error with check option – where clause violation

When we perform DML operations on WITH CHECK OPTION views, it validates the where clause.

DML operations are allowed only when WHERE clause is satisfied.

Indexes
Indexes are used to improve the performance of select  statements.
Look at this query
Select  *  from  emp
Where  sal  > 2000;

As our emp table is having 14 rows,  it compares row by row.

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	2-Apr-81	2975		20
7654	MARTIN	SALESMAN	7698	28-Sep-81	1250	1400	30
7698	BLAKE	MANAGER	7839	1-May-81	2850		30
7782	CLARK	MANAGER	7839	9-Jun-81	2450		10
7788	SCOTT	ANALYST	7566	9-Dec-82	3000		20
7839	KING	PRESIDENT		17-Nov-81	5000		10
7844	TURNER	SALESMAN	7698	8-Sep-81	1500	0	30
7876	ADAMS	CLERK	7788	12-Jan-83	1100		20
7900	JAMES	CLERK	7698	3-Dec-81	950		30
7902	FORD	ANALYST	7566	3-Dec-81	3000		20
7934	MILLER	CLERK	7782	23-Jan-82	1300		10

It compares 1st row salary   800 > 2000  , condition is FALSE.
It compares 2nd row salary  1600 > 2000, condition is FALSE.
It compares 3rd row salary  1250 > 2000, condition is FALSE.
It compares 4th row salary  2975 > 2000, condition is TRUE.

It compares all the rows, finally rows which are satisfying the condition are retrieved.
The point it the no of comparisons.
As the emp table is having 14 rows, it performs 14 comparisions.

Assume if emp table is having  1 million rows,  query need to perform 1 million comparisons.
Performing 1 million comparisons takes long time, thus query performance is decreased.

When there are more no of rows, query needs to perform more no of comparisons.
When there are more no of comparisons, the performance is decreased.

So, to increase the performance, indexes need to be used.

Types of indexes

1. Simple Index
2. Composite index

1. Simple Index

When index is created on single column, it is simple index.

Syntax:
Create index  < index_name>  on  table_name ( col_name);

Look at this query
Select  *  from  emp
Where  sal  > 2000;

Assume emp table is having 1 million records, the query performance is slow.

Look at the where clause of the query, sal column is used.
Index should be created on columns which are used in where clause.
To improve the performance, we need to create index on sal column.

Ex:
Create index  ID1 in emp (sal);

After creating the index, if you run the query

Select  *  from  emp
Where  sal  > 2000;

The performance of the query  is increased.

When we create index, a separate structure is created with two columns.
First column is the ROWID.
Second column is the column on which index is created.
Rows in the index are arranged in ascending order of the indexed column.

We can visualize the index as follows

ROWID	SAL
AABqJuAB+AAA2O4AAA	800
AABqJuAB+AAA2O4AAL	950
AABqJuAB+AAA2O4AAK	1100
AABqJuAB+AAA2O4AAC	1250
AABqJuAB+AAA2O4AAE	1250
AABqJuAB+AAA2O4AAN	1300
AABqJuAB+AAA2O4AAJ	1500
AABqJuAB+AAA2O4AAB	1600
AABqJuAB+AAA2O4AAG	2450
AABqJuAB+AAA2O4AAF	2850
AABqJuAB+AAA2O4AAD	2975
AABqJuAB+AAA2O4AAH	3000
AABqJuAB+AAA2O4AAM	3000
AABqJuAB+AAA2O4AAI	5000

Now, when we run the query

Select * from emp
Where sal > 2000;

Instead of searching row by row in the table, searching is done on the index using algorithms
Gets the bulk of ROWID, using which rows are displayed.

1. Composite index

When index is created on multiple columns, it is called composite index.

Look at this query

Select  *  from emp
Where  sal > 2000 and job = ‘MANAGER’;

Look at the where clause of the query.
Sal and job columns are used.
We know, index should be created  on columns which are used in WHERE clause.
As WHERE clause contains SAL and JOB columns,  we need to create index on two columns.
Ex:
Create Index  ID2 on   emp (  sal,  job );

These kind of indexes which are created on multiple columns, are called as composite index.

Query to see list of all the indexes
Select  index_name , table_name
From user_indexes;

Query to see the list of indexes and its corresponding table names and column names
Select index_name , table_name , column_name
From user_indexes;

Index can also be categorized in two types.

1. Unique Index
2. Non-Unique index

1. Unique Index

When index column contains unique values ( no duplicates ) , it is called Unique index.
Unique index is created automatically, when we create a table with primary key constraint or unique constraint.

Ex

Step 1:  Run the query to see the list of all the indexes.

Select index_name , table_name , column_name
From user_indexes;

Take note of the record count.

Step 2: Create a table with primary key constraint

Create table student ( sno  number(3) PRIMARY KEY,
                                    Sname  varchar2(10),
                                    Marks  number(3));

Step 3: Again run the query to see list of all the indexes.

Select index_name , table_name , column_name
From user_indexes;

Take note of the record count.
Record count in step 3 is one more than record count in step 2.

So, we never create unique index manually, It is created automatically when table is create with primary key or unique constraint.

1. Non-unique index

When indexed column contains duplicates, it called as Non-unique index.

Ex:
Create index  ID1 in emp (sal);

There may be two or more employees having same salary.
So the indexed column sal can contain duplicates,  it is called Non-unique index.
Index ID1 is an example of simple index as well as Non-unique index.

Function based index
When index is created using functions, it is called function based index.

Ex
Create index ID3 on  emp ( lower(ename) );

Index ID3 is used when the function lower is used in where clause.
I.e.  select  *  from emp
       Where lower (ename)=’king’;

Note: Index is an object which is used to improve the performance of select stmt.

Sequences

Sequence is an object which is used to generate numbers.

Syntax
Create  sequence  <seq_name>
Start  with < value >
Increment by < value >;

Ex
Create  sequence  seq1
Start with 1
Increment  by 1;

In the above example, name of the sequence  is seq1. It generated numbers 1,2,3…..so on

How can we use the sequence?

I want to create a table student18, and insert 5 rows into it.

            student18

sno	sname	marks
1	arun	40
2	vijay	65
3	amit	91
4	karthik	98
5	vishal	77

Observe, the column sno,   the values are 1,2,3,4,5.
Instead of hard coding the values, in the insert command, I can use the sequence  seq1 which we have created.

Note
Nextval is a pseudo column which is used to generate the numbers.

Ex 1
Create table  student18  ( sno  number(3),
                                          Sname  varchar2(10),
                                          Marks  number(3));
Insert  into  student18  values ( seq1.nextval, ‘arun’, 40 );
Insert into  student18  values ( seq1.nextval, ‘vijay’, 65 );
Insert into  student18  values ( seq1.nextval, ‘amit’, 91 );
Insert into  student18  values ( seq1.nextval, ‘karthik’, 98 );
Insert into  student18  values ( seq1.nextval, ‘vishal’, 77 );

In the above insert commands, we are not hard coding the  values of the sno column.
We are using  <seq_name>.nextval  ,  it will generate the number.

Note
Currval is a pseudo column which is used to know the latest number generated.

Select  seq1.currval  from  dual;  --  5

So, when ever we want numbers to generated automatically, we use sequence.

Like credit card numbers, mobile numbers, sim card numbers, bank account  numbers etc;

Lets look at  few more examples

Ex 2
Create sequence seq2
start with 1000
increment by 1
maxvalue 5000;

We can restrict sequence to a maximum number by using maxvalue option.

Ex 3

Create sequence seq3
Start with 1
Increment by 1
Maxvalue 7000
Cycle;

After sequence reaching maxvalue, it again starts generating number 1,2,  so on
Sequence with cycle option, cannot be used for primary key columns as it may generate duplicate values.

Query  to see list of all the sequences

Select sequence_name  from user_sequences;

If you do not want the sequence, we can drop it.

Syntax
Drop sequence  < seq_name >;

Ex
Drop sequence  seq1;

Imp

There are two pseudo columns, related to sequences.

1. Nextval
2. Currval;

Nextval  will generate the next number.
Currval will return the latest  number generated.

 Synonyms

Synonym is an alternate ( extra name ) name given to an object.

Syntax
Create synonym < synonym_name>  for < table_name> ;

Ex
Create  synonym  e1  for  emp;

From now, to access  table emp, we can use the synonym  e1
Like
Select * from   e1;

Insert into e1 ( empno, ename , sal, deptno )  values ( 444,’AAA’, 2000, 10);

We can use synonyms not only for select stmt, for DML commands also.

What is the advantage of creating synonym?
Generally, table name will be long.
Instead of using lengthy tables names in the SQL queries, we can use synonyms.

What is difference between table aliases and  synonyms?
Do you remember, we have learnt table alias concept in joins, which helps in reducing the length of the query.
Table alias is temporary , where as synonym are permanent.

Query to see list of synonyms
Select synonym_name  from user_synonyms;

When you do not want synonym, we can drop it.
Syntax
Drop synonym  < synonym_name> ;

Ex
Drop synonym  e1;