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
);
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
11 rows 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
12 rows 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.
Ans. select distinct hname from hostel h,room r where h.hno=r.hno and rtype='s';
Output:
HNAME
---------
Milan
Sardar
5) Display the warden name and hostel address of students of Computer Science department.
Ans. select warden,haddr from hostel h,stud s where s.hno=h.hno and dept='Computer Science';
Output:
WARDEN HADDR
------------------------------ ------------------------------
Anil chhani jakatnaka, vadodara
Anil chhani jakatnaka, vadodara
Arvindbhai V.V.Nagar
Rajubhai Rajkot
6) Display those hostel details where single seated or double-seated rooms are vacant.
Ans. select h.hno,hname,haddr,tot_capacity,warden from hostel h,room r where h.hno=r.hno and rtype in('s','d') and status like 'v';
Output:
HNO HNAME HADDR TOT_CAPACITY WARDEN
---- ------------------------------ ------------------------------ ------------ -----------------
h001 Chanakya chhani jakatnaka, vadodara 11 Anil
h002 Milan V.V.Nagar 4 Arvindbhai
7) Display details of hostels occupied by medical students.
Ans. select hname from hostel h,stud s where h.hno=s.hno and dept='Medical';
Output:
HNAME
------------
Chanakya
Chanakya
Sardar
8) Display hostels, which are totally occupied to its fullest capacity.
Ans. select hname from hostel where (hno,tot_capacity) in(select hno,count(*) from stud group by hno);
Output:
HNAME
----------
Milan
9) List details about students who are staying in the double-seated rooms of Chanakya Hostel.
Ans. select *from stud where hno in(select hno from room where rtype like 'd' and hno in(select hno from hostel where hname like 'Chanakya'));
Output:
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 Dilipkumar 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
10) Display the total number of students staying in each room type of each hostel.
Ans. select room.hno,rtype,sum(no_of_student) from hostel,room where hostel.hno=room.hno group by room.hno,rtype;
Output:
HNO RTYPE SUM(NO_OF_STUDENT)
---- -------------------- ------------------
h001 d 4
h001 f 4
h001 t 3
h002 d 2
h002 f 4
h002 s 1
h002 t 3
h003 d 2
h003 s 2
h003 t 3
10 rows selected.
11) Display details about students who have paid fees in the month of Nov. 2003.
Ans. select *from stud where sid in(select sid from fees where to_char(fdate,'Mon yyyy.') like 'Nov 2003.');
Output:
SID SNAME SADDR FACULTY DEPT CLASS HNO RNO
---- ------------------------------ ------------------------------ ------------------------------ --
s005 Ripal baroda Commerce MCA fymca h002 r001
s010 Urvish baroda Commerce MCA fymca h003 r002
12) For those hostels where total capacity is more than 300, display details of students staying in Science faculty.
Ans. select *from stud where faculty like 'Science' and hno in(select hno from hostel where tot_capacity>300);
Output:
SID SNAME SADDR FACULTY DEPT CLASS HNO RNO
---- ------------------------------ ------------------------------ ------------------------------ --
s009 Prashant anand Science Computer Science tycs h003 r001
s011 Amit baroda Science Mechanical sybemech h003 r003
13) Display hostel details where there are atleast 10 vacant rooms.
Ans. select *from hostel where hno in(select hno from room where status like 'v' group by hno having count(*)>=10);
Output:
HNO HNAME HADDR TOT_CAPACITY WARDEN
---- ------------------------------ ------------------------------ ------------ ------------
h003 Sardar Rajkot 325 Rajubhai
14) Display details of students who have still not paid fees.
Ans. select *from stud where sid not in(select sid from fees)
Output:
SID SNAME SADDR FACULTY DEPT CLASS HNO RNO
---- ------------------------------ ------------------------------ ------------------------------ --
s013 Dilipkumarkumar baroda Medical Medical fyfarm h001 r002
15) Display those hostels where single-seated room is the costliest.
Ans. select hname from hostel where hno in(select hno from charges where rtype like 's' and charges in(select max(charges) from charges where rtype like 's'))
Output:
HNAME
------------
Sardar
Question – 8
1) APPLICANT TABLE:
a) Create Applicant Table with fields aid, aname, addr, abirth_dt:
create table applicant
(
aid varchar2(5) primary key,
aname varchar2(30) not null,
addr varchar2(30),
abirth_dt date
)
b) Insert Record into Applicant Table:
insert into applicant values
(
'&aid','&aname','&addr',to_date('&abirth_dt','dd-mm-yy')
)
c) Viewing Records Entered in the Applicant Table:
Select * from applicant;
AID ANAME ADDR ABIRTH_DT
----- ------------------------------ ------------------------------ ---------
A0001 Jaydev baroda 18-SEP-85
A0002 Pritesh Vasad 18-MAY-87
A0003 Prakash Baroda 12-OCT-05
A0004 Jaydeep anand 04-APR-88
A0005 Suketu v.v.nagar 12-JUL-87
A0006 Himanshu junagadh 08-AUG-87
A0007 Nirav junagadh 12-FEB-03
A0008 Pragnesh surat 04-SEP-85
A0009 Yogesh nadiyad 12-AUG-85
A0010 Urvish baroda 04-AUG-86
A1123 Ripal baroda 12-FEB-03
11 rows selected.
2) ENTRANCE_TEST TABLE:
a) Create Entrance_Test Table with fields etid, etname, max_score, cut_score:
create table entrance_test
(
etid varchar2(4) primary key constraint chk_etid check(etid like 'e%'),
etname varchar2(30) not null,
max_score number(3) constraint chk_max_score check(max_score>0),
cut_score number(3),
constraint chk_cut_score check(cut_score>0 and cut_score
);
b) Insert Records into Entrance_Test Table:
insert into entrance_test values
(
'&etid','&etname',&max_score,&cut_score
);
c) Viewing Records Entered in the Entrance_Test Table:
Select * from entrance_test;
ETID ETNAME MAX_SCORE CUT_SCORE
---- ------------------------------ ---------- ----------
et01 ORACLE FUNDAMENTALS 400 160
et02 GCET 600 240
et03 MAT 500 200
et04 CAT 300 120
et05 SNAP 500 200
3) ETEST_CENTER TABLE:
a) Create Etest_Center Table with fields etcid, location, incharge, capacity:
create table etest_center
(
etcid varchar2(4) primary key constraint chk_etcid check(etcid like 'e%'),
location varchar2(30) not null,
incharge varchar2(30),
capacity number(3) constraint chk_capacity check(capacity>0)
);
b) Insert Records into Etest_center Table:
insert into etest_center values
(
'&etcid','&location','&incharge',&capacity
);
c) Viewing Records Entered in the Etest_center Table:
Select * from etest_center;
ETCI LOCATION INCHARGE CAPACITY
---- ------------------------------ ------------------------------ ----------
ec01 Mumbai Urvish 100
ec02 Chennai Milan 5
ec03 Baroda Chirayu 200
ec04 Anand Nishit 250
ec05 Ahmedabad Raju 300
4) ETEST_DETAILS TABLE:
a) Create Etest_Details Table with fields aid, etid, etcid, etest_dt, score:
create table etest_details
(
aid varchar2(4),
etid varchar2(4),
etcid varchar2(4),
etest_dt date,
score number(3),
primary key(aid,etid,etcid),
foreign key(aid) references applicant(aid),
foreign key(etid) references entrance_test(etid),
foreign key(etcid) references etest_center(etcid)
);
b) Insert Table:
insert into etest_details values
(
'&aid','&etid','&etcid','&etest_dt',&score
);
c) Viewing Records Entered in the Etest_Details Table:
Select * from etest_details;
AID ETID ETCI ETEST_DT SCORE
----- ---- ---- --------- ----------
A0001 et01 ec01 12-FEB-09 240
A0001 et01 ec02 12-OCT-05 300
A0001 et02 ec03 14-JAN-05 187
A0001 et03 ec04 13-OCT-05 170
A0001 et04 ec01 28-MAR-04 197
A0001 et05 ec03 05-OCT-04 166
A0002 et01 ec02 12-OCT-05 300
A0003 et01 ec02 12-OCT-05 174
A0004 et03 ec04 05-MAY-05 45
A0006 et04 ec02 12-OCT-05 98
A0007 et05 ec03 12-DEC-89 69
A0008 et01 ec03 12-APR-04 255
A0009 et02 ec02 12-OCT-05 74
A0009 et03 ec04 08-AUG-07 155
A1123 et04 ec01 02-FEB-06 177
A0007 et01 ec02 12-FEB-03 199
13 rows selected.
Select Queries:
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’
Ans. update applicant set aid='A' || aid;
Output: 16 rows updated.
2) Display test center details where no test were conducted.
Ans. select *from etest_center where etcid not in(select etcid from etest_details);
Output:
ETCI LOCATION INCHARGE CAPACITY
---- ------------------------------ ------------------------------ ----------
ec05 Ahmedabad Raju 300
3) Display details about applicants who have the same score as that of Jaydev in ‘ORACLE FUNDAMENTALS’.
Ans. 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'
)
)
and aid not in
(
select aid from applicant where aname='Jaydev'
)
)
/
Output:
AID ANAME ADDR ABIRTH_DT
----- ------------------------------ ------------------------------ ---------
A0002 Pritesh Vasad 18-MAY-87
4) Display details of applicants who appeared for all tests.
Ans. select a.aname from applicant a where(select count(distinct ed.etid) from etest_details ed where
ed.aid=a.aid)=(select count(et.etid) from entrance_test et);
Output:
ANAME
-------------
Jaydev
5) Display those tests where no applicant has failed.
Ans. select etname from entrance_test where etid not in(
select distinct et.etid from entrance_test et,etest_details ed where et.etid=ed.etid and score
Output:
ETNAME
--------------------------
ORACLE FUNDAMENTALS
6) Display details of entrance test centers which had full attendance between1st OCT 05 and 15th OCT 05.
Ans. select ec.* from etest_center 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);
Output:
ETCI LOCATION INCHARGE CAPACITY
---- ------------------------------ ------------------------------ ----------
ec02 Chennai Milan 5
7) Display details of the applicants who scored more than the cut score in the tests they appeared in.
Ans. select distinct t.etid,a.* from applicant a,entrance_test t,etest_details d where d.aid=a.aid and
d.etid=t.etid and d.score>t.cut_score;
Output:
ETID AID ANAME ADDR ABIRTH_DT
---- ----- ------------------------------ ------------------------------ ---------
et01 A0001 Jaydev baroda 18-SEP-85
et01 A0002 Pritesh Vasad 18-MAY-87
et01 A0003 Prakash Baroda 12-OCT-05
et01 A0007 Nirav junagadh 12-FEB-03
et01 A0008 Pragnesh surat 04-SEP-85
et04 A0001 Jaydev baroda 18-SEP-85
et04 A1123 Ripal baroda 12-FEB-03
7 rows selected.
8) Display average and maximum score test wise if tests conducted at Mumbai.
Ans. select etid,max(score),avg(score) from etest_details group by etid;
Output:
ETID MAX(SCORE) AVG(SCORE)
---- ---------- ----------
et01 300 244.666667
et02 187 130.5
et03 170 123.333333
et04 197 157.333333
et05 166 117.5
9) Display the number of applicants who have appeared for each test, test center wise.
Ans. select etid,etcid,count(*) from etest_details group by etcid,etid
Output:
ETID ETCI COUNT(*)
---- ---- ----------
et01 ec01 1
et04 ec01 2
et01 ec02 4
et02 ec02 1
et04 ec02 1
et01 ec03 1
et02 ec03 1
et05 ec03 2
et03 ec04 3
9 rows selected.
10) Display details about test centers where no tests have been conducted.
Ans. select *from etest_center where etcid not in(select etcid from etest_details);
Output:
ETCI LOCATION INCHARGE CAPACITY
---- ------------------------------ ------------------------------ ----------
ec05 Ahmedabad Raju 300
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 centers.
Ans. select t.*,c.* from entrance_test t,etest_center c,etest_details d
where t.etid=d.etid and c.etcid=d.etcid and d.etest_dt between to_date('2-mar-04') and to_date('23-apr-04');
Output:
ETID ETNAME MAX_SCORE CUT_SCORE ETCI LOCATION INCHARGE CAPACITY
---- ------------------------------ ---------- ---------- ---- ------------------------------ ------
et04 CAT 300 120 ec01 Mumbai Urvish 100
et01 ORACLE FUNDAMENTALS 400 160 ec03 Baroda Chirayu 200
12) How many applicants appeared in the ‘ORACLE FUNDAMENTALS’ test at Chennai in the month of February ?
Ans. select count(*) from applicant a,etest_center c,etest_details d,entrance_test t where
a.aid=d.aid and c.etcid=d.etcid and t.etid=d.etid and t.etname='ORACLE FUNDAMENTALS' and
c.location='Chennai' and to_char(etest_dt,'Mon')='Feb';
Output:
COUNT(*)
----------
1
13) Display details about applicants who appeared for tests in the same month as the month in which they were born.
Ans. select a.* from applicant a,etest_details d where a.aid=d.aid and
abirth_dt=etest_dt;
Output:
AID ANAME ADDR ABIRTH_DT
----- ------------------------------ ------------------------------ ---------
A0003 Prakash Baroda 12-OCT-05
A0007 Nirav junagadh 12-FEB-03
14) Display the details about APPLICANTS who have second the highest in each test, test center wise.
Ans. 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));
Output:
AID ANAME ADDR ABIRTH_DT
----- ------------------------------ ------------------------------ ---------
A0001 Jaydev baroda 18-SEP-85
A0002 Pritesh Vasad 18-MAY-87
A0006 Himanshu junagadh 08-AUG-87
A0008 Pragnesh surat 04-SEP-85
A0009 Yogesh nadiyad 12-AUG-85
15) Design a read only view, which has details about applicants and the tests that he has appeared for.
Ans. create view v_815 as select aname,etname,score from applicant a,etest_details d, entrance_test t where d.aid=a.aid and d.etid=t.etid with read only;
Output : View created.
(2) DATE (DUAL TABLE)
1). Add fifteen days to current date.
Ans) :- select sysdate+15 from dual;
OUTPUT :-
SYSDATE+15
-----------------
07-OCT-09
2). Add & Subtract five months from current month.
Ans):- select add_months(sysdate,5),add_months(sysdate,-5) from dual;
OUTPUT :-
ADD_MONTH ADD_MONTH
---------------------- ---------------
22-FEB-10 22-APR-09
3). Calculate no. of months between current date and 5-07-08.
Ans):- select months_between(sysdate,'05-Jul-08') from dual;
OUTPUT :-
MONTHS_BETWEEN(SYSDATE,'05-JUL-08')
-----------------------------------------------------------------
4.5701101
4). Find the last day of a month.
Ans):- select last_day(sysdate)"Lastday" from dual;
OUTPUT :-
Lastday
-------------
30-SEP-09
5). How many days left in a current month.
Ans):- select last_day(sysdate) - sysdate from dual;
OUTPUT :-
LAST_DAY(SYSDATE)-SYSDATE
-------------------------------------------------
8
6). Find ASCII eqvivalent of letter 'Q'.
Ans):- select ascii('Q') from dual;
OUTPUT :-
ASCII('Q')
----------------
81
7). Find name of all constraint based on particular table.
Ans):- select constraint_name from user_constraints where table_name='SAILOR';
OUTPUT :-
CONSTRAINT_NAME
--------------------------------
SYS_C0020447
SYS_C0020448
8). Difference between current date & specify date.
Ans):- select to_date(sysdate) - to_date('15-sep-2008') from dual;
OUTPUT :-
TO_DATE(SYSDATE)-TO_DATE('15-SEP-2008')
--------------------------------------------------------------------
372
9). Find username & userid of current login.
Ans):- select user,uid from dual;
OUTPUT :-
USER UID
------------------------------ ----------
MCA0804 219
10). Find the occurence of 'or' in the string 'coorporate floor', using the instr.
Ans):- select instr('corporate floor','or') from dual;
OUTPUT :-
INSTR('CORPORATEFLOOR','OR')
-------------------------------------------------------
2
QUESTION:-9
1) CREATE TABLE SAILOR1 WITH FIELDS SID, SNAME, RATING, & AGE.
CREATING A TABLE
SAILOR1
create table sailor1
(sid number(3) primary key,
snm varchar2(20),
rating number(3),
age number(3) check(age > 0))
INSERTING RECORDS INTO TABLE
insert into sailor1
values(&sid,'&snm',&rating,&age);
VIEWING RECORDS ENTERED IN THE TABLE
select * from sailor1;
SID SNA RATING AGE
---- --- ------------------ ----------
22 d 7 45
29 b 1 33
31 1 8 55
32 a 8 25
58 r 10 35
64 h 7 35
71 z 10 16
74 h 9 35
85 a 3 25
95 b 3 63
2) CREATE TABLE BOATS1 WITH FIELDS BID, BNAME & COLOR.
CREATING A TABLE
BOATS1
create table boat
(bid number(3) primary key,
bnm varchar2(20),
color varchar2(10))
INSERTING RECORDS INTO TABLE
insert into boat
values(&bid,'&bnm','&color');
VIEWING RECORDS ENTERED IN THE TABLE
select * from boats1;
BID BNM COLOR
--- ----- -------
101 i1 blue
102 i1 red
103 c1 green
104 m1 red
3) CREATE TABLE RESERVE1 WITH FIELDS BID, BID & DAY DATE.
CREATING A TABLE
RESERVE1
create table reserve
(sid number(3) references sailor(sid),
bid number(3) references boat(bid),
day date,
primary key(sid,bid))
INSERTING RECORDS INTO TABLE
insert into reserve
values(&sid,&bid,'&day');
VIEWING RECORDS ENTERED IN THE TABLE
select * from reserve1;
SID BID DAY
---- --- ---------
22 101 10-OCT-07
22 102 10-OCT-07
22 104 10-AUG-07
31 104 11-DEC-07
64 101 09-MAY-07
64 102 09-AUG-09
SELECT QUERISE:-
1) FIND THE NAME OF SAILOR WHO HAVE RESERVED BOAT NO 103.
Select sname from sailor1 where sid in(select sid from reserve1 where bid in(select bid from boats1 where bid=103));
Sname
------
D
I
H
2) FIND THE SID OF SAILOR WHO HAVE RESERVED RED BOAT.
Select sid from sailor1 where sid in(select sid from reserve1 where bid in(select bid from boat s1 where color=’red’));
Sid
-------
22
31
64
OR
Select distinct (sid) from reserve1 r, boats1 b, where r.bid=b.bid and b.color=’red’;
Sid
-------
22
31
64
3) FIND THE SAILOR NAME WHO HAVE RESERVED RED COLOR BOAT.
Select distinct (sname) from sailor1 s, boats1 b, reserve1 r, where s.bid=r.bid and r.bid=b.bid and b.color=’red’;
SNA
---
d
1
h
4) FIND THE NAME OF SAILOE WHO HAVE RESERVE THE RED OR GREEN BOAT.
Select distinct (sname) from sailor1 s, reserve1 r, boats1 b, where s.sid= r.sid and b. bid=r.bid and (b.color=’red’ or b.color=’green’)
Sid sname rating age
------ ----------- ---------- -----------
31 1 8 55
32 a 8 25
58 r 10 35
71 z 10 16
74 h 9 35
5) FIND THE NAME OF SAILOR WHO HAVE NOT RESERVE THE RED BOAT.
Select sname from sailor1 where sid not in (select sid from reserve1 where bid in (select bid from boat1 where color=’red’));
SNA
---
b
a
r
z
h
a
b
I like the article on the topic due to this reason it is seen that other readers are showing their interest by commenting on it.
ReplyDeleteComputer courses in vadodara