CREATE TABLE dept (
deptno NUMBER(2,0),
dname VARCHAR2(14),
loc VARCHAR2(13),
CONSTRAINT pk_dept PRIMARY KEY (deptno)
);
Insert a row into DEPT table by column position.
insert into DEPT values(10, 'ACCOUNTING', 'NEW YORK');
insert into dept values(20, 'RESEARCH', 'DALLAS');
insert into dept values(30, 'SALES', 'CHICAGO');
insert into dept values(40, 'OPERATIONS', 'BOSTON');
Create the EMP table which has a foreign key reference to the DEPT table. The
foreign key will require that the DEPTNO in the EMP table exist in the DEPTNO
column in the DEPT table.
CREATE TABLE emp (
empno NUMBER(4,0),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4,0),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2,0),
CONSTRAINT pk_emp PRIMARY KEY (empno),
CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept (deptno)
);
Insert EMP row, using TO_DATE function to cast string literal into an oracle DATE
format.
insert into emp values(7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981','dd-
mm-yyyy'),5000, null, 10);
insert into emp values( 7698, 'BLAKE', 'MANAGER', 7839, to_date('1-5-1981','dd-
mm-yyyy'), 2850, null, 3);
insert into emp values(7782, 'CLARK', 'MANAGER', 7839,to_date('9-6-1981','dd-
mm-yyyy'), 2450, null, 10);
insert into emp values(7566, 'JONES', 'MANAGER', 7839, to_date('2-4-1981','dd-
mm-yyyy'), 2975, null, 20);
insert into emp values(7788, 'SCOTT', 'ANALYST', 7566,to_date('13-JUL-87','dd-
mm-rr') - 85, 3000, null, 20);
insert into emp values(7902, 'FORD', 'ANALYST', 7566, to_date('3-12-1981','dd-
mm-yyyy'), 3000, null, 20);
insert into emp values( 7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980','dd-
mm-yyyy'), 800, null, 20);
insert into emp values( 7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-2-1981','dd-
mm-yyyy'), 1600, 300, 30);
insert into emp values( 7521, 'WARD', 'SALESMAN', 7698, to_date('22-2-
1981','dd-mm-yyyy'), 1250, 500, 30);
insert into emp values( 7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-9-
1981','dd-mm-yyyy'), 1250, 1400, 30);
insert into emp values(7844, 'TURNER', 'SALESMAN', 7698, to_date('8-9-
1981','dd-mm-yyyy'), 1500, 0, 30);
insert into emp values( 7876, 'ADAMS', 'CLERK', 7788, to_date('13-JUL-87', 'dd-
mm-rr') - 51, 1100, null, 20);
insert into emp values(7900, 'JAMES', 'CLERK', 7698, to_date('3-12-1981','dd-mm-
yyyy'), 950, null, 30 );
insert into emp values(7934, 'MILLER', 'CLERK', 7782, to_date('23-1-1982','dd-
mm-yyyy'), 1300, null, 10);
Querying(using ANY, ALL, UNION, INTERSECT, JOIN, Constraints etc)
The ANY and ALL operators allow you to perform a comparison between a
single
column value and a range of other values.
ANY:Display employees of dept 20 whose sal is more than any emp of
dept 30
Select ename,sal,deptno from emp where deptno=20 and sal>any(select sal from
emp where deptno=30);
ALL: display the employees who joined before all managers
Select ename,hiredate,job from emp where hiredate<all(select hiredate from emp
where job=’manager’);
UNION: The UNION operator is used to combine the result-set of two or more
SELECT
statements.
illustrate Union
Select deptno from emp union select deptno from dept;
UNION ALL: The UNION ALL command combines the result set of two or more
SELECT statements (allows duplicate values).
Example of Union All
Select deptno from emp union all select deptno from dept;
Select job from emp where deptno=10 union all select job from emp where
deptno=20;
INTERSECT: The INTERSECT operator in SQL is used to retrieve the records that
are identical/common between the result sets of two or more tables.
Select deptno from emp intersect select deptno from dept;
Select job from emp where deptno=10 intersect select job from emp where
deptno=20 union select job from emp where deptno=30;
MINUS: Illustrate set minus operator
Select *from emp where rownum<11 minus select *from emp where rownum<10;
Select job from emp where deptno=30 minus select job from emp where
deptno=10;
JOINS
Equi join: Display the details of an employee along with their department
name and
Location
Select empno,ename,emp.deptno,dname,loc from emp,dept where
emp.deptno=dept.deptno;
Outer join: Write an example using joins
Select empno,ename,emp.deptno,dname,loc from emp,dept where
emp.deptno(+)=dept.deptno;
Select empno, ename, emp. deptno, dname, loc from emp, dept where emp.
Deptno = dept.deptno(+);
Left outer join: Returns all records from the left table, and the matched records
from the right
Table
Select e.empno,e.ename,d.deptno,d.dname,d.loc from dept d left outer join emp e
on(e.deptno=d.deptno);
INNER JOIN: Returns records that have matching values in both tables
SELECT ename, dname, job, empno, hiredate, loc FROM emp INNER JOIN dept ON
emp.deptno = dept.deptno ORDER BY ename;
LEFT JOIN: The LEFT JOIN keyword returns all records from the left table (table1),
and the matching records from the right table (table2). The result is 0 records from
the right side, if there is no match.
SELECT ename, dname, job, empno, hiredate, loc FROM emp LEFT JOIN dept ON
emp.deptno = dept.deptno;
RIGHT JOIN : The RIGHT JOIN keyword returns all records from the right table
(table2), and the matching records from the left table (table1). The result is 0
records from the left side, if there is no match.
SELECT ename, dname, job, empno, hiredate, loc FROM emp RIGHT JOIN dept ON
emp.deptno = dept.deptno;
FULL JOIN: The FULL JOIN keyword returns all records when there is a match in
left (table1)
or right (table2) table records.
SELECT ename, dname, job, empno, hiredate, loc FROM emp FULL JOIN dept ON
emp.deptno = dept.deptno;
Simple natural join between DEPT and EMP tables based on the primary
key of the DEPT table DEPTNO, and the DEPTNO foreign key in the EMP
table.
Select ename, dname, job, empno, hiredate, loc from emp, dept where
emp.deptno =
dept.deptno order by ename;
The GROUP BY clause in the SQL statement allows aggregate functions of
non grouped columns. The join is an inner join thus departments with no
employees are not displayed.
Select dname, count(*) count_of_employees from dept, emp where dept.deptno =
emp.deptno
group by DNAME order by 2 desc;
CONSTRAINTS: A constraint is a check or a rule that is used to perform data
integrity check
against a table columns
CREATE TABLE Employee (
eno NUMBER(4) NOT NULL,
ename VARCHAR2(10) NOT NULL,
sal NUMBER(7,2),
deptno NUMBER(2) NOT NULL
);
desc Employee;
UNIQUE: When a tables column is defined with a unique constraint that column
will not accept
any duplicate values.
Create Table Emplo(Eno number(4) constraint un_employ_eno unique, ename
varchar2(10));
desc Emplo;
CHECK: It is used to check the data before entering into the table column.It is
used to maintain validity integrity.
Create Table Emplol(eno number(4) constraint un_emplol_eno unique constraint
nn_emplol_eno not null, ename varchar2(10) constraint ck_emplol_ename
check(ename=upper(ename)));
desc Emplol;
Create Table deptl(dno number(2) constraint pk_dno primary key,dname
varchar2(10),loc varchar2(10));
desc deptl;
Create Table emplo3(eno number(4),ename varchar2(10),dno constraint
fk_emplo1_dno references deptl(dno));
desc emplo3;
6 B) NESTED SUBQUERIES: An SQL Subquery, is a SELECT query within another
query.
It is also known as Inner query or Nested query and the query containing it is the
outer query.
Select *from emp e1 where sal>(select sal from emp e2 where
e1.mgr=e2.empno);
Select empno,ename,sal from emp where empno in(7902,7900,7566,7698);
CORRELATED SUBQUERIES: Correlated subqueries are used for row-by-row
processing.
Each subquery is executed once for every row of the outer query.
A correlated subquery is evaluated once for each row processed by the parent
statement.
The parent statement can be a SELECT, UPDATE, or DELETE statement.
Select *from emp e where sal>(select avg(sal) from emp where
e.deptno=deptno);
