1.
create table s
(s# char(5),
sname char(20),
status numeric(5),
city char(15),
primary key (s#));
table created.
create table p
(p# char(6),
pname char(20),
color numeric(6),
weight numeric (5,1),
city char(15),
primary key (p#));
table created
create table sp
(s# char(5),
p# char(6),
primary key (s#,p#),
foreign key (s#) references s,
foreign key (p#) references p);
table created
desc s;
Name Null? Type
S# NOT NULL CHAR(5)
SNAME CHAR(20)
STATUS NUMBER(5)
CITY CHAR(15)
Desc p;
Name Null? Type
P# NOT NULL CHAR(6)
PNAME CHAR(20)
COLOR CHAR(6)
WEIGHT NUMBER(5,1)
CITY CHAR(5)
Desc sp;
Name Null? Type
S# NOT NULL CHAR(5)
P# NOT NULL CHAR(6)
QTY NUMBER(9)
insert into s values ('&s#','&sname',&status,'&city');
Enter values for substitution variables in the script to execute:
Variable Value
s
sname
status
city
insert into p values ('&p#','&pname','&color',&weight,'&pcity');
Enter values for substitution variables in the script to execute:
Variable Value
p
pname
color
weight
pcity
insert into sp values ('&s#','&p#',&qty);
Enter values for substitution variables in the script to execute:
Variable Value
s
p
qty
select * from s;
S# SNAME STATUS CITY
s1# smith 20london
s2# jones 10paris
s3# blake 30paris
s4# clark 20london
s5# adams 30athens
s6# william 20london
6 rows selected.
select * from p;
P# PNAME COLOR WEIGHT CITY
p1# nut red 12londo
p2# bolt green 17paris
p3# screw blue 17rome
p4# screw red 14londo
p5# cam blue 12paris
p6# cog red 19londo
6 rows selected.
select * from sp;
S# P# QTY
s1# p1# 200
s1# p2# 200
s1# p3# 400
s1# p4# 200
s1# p5# 100
s1# p6# 100
s2# p1# 300
s2# p2# 400
s3# p2# 200
s4# p2# 200
s4# p4# 300
11 rows selected.
Queries:
1.Get color and city for non Paris parts with weight greater than 10
select px.color,px.city from p px where px.city<>'paris' and px.weight>10.0;
COLOR CITY
red londo
blue rome
red londo
red londo
2.For all parts get the part number and weight of the part in grams.
select p.p#, p.weight*454 as gramweight from p;
P# GRAMWEIGHT
p1# 5448
p2# 7718
p3# 7718
p4# 6356
p5# 5448
p6# 8626
6 rows selected.
3.Get the combination of suppliers and parts information such that suppliers and parts are collocated.
select s.*,p.p#,p.pname,p.color,p.weight from s,p where s.city=p.city;
S# SNAME STATUS CITY P# PNAME COLOR WEIGHT
s2# jones 10paris p2# bolt green 17
s3# blake 30paris p2# bolt green 17
s2# jones 10paris p5# cam blue 12
s3# blake 30paris p5# cam blue 12
4. get the total number of suppliers
select count (*) TOTAL from s;
TOTAL
6
5. get the maximum and minimum quantity for part p2.
select max(sp.qty)MAXQ ,min(sp.qty) MINQ from sp where sp.p#='p2';
MAXQ MINQ
6. for each part supplied, get the part number and the total shipment quantity.
select sp.p#,sum (sp.qty) TOTQTY from sp group by sp.p#;
P# TOTQTY
p1# 500
p2# 1000
p3# 400
p4# 500
p5# 100
p6# 100
6 rows selected.
7. Get supplier names for suppliers who supply part p2.
select distinct s.sname from s where s.s# in (select sp.s# from sp where sp.p# = 'p2');
no rows selected
8. Get supplier names for suppliers who supply atleast one red part
select distinct s.sname from s where s.s# in (select sp.s# from sp where sp.p# in (select p.p# from
p where p.color='red'));
SNAME
clark
jones
smith
9. Get suppliers numbers for suppliers with status less than the current maximus status in the s
table.
select s.s# from s where s.status < (select max(s.status)from s);
S#
s1#
s2#
s4#
s6#
10. Get suppliers names for suppliers who supply part p2.
select distinct s.sname from s where exists (select * from sp where sp.s#=s.s# and sp.p#='p2');
no rows selected
11. select supplier names for suppliers who do not supply part p2.
select distinct s.sname from s where not exists (select * from sp where sp.s# = s.s# and sp.p# = ‘p2’);
SNAME
adams
blake
clark
jones
smith
william
6 rows selected.
12. get supplier names for suppliers who supply all parts
select distinct s.sname from s where not exists (select * from p where not exists (select * from sp where
sp.s# =s.s# and sp.p# =p.p#));
SNAME
smith
13. get part numbers for parts that either weigh more than 16 pounds or are supplied by suppliers s2, or
both
select p.p# from p where p.weight>16.0 union select sp.p# from sp where sp.s# ='s2';
P#
p2#
p3#
p6#
