How to Create Table in Oracle...
-- creating table with constraints but without names to the constraint
create table t1
(empno number primary key,
ename varchar2(10) not null,
invno char(5) unique,
sal number check (sal >= 5000))
-- Displaying the inbuilt names of constraints
desc user_constraints
select constraint_name,constraint_type,table_name from user_constraints
where table_name='T1';
-- creating table with names to constraint
create table t2
(empno number constraint pk primary key,
ename varchar2(10) not null,
invno number constraint uinvno unique,
sal number constraint chsal check(sal >= 5000));
-- Displaying the user defined names of constraints
select constraint_name,constraint_type,table_name from user_constraints
where table_name='T2';
--Table level constraints
create table t3
(empno number,
invno varchar2(10),
ename varchar2(10) not null,
issuedate date,
returndate date,
constraint compk primary key (empno,invno),
constraint un unique (ename),
constraint chdate check (returndate >= issuedate));
-- Foreign key
create table X
(empno number constraint fk references t1(empno) on delete cascade,
marks number);
How to work with Date Functions??
How to add days in current date?
select sysdate + 3 from dual;
----------------------------------------------------
select '07-dec-04' + 3 from dual; -- Gives error
select to_date('07-dec-04') + 3 from dual;
select to_date('07-dec-04') - to_date('13-dec-04') from dual; -- Negative value
---------------------------------------------------------------
date + number/24 --> Adds a number of hours to a date
Suppose the time of sysdate is 9:30 pm and 5 hours are added in it then it will be2:30 am of the next date. So it shows the next date.
select sysdate + 4/24 from dual;
---------------------------------------------------
How to do Months_between(d1,d2)
If d1 > d2 then +ve else -ve
Select MONTHS_BETWEEN(TO_DATE('01-AUG-02'),to_date('01-dec-05')) from dual;
select months_between(sysdate,hiredate) from emp
select round(months_between(sysdate,hiredate),0) As "Months Between" from emp
Assignment - Find the years between using months_between
select months_between('13-dec-04','24-jul-04') from dual
select round(months_between('13-dec-04','24-jul-04'),0) from dual
Add_months(date,n) [n cand be +ve or -ve]
select add_months('4-dec-04',3) from dual; Gives 04-Mar-05
select add_months('4-dec-04',-3) from dual; Gives 04-Sep-04
-----------------------------------------------------------------------------------------------
Next_Day(date,'char') -- Give the date of the next week for the specified day in the char parameter.
select next_day('04-Dec-04','Wed') from dual; Gives the date coming on next Wednesday after 4-Dec-04. The answer is 08-Dec-04
OR
Instead of character parameter a numeric value can be also given. It has Sunday as 1 Monday as 2 ...Saturday as 7
select next_day('04-Dec-04', 4) from dual. The answer is 08-Dec-04
Note: The number cannot be negative.
----------------------------------------------------------------------
Last_Day(date) - Returns the last date of the month.
select last_day(sysdate) from dual;
--------------------------------------------------------------------------
Round(Date,fmt)
If month is the fmt, then rounds the date to the months extremities.If date is between 1 and 15 then it rounds the date to the first of the same month. If it is 16 to end then rounds to first date of the next date.
E.g select round(to_date('04-dec-04'),'Month') from dual ----> 01-DEC-04
select round(to_date('15-dec-04'),'Month') from dual; ----> 01-DEC-04
select round(to_date('16-dec-04'),'Month') from dual; ----> 01-JAN-05
select round(to_date('02-Feb-04'), 'Year') from dual; --> 01-JAN-04
select round(to_date('30-Jun-04'), 'Year') from dual; --> 01-JAN-04
select round(to_date('1-Jul-04'), 'Year') from dual; --> 01-JAN-05
Trunc
select trunc(to_date('23-dec-04'),'Month') from dual; Gives 01-Dec-04
select trunc(to_date('07-Dec-04'),'Month') from dual; Gives 01-Dec-04
select trunc(to_date('23-Dec-04'),'Year') from dual; Gives 01-Jan-04
select trunc(to_date('02-Feb-04'),'Year') from dual; Gives 01-Jan-04
To_Char
To see all the records of employees joined after 1982
select * from emp
where to_char(hiredate,'yy') >= 82;
Or where to_char(hiredate,'yyyy’) >= 1982
To see all the records of employees who have joined from October of any month.
select * from emp
where to_char(hiredate,'mm') >= 10;
(Note à Only number 1 to 12 can be used for comparison)
To see all the records of employees who have joined from 21st date.
select * from emp
where to_char(hiredate,'dd') >= 21;
To see all the records of employees who have joined from April-81
select * from emp
where to_char(hiredate,'mm') > 04 and to_char(hiredate,'yy') >= 81
To see all the records of employees who have joined after 28-Sep-81
select * from emp
where hiredate > '28-Sep-81'
DATE FORMAT MODEL
YEARS
select to_char(hiredate,'YYYY') || '-' || to_char(hiredate,'YEAR') FROM EMP;
MONTHS
select to_char(hiredate,'MONTH') || '-' || to_char(hiredate,'MM')
|| '-' || TO_CHAR(hiredate,'MON') FROM EMP;
DAYS
select to_char(hiredate,'DAY') || '-' || to_char(hiredate,'DD')
|| '-' || TO_CHAR(hiredate,'DY') FROM EMP;
ADVANCED FORMATS -
CENTURY
SELECT TO_CHAR(SYSDATE,’SCC’) FROM DUAL;
select to_char(HIREDATE,'SCC') || '-' || TO_CHAR(HIREDATE,'YY') "CEN-YR"
FROM EMP;
SYEAR The spelled out year.
select to_char(HIREDATE,'SYEAR') "YEAR"
FROM EMP
Q To get the quarter of the year (1,2,3 and 4)
select to_char(HIREDATE,'Q') "Quarter"
FROM EMP;
RM Roman numeral month
select to_char(HIREDATE,'RM')"MONTH"
FROM EMP;
J Julian day - The number of days since 31Dec 4713 B.C.
select to_char(HIREDATE,'J')"JulianDay"
FROM EMP;
TIME
select to_char(sysdate,'hh:mi:ss') FROM DUAL;
To show AM or PM
select to_char(sysdate,'hh:mi:ss')|| '-' || to_char(sysdate,'PM') from dual;
SSSS Seconds past midnight (0-86399) [ (60secs * 60 min * 24) - 1]
select to_char(sysdate,'ssss') from dual;
Suffixes -
select to_char(sysdate,'ddth') from dual;
select to_char(sysdate,'ddsp') from dual;
select to_char(sysdate,'ddspth') from dual;
RR DATE FORMAT -
create table rryy
(name varchar2(10),
dob date);
NAME DOB
A 24-SEP-02
B 12-DEC-67
C 14-JAN-59
D 05-APR-04
A AND D ARE IN 2000 CENTURY.
B AND C ARE IN 1900 CENTURY.
To see the records who have dob of 19th century.
Suppose yy format is used -
SELECT * FROM RRYY
WHERE TO_CHAR(DOB,'YY') < 99.
It assumes all the years are in the 20th century (2099). So all 4 records will come.
So to show only ,B and C records --
SELECT * FROM RRYY
WHERE DOB < TO_DATE('01-JAN-90', 'DD-MON-RR');
Here 90 it understands as 1990.
If YY is given instead of RR -
SELECT * FROM RRYY
WHERE DOB < TO_DATE('01-JAN-90', 'DD-MON-YY');
Here 90 it understands as 2090. So again all 4 records will come.
Similarly ---
SELECT * FROM RRYY
WHERE DOB > TO_DATE('01-JAN-90', 'DD-MON-RR');
Due to RR 90 will be 1990. So it shows all the records where DOB is above 1990.
A and D records will come.
If YY is given instead of RR -
SELECT * FROM RRYY
WHERE DOB > TO_DATE('01-JAN-90', 'DD-MON-YY');
Due to YY it takes 90 as 2090. It will not find any record above 2090.
How to use Case and DECODE function???
How to use Case and DECODE function???
CASE
--Case is used instead of Decode function
--Case is new in 9i
--Case is simple to read and maintain than in Decode
E.g 1) select job,
case job
when 'ANALYST' then 4000
when 'MANAGER' then 5000
when 'CLERK' then 3000
else
2000
end "Raise"
from emp;
E.g 2) select sal,
case sal
when 1600 then 'A'
when 2850 then 'B'
else
‘C’
end "Grade"
from emp;
E.g 3)
Drop table codes;
create table codes
(traincode varchar2(15));
insert into codes
values('M-D');
insert into codes
values('P-M');
insert into codes
values('P-D');
insert into codes
values('M-A');
insert into codes
values('C-B');
--Here the code values are ellaborated with full forms using case
select traincode,
CASE traincode
when 'M-D' then 'Mumbai to Delhi'
when 'P-M' then 'Pune to Mumbai'
when 'P-D' then 'Pune to Delhi'
when 'M-A' then 'Mumbai to Ahemdabad'
else
'South trains'
end
As "Description"
from codes;
Case in Comparison –
select sal,
case when sal >= 3000 then sal + 10000
when sal <= 1000 then sal - 500
else
sal
end
from emp;
DECODE
select job,
DECODE ( JOB, 'ANALYST','A','MANAGER','M','CLERK','C','ZZ') “CODE”
from emp;
Oracle SQL Subquery
Pairwise Comparison Subquery
select * from emp
where (job, deptno) = (select job, deptno
from emp
where ename = 'SMITH');
select * from emp
where (job,mgr) in (select job, mgr
from emp
where ename = 'ALLEN')
Non-Pairwise Comparison Subquery
select * from emp
where job = (select job
from emp
where ename = 'ALLEN')
and mgr = (select mgr
from emp
where ename = 'ALLEN')
Sub-Query in From Clause
To see names, salaries,deptno and average sal of those employees who earn more than the average salary in their departments.
SELECT a.ename, a.sal, a.deptno, b.salavg
FROM emp a, (SELECT deptno,
AVG(sal) salavg
FROM emp
GROUP BY deptno) b
WHERE a.deptno = b.deptno
AND a.sal > b.salavg;
Scalar Sub Queries
Sub Query in Case Statement
select empno,ename,sal,job,
(case when deptno = (select deptno from dept
where DNAME = 'ACCOUNTING')
then 'Acc-Dept'
else 'Non Acc'
end) Dept_Details
from emp
Correlated Sub Query
To see employees who earn a salary less than the average salary of their own job
Parent query takes each row and submits it to child query.
Child query gets executed for each row
select ename,job,sal
from emp E
where sal < (Select avg(sal)
from emp
where job = E.job);
To see the first two salary values
select distinct sal
from emp E
where 2 >= (select count(*)
from emp
where sal > E.sal)
order by sal desc
To see the fourth highest salary
select distinct sal
from emp E
where 4 = (select count(distinct sal)
from emp
where sal > E.sal)
Co related sub query involving two tables—
Drop table LFC;
create table LFC
(empno number,
No_Of_Leaves number,
from_date date,
till_date date);
Insert into LFC
values(7369, 10, ‘8-aug-03’, ‘18-aug-04’);
Insert into LFC
values(7839, 20, ‘11-dec-04’, ‘31-Dec-04’);
Insert into LFC
values(7369, 7, ‘21-apr-05’, ‘26-apr-05’);
Insert into LFC
values(7902, 8, ‘5-may-04’, ‘10-may-04’);
Select * from LFC;
To see the employee names that have taken LFC benefit at least twice.
select e.empno, e.ename, e.job
from emp e
where 2 <= (select count(*)
from LFC
where empno = e.empno)
Correlated Update
Denormalize the emp table by adding dname column.
Then populate the dname column as per the dept table values for matching deptno.
Alter table emp add deptno varchar2(14)
Update emp e
Set deptno = (select dname
From dept d
Where e.deptno = d.deptno)
DRAWBACKS OF CORRELATED SUB QUEIRES
--To list the Sum of Salaries for departments comprising more than
--1/3 of the firm's annual salary.
select dname,sum(sal) as DEP_TOTAL
from emp,dept
where emp.deptno = dept.deptno
group by dname
having sum(sal) >
(select sum(sal) * 1/3
from emp,dept
where emp.deptno = dept.deptno);
-- Drawbacks of this mechanism are as follows --
-- 1) Each record of group from parent query is taken and compared with sub --query.
--2) In the sub query again the entire calculation is done.
--3)Due to this the performance overheads are increased.
ORACLE 9I has a new concept of with clause in subqueries to solve this problem
--To list the Sum of Salaries for departments comprising more than
--1/3 of the firms's annual salary.
with summary as
(select dname,sum(sal) as DTOTAL
from emp,dept
where emp.deptno = dept.deptno
group by dname)
select dname, DTOTAL
from summary
where DTOTAL >
(select sum(DTOTAL) * 1/3
from summary);
-- Advantages of using with clause and subquery
--1) From the parent query the summarized and grouped data is taken into
-- an temporary table called summary.
--2) Now the data from the summary table is compared with the sub query .
-- In the sub query also the same data from summary table is taken for --calculations.
-- So the child query gets executed only once and then it references to the parent query.
-- Explanation pertaining to current example
-- The sum(sal) for each deptno is taken in the summary table
--Then sub query gets executed only once to see what is the value one third of the sum of all the
-- total salaries
-- So for the each record from summary table the sub query was executed only once!!!
-- It means that there are less performance overheads
Exists and Not Exists
Drop table s;
Create table s(roll number, name varchar2(40));
Insert into s values(1,'A');
Insert into s values(2,'B');
Insert into s values(3,'C');
Insert into s values(4,'D');
Drop table r;
create table r(roll number,marks number);
Insert into r values(2,90);
Insert into r values(3,98);
To see records from s table for the students who have given exam –
select * from s
where Exists
(select *
from r
where s.roll = r.roll);
To see records from s table for the students who not have given exam –
select * from s
where Not Exists
(select *
from r
where s.roll = r.roll);
-- creating table with constraints but without names to the constraint
create table t1
(empno number primary key,
ename varchar2(10) not null,
invno char(5) unique,
sal number check (sal >= 5000))
-- Displaying the inbuilt names of constraints
desc user_constraints
select constraint_name,constraint_type,table_name from user_constraints
where table_name='T1';
-- creating table with names to constraint
create table t2
(empno number constraint pk primary key,
ename varchar2(10) not null,
invno number constraint uinvno unique,
sal number constraint chsal check(sal >= 5000));
-- Displaying the user defined names of constraints
select constraint_name,constraint_type,table_name from user_constraints
where table_name='T2';
--Table level constraints
create table t3
(empno number,
invno varchar2(10),
ename varchar2(10) not null,
issuedate date,
returndate date,
constraint compk primary key (empno,invno),
constraint un unique (ename),
constraint chdate check (returndate >= issuedate));
-- Foreign key
create table X
(empno number constraint fk references t1(empno) on delete cascade,
marks number);
How to work with Date Functions??
How to add days in current date?
select sysdate + 3 from dual;
----------------------------------------------------
select '07-dec-04' + 3 from dual; -- Gives error
select to_date('07-dec-04') + 3 from dual;
select to_date('07-dec-04') - to_date('13-dec-04') from dual; -- Negative value
---------------------------------------------------------------
date + number/24 --> Adds a number of hours to a date
Suppose the time of sysdate is 9:30 pm and 5 hours are added in it then it will be2:30 am of the next date. So it shows the next date.
select sysdate + 4/24 from dual;
---------------------------------------------------
How to do Months_between(d1,d2)
If d1 > d2 then +ve else -ve
Select MONTHS_BETWEEN(TO_DATE('01-AUG-02'),to_date('01-dec-05')) from dual;
select months_between(sysdate,hiredate) from emp
select round(months_between(sysdate,hiredate),0) As "Months Between" from emp
Assignment - Find the years between using months_between
select months_between('13-dec-04','24-jul-04') from dual
select round(months_between('13-dec-04','24-jul-04'),0) from dual
Add_months(date,n) [n cand be +ve or -ve]
select add_months('4-dec-04',3) from dual; Gives 04-Mar-05
select add_months('4-dec-04',-3) from dual; Gives 04-Sep-04
-----------------------------------------------------------------------------------------------
Next_Day(date,'char') -- Give the date of the next week for the specified day in the char parameter.
select next_day('04-Dec-04','Wed') from dual; Gives the date coming on next Wednesday after 4-Dec-04. The answer is 08-Dec-04
OR
Instead of character parameter a numeric value can be also given. It has Sunday as 1 Monday as 2 ...Saturday as 7
select next_day('04-Dec-04', 4) from dual. The answer is 08-Dec-04
Note: The number cannot be negative.
----------------------------------------------------------------------
Last_Day(date) - Returns the last date of the month.
select last_day(sysdate) from dual;
--------------------------------------------------------------------------
Round(Date,fmt)
If month is the fmt, then rounds the date to the months extremities.If date is between 1 and 15 then it rounds the date to the first of the same month. If it is 16 to end then rounds to first date of the next date.
E.g select round(to_date('04-dec-04'),'Month') from dual ----> 01-DEC-04
select round(to_date('15-dec-04'),'Month') from dual; ----> 01-DEC-04
select round(to_date('16-dec-04'),'Month') from dual; ----> 01-JAN-05
select round(to_date('02-Feb-04'), 'Year') from dual; --> 01-JAN-04
select round(to_date('30-Jun-04'), 'Year') from dual; --> 01-JAN-04
select round(to_date('1-Jul-04'), 'Year') from dual; --> 01-JAN-05
Trunc
select trunc(to_date('23-dec-04'),'Month') from dual; Gives 01-Dec-04
select trunc(to_date('07-Dec-04'),'Month') from dual; Gives 01-Dec-04
select trunc(to_date('23-Dec-04'),'Year') from dual; Gives 01-Jan-04
select trunc(to_date('02-Feb-04'),'Year') from dual; Gives 01-Jan-04
To_Char
To see all the records of employees joined after 1982
select * from emp
where to_char(hiredate,'yy') >= 82;
Or where to_char(hiredate,'yyyy’) >= 1982
To see all the records of employees who have joined from October of any month.
select * from emp
where to_char(hiredate,'mm') >= 10;
(Note à Only number 1 to 12 can be used for comparison)
To see all the records of employees who have joined from 21st date.
select * from emp
where to_char(hiredate,'dd') >= 21;
To see all the records of employees who have joined from April-81
select * from emp
where to_char(hiredate,'mm') > 04 and to_char(hiredate,'yy') >= 81
To see all the records of employees who have joined after 28-Sep-81
select * from emp
where hiredate > '28-Sep-81'
DATE FORMAT MODEL
YEARS
select to_char(hiredate,'YYYY') || '-' || to_char(hiredate,'YEAR') FROM EMP;
MONTHS
select to_char(hiredate,'MONTH') || '-' || to_char(hiredate,'MM')
|| '-' || TO_CHAR(hiredate,'MON') FROM EMP;
DAYS
select to_char(hiredate,'DAY') || '-' || to_char(hiredate,'DD')
|| '-' || TO_CHAR(hiredate,'DY') FROM EMP;
ADVANCED FORMATS -
CENTURY
SELECT TO_CHAR(SYSDATE,’SCC’) FROM DUAL;
select to_char(HIREDATE,'SCC') || '-' || TO_CHAR(HIREDATE,'YY') "CEN-YR"
FROM EMP;
SYEAR The spelled out year.
select to_char(HIREDATE,'SYEAR') "YEAR"
FROM EMP
Q To get the quarter of the year (1,2,3 and 4)
select to_char(HIREDATE,'Q') "Quarter"
FROM EMP;
RM Roman numeral month
select to_char(HIREDATE,'RM')"MONTH"
FROM EMP;
J Julian day - The number of days since 31Dec 4713 B.C.
select to_char(HIREDATE,'J')"JulianDay"
FROM EMP;
TIME
select to_char(sysdate,'hh:mi:ss') FROM DUAL;
To show AM or PM
select to_char(sysdate,'hh:mi:ss')|| '-' || to_char(sysdate,'PM') from dual;
SSSS Seconds past midnight (0-86399) [ (60secs * 60 min * 24) - 1]
select to_char(sysdate,'ssss') from dual;
Suffixes -
select to_char(sysdate,'ddth') from dual;
select to_char(sysdate,'ddsp') from dual;
select to_char(sysdate,'ddspth') from dual;
RR DATE FORMAT -
create table rryy
(name varchar2(10),
dob date);
NAME DOB
A 24-SEP-02
B 12-DEC-67
C 14-JAN-59
D 05-APR-04
A AND D ARE IN 2000 CENTURY.
B AND C ARE IN 1900 CENTURY.
To see the records who have dob of 19th century.
Suppose yy format is used -
SELECT * FROM RRYY
WHERE TO_CHAR(DOB,'YY') < 99.
It assumes all the years are in the 20th century (2099). So all 4 records will come.
So to show only ,B and C records --
SELECT * FROM RRYY
WHERE DOB < TO_DATE('01-JAN-90', 'DD-MON-RR');
Here 90 it understands as 1990.
If YY is given instead of RR -
SELECT * FROM RRYY
WHERE DOB < TO_DATE('01-JAN-90', 'DD-MON-YY');
Here 90 it understands as 2090. So again all 4 records will come.
Similarly ---
SELECT * FROM RRYY
WHERE DOB > TO_DATE('01-JAN-90', 'DD-MON-RR');
Due to RR 90 will be 1990. So it shows all the records where DOB is above 1990.
A and D records will come.
If YY is given instead of RR -
SELECT * FROM RRYY
WHERE DOB > TO_DATE('01-JAN-90', 'DD-MON-YY');
Due to YY it takes 90 as 2090. It will not find any record above 2090.
How to use Case and DECODE function???
How to use Case and DECODE function???
CASE
--Case is used instead of Decode function
--Case is new in 9i
--Case is simple to read and maintain than in Decode
E.g 1) select job,
case job
when 'ANALYST' then 4000
when 'MANAGER' then 5000
when 'CLERK' then 3000
else
2000
end "Raise"
from emp;
E.g 2) select sal,
case sal
when 1600 then 'A'
when 2850 then 'B'
else
‘C’
end "Grade"
from emp;
E.g 3)
Drop table codes;
create table codes
(traincode varchar2(15));
insert into codes
values('M-D');
insert into codes
values('P-M');
insert into codes
values('P-D');
insert into codes
values('M-A');
insert into codes
values('C-B');
--Here the code values are ellaborated with full forms using case
select traincode,
CASE traincode
when 'M-D' then 'Mumbai to Delhi'
when 'P-M' then 'Pune to Mumbai'
when 'P-D' then 'Pune to Delhi'
when 'M-A' then 'Mumbai to Ahemdabad'
else
'South trains'
end
As "Description"
from codes;
Case in Comparison –
select sal,
case when sal >= 3000 then sal + 10000
when sal <= 1000 then sal - 500
else
sal
end
from emp;
DECODE
select job,
DECODE ( JOB, 'ANALYST','A','MANAGER','M','CLERK','C','ZZ') “CODE”
from emp;
Oracle SQL Subquery
Pairwise Comparison Subquery
select * from emp
where (job, deptno) = (select job, deptno
from emp
where ename = 'SMITH');
select * from emp
where (job,mgr) in (select job, mgr
from emp
where ename = 'ALLEN')
Non-Pairwise Comparison Subquery
select * from emp
where job = (select job
from emp
where ename = 'ALLEN')
and mgr = (select mgr
from emp
where ename = 'ALLEN')
Sub-Query in From Clause
To see names, salaries,deptno and average sal of those employees who earn more than the average salary in their departments.
SELECT a.ename, a.sal, a.deptno, b.salavg
FROM emp a, (SELECT deptno,
AVG(sal) salavg
FROM emp
GROUP BY deptno) b
WHERE a.deptno = b.deptno
AND a.sal > b.salavg;
Scalar Sub Queries
Sub Query in Case Statement
select empno,ename,sal,job,
(case when deptno = (select deptno from dept
where DNAME = 'ACCOUNTING')
then 'Acc-Dept'
else 'Non Acc'
end) Dept_Details
from emp
Correlated Sub Query
To see employees who earn a salary less than the average salary of their own job
Parent query takes each row and submits it to child query.
Child query gets executed for each row
select ename,job,sal
from emp E
where sal < (Select avg(sal)
from emp
where job = E.job);
To see the first two salary values
select distinct sal
from emp E
where 2 >= (select count(*)
from emp
where sal > E.sal)
order by sal desc
To see the fourth highest salary
select distinct sal
from emp E
where 4 = (select count(distinct sal)
from emp
where sal > E.sal)
Co related sub query involving two tables—
Drop table LFC;
create table LFC
(empno number,
No_Of_Leaves number,
from_date date,
till_date date);
Insert into LFC
values(7369, 10, ‘8-aug-03’, ‘18-aug-04’);
Insert into LFC
values(7839, 20, ‘11-dec-04’, ‘31-Dec-04’);
Insert into LFC
values(7369, 7, ‘21-apr-05’, ‘26-apr-05’);
Insert into LFC
values(7902, 8, ‘5-may-04’, ‘10-may-04’);
Select * from LFC;
To see the employee names that have taken LFC benefit at least twice.
select e.empno, e.ename, e.job
from emp e
where 2 <= (select count(*)
from LFC
where empno = e.empno)
Correlated Update
Denormalize the emp table by adding dname column.
Then populate the dname column as per the dept table values for matching deptno.
Alter table emp add deptno varchar2(14)
Update emp e
Set deptno = (select dname
From dept d
Where e.deptno = d.deptno)
DRAWBACKS OF CORRELATED SUB QUEIRES
--To list the Sum of Salaries for departments comprising more than
--1/3 of the firm's annual salary.
select dname,sum(sal) as DEP_TOTAL
from emp,dept
where emp.deptno = dept.deptno
group by dname
having sum(sal) >
(select sum(sal) * 1/3
from emp,dept
where emp.deptno = dept.deptno);
-- Drawbacks of this mechanism are as follows --
-- 1) Each record of group from parent query is taken and compared with sub --query.
--2) In the sub query again the entire calculation is done.
--3)Due to this the performance overheads are increased.
ORACLE 9I has a new concept of with clause in subqueries to solve this problem
--To list the Sum of Salaries for departments comprising more than
--1/3 of the firms's annual salary.
with summary as
(select dname,sum(sal) as DTOTAL
from emp,dept
where emp.deptno = dept.deptno
group by dname)
select dname, DTOTAL
from summary
where DTOTAL >
(select sum(DTOTAL) * 1/3
from summary);
-- Advantages of using with clause and subquery
--1) From the parent query the summarized and grouped data is taken into
-- an temporary table called summary.
--2) Now the data from the summary table is compared with the sub query .
-- In the sub query also the same data from summary table is taken for --calculations.
-- So the child query gets executed only once and then it references to the parent query.
-- Explanation pertaining to current example
-- The sum(sal) for each deptno is taken in the summary table
--Then sub query gets executed only once to see what is the value one third of the sum of all the
-- total salaries
-- So for the each record from summary table the sub query was executed only once!!!
-- It means that there are less performance overheads
Exists and Not Exists
Drop table s;
Create table s(roll number, name varchar2(40));
Insert into s values(1,'A');
Insert into s values(2,'B');
Insert into s values(3,'C');
Insert into s values(4,'D');
Drop table r;
create table r(roll number,marks number);
Insert into r values(2,90);
Insert into r values(3,98);
To see records from s table for the students who have given exam –
select * from s
where Exists
(select *
from r
where s.roll = r.roll);
To see records from s table for the students who not have given exam –
select * from s
where Not Exists
(select *
from r
where s.roll = r.roll);
No comments:
Post a Comment