=> ALL GTU QUERIES IN SEM 2
BY: H!mmat Dobar!ya (E-MAIL: hmt.dobariya@gmail.com)
Question 1
Customer Item schema queries which fall in all the categories
mentioned above.
CUST(Custno, cname, state, phone)
ITEM(itemno, Itemname, Itemprice, Qty_hand)
INVOICE(Invno, invDate, Custno)
INVITEM(Invno, Itemno, Qty)
Create four table along with necessary constraints( PK,FK,notnull, Unique
and Check constraints)
CUST
create table cust
(custno varchar2(6)
constraint pk_cust primary key,
cname varchar2(20) not
null,
state varchar2(15),
phone number(10));
ITEM
create table item
(itemno varchar2(6)constraint
pk_ino primary key,
itemname varchar2(20)
not null,
itemprice number(8,2)
constraint chk_price check(itemprice>0),
qty_hand number(5)
constraint chk_qty check(qty_hand>0),
constraint chk_item
check(itemno like 'i%'));
INVOICE
create table invoice
(invno number(6)
constraint pk_invno primary key,
invdate date,
custno varchar2(6)
constraint fk_cust references cust(custno));
INVITEM
create table invitem
(invno number(6)
constraint fk_invno references invoice(invno),
itemno varchar2(6)
constraint fk_itemno references item,
qty number(6));
1. Write a Insert script for insertion of rows with substitution
variables.
CUST :
insert into cust
values('c00001','pankaj','Gujarat',9999999999);
insert into cust
values('c00002','sanju','Maharashtra',8888888888);
insert into cust
values('c00003','dheru','Madras',7777777777);
insert into cust
values('c00004','jigee','Punjab',6666666666);
ITEM :
insert into item
values('i00001','Mouse',150,10);
insert into item
values('i00002','Keyboard',250,20);
insert into item
values('i00003','Monitor',3500,5);
insert into item
values('i00004','Speaker',450,10);
insert into item
values('i00005','RAM',800,5);
INVOICE :
insert into invoice
values(1001,'04-Jan-09','c00002');
insert into invoice
values(1002,'05-May-09','c00001');
insert into invoice
values(1003,'04-Apr-09','c00004');
insert into invoice
values(1004,'28-Jun-09','c00002');
insert into invoice
values(1005,'31-Aug-09','c00001');
INVITEM :
insert into invitem
values(1003,'i00005',2);
insert into invitem
values(1001,'i00004',3);
insert into invitem
values(1005,'i00001',5);
insert into invitem
values(1002,'i00005',2);
insert into invitem
values(1002,'i00002',2);
insert into invitem
values(1003,'i00001',2);
insert into invitem
values(1004,'i00004',4);
insert into invitem
values(1002,'i00001',1);
insert into invitem
values(1001,'i00002',3);
Add a column to the
Item table, which will allow us to store Item color field.
alter table item
add(itemcolor varchar2(15));
Write SELECT statement
for the given queries.
1. Display Item name,
Price in sentence form using concatenation
select 'Itemname:' || itemname || ' And Itemprice:' || itemprice "Item-Price" from item;
select 'Itemname:' || itemname || ' And Itemprice:' || itemprice "Item-Price" from item;
2. Find total value of
each item based on quantity on hand
select sum(qty_hand) "Total" from item;
select sum(qty_hand) "Total" from item;
3. Find customers who
are from state of Gujarat.
select * from cust where state='Gujarat' ;
select * from cust where state='Gujarat' ;
4. Display items with
unit price of at least Rs. 100
select * from ITEM where itemprice>=100;
select * from ITEM where itemprice>=100;
5. List items whose
range lies between Rs. 200 and Rs. 500
select * from ITEM where itemprice BETWEEN 200 and 500;
select * from ITEM where itemprice BETWEEN 200 and 500;
6. Which customers are
from lalbaug area of Ahmedabad, Baroda and Patan.
select * from cust where area='lalbaug' and city IN ('Ahmedabad','Baroda','Patan')
select * from cust where area='lalbaug' and city IN ('Ahmedabad','Baroda','Patan')
7. Find all customers
whose name start with Letter ‘P’.
select * from cust where cname LIKE 'p%';
select * from cust where cname LIKE 'p%';
8. Find name of items
with ‘W’ in their name.
select * from item where itemname LIKE '%a%';
select * from item where itemname LIKE '%a%';
9. Sort all customers
alphabetically
select * from cust order by cname;
select * from cust order by cname;
10. Sort all items in
descending order by their prices.
select * from item order by itemprice DESC;
select * from item order by itemprice DESC;
11. Display all
customers from M.P alphabetically
select * from cust where state='Madras' order by cname;
select * from cust where state='Madras' order by cname;
12. Display invoices
dates in ‘September 05, 2007’ format.
select invno, custno, to_char(invdate,'Month DD,YYYY') "Date" from invoice;
select invno, custno, to_char(invdate,'Month DD,YYYY') "Date" from invoice;
13. Find total,
average, highest and lowest unit price.
select sum(itemprice) "TOTAL_PRICE", MAX(itemprice) "MAX_PRICE", AVG(itemprice) "AVG_PRICE", MIN(itemprice) "MINIMUM_PRICE" from item;
select sum(itemprice) "TOTAL_PRICE", MAX(itemprice) "MAX_PRICE", AVG(itemprice) "AVG_PRICE", MIN(itemprice) "MINIMUM_PRICE" from item;
14. Count number of
items ordered in each invoice
select invno "INVOICE NO", COUNT(*) “TOTAL” from invitem GROUP BY invno;
select invno "INVOICE NO", COUNT(*) “TOTAL” from invitem GROUP BY invno;
15. Find invoices in
which three or more items are ordered.
select invno "INVOICE NO", sum(qty) "ORDERED" from invitem group by invno having sum(qty)>=3;
select invno "INVOICE NO", sum(qty) "ORDERED" from invitem group by invno having sum(qty)>=3;
16. Find all possible
combination of customers and items ( use Cartesian product)
select cname, itemname from cust, invoice, invitem, item item where cust.custno=invoice.custno and invoice.invno=invitem.invno and invitem.itemno=item.itemno;
select cname, itemname from cust, invoice, invitem, item item where cust.custno=invoice.custno and invoice.invno=invitem.invno and invitem.itemno=item.itemno;
17. Display all item
quantity and item price for invoices ( natural join)
select * from invoice NATURAL JOIN invitem group by invitem.invno;
select * from invoice NATURAL JOIN invitem group by invitem.invno;
18. Find total price
amount for each invoice.
select invno, count(*) "QUANTITY", sum(qty*itemprice) “TOTAL” from invitem,item where item.itemno=invitem.itemno group by invitem.invno;
select invno, count(*) "QUANTITY", sum(qty*itemprice) “TOTAL” from invitem,item where item.itemno=invitem.itemno group by invitem.invno;
19. Use outer join to
display items ordered as well as not ordered so far.
select * from item,invitem where item.itemno=invitem.itemno(+);
select * from item,invitem where item.itemno=invitem.itemno(+);
20. Find invoices with
‘Gear’ in their item name.
select invno from item, invitem where invitem.itemno=item.itemno and item.itemn ame LIKE '%o%';
select invno from item, invitem where invitem.itemno=item.itemno and item.itemn ame LIKE '%o%';
21. Display name of
items ordered in invoice number 1001
select itemname, invno from item, invitem where item.itemno= invitem.itemno and invno=1001;
select itemname, invno from item, invitem where item.itemno= invitem.itemno and invno=1001;
22. Find the items that
are cheaper than ‘Bullet’.
select itemno, itemname from item where itemprice<(select itemprice from item where itemname='Speaker');
select itemno, itemname from item where itemprice<(select itemprice from item where itemname='Speaker');
23. Create a table (
namely guj_cust) for all Gujarat customer based on existing customer table
create table cust_adi as select * from cust where state=’Ahmedabad';
create table cust_adi as select * from cust where state=’Ahmedabad';
24. Copy all M.P
customers to the table with Gujarat customers
insert into cust_adi (select * from cust where state='Punjab')
insert into cust_adi (select * from cust where state='Punjab')
25. Rename Guj_cust
table to MP_cust table.
rename cust_adi to cust_punjab
rename cust_adi to cust_punjab
26. Find the customers who are not in Gujarat or
M.P
Select * from cust where state NOT IN('Ahmedabad','Punjab');
Select * from cust where state NOT IN('Ahmedabad','Punjab');
27. Delete rows from
customer table that are also in MP_cust table
delete from cust where exists(select custno from cust_punjab where cust_punjab.custno =cust.custno );
delete from cust where exists(select custno from cust_punjab where cust_punjab.custno =cust.custno );
28. Find the items with
top three prices
select * from (
select * from (
select itemname,
row_number()
over (order by itemprice) rank
from item
)
where rank<=3;
29. Find two items
with lowest quantity on hand
select * from
select * from
(
select itemname,
qty_hand,
row_number()
over (order by qty_hand) rank
from item
)
where rank between 1 and 2;
30. Create a simple
view with item names and item price only
create view view1 as select itemname , itemprice from item
create view view1 as select itemname , itemprice from item
31. Create a sequence
that can be used to enter new items into item table
create sequence seq_item increment by 1 start with 6;
create sequence seq_item increment by 1 start with 6;
32. Add a new item
into item table with sequence just created.
insert into item values(seq_item.nextval,'Modem',450,2);
insert into item values(seq_item.nextval,'Modem',450,2);
33. Create a index
file to speed up a search based on customer name
create index idx_item on item(itemno);
create index idx_item on item(itemno);
34. Lock customer Mr.
Shah record to update the state and phone no.
select state,phone from cust where cname='pankaj' for update;
select state,phone from cust where cname='pankaj' for update;
35. Give everybody
select and insert rights on your item table
grant select,insert on item to ALL;
grant select,insert on item to ALL;
36. Revoke the insert
option on item table from user ‘Roshi’
revoke insert on item from rahul;
revoke insert on item from rahul;
Question 2
STUDENT(rollno,name,class,birthdate)
COURSE(courseno, coursename, max_marks, pass_marks)
SC(rollno,courseno,marks)
CREATE TABLE
Student:
create table student
(
rollno number(10) primary key,
name varchar2(10) NOT NULL,
class varchar2(10) NOT NULL,
bod date
);
Course:
create table course
(
courseno number(10) primary key,
coursename varchar(10) NOT NULL,
max_marks number(4) NOT NULL,
pass_marks number(4) NOT NULL
);
Sc:
create table sc
(
rollno number(10) references student(rollno),
courseno number(10) references course(courseno),
marks varchar(10) NOT NULL
);
INSERT RECORDS
-- INSERT INTO STUDENT
insert into student
values(0001,'pankaj','fymca','27-jan-1989');
insert into student
values(0002,'rahul','fymca','15-aug-1988');
insert into student
values(0003,'priyesh','tymca','30-apr-1990');
insert into student
values(0004,'ravinder','fymca','27-jan-1989');
insert into student
values(0005,'dheru','symca','15-feb-1987');
insert into student values(0006,'amit','fymca','19-jun-1989');
insert into student
values(0007,'deepak','symca','25-dec-1989');
-- INSERT INTO COURSE
insert into course
values(01,'dm',100,50);
insert into course
values(02,'fop',100,50);
insert into course
values(03,'dbms',100,50);
insert into course
values(04,'erpfm',100,50);
insert into course
values(05,'faco',100,50);
-- INSERT INTO SC
insert into sc
values(0007,04,75);
insert into sc
values(0004,03,45);
insert into sc
values(0005,03,70);
insert into sc
values(0004,02,90);
insert into sc
values(0001,01,75);
insert into sc
values(0004,05,80);
insert into sc
values(0006,04,55);
insert into sc
values(0001,03,68);
insert into sc
values(0004,01,36);
insert into sc
values(0003,03,59);
insert into sc
values(0001,05,48);
insert into sc
values(0002,04,99);
1. Add constraint that marks entered are between 0 to 100 only.
alter table sc add constraint abc check(marks between 0 and 100);
2. While creating
COURSE table, primary key constraint was forgotten. Add the primary key
now.
alter table course add primary key(courseno);
alter table course add primary key(courseno);
3. Display details of
student where course is ‘Data Base Management System’.
select sc.rollno,name
from sc,course,student where course.coursename='dbms' and
sc.courseno=course.courseno and sc.rollno=student.rollno;
4. Select student
names who have scored more than 70% in Computer Networks and have not failed in
any subject.
select sc.rollno, coursename, student.name from course,sc, student where marks>70 and coursename='erpfm' and sc.rollno in( select rollno from sc where rollno not in(select rollno from sc, course where marks<course.pass_marks )) and student.rollno=sc.rollno;
select sc.rollno, coursename, student.name from course,sc, student where marks>70 and coursename='erpfm' and sc.rollno in( select rollno from sc where rollno not in(select rollno from sc, course where marks<course.pass_marks )) and student.rollno=sc.rollno;
5. Select names and
class of students whose names begin with ‘A’ or ‘B’.
select name,class from student where name LIKE 'r%' OR name LIKE 'p%';
select name,class from student where name LIKE 'r%' OR name LIKE 'p%';
6. Display average marks
obtained by each student.
select a.rollno, avg(marks) from sc a, student b where a.rollno=b.rollno group by a.rollno;
select a.rollno, avg(marks) from sc a, student b where a.rollno=b.rollno group by a.rollno;
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/10 0 from course);
select coursename from course where pass_marks>(select avg(max_marks)*30/10 0 from course);
8. Select the course
where second and third characters are ‘AT’.
select * from course where coursename lIKE '_bm%';
9. Display details of
students born in 1975 or 1976.
select * from student where to_char(bod,'yyyy') BETWEEN 1988 and 1990;
select * from student where to_char(bod,'yyyy') BETWEEN 1988 and 1990;
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.
CREATE TABLE
Hostel
create table hostel
(
h#
number(10) primary key,
hname
varchar2(20) NOT NULL,
haddr
varchar2(20) NOT NULL,
total_capacity
number(10),
warden
varchar2(20)
);
Room
create table room
(
h#
number(10) references hostel(h#),
r#
number(10) not null,
rtype
varchar2(10) not null,
location
varchar2(10) not null,
no_of_students
number(5),
status
varchar2(20),
primary
key(h#,r#)
);
Charges
create table charges
(
h#
number(10) references hostel(h#),
rtype
varchar2(10) not null,
charges
number(10),
primary
key(h#,rtype)
);
Student1
create table student1
(
sid
number(10) primary key,
sname
varchar2(10),
saddr
varchar2(10),
faculty
varchar2(10),
dept
varchar2(10),
class
varchar2(10),
h#
number(10) references hostel(h#),
r#
number(10)
);
Fees
create table fees
(
sid
number(10),
fdate
date,
famount
number(10),
primary
key(sid,fdate)
);
INSERT RECORDS
-- INSERTING INTO
HOSTEL
insert into hostel
values (1,'sardar patel','baroda',500,'vidhi');
insert into hostel values(2,'lala','baroda',100,'vidhi');
insert into hostel
values(3,'jain chhatra','jamnagar',500,'kamini');
insert into hostel
values(4,'krishana','bombay',500,'pooja');
-- INSERTING INTO ROOM
insert into room
values(1,1,'s','haj',500,'vacant');
insert into room
values(2,1,'f','haj',500,'vacant');
insert into room
values(2,3,'s','haj',500,'vacant');
insert into room
values(2,2,'t','haj',500,'vacant');
-- INSERTING INTO
CHARGES
insert into charges
values(1,'d',5000);
insert into charges
values(2,'s',5000);
insert into charges
values(3,'s',5000);
-- INSERTING INTO
STUDENT
insert into student1
values(1,'ami','jam','antani sir',1,'mca1',1,2);
insert into student1
values(2,'pankaj','jam','bipin sir',2,'mca1',2,2);
insert into student1
values(3,'jigi','jam','seema mam',3,'mca1',3,2);
-- INSERTING INTO FEES
insert into fees
values(1,'02-dec-2010',500);
insert into fees
values(2,'12-dec-2010',500);
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-seater.
alter table room add constraint t1 check (rtype in('s','d','t','f'));
2. Display the total
number of rooms that are presently vacant.
select count(*) from room where status='vacant';
select count(*) from room where status='vacant';
3. Display number of
students of each faculty and department wise staying in each hostel.
select no_of_students,r.h#,s.faculty,s.dept from room r, student1 s where r.h#=s.h#;
select no_of_students,r.h#,s.faculty,s.dept from room r, student1 s where r.h#=s.h#;
4. Display hostels,
which have at least one single-seated room.
select rtype,b.h#,a.hname from room b, hostel a where rtype='s' and a.h#=b.h#;
select rtype,b.h#,a.hname from room b, hostel a where rtype='s' and a.h#=b.h#;
5. Display the warden
name and hostel address of students of Computer Science department.
select hname,warden,sname,saddr from student1 a, hostel b where dept=2 and a.h#=b.h#;
select hname,warden,sname,saddr from student1 a, hostel b where dept=2 and a.h#=b.h#;
6. Display those
hostel details where single seated or double-seated rooms are vacant.
select a.h#,r#,b.hname from room a,hostel b where rtype='s' and status='vacant' and a.h#=b.h#;
select a.h#,r#,b.hname from room a,hostel b where rtype='s' and status='vacant' and a.h#=b.h#;
7. Display details of
hostels occupied by medical students.
select a.h#,a.hname,a.warden from hostel a,student1 b where b.dept=3 and a.h#=b.h#;
select a.h#,a.hname,a.warden from hostel a,student1 b where b.dept=3 and a.h#=b.h#;
8. Display hostels, which are
totally occupied to its fullest capacity.
select * from hostel where (h#,total_capacity) in (select h#,count(sid) from student1 group by h#);
select * from hostel where (h#,total_capacity) in (select h#,count(sid) from student1 group by h#);
9. List details about
students who are staying in the double-seated rooms of Chanakya Hostel.
select c.r#,c.sname,c.saddr from student1 c where (c.r#,c.h#) in ( select b.r#, a.h# from hostel a, room b where a.hname='lala' and b.rtype='d' and a.h#=b.h# );
select c.r#,c.sname,c.saddr from student1 c where (c.r#,c.h#) in ( select b.r#, a.h# from hostel a, room b where a.hname='lala' and b.rtype='d' and a.h#=b.h# );
10. Display the total
number of students staying in each room type of each hostel.
select s.h#,s.r#,count(sid) from student1 s, hostel h where h.h#=s.h#
group by s.h#,s.r#;
select s.h#,s.r#,count(sid) from student1 s, hostel h where h.h#=s.h#
group by s.h#,s.r#;
11. Display details
about students who have paid fees in the month of Nov. 2003.
select s.sid,s.sname,s.saddr,s.dept from student1 s, fees f where s.sid=f.sid and to_char(fdate,'mon-yyyy')='nov-2003';
select s.sid,s.sname,s.saddr,s.dept from student1 s, fees f where s.sid=f.sid and to_char(fdate,'mon-yyyy')='nov-2003';
12. For those hostels
where total capacity is more than 300, display details of
students studying in Science faculty.
select sid,sname,saddr from student1
select sid,sname,saddr from student1
where h# in
(
select h# from hostel where
total_capacity>300
)
and dept=3;
13. Display hostel
details where there are at least 10 vacant rooms.
select h#,hname,warden from hostel where h# in (select h# from room group by h# having count(status)>=3);
select h#,hname,warden from hostel where h# in (select h# from room group by h# having count(status)>=3);
14. Display details of
students who have still not paid fees.
select sid,sname,saddr from student1 where sid not in (select sid from fees);
select sid,sname,saddr from student1 where sid not in (select sid from fees);
15. Display those
hostels where single-seated room is the costliest.
select c.h#,h.hname,h.haddr from charges c,hostel h where charges=(select max(charges) from charges where rtype='s') and h.h#=c.h#;
select c.h#,h.hname,h.haddr from charges c,hostel h where charges=(select max(charges) from charges where rtype='s') and h.h#=c.h#;
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)
Value of screen_id
must start with letters ‘S’.
Attribute location can
be any one of ‘ FF’, ‘SF’, or ‘TF’.
Date_of_arrival must
be less than date_of_closure.
CREATE TABLE
Screen
create table screen
(
screen_id varchar2(3) primary key,
location varchar2(3) not null check(location
in('FF','SF','TF')),
seating_cap number(3) not null check(seating_cap>0),
constraint sid check(screen_id like ('S%')),
);
Movie
Create table movie
(
movie_id varchar2(3)
primary key,
movie_name varchar2(20) unique,
date_of_release date
not null
);
Current1
create table current1
(
screen_id varchar2(3) references SCREEN(screen_id),
movie_id varchar2(6) references MOVIE(movie_id),
date_of_arrival date not null,
date_of_closure date not null,
constraint dtc check(date_of_arrival<date_of_closure)
);
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 a.movie_name from movie a,current1 b where (date_of_closure - date_of_arrival) =(select max(date_of_closure - date_of_arrival) from current1) and a.movie_id=b.movie_id;
2. Get the average
duration of a movie on screen number ‘S4’.
select screen_id, avg(date_of_closure-date_of_arrival) avg_duration from current1 where screen_id='S3' group by screen_id;
select screen_id, avg(date_of_closure-date_of_arrival) avg_duration from current1 where screen_id='S3' group by screen_id;
3. Get the details of
movie that closed on date 24-november-2004.
select a.movie_id, a.movie_name,a.date_of_release from current1 b, movie a where date_of_closure='27-Oct-10' and a.movie_id=b.movie_id;
select a.movie_id, a.movie_name,a.date_of_release from current1 b, movie a where date_of_closure='27-Oct-10' and a.movie_id=b.movie_id;
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 * from movie where TO_CHAR(date_of_release,'ww')=3 and movie_name=’hulchul’;
select * from movie where TO_CHAR(date_of_release,'ww')=3 and movie_name=’hulchul’;
5. Get the full outer
join of the relations screen and current.
select * from screen a, current1 b where a.screen_id(+)=b.screen_id(+);
select * from screen a, current1 b where a.screen_id(+)=b.screen_id(+);
Question 5
DISTRIBUTOR (DNO, DNAME, DADDRESS, DPHONE)
ITEM (ITEMNO, ITEMNAME, COLOUR, WEIGHT)
DIST_ITEM (DNO, ITEMNO, QTY)
CREATE TABLE
Distributor
create table
distributor
(
dno number(10) primary key,
dname varchar2(20),
daddress varchar2(30),
dphone number(10)
);
Item
create table
item
(
itemno number(10) primary key,
itemname varchar2(10),
color varchar2(10),
weight number(10)
);
Dist_Item
create table dist_item
(
dno number(10) references distributor(dno),
itemno number(10) references item(itemno),
qty number(10),
primary key(dno,itemno)
);
INSERT
-- INSERT INTO
DESTRIBUTOR
insert into
distributor values(001,'geeta','mumbai',9995556663,'pankaj');
insert into
distributor values(002,'jigee','pune',9995556663,'monica');
insert into
distributor values(003,'parul','banglore',9995556663,'shweta');
insert into
distributor values(004,'purohit','vijaywada',9956157815,'tyagi');
insert into
distributor values(006,'pranaohsiva','madinagar',9545158844,'sharad');
insert into
distributor values(005,'lohnghat','chandigarh',8545264588,'rahul');
insert into
distributor values(007,'sheela','ambala',8881556478,'praveen');
insert into
distributor values(008,'sumit','mehasana','','amar');
-- INSERT INTO ITEM
insert into item
values(01,'keyboar','black',70);
insert into item
values(02,'mouse','white',20);
insert into item
values(03,'monitor-T','white',500);
insert into item
values(05,'printer','yellow',750);
insert into item
values(04,'monitor-C','green',1500);
insert into item
values(06,'cabinate','black',200);
insert into item
values(07,'Mboard','white',75);
insert into item
values(11,'speaker','red',10);
insert into item
values(09,'scanner','yellow',50);
insert into item
values(08,'ups','yellow',1500);
insert into item
values(10,'mboard-j','red',200);
-- INSERT INTO
DIST_ITEM
insert into dist_item
values(1,5,20);
insert into dist_item
values(2,2,80);
insert into dist_item
values(1,3,59);
insert into dist_item
values(3,2,90);
insert into dist_item
values(3,5,08);
insert into dist_item
values(3,4,75);
insert into dist_item
values(1,2,100);
insert into dist_item values(2,2,24);
1. Add a column
CONTACT_PERSON to the DISTRIBUTOR table with the not null constraint.
alter table distributor add contact_person varchar2(10) not null;
alter table distributor add contact_person varchar2(10) not null;
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.
create view pune_dist as select di.dno,di.itemno,di.qty from distributor d,dist_item di where d.daddress='pune' and d.dno=di.dno;
create view pune_dist as select di.dno,di.itemno,di.qty from distributor d,dist_item di where d.daddress='pune' and d.dno=di.dno;
3. Display details of
all those items that have never been supplied.
select * from item where itemno not in(select itemno from dist_item group by itemno);
select * from item where itemno not in(select itemno from dist_item group by itemno);
4. Delete all those
items that have been supplied only once.
delete from dist_item where itemno in(select itemno from dist_item group by itemno having count(itemno)=1)
delete from dist_item where itemno in(select itemno from dist_item group by itemno having count(itemno)=1)
5. List the names of
distributors who have an ‘A’ and also a ‘B’ somewhere in their names.
select dno, dname from distributor where dname like '%a%e%' or dname like '%e%a%';
select dno, dname from distributor where dname like '%a%e%' or dname like '%e%a%';
6. Count the number of
items having the same colour but not having weight between 20 and 100.
select count(color),color from item where weight <20 or weight>100 group by color;
select count(color),color from item where weight <20 or weight>100 group by color;
7. Display all those
distributors who have supplied more than 1000 parts of the same type.
select dno,itemno,sum(qty) from dist_item group by dno,itemno having sum(qty)>=100;
select dno,itemno,sum(qty) from dist_item group by dno,itemno having sum(qty)>=100;
8. Display the average
weight of items of same colour provided at least three items have that
colour.
select color, avg(weight) "AVG WEIGHT", count(color) "TOTAL NO" from item group by color having count(color)>=3;
select color, avg(weight) "AVG WEIGHT", count(color) "TOTAL NO" from item group by color having count(color)>=3;
9. Display the position
where a distributor name has an ‘OH’ in its spelling somewhere after the
fourth character.
select dname,instr(dname,'oh') “POSITION” from distributor where instr(dname,'oh',5)<>0;
select dname,instr(dname,'oh') “POSITION” from distributor where instr(dname,'oh',5)<>0;
10. Count the number of
distributors who have a phone connection and are supplying item number
‘I100’.
select count(d.dno) from distributor d, dist_item di where d.dno=di.dno and di.itemno=5 and d.dphone is not NULL
select count(d.dno) from distributor d, dist_item di where d.dno=di.dno and di.itemno=5 and d.dphone is not NULL
11. Create a view on the
tables in such a way that the view contains the distributor name, item
name and the quantity supplied.
select * from view1;
select * from view1;
12. List the name,
address and phone number of distributors who have the same three digits in
their number as ‘Mr. Talkative’.
select dname,dphone,daddress from distributor where substr(dphone,1,3) in
(select substr(dphone,1,3) from distributor where dname='jigee') and dname!='jigee'
select dname,dphone,daddress from distributor where substr(dphone,1,3) in
(select substr(dphone,1,3) from distributor where dname='jigee') and dname!='jigee'
13. List all
distributor names who supply either item I1 or I7 and the
quantity supplied is more than 100.
select d.dno , d.dname from distributor d where dno in(select dno from dist_item where (itemno=2 or itemno=3) and qty>=50);
select d.dno , d.dname from distributor d where dno in(select dno from dist_item where (itemno=2 or itemno=3) and qty>=50);
14. Display the data of
the top three heaviest ITEMS.
select * from
select * from
(
select itemname,
row_number() over (order by weight desc) rank from item
)where
rank<=3;
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)
CREATE TABLE
Worker:
create table worker
(
worker_id number(10) primary key,
name varchar2(10),
wage_per_hour number(10),
specialised_in varchar2(20),
manager_id number(10)
);
Job:
create table job
(
job_id number(10) primary key,
type_of_job varchar2(10),
status varchar2(10)
);
Assigned:
create table
job_assigned
(
worker_id number(10) references worker(worker_id),
job_id number(10) references job(job_id),
starting_date date,
number_of_days number,
primary key(worker_id,job_id)
);
INSERT RECORDS
-- Insert Into Worker
insert into worker
values(001,'pankaj',1000,'computer','');
insert into worker
values(002,'jigee',500,'medical',005);
insert into worker
values(003,'ami',100,'sports',002);
insert into worker
values(004,'geeta',600,'civil',006);
insert into worker
values(005,'sumit',750,'engg',001);
insert into worker
values(006,'parul',500,'autocad',003);
insert into worker
values(008,'monica',500,'computer',009);
insert into worker
values(009,'rahul',800,'computer',006);
insert into worker
values(007,'sanju',1100,'civil',008);
-- Insert Into
Job
insert into job
values(05,'computer','incomplete');
insert into job
values(02,'banking','half');
insert into job
values(03,'engg','one third');
insert into job
values(01,'electrical','incomplete');
insert into job
values(04,'autocad','incomplete');
insert into job
values(07,'civil','half');
insert into job
values(06,'medical','one fourth');
-- Insert Into
Job_Assigned
insert into job_assigned
values(002,06,'02-jan-2000',350);
insert into
job_assigned values(001,06,'15-jan-2010',50);
insert into
job_assigned values(003,01,'22-mar-2003',150);
insert into
job_assigned values(001,05,'01-jan-2000',05);
insert into
job_assigned values(002,04,'02-jan-2008',20);
insert into
job_assigned values(004,05,'28-dec-2000',10);
insert into
job_assigned values(002,05,'30-apr-2000',89);
insert into
job_assigned values(003,05,'30-apr-2000',89);
1. Display the date on
which each worker is going to end his presently assigned job.
select worker_id,starting_date+number_of_days "ENDING DATE" from job_assigned;\
select worker_id,starting_date+number_of_days "ENDING DATE" from job_assigned;\
2. Display how many days
remain for each worker to finish his job.
select worker_id, round(starting_date+number_of_days - sysdate) "REMAINING DAYS" from job_assigned where starting_date+number_of_days>sysdate;
select worker_id, round(starting_date+number_of_days - sysdate) "REMAINING DAYS" from job_assigned where starting_date+number_of_days>sysdate;
3. Display the
STARTING_DATE in the following format – ‘The fifth day of the month of
October, 2004’.
select 'The ' || to_char(starting_date,'ddth')||' day of the month of '||to_char(starting_date,'Month, yyyy') "DATE" from job_assigned;
select 'The ' || to_char(starting_date,'ddth')||' day of the month of '||to_char(starting_date,'Month, yyyy') "DATE" from job_assigned;
4. Change the status to
‘Complete’ for all those jobs, which started in year 2000.
update job set status='complete' where job_id in
update job set status='complete' where job_id in
(
select job_id from job_assigned where
to_char(starting_date,'yyyy')='2000' group by job_id
)
5. Display job details
of all those jobs where at least 25 workers are working.
select job_id,count(worker_id) "TOTAL WORKER" from job_assigned group by job_id having count(worker_id)>=3;
select job_id,count(worker_id) "TOTAL WORKER" from job_assigned group by job_id having count(worker_id)>=3;
6. Display all those
jobs that are already completed.
select ja.job_id,j.type_of_job,ja.worker_id from job_assigned ja,job j where (ja.starting_date+ja.number_of_days)<sysdate and j.job_id=ja.job_id;
select ja.job_id,j.type_of_job,ja.worker_id from job_assigned ja,job j where (ja.starting_date+ja.number_of_days)<sysdate and j.job_id=ja.job_id;
7. Find all the jobs,
which will begin within the next two weeks.
select ja.job_id,j.type_of_job,ja.worker_id from job_assigned ja,job j where ja.starting_date>sysdate and ja.starting_date+ja.number_of_days<sysdate+14;
select ja.job_id,j.type_of_job,ja.worker_id from job_assigned ja,job j where ja.starting_date>sysdate and ja.starting_date+ja.number_of_days<sysdate+14;
8. List all workers who
have their wage per hour ten times greater than the wage of
their managers.
select a.name "worker name" from worker a,worker b where a.manager_id=b.worker_id and a.wage_per_hour>b.wage_per_hour*2;
select a.name "worker name" from worker a,worker b where a.manager_id=b.worker_id and a.wage_per_hour>b.wage_per_hour*2;
9. List the names of
workers who have been assigned the job of molding.
select w.worker_id,w.name from worker w,job j,job_assigned ja where w.worker_id=ja.worker_id and j.job_id=ja.job_id and j.type_of_job='computer';
select w.worker_id,w.name from worker w,job j,job_assigned ja where w.worker_id=ja.worker_id and j.job_id=ja.job_id and j.type_of_job='computer';
10. What is the total
number of days allocated for packaging the goods for all the workers together.
select job_id, avg(number_of_days) from job_assigned group by job_id;
select job_id, avg(number_of_days) from job_assigned group by job_id;
11. Which workers
receive higher than average wage per hour.
select * from worker where wage_per_hour>(select avg(wage_per_hour) from worker);
select * from worker where wage_per_hour>(select avg(wage_per_hour) from worker);
12. Display details of
workers who are working on more than one job.
select worker_id, count(job_id) from job_assigned group by worker_id having count(job_id)>1;
select worker_id, count(job_id) from job_assigned group by worker_id having count(job_id)>1;
13. Which workers having
specialization in polishing start their job in December?
select w.worker_id , name from worker w, job_assigned ja where w.specialise d_in='computer' and to_char(ja.starting_date,'mon')='dec';
select w.worker_id , name from worker w, job_assigned ja where w.specialise d_in='computer' and to_char(ja.starting_date,'mon')='dec';
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 wage_per_hour>(select MAX(wage_per_hour) from worker) or specialised_in=(select specialised_in from worker where name='pankaj');
select * from worker where wage_per_hour>(select MAX(wage_per_hour) from worker) or specialised_in=(select specialised_in from worker where name='pankaj');
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)
CREATE TABLE
Publisher:
create table publisher
(
publ_id varchar2(10) primary key check (publ_id LIKE 'p%'),
publ_name varchar2(10),
cantact_person varchar2(10),
contact_addr varchar2(10),
contact_phone number(10)
);
Category:
create table category
(
cat_id varchar2(10) primary key check(cat_id LIKE 'c%'),
cat_details varchar2(10),
max_books number(4),
duration number(4)
);
Book_master:
create table
book_master
(
book_id number primary key,
bname varchar2(20),
isbn_no number(10),
total_copies number(5),
publ_id varchar2(10) references publisher(publ_id)
);
Member
create table member
(
member_id number(5) primary key,
mname varchar2(10),
cat_id varchar2(10) references category(cat_id),
mem_ship_dt date
);
Issue
create table issue
(
issue_id number(10) primary key,
member_id number(5) references member(member_id),
book_id number references book_master(book_id),
issu_ret varchar2(4),
issue_ret_dt date
);
INSERT RECORDS
-- Insert Into
Publisher
insert into publisher
values('p01','pankaj','ashish','bglore',9992225554);
insert into publisher
values('p02','geeta','shweta','rajkot',9922448891);
insert into publisher
values('p03','ami','parul','pune',8884565789);
insert into publisher
values('p04','mehul','monica','delhi',7896541289);
-- Insert Into
Category
insert into category
values('c01','science',120,2004);
insert into category
values('c02','computer',110,2002);
insert into category
values('c03','novel',40,2008);
insert into category
values('c04','physics',90,2003);
insert into category
values('c05','halloween',77,2007);
insert into category
values('c06','civics',50,2010);
-- Insert Into
Book_Master
insert into
book_master values(001,'HP',160054,100,'p01');
insert into
book_master values(002,'godaan',160159,150,'p02');
insert into
book_master values(003,'RDPD',160357,50,'p03');
insert into
book_master values(004,'S1',160789,88,'p04');
insert into book_master
values(005,'H1',160152,95,'p02');
insert into
book_master values(006,'GK',160789,200,'p04');
insert into
book_master values(007,'FAQ',160152,65,'p01');
-- Insert Into Member
insert into member
values(01,'rahul','c03','01-jan-2010','jamnagar');
insert into member
values(02,'preeti','c04','21-jun-2010','rajkot');
insert into member
values(03,'amar','c04','15-apr-2010','delhi');
insert into member
values(04,'ashish','c05','30-aug-2010','pune');
insert into member
values(05,'abhishek','c03','23-oct-2010','madras');
insert into member
values(06,'arun','c06','20-sep-2010','bgolore');
-- Insert Into Issue
insert into issue
values(01,02,002,'I','22-jun-2010');
insert into issue
values(02,03,001,'I','16-apr-2010');
insert into issue
values(07,04,004,'R','14-oct-2010');
insert into issue
values(03,02,005,'R','05-jul-2010');
insert into issue
values(04,04,002,'I','25-nov-2010');
insert into issue
values(05,02,006,'I','02-aug-2010');
insert into issue
values(06,06,007,'I','30-sep-2010');
insert into issue values(07,05,007,'I','25-nov-2010');
insert into issue
values(08,04,007,'I','15-dec-2010');
insert into issue
values(09,03,007,'I','30-dec-2010');
In the above tables duration is in years and it stores the membership duration for that
category.
1. 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.
alter table issue add check(issu_ret in ('I','R'));
alter table issue add check(issu_ret in ('I','R'));
2. Add a column to the
MEMBER table, which will allow us to store the address of the member.
alter table member add (addr varchar2(10));
alter table member add (addr varchar2(10));
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 1=2;
create table library_users as select * from member where 1=2;
4. Give details about
members who have issued books, which contain ‘DATA’ somewhere in their
titles.
select i.member_id,m.mname from issue i, book_master b, member m
where i.book_id=b.book_id and bname LIKE '%P%' and i.issu_ret='I' andm.member_id=i.member_id;
select i.member_id,m.mname from issue i, book_master b, member m
where i.book_id=b.book_id and bname LIKE '%P%' and i.issu_ret='I' andm.member_id=i.member_id;
5. Display the books
that have been issued at the most three times in the year 2003.
select book_id , count(*) from issue where issu_ret='I' and to_char(issue_ret_dt,'yyyy')= 2010 group by book_id having count(*)<=2 order by book_id;
select book_id , count(*) from issue where issu_ret='I' and to_char(issue_ret_dt,'yyyy')= 2010 group by book_id having count(*)<=2 order by book_id;
6. Display which books
of publisher PHI that are issued right now.
select p.publ_id,b.bname from issue i,publisher p,book_master b
where
p.publ_id=b.publ_id and b.book_id=i.book_id and i.issu_ret='I' and
p.publ_name='pankaj' and to_char(issue_ret_dt,’dd-mon-yyyy’)=to_char(sysdate,’dd-mon-yyyy’);
7. Display details about
books whose all copies are issued.
select * from book_master where (book_id,total_copies) in (select book_id,count(*) from issue group by book_id);
select * from book_master where (book_id,total_copies) in (select book_id,count(*) from issue 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 i.book_id,i.member_id "MEMBER ID",m.mname "MEMBER NAME", b.bname,b.isbn_no,b.total_copies,b.publ_id
select i.book_id,i.member_id "MEMBER ID",m.mname "MEMBER NAME", b.bname,b.isbn_no,b.total_copies,b.publ_id
from issue i,member
m,book_master b where i.issue_ret_dt> '01-sep-2010' and
i.issue_ret_dt<'01-dec-2010' and i.book_id= b.book_id and
i.member_id=m.member_id and i.issu_ret='I';
9. Display all staff
members who have issued at least two books.
select mname from member where member_id in(select i.member_id from issue I group by i.member_id having count(i.book_id)>=3);
select mname from member where member_id in(select i.member_id from issue I group by i.member_id having count(i.book_id)>=3);
10. Display details
about those publishers whose more than 100 books are available in the library.
select publ_id,publ_name from publisher where publ_id in ( select b.publ_id from book_master b,publisher p where p.publ_id=b.publ_id group by b.publ_id having count(b.book_id)>=2);
select publ_id,publ_name from publisher where publ_id in ( select b.publ_id from book_master b,publisher p where p.publ_id=b.publ_id group by b.publ_id having count(b.book_id)>=2);
11. Delete all those
members whose membership has expired.
delete from category c where duration < to_char(to_date(sysdate),'yyyy');
delete from category c where duration < to_char(to_date(sysdate),'yyyy');
12. How many members
registered in the last three months ?
select count(*) "TOTAL NO" from member where to_char(mem_ship_dt,'mon') in ('dec','oct','nov');
select count(*) "TOTAL NO" from member where to_char(mem_ship_dt,'mon') in ('dec','oct','nov');
13. Display since how
many months has each staff member registered.
select member_id, round((sysdate-mem_ship_dt)/30) "TOTAL MONTH" from member;
select member_id, round((sysdate-mem_ship_dt)/30) "TOTAL MONTH" from member;
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)
CREATE TABLE
Application:
create table applicant
(
aid varchar2(5) primary key,
aname varchar2(20),
addr varchar2(20),
abirth_dt date
);
Entrance_Test
create table
entrance_test
(
etid number(10) primary key,
etname varchar2(20),
max_score number(10),
cut_score number(10)
);
Etest_Centre
create table
etest_centre
(
etcid number(10) primary key,
location varchar2(15),
incharge varchar2(10),
capacity number(10)
);
Etest_Details
create table
etest_details
(
aid varchar2(5) references applicant(aid),
etid number(10) references entrance_test(etid),
etcid number(10) references etest_centre(etcid),
etest_dt date check(to_char(etest_dt,’d’)<>1),
score number(10),
primary key(aid,etid,etcid)
);
INSERT RECORDS
-- Insert Into
Application
insert into applicant
values('A001','pankaj','bglore','25-jan-1988');
insert into applicant
values('A002','sumit','pune','07-jun-1989');
insert into applicant
values('A003','jigee','kolkata','12-apr-1990');
insert into applicant
values('A004','monica','madras','02-jul-1988');
insert into applicant
values('A005','parul','rajkot','01-aug-1989');
insert into applicant
values('A006','geeta','mumbai','03-dec1988');
insert into applicant
values('A007','sweta','surat','02-mar-1991');
insert into applicant
values('A008','ami','anand','11-oct-1991');
-- Insert Into
Entrace_Test
insert into
entrance_test values(01,'GCET',400,150);
insert into
entrance_test values(02,'AIEEE',200,100);
insert into
entrance_test values(03,'NIIT',200,130);
insert into entrance_test
values(04,'PMT',500,200);
insert into
entrance_test values(05,'NDA',100,50);
insert into
entrance_test values(06,'PABT',150,75);
-- Insert Into
Etest_Centre
insert into
etest_centre values(01,'JAMNAGAR','SWETAmam',120);
insert into etest_centre
values(02,'PUNE','supriyamam',60);
insert into
etest_centre values(03,'BGLORE','nilesh',120);
insert into
etest_centre values(04,'DELHI','vikram',100);
insert into
etest_centre values(05,'SURAT','aartimam',50);
insert into
etest_centre values(06,'MEHSANA','vipulsir',200);
insert into
etest_centre values(07,'RAJKOT','tyagisir',150);
-- Insert Into
Etest_Details
insert into
etest_details values('A001',02,03,'25-dec-2010',150);
insert into
etest_details values('A002',03,06,'04-mar-2011',120);
insert into etest_details
values('A002',02,02,'29-jan-2010',120);
insert into
etest_details values('A004',04,04,'05-dec-2010',114);
insert into
etest_details values('A002',05,07,'02-jan-2011',99);
insert into
etest_details values('A006',04,04,'21-apr-2011',300);
insert into
etest_details values('A006',06,02,'20-feb-2011',76);
(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’.
alter table applicant add check (aid like 'A%');
alter table applicant add check (aid like 'A%');
2. Display test center
details where no tests were conducted.
select * from etest_centre where etcid not in (select etcid from etest_details group by etcid);
select * from etest_centre where etcid not in (select etcid from etest_details group by etcid);
3. Display details about
applicants who have the same score as that of Jaydev in ‘ORACLE
FUNDAMENTALS’.
select applicant.aid,applicant.aname from applicant, entrance_test,etest_detailswhere applicant.aid=etest_details.aid and score =
(
select applicant.aid,applicant.aname from applicant, entrance_test,etest_detailswhere applicant.aid=etest_details.aid and score =
(
select score
from etest_details ed,entrance_test
et,applicant a
where et.etname='AIEEE' and et.etid=ed.etid
and a.aname='pankaj' and a.aid=ed.aid
) and entrance_test.etname='AIEEE'
) and entrance_test.etname='AIEEE'
and entrance_test.etid=etest_details.etid;
4. Display details of
applicants who appeared for all tests.
select aid from etest_details where etid = all (select etid from etest_details);
select aid from etest_details where etid = all (select etid from etest_details);
5. Display those tests
where no applicant has failed.
select * from entrance_test where etid not in (select ed.etid from etest_details ed,entrance_test et
where ed.etid=et.etid and score<cut_score);
select * from entrance_test where etid not in (select ed.etid from etest_details ed,entrance_test et
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 etcid from etest_centre where (etcid,capacity) in (
select etcid,count(*) from etest_details where etest_dt>'1-sep-2010' and etest_dt<'30-dec-2010' group by etcid );
select etcid from etest_centre where (etcid,capacity) in (
select etcid,count(*) from etest_details where etest_dt>'1-sep-2010' and etest_dt<'30-dec-2010' group by etcid );
7. Display details of
the applicants who scored more than the cut score in the teststhey appeared in.
select ed.etid,cut_score,score from etest_details ed, entrance_test et where ed.etid=et.etid and score>cut_score;
select ed.etid,cut_score,score from etest_details ed, entrance_test et where ed.etid=et.etid and score>cut_score;
8. Display average and
maximum score test wise of tests conducted at Mumbai.
select max(ed.score) "MAX", avg(ed.score) "AVG" from etest_details ed, etest_centre et where et.location='DELHI' and et.etcid=ed.etcid group by ed.etid;
select max(ed.score) "MAX", avg(ed.score) "AVG" from etest_details ed, etest_centre et where et.location='DELHI' and et.etcid=ed.etcid group by ed.etid;
9. Display the number of
applicants who have appeared for each test, test center wise
select ed.etid,ed.etcid,count(aid) from etest_details ed group by ed.etid,ed.etcid order by etid;
select ed.etid,ed.etcid,count(aid) from etest_details ed group by ed.etid,ed.etcid order by etid;
10. Display details
about test centers where no tests have been conducted.
select * from etest_centre where etcid not in (select etcid from etest_details);
select * from etest_centre where etcid not in (select 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 ed.etid, et.etname, et.max_score, et.cut_score, ec.etcid, ec.location, ec.incharge, ec.capacity from etest_details ed, entrance_test et, etest_centre ec
where etest_dt>'01-dec-2010' and etest_dt<'30-dec-2010' and et.etid=ed.etid and ec.etcid=ed.etcid;
select ed.etid, et.etname, et.max_score, et.cut_score, ec.etcid, ec.location, ec.incharge, ec.capacity from etest_details ed, entrance_test et, etest_centre ec
where etest_dt>'01-dec-2010' and etest_dt<'30-dec-2010' and et.etid=ed.etid and ec.etcid=ed.etcid;
12. How many applicants
appeared in the ‘ORACLE FUNDAMENTALS’ test at Chennai in the month of
February ?
select count(*) "NO" from etest_details ed, etest_centre ec, entrance_test et where to_char(etest_dt,'mon')='dec'
select count(*) "NO" from etest_details ed, etest_centre ec, entrance_test et where to_char(etest_dt,'mon')='dec'
and
ec.location='DELHI' and ec.etcid=ed.etcid and et.etid=ed.etid and etname='PMT'
group by ec.etcid;
13. Display details
about applicants who appeared for tests in the same month as the month in
which they were born.
select aid,aname from applicant where aid in(
select a.aid from applicant a,etest_details ed where to_char(a.abirth_dt,'mon')=to_char(ed.etest_dt,'mon'));
select aid,aname from applicant where aid in(
select a.aid from applicant a,etest_details ed where to_char(a.abirth_dt,'mon')=to_char(ed.etest_dt,'mon'));
14. Display the details
about APPLICANTS who have scored the highest in each test,test centre wise.
select ed.etid,ed.etcid,max(score) from etest_details ed group by ed.etid,ed.etcid order by etid;
select ed.etid,ed.etcid,max(score) from etest_details ed group by ed.etid,ed.etcid order by etid;
15. Design a read only
view, which has details about applicants and the tests that he has
appeared for.
create view view1 as select a.aid,aname,et.etid,et.etname from applicant a, etest_details ed,entrance_test et where a.aid=ed.aid and et.etid=ed.etid;
create view view1 as select a.aid,aname,et.etid,et.etname from applicant a, etest_details ed,entrance_test et where a.aid=ed.aid and et.etid=ed.etid;
Comments
TAMARA QUE ME AKHA BHAGAVAN MAHAVIRE NE ALINK MOKALI API CHE BAKI PL SQL NI QUERIES MATE WAIT KARE CHE
ReplyDelete