MAP

SQL EXERCISE SOLUTION WITH OUTPUT


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

Twitter Delicious Facebook Digg Stumbleupon Favorites More