1)
Create the table CUST with the fields (custno, cname, state, phone):
create
table cust
(custno
varchar2(5) primary key,
cname varchar2(20) unique,
state varchar2(10),
phone number(10),
constraint st check(state in
('Gujarat','Rajasthan','MP','UP','Maharastra')));
Insert
the records into the table CUST:
insert
into cust values (&custno,'&cname','&state',&phone);
View
all the records of table CUST
select
* from cust;
Output:
CUSTNO CNAME STATE PHONE
------------ ---------- ---------------- ----------------
C0001 Ashish Gujarat 9999988888
C0002 Dhaval Maharashtra
9898989898
C0003 Dipen MP 9895989598
C0004 Hardik UP 9998989899
C0005 Jigar Rajasthan 9898999598
2) Create the table ITEM with the fields
(itemno, itemname, itemprice, Qty_hand):
create
table item
(itemno
varchar2(5) primary key,
itemname varchar2(20) not null,
itemprice number(8,2),
Qty_hand number(3),
constraint
itp check(itemprice>0));
Insert
the records into the table ITEM:
insert into item values
(&itemno,'&itemname',&itemprice,&qty_hand);
View
all the records of table ITEM
select
* from item;
Output:
ITEMNO ITEMNAME ITEMPRICE QTY_HAND
-----------
------------------
--------------------
--------------------
I0001 Pendrive 450 400
I0002 Floppy 25 50
I0003 Cds 10 110
I0004 Mouse 400 75
I0005 Keyboard 700 125
3)
Create the table INVOICE with the fields (invno, invDate, custno):
create
table invoice
(invno
number(4) primary key,
invdate date,
custno varchar2(5) references cust);
Output:
Insert
the records into the table INVOICE:
insert
into invoice values (&INVNO,'&INVDATE', &CUSTNO);
View
all the records of table INVOICE
select
* from invoice;
Output:
INVNO INVDATE CUSTNO
---------- -------------- -------------
IN001 05-SEP-07 C0003
IN002 06-SEP-01 C0005
IN003 07-SEP-01 C0001
IN004 18-SEP-01 C0004
IN005 21-SEP-01 C0002
4)
Create the table INVITEM with the fields (invno, itemno, Qty):
create
table invitem
(invno
varchar2(5) references invoice,
Itemno varchar2(5) references item,
qty number(3),);
Insert
the records into the table INVITEM:
insert
into invitem values (&INVNO, &ITEMNO,&QTY);
View
all the records of table INVITEM
select
* from invitem;
Output:
INVNO ITEMNO QTY
--------
- ----------- -------
IN001 I0003 20
IN005 I0005
22
IN003 I0002
25
IN004 I0001 45
IN002 I0004 50
Queries:
3)
Add a column to the Item table, which will allows us to store Item color field.
alter
table item
add
(color varchar2(8));
View
all the records of table ITEM
select
* from item;
Output:
ITEMNO ITEMNAME ITEMPRICE QTY_HAND COLOR
-----------
----------------
----------------
----------------- ----------
I0001 Pendrive 450 400 Black
I0002 Floppy 25 50
Blue
I0003 Cds 10 110 Silver
I0004 Mouse 400 75 White
I0005 Keyboard 700 125 Silver
4)
Write SELECT statement for the given queries.
a)
Display Itemname, Price in sentence from using concatenation.
Select
itemname ||’sold at Rs.’|| itemprice from item;
Output:
ITEMNAME
||’sold at Rs.’|| ITEMPRICE
-------------------------------------------------------------
Pendrive
sold at Rs.450
Floppy
sold at Rs.25
Cds
sold at Rs.10
Mouse
sold at Rs.400
Keyboard
sold at Rs.700
b)
Find total value of each item based on quantity on hand.
select
itemname, itemprice*qty_hand "Total value of each Item" from item;
Output:
ITEMNAME
Total value of each Item
---------------- --------------------------------
Pendrive 180000
Floppy 1250
Cds 1100
Mouse 30000
Keyboard 87500
c)
Find customer who are from state of Gujarat.
Select
* from cust where state like 'Gujarat';
Output:
CUSTNO CNAME STATE PHONE
------------- ---------- ---------- -----------
C0001 Ashish Gujarat 9999988888
d)
Display items with unit price of at least Rs. 100
select
* from item where itemprice>=100;
Output:
ITEMNO ITEMNAME ITEMPRICE QTY_HAND COLOR
------------ --------------- ---------------- ----------------- ----------
I0001 Pendrive 450 400 Black
I0004 Mouse 400 75 White
I0005 Keyboard 700 125 Silver
e)
List items where range lies between Rs. 200 and Rs. 500
select
* from item where itemprice >200 and itemprice <500;
Output:
ITEMNO ITEMNAME ITEMPRICE QTY_HAND COLOR
------------ --------------- ---------------- ----------------- ----------
I0001 Pendrive 450 400 Black
I0004 Mouse 400 75 White
f) Which customers are from lalbaug area
of Ahmedabad, Baroda and Patan.
Alter
table cust
add
(city varchar2(20),
area
varchar2(20));
select
cname from cust where city in('Ahmedabad','Baroda','Surat') and area like
'Lalbaug';
no
rows selected.
g)
Find all customers whose name start with Letter ‘D’.
select
cname from cust where cname like 'D%';
Output:
CUSTNO CNAME STATE PHONE CITY
------------------------------------------------------------------------------------
C0002 Dhaval Maharashtra 9898989898 Mumbai
C0003 Dipen MP 9895989598 Bhopal
h)
Find name of Items with ‘b’ in their name.
select
* from item where itemname like '%b%' or itemname like 'b%' or itemname like
'%b';
Output:
ITEMNO ITEMNAME ITEMPRICE QTY_HAND COLOR
----------
---------------- ---------------- -----------------
----------
I0005 Keyboard 700 125 Silver
i)
Sort all customers alphabetically.
select * from cust order by cname;
Output:
CUSTNO CNAME STATE PHONE CITY
------------ ---------- ---------------- ---------------- ------
C0001 Ashish Gujarat 9999988888 Anand
C0002 Dhaval Maharashtra 9898989898 Mumbai
C0003 Dipen MP 9895989598 Bhopal
C0004 Hardik UP 9998989899 Lucknow
C0005 Jigar Rajasthan 9898999598 Jaipur
j)
Sort all Items in descending order by their prices.
select
* from item order by itemprice desc;
Output:
ITEMNO ITEMNAME ITEMPRICE QTY_HAND COLOR
------------ ---------------- ---------------- ---------------- ------------
I0005 Keyboard 700 125 Silver
I0001 Pendrive 450 400 Black
I0004 Mouse 400 75 White
I0002 Floppy 25 50 Blue
I0003 Cds 10 110 Silver
k)
Display all customers from M.P. alphabetically.
select
cname from cust where state='MP' order by cname;
Output:
CUSTNO CNAME STATE PHONE CITY
------------- ----------- ---------- ----------- -------
C0003 Dipen MP 9895989598 Bhopal
l)
Display invoices dates in ‘September 05, 2007’ format.
Select
to_char(invdate,'month dd,yyyy') from invoice;
Output:
TO_CHAR(INVDATE,'
-----------------
september
05,2007
september
06,2001
september
07,2001
september
18,2001
september
21,2001
m)
Find total, average, highest and lowest unit price.
Select
sum(itemprice),avg(itemprice),max(itemprice),min(itemprice) from item;
Output:
SUM(ITEMPRICE) AVG(ITEMPRICE) MAX(ITEMPRICE) MIN(ITEMPRICE)
----------------------------------------------------------------------------------------------------------
1585 317 700 10
n)
Count number of items ordered in each invoice.
select
count(itemno) from invitem order by itemno;
Output:
COUNT(ITEMNO)
-------------
5
o)
Find invoice which three or more items are ordered.
select distinct invno from invitem where qty>=3;
Output:
IN001
IN005
IN003
IN004
IN002
p)
Find all possible combination of customers and items (use Cartesian product)
Select
c.cname,i.itemname from cust c cross join item I;
Output:
CNAME ITEMNAME
----------- ---------------
Ashish Pendrive
Dhaval Pendrive
Dipen Pendrive
Hardik Pendrive
Jigar Pendrive
Ashish Floppy
Dhaval Floppy
Dipen Floppy
Hardik Floppy
Jigar Floppy
Ashish Cds
Dhaval Cds
Dipen Cds
Hardik Cds
Jigar Cds
Ashish Mouse
Dhaval Mouse
Dipen Mouse
Hardik Mouse
Jigar Mouse
Ashish Keyboard
Dhaval Keyboard
Dipen Keyboard
Hardik Keyboard
Jigar Keyboard
q)
Display all item quantity and item price for invoices (natural join)
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
---------- --------------- --------------
IN001 20 9000
IN002 50 1250
IN003 25 250
IN004 45 18000
IN005 22 15400
r)
Find total price amount for each invoice.
Select
invno, sum(qty_hand * itemprice) "Total Price" from item,invitem
where item.itemno=invitem.itemno group by invno;
Output:
INVNO
Total Price
-------- --------------
IN001 180000
IN002 1250
IN003 1100
IN004 30000
IN005 87500
s)
Use outer join to display items ordered as well as not ordered so far.
Select
invitem.itemno "Order", item.itemno "Not Ordered" from
invitem, item where invitem.itemno (+) =item.itemno;
Output:
Order Not Ordered
-------- -----------------
I0001 I0001
I0002 I0002
I0003 I0003
I0004 I0004
I0005 I0005
t)
Find invoices with ‘Floppy’ in their item name.
Select
invno, itemname from item, invitem where item.itemno=invitem.itemno and
itemname='Gear';
Output:
INVNO ITEMNAME
------------ ----------------
IN003
Floppy
u)
Display name of items ordered in invoice number I0003
Select
itemname from invitem,item where invitem.itemno=item.itemno and invno='I0003';
Output:
ITEMNAME
----------------
Floppy
v)
Find the items that are cheaper than ‘Bullet’.
Select
itemname from item where itemprice < (select itemprice from item where
itemname like 'Bullet');
Output:
ITEMNAME
----------------
Floppy
Cds
Mouse
w)
Create a table (namely guj_cust) for all Gujarat customer based on existing
customer table.
Create
table guj_cust as select * from cust where state like ‘Gujarat’;
Output:
Table
created.
x)
Copy all M.P. customers to the table with Gujarat Customers.
Insert
into guj_cust select * from cust where state like ‘MP’;
Output:
1
row created.
y)
Rename Guj_cust table to MP_cust table.
Rename
guj_cust to MP_cust;
Output:
Table
renamed.
z)
Find the customers who are not in Gujarat or M.P.
Select
cname from cust where state not in (‘Gujarat’,’MP’);
Output:
CNAME
-----------
Dhaval
Hardik
Jigar
aa)
Delete rows from customer table that are also in MP_cust table.
Delete
from cust where custno in (select custno from mp_cust);
no
rows selected.
bb)
Find the Items with top three prices.
Select
itemname from (select itemname from item order by itemprice desc) where
rownum<=3;
Output:
ITEMNAME
-----------------
Keyboard
Pendrive
Mouse
cc)
Find two items with lowest quantity on hand.
Select
itemname from (select itemname from item order by qty_hand) where rownum<=2;
Output:
ITEMNAME
-----------------
Floppy
Mouse
dd)
Create a simple view with item names and item price only.
Create
view vit as select itemname, itemprice from item;
Output:
View
created.
ee)
Create a sequence that can be used to enter new items into item table.
Create sequence seqc incremented by 10 start with 011
minvalue 011 maxvalue 100;
Output:
Sequence
created.
ff)
Add a new Item into Item table with sequence just created.
Insert
into item values (seqc.nextval,'Datacard', 37, 200,'white);
Output:
1
row created.
gg)
Create a Index file to speed up a search based on customer table.
Create
index idx on cust (cname);
Output:
Index
created.
hh) Lock customer Mr. Shah record to update the state and
phone no.
ii)
Give everybody select and insert right on your item table.
Grant
select, insert on item to Hardik;
jj)
Revoke the insert option on item table from user ‘Roshi’.
Revoke
insert on item Roshi;
Output:
QUESTION:- 4
1)
Create the table SCREEN with the fields (screen_id, location, seating_cap):
create
table screen
(screen_id
varchar2(3) primary key,
location varchar2(3) not null,
seating_cap number(3) not null,
constraint
sid check(screen_id like ('S%')),
constraint
loc check(location in('FF','SF','TF')),
constraint
sc check(seating_cap>0));
Insert
the records into the table SCREEN:
Insert
into screen values ('&screen_id','&location',&seating_cap);
View
all records of table SCREEN
Select
* from screen;
Output:
SCREEN_ID LOCATION
SEATING_CAP
----------------- ---------------- ---------------------
S1 SF 400
S2 TF 350
S3 TF 250
S4 SF 300
S5 TF 170
2) Create the table MOVIE with the fields (movie_id, movie_name,
date_of_release):
Create
table MOVIE
(movie_id
varchar2(3) primary key,
movie_name varchar2(20) unique,
date_of_release date not null);
Insert
the records into the table MOVIE:
Insert
into movie values('&movie_id','&movie_name','date_of_release');
View
all records of table MOVIE
Select
* from movie;
Output:
MOVIE_ID MOVIE_NAME
DATE_OF_RELEASE
-------------- -------------------- ------------------------------
M01 Star Wars III 11-SEP-09
M02 Oceans
13 10-JUL-09
M03 Armageddon 18-FEB-05
M04 Step
up 27-SEP-02
M05 Terminator-3 25-OCT-05
3)
Create the table CURRENT1 with the fields (screen_id, movie_id,
date_of_arrival, date_of_closure):
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));
Output:
Insert
the records into the table CURRENT1:
('&screen_id','&movie_id','date_of_arrival','date_of_closure');
View
all the records of table CURRENT1
Select
* from current1;
Output:
SCREEN_ID MOVIE_ID
DATE_OF_ARRIVAL DATE_OF_CLOSURE
-------------------------------------------------------------------------------------------------
S1 M01
13-JUL-09 26-AUG-09
S2 M03
25-APR-04 03-MAY-04
S3 M02
05-JAN-09 25-FEB-09
S4 M04 16-MAR-09 20-APR-09
S5 M05
03-MAY-05 09-JUL-05
Queries:
1.
Get the name of movie which has run the longest in the multiplex so far.
select movie_name from movie where movie_id
in(select movie_id from current1 where (date_of_closure - date_of_arrival) in
(select max(date_of_closure-date_of_arrival) from current1));
Output:
MOVIE_NAME
---------------------
Terminator-3
2.
Get the average duration of a movie on screen number 'S4'.
Select
avg(to_number(date_of_closure-date_of_arrival)) from current1 where
screen_id='S4';
Output:
AVG(TO_NUMBER(DATE_OF_CLOSURE-DATE_OF_ARRIVAL))
----------------------------------------------------------------------------------------
35
3.
Get the details of movie that closed on date 24-november-2004.
Select
* from movie where movie_id in (select movie_id from current1 where
date_of_closure ='24-nov-04');
Output:
no
rows selected.
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
date_of_release from movie where to_char(date_of_release,'iw')=7 and
to_char(date_of_release,'d')=6;
Output:
DATE_OF_R
---------
18-FEB-05
5.
Get the full outer join of the relations screen and current.
Select
s.*, c.* from screen s, current1 c where c.screen_id(+)=s.screen_id;
Output:
SCR LOC SEATING_CAP
SCR MOVIE_ DATE_OF_A DATE_OF_C
-------
------ ---------------------
------ ---------
-----------------
-----------------
S1 SF 400
S1
M01 13-JUL-09 26-AUG-09
S2 TF 350
S2
M03 25-APR-04 03-MAY-04
S3 TF 250
S3 M02
05-JAN-09 25-FEB-09
S4 SF 300
S4
M04 16-MAR-09 20-APR-09
S5 TF 170
S5
M05 03-MAY-05 09-JUL-05
QUESTION:- 5
1)
Create the table DISTRIBUTOR with the fields (DNO, DNAME, DADDRESS, DPHONE)
create
table distributor
(dno
varchar2(3) primary key,
dname varchar2(15) not null,
daddr varchar2(25),
dphone number(7));
Insert
the records into the table DISTRIBUTOR:
Insert
into distributor values ('&dno', '&dname',' &daddr', &dphone);
View
all the records of table DISTRIBUTOR
Select
* from distributor;
Output:
DNO DNAME DADDR DPHONE
------- --------------------- ----------------- ----------------
D01 Hardik Ode 9315462
D02 Dhaval Anand 9325135
D03 AAAAOH Baroda 9563154
D04 Mr. Talkative Vasad 9321354
D05 Dipen
Thasara 9345432
2)
Create the table ITEM1 with the fields (ITEMNO, ITEMNAME, COLOR, WEIGHT):
create
table item1
(
itemno varchar2(3) primary key,
itemname varchar2(20) not null,
colour varchar2(10),
weight number(3),
constraint
chk_wht check(weight>0));
Insert
the records into the table ITEM1:
Insert
into item1 values ('&itemno','&itemname','&colour',&weight);
View
all the records of table ITEM1
Select
* from item1;
Output:
ITEMNO ITEMNAME
COLOUR WEIGHT
------------- ---------------- ----------------- ------------
I01 Screw Black 20
I02 Bolt white 100
I03 Nut red 50
I04 Hammer green 75
I05 Washer red 110
I06 Wire Gray 37
I07 Nail Green 46
3)
Create the table DIST_ITEM with the fields (DNO, ITEMNO, QTY):
create
table DIST_ITEM
(dno varchar2(3) references DISTRIBUTOR,
itemno varchar2(3) references ITEM1,
qty number(4));
Insert
the records into the table DIST_ITEM:
Insert
into dist_item values ('&dno','&itemno',&qty);
4) View all the records of table DIST_ITEM
Select
* from dist_item;
Output:
DNO ITEMNO
QTY
-------- -------------- -----
D01 I02 130
D02 I01 500
D03 I05 420
D04 I03 320
D05 I06 160
D02 I04 190
D01 I07
462
D05 I01 256
D03 I04 315
Queries:
1. Add column CONTACT_PERSON to the
distributor table with the not null constraint.
Alter
table distributor add (contact_person varchar2(15) not null);
Output:
Table altered.
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 LONDON_DIST as select di.dno, di.itemno, di.qty from dist_item di,
dist_item d where di.dno=d.dno and upper(daddr) like '%LONDON%' with check
option;
Output:
View
created.
3.
Display detail of all those item that have never been supplied.
Select
* from item1 where itemno not in(select itemno
from dist_item)
No
rows selected.
5.
Delete all those items that have been sulpplied only once.
Delete
from item1 where itemno in(select itemno from dist_item group by itemno having
count(*)=1);
Output:
or
Delete
from item1 where itemno in(select itemno from dist_item group by itemno having
count(*)=1);
no
rows selected.
5.
List the names of distributors who have an 'A' and also a 'B' somewhere in
their names.
Select
* from distributor where dname like '%a%' and dname like '%b%';
no
rows selected.
6.
Count the number of items having the same color but not having weight between
20 and 100.
Select
colour, count(*) from item1 where weight not between 20 and 100 group by
colour;
Output:
COLOUR COUNT(*)
--------------
--------------
red 1
7.
Display all those distributors who have supplied more than 1000 parts of the
same type.
Select
* from distributor where dno in (select dno from dist_item group by dno, itemno
having sum(qty)>1000);
no
rows selected.
8.
Display the average weight of items of same colour provided at least one items
have that colour.
Select
colour, avg (weight) from item1 group by colour having count (*)>1;
Outout:
COLOUR AVG(WEIGHT)
------------- --------------------
red 80
9.
Display the position where a distributor name has an 'OH' in its spelling
somewhere after the forth character.
Select
upper (dname)||' has "OH" in its name at position'||
to_char(instr(upper(dname),'OH',4)) from distributor;
Output:
UPPER(DNAME)||'HAS"OH"INITSNAMEATPOSITION'||TO_CHAR(INSTR(UPPER(DNAME),'OH',4))
-------------------------------------------------------------------------------
HARDIK
has "OH" in its name at position0
DHAVAL
has "OH" in its name at position0
AAAAOH
has "OH" in its name at position5
MR.
TALKATIVE has "OH" in its name at position0
DIPEN
has "OH" in its name at position0
10.
Count the number of distributors who have a phone connection and are supplying
item number 'I100'.
Select
count (*) from distributor d, dist_item di where d.dphone is not null and d.dno
= di.dno and di.itemno=‘i100’;
Output:
COUNT(*)
-------------
0
11.
Create a view on the table in such a way that the view contains the distributor
name, item name and the quantity supplied.
Create
view vdnm_inm_qty as select d.dname, i.iname, di.qty from distributor d, item1
i, dist_item di where d.dno=di.dno and i.itemno=di.itemno;
View
created.
12.
List the name, address and phone number of distributors who have the same three
digits in their number as 'Mr. Talkative'.
select
dname, daddr, dphone from distributor where substr(dphone,1,3)in (select
substr(dphone,1,3) from distributor where lower(dname)='hardik');
Output:
13.
List all distributor names who supply either item I01 or I07 and the quantity
supplied is more than 100.
Select
dname from distributor d, dist_item di, item1 i where di.dno=d.dno and i.itemno=di.itemno
and di.qty>100;
14.
Display the data of the top three heaviest ITEMS.
Select
* from item1 where weight>= (select max(weight) from item1 where weight not
in (select max(weight)from item1 where weight not in (select max(weight)from
item1)) and weight <>(select max(weight) from item1));
Output:
ITE
ITEMNAME COLOUR WEIGHT
-----
--------------- ------------- -----------
I02
Bolt white 100
I04
Hammer green 75
I05
Washer red 110
QUESTION:- 6
1) Create the table WORKER with the fields (worker_id, name,
wage_per_hour, specialized_in, manager_id):
create
table worker
(worker_id
varchar2(3) primary key,
name
varchar2(15) not null,
wage_per_Hour
number(3),
specialised_in
varchar2(5),
manager_Id
varchar2(3) primary key,
constraint
ck_wage check(wage_per_Hour >=0));
Insert
the records into the table WORKER:
Insert into worker
values(‘&worker_id’,’&name’,&wage_per_hour,’&specialised_in’,’&manager_id’);
View all the records of table WORKER
Select
* from WORKER;
Output:
WOR
NAME WAGE_PER_HOUR SPECIALISED_IN MAN
-------
--------------- ---------------------------
------------------------- -------
W01
Mr.Cacophonix 50 Polishing M01
W02
Dhaval 40 Polishing M02
W03
Dipen 35 Fitting M03
W04
Hardik 30 Marketing M04
W05
Jigar 55 Fitting M05
2) Create the table JOB with the fields (job_id, type_of_job,
status):
create
table job
(
job_Id varchar2(3) primary key,
Type_of_Job varchar2(15) not null,
status varchar2(1));
Insert
the records into the table JOB:
Insert
into job values (‘&job_id’, '&type_of_job', '&status');
View all the records of table JOB
Select
* from JOB;
Output:
JOB TYPE_OF_JOB S
------
--------------------- -
J01 Packing A
J02 Editing A
J03 Molding B
J04 Accounting B
J05 Printing B
3) Create the table JOB_ASSIGNED with the fields (worker_id, job_id,
starting_date, number_of_days):
create
table JOB_ASSIGNED
(
worker_id varchar2(3) references worker,
Job_Id varchar2(3) references job,
Starting_Date date,
Number_Of_Days number(4));
Insert the records into the table JOB_ASSIGNED:
Insert
into job_assigned
Values
(‘&worker_id’, ’&job_id’, '&starting_date', &number_of_days);
View
all the records of table JOB_ASSIGNED
Select
* from job_assigned;
Output:
WOR
JOB STARTING_ NUMBER_OF_DAYS
-------
------ ----------- ----- ----------------------------
W01 J01 15-SEP-09 35
W02 J01 20-SEP-08 34
W03 J04 12-OCT-09 39
W01 J05 19-OCT-09 10
W02 J04 12-SEP-08 25
Queries:
1.
Display the date on which each worker is going to end his presently assigned
job.
Select
starting_date + number_of_days "End Date of Assigned Job" from
job_assigned where (starting_date + number_of_days)>=sysdate;
Output:
End
Date
-------------
20-NOV-09
2.
Display how many days remain for each worker to finish his job.
select
worker_id, job_id,(starting_date+ number_of_days) - Round (sysdate) "Day
Remain for Worker” from job_assigned where (starting_date + number_of_days)
>=sysdate;
Output:
WOR
JOB Day Remain for Worker
-------
--------------------------------------
W03 J04
19
3.
Display the STARTING_DATE in the following format - 'The fifth day of month of
October, 2004'.
select
to_char(starting_date,'"The" ddspth " day of month of"
month "," yyyy"') from job_assigned;
Output:
TO_CHAR(STARTING_DATE,'"THE"DDSPTH"DAYOFMONTHOF"MONTH
----------------------------------------------------------------------------------------------------
The
fifteenth day of month of september ,
2009
The
twentieth day of month of september ,
2008
The
twelfth day of month of october , 2009
The
nineteenth day of month of october , 2009
The
twelfth day of month of september , 2008
4.
Change the status to 'Complete' for all those jobs, which started in year 2008.
Update
job set status = 'complete ' where job_id in (select job_id from job_assigned
where to_char(starting_date,'yyyy')='2008');
Output:
2
rows updated.
select
* from job;
JOB
TYPE_OF_JOB S
------
-------------------- -
J01 Packing I
J02 Editing A
J03 Molding
B
J04 Accounting I
J05 Printing B
5.
Display job details of all those jobs where at least 25 workers are working.
Select
* from job where job_id in (select job_id from job_assigned group by job_id
having count (*)>=25);
no
rows selected.
6.
Display all those jobs that are already incompleted.
Select
job_id, type_of_job from job where status='I';
Output:
JOB TYPE_OF_JOB
------
---------------------
J01 Packing
J04 Accounting
7.
Find all the jobs, which begin within the next two weeks.
Select
* from job where job_id in (select job_id from job_assigned where starting_date
<= (sysdate+15));
Output:
JOB TYPE_OF_JOB S
------ ------------ -------- -
J01 Packing I
J04 Accounting I
J05 Printing
B
8.
List all workers who have their wage per hour ten times greater than the wage
of their managers.
Select
name from worker w, job j, job_assigned a where w.worker_id=a. worker_id and
j.job_id =a.job_id and j.type_of_job='Polishing';
no
rows selected
9.
List the names of workers who have been assigned the job of Packing.
Select
w.name from worker w where w.worker_id in (select worker_id from job_assigned
where job_id in (select job_id from job where type_of_job ='Packing'));
Output:
NAME
---------------
Mr.Cacophonix
Dhaval
10.
What is total number of days allocated for printing on the goods for all the
workers together.
Select
sum(number_of_days) from job_assigned where job_id in(select job_id from job
where type_of_job='Printing');
Output:
SUM(NUMBER_OF_DAYS)
-------------------------------------
10
11.
Which workers receive higher than average wage per hour.
Select
* from worker where wage_per_hour > (select avg(wage_per_hour) from worker);
Output:
WOR
NAME WAGE_PER_HOUR
SPECIALISED_IN MAN
-------
-------------------- ---------------------------
------------------------ -------
W01
Mr.Cacophonix 50 Polishing M01
W05
Jigar 55 Fitting M05
12.
Display details of workers who are working on more than one job.
Select
* from worker where worker_id in (select worker_id from job_assigned group by
worker_id having count (*)>1);
Output:
WOR
NAME WAGE_PER_HOUR SPECIALISED_IN MAN
-------
--------------------- ----------------------------
------------------------ ------
W01
Mr.Cacophonix 50 Polishing M01
W02
Dhaval 40
Polishing M02
13.
Which workers having specialization in polishing start their job in September?
Select
a.worker_id, a.name from worker a, job_assigned b where
specialised_in='Polishing' and to_char (starting_date,'mon') ='sep';
Output:
WOR
NAME
-------
---------------
W01
Mr.Cacophonix
W02
Dhaval
14.
Display details of workers who are specialized in the same field as that of
Mr.Cacophonix or have a wage per hour more than any of the workers.
Select
* from worker where specialised_in in (select specialised_in from worker where name= ‘Mr.Cacophonix’) or wage_per_hour>
(select max (wage_per_hour) from worker);
Output:
WOR
NAME WAGE_PER_HOUR SPECIALISED_IN MAN
-------
--------------------- ----------------------------
------------------------ --------
W01 Mr.Cacophonix 50 Polishing M01
W02 Dhaval
40 Polishing M02
QUESTION:- 7
(1)
Create the table PUBLISHER with the fields (publ_id, publ_name, contact_person,
contact_addr, contact_phone):
create
table PUBLISHER
(publ_id
varchar2(3) primary key,
publ_name varchar2(25),
contact_person varchar2(25),
contact_addr varchar2(35),
contact_phone number(6));
Output:
Insert
the records into the table PUBLISHER:
Insert
into PUBLISHER values (‘&publ_id’, '&publ_name', '&contact_person',
'&contact_addr', &contact_phone);
View
all the records of the table PUBLISHER
Select
* from PUBLISHER;
Output:
PUB PUBL_NA CONTACT_PER CONTACT_AD
CONTACT_PH
------ ------------------------- ---------------------- ---------------------
---------------------------
P01 Atul Prakashan
Ashish
Sojitra 321789
P02 Tata McGrawhill
Dhaval
Anjar 289147
P03 Pearson Education
Dipen
Anand 321454
P04 PHP
Hardik Ode 282007
P05 Oracle
Shivam Bardoa 257324
(2)
Create the table CATEGORY with the fields (cat_id, cat_details, max_books, duration):
create
table category
(cat_id
number(3) primary key,
cat_details varchar2(10) not null,
max_books number(2) ,
duration number(2),
constraint ck_max_books
check(max_books>0));
Insert
the records into the table CATAGORY:
Insert
into category values (&cat_id, '&cat_details', &max_books,
&duration);
View
all the records of the table CATEGORY:
Select
* from category;
Output:
CAT_ID
CAT_DETAILS MAX_BOOKS DURATION
---------- -------------------- ------------------- ----------------
1 Programming 50 4
2 Web Designing 40 10
3 Networking 70 30
4 Hardware 85 50
5 Hacking 70 50
(3)
Create the table BOOK_MASTER with the fields (book_id, bname, isbn_no,
total_copies, publ_id):
create
table book_master
(
book_id varchar2(3) primary key,
bname varchar2(25) not null,
isbn_no number(9) unique,
total_copies number(2),
publ_id varchar2(3) references publisher);
Output:
Insert
the records into the table BOOK_MASTER:
Insert into book_master values(‘&book_id’,
'&bname',&isbn_no,&total_copies,
’&publ_id’);
View
all the records of the table BOOK_MASTER:
Select
* from book_master;
Output:
BOO
BNAME ISBN_NO
TOTAL_COPIES PUB
------ --------------------------- ------------- ----------------------- ------
B01
C Programming 73659789 50 P02
B02
Networking in 24 hrs 69853758 37 P04
B03
Hacking by A.F. 74204200 60 P03
B04
MCSA-290 79836789 30 P04
B05
Linux Unleashed 69873215 40 P04
(4)
Create the table MEMBER with the fields (member_id, mname, cat_id,
mem_ship_dt):
create
table member
(
member_id varchar2(3) primary key,
mname varchar2(25) not null,
cat_id number(3) references category,
mem_ship_dt date);
Insert
the records into the table MEMBER:
Insert
into MEMBER values (‘&member_id’, '&mname',&cat_id,'&mem_ship_dt');
View
all the records of the table MEMBER:
Select
* from member;
Output:
MEM
MNAME CAT_ID
MEM_SHIP_
---
------------------------- ---------- ------------------
M01
Jigar 2 20-JAN-08
M02
Dhaval 4 05-AUG-09
M03
Ashish 3 31-JAN-08
M04
Hardik 4 04-AUG-09
M05
Abbas 3 12-DEC-08
(5)
Create the table ISSUE with the fields (issue_id, member_id, book_id,
issue_ret, issue_ret_dt):
create
table issue
(issue_id
varchar2 (3) primary key,
member_id varchar2(3) references member,
book_id varchar2(3) references book_master,
issue_ret varchar2(1),
issue_ret_dt date);
Insert
the records into the table ISSUE:
Insert
into issue values(‘&issue_id’, ’&member_id’, ’&book_id’,
'&issue_ret', '&issue_ret_dt');
View
all the records of the table ISSUE:
Select
* from issue;
Output:
ISS
MEM BOO I ISSUE_RET
-----
------- ------- - -----------------
IS1
M01 B01 A 25-JUL-08
IS2
M05 B03 C 23-APR-08
IS3
M04 B01 A 22-AUG-05
IS4
M02 B03 C 12-JUN-08
IS5
M03 B04 I 27-JUN-08
Queries:
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 constraint constraint_issue_ret check (issue_ret like 'I' or
issu_ret like 'R');
2.
Add a column to the MEMBER table, which will allow us to store the address of
the member.
Alter
table member add (address varchar2(40));
desc
member;
Output:
Name Null? Type
--------------------------- -------------- ---------------------
MEMBER_ID NOT NULL VARCHAR2(3)
MNAME NOT NULL VARCHAR2(25)
CAT_ID NUMBER(3)
MEM_SHIP_DT DATE
ADDRESS VARCHAR2(40)
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;
Output:
Table
created.
Select
* from LIBRARY_USERS;
Output:
MEM
MNAME CAT_ID
MEM_SHIP_
---
------------------------- --------- --------- ------------------
M01
Jigar 2 20-JAN-08
M02
Dhaval 4 05-AUG-09
M03
Ashish 3
31-JAN-08
M04
Hardik 4 04-AUG-09
M05
Abbas 3 12-DEC-08
4.
Give details about members who have issued books, which contain 'ing' somewhere
in their titles.
Select
* from member where member_id in (select member_id from issue where book_id in
(select book_id from book_master where bname like '%ing%'));
Output:
MEM
MNAME CAT_ID
MEM_SHIP_
-------
------------ ---------- -------------------
M01
Jigar 2 20-JAN-08
M02
Dhaval 4 05-AUG-09
M04
Hardik 4 04-AUG-09
M05
Abbas 3 12-DEC-08
5.
Display the books that have been issued at the most three times in the year
2003.
Select
* from book_master where book_id in (select book_id from issue where (to_char
(issue_ret_dt,'YYYY')) ='2003' group by book_id having count (*) <=3);
no
rows selected
6.
Display which books of publisher PHI that are issued right now.
Select
bname from book_master b, publisher p, Issue i where b.publ_id=p.publ_id and
p.publ_name like 'PHP’ and b.book_id=i.book_id and i.issue_ret like '1');
no
rows selected
7.
Display details about books whose all copies are issued.
Select
* from book_master where (book_id, total_copies) in (select book_id, count
(issue_ret) from issue where issue_ret like '1' group by book_id);
no
rows selected
8.
Display the book details and members for books, which have been issued between
1st Oct 2005 and 15th Nov 2005.
Select
bname, isbn_no, total_copies, mname, mem_ship_dt from book_master b, member m,
issue i where (issue_ret like 'I') and (issue_ret_dt between '1-oct-2005' and
'15-Nov-2005') and b.book_id=i.book_id and m.member_id=i.member_id;
no
rows selected
9.
Display all staff members who have issued at least two books.
Select
mname, count (i.issu_ret) as "Total Issued Books" from member m,
issue i where (i.member_id=m.member_id and i.issue_ret like 'I') group by mname
having (count(i.issu_ret))>1;
Output:
10. Display details about those
publishers whose more than 100 books are available in the library.
Select
publ_name, count (b.book_id) as "No. of Books" from publisher p,
book_master b where (p.publ_id=b.publ_id) group by publ_name having
(count(b.book_id))>1;
Output:
PUBL_NAME No. of Books
-------------------------
------------------
PHP 3
11.
Delete all those members whose membership has expired.
Delete
from member m, category c where ((sysdate - mem_ship_dt)/365) > c.duration
and m.cat_id=c.cat_id;
Output:
12.
How many members registered in the last three months?
Select
count(*) as "Members Registered in Last Three Months" from member
where mem_ship_dt>(sysdate -90);
Output:
13.
Display since how many months has each staff member registered.
Select
mname, (round ((sysdate - mem_ship_dt) / 30)) as "Months Since Each Member
Registered" from member;
Output:
0 comments:
Post a Comment