DATA DEFINITION LANGUAGE:

This language is used to define the structure of the object(table) and allows the user to make changes on the structure.
1)create
2)alter --> add
modify
drop
3)drop

sql > create table <tablename>(colname datatype(size),colname datatype(size),------------);
note:
1)In a user the table name should be unique
2)table name should start with alphabetic character.
3)No special characters and space is allowed in the table name other than ($,_)
4)The size of table is dynamic
5)The no. of columns allowed in a table are 'n'.(256)
6)No column name in a table should be repeated

ex:
create table employee10(eno number(3),ename varchar2(20),esal number(7,2),sex char(1),
doapp date,empdesc long);

to describe the structure of the table:

sql>desc <tablename>;
or
sql>describe <tablename>;

alter (add) -- to add a new column to the existing table

sql> alter table<tablename> add(colname datatype(size),colname datatype(size),-------);
ex:
alter table employee10 add(deptno number(5),dname varchar2(20),company varchar2(20));

alter(modify) -- to modify the existing columns of the table
(table is preferred to be empty)
sql>alter table <tablename> modify(colname datatype(size),colname datatype(size),------);
ex:
alter table employee10 modify(eno varchar2(5),esal number,empdesc varchar2(50));

alter(drop) -- to drop the column of the table
sql>alter table <tablename> drop column <colname>;
ex:
alter table employee10 drop column sex;

note: cannot drop all the columns of the table
cannot drop the column which has reference in another table.

drop -- to drop the table

sql>drop table <tablename>;

it destructs the complete table irrespect of whether the table is empty or it has data.
cannot drop the table which has another table as dependent

DML(DATA MANIPULATION LANGUAGE)

This language defines the data of the table i.e insertion,retrieval,deletion and updation.

insert
select(DRL)
update
delete

sql>insert into <tablename> values(value1,value2,value3.........);

ex:
create table employee10(eno number(3),ename varchar2(20),esal number(5,2),esex char(1),doapp date);

insert into employee10 values(101,'sunanda',565.67,'f','17-apr-05');

note: if the columns datatype is char,varchar,long,date then it should be within single quotations


sql> insert into <tablename> values(&colname1,&colname2,&colname3,---------------);
& -- represents standard input
sql>insert into employee10 values(&eno,'&ename',&esal,'&esex','&doapp');

sql>insert into <tablename>(colname,colname,----) values(&colname,&colname,------);
sql>insert into employee10(eno,esal,doapp) values (&eno,&esal,'&doapp')

DRL(DATA RETRIEVAL LANGUAGE)

sql>select * from <tablename>;
* --> all the rows and columns
sql>select * from employee10;

select with where condition:

sql>select * from <tablename> where condition;
sql>select * from employee10 where ename='sunanda'
sql>select * from employee10 where esex='f';

select with 'selected' columns
sql > select colname,colname,-------- from <tablename> [ where condition]
sql>select doapp,eno,esal from employee10;
sql>select eno,esal from employee10 where esal > 500;

update: to update the existing records

sql>update <tablename> set colname=value1,colname=value2,----- where condition;
sql>update employee10 set eno=777 where ename='aijaz';
sql>update employee10 set eno=999,esal=999.99 where ename='sunanda';

delete -- to delete the records from the table
this doesn't effect the structure of the table
sql>delete from <tablename> [ where condition ];
sql>delete from employee10 where ename='null';
sql>delete from employee10;
cannot delete the Parent record on which child is dependent

TRANSACTION CONTROL LANGUAGE(TCL)
Transaction is defined as the changes made on the database.
1)commit
2)rollback
3)savepoint

commit --> To permanently save the transaction
syntax:
sql> commit;
rollback --> To undo the Previous transaction
syntax:
sql> rollback;
sql> roll;
sql>rollback to savepoint;
Note: rollback will undo the transaction till the last commit;
savepoint --> These are like markers during the transaction
syntax:
sql>savepoint <savepointname>
NOTE:
when Oracle is properly shutdown i.e by 'exit' at sql prompt then AUTOCOMMIT fires.
The entire transaction till the period gets automatically saved permanently.
ALL THE DDL OPERATIONS ARE BY DEFAULT AUTO COMMIT.

DATA CONTROL LANGUAGE:

This languages defines the privileges(rights) given by DBA to a User and the Privileges(rights) given by one user to other on an object i.e table.

privileges are of 2 types:

1)system privileges
2)object privileges

system privileges can be given by DBA.
(connect,resource)

Object privileges can be given by One user to other on Table
(insert,update,select,delete,alter,reference)

To give(grant) the privileges

sql > grant all on <tablename> to <username>;

To takeback(revoke) the privileges

sql> revoke all on <tablename> from <username>;


SIMPLE QUERIES:

1)Write a Query to display the details of MANAGER

select * from emp where job='MANAGER'

2)WAQ to display the details of SALESMAN,CLERK,ANALYST

select * from emp where job='SALESMAN' or job='CLERK' or job='ANALYST';
select * from emp where job in ('SALESMAN','CLERK','ANALYST')

3)WAQ to display the details of employee whose sal is between 2000 and 4000;
select * from emp where sal >=2000 and sal<=4000
select * from emp where sal between 2000 and 4000

4)WAQ to display the employee who don't have commission
select * from emp where comm is null;

5)WAQ to display the employees those who have commission
select * from emp where comm is not null;

6)WAQ to display the employees whose name starts with 'A'
select * from emp where ename like 'A%'

% --> represents any number of characters of any type

7)WAQ to display the employee details whose names 3rd character is 'A'

select * from emp where ename like '__A%';

_(underscore) --> represents a single character of any type

8)WAQ TO display salary,quarterlysalary,halfyearly salary, annual salary of all the employees

select sal Monthlysal,sal*3 QuarterlySal,sal*6 HalfyrlySal,sal*12 AnnualSal from emp;

9)WAQ to display the employees whose hire date is 80's

select * from emp where hiredate between '1-jan-80' and '31-dec-80'
select * from emp where hiredate like '%80'

10)WAQ to display the employee whose name has 'A'

SELECT * FROM EMP WHERE ENAME LIKE '%A%'

CLAUSES:

1)groupby clause--> this clause is used to retrieve similar group of the data from the table.

ex:

waq to display the no of employees,sum of salary,deptno grouped by deptno

select count(*) "No of Employees",sum(sal) "Sum of Salary", deptno from emp group by deptno

waq to display the no of employees,sum of salary,avg of salary based on their job

select count(*) "No of Employees",sum(sal) "Sum of Salary", avg(sal) "Average salary", job from emp group by job

2) Having clause --> This clause should always be preceded by group by. It is used to apply condition for group by clause

1)waq to display the count of employees,sum of their salary of only those dept in which more than 3 employees are working.

select count(*) "No of Employees",sum(sal) "Sum of Salary", avg(sal) "Average salary",job from emp group by job

2)waq to find the no of employees got appointed in 81
SELECT COUNT(*) "NO OF EMPLOYEES",HIREDATE FROM EMP GROUP BY HIREDATE HAVING HIREDATE LIKE '%81';

3)Order by clause:
it is used to display the data of the table in specific order like descending or ascending,. By default it displays in ascending order.

select * from emp order by sal;
select * from emp order by sal desc;


SET OPERATORS:

Set Operators are used to retrieve the data from multiple tables using multiple select statements.

set operators are classified into:
1)union--> retrieves all the records from one or more tables excluding duplicates
2)union all-->retrieves all the records from one or more tables including duplicates
3)intersect-->retrieves all the records which are common among the tables
4)minus--> retrieves all the records which are in first table but not in second

CONDITIONS FOR SET OPERATORS:

1)The No. of Columns and Their Datatypes should be same in all the tables which are used in set Operators.

2)Set Operators will not work for long or long raw datatype

3)As the No. of Tables Increases, No. of select statements increases causes the lengthy sql statement

4)Always the output displayed will be with the column names of the 1st table

syntax:

sql>select * from <tablename1> <setoperator> select * from <tablename2> <setoperator>
select *from <tablename3> <setoperator>...........

note: TO OVERCOME THE DISADVANTAGE OF SET OPERATORS JOINS WERE INTRODUCED.


JOIN:

are used to retrieve the data from multiple tables. The purpose of join is to retrieve the data spread across multiple tables using single select statement, wherein the tables should have some 'logical relationship'.

syntax:

sql>select colname,colname,colname ------- from <tablename1>,<tablename2>,-----
where logical condition;

Mainly Joins are classified into 3 types:

1)SIMPLE JOIN
2)SELF JOIN
3)OUTER JOIN

1)SIMPLE JOIN is the most common type of join used. It is further classified into

a)EQUI JOIN
b)NON EQUI JOIN

A Join which is based on equality(=) is said to be EQUI JOIN

A join which is based on relation operators ( <,>,<=,>=,<>) is said to Non Equi join


ex:
1)WAQ to display empno,ename,sal,job,dname,loc of all the employees

select empno,ename,job,sal,dname,loc from emp,dept where emp.deptno=dept.deptno;

2)WAQ to display empno,ename,hiredate,sal,comm,grosssalary,dname,loc of employees who are 'SALESMAN';

select empno,ename,hiredate,sal,comm,sal+nvl(comm,0) grosssalary,dname,loc from
emp,dept where emp.deptno=dept.deptno and emp.job='SALESMAN';

3)WAQ to display ename,job,sal,deptno,dname of all the employees

select ename,job,sal,deptno,dname from emp,dept where emp.deptno=dept.deptno;

-->error : column ambigously defined

select ename,job,sal,emp.deptno,dname from emp,dept where emp.deptno=dept.deptno;

(or)
select ename,job,sal,dept.deptno,dname from emp,dept where emp.deptno=dept.deptno

NOTE:
TABLE ALIASE:

are used to define the tables with short names. They avoid the lengthy sql statements and prevent ambiguity(confusion).

select e.ename,e.job,e.sal,d.deptno,d.dname from emp e,dept d where e.deptno=d.deptno;

WAQ to display ename,job,grosssalary,deptno,dname,loc of all the employees whose hiredate is 81

select e.ename,e.sal+nvl(e.comm,0),d.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno and e.hiredate like '%81';

NON-EQUI JOIN:

EX:

1) WAQ to display empno,ename,sal,job,dname,loc

SELECT EMPNO,ENAME,SAL,JOB,DNAME,LOC FROM EMP,DEPT WHERE EMP.DEPTNO<>DEPT.DEPTNO;

2)SELECT EMPNO,ENAME,JOB,DNAME,LOC FROM EMP,DEPT

SELF JOIN:

joining of the table to itself is known as self join. The join is performed by MIRRORING the table.

EX:

WAQ TO DISPLAY THE EMPLOYEE DETAILS WORKING UNDER BLAKE

SELECT E.ENAME||' IS THE EMPLOYEE OF '||D.ENAME FROM EMP E,EMP D WHERE
E.MGR=D.EMPNO AND D.ENAME='BLAKE'


WAQ TO DISPLAY THE EMPLOYER OF BLAKE

SELECT E.ENAME||' IS THE EMPLOYEE OF '||D.ENAME FROM EMP E,EMP D WHERE
E.MGR=D.EMPNO AND E.ENAME='BLAKE'

WAQ TO DISPLAY THE EMPLOYEES WHO ARE WORKING UNDER WHOM

SELECT E.ENAME||' IS THE EMPLOYEE OF '||D.ENAME FROM EMP E,EMP D WHERE
E.MGR=D.EMPNO;

OUTER JOIN:

it is the extension of simple join. It returns all the rows which satisfy the join condition as well as returns those rows which are not in one table but in other. Outer join is represented by (+).

OUTER JOIN IS FURTHER CLASSIFIED INTO
1) Left Outer Join
2) Right Outer Join

WAQ TO DISPLAY THE COMPLETE HIERARCY OF EMPLOYEES WORKING UNDER EMPLOYERS

SELECT E.ENAME||' IS THE EMPLOYEE OF '||D.ENAME FROM EMP E ,EMP D WHERE E.MGR(+)=D.EMPNO

SELECT E.ENAME||' IS THE EMPLOYEE OF '||D.ENAME FROM EMP E ,EMP D WHERE E.MGR=D.EMPNO(+)

CROSS JOIN :

SELECT E.ENAME||' IS THE EMPLOYEE OF '||D.ENAME FROM EMP E ,EMP D ;

NOTE:
An Outer Join Predicate may reference to only one table.

SELECT E.ENAME||' IS THE EMPLOYEE OF '||D.ENAME FROM EMP E,EMP D WHERE E.EMPNO(+)=D.MGR(+)

PREDEFINED FUNCTIONS:

Function is defined as a subprogram which performs a specific job.
Function returns an output or return value back to the Main Program.

In sql Predefined functions are classified into 2 types:

1)Single Row Functions
2) Group Functions

Single Row Functions --> The functions which gets executed for each row of a table
Group Function --> The function which gets executed for a group of rows only Once.

Single Row Functions are further classified into
a)Date Functions
b)Character Functions
c)Numeric or Mathematic functions
d)Conversion Functions
e)Miscellenous Functions

Date Functions:

1)sysdate --> returns the current system date
select sysdate from dual;

Note:
Dual is a Pseudo(Virtual) Table.
By default this table is created when Oracle is Installed

2)add_months(date,n) --> returns the new date by adding 'n' months to the given date
select sysdate,add_months(sysdate,-4) from dual;

3)last_day(date) --> returns the last date of the specified date
select sysdate,last_day(sysdate) from dual;

4)months_between(date1,date2) --> returns the difference between to dates in terms of months.
select months_between(sysdate,'03-sep-1981')/12 from dual;

5)round(date,[format]) --> returns the specified date by rounding it according to the format
format -- day --- for days
mon --- for months
yy -- for year

select sysdate,round(sysdate,'day') from dual;

select sysdate,round(sysdate,'month') from dual;

select sysdate,round(sysdate,'year') from dual;

6)trunc(date,[format]) --> returns the specified date by truncating it according to the format
format -- day --- for days
mon --- for months
yy -- for year
select sysdate,trunc(sysdate,'day') from dual

select sysdate,trunc(sysdate,'month') from dual

select sysdate,trunc(sysdate,'year') from dual

The difference between round and trunc is round returns the value based on the nearest value and trunc returns the value of the previous

7)greatest(date1,date2) --> returns the greatest or biggest among the two dates
select greatest(sysdate,'22-oct-2078') from dual

MATHEMATIC FUNCTIONS:

1)abs(no) --> returns the only POSITIVE nos.
select abs(-99878) from dual;
2)sqrt(no) --> returns the square root of the given no.
sqrt cannot be found for -ve nos.
select sqrt(25) from dual;
select sqrt(abs(-81)) from dual
3)power(m,n) --> returns the m to the power of n value
select power(2,3) from dual
4)mod(m,n) --> returns the modulus(remainder) of m divided by n
select sqrt(abs(-81)) from dual;
5)floor(m) --> returns the Real part of the number 'm'
select floor(245.565656565) from dual
select floor(245.99999999) from dual
6)trunc(m,n) --> returns the number 'm' by truncating(removing) it to 'n' decimals
select trunc(245.455667,3) from dual
select trunc(245.4556332342427,5) from dual
7)round(m,n) --> returns the number 'm' by rounding it to nearest value to 'n' decimals
select round(245.4554732342427,3) from dual;
8)sin(no) --> returns the sine value of 'no'
select sin(90) from dual;
9)cos(no) --> returns the cosine value of 'no'
select cos(90) from dual;
10)tan(no) --> returns the tangent value of 'no'
select tan(90) from dual;

CHARACTER FUNCTIONS:

1)length('string') --> returns the length of the string including space
select length('mohd abdul qadir') from dual;
2)initcap('string') --> returns the string by converting the initial character to upper case
select initcap('mohd abdul qadir') from dual;
3)lower('string') --> returns the string in lower case format
select lower('ORACLE IS AN ORDBMS PACKAGE') from dual
4)upper('string') --> returns the string in upper case format
select upper('oracle is an ordbms database') from dual
5)substr(string,m,n) --> returns the string from 'm' position to 'n' no. of characters
select substr('welcome',4,3) from dual
6)replace(string,m,n) --> returns the string by replacing the 'm' character with 'n'
select replace('jack and jill are very jolly','j','b') from dual
select ename,lower(ename),length(ename),initcap(ename) from emp
select replace(ename,'A','Z') from emp;
7) ltrim('string',[format]) --> returns the string by trimming the leading format
select ltrim('yyyyayyyyyyyysayeed','y') from dual;
8)rtrim('string',[format]) --> returns the string by trimming the trailing format
select rtrim('sayeedyyyyyyyyyyyyy','y') from dual

CONVERSION FUNCTIONS:
converting from one datatype to another datatype

1)to_char(date,[format]) --> converts the date data type to character data type according to the specified format
select sysdate,to_char(sysdate,'day/month/year') from dual
select hiredate,to_char(hiredate,'day-month-yyyy') from emp
select hiredate,to_char(hiredate,'day-month-yyyy') "date in words" from emp
select sysdate,to_char(sysdate,'dd/mm/yyyy hh:mi:ss') from dual
select sysdate,to_char(sysdate,'hh24') from dual
select sysdate,to_char(sysdate,'hh12') from dual

2)to_date([format],date) --> converts the character format of date into standard date datatype format [dd-mon-yy].

select to_date('7 march 2005','dd month yyyy') from dual;
select to_date('07 05 2005','dd mm yyyy') from dual
select to_date('march 7 2005','month dd yyyy') from dual
select to_date('&a','dd-mon-yyyy') from dual
where 'a' is a variable

3)to_number('string') --> converts the numeric string into a pure number

select to_number(123) from dual
select to_number('123') from dual
select to_number(substr('bond007',5,3)) from dual
select to_number('01230') from dual

MISCELLENOUS FUNCTIONS:

1) user --> returns the user name currently connected or working
select user from dual;

2) uid --> returns the user's identification number
select user,uid from dual;

Note: uid is the unique number which is randomly genereted when the user logins with a valid user name and valid password.

3)nvl --> null value
select sal,comm,sal+nvl(comm,0) from emp

GROUP FUNCTIONS:

1)min(colname) --> returns the minimum value from the specified colname.
select min(sal) from emp;
select min(ename) from emp;

waq to find the min sal of 'MANAGERS';
select min(sal) from emp where job='MANAGER'

2)max(colname) --> returns the maximum value from the specified colname.
select max(sal) from emp
select max(ename) from emp

3)sum(colname) --> returns the sum of all the colname(only for numeric)
select sum(sal) from emp

waq to find the sum of salary of employees who have commission
select sum(sal) from emp where comm is not null

4)avg(colname) --> returns the average of all the colname
select sum(sal)/14, avg(sal) from emp

5)stddev(colname) --> returns the standard deviation of the specified colname
select stddev(sal) from emp

6)variance(colname) --> returns the variance of the specified colname
select variance(sal) from emp

7)count(*) --> returns the count of no. of rows including duplicate and excluding null
select count(*) from emp

8)count(colname) --> return the count of no.of rows present in the specified col including duplicates excluding null

select count(sal),count(comm) from emp;

9)count(distinct colname) --> returns the count of no. of rows present in the specified col excluding duplicates and null

select count(sal),count(distinct sal),count(comm) from emp;

INTEGRITY CONSTRAINTS:

Constraints are defined as validation rules or restrictions imposed on the table which the data of the table has to follow.
Voilating Constraints will result in errors.

Constraints can be imposed(placed) at two different levels

1)column level constraints
2)table level constraints

Constraint are mainly classified into 3 types:

1)DOMAIN INTEGRITY CONSTRAINT
2)ENTITY INTEGRITY CONSTRAINT
3)REFERENTRIAL INTEGRITY CONSTRAINT

syntax for Column Level constraint:

create table <tablename>(colname1 datatype(size) constraint <userconstraintname> constraint type,colname2 datatype(size)----------------);

syntax for Table Level Constaint:

create table <tablename>(colname1 datatype(size),colname2 datatype(size),----------,
constraint <constraintname> constrainttype(colname with condition),--------------);

note:

1)If the Constraint Name is not defined by the user then Oracle assignes a Default name which is a alphanumeric name.

2)More than one column in a table can have constaints except 'PRIMARY KEY'

3)More than One Constraint can be Placed on the column

1)DOMAIN INTEGRITY CONSTRAINT:

This Constraint is further classified into

a)not null constraint
b)check constraint

a)not null constraint --> when this constraint is assigned to the column then that column doesn't allow 'NULL' values

***NOT NULL IS A COLUMN LEVEL CONSTRAINT

EX:
create table employee(eno number(3) constraint abc_10 not null,ename varchar2(20));

create table employee(eno number(3) not null,ename varchar2(20));

CONSTRAINT INFORMATION IS STORED IN A TABLE UNDER DATA DICTIONARY

DATADICTIONARY-- is a Master table.

sql>desc user_constraints;
sql>select constraint_name,constraint_type,table_name,search_condition from user_constraints where table_name='TABLENAME IN UPPERCASE';

TO DROP THE CONSTRAINT:
syntax:

sql>alter table <tablename> drop constraint <constraint name>;

ex: sql> alter table employee drop constraint abc_10;

TO ADD THE NOT NULL CONSTRAINT:

syntax:

sql>alter table <tablename> modify <colname> constraint <constraint name> not null;

ex:create table employee(eno number(3),ename varchar2(20));
alter table employee modify eno constraint not_null not null;

NOTE: Not Null Constraint is used with 'MODIFY' because it is 'COLUMN LEVEL'

CHECK CONSTRAINT:

When this constraint is imposed on the column of the table then the data inserted or updated on that column should satisfy 'check' value.

create table employee(eno number(3),ename varchar2(20),esal number constraint
esal_chk check(esal<=5000),esex char(1) constraint esex_chk check(esex='f' or esex='m'))


TO ADD CHECK CONSTRAINT:

alter table <tablename> add constraint <constraintname> check(colname with condition);

ex:)alter table employee add constraint esal_chk check(esal >=3500 and esal<=5500);

ex:) create table employee(eno number(3),ename varchar2(20),esal number,esex char(1),
constraint esal_chk check(esal<=5000),constraint esex_chk check(esex='f' or esex='m'))

ENTITY INTEGRITY CONSTRAINT:

This constraint doesn't allow duplicate values.
It is further classified into

1)unique constraint
2)Primary key Constraint

1)Unique constraint --> when this constraint is defined on the column of the table then it doesn't allow duplicate values, but it allows 'NULL' values
Null cannot be compared with itself
Null cannot be compared with zero
Null cannot be compared with anything

create table employee(eno number(3) constraint pkg_10 unique,ename varchar2(20));

2)Primary Key constraint --> when this constraint is defined on the column of the table then it neither allows duplicate values nor null values.

PRIMARY KEY CONSTRAINT= UNIQUE CONSTRAINT + NOT NULL

Note:

1) Only one column in a table can be Primary key
2)column with Long and Long Raw datatype cannot be assigned as Primary
3)When a table has Primary Key Column then that table is said to be 'DEFAULT INDEXED'

ex:

create table employee(eno number constraint eno_pk primary key,ename varchar2(20));

A column with unique constraint and not null constraint is never equal to Primary key constraint.

REFERENTRIAL INTEGRITY CONSTRAINTS:

This constraint is used to create RELATION SHIP among the tables.
The Relationship like -- MASTER DETAIL RELATION SHIP (OR)
-- PARENT CHILD RELATION SHIP

REFERENCES AND FOREIGN KEY are the reserve words used in creating relationship.

ex:

create table university(univno number(3) primary key,univname varchar2(20),loc
varchar2(20));

create table student(sno number primary key,sname varchar2(20),course varchar2(20),
feepaid number,unid number constraint ref10 references university(univno));

note: 'NULL' value is accepted by the foreign key.

TO ADD THE FOREIGN KEY

alter table student add constraint ref10 foreign key (unid) references university

NOTE:

1)CANNOT DELETE THE PARENT RECORD WHICH HAS CHILD DEPENDENT RECORDS.

2)CANNOT DROP THE PARENT TABLE

ON DELETE CASCADE:

When this clause is defined on the CHILD TABLE then it allows to delete the Parent records even though the child records are dependent on it

alter table student add constraint ref100 foreign key(unid) references university(univno)
on delete cascade;

DEFERRABLE INITIALLY DEFERRED:

When this clause is defined on the CHILD TABLE then the constraint voilation is checked during commit.

alter table student add constraint ref25 foreign key(unid) references university(univno)
on delete cascade deferrable initially deferred;

Note:

When a table is created from an existing table which has constraints defined then the
newly created table will get only 'NOT NULL' and 'PRIMARY KEY CONSTRAINT'

sql>create table <newtablename> as select * from <existing tablename>;

LOCKS:

Lock is a mechanism which is used to protect the data of the table from destruction when accessed by multiple users concurrently(at the same time).

Lock are classified into 2 types

1)row level locks --> which is imposed by Oracle By default.

2)Table level locks --> Lock which is imposed on the entire table by the user.

In table level locks the 3 different modes are
1) share mode
2) share update mode
3) exclusive mode

syntax:

sql>lock table <tablename> in <share/share update/exclusive> mode;

LOCK GETS RELEASED AFTER THE TRANSACTION i.e either after COMMIT OR ROLLBACK;

share mode:
This mode allows the user to only retrieve the data.No DML Operations are allowed on the Locked table.
But the User can even Lock the table.

share update mode:
This mode allows the user to retrieve,insert,update and delete the records but the operations should not be on the 'RECENTLY UPDATED RECORDS'

exclusive mode:
This mode is same as SHARE MODE but it doesn't allow the user to LOCK back the table.

TABLE PARTITIONS:

Partition is splitting the table into different physical storage locations.

adv:
Data Security
Mainly helps in Back up Processing and Data Recovery.

Partitions are classified into 2 types:
1)Range Partition
2)Hash Partition

Range Partition --> The Partition which is Based on 'RANGE' of values
It is mainly used for finite size of data
Hash Partition --> The Partition which is Based on 'HASHING' functions
it is mainly used for infinited size or very large size of data.

Note:
1)An UnPartitioned table cannot be Partitioned
2)Column with Long and Long raw datatype cannot be assigned as Partitioning cols.
3)Only on Numeric cols Partitions can be created

syntax for Range Partition:

create table <table name>(colname datatype(size),---------------)
partition by range (colname) (partition <partitionname> values less than (value),-----------);

note:
The record inserted should not be greater than the highest partition value

ex:
create table employee(eno number primary key,ename varchar2(20),esal number)
partition by range (esal) (partition p1 values less than (5000),partition p2 values
less than (10000));

TO RETRIEVE THE DATA FROM PARTITION:

sql>select * from <tablename> partition (partition name);
sql>select * from employee partition (p2)

syntax for HASH Partition:

create table <tablename> (colname datatype(size),-------------------)
partiton by hash (colname) ( partition <partitionname>,partition <partition name>,--------);

create table bank(accno number primary key,accname varchar2(20),amt number)
partition by hash (amt) ( partition h1,partition h2,partition h3);

Partitions information is stored in :

sql> desc user_tab_partitions;

sql>select table_name,partition_name,high_value,tablespace_name from user_tab_partitions where table_name='TABLE NAME IN UPPERCASE'


PARTITION MAINTENANCE:

1)ADD PARTITION:

sql>alter table <tablename> add partition <partition name> values less than (value);
sql>alter table employee add partition p3 values less than (15000);
note: Newly created partition should always be greater than the existing highest partition value

2)TRUNCATE PARTITION:
sql> alter table <tablename> truncate partition <partition name>;
sql>alter table employee truncate partition p3;

TRUNCATE will remove the complete data from the partition.
Rollback will not work on Truncate

3)MERGE TWO PARTITONS:
sql>alter table <tablename> merge partitions <pname1>,<pname2> into <newpname>;
sql>alter table employee merge partitions p2,p3 into partition p10;
note: Only Adjacent Partitions can be Merged.

4)SPLIT PARTITION:
sql>alter table <tablename> split partition <pname> at (value) into (partition pname1,partiton pname2);
sql>alter table employee split partition p10 at (10000) into ( partition p11,partition p12);

5)DROP PARTITON:
sql> alter table <tablename> drop partition <partition name>;
sql>alter table employee drop partition p11;

cannot drop the Only(all) Partitions of the Table

SUB QUERIES:

It is defined as query within another query.
Inner query is said to be child query.
Outer query is said to be the Parent query.

Child query executes first and returns the value to the Parent query.
Always the inner or child query should be placed within normal brackets

If the Inner Query returns more than one value to the Parent query then
use(in,any,all) operators.

syntax:

sql>select * from <tablename> where colname=(select colname from <tablename)

ex:

waq to find the details of the highest paid employee
select * from emp where sal=(select max(sal) from emp);

waq to find the details of the lowest paid employee
select * from emp where sal=(select min(sal) from emp);

waq to find employees who are working with 'ALLEN'
select * from emp where deptno=(select deptno from emp where ename='ALLEN');

waq to find employees who are collegues of 'ALLEN'
select * from emp where job=(select job from emp where ename='ALLEN');

waq to find employees who are collegues of 'ALLEN' excluding 'ALLEN'
select * from emp where job=(select job from emp where ename='ALLEN') and
ename<>'ALLEN';

waq to find the employees whose salaries are less than min(sal) of employes of dept=10
select * from emp where sal < ( select min(sal) from emp where deptno=10);

waq to display the employees who are working in sales department
select * from emp where deptno=(select deptno from dept where dname='SALES')

waq to find the employees whose salary is less than the salary of employees of dept=10
select * from emp where sal < ( select sal from emp where deptno=10);
-->error: Inner Query returns more than one value

in--> returns the values within the list
select * from emp where sal in ( select sal from emp where deptno=10);

any--> any value satisfiying from the list
select * from emp where sal < any ( select sal from emp where deptno=10)

all--> all the values satisfying from the list
select * from emp where sal < all ( select sal from emp where deptno=10)

MULTIPLE SUB QUERIES:

Subqueries which are associated with logical Operators(and,or,not) are known as Multiple Subqueries.

ex:

1)waq to display the employees whose salary is greater than SMITH salary and less than BLAKE salary

SELECT * FROM EMP WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME='SMITH') AND SAL < (SELECT SAL FROM EMP WHERE ENAME='BLAKE');

2)waq to display the employees who are working under BLAKE and who are collegues of ALLEN

SELECT * FROM EMP WHERE MGR=(SELECT EMPNO FROM EMP WHERE ENAME='BLAKE') AND JOB=(SELECT JOB FROM EMP WHERE ENAME='ALLEN');

3)waq to display the employee details who are collegues of MARTIN and the employees who are collegues of JONES excluding MARTIN AND JONES

SELECT * FROM EMP WHERE JOB IN(SELECT JOB FROM EMP WHERE ENAME='MARTIN' OR ENAME='JONES') AND ENAME<>'MARTIN' AND ENAME<>'JONES' ;

SELECT * FROM EMP WHERE JOB IN ( SELECT JOB FROM EMP WHERE ENAME='MARTIN' OR ENAME ='JONES') AND ENAME NOT IN('MARTIN','JONES')


NESTED SUBQUERIES:

it is defined as subquery within an another sub query.

syntax:

select * from <tablename> where colname=(select colname from <tablename> where colname=(select colname..........................................................

waq to display the employees whose salary is greater than the employee of sales department

SELECT * FROM EMP WHERE SAL > ALL ( SELECT SAL FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME='SALES'))

SELECT * FROM EMP WHERE SAL > ( SELECT MAX(SAL) FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME='SALES'))

CORRELATED SUB QUERIES:

it is defined as a sub query in which for every execution of sub query the Parent query executes simultenously.

ex:

waq to display the employees whose sal is greater than the avg(sal) of their departments

select * from emp e where sal > ( select avg(sal) from emp where e.deptno=deptno);

waq to display the employee details who got the second highest salary

select sal from emp e where 2 in ( select count(distinct(sal)) from emp where
e.sal<=sal);

to display the nth highest salary
select sal from emp e where &n in(select count(distinct(sal)) from emp where
e.sal<=sal)

DATABASE OBJECTS IN ORACLE:

Database Objects are the objects which are stored in database and can be used when required(reusability).

In Oracle the database objects are classified into

a)synonym
b)sequence
c)view
d)index
e)cluster
f) User

Synonym:

It is a database Object which is stored in database. Synonym is defined as an aliase name or an alternate name given to the table.

adv:

1)To minimize the lengthy sql statements
2)To hide the tablename i.e data security

synonym are of 2 types
1)public synonym --> created by DBA
2)private synonym--> created by User

syntax:

sql> create [public/private] synonym <synonym name> for <tablename>;

Note: Synonym is by default private

To drop the synonym:

sql> drop synonym <synonymname>;

To retrieve the Information about Synonyms created on different tables

sql> desc user_synonyms;

sql>select * from user_synonyms;

Note:

1)If the table is dropped the synonym exist but without translation
2)A synonym can be created for a non existing table
3) If the table is renamed then the synonym exist but without translation.
4) alter(add,modify,drop) command will not work on synonym
5) Locks will work in the same manner as of on the table.

ex:

create table vendor_master_details(vencode number(3),venname varchar2(20));

create synonym ven for vendor_master_details;

sequence:

Sequence is a database object which is used to generate sequential integer values.

syntax:

create sequence <sequencename>
start with n
increment by n
maxvalue n
minvalue n
cycle/nocycle
cache n;

ex:
create table student(sno number,sname varchar2(20));

create sequence seq1
start with 1
increment by 1
maxvalue 10;

insert into student values(seq1.nextval,'&sname');

All the Information about the sequence is stored in
sql>desc user_sequences;

NOTE:

next_val --> represents the next value generated by the sequence which is in cache
curr_val--> represents the current value generated
cache -- size should be less than the Max value
Minvalue should be less than the max value.

ex:

create sequence seq1
start with 1
increment by 1
maxvalue 10
minvalue -5
cycle
cache 11;

select seq1.nextval from dual;

VIEW:

view is defined as virtual table(no real).
view is said to be a stored select statement.

adv:
To hide the tablename as well as the data of the table.

view are classified into 2 types

1)simple view
2)complex view

simple view --> view associated with the select statement of the single table
complex view --> view associated with the select statement of the multiple tables
i.e view with join

note: complex view cannot be updated(insert,delete,update) using sql.
complex view can be updated in pl/sql using 'instead of triggers'

syntax:

sql > create [force] view <viewname>(column aliase........) as select colname1,colname2,----
from <tablename> [where condition] [with read only];

ex:
create view v1 as select * from emp10 where job='MANAGER';

Note:

When a new record is inserted into the view and if it is based on the view condition then only the view is updated else the view does'nt get updated and the record gets inserted into the main table.

Whenever transaction is done through view auto commit fires on the actual table.

ex: view with column aliasing(RENAMING THE ACTUAL COLUMN NAMES)

create view v1(eno,empname,design,dateofapp,pay) as select empno,ename,job,
hiredate,sal from emp where deptno=10

force view:

A view which is based on a NON EXISTING TABLE

sql>create force view s1 as select * from student where res='p';

sql>create table student(sno number primary key,sname varchar2(20),res char(1));

insert the records into the student table.

*** note:
The force view will gets its structure or description only when select statement is performed on the force view

READ ONLY VIEW:

When a view is created with 'read only' then no manipulations can be done on that view. It supports only select command.

create view s11 as select * from student where res='p' with read only;

note:

More than one view is allowed on a table.
view can be created even on a view

complex view:

create view compview1 as select empno,ename,job,sal,comm,d.deptno,dname from emp e,dept d where e.deptno=d.deptno and e.job='MANAGER';

CLUSTER:

cluster is an optional method of storing the data. It stores the common columns among the tables.

syntax:
create cluster <clustername>(colname datatype(size));

NOTE:
cluster has to be associted with the tables while creating the tables


ex:

create cluster univ_stud(univno number(3));

create table university(univno number(3) primary key,univname varchar2(20),loc
varchar2(20)) cluster univ_stud(univno);

create table student(sno number primary key,sname varchar2(20),course varchar2(20),
univno number(3) constraint ref10 references university(univno))cluster univ_stud(univno);

NOTE:
BEFORE INSERTING THE DATA INTO THE TABLES THE CLUSTER HAS TO BE INDEXED

create index univ_stud_ind on cluster univ_stud;


TO KNOW THE INFORMATION ABOUT CLUSTER:

sql> desc user_clusters;

sql>select * from cluster <clustername>;

note:

cluster doesn't store duplicate data
cluster is independent of the constraints
cluster can be dropped only after dropping the tables
INDEX:

Index is method of retrieving the data of the table in a faster way. It reduces the disc I/O

Note:
The working of Indexes can be felt on very large size of data.

Index are classified into

1)unique index --> doesn't accept duplicate values
2)composite index --> index created on multiple cols
*3)reverse index --> index created in reverse order
4)bitmap index --> index created for a similar type of data

syntax:

create [unique/bitmap] index <indexname> on <tablename> (colname1,colname2---)
[reverse/no reverse]

to know the information about indexes:

sql> desc user_indexes

Note:

1)The index available on a column is put to use when the indexed col is referred in 'where' clause of a 'select' statement in its purest form.

2) if a negation operatior is used while referring to the indexed col in the 'where' clause the index available on that col will not be used.

3)if 'is null' or its negation('is not null') are used with the indexed col while referring it in 'where' clause the index available on that col will not be used.

4)if any function is implemented on the index col while referring to it in 'where' clause of the 'select' statement its purity is disturbed and the index available on the col will not work

5)If the Column of a table is set to Primary key or Unique key then it is default indexed.

6)Index cannot be assigned to the column whose datatype is long,longraw datatype.

examples:

create table employee(eno number primary key,ename varchar2(20),pan number);
create unique index ind1 on employee(ename)

create index r1 on employee(pan) reverse;

create table student(sno number primary key,sname varchar2(20),sub1 char(1),sub2
char(1),sub3 char(1));

create bitmap index b1 on student(sub1);

create bitmap index b2 on student(sub2,sub3);

to drop the index:

sql> drop index <indexname>;

OBJECT ORIENTED CONCEPTS IN SQL:

The Objects defined in Oracle are
1)abstract datatype
2)varrays
3)Nested Tables

1)abstract datatype:
It is defined as user defined datatype.
it is reusable.
it is collection of heteregenous datatypes(dissimilar)

syntax:

create or replace type <typename> is/as object(colname datatype(size),----------);

create or replace type address is object(hno varchar2(15),street varchar2(10),
city varchar2(10),pin number);

create table employee(eno number(3) primary key,ename varchar2(20),esal number,
empadd address)

insert into employee values(&eno,'&ename',&esal,address('&hno','&street','&city',&pin))

To drop the type:

sql>drop type <typename>;
note:cannot drop the type which has table dependents
to drop the type which has table dependents
sql> drop type <typename> force;

varrays:

is defined as collection of similar type of elements(homogenous).
varrays are fixed in size.

syntax:

create or replace type <typename> as varray(size) of datatype(size);

create or replace type children as varray(5) of varchar2(20);

create table parent(fathername varchar2(20),age number,noofchildren number,
nameofchildren children)

note:

updation of varrays is not defined in sql
In pl/sql using collection methods varrays can be manipulated

to drop the varray:

sql>drop type <typename>;
sql>drop type children;
note:cannot drop the type which has table dependents
to drop the type which has table dependents
sql> drop type <typename> force;
sql> drop type children force;

Nested table:

it is defined as table within a table i.e one of the column of the table will itself be a table. Nested tables are unlimited. Nested tables are complicated

ex:

create or replace type libtype as object(bookname varchar2(20),price number,author
varchar2(20));

create or replace type libnest as table of libtype;

create table student(sno number primary key,sname varchar2(20),bookdetails libnest) nested table bookdetails store as bk_details

insert into student values(&sno,'&sname',libnest(libtype('&bookname',&price,'&author'),
libtype('&bookname',&price,'&author')));

ex:

create or replace type passenger as object(name varchar2(20),age number,sex char(1));

create or replace type passengerlist as table of passenger;

create table traindet(tno number primary key,trainname varchar2(20),destination
varchar2(20),dofj date,nameofpassenger passengerlist) nested table nameofpassenger
store as psgn;

insert into traindet values(&tno,'&trainname','&destination','&dofj',passengerlist(
passenger('&name',&age,'&sex'),passenger('&name',&age,'&sex'),passenger
('&name',&age,'&sex')));

NOTE:

Nested tables can be manipulated using collection methods and collection types in pl/sql.


ADVANCED FEATURES:

TEMPORARY TABLES:
It is defined as a normal table where the data of the table exists till the session of the user i.e data is not committed permanently.
clauses used are:
1)on commit preserve rows
2)on commit delete rows
create global temporary table <tablename>(colname datatype(size),--------------------)
on commit preserve rows/on commit delete rows;
ex:
create global temporary table employee(eno number primary key,ename varchar2(20))
on commit preserve rows;
note:
on commit preserve rows will store the data in table till the session
create global temporary table employee(eno number primary key,ename varchar2(20))
on commit delete rows;
note:
on commit delete rows will store the data till the commit is performed

Merge -->it is DDL command in 9i. it provides the ability to conditionally update or insert data into a database table.

NEW ADDED FUNCTIONS:

rollup-->delivers aggregate and superaggregate for expression within a group by statement. rollup grouping produces a results set containing the regular grouped rows and the subtotal values
ex:
select department_id,job_id,sum(sal) from employees where department_id<60 group by rollup(department_id,job_id);

cube -->extension to group by clause.
produces cross tabulation values with a single select statement.

select department_id,job_id,sum(sal) from employees where department_id<60 group by cube(department_id,job_id)

with clause --> we can use the same query block in a select statement when it occurs more than once within a complex query.

1)nvl --> converts a null value to an actual value
ex:
select sal,comm,sal+nvl(comm,0) from emp;
2)nvl2(expr1,expr2,expr3) -->
if expr1 is not null then
nvl2 returns expr2
if expr1 is null then
nvl2 returns expr3
expr1 can be of any datatype
ex:
select sal,comm,nvl2(sal,sal+comm,comm) from emp;
select sal,comm,nvl2(comm,sal+comm,sal) from emp;
3)nullif -->
compares two expressions and return null if they are equal or the first expression if they are not equal.
nullif(expr1,expr2)
ex:
select nullif(sal,sal) from emp;
select nullif(sal,comm) from emp;
4)coalesce -->
returns the first non null expression in the expression list
ex:
select sal,comm,coalesce(comm,comm) from emp;
5)case -->
it is same as case construct(menu format)

select ename,job,sal,
case job when 'CLERK' then sal-100
when 'SALESMAN' then 2.5*sal
when 'ANALYST' then 3.5*sal
else
sal end revised salary from emp(can apply where condition);
6)decode -->
facilitates conditional inquiries by doing the work of CASE or IF-THEN-ELSE
decode function decodes expression after comparing it to each search value.If the expression is same as the search result is returned.
if default value is omitted, a null value is returned where a search value does not match any of the result values.

EX1:select ename,job,sal,decode(job,'CLERK',1.10*SAL,
'SALESMAN',500+SAL,
'ANALYST',SAL-500) "REVISED SAL" FROM EMP

EX2:
select ename,job,sal,decode(job,'CLERK',1.10*SAL,
'SALESMAN',500+SAL,
'ANALYST',SAL-500,SAL) "REVISED SAL" FROM EMP

ENHANCEMENTS IN JOINS: (Ansi joins)

1)cross join:
this clause produces the cross product of 2 tables. It is same as cartesian product between two tables.

ex:
select ename,job,dname from emp,dept;
select ename,job,dname from emp cross join dept;

2)Natural Join:
this clause is based on all columns in the two tables that have the same name.
it select rows from the two tables that have equal values in all the matched columns

ex:
select empno,ename,job,sal,dname from emp,dept where emp.deptno=dept.deptno;
select empno,ename,job,sal,dname from emp natural join dept ;

USING CLAUSE:

if several cols have the same names but the datatypes do not match, the natural join clause can be modified with the 'USING CLAUSE' to specify the cols that should be used for an equijoin

ex:
select empno,ename,job,sal,deptno,dname,loc from emp join dept using(deptno);

ON CLAUSE:

this makes the code to easy understand
to specify arbitary conditions or specify cols to joins

ex:

select e.empno,e.ename,e.job,e.sal,d.deptno,d.dname,d.loc from emp e,dept d where
e.deptno=d.deptno and e.job='SALESMAN';

select e.empno,e.ename,e.job,e.sal,d.deptno,d.dname,d.loc from emp e join dept d on (e.deptno=d.deptno) and e.job='SALESMAN';

select empno,ename,sal,dname,loc,grade from emp join dept on emp.deptno=dept.deptno join salgrade on emp.sal between losal and hisal;

select empno,ename,sal,dname,loc,grade from emp join dept on emp.deptno=dept.deptno and emp.job='SALESMAN' join salgrade on emp.sal between losal and hisal;

LEFT OUTER JOIN:
query retrieves all rows in the employee table which is the left table even if there is no match in department table

select e.empno,e.ename,e.job,e.sal,d.deptno,d.dname from emp e left outer join dept d on
e.deptno=d.deptno

RIGHT OUTER JOIN:

query retrieves all rows in the department table which is the right table even if there is no match in employee table

select e.empno,e.ename,e.job,e.sal,d.deptno,d.dname from emp e right outer join dept d on e.deptno=d.deptno

FULL OUTER JOIN:

retrieves all rows in emp table even if there is no match in dept table. It retrieves all rows in dept table even if there is no match in emp table.

select e.empno,e.ename,e.job,e.sal,d.deptno,d.dname,d.loc from emp e full outer join
dept d on (e.deptno=d.deptno);

note: enter a record in emp table which has deptno as NULL to test the above query

ROLE:

A Role is a named group of related privileges that can be granted to the user. This method makes it easier to revoke and maintain the privileges.
1) user can access to several roles
2) More than one user can be assigned to the same role

syntax:

create role <userdefined rolename>;

ex:
create role myrole;

grant create table,create sequence,------- to myrole;

grant myrole to kamaal,waleed,mohd,-------;

Note:

Role can be created by DBA.

GRANTING PRIVILEGES WITH GRANT OPTION:

A privilege that is granted with the 'WITH GRANT' option clause can be passed on to other users and roles by the grantee. Object privileges granted with the 'GRANT' option clause are revoked when the grantor's privilege is revoked.

syntax:

sql> grant <privileges> on <tablename> to <username> with grant option;

NOTE:

An owner of a table can grant access to all users by using 'public' keyword

sql> grant <privileges> on <tablename> to public;

CASCADING CONSTRAINTS:

--> It is used along with the drop column clause of alter.
--> Cascade constraints clause drops all referentrial integrity constraints that refer to the primary and unique keys defined on the dropped columns;

sql> alter table <tablename> drop column <columnaname> cascade constraints;

GROUP BY CLAUSE:

this clause is used to retrieve similar group of data from the specified table

ex:

waq to display the count of no of employees,job category,sum of their salaries according to job wise

select count(*) "No of Employees",job "Jobs",sum(sal) "Sum of Salaries" from emp
group by job;

waq to display the count of no of employees,their avg salaries ,deptno according to the department category

select count(*) "No of Employees",deptno "Deptno",avg(sal) "AverageSal" from emp
group by deptno;

HAVING CLAUSE:

this clause is used with 'group by' to place some condition on the data which is retrieved through 'group by'.
'having' clause should be followed by 'group by'

ex:

waq to display employee count,jobs,sum(sal) of only those jobs in which more than 3 employees are working

select count(*),job,sum(sal) from emp group by job having count(*)>3;

waq to display the department in which more than 4 employees are working

select count(

ما شاء الله