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)

-------------

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))

----------------------------------------------------------------------------------------

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 7^th 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:

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(*)

-------------

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)

-------------------------------------

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 1^stOct 2005 and 15^thNov 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:

MAP

EBOOKS DOWNLOAD FREE | C LANGUAGE | Free Download Ebooks Programming Language C

SQL EXERCISE SOLUTION WITH OUTPUT

2) Create the table MOVIE with the fields (movie_id, movie_name, date_of_release):

1) Create the table WORKER with the fields (worker_id, name, wage_per_hour, specialized_in, manager_id):

View all the records of table WORKER

2) Create the table JOB with the fields (job_id, type_of_job, status):

View all the records of table JOB

3) Create the table JOB_ASSIGNED with the fields (worker_id, job_id, starting_date, number_of_days):

Insert the records into the table JOB_ASSIGNED:

Download

ABOUT OWNER OF GTU

LixTech Infosoft

Important Links

Followers

GTU ON FACEBOOK

Labels

Labels