TABLE CREATION:
Design a employee database with the following tables:
emp(name, eno, deptno, addr, dob, sex, salary)
sql>create table emp(name varchar2(25) not null, eno number primary key, deptno number, addr varchar2(50), dob
date, sex char(1), salary number(9,2));
Table created.
dept(dname, dno, location)
sql>create table dept(dname varchar2(20), dno number primary key, location varchar2(15));
Table created.
project(pno, pname, dno, plocation)
sql>create table project (pno varchar2(10) primary key, pname varchar2(20), dno number, plocation varchar2(15));
Table created.
works(eno, pno, hours)
sql>create table works(eno number, pno varchar2(10), hours number);
Table created.
CONSTRAINTS ADDED TO TABLES:
sql> alter table emp add constraint fk_dept foreign key (deptno) references dept(dno);
Table altered.
sql>alter table project add constraint fk_pr_dept foreign key(dno) references dept(dno);
Table altered.
sql>alter table works add constraint fk_wrk_emp foreign key(eno)references emp(eno);
Table altered.
sql>alter table works add constraint fk_wrk_proj foreign key(pno) references project(pno);
Table altered.
TABLE DESCRIPTIONS:
SQL> desc emp;
Name Null? Type
--------------------------------- --------------------- ----------------------------
NAME NOT NULL VARCHAR2(25)
ENO NOT NULL NUMBER
DEPTNO NUMBER
ADDR VARCHAR2(50)
DOB DATE
SEX CHAR(1)
SALARY NUMBER(9,2)
SQL> desc dept;
Name Null? Type
--------------------- ------------------ ----------------------------
DNAME VARCHAR2(20)
DNO NOT NULL NUMBER
LOCATION VARCHAR2(15)
SQL> desc project;
Name Null? Type
---------------------- ---------------- ----------------------------
PNO NOT NULL VARCHAR2(10)
PNAME VARCHAR2(20)
DNO NUMBER
PLOCATION VARCHAR2(15)
SQL> desc works;
Name Null? Type
------------------ -------- ----------------------------
ENO NUMBER
PNO VARCHAR2(10)
HOURS NUMBER
INSERTING VALUES IN DEPT TABLE:
sql> insert into dept values('research', 5000, 'chennai');
1 row created.
sql> insert into dept values('research', 5010, 'bangalore');
1 row created.
sql> insert into dept values('admin', 5001, 'chennai');
1 row created.
sql> insert into dept values('auditor', 5100, 'coimbatore');
1 row created.
INSERTING VALUES IN EMP TABLE:
sql> insert into emp values('sathya', 1000, 5000, 'saibaba colony, coimbatore', '15-SEP-1974', 'f', 10000);
1 row created.
sql> insert into emp values('ramya', 1002, 5000, 'cluddalore main road, cluddalore', '19-JAN-1985', 'f', 12000);
1 row created.
sql> insert into emp values('suresh', 1001, 5010, 'ramsamy nagar, coimbatore', '4-NOV-1985', 'm', 10500);
1 row created.
sql> insert into emp values('rameshan', 1003, 5100, 'nsr road, coimbatore', '30-JUL-1956', 'm', 10800);
1 row created.
INSERTING VALUES IN PROJECT TABLE:
sql>insert into project values('p1', 'algorithms', 5000, 'rajasthan');
1 row created.
sql>insert into project values('p2', 'trees', 5000, 'bhopal');
1 row created.
sql>insert into project values('p3', 'neuralnetworks', 5010, 'delhi');
1 row created.
sql>insert into project values('p4', 'networks', 5000, 'delhi');
1 row created.
sql>insert into project values('p5', 'embedded', 5010, 'rajasthan');
1 row created.
INSERTING VALUES IN WORKS TABLE:
sql> insert into works(eno, pno, hours)values(1000,'p1', 8);
1 row created.
sql> insert into works(eno, pno, hours)values(1001,'p2', 6);
1 row created.
sql> insert into works(eno, pno, hours)values(1002,'p1', 7);
1 row created.
sql> insert into works(eno, pno, hours)values(1003,'p1', 12);
1 row created.
DISPLAYING TABLE CONTENTS:
SQL> select * from dept;
DNAME DNO LOCATION
-------------------- ---------- ---------------
research 5000 chennai
research 5010 bangalore
admin 5001 chennai
auditor 5100 coimbatore
SQL> select * from emp;
NAME ENO DEPTNO ADDR DOB S SALARY
-------------- ---------- ------------- -------------------------------------------- --------------- ----- ------------
sathya 1000 5000 saibaba colony, coimbatore 15-SEP-74 f 10000
ramya 1002 5000 cluddalore main road, cluddalore 19-JAN-85 f 12000
suresh 1001 5010 ramsamy nagar, coimbatore 04-NOV-85 m 10500
rameshan 1003 5100 nsr road, coimbatore 30-JUL-56 m 10800
SQL> select * from project;
PNO PNAME DNO PLOCATION
------- -------------------- ---------- ------------------
p1 algorithms 5000 rajasthan
p2 trees 5000 bhopal
p3 neuralnetworks 5010 delhi
p4 networks 5000 delhi
p5 embedded 5010 rajasthan
SQL> select * from works;
ENO PNO HOURS
-------- -------- ----------
1000 p1 8
1001 p2 6
1002 p1 7
1003 p1 12
DML QUERIES:
a. Retrieve the name of all the employee who work for 'research' department.
SQL> select eno, name from emp where deptno in(select dno from dept where dname='research');
ENO NAME
------ ----------------------
1000 sathya
1002 ramya
1001 suresh
b. Retrieve the total no of employees in dept 'research' and dept 'admin'.
SQL> select count(eno) as tot_emp_in_rsrch_admin from emp where deptno in(select dno from dept where
dname='research' or dname='admin');
TOT_EMP_IN_RSRCH_ADMIN
---------------------------------------
3
c. Create a view to count the no of distinct salary for each department.
SQL> create or replace view sal_view as (select distinct count(salary) distinct_sal_nos, deptno from emp group by
deptno);
View created.
SQL> select * from sal_view;
DISTINCT_SAL_NOS DEPTNO
---------------------------- --------------
2 5000
1 5010
1 5100
CREATE A CURSOR TO DISPALY THE DETAILS OF ALL EMPLOYEES WHO WORK MORE THAN
8 HRS FOR PROJECT 'P1'.
SQL> set serveroutput on
SQL> declare
2 emp_row emp%rowtype;
3 cursor emphr_cur is
4 select * from emp
5 where eno in(Select eno from works
6 where pno='p1' and hours>8);
7 begin
8 for cr in emphr_cur
9 loop
10 dbms_output.put_line(cr.name||','||cr.eno);
11 end loop;
12 end;
13 /
rameshan,1003
PL/SQL procedure successfully completed.
Comments