Software Lab (SQL & PLSQL)
Question – 1
1)CUSTOMER TABLE:
a)Create Customer Table with fields custno, cname, state, phone :
create table cust( custno varchar2(4) primary key constraint chk_custno
check(custno like 'c%'), cname varchar2(30) not null,
state varchar2(20), phone number(10) );
b)Insert Records into Customer Table:
insert into cust values
( '&custno','&cname','&state','&phone');
c)Viewing Records Entered in the customer Table:
select * from cust;
CUSTNO CNAME STATE PHONE
------
211 Prit Patel gujarat 73789
212 Hima Patel gujarat 57897
225 Tulsi MP 83432
239 Amish Patel MP 289316
285 Sugeet Patel UP 264585
2)ITEM TABLE:
a)Create Item Table with fields itemno, itemname, itemprice, qty_on_hand:
create table item
(
itemno varchar2(4) primary key constraint chk_itemno check(itemno like 'i%'),
itemname varchar2(30) not null,
itemprice number(10,2) constraint chk_itemprice check(itemprice>0),
qty_hand number(5) not null
);
b)Insert Records into Item Table:
insert into item values
(
'&itemno','&itemname',&itemprice,&qty_hand
);
c)Viewing Records Entered in the Item Table:
Select * from item;
ITEMNO ITEMNAME ITEMPRICE QTY_HAND ITEMCOLOR
------
1 Screw 2.25 50 GREY
2 Bullet 225 110 GREY
3 Bolt 390 75 GOLDEN
4 Hammer 9.99 125 BROWN
5 Washer 5.99 100 BLACK
6 Nail .99 300 BLUE
7 Gear 100 250 RED
7 rows selected.
3)INVOICE TABLE:
a)Create Invoice Table with fields invno, invdate, custno:
create table invoice
(
invno varchar2(4) primary key constraint chk_invno check(invno like 'i%'),
invdate date,
custno varchar2(4) references cust(custno)
);
b)Insert Records into Invoice Table:
insert into invoice values
(
'&invno','&invdate','&custno'
);
c)Viewing Records Entered in the Invoice Table:
Select * from invoice;
INVNO INVDATE CUSTNO
------
1 23-FEB-89 211
2 30-MAY-90 212
3 05-MAR-06 225
4 07-SEP-08 239
5 10-DEC-05 285
4)INVITEM TABLE:
a)Create InvItem Table with fields invno, itemno, qty :
create table invitem
(
invno varchar2(4),
itemno varchar2(4),
qty number(5),
primary key(invno,itemno),
foreign key(invno) references invoice(invno),
foreign key(itemno) references item(itemno)
)
b)Insert Records into InvItem Table:
insert into invitem values
(
'&invno','&itemno',&qty
)
c)Viewing Recored Entered in the InvItem Table:
Select * from invitem;
INVNO ITEMNO QTY
------
1 4 10
2 3 4
3 6 1
4 5 6
5 7 20
Select Queries:
3)Add a column to the Item table, which will allow us to store Item color field.
Ans. alter table item add(itemcolor varchar2(15));
Output : Table altered.
4)Write SELECT statements for the given queries.
a)Display Itemname, Price in sentence from using concatenation.
query : select itemname||' sold worth Rs.'||itemprice from item;
Output:
ITEMNAME||'SOLDWORTHRS.'||ITEMPRICE
------
Screw sold worth Rs.2.25
Bullet sold worth Rs.225
Bolt sold worth Rs.390
Hammer sold worth Rs.9.99
Washer sold worth Rs.5.99
Nail sold worth Rs..99
Gear sold worth Rs.100
7 rows selected.
b)Find total value of each item based on quantity on hand.
Ans. select itemname,itemprice*qty_hand "Total value of each Item" from item;
Output:
ITEMNAME Total value of each Item
------
Screw 112.5
Bullet 24750
Bolt 29250
Hammer 1248.75
Washer 599
Nail 297
Gear 25000
7 rows selected.
c)Find customer who are from state of Gujarat.
Ans. select cname from cust where state like 'Gujarat';
Output:
CNAME
------
Prit Patel
Hima Patel
d)Display items with unit price of at least Rs. 100
Ans. select itemname,itemprice from item where itemprice>=100;
Output:
ITEMNAME ITEMPRICE
------
Bullet 225
Bolt 390
Gear 100
e)List items where range lies between Rs. 200 and Rs. 500
Ans. select itemname from item where itemprice between 200 and 500;
Output:
ITEMNAME
------
Bullet
Bolt
f)Which customers are from lalbaug area of ahmedabad, Baroda and Patan.
Ans. select cname from cust where city in('ahmedabad','Baroda','Patan') and area like 'lalbaug';
Output:
CNAME
------
Prit Patel
Hima patel
g)Find all customers whose name start with Letter ‘P’
Ans. select cname from cust where cname like 'P%';
Output:
CNAME
------
Prit Patel
h)Find name of Items with ‘W’ in their name.
Ans. select itemname from item where itemname like '%w%';
Output:
ITEMNAME
------
Screw
i)Sort all customers alphabetically
Ans. select cname from cust order by cname;
Output:
CNAME
------
Amish Patel
Hima Patel
Prit Patel
Sugeet Patel
Tulsi
j)Sort all Items in descending order by their prices.
Ans. select itemname from item order by itemprice desc;
Output:
ITEMNAME
------
Bolt
Bullet
Gear
Hammer
Washer
Screw
Nail
7 rows selected.
k)Display all customers from M.P. alphabetically.
Ans. select cname from cust where state='MP' order by cname;
Output:
CNAME
------
Amish Patel
Tulsi
l)Display invoices dates in ‘September 05, 2007’ format.
Ans. select TO_CHAR(invdate,'MONTH DD, YYYY') from invoice;
Output:
TO_CHAR(INVDATE,'M
------
FEBRUARY 23, 1989
MAY 30, 1990
MARCH 05, 2006
SEPTEMBER 07, 2008
DECEMBER 10, 2005
m)Find total, average, highest and lowest unit price.
Ans. select sum(itemprice) "Total",avg(itemprice) "Average",max(itemprice) "Highest",min(itemprice)"Lowest" from item;
Output:
Total Average Highest Lowest
------
734.22 104.888571 390 .99
n)Count number of items ordered in each invoice.
Ans. select invno,count(qty) "number of item" from invitem group by invno;
Output:
INVNO number of item
------
1 1
2 1
3 1
4 1
5 1
o)Find invoice which three or more items are ordered.
Ans. select distinct invno from invitem where qty>=3;
Output:
INVNO
------
1
2
4
5
p)Find all possible combination of customers and items (use Certesian product)
Ans. select c.cname, i.itemname from cust c cross join item I;
Output:
CNAME ITEMNAME
------
Prit Patel Screw
Hima Patel Screw
Tulsi Screw
Amish Patel Screw
Sugeet Patel Screw
Prit Patel Bullet
Hima Patel Bullet
Tulsi Bullet
Amish Patel Bullet
Sugeet Patel Bullet
Prit Patel Bolt
CNAME ITEMNAME
------
Hima Patel Bolt
Tulsi Bolt
Amish Patel Bolt
Sugeet Patel Bolt
Prit Patel Hammer
Hima Patel Hammer
Tulsi Hammer
Amish Patel Hammer
Sugeet Patel Hammer
Prit Patel Washer
Hima Patel Washer
CNAME ITEMNAME
------
Tulsi Washer
Amish Patel Washer
Sugeet Patel Washer
Prit Patel Nail
Hima Patel Nail
Tulsi Nail
Amish Patel Nail
Sugeet Patel Nail
Prit Patel Gear
Hima Patel Gear
Tulsi Gear
CNAME ITEMNAME
------
Amish Patel Gear
Sugeet Patel Gear
35 rows selected.
q)Display all item quantity and item price for invoices ( natural join )
Ans. select invno,sum(qty),sum(qty*itemprice) "Item price" from invitem,item where invitem.itemno=item.itemno group by invno;
Output:
INVNO SUM(QTY) Item price
------
1 10 99.9
2 4 1560
3 1 .99
4 6 35.94
5 20 2000
r)Find total price amount for each invoice.
Ans. select invno,sum(qty*itemprice) "Total price" from invitem,item where invitem.itemno=item.itemno group by invno;
Output:
INVNO Total price
------
1 99.9
2 1560
3 .99
4 35.94
5 2000
s)Use outer join to display items ordered as well as not ordered so far.
Ans. select invitem.itemno "Order",item.itemno "not order" from invitem,item where invitem.itemno(+)=item.itemno;
Output:
Order not or
------
1
2
3 3
4 4
5 5
6 6
7 7
7 rows selected.
t)Find invoices with ‘Gear’ in their item name.
Ans. select invno from invitem,item where invitem.itemno=item.itemno and item.itemname='Gear';
Output:
INVNO
------
5
u)Display name of items orderd in invoice number 1001
Ans. select itemname from invitem,item where invitem.itemno=item.itemno and invno='in01';
Output:
ITEMNAME
------
Hammer
v)Find the items that are cheaper than ‘Bullet’
Ans. select itemname from item where itemprice<(select itemprice from item where itemname like 'Bullet');
Output:
ITEMNAME
------
Screw
Hammer
Washer
Nail
Gear
w)Create a table ( namely guj_cust ) for all Gujarat customer based on existing customer table
Ans. create table guj_cust as select *from cust where state='Gujarat';
Output: Table created.
x)Copy all M.P. customers to the table with Gujarat Customers.
Ans. insert into guj_cust select *from cust where state='MP';
Output: 2 rows created.
y)Rename Guj_cust table to MP_cust table.
Ans. rename guj_cust to mp_cust
Output: Table renamed.
z)Find the customers who are not in Gujarat or M.P.
Ans. select cname from cust where state not in('Gujarat','MP');
Output:
CNAME
------
Prit Patel
Hima Patel
Sugeet Patel
aa)Delete rows from customer table that are also in MP_cust table.
Ans. delete from cust where custno in(select custno from mp_cust);
Output:
delete from cust where custno in(select custno from mp_cust)
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.SYS_C003086) violated - child record found
bb)Find the Items with top three prices.
Ans. select itemname from(select itemname from item order by itemprice desc) where rownum<=3;
Output:
ITEMNAME
------
Bolt
Bullet
Gear
cc)Find two items with lowest quantity on hand.
Ans. select itemname from(select itemname from item order by qty_hand) where rownum<=2;
Output:
ITEMNAME
------
Screw
Bolt
dd)Create a simple view with item names and item price only.
Ans. create view v_dd as select itemname, itemprice from item;
Output: View created.
ee)Create a sequence that can be used to enter new items into item table.
Ans. create sequence seq_14ee increment by 1 start with 011 minvalue 011 maxvalue 999;
Output: Sequence created.
ff)Add a new Item into Item table with sequence just created.
Ans. insert into item values
(
'i'||seq_14ee.nextval,'Switch',23,121,'gray'
);
Output : 1 row created.
gg)Create a Index file to speed up a search based on customer table
Ans. create index index_14gg on cust(cname);
Output : Index created.
hh)Lock customer Mr. Shah record to update the state and phone no.
Ans.
Output:
ii)Give everybody select and insert right on your item table.
Ans. grant select,insert on item to Tulsi;
Output:
jj)Revoke the insert option on item table from user ‘Roshi’
Ans. revoke insert on item from roshi
Output:
Question - 2
1)STUDENT TABLE:
a)Create Student Table with fields rollno, name, class, birthdate:
create table student
(
rollno varchar2(4) primary key constraint chk_rollno check(rollno like 'r%'),
name varchar2(30) not null,
class varchar2(10),
birthdate date
);
b)Insert Records into Student Table:
insert into student values
(
'&rollno','&name','&class','&birthdate'
);
c)Viewing Records Entered it the Student Table:
Select * form student;
ROLLNO NAME CLASS BIRTHDATE
------
1 Pritesh Patel A 23-FEB-89
2 Sugeet Patel A 05-SEP-85
3 Dipesh Patel B 24-MAR-76
4 Chandresh patel B 17-APR-87
5 Bhavin Jilvaani A 25-DEC-75
2)COURSE TABLE:
a)Create Table:
create table course
(
courseno varchar2(4) primary key constraint chk_courseno check(courseno like 'c%'),
coursename varchar2(50),
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)
);
b)Insert Records into Course Table:
insert into course values
(
'&courseno','&coursename',&max_marks,&pass_marks
)
c)Viewing Record Entered in the Course Table:
Select * from course;
COURSENO COURSENAME MAX_MARKS PASS_MARKS
------
610001 FOP 90 40
610002 FOP.Prac 90 40
610003 MATHS 90 40
610004 COMP ORG 90 40
610005 DBMSI 90 40
610006 SQL & PL/SQL 90 40
610007 ERFM 90 40
7 rows selected.
3)SC TABLE:
a)Create SC Table with fields rollno, coursenom, marks:
create table sc
(
rollno varchar2(4),
courseno varchar2(4),
marks number(3) constraint chk_marks check(marks>0),
primary key(rollno,courseno),
foreign key(rollno) references student(rollno),
foreign key(courseno) references course(courseno)
);
b)Insert Records into SC Table:
insert into sc values
(
'&rollno','&courseno',&marks
);
c)Viewing Records Entered in the SC Table:
Select * from sc;
ROLLNO COURSENO MARKS
------
3 610005 70
3 610001 70
3 610002 68
3 610003 58
3 610004 74
3 610006 59
3 610007 55
1 610001 80
1 610002 89
1 610003 78
1 610004 88
ROLLNO COURSENO MARKS
------
1 610005 76
1 610006 85
1 610007 90
2 610001 90
2 610002 85
2 610003 78
2 610004 75
2 610005 68
2 610006 59
2 610007 74
4 610001 75
ROLLNO COURSENO MARKS
------
4 610002 45
4 610003 58
4 610004 68
4 610005 78
4 610006 62
4 610007 63
5 610001 70
5 610002 78
5 610003 52
5 610004 79
5 610005 85
ROLLNO COURSENO MARKS
------
5 610006 76
5 610007 80
35 rows selected.
Select Queries:
1)Add constraint that marks entered are between 0 to 100 only.
Ans. alter table sc add constraint chk_sc_marks check(marks between 0 and 100);
Output :
Table altered.
2)While creating COURSE table, primary key constraint was forgotten. Add the primary key now.
Ans. alter table course add constraint p_courseno primary key(courseno);
Output : Table altered
3)Display details of student where course is ‘Data Base Management System’
Ans. select student.rollno,name,class,birthdate from student,course,sc where sc.courseno=course.courseno and sc.rollno=student.rollno and course.coursename='DBMSI';
Output :
ROLLNO NAME CLASS BIRTHDATE
------
3 Dipesh Patel B 24-MAR-76
1 Pritesh Patel A 23-FEB-89
2 Sugeet Patel A 05-SEP-85
4 Chandresh patel B 17-APR-87
5 Bhavin Jilvaani A 25-DEC-75
4)Select student names who have scored more than 70% in Computer Networks and have not failed in any subject.
Ans. select name from student,course,sc where (sc.courseno=course.courseno and sc.rollno=student.rollno) and (coursename=’COMP ORG' and marks>=70) and (marks>=45);
Output:
NAME
------
Dipesh Patel
Pritesh Patel
Sugeet Patel
Bhavin Jilvaani
5)Select names and class of students whose names begin with ‘A’ or ‘B’.
Ans. select name,class from student where name like 'A%' or name like 'B%';
Output:
NAME CLASS
------
Bhavin Jilvaani FYMCA
6)Display average marks obtained by each student.
Ans. select rollno,round(avg(marks),2) from sc group by rollno;
Output:
ROLLNO ROUND(AVG(MARKS),2)
------
1 83.71
2 75.57
3 64.86
4 64.14
5 74.29
7)Select all courses where passing marks are more than 30% of average maximum marks.
Ans. select coursename from course where pass_marks>(select avg(max_marks)*30/100 from course);
Output:
COURSENAME
------
FOP
FOP.Prac
MATHS
COMP ORG
DBMSI
SQL & PL/SQL
ERFM
7 rows selected.
8)Select the course where Second and third characters are ‘AT’
Ans. select coursename from course where coursename like '_AT%';
Output:
COURSENAME
------
MATHS
9)Display details of students born in 1975 or 1976.
Ans. select *from student where to_char(birthdate,'yyyy') in('1975','1976');
Output:
ROLLNO NAME CLASS BIRTHDATE
------
3 Dipesh Patel FYMCA 24-MAR-76
5 Bhavin Jilvaani FYMCA 25-DEC-75
Question – 3
1)HOSTEL TABLE:
a)Create Hostel Table with fields hno, hname, haddr, tot_capacity, warden:
create table hostel
(
hno varchar2(4) primary key constraint chk_hno check(hno like 'h%'),
hname varchar2(30) not null,
haddr varchar2(30),
tot_capacity number(3) constraint chk_tot_cap check(tot_capacity>0),
warden varchar2(30)
);
b)Insert Records into Hostel Table:
insert into hostel values
(
'&hno','&hname','&haddr',&tot_capacity,'&warder'
);
c)Viewing Records Entered in the Hostel Table:
Select * from hostel;
HNO HNAME HADDR TOT_CAPACITY WARDEN
------
h001 Chanakya chhani jakatnaka, vadodara 11 Anil
h002 Milan V.V.Nagar 4 Arvindbhai
h003 Sardar Rajkot 325 Rajubhai
2)ROOM TABLE:
a)Create Room Table with fields hno, rno, rtype, location, no_of_student, status:
create table room
(
hno varchar2(4),
rno varchar2(4) constraint chk_rno check(rno like 'r%'),
rtype varchar2(20) not null,
location varchar2(30),
no_of_student number(3) constraint chk_no_of_student check(no_of_student>=0),
status varchar2(1) constraint chk_status check(status in('v','o')),
primary key(hno,rno),
foreign key(hno) references hostel(hno)
)
b)Insert Recored into Room Table:
insert into room values
(
'&hno','&rno','&rtype','&location',&no_of_student,'&status'
)
c)Viewing Records Entered it the Room Table:
Select * form room;
HNO RNO RTYPE LOCATION NO_OF_STUDENT S
------
h001 r001 d left 2 v
h001 r002 d left 2 o
h001 r003 t right 3 o
h001 r004 f right 4 v
h002 r001 s left 1 o
h002 r002 d left 2 v
h002 r003 t right 3 o
h002 r004 f right 4 o
h003 r001 s left 1 o
h003 r002 d left 2 o
h003 r003 t right 3 v
h003 r004 s right 1 o
3)CHARGES TABLE:
a)Create Charges Table with fields hno, rtype, charges:
create table charges
(
hno varchar2(4),
rtype varchar2(20),
charges number(8,2),
primary key(hno,rtype),
foreign key(hno) references hostel(hno)
);
b)Insert Table Records into Charges Table:
insert into charges values
(
'&hno','&rtype',&charges
);
c)Viewing Records Entered it the Charges Table:
Select * from charges;
HNO RTYPE CHARGES
------
h001 s 10000
h001 d 15000
h001 t 20000
h001 f 25000
h002 s 11000
h002 d 16000
h002 t 21000
h002 f 26000
h003 s 12000
h003 d 17000
h003 t 22000
h003 f 27000
11rows selected.
4)STUD TABLE:
a)Create Stud Table with fields sid, sname, saddr, faculty, dept, class, hno, rno:
create table stud
(
sid varchar2(4) primary key constraint chk_sid check(sid like 's%'),
sname varchar2(30) not null,
saddr varchar2(30),
faculty varchar2(30),
dept varchar2(20),
class varchar2(20),
hno varchar2(4),
rno varchar2(4),
foreign key(hno,rno) references room(hno,rno)
);
b)Insert Records into Stud Table:
insert into stud values
(
'&sid','&sname','&saddr','&faculty',
'&dept','&class','&hno','&rno'
);
c)Viewing Records Entered in the Stud Table:
Select * from stud;
SID SNAME SADDR FACULTY DEPT CLASS HNO RNO
------
s001 Pritesh Vasad Science Computer Science fycs h001 r001
s002 Prakash Baroda Commerce Medical fyfarm h001 r002
s013 Dilipkumarkumar baroda Medical Medical fyfarm h001 r002
s003 Suketu v.v.nagar Engineer Mechanical fybemech h001 r003
s004 Jaydeep anand Science Computer Science fycs h001 r004
s005 Ripal baroda Commerce MCA fymca h002 r001
s006 Sandeep baroda Science MCA fymca h002 r002
s007 Vikky baroda Computer Computer Science sycs h002 r003
s008 Samir baroda Science Medial syfarm h002 r004
s009 Prashant anand Science Computer Science tycs h003 r001
s010 Urvish baroda Commerce MCA fymca h003 r002
s011 Amit baroda Science Mechanical sybemech h003 r003
s012 Neel Khambhoraj Engineering Medical tyfarm h003 r004
12rows selected.
5)FEES TABLE:
a)Create Fees Table with fields sid, fdate, famount:
create table fees
(
sid varchar2(4),
fdate date,
famount number(8,2) constraint chk_famt check(famount>0),
primary key(sid,fdate),
foreign key(sid) references stud(sid)
);
b)Insert Records into Fees Table:
insert into fees values
(
'&sid','&fdate',&famount
);
c)Viewing Records Entered it the Fees Table:
Select * from fees;
SID FDATE FAMOUNT
------
s001 01-DEC-08 10000
s002 05-DEC-08 15000
s003 10-DEC-08 20000
s004 18-DEC-09 25000
s005 10-NOV-03 22000
s006 18-FEB-09 16000
s007 03-JUN-09 21000
s008 08-JUN-09 26000
s009 18-AUG-09 12000
s010 22-NOV-03 34000
s011 15-OCT-07 22000
s012 17-APR-05 27000
12 rows selected.
Select Queries:
1)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-seated.
Ans. alter table room add constraint chk_rtype check(rtype in('s','d','t','f'));
Output:
Table altered
2)Display the total number of rooms that are presently vacant.
Ans. select count(*) "Total Rooms are vacant" from room where status like 'v';
Output:
Total Rooms are vacant
------
4
3)Display number of students of each faculty and department wise staying in each hostel.
Ans. select faculty,dept,hno,count(*) from stud group by faculty,dept,hno;
Output:
FACULTY DEPT HNO COUNT(*)
------
Medical Medical h001 1
Science MCA h002 1
Science Medial h002 1
Science Mechanical h003 1
Science Computer Science h001 2
Science Computer Science h003 1
Commerce MCA h002 1
Commerce MCA h003 1
Commerce Medical h001 1
Computer Computer Science h002 1
Engineer Mechanical h001 1
Engineering Medical h003 1
12 rows selected.
4)Display hostels, which have at least one single-seated room.