Practical Solution for Software Lab (SQL & PL/SQL)

SQL QUERIES (Based on DDL statement, constraints, DML statement, SELECT statement and Views.)

Note: In all schemas, Create the table with necessary constraints (PK, FK, Notnull, Unique and Check
constraints) on SQL prompt and then solve the given queries.)

Question 1

Customer Item schema queries which fall in all the categories mentioned above.

CUST (Custno, cname, state, phone)

ITEM(item no, Itemname, Itemprice, Qty_hand)
INVOICE(Invno, invDate, Custno)
INVITEM (Invno, Itemno, Qty)

Q.1 Create four table along with necessary constraints (PK, FK, notnull, Unique and
Check constraints)

Ans: - Below Create all four table CUST, ITEM, INVOICE, INVITEM

create table cust

(Custno varchar2(5) constraint cust_custno_pk primary key,
cname varchar2(25) constraint cust_cname_uk unique,
state varchar2(10),
phone number(10),
constraint cust_st_ck CHECK (state in('Gujarat','Rajasthan','MP','UP','Maharastra')));

create table item

(itemno varchar2(5) constraint item_itemno_pk primary key,
Itemname varchar2(25) constraint item_itemname_nn not null,
Itemprice number(8,2) constraint item_price_ck CHECK (Itemprice > 0),
Qty_hand number(3));

create table invoice

(Invno varchar2(5) constraint invoice_invno_pk primary key,
invDate date,
Custno varchar2(5) constraint invoice_Custno_fk REFERENCES cust (Custno)
ON DELETE CASCADE);
 create table invitem
(Invno varchar2(5),
Itemno varchar2(5),
Qty number(3),
constraint invitem_Invno_fk FOREIGN KEY (Invno) REFERENCES invoice (Invno)
ON DELETE SET NULL,
constraint invitem_itemno_fk FOREIGN KEY (itemno) REFERENCES item (itemno) ON
DELETE SET NULL);

Q.2 Write a Insert script for insertion of rows with substitution variables

Ans: - Insert Query Given below based on substitution variables

Insert Query for cust table

insert into cust (Custno,cname,state,phone) values('&Custno','&cname','&state',&phone);

OUTPUT

SQL> select * from cust;

CUSTN CNAME STATE PHONE

----- ------------------------- ---------- ----------
C0001 keyur Gujarat 9033750321
C0002 vipul Rajasthan 9033750322
C0003 akash MP 9033750327
C0004 ajit UP 9033750388
C0005 afzal Maharastra 9033750377

Insert Query for item table

insert into item (itemno,Itemname,Itemprice,Qty_hand) values ('&itemno','&Itemname',&itemprice,

&Qty_hand);

OUTPUT

SQL> select * from item

ITEMN ITEMNAME ITEMPRICE QTY_HAND
----- ------------------------- ---------- ----------
I0001 Pendrive 450 400
I0002 Floppy 25 50
I0003 cd 10 110
I0004 Mouse 400 75
I0005 Keybord 700 125

Insert Query for invoice table

insert into invoice(Invno,invDate,Custno) values ('&Invno','&invDate','&Custno')

OUTPUT

SQL> select * from invoice;

INVNO INVDATE CUSTNO

----- --------- -----
IN001 05-SEP-10 C0001
IN002 08-SEP-10 C0002
IN003 10-SEP-10 C0003
IN004 15-SEP-10 C0004
IN005 21-SEP-10 C0005

Insert Query for invitem

insert into invitem(invno,itemno,qty) values ('&invno','&itemno',&qty)

OUTPUT

SQL> select * from invitem;

INVNO ITEMN QTY

----- ----- ---------------------
IN001 I0001 10
IN002 I0002 15
IN003 I0003 20
IN004 I0004 25
IN005 I0005 30
Q.3 Add a column to the Item table, which will allow us to store Item color field.

SQL> alter table item

add (color varchar2 (8));

update item set color='Black' where itemno='I0001';

update item set color='Blue' where itemno= 'I0002';
update item set color='Silver' where itemno='I0003';
update item set color='White' where itemno='I0004';
update item set color='Green' where itemno='I0005';

SQL> select * from item;

ITEMNO ITEMNAME ITEMPRICE QTY_HAND COLOR

----- ------------------------- ---------- ---------- -------------------------------------
I0001 Pendrive 450 400 Black
I0002 Floppy 25 50 Blue
I0003 cd 10 110 Silver
I0004 Mouse 400 75 White
I0005 Keybord 700 125 Green

Q.4 Write SELECT statement for the given queries.

a. Display Item name, Price in sentence form using concatenation

select itemname || ' sold at Rs :-'|| itemprice from item

OUTPUT

Item information
---------------------------
Pendrive sold at Rs :-450
Floppy sold at Rs :-25
cd sold at Rs :-10
Mouse sold at Rs :-400
Keybord sold at Rs :-700
 b. Find total value of each item based on quantity on hand

select itemname, itemprice*qty_hand "Total value of each Item" from item;

OUTPUT

ITEMNAME Total value of each Item

------------------------- ------------------------
Pendrive 180000
Floppy 1250
cd 1100
Mouse 30000
Keybord 87500

c. Find customers who are from state of Gujarat.

select * from cust where state='Gujarat';

OUTPUT

CUSTN CNAME STATE PHONE

----- ------------------------- ---------- ------------------------
C0001 keyur Gujarat 9033750321

d. Display items with unit price of at least Rs. 100

select itemname,itemprice from item where itemprice>100;

OUTPUT

ITEMNAME ITEMPRICE
------------------------- ----------------------
Pendrive 450
Mouse 400
Keybord 700
 e. List items whose range lies between Rs. 200 and Rs. 500

select itemname,itemprice from item where itemprice between 200 and 500;

OUTPUT

ITEMNAME ITEMPRICE
------------------------- -----------------------
Pendrive 450
Mouse 400

g. Find all customers whose name start with Letter ‘k’.

select cname from cust where cname like 'k%';

OUTPUT

CNAME
-------
Keyur

h. Find name of items with ‘b’ in their name.

select * from item where itemname like '%b%' or itemname like 'b%' or itemname like '%b';

OUTPUT

ITEMN ITEMNAME ITEMPRICE QTY_HAND COLOR

----- ------------------------- ---------- ---------- ---------------------------
I0005 Keybord 700 125 Green
i. Sort all customers alphabetically

Select * from cust order by cname;

OUTPUT

CUSTN CNAME STATE PHONE

----- ------------------------- ---------- ----------------------------
C0005 afzal Maharastra 9033750377
C0004 ajit UP 9033750388
C0003 akash MP 9033750327
C0001 keyur Gujarat 9033750321
C0002 vipul Rajasthan 9033750322

j. Sort all items in descending order by their prices.

select * from item order by itemprice desc;

OUTPUT

ITEMN ITEMNAME ITEMPRICE QTY_HAND COLOR

----- ------------------------- ---------- ---------- -------
I0005 Keybord 700 125 Green
I0001 Pendrive 450 400 Black
I0004 Mouse 400 75 White
I0002 Floppy 25 50 Blue
I0003 cd 10 110 Silver

k. Display all customers from M.P alphabetically

select cname from cust where state='MP' order by cname;

OUTPUT

CNAME
------
akash
l. Display invoices dates in ‘September 05, 2010’ format.

select to_char(invdate,'month dd,yyyy') "Invoice Date" from invoice;

OUTPUT

Invoice Date
-----------------
september 05,2010
september 08,2010
september 10,2010
september 15,2010
september 21,2010

m. Find total, average, highest and lowest unit price

select sum(itemprice) "Total" , avg(itemprice) "Average" ,min(itemprice) "Lowest", max(itemprice)

"Highest" from item;

OUTPUT

Total Average Lowest Highest

------ ---------- ---------- ----------------
1585 317 10 700

n. Count number of items ordered in each invoice

select invno,count(qty) "number of item ordered" from invitem group by invno

OUTPUT

INVNO number of item ordered

----- ----------------------
IN001 1
IN002 1
IN003 1
IN004 1
IN005 1
IN006 0
o. Find invoices in which three or more items are ordered.

select item.itemname,invitem.itemno from item,invitem where item.itemno=invitem.itemno and qty >=3;

OUTPUT

ITEMNAME ITEMN
------------------------- -----
Pendrive I0001
Floppy I0002
cd I0003
Mouse I0004
Keybord I0005

p. Find all possible combination of customers and items (use Cartesian product)

select cname,itemname from cust,item;

q. Display all item quantity and item price for invoices ( natural join)

select invno,sum(qty),sum(qty*itemprice) "item price" from item natural join invitem group by
invno;

OUTPUT

INVNO SUM(QTY) item price

----- ---------- -------------------------
IN001 10 4500
IN002 15 375
IN003 20 200
IN004 25 10000
IN005 30 21000

r. Find total price amount for each invoice.

Select invno, sum(qty_hand * itemprice) "Total Price" from item,invitem where

item.itemno=invitem.itemno group by invno;
OUTPUT

INVNO Total Price

----- --------------------
IN001 180000
IN002 1250
IN003 1100
IN004 30000
IN005 87500

s. Use outer join to display items ordered as well as not ordered so far.

select invitem.invno,item.itemname,invitem.itemno from item,invitem where

item.itemno=invitem.itemno(+);

OUTPUT

INVNO ITEMNAME ITEMN

----- ------------------------- -----------------
IN001 Pendrive I0001
IN002 Floppy I0002
IN003 cd I0003
IN004 Mouse I0004
IN005 Keybord I0005
Motherbord

t. Find invoices with ‘Floppy’ in their item name.

Select invno, itemname from item, invitem where item.itemno=invitem.itemno and

itemname='Floppy;

OUTPUT

INVNO ITEMNAME
----- ---- ------- --------
IN002 Floppy

u. Display name of items ordered in invoice number IN001

Select invitem.itemno,itemname from invitem,item where invitem.itemno=item.itemno and

invno='IN001';
OUTPUT

ITEMN ITEMNAME
---------- -----------
I0001 Pendrive

v. Find the items that are cheaper than ‘Bullet’.

Select itemno,itemname,itemprice from item where itemprice < (select itemprice from item where
itemname like 'Keybord')

OUTPUT

ITEMN ITEMNAME ITEMPRICE

----- ------------------------- ------------------------
I0001 Pendrive 450
I0002 Floppy 25
I0003 cd 10
I0004 Mouse 400

w. Create a table ( namely guj_cust) for all Gujarat customer based on existing
customer table

Create table guj_cust as select * from cust where state like 'Gujarat';

OUTPUT

Table created.

SQL> select * from guj_cust;

CUSTN CNAME STATE PHONE

----- ------------------------- ---------- ---------------------------
C0001 keyur Gujarat 9033750321
C0006 ayaz Gujarat 9033750327

x. Copy all M.P customers to the table with Gujarat customers

Insert into guj_cust select * from cust where state like 'MP';
OUTPUT

SQL> select * from guj_cust;

CUSTN CNAME STATE PHONE

----- ------------------------- ---------- ------------------
C0001 keyur Gujarat 9033750321
C0006 ayaz Gujarat 9033750327
C0003 akash MP 9033750327

y. Rename Guj_cust table to MP_cust table.

Rename guj_cust to MP_cust;

OUTPUT

SQL> select * from MP_cust;

CUSTN CNAME STATE PHONE

----- ------------------------- ---------- ------------------
C0001 keyur Gujarat 9033750321
C0006 ayaz Gujarat 9033750327
C0003 akash MP 9033750327

z. Find the customers who are not in Gujarat or M.P

Select cname,state from cust where state not in ('Gujarat','MP')

OUTPUT

CNAME STATE
------------------------- ----------
vipul Rajasthan
ajit UP
afzal Maharastra

aa. Delete rows from customer table that are also in MP_cust table

DELETE FROM cust

WHERE custno = ANY
(SELECT custno FROM MP_cust);
OUTPUT

3 rows deleted.

SQL> select * from cust;

CUSTN CNAME STATE PHONE

----- ------------------------- ---------- ---------------------------------
C0002 vipul Rajasthan 9033750322
C0004 ajit UP 9033750388
C0005 afzal Maharastra 9033750377

SQL> select * from MP_cust;

CUSTN CNAME STATE PHONE

----- ------------------------- ---------- ----------------------------
C0001 keyur Gujarat 9033750321
C0006 ayaz Gujarat 9033750327
C0003 akash MP 9033750327

bb. Find the items with top three prices

Select itemname from (select itemname from item order by itemprice desc) where rownum<=3;

OUTPUT

ITEMNAME
-----------
Motherbord
Keybord
Pendrive

cc. Find two items with lowest quantity on hand

Select itemname from (select itemname from item order by qty_hand) where rownum<=2;

OUTPUT

ITEMNAME
----------------
Motherbord
Floppy
dd. Create a simple view with item names and item price only

Create view vit as select itemname, itemprice from item;

View created.

SQL> select * from vit;

ITEMNAME ITEMPRICE
------------------------- -------------------------
Pendrive 450
Floppy 25
cd 10
Mouse 400
Keybord 700
Motherbord 15000

6 rows selected.

ee. Create a sequence that can be used to enter new items into item table

OUTPUT

Create sequence seqc

increment by 1
start with 7
maxvalue 100

Sequence created.

ff. Add a new item into item table with sequence just created.

insert into item values(seqc.nextval,'Datacard',5000,20,'Gray')

1 row created.

gg. Create a index file to speed up a search based on customer State

Create index state_idx on cust (state);

Index created.
SQL> select INDEX_NAME,TABLE_NAME from USER_IND_COLUMNS;

INDEX_NAME TABLE_NAME
------------------------------ --------------------------
CUST_CUSTNO_PK CUST
CUST_CNAME_UK CUST
STATE_IDX CUST

hh. Lock customer Mr. Shah record to update the state and phone no.

Transaction 1
Select state,phone from cust where custno='C0002' for update;

STATE PHONE
---------- -----------------
Rajasthan 9033750322

SQL> commit;

Transaction 2

lock table cust in exclusive mode;

Table(s) locked

Transaction 1
update cust set state='Gujarat' where custno='C0002';

Transaction 2
Commit;

ii. Give everybody select and insert rights on your item table

Grant select, insert on item to Hardik;

jj. Revoke the insert option on item table from user ‘Roshi’

Revoke insert on item Roshi;

END OF QUESTION NO 1
 Question 2
STUDENT(rollno,name,class,birthdate)
COURSE(courseno, coursename, max_marks, pass_marks)
SC(rollno,courseno,marks)

Q.1 Create four table along with necessary constraints (PK, FK, notnull, Unique and
Check constraints)

Ans: - Below Create all Three table STUDENT, COURSE,SC

create table student

( rollno varchar2(4) constraint st_rno_pk primary key,
name varchar2(30) constraint st_name_nn not null,
class varchar2(10),
birthdate date) ;

create table course

(courseno varchar2(4)constraint co_cono_pk primary key,
coursename varchar2(50) constraint co_coname_nn not null,
max_marks number(3) constraint chk_max_marks check(max_marks>0),
pass_marks number(3),
constraint chk_pass_marks check(pass_marks>0 and pass_marks<max_marks));

create table sc
( rollno varchar2(4),
courseno varchar2(4),
marks number(3),
constraint sc_rno_cno_pk primary key(rollno,courseno),
constraint sc_rno_fk foreign key(rollno) references student(rollno),
constraint sc_cno_fk foreign key(courseno) references course(courseno));

Q.2 Write a Insert script for insertion of rows with substitution variables

insert into student values ('&rollno','&name','&class','&birthdate');

SQL> select * from student;

ROLL NAME CLASS BIRTHDATE

---- ------------------------------ ---------- ---------------------------------
1 atul A 03-AUG-10
2 mahesh B 04-JUL-75
3 nirav C 07-JUN-76
4 habiba D 05-FEB-88
5 priya E 19-MAR-95

insert into course values('&courseno','&coursename',&max_marks,&pass_marks)

SQL> select * from course;

COURSENO COURSENAME MAX_MARKS PASS_MARKS

---- -------------------------------------------------- ---------- ----------
1 MCA 100 40
2 BCA 100 35
3 DBMS 100 40
4 NETWORKING 100 40
5 MATHS 100 40

insert into sc values('&rollno','&courseno','&marks');

SQL> select * from sc;

ROLLNO COURSENO MARKS

---- ---- -------------------------------------
1 1 65
2 2 76
3 3 87
4 4 72
5 5 54
1 2 51
1 3 48
1 4 69
1 5 43
2 1 40
2 3 54
ROLLNO COURSENO MARKS
---- ---- --------------------------------------
2 4 61
2 5 75
3 1 63
3 2 81
3 4 90
3 5 71
4 1 78
4 2 87
4 3 92
4 5 76
5 1 76

ROLLNO COURSENO MARKS

---- ---- -------------------------------
5 2 75
5 3 43
5 4 56

1. Add constraint that marks entered are between 0 to 100 only.

alter table sc add constraint chk_sc_marks check(marks between 0 and 100);

Table altered.

2. While creating COURSE table, primary key constraint was forgotten. Add the primary key now .

alter table course add constraint p_courseno primary key(courseno);

Table altered.

3. Display details of student where course is ‘Data Base Management System’.

select student.rollno,name,class,birthdate,coursename from student,course,sc where

sc.courseno=course.courseno and sc.rollno=student.rollno and course.coursename='DBMS';
 OUTPUT

ROLLNO NAME CLASS BIRTHDATE COURSENAME

---- ------------------------------ ---------- --------- -----------------------------------------
3 nirav C 07-JUN-76 DBMS

4. Select student names who have scored more than 70% in Computer Networks and
have not failed in any subject.

select name from student,course,sc where (sc.courseno=course.courseno and

sc.rollno=student.rollno) and (coursename='NETWORKING' and marks>=70) and (marks>=45);

OUTPUT

NAME
-------
habiba
nirav

5. Select names and class of students whose names begin with ‘a’ or ‘b’.

select name,class from student where name like 'a%' or name like 'b%'

OUTPUT

NAME CLASS
------------------------------ -------
atul A

6. Display average marks obtained by each student.

select rollno,round(avg(marks),2) from sc group by rollno;

OUTPUT
ROLL ROUND (AVG(MARKS),2)
---- ----------------------------------------------
1 55.2
2 61.2
3 78.4
4 81
5 60.8
7. Select all course where passing marks are more than 30% of average maximum marks .

select coursename from course where pass_marks>(select avg(max_marks)*30/100 from course);

OUTPUT

COURSENAME
---------------------
MCA
BCA
DBMS
NETWORKING
MATHS

8. Select the course where second and third characters are ‘AT’.

select coursename from course where coursename like '_AT%';

OUTPUT

COURSENAME
--------------------
MATHS

9. Display details of students born in 1975 or 1976.

select *from student where to_char(birthdate,'yyyy') in('1975','1976');

OUTPUT

ROLL NAME CLASS BIRTHDATE

---- ------------------------------ ---------- ----------------------------------
2 mahesh B 04-JUL-75
3 nirav C 07-JUN-76
 Question 3

HOSTEL (H#, hname, haddr, total_capacity, warden)

ROOM (h#, r#, rtype, location, no_of_students, status)
CHARGES (h#, rtype, charges)
STUDENT (sid, sname, saddr, faculty, dept, class, h#, r#)
FEES (sid, fdate, famount)

The STATUS field tells us whether the room is occupied or vacant. The charges
represent the term fees to be paid half yearly. A student can pay either the annual fees at
one time or the half yearly fees twice a year.

Q.1 Create five table along with necessary constraints (PK, FK, notnull, Unique and
Check constraints)

Ans: - Below Create all five tables HOSTEL, ROOM, CHARGES, STUDENT, FEES

create table hostel

(H# varchar2(2) constraint pk_h_hostel primary key,
hname varchar2(20),
haddr varchar2(25),
total_capacity number(3),
warden varchar2(20));

create table room

(H# varchar2(2),
r# number(3),
rtype varchar2(1),
location varchar2(10),
no_of_students number(1),
status varchar2(1) constraint ck_room_status CHECK(status IN('O' ,'V')),
constraint pk_room_H#_r# primary key(H#,r#),
constraint fk_room_H# FOREIGN KEY(H#) REFERENCES hostel (H#))
create table charges
(H# varchar2(2),
rtype varchar2(1),
charges number(4),
constraint pk_charges_H#_rtype primary key(H#, rtype),
constraint fk_charges_H# FOREIGN KEY (H#) REFERENCES hostel (H#));

create table student_hostel

(S# varchar2(10) constraint pk_student primary key,
sname varchar2(25),
saddr varchar2(35),
faculty varchar2(15),
dept varchar2(10),
class varchar2(10),
H# varchar2(2),
R# number(3),
constraint fk_student_r# FOREIGN KEY(H#,r#) REFERENCES ROOM(H#,r#));

create table fees

(S# varchar2 (10),
fdate date,
famount number(5),
constraint pk_fees_sid_fdate primary key(S#,fdate),
constraint fk_room_S# FOREIGN KEY (S#) REFERENCES student_hostel(S#));

Q.2 Add a check constraint to the room table so that the room type allows the following
values only – ‘s’ for single, ‘d’ for double, ‘t’ for triple and ‘f’ for four-seater.

Ans :- SQL> alter table room

2 ADD constraint CK_ROOM_TYPE CHECK(rtype IN ('s' , 'd' ,'t' ,'f'));

Table altered.

Q. Write a Insert script for insertion of rows with substitution variables

Ans: - Insert Query Given below based on substitution variables

insert into hostel values('&H#','&hname','&haddr',&total_capacity,'&warden');

OUTPUT

SQL> select * from hostel;

H# HNAME HADDR TOTAL_CAPACITY WARDEN

-- -------------------- ------------------------- -------------- -------------------------------
1# chanakya Vadodara-Gpark 32 Anil
2# V.V.Nagar V.V.Nagar/College 450 Arvindbhai
3# Rajkot/Mca-college Rajkot/bus-stand 400 Rameshbhai

insert into room values('&H#',&r,'&rtype','&location',&no_of_students,'&status');

OUTPUT

SQL> select * from room;

H# R# rtype LOCATION NO_OF_STUDENTS Status

-- ---------- - ---------- -------------- -------------------------------------------
1# 100 s left 2 O
2# 101 s right 3 O
2# 200 d left 5 V
2# 201 d right 6 V
3# 300 t left 7 V
3# 301 t right 8 O
1# 104 f right 9 O
1# 105 f left 9 V
1# 108 d right 4 O
1# 109 t left 8 O
2# 205 t left 8 V

H# R# rtype LOCATION NO_OF_STUDENTS Status

-- ---------- - ---------- -------------- ------------------------------------
2# 206 f right 9 O
3# 305 s right 3 O
3# 306 d left 5 V
3# 307 f right 9 O
1# 106 d left 9 O
1# 107 s right 6 O
1# 103 d left 6 O
insert into charges values('&H','&rtype',&charges);

OUTPUT

SQL> select * from charges;

H# rtype CHARGES
-- - ------------------------
1# s 1000
1# d 2000
1# t 3000
1# f 4000
2# s 5000
2# d 5300
2# t 5600
2# f 6000
3# s 7000
3# d 8500
3# t 8600
3# f 9000

insert into student_hostel values('&S#','&sname','&saddr','&faculty','&dept','&class','&H#',&r)

OUTPUT

SQL> select * from student_hostel;

S# SNAME SADDR FACULTY DEPT CLASS H# R#

---------- ------------------------- ----------------------------------- --------------- ---------- ---------------------
S0001# pratik vasad commarce B.ed fyB.ed 1# 100
S0002# jignesh himmatnager computer MCA MCA1 2# 200
S0003# kailash baroda management MBA MBA3 3# 306
S0004# haresh mehsana engg mech 3sem 2# 201
S0005# urvish patan medical md 1sem 1# 108
S0006# kalpesh palanpur medical medicine 4sem 1# 105
S0007# haresh unja management mba4 mba4 1# 109
S0008# prakash vasad commerce M.ed fyM.ed 1# 104
S0009# rajiv mehsana cs msc msc1 2# 101
S0010# vikas himmatnagar engg mech 1sem 3# 301
S0011# ayan sidhpur engg it 1sem 2# 206
S# SNAME SADDR FACULTY DEPT CLASS H# R#
---------- ------------------------- ----------------------------------- --------------- ---------- -------------------------------
S0012# afzal mehsana computer MCA MCA3 3# 305
S0013# rafiq mehsana MCA MCA MCA1 3# 307
S0014# pankaj mehsana commarce B.ed fyBed 1# 106
S0015# Ajay mehsana commarce M.ed fymed 1# 103

insert into fees values('&S#','&fdate',&famount);

OUTPUT

SQL> select * from fees;

S# FDATE FAMOUNT
---------- --------- ----------
S0001# 23-MAR-10 1000
S0002# 25-JUL-10 5300
S0003# 10-AUG-10 8500
S0004# 18-NOV-03 5300

2. Display the total number of rooms that are presently vacant.

select COUNT(*) "ROOMS ARE VACANT" from room where status='V';

OUTPUT

ROOMS ARE VACANT

----------------
7

3. Display number of students of each faculty and department wise staying in each hostel.

select faculty,dept,H#,COUNT(*) from student_hostel GROUP BY faculty,dept,H#

OUTPUT

FACULTY DEPT H# COUNT(*)

--------------- ---------- -- ----------
engg mech 2# 1
medical md 1# 1
commarce B.ed 1# 1
COMPUTER_SCIENCE MCA 2# 1
management MBA 3# 1

4. Display hostels, which have at least one single-seated room.

select *
from hostel
where H# IN
(select DISTINCT(H#)
from room
where rtype='s');

OUTPUT

H# HNAME HADDR TOTAL_CAPACITY WARDEN

-- -------------------- ------------------------- -------------- ---------------------------------------
1# Chanakya Vadodara-Gpark 100 Anil
2# V.V.Nagar V.V.Nagar/College 200 Arvindbhai
3# Rajkot/Mca-college Rajkot/bus-stand 300 Rameshbhai

5. Display the warden name and hostel address of students of Computer Science department

select sname,class,hname,haddr,warden
from student_hostel s,hostel h
where s.H#=h.H#
and UPPER(dept)='COMPUTER SCIENCE’
OUTPUT

SNAME CLASS HNAME HADDR WARDEN

------------------------- ---------- -------------------- ------------------------- -------------------------
jignesh MCA1 V.V.Nagar V.V.Nagar/College Arvindbhai

6. Display those hostel details where single seated or double-seated rooms are vacant.

select *
from hostel
where H# IN
(select H#
from room
where rtype IN ('s','d') AND status= 'V');

OUTPUT

H# HNAME HADDR TOTAL_CAPACITY WARDEN

-- -------------------- ------------------------- -------------- ------------------------------------
1# Chanakya Vadodara-Gpark 100 Anil
2# V.V.Nagar V.V.Nagar/College 200 Arvindbhai
3# Rajkot/Mca-college Rajkot/bus-stand 300 Rameshbhai

7. Display details of hostels occupied by medical students.

select *
from hostel
where H# IN
(select H#
from student_hostel
where lower(faculty)='medical')

OUTPUT

H# HNAME HADDR TOTAL_CAPACITY WARDEN

-- -------------------- ------------------------- -------------- ----------------------------------
1# Chanakya Vadodara-Gpark 100 Anil
8. Display hostels, which are totally occupied to its fullest capacity.

select *
from hostel
where (H#, total_capacity) IN
(select H#, count(*)
FROM student_hostel
group by H#);

OUTPUT

1# Chanakya

9. List details about students who are staying in the double-seated rooms of Chanakya Hostel.

select *
from student_hostel
where r# IN
(select r#
from room
where rtype='d')
and H# IN
(select H# from hostel
where hname='chanakya')

OUTPUT

S# SNAME SADDR FACULTY DEPT CLASS H# R#

---------- ------------------------- ----------------------------------- --------------- ---------- ---------------------
S0015# Ajay mehsana commarce M.ed fymed 1# 103
S0014# pankaj mehsana commarce B.ed fyBed 1# 106
S0005# urvish patan medical md 1sem 1# 108

10.Display the total number of students staying in each room type of each hostel.

select r.H#,r.rtype,count(*)
from student_hostel s, room r
where S.r# = r.r# AND S.H# = r.H#
group by r.H#,r.rtype;
OUTPUT

H# rtype COUNT(*)
-- - -----------------------
1# d 3
1# f 2
1# s 1
1# t 1
2# d 2
2# f 1
2# s 1
3# d 1
3# f 1
3# s 1
3# t 1

11. Display details about students who have paid fees in the month of Nov. 2003.

select *
from student_hostel
where S# IN
(select S#
from fees
where TO_CHAR(fdate,'MON YYYY') = 'NOV 2003');

OUTPUT

S# SNAME SADDR FACULTY DEPT CLASS H# R#

---------- ------------------------- ----------------------------------- --------------- ---------- ------------------------
S0004# haresh mehsana engg mech 3sem 2# 201

11.For those hostels where total capacity is more than 300, display details of students studying in
Science faculty.

select *
from student_hostel
where faculty = 'cs'
AND H# IN
(select H#
from hostel
where total_capacity > 300);
OUTPUT

S# SNAME SADDR FACULTY DEPT CLASS H# R#

---------- ------------------------- ----------------------------------- --------------- ---------- ----------------------
S0009# rajiv mehsana cs msc msc1 2# 101

12.Display hostel details where there are at least 3 vacant rooms.

select *
from hostel
where H# IN
(select H#
from room
where status = 'V'
group by H#
having count(*) >= 3)

OUTPUT

H# HNAME HADDR TOTAL_CAPACITY WARDEN

-- -------------------- ------------------------- -------------- ---------------------------------------
2# V.V.Nagar V.V.Nagar/College 450 Arvindbhai

13.Display details of students who have still not paid fees.

14.
select *
from student_hostel
where S# IN
(select S#
from student_hostel
MINUS
select DISTINCT(S#)
from fees)

OUTPUT

S# SNAME SADDR FACULTY DEPT CLASS H# R#

---------- ------------------------- ----------------------------------- --------------- ---------- ---------------------------------------
S0005# urvish patan medical md 1sem 1# 108
S0006# kalpesh palanpur medical medicine 4sem 1# 105
S0007# haresh unja management mba4 mba4 1# 109
S0008# prakash vasad commerce M.ed fyM.ed 1# 104
S0009# rajiv mehsana cs msc msc1 2# 101
S0010# vikas himmatnagar engg mech 1sem 3# 301
S0011# ayan sidhpur engg it 1sem 2# 206
S0012# afzal mehsana computer MCA MCA3 3# 305
S0013# rafiq mehsana MCA MCA MCA1 3# 307
S0014# pankaj mehsana commarce B.ed fyBed 1# 106
S0015# Ajay mehsana commarce M.ed fymed 1# 103

15.Display those hostels where single-seated room is the costliest

select *
from hostel
where H# IN
(select H#
from charges
where rtype='s'
AND charges =
(select MAX(charges)
from charges
where rtype = 's'));

OUTPUT

H# HNAME HADDR TOTAL_CAPACITY WARDEN

-- -------------------- ------------------------- -------------- ---------------------------------------
3# Rajkot/Mca-college Rajkot/bus-stand 400 Rameshbhai

Question 4

Screen(screen_id,location ,seating_cap)
Movie(movie_id,movie_name,date_of_release)
Current(screen_id,movie_id,date_of_arrival,date_of_closure)

Note: In all schemas, Create the table with necessary constraints (PK, FK, Notnull, Unique and Check
constraints) on SQL prompt and then solve the given queries.)

Q.1 Create Three table along with necessary constraints (PK, FK, notnull, Unique and
Check constraints)

Ans: - Below Create all four table Screen, Movie, Current

 create table screen
(screen_id varchar2(3) constraint sc_pk primary key,
location varchar2(3) constraint lo_nn not null,
seating_cap number(3) constraint se_nn not null,
constraint sid check(screen_id like ('S%')),
constraint loc check(location in('FF','SF','TF')),
constraint sc check(seating_cap>0));

create table movie

(movie_id varchar2(3) constraint mv_pk primary key,
movie_name varchar2(20) constraint mvn_uk unique,
date_of_release date not null);

create table current_movie

(screen_id varchar2(3),
movie_id varchar2(6),
date_of_arrival date not null,
date_of_closure date not null,
constraint dtc check(date_of_arrival<date_of_closure),
constraint sc_fk foreign key(screen_id) references screen(screen_id),
constraint mv_fk foreign key(movie_id) references movie(movie_id));

Q.2 Write a Insert script for insertion of rows with substitution variables

insert into screen values ('&screen_id','&location',&seating_cap);

SQL> select * from screen;

SCREEN LOCATION SEATING_CAP

--- --- ------------------------------------
S1 SF 400
S2 TF 350
S3 FF 250
S4 TF 300
S5 SF 150
insert into movie values('&movie_id','&movie_name','&date_of_release');

SQL> select * from movie;

MOVIE_ID MOVIE_NAME DATE_OF_RELEASE

--- -------------------- ---------------------------------------------------
M01 terminator 11-SEP-09
M02 wrong turn 10-AUG-08
M03 star wars III 21-FEB-05
M04 harry porter6 18-NOV-04
M05 saw7 10-DEC-10
insert into current_movie values('&screen_id','&movie_id','&date_of_arrival','&date_of_closure');

SQL> select * from current_movie;

SCREEN_ID MOVIE_ID DATE_OF_ARRIVAL DATE_OF_CLOSURE

--- ------ --------- -------------------------------------------------------------------------------
S1 M01 12-SEP-09 01-OCT-09
S2 M02 01-AUG-09 20-AUG-09
S3 M03 20-AUG-10 24-SEP-10
S4 M04 05-NOV-10 26-NOV-10
S5 M05 03-DEC-10 31-DEC-10

Solve the following queries based on the above schema:

1. Get the name of movie which has run the longest in the multiplex so far.

select movie_name from movie where movie_id in(select movie_id from current_movie where
(date_of_closure - date_of_arrival) in (select max(date_of_closure-date_of_arrival) from
current_movie));

OUTPUT

MOVIE_NAME
----------------
star wars III
2. Get the average duration of a movie on screen number ‘S4’.

Select avg(to_number(date_of_closure-date_of_arrival)) "NO OF AVG DAY FOR S4" from

current_movie where screen_id='S4';

OUTPUT

NO OF AVG DAY FOR S4

--------------------
21

3. Get the details of movie that closed on date 26-november-2010.

Select * from movie where movie_id in (select movie_id from current_movie where
date_of_closure ='26-NOV-10')

OUTPUT

MOVIE_ID MOVIE_NAME DATE_OF_RESEASE

--- -------------------- ------------------------------------------------
M04 harry porter6 18-NOV-04

4. Movie ‘star wars III ‘ was released in the 7th week of 2005. Find out the date of its
release considering that a movie releases only on Friday.

Select NEXT_DAY(date_of_release,'FRIDAY') from movie where movie_name='star wars III';

OUTPUT

NEXT_DAY
-----------------
25-FEB-05

5 Get the full outer join of the relations screen and current.

For making outer join you have to set null value in the table there is row missing in both table.
select s.screen_id,s.location,c.movie_id from screen s full outer join current_movie c
on(s.screen_id=c.screen_id);

OUTPUT

SCREEN_ID LOCATION MOVIE_ID

--- --- -----------------------------------------
S1 SF M01
S2 TF M02
S3 FF M03
S4 TF M04
M05

Question 5

DISTRIBUTOR (DNO, DNAME, DADDRESS, DPHONE)

ITEM (ITEMNO, ITEMNAME, COLOUR, WEIGHT)
DIST_ITEM (DNO, ITEMNO, QTY)

Note: In all schemas, Create the table with necessary constraints (PK, FK, Notnull, Unique and Check
constraints) on SQL prompt and then solve the given queries.)

Q.1 Create Three table along with necessary constraints (PK, FK, notnull, Unique and
Check constraints)

Ans: - Below Create all three table distributor, item, dist_item.

SQL> create table distributor

2 (dno varchar2(2) constraint pk_distributor primary key,
3 dname varchar2(15) constraint nn_dname not null,
4 daddress varchar2(25),
5 dphone number(10));

Table created.
SQL> create table item
2 (itemno varchar2(2) constraint pk_item primary key,
3 itemname varchar2(15) constraint nn_itamname not null,
4 color varchar2(10),
5 weight number(3) constraint ck_weight check(weight>0));

Table created.

SQL> create table dist_item

2 (dno varchar2(2),
3 itemno varchar2(2),
4 qty number(4),
5 constraint pk_dno_itemno primary key(dno,itemno),
6 constraint fk_dist_dno foreign key (dno) references distributor(dno),
7 constraint fk_dist_itemno foreign key(itemno) references item(itemno));

Table created.

Q.2 Write a Insert script for insertion of rows with substitution variables

insert into distributor values ('&dno', '&dname',' &daddress', &dphone);

SQL> select * from distributor;

DNO DNAME DADDRESS DPHONE

-- --------------- ------------------------- -------------------------------
d1 abbas himmatnagar 225305
d2 ashish visnagar 225306
d3 verpoh mehsana 225307
d4 prakash ahmedabad 225308
d5 anil london 225309

insert into item values ('&itemno','&itemname','&colour',&weight);

SQL> select * from item;

ITEMNO ITEMNAME COLOR WEIGHT
-- --------------- ---------- --------------------------------------
i1 bolt black 5
i2 cd white 25
i3 monitor green 50
i4 motherboard green 150
i5 chair gray 100
i6 boadr red 40
i7 hammer black 70
i8 mouse black 25

insert into dist_item values ('&dno','&itemno',&qty);

SQL> select * from dist_item;

DNO ITEMNO QTY

-- -- ------------------------------
d1 i1 1000
d2 i2 100
d3 i3 50
d4 i4 250
d5 i5 300
d5 i6 800

1. Add a column CONTACT_PERSON to the DISTRIBUTOR table with the not null constraint.

alter table distributor

ADD (contact_person varchar2(25) constraint nn_contact not null);

NOTE: - you have to perform this query before insert record when table is empty

2. Create a view LONDON_DIST on DIST_ITEM which contains only those

records where distributors are from London. Make sure that this condition is
Checked for every DML against this view.

SQL> create view london_dist AS

2 select di.dno,di.itemno,di.qty
3 from dist_item di,distributor d
4 where di.dno=d.dno
5 AND LOWER(DADDRESS) LIKE '%london%'
6 with check option;
OUTPUT

SQL> select * from london_dist;

DNO ITEMNO QTY

-- -- ---------------------------
d5 i5 300

3. Display details of all those items that have never been supplied.

SQL> select * from item

2 where itemno in
3 (select itemno
4 from item
5 minus
6 select DISTINCT(itemno)
7 from dist_item);

OUTPUT

ITEMNO ITEMNAME COLOR WEIGHT

-- --------------- ---------- ------------------------------------------
i6 boadr red 40
i7 hammer black 70

4. Delete all those items that have been supplied only once.

SQL> DELETE FROM ITEM

2 where itemno IN
3 (SELECT itemno
4 from dist_item
5 group by itemno
6 HAVING COUNT(*) = 1);

5. List the names of distributors who have an ‘A’ and also a ‘B’ somewhere in their names .

SQL> select dname

2 from distributor
3 where LOWER (dname) LIKE '%a%b%'
 4 OR LOWER (dname) LIKE '%b%a%';
OUTPUT

DNAME
----------
Abbas

6. Count the number of items having the same colour but not having weight between
20 and 100.

SQL> select color,count(*) from item

2 where weight not between 20 AND 100
3 group by color;

OUTPUT

COLOR COUNT(*)
---------- -----------------------
black 1
green 1

7. Display all those distributors who have supplied more than 1000 parts of the same type.

select * from distributor

where dno IN
(SELECT dno
from dist_item
group by dno
having SUM(QTY) > 1000);

OUTPUT

DNO DNAME DADDRESS DPHONE

-- --------------- ------------------------- -----------------------------
d5 anil london 225309
8. Display the average weight of items of same colour provided at least three items have that colour .

SQL> select color,avg(weight)

2 from item
3 group by color
4 having count(*) >= 3;

OUTPUT

COLOR AVG(WEIGHT)
---------- --------------------------
black 33.3333333

9. Display the position where a distributor name has an ‘OH’ in its spelling somewhere after the
fourth character.

select lower(dname) || 'HAS "oh" IN ITS NAME AT POSITION =>' || TO_CHAR (INSTR
(lower(dname), 'oh',4)) “Result” from distributor;

OUTPUT

Result
--------------------------------------------
abbasHAS "oh" IN ITS NAME AT POSITION =>0
ashishHAS "oh" IN ITS NAME AT POSITION =>0
verpohHAS "oh" IN ITS NAME AT POSITION =>5
prakashHAS "oh" IN ITS NAME AT POSITION =>0
anilHAS "oh" IN ITS NAME AT POSITION =>0

10. Count the number of distributors who have a phone connection and are supplying
item number ‘i1’.

select count(*)
from distributor d,dist_item di
where d.dphone is not null
and d.dno=di.dno
and di.itemno='i1';
OUTPUT

COUNT(*)
--------------
1

11. Create a view on the tables in such a way that the view contains the distributor
name, item name and the quantity supplied.

create VIEW NEWVIEW AS

SELECT d.dname NAME, i.itemname ITEM, di.qty QUANTITY
from distributor d, item i , dist_item di
where d.dno=di.dno
AND i.itemno=di.itemno;

View created.

OUTPUT
SQL> select * from newview;

NAME ITEM QUANTITY

--------------- --------------- ----------
abbas bolt 1000
ashish cd 100
verpoh monitor 50
prakash motherboard 750
anil chair 300
anil boadr 800

12. List the name, address and phone number of distributors who have the same three
digits in their number as ‘Mr. abbas’.

select dname,daddress,dphone
from distributor
where SUBSTR(dno,1,3) IN
(select SUBSTR(dno,1,3)
from distributor

where lower(dname) = 'abbas');

OUTPUT

DNAME DADDRESS DPHONE

--------------- ------------------------- -----------------
abbas himmatnagar 225305

13. List all distributor names who supply either item i1 or i7 and the quantity
supplied is more than 100.

select dname,dt.itemno,qty
from distributor d,item i,dist_item dt
where d.dno = dt.dno AND i.itemno = dt.itemno
AND i.itemno IN ('i1','i7') AND qty > 100;

OUTPUT

DNAME IT QTY
--------------- -- ----------------
abbas i1 1000

14. Display the data of the top three heaviest ITEMS

select ROWNUM,itemno,itemname,weight
from (select itemno,itemname,weight
from item
order by weight desc)
where ROWNUM <= 3

OUTPUT

ROWNUM ITEMNO ITEMNAME WEIGHT

------- -- --------------- -------------------------------------------
1 i4 motherboard 150
2 i5 chair 100
3 i7 hammer 70\
Question 6
WORKER (worker_id, name, wage_per_hour, specialised_in, manager_id)
JOB (job_id, type_of_job, status)
JOB_ASSIGNED (worker_id, job_id, starting_date, number_of_days)

Note: In all schemas, Create the table with necessary constraints (PK, FK, Notnull, Unique and Check
constraints) on SQL prompt and then solve the given queries.)

Q.1 Create Three table along with necessary constraints (PK, FK, notnull, Unique and
Check constraints)

Ans: - Below Create all three WORKER, JOB, JOB_ASSIGNED.

create table worker

(worker_id varchar2(10) constraint pk_worker primary key,
name varchar2(15) constraint nn_name not null,
wage_per_hour number(3) constraint ck_wage check(wage_per_hour >=0),
specialised_in varchar2(15),
manager_id varchar2(10) constraint pk_mgrid primary key);

Table created.

create table job

(job_id varchar2(10) constraint pk_job primary key,
type_of_job varchar2(15) constraint nn_type_job not null,
status varchar2(12));

Table created.

create table job_assigned

(worker_id varchar2(10),
job_id varchar2(10),
starting_date date,
number_of_days number(4),
constraint pk_worker_job primary key (worker_id,job_id),
constraint fk_job_ass_worker_id foreign key (worker_id) references worker(worker_id),
constraint fk_job_ass_job_id foreign key (job_id) references job(job_id));

Table created.
Q.2 Write a Insert script for insertion of rows with substitution variables

insert into worker values('&worker_id','&name',&wage_per_hour,'&specialised_in','&manager_id');

SQL> select * from worker;

WORKER_ID NAME WAGE_PER_HOUR SPECIALISED_IN MANAGER_ID

---------- --------------- ------------- --------------- -----------------------------------------------------------------
w01 mr.cacophonix 50 polishing m01
w02 paresh 90 accounting m02
w03 chintan 65 fitting m03
w04 jitendra 75 polishing m04
w05 mahesh 80 marketing m05

insert into job values ('&job_id', '&type_of_job', '&status');

SQL> select * from job;

JOB_ID TYPE_OF_JOB STATUS

---------- --------------- --------------------------
J0001 packing not complete
J0002 editing not complete
J0003 molding not complete
J0004 purchasing not complete
J0005 printing complete

insert into job_assigned Values ('&worker_id', '&job_id', '&starting_date', &number_of_days);

WORKER_ID JOB_ID STARTING_ NUMBER_OF_DAYS

---------- ---------- --------- --------------------------------------------------
w01 J0001 05-DEC-04 40
w02 J0002 25-DEC-2010 35
w03 J0003 12-MAR-00 1500
w04 J0004 04-FEB-11 65
w05 J0005 21-JAN-11 98
 1. Display the date on which each worker is going to end his presently assigned job.

select starting_date + number_of_days "Ending Data"

from job_assigned
where (starting_date + number_of_days) >= sysdate;

OUTPUT

Ending Date
----------------
29-APR-11

2. Display how many days remain for each worker to finish his job.

select worker_id,job_id,(starting_date + number_of_days)-ROUND(SYSDATE) "Days

Remaining"
from job_assigned
where (starting_date + number_of_days) >= sysdate;

OUTPUT

WORKER_ID JOB_ID Days Remaining

---------- ---------- -----------------------------------
w05 J0005 97

3. Display the STARTING_DATE in the following format – ‘The fifth day of the
month of October, 2004’.

select worker_id,job_id,to_char(starting_date, '"THE" fmddspth "day of the month of " Month,

YYYY') "OUTPUT" from job_assigned;

OUTPUT

WORKER_ID JOB_ID OUTPUT

---------- ---------- ----------------------------------------------------
w01 J0001 THE fifth day of the month of October, 2004
w02 J0002 THE tenth day of the month of January, 2005
w03 J0003 THE twelfth day of the month of March, 2000
w04 J0004 THE twenty-fifth day of the month of July, 2001
w05 J0005 THE twenty-first day of the month of January, 2011
 4. Change the status to ‘Complete’ for all those jobs, which started in year 2000.

update job
set status='complete'
where job_id IN
(select job_id
from job_assigned where
TO_CHAR(starting_date, 'YYYY') = '2000');

OUTPUT

1 row updated.

SQL> select * from job;

JOB_ID TYPE_OF_JOB STATUS

---------- --------------- ------------
J0001 packing not complete
J0002 editing not complete
J0003 molding complete
J0004 purchasing not complete
J0005 printing complete

5 Display job details of all those jobs where at least 1 workers are working.

select * from job

where job_id IN
(select job_id from job_assigned
group by job_id
having count(*) >= 1);

OUTPUT

JOB_ID TYPE_OF_JOB STATUS

---------- --------------- ----------------------
J0001 packing not complete
J0002 editing not complete
J0003 molding complete
J0004 purchasing not complete
J0005 printing complete
6. Display all those jobs that are already completed.

select *
from job
where job_id not in
(select DISTINCT (job_id)
from job_assigned
where (starting_date + number_of_days) > sysdate);

OUTPUT

JOB_ID TYPE_OF_JOB STATUS

---------- --------------- ---------------------------
J0001 packing not complete
J0002 editing not complete
J0003 molding complete
J0004 purchasing not complete

7. Find all the jobs, which will begin within the next two weeks.

select t.job_id,t.type_of_job
from job t,job_assigned a
where t.job_id=a.job_id and
(a.starting_date > sysdate) and
(a.starting_date < (sysdate+14));

OUTPUT

JOB_ID TYPE_OF_JOB
---------- -----------------------
J0004 purchasing

8. List all workers who have their wage per hour ten times greater than the wage of their managers

select w.name "worker"

from worker w, worker m
where w.manager_id = m.worker_id AND
w.wage_per_hour > 10* m.wage_per_hour;
9. List the names of workers who have been assigned the job of molding.

select w.name
from worker w,job j,job_assigned a
where w.worker_id=a.worker_id and
j.job_id=a.job_id and
j.type_of_job='molding';

OUTPUT

NAME
----------
Chintan

10.What is the total number of days allocated for packaging the goods for all the workers together .

select sum(a.number_of_days) "Total Days for Packg"

from job j,job_assigned a
where j.job_id = a.job_id AND
j.type_of_job='packing'

OUTPUT

Total Days for Packg

--------------------
600

11.Which workers receive higher than average wage per hour.

select worker_id,name from worker

where wage_per_hour > (select avg(wage_per_hour)
from worker);

OUTPUT

WORKER_ID NAME
---------- ----------------------
w02 paresh

\
12.Display details of workers who are working on more than one job.

select * from worker

where worker_id IN
(select worker_id
from job_assigned
group by worker_id
having count(*) > 1);

13.Which workers having specialization in polishing start their job in December?

select w.worker_id,name
from worker w,job_assigned a
where specialised_in = 'polishing' and
w.worker_id = a.worker_id and
(to_char (starting_date, 'MONTH') = 'DECEMBER');

OUTPUT
WORKER_ID NAME
---------- -----------------------
w01 mr.cacophonix
w02 paresh

14.Display details of workers who are specialized in the same field as that of Mr. Cacophonix or
have a wage per hour more than any of the workers.

select *
from worker
where specialised_in IN
(select specialised_IN
from worker
where LOWER(name) = 'mr.cacophonix')
or wage_per_hour >
(select MAX(wage_per_hour)
from worker);

OUTPUT
WORKER_ID NAME WAGE_PER_HOUR SPECIALISED_IN MANAGER_ID
---------- --------------- ------------- --------------- -------------------------------------------------------
w01 mr.cacophonix 50 polishing m01
w02 paresh 360 polishing m02
w04 jitendra 75 polishing m04
Question 7
PUBLISHER(publ_id, publ_name, contact_person, contact_addr, contact_phone)
CATEGORY(cat_id, cat_details, max_books, duration)
BOOK_MASTER(book_id, bname, isbn_no, total_copies, publ_id)
MEMBER(member_id, mname, cat_id, mem_ship_dt)
ISSUE(ISSUE_id, member_id, book_id, issu_ret, issue_ret_dt)

Note: In all schemas, Create the table with necessary constraints (PK, FK, Notnull, Unique and Check
constraints) on SQL prompt and then solve the given queries.)

Q.1 Create five table along with necessary constraints (PK, FK, notnull, Unique and
Check constraints)

Ans: - Below Create all five. PUBLISHER, CATEGORY, BOOK_MASTER, MEMBER, ISSUE

create table publisher

(publ_id varchar2(3) constraint pk_publisher primary key,
publ_name varchar2(25),
contact_person varchar2(25),
contact_addr varchar2(35),
contact_phone number(9));

create table category

(cat_id number(1) constraint pk_category primary key,
cat_details varchar2(10) constraint nn_cat_details not null,
max_books number(2) constraint ck_max_books check(max_books >0),
duration number(2));

create table book_master

(book_id varchar2(10) constraint pk_book primary key,
bname varchar2(25) constraint nn_book not null,
isbn_no number(9) constraint uk_book unique,
total_copies number(2),
publ_id varchar2(3),
constraint fk_book_publ_id foreign key(publ_id) references publisher(publ_id));
create table member
(member_id varchar2(6) constraint pk_member primary key,
mname varchar2(6) constraint nn_member not null,
cat_id number(1),
mem_ship_dt date,
constraint fk_mem_cat_id foreign key (cat_id) references category (cat_id));

create table issue

(issue_id number(6) constraint pk_issue primary key,
member_id varchar2(6),
book_id varchar2(10),
issue_ret varchar2(1),
issue_ret_dt date,
constraint fk_issue_memeber_id foreign key (member_id) references member(member_id),
constraint fk_issue_book_id foreign key (book_id) references book_master(book_id))

In the above tables duration is in years and it stores the membership duration for that
category.
Change the table design of ISSUE table to add a constraint, which will allow only ‘I’ or
‘R’ to be entered in the ISSUE_RET column, which stores the action whether the book is
being issued or returned.

2. Add a column to the MEMBER table, which will allow us to store the address of the member

alter table member

add member_addr varchar2(35);

3. Create a table LIBRARY_USERS which has a structure similar to that of the MEMBER table
but with no records

create table library_users as

select * from member where member_id is null;

4. Give details about members who have issued books, which contain ‘DATA’
somewhere in their titles.

select *
 from member
where member_id IN

(select member_id
from issue
where issue_ret =’I’
AND book_id IN
(select book_id
from book_master
where upper(bname) like ‘%DATA%’));

5. Display the books that have been issued at the most three times in the year 2003.

select * from book_master

where book_id IN
(select book_id from issue
Where to_char(issue_ret_dt, ‘YYYY’) = ‘2003’
Group by book_id
Having count(*) <=3);

6. Display which books of publisher PHI that are issued right now.

select * from book_master

where publ_id IN
(select publ_id from publisher
Where publ_name = ‘PHI’)
AND book_id IN
(select book_id from issue where issue_ret = ‘I’);

7. Display details about books whose all copies are issued.

select * from book_master

where (book_id , total_copies) IN
(select book_id, count(member_id)
from issue
where issue_ret = ‘I’
group by book_id);

8. Display the book details and members for books, which have been issued between
1st Oct 2005 and 15th Nov 2005.
select bm.*,m.*
from book_master bm, member m, issue i
where bm.book_id = i.book_id
and m.member_id = i.member_id
and issue_ret = ‘I’
and issue_ret_dt between to_date(‘01/10/2005’, ‘dd/mm/yyyy’)
and to_date(‘15/11/2005’,’dd/mm/yyyy’);

9. Display all staff members who have issued at least two books.

select M.*
from member m,issue i , category c
where i.member_id = m.member_id
AND m.cat_id = c.cat_id
AND c.cat_details = ‘staff’
AND issue_ret = ‘I’
group by m.member_id
having count(*) >= 2;

10. Display details about those publishers whose more than 100 books are available in the library .

select * from publisher

where publ_id IN
(select publ_id
from book_master
group by publ_id
having count(*) > 100);

11. Delete all those members whose membership has expired.

delete from master

where member_id IN
(select member_id
from member m , category c
where m.cat_id = c.cat_id
AND (mem_ship_dt + 365*duration) <sysdate);
12 How many members registered in the last three months ?

select count(*) “mem. Reg last three month “

from member
where (round(sysdate) – mem_ship_dt) <= 90;

13 Display since how many months has each staff member registered.

select mname, cat_id,months_between(sysdate, mem_ship_dt)

from member
where cat_id IN
(select cat_id
from category
where cat_details = ‘staff’);

Question 8
APPLICANT (aid, aname, addr, abirth_dt)
ENTRANCE_TEST (etid, etname, max_score, cut_score)
ETEST_CENTRE (etcid, location, incharge, capacity)
ETEST_DETAILS (aid, etid, etcid, etest_dt, score)

Note: In all schemas, Create the table with necessary constraints (PK, FK, Notnull, Unique and Check
constraints) on SQL prompt and then solve the given queries.)

Q.1 Create four table along with necessary constraints (PK, FK, notnull, Unique and
Check constraints)

Ans: - Below Create all four APPLICANT, ENTRANCE_TEST, ETEST_CENTRE,

ETEST_DETAILS;

create table applicant

(aid varchar2(6) constraint pk_applicant primary key,
aname varchar2(25) constraint nn_applicant not null,
addr varchar2(35),
abirth_dt date);
create table entrance_test
(etid number(3) constraint pk_entrance primary key,
etname varchar2(25),
max_score number(3) constraint ck_ent_max check(max_score>0),
cut_score number(2),
constraint ck_ent_cut check (cut_score >0 and cut_score < max_score));

create table etest_centre

(etcid varchar2(6) constraint pk_ent_centre primary key,
location varchar2(25) constraint nn_ent_loc not null,
incharge varchar2(25),
capacity number(4) constraint ck_ent_cap check(capacity >0));

create table etest_details

(aid varchar2(6),
etid number(3),
etcid varchar2(6),
etest_dt date,
score number(3),
constraint fk_et_det_aid foreign key (aid) references applicant (aid),
constraint fk_et_det_etid foreign key (etid) references entrance_test(etid),
constraint fk_et_det_etcid foreign key (etcid) references etest_centre(etcid),
constraint pk_aid_etid_etcid primary key(aid,etid,etcid));

(This database is for a common entrance test which is being conducted at a number of
centers and can be taken by an applicant on any day except holidays)

1. Modify the APPLICANT table so that every applicant id has an ‘A’ before its
value. E.g. if value is ‘1123’, it should become ‘A1123’.

update applicant
set aid = ‘A’ || ltrim(aid);

2. Display test center details where no tests were conducted.

select *
from etest_centre
where etcid in
(select etcid
from etest_centre
MINUS
 select DISTINCT (etcid) from etest_details);
3. Display details about applicants who have the same score as that of Jaydev in
‘ORACLE FUNDAMENTALS’.

select * from applicant

where aid IN
(select aid from etest_details
where etid IN
(select etid from entrance_test
where etname = ‘ORACLE FUNDAMENTALS’)
AND score IN

(select score from etest_details

where aid IN
(select aid from applicant
where aname= ‘JAYDEV’)));

4. Display details of applicants who appeared for all tests.

select DISTINCT (a.aname)

from applicant a
where (select count(ed.etid) from etest_details ed
where ed.aid =a.aid) = (select count(et.etid) from entrance_test et);

5. Display those tests where no applicant has failed.

select *
from entrance_test
where etid not in
(select DISTINCT (et.etid)
from entrance_test et, etest_details ed
where ed.etid = et.etid and
score < cut_score);

6. Display details of entrance test centers which had full attendance between 1st Oct
05 and 15th Oct 05.

select ec.*
from etest_centre ec
where (etcid,capacity) IN
(select etcid,count(*)
from etest_details ed
 where etest_dt between to_date(’01-oct-05’) AND
to_date(’15-OCT-05’)
group by etcid, etest_dt);

7. Display details of the applicants who scored more than the cut score in the tests they appeared in.

select a.*
from applicant a, entrance_test t, etest_details ed
where ed.aid = a.aid and ed.etid = t.etid
AND ed.score >=t.cut_score;

8. Display average and maximum score test wise of tests conducted at Mumbai.

select etid,avg(score), max(score)

from etest_details ed, etest_centre ec
where ed.etcid = ec.etcid
AND location = ‘MUMBAI’
group by ETID;

9. Display the number of applicants who have appeared for each test, test center wise .

select etid,etcid,count(*)
from etest_details
group by etcid,etid;

10. Display details about test centers where no tests have been conducted .

select * from etest_centre

where etcid IN
((select etcid from etest_center)
MINUS
(select DISTINCT (etcid) from etest_details));

11. For tests, which have been conducted between 2-3-04 and 23-4-04, show details
of the tests as well as the test centres.

select et.*,ec.*
from entrance_test et , etest_details ed , etest_centre ec
where ed.etcid= ec.etcid
and ed.etid =et.etid
and etest_dt between to_date(’02-03-04,’DD-MM-YY’)
and to_date(’23-04-04’,’DD-MM-YY’);

12. How many applicants appeared in the ‘ORACLE FUNDAMENTALS’ test at

Chennai in the month of February ?

select count(*)
from entrance_test et , etest_details ed ,etest_centre ec
where ed.etcid=ec.etcid and ed.etid=et.etid
AND etname = ‘ORACLE FUNDAMENTALS’ AND location = ‘CHENNAI’
AND to_char(etest_dt,’MON’) = ‘FEB’;

13. Display details about applicants who appeared for tests in the same month as the
month in which they were born.

select a.*
from applicant a , etest_details ed
where ed.aid = a.aid
AND to_char(a.abirth_dt,’MM’) = to_char(ed.etest_dt,’MM’);

14. Display the details about APPLICANTS who have scored the highest in each test,
test centre wise.

select *
from applicant
where aid in
(select aid
from etest_details
where (etcid,etid,score) IN
(select etcid,etid,max(score)
from etest_details
group by etcid,etid));

15. Design a read only view, which has details about applicants and the tests that he
has appeared for.

create or replace view test_info as

select aname,etname,score
from applicant a , etest_details ed,entrance_test et
where ed.aid = a.aid and ed.etid = et.etid
with read only;