DBMS Semecter Lab Manual

Employee Database

:-An enterprise wishes to maintain a database to automate its operations. Enterprise divided into certain department consists of employees. The following two tables describe the automation schemas.

Dept (dno, dname, loc) Employ (eno, ename, job, mgr, sal, comm, dno, hire date)

Write a Query to display all the details of dept table. SQL> select * from dept;

Write a Query to display all the details of employ table. SQL> select * from employ;

Write a Query to display the unique jobs from the employ table. SQL> select distinct(job) from employ;

Write a Query to display the employ record for eno=4. SQL> select * from employ where eno=4;

Write a Query to display the employ record for eno! =2. SQL> select * from employ where eno!=2;

Write a Query to display the employ record for sal>15000. SQL> select * from employ where sal>15000;

Write a Query to display the employ record for eno greater than any of 2, 3, 4. SQL> select * from employ where eno>any(2,3,4);

Write a Query to display the employ record for eno greater than of all 2, 3, 4. SQL> select * from employ where eno>all(2,3,4);

Write a Query to display the employ record for ename like start with any character, second character must be ‘a’ and any of the remaining characters. SQL> select * from employ where ename like ‘_a%’;

Write a Query to display the employ record for ename not like start with any character, second

character must be ‘a’ and any of the remaining characters. SQL> select * from employ where ename not like ‘_a%’;

Write a Query to display the employ record for eno is not in 2, 3, 4. SQL> select * from employ where eno not in(2,3,4);

Write a Query to display the employ record for eno between 1&3. SQL> select * from employ where eno between 1 and 3;

Write a Query to display the employ record for eno not between 1&3. SQL> select * from employ where eno not between 1 and 3;

Write a Query to display the employ record for hire date is ’01- JAN-1996’ and eno>3. SQL> select * from employ where hiredate>’01-JAN-1996’ and eno>3;

Write a Query to display the employ record for hire date is ’01-JAN-1996’ or eno>3. SQL> select * from employ where hiredate>’01-JAN-1996’ or eno>3;

Write a Query to display the employ record for hire date is null. SQL> select * from employ where hiredate is null;

Write a Query to display the employ record in the descending order of ename. SQL> select * from employ order by ename desc;

Write a Query to display the employ record in the ascending order of job and descending order of ename.

SQL> select * from employ order by job asc,ename desc;

Write a Query to display the initial letters of the ename in the upper case. SQL> select initcap(ename) from employ;

Write a Query to display ename in the lower case. SQL> select lower(ename) from employ;

Write a Query to display ename in the Upper case. SQL> select upper(ename) from employ;

Write a Query to display length of ename. SQL> select length(ename) from employ;

Write a Query to display the maximum salary of employees SQL> select max(sal) from employ;

Write a Query to display the minimum salary of employees. SQL> select min(sal) from employ;

Write a Query to count the values in the commission. SQL> select count(comm) from employ;

Write a Query to display the average salaries of employees. SQL> select avg(sal) from employ;

Write a Query to display the total salaries of employees in job wise. SQL> select job,sum(sal) from employ group by job order by job;

Write a Query to display the total salaries of employees for job in lecturer. SQL> select job,sum(sal) from employ group by job having job=’lecturer’;

Write a Query to display the employees with their department names. SQL> select ename,dname from employ,dept where employ.dno=dept.dno;

Write a Query to display the total salaries in department wise in ascending order. SQL> select dname,sum(sal) from employ,dept where employ.dno=dept.dno group by dname order by dname;

Write a Query to display the total salary of computer science department. SQL> select dname,sum(sal) from employ,dept where employ.dno=dept.dno group by dname having dname=’computerscience’;

Write a Query to display the employ record matched or unmatched with department. SQL> select * from employ left outer join dept on employ.dno=dept.dno;

Write a Query to display the employ record who are having the same job for eno=4. SQL> select * from employ where job in (select job from employ where eno=4);

Display those employees who joined the company before 15th _{of month.}

SQL> select * from employ where to_char(hiredate,’dd’)<15;

Display those employees who completed 32 years of service. SQL> delete from employ where months_between (sysdate,hiredate)/12>=32;

Update the employ salary by 25% whose experience is >10years. SQL> update employ set sal=sal+sal*25/100 where months between (sys date,hiredate)/12>10;

Determine the employ whose total salary is like the minimum salary of any department. SQL> select * from employ where sal * 1 in(select min(sal) from employ dept where employ.dno=dno group by ename);

List the employees whose salary is greater than his manager’s salary.

List the eno, ename, job, dname, loc for those who are working as managers. SQL>select eno,ename,job,dname,loc from employ,dept where job=’lecturer’ and

employ.dno=dept.dno;

List the ename, job and manager who are without manager. SQL>select ename,job,mgr from employ where mgr is null;

List the names of departments where at least 2employees are working in each department. SQL>select dname.count(employ.dno) from employ dept where employ.dno=dno group by dname having count(employ.dno)>3;

Find out the first 2 earners of the company in ascending orders. SQL>select * from employ where eno<3 order by sal asc;

Find out the number of employees whose salary is greater than their manager’s salary. Select * from emp e where sal>(select sal from emp where empno=e.mgr);

List first 50% of character of ename in lower case and remaining are upper case. Select ename, lower(substr(ename,length(ename)/2), uppercase(substr(ename, (length(ename)/2+1,length(ename))) from emp;

List the employees who are working as managers. SQL>select * from employ where eno in (select mgr from employ);

List the employees who joined in the company on the same date. SQL>select * from employ where hiredate in (select hiredate from employ having count (hiredate)>1 group by hiredate;

List the employees with hire date in format June 4,1988. Select to_char(hiredate,’month dd yyyy’)from emp;

Find out the employees who joined in the company before their managers.

Select * from emp e where hiredate<(select hiredate from emp where empno=e.mgr);

List the department numbers where there are no employees. SQL>select dno,dname from dept where dno not in (select dno from employ);

Display the jobs which are unique to dept no 10.

select job from emp where deptno=10 minus select job from empwhere deptno!=10; (or)

Shipment Database

:

An enterprise wishes to maintain the details about his suppliers and other corresponding details to that it uses the following tables.

Table s (sid, sname, address) SQL>select * from s;

Table p (pid, pname, color) SQL>select * from p;

Table cat (sid, pid, cost) SQL>select * from cat;

Find the pname of parts for which there is same supplier. SQL>select p.pname from s,p,cat where s.sid=cat.sid and cat.pid=p.pid;

Find the sname of suppliers who supply part. SQL>select distinct(sname) from s,cat where s.sid=cat.sid;

Find the sname of suppliers who supply every black part. SQL>select distinct(sname) from s,p,cat where s.sid=cat.sid and cat.pid=p.pid and p.color=’black’;

Find the pname of part supplied by Akividu supplier. SQL>select pname from s,p,cat where address=’akvd’ and s.sid=cat.sid and cat.pid=p.pid;

Using group by with having clause get the pact numbers for all the parts supplied by more than one supplier.

Select pid from s,p where s.sid=cat.sid groupby pid having count distinct(cat.sid)>1;

Find the Sid of suppliers who supply a black and a blue part. SQL>select distinct(s.sid) from s,p,cat where s.sid=cat.sid and cat.pid=p.pid and p.color=’black’ intersect distinct(s.sid) from s,p,cat where s.sid=cat.sid and cat.pid=p.pid and p.color=’blue’;

Find the Sid of suppliers who supply a black or blue part. SQL>select distinct(s.sid) from s,p,cat where s.sid=cat.sid and cat.pid=p.pid and p.color=’black’ union select distinct(s.sid) from s,p,cat where s.sid=cat.sid and cat.pid=p.pid and p.color=’blue’;

PL/SQL PROGRAMS

1. Write a PL/SQL program to swap two numbers without using third variable.

Declare

a number(3);

b number(3);

Begin

a: =10;

b: =20;

dbms_output.put_line ('before swapping a='||a);

dbms_output.put_line ('before swapping b='||b);

a:=a+b;

b:=a-b;

a:=a-b;

dbms_output.put_line ('after swapping a='||a);

dbms_output.put_line ('after swapping b='||b);

End;

/

O/P:-2. Write a PL/SQL program to find the biggest of three given number.

Declare

a number(3);

b number(3);

c number(3);

Begin

a:=&a;

b:=&b;

c:=&c;

if(a>b)then

if(a>c)then

dbms_output.put_line ('a is big');

else

dbms_output.put_line ('b is big');

end if;

else

if(b>c)then

dbms_output.put_line ('b is big');

else

dbms_output.put_line ('c is big');

end if;

end if;

end;

/

O/P:-3. Write a PL/SQL program to display “sum” of even numbers and sum of odd

numbers in the given range.

Declare

n number(5);

even_sum number(5);

odd_sum number (5);

i number(5);

begin

n:=&n;

even_sum:=0;

odd_sum:=0;

for i in 1..n loop

if mod(i,2)=0then

even_sum:=even_sum+i;

end if;

if mod(i,2)=1 then

odd_sum:=odd_sum+i;

end if;

end loop;

dbms_output.put_line('odd_sum is'||odd_sum);

End;

/

O/P:-4. Write a PL/SQL program to generate Multiplication table.

SQL> declare

i number(5);

j number(5);

begin

i:=2;

while(i<=6)loop

for j in 1..20 loop

dbms_output.put_line(j || '*' || i || ‘=’ || j*i);

end loop;

i:=i+2;

end loop;

end;

/

1*2 = 2

2*2 = 4

3*2 = 6

4*2 = 8

5*2 = 10

6*2 = 12

7*2 = 14

8*2 = 16

9*2 = 18

10*2 = 20

11*2 = 22

12*2 = 24

13*2 = 26

14*2 = 28

15*2 = 30

16*2 = 32

17*2 = 34

18*2 = 36

1*4 = 4

2*4 = 8

3*4 = 12

4*4 = 16

5*4 = 20

6*4 = 24

7*4 = 28

8*4 = 32

9*4 = 36

10*4 = 40

11*4 = 44

12*4 = 48

13*4 = 52

14*4 = 56

15*4 = 60

16*4 = 64

17*4 = 68

18*4 = 72

20*2 = 40

20*4 = 80

20*6 = 120

5. Write a PL/SQL program to generate prime no’s in a given range.

declare

i number := &num;

flag number(1) :=0;

begin

for l in 2..i loop

for m in 2..l-1 loop

if mod(l,m) = 0 then

flag := 1;

exit;

end if;

end loop;

if flag = 0 then

dbms_output.put_line('* '||l);

end if;

flag := 0;

end loop;

* end;

/

Output:

Enter value for num: 10

old 2: i number := &num;

new 2: i number := 10;

* 3

* 5

* 7

PL/SQL procedure successfully completed.

SQL> /

Enter value for num: 20

old 2: i number := &num;

new 2: i number := 20;

* 2

* 3

* 5

* 7

* 11

* 13

* 17

* 19

PL/SQL procedure successfully completed.

6. write a PL/SQL program to find the Armstrong numbers in a given range.

declare

i number(5);

j number(5);

s number(5);

r number(5);

n number(5);

m number(5);

n:=&n;

for i in 1..n loop

s:=0;

j:=i;

m:=j;

while(j>0) loop

r:=mod(j,10);

s:=s+(r*r*r);

j:=trunc(j/10);

end loop;

if(s=m)then

dbms_output.put_line (i || 'amstrong');

end if;

end loop;

end;

/

Enter value for n: 300

old 9: n:=&n;

new 9: n:=300;

1amstrong

153amstrong

7. Write a PL/SQL program to generate strong numbers in a given range.

declare

i number(5);

j number(5);

r number(5);

n number(5);

k number(5);

f number(20);

m number(5);

begin

n:=&n;

for i in 1..n loop

s:=0;

j:=i;

m:=j;

while(j>0) loop

r:=mod (j,10);

f:=1;

for k in 1..r loop

f:=f * k;

end loop;

s:=s+f;

j:=trunc(j/10);

end loop;

if s=m then

dbms_output.put_line(i || 'strong');

end if;

end loop;

end;

Enter value for n: 50

old 11: n:=&n;

new 11: n:=50;

1strong

2strong

PL/SQL procedure successfully completed.

SQL> /

Enter value for n: 300

old 11: n:=&n;

new 11: n:=300;

1strong

2strong

145strong

PL/SQL procedure successfully completed.

8. Write a PL/SQL program to generate palindrome numbers in a given range

declare

n number(5);

s number(5);

r number(5);

i number(5);

j number(5);

begin

i:=1;

n:=&n;

j:=i;

s:=0;

while (j>0) loop

r:=mod(j,10);

s:=(s*10)+r;

j:=trunc(j/10);

end loop;

if i=s then

dbms_output.put_line(i || ' is palindrome');

end if;

end loop;

end;

/

Output:

SQL> /

Enter value for n: 100

old 9: n:=&n;

new 9: n:=100;

1 is palindrome

2 is palindrome

3 is palindrome

4 is palindrome

5 is palindrome

6 is palindrome

7 is palindrome

9 is palindrome

11 is palindrome

22 is palindrome

33 is palindrome

44 is palindrome

55 is palindrome

66 is palindrome

77 is palindrome

88 is palindrome

99 is palindrome

PL/SQL procedure successfully completed.

9. Write a PL/SQL program to prepare an Electricity Bill using the following table.

Write a pl/sql procedure to prepare an electricity bill using the following table.

Table name is elect

Name

Type

mno

number(3)not null

ename

varchar2(20)

cur_read

number(5)

prev_read

number(5)

no_units

number(5)

amount

number(8,2)

ser_tax

number(8,2)

Net_amt

number(9,2)

Create table elect(mno number(3)not null,ename varchar2(20),cur_read

number(5),prev_read number(5),no_units number(5),amount number(8,2),ser_tax

number(8,2),net_amt number(9,2));

insert into elect(mno,ename,cur_read,prev_read)values(001,'mahesh',500,350);

insert into elect(mno,ename,cur_read,prev_read)values(002,'teja',400,250);

insert into elect(mno,ename,cur_read,prev_read)values(003,'banti',600,150);

insert into elect(mno,ename,cur_read,prev_read)values(004,'syam',400,100);

insert into elect(mno,ename,cur_read,prev_read)values(005,'teja',700,450);

select * from elect;

MNO ENAME CUR_READ PREV_READ NO_UNITS AMOUNT SER_TAX NET_AMT

1. mahesh 500 350

2. Teja 600 250

3. Banti 600 150

4. Syam 500 100

5. Teja 700 450

Main Program:

declare

v_mno elect.mno %type;

v_ename elect.ename %type;

v_cur_read elect.cur_read %type;

v_prev_read elect.prev_read %type;

cursor c_elect is select mno,ename,cur_read,prev_read from elect;

begin

open c_elect;

exit when c_elect %notfound;

elect_bill(v-mno,v_ename,v_cur-read,v_prev_read);

end loop;

end;

/

Creating procedure:

create or replace procedure elect_bill(t_mno number,

t_ename varchar2,

t_cur_read number,

t_prev_read number)

is

v_no_units elect.no units %type;

v_amount elect.amount %type;

v_ser_tax elect.ser_tax %type;

v_net_amt elect.net_amt %type;

v_rate number;

begin

v_no_units:=t_cur_read-t_prev_read;

if v_no_units<=100 then

v_rate:=1.50;

elsif v_no_units<=300 then

v_rate:=2.50;

else

end if;

v_amount:=v_no_units*v_rate;

v_ser_tax:=v_amounts*(7.5/100);

v_net_amt:=v_amount+v_ser_tax;

update elect set no_units=v_no_unit,amount=v_amount,ser_tax=v_ser_tax,

net_amt=v_net_amt where mno=t_mno;

dbms_output.put_line('total net amount'||v_net_amt);

end;

/

Output:

Select * from elect;

MN O

ENAM E

CUR_REA D

PREV_REA D

NO_UNIT S

AMOUN T

SER_TA X

NET_AM T

1.

mahes

h

500

350

2.

Teja

600

250

3.

Banti

600

150

4.

Syam

500

100

5.

Teja

700

450

Note:

10. Write a PL/SQL program Procedure to evaluate the grade of student with

following conditions:

1. For pass: All marks > 40

2. For I class :Total percent>59

3. For II class :Total percent B/W >40 and < 60

4. For III class :Total percent =40

Table std:

NAME TYPE

No number not null

Name varchar2(20)

Class varchar2(10)

m1,m2,m3,m4,m5 number

And also maintain the details in abstract table details in abstract table.

Table Abstract:

Name Type

std-no number

std-name varchar2(10)

class varchar2(10)

tot number

grade varchar2(10)

percent number

date-enter date

Program:

Creating student table:

create table std(sno number(5),name varchar2(10),class varchar2(10),m1

number(5),m2 number(5),m3 number(5),m4 number(5),m5 number(5));

insert into std values(1,'Rameshwar','bsc',56,76,49,79,81);

insert into std values(2,'Santosh','bsc',35,50,70,66,70);

insert into std values(3,'Chaitu','bsc',48,50,70,46,60);

insert into std values(4,'Varma','bsc',79,68,93,88,70);

insert into std values(5,'Towfi','bsc',43,49,47,50,45);

select * from std;

NO

NAME

CLASS M1 M2 M3 M4 M5

1.

Rameshwar bsc 56 76 49 79 81

2. Santhosh bsc 35 50 70 66 70

4. Varma bsc 79 68 93 88 70

5. Syam bsc 43 49 47 50 45

Creating Abstract table

:

create table Abstract(std_no number(5),std_name varchar2(10),class varchar2(10),tot

number(5),grade varchar2(10),percent number,date_enter date);

Creating procedure:

create or replace procedure evaluate_grade(v_no number,v_name varchar2,v_class

varchar2,v_M1 number,v_M2 number,v_M3 number,v_M4 number,v_M5

number);

v_total Abstract1.Total %type;

v_grade Abstract1.grade %type;

v_percent Abstract1.percent %type;

v_date_enter Abstract1.date_enter %type;

v_total=v_M1+v_M2+v_M3+v_M4+v_M5;

v_percent:=v_tota/5;

if(v_m1>40 and v_m2>40 and v_m3>40 and v_m4>40 and v_m5>40)then

if(v_percent>59)then

v_grade:='first';

else if(v_percent>40)then

v_grade:='second';

else

v_grade:='third';

end if;

else

v_grade:='fail';

end if;

insert into Abstract

values(v_no,v_name,v_name,v_class,v_total,v_grade,v_percent,sys date);

end;

/

Main program:

declare

v_m2 std1.m2 %type;

v_m3 std1.m3 %type;

v_m4 std1.m4 %type;

v_m5 std1.m5 %type;

cusor cur_std1 is select * from std1;

begin

open cur_std1;

loop

fetch cur_sur into v_no,v_name,v_class,v_m1,v_m2,v_m3,v_m4,v_m5;

exit when cur_std1 %notfound;

evaluate_grade(v_no,v_name,v_class,v_m1,v_m2,v_m3,v_m4,v_m5);

end loop;

end;

/

Output:

Std.no Std_name

Class Tot Grade

Percent Date_enter

1

Rameshwar Bsc

341 First

68.2

29-dec -2014

2

Santosh

Bsc

291 Fail

58.2

29-dec -2014

3

Chaitu

Bsc

274 Second 54.8

29-dec -2014

4

Varma

Bsc

398 First

79.6

29-dec -2014

11. Write a PL/SQL program to find the Factorial of a given number.

Creating a function:

create or replace function factorial(n number)return number as

i number(3);

f number(3):=1;

begin

for i in 1..n loop

f:=f*i;

end loop;

return f;

end;

Calling function:

declare

n number(5);

f number(3);

begin

n:=5;

dbms_output.put_line('the factorial is'||factorial(n));

end;

/

Output:

SQL> /

The factorial is 120

12. Write a trigger on price of the product table. Fire the trigger when the product

price is decreased above 25%.

Create a product table:

Create table product1(prod_id number(5),prod_name varchar2(10),price number(6,2));

insert into product1 values(101,'tubes',300);

insert into product1 values(102,'tyers',500);

insert into product1 values(103,'bolts',100);

insert into product1 values(104,'nuts',700);

insert into product1 values(105,'seats',900);

select * from product1;

PROD_ID PROD_NAME PRICE

______________________________

101 tubes 300

102 tyers

500

103 bolts

100

104 nuts

700

105 seats

900

Creating product_ Qudit Table:

-create table product_Qudit(prod_id number(5),old_price number(6,2),new_price

number(6,2));

Creating a

trigger:-create or replace trigger bvrice before delete or insert or update on product1 for each row

when (new.price<(old.price*0.75) )

begin

dbms_output.put_line('prod_id'||:old.prod_id);

dbms_output.put_line('old_price'||:old.price);

dbms_output.put_line('new_price'||:new.price);

insert into

product_qudit(prod_id,old_price,new_price)values(:old.prod_id,:old.price ,:new.price);

end before_product_update;

Firing a

SQL> update product1 set price=price*0.70 where prod_id in( 102,105);

prod_id 102

old_price 500

new_price 350

The price reduction is morethan 25%

prod_id 105

old_price 900

new_price 630

The price reduction is morethan 25%