3. (50) This assi ment is to be completed on a DBMS of your choice Indicate the

Question

3. (50) This assi ment is to be completed on a DBMS of your choice Indicate the name and version of vour DBMS Consider the following schema Suppliers(sid: integer, sname: string, address: string) Parts(pid: integer, pncme: string, color: string) Catalog(sid: integer, pid: integer, cost: real) The Catalog relation lists the prices charged for parts by Suppliers sid snamme addreSS pid pnaime coloul sid pid cost sl pl 200 s1 p3 300 s2 pl 100 s2 p2 400 s2 p3 200 s2 p4 300 s2 p5 100 s3 p2 200 s3 p5 400 s4 p4 400 s5 pl 100 s5 p2 200 pl nut red p2 bolt green p3 screw red sl smith london S2 jones pariS s3 blake paris s4 clark london s5 adams athens s6 (you) regina caim ue po cog greein Write the following queries in SQL: 1. Find the puames of parts for which there is some supplier 2. Find the sncnes of suppliers who supply every part. 3. Find the sncnes of suppliers who supply every red part. 4. Find the pnames of parts supplied by S1 and no one else 5. Find the sids of suppliers who charge more for some part than the average cost of that part (averaged over all the suppliers who supply that part) 6. For each part, find the sncme of the supplier who charges the most for that part 7. Find the sids of suppliers who supply only red parts 8. Find the sids of suppliers who supply a red part and a green part 9. Find the sids of suppliers who supply a red part or a green part. 10. For every supplier that only supplies green parts, print the name of the supplier and the total number of parts that she supplies 11. For every supplier that supplies a green part and a red part, print the name and price of the most expensive part that she supplies Hand in your SQL query statement and the test results

Explanation / Answer

Find
the
pnames
of
parts
for
whic
h
there
is
some
supplier.
SELECT
p.pname
FR
OM
P
arts
AS
p
WHERE
EXISTS
(SELECT
*
FR
OM
Catalog
AS
c
WHERE
c.pid
=
p.pid)
5.2.3
Find
the
snames
of
suppliers
who
supply
ev
ery
red
part.
SELECT
s.sid,
s.sname
FR
OM
Suppliers
AS
s,
Catalog
AS
c,
P
arts
AS
p
WHERE
s.sid
=
c.sid
AND
p.pid
=
c.pid
AND
p.color
=
`red'
GR
OUP
BY
s.sid,
s.sname
HA
VING
COUNT(*)
=
(SELECT
COUNT(*)
FR
OM
P
arts
AS
p1
WHERE
p1.color=`red')
5.2.4
Find
the
pnames
of
parts
supplied
b
y
Acme
Widget
Suppliers
and
no
one
else.
SELECT
p.pname
FR
OM
P
arts
AS
p
WHERE
p.pid
IN
((SELECT
c1.pid
AS
pid
FR
OM
Catalog
AS
c1,
Suppliers
s1
WHERE
c1.pid
=
s1.sid
AND
s1.sname
=
`Acme
Widget
Suppliers')
EX
CEPT
(SELECT
DISTINCT
c2.pid
AS
pid
FR
OM
Catalog
AS
c2,
Suppliers
s2
WHERE
c2.pid
=
s2.sid
AND
s2.sname
!=
`Acme
Widget
Suppliers'))
5.2.5
Find
the
sids
of
suppliers
who
c
harge
more
for
some
part
than
the
a
v
erage
cost
of
that
part
(a
v
eraged
o
v
er
all
the
suppliers
who
supply
that
part).
SELECT
DISTINCT
c.sid
FR
OM
Catalog
AS
c,
(SELECT
c1.pid
AS
a
vg
pid,
A
V
G(c1.cost)
AS
a
vg
cost
FR
OM
Catalog
AS
c1
GR
OUP
BY
c1.pid)
AS
TEMP
WHERE
c.pid
=
TEMP
.a
vg
pid
AND
c.cost
>
TEMP
.a
vg
cost
1
5.2.8
Find
the
sids
of
suppliers
who
supply
a
red
part
and
a
green
part.
(SELECT
DISTINCT
c1.sid
AS
SID
FR
OM
P
arts
AS
p1,
Catalog
AS
c1
WHERE
p1.pid
=
c1.pid
AND
p1.color
=
`green')
INTERSECT
(SELECT
DISTINCT
c2.sid
AS
SID
FR
OM
P
arts
AS
p2,
Catalog
AS
c2
WHERE
p2.pid
=
c2.pid
AND
p2.color
=
`red')

Find the
names
of suppliers who supply some r
ed part.
SELECT
S.sname
FROM
Suppliers S, Parts P, Catalog C
WHERE
P.color = “red” AND P.pid = C.pid AND S.sid = C.sid
Another way to do it is
SELECT
S.sname
FROM
Suppliers S
WHERE
S.sid IN
(SELECT
C.sid
FROM
Parts P, Catalog C
WHERE
P.color = “red” A
ND P.pid = C.pid)
2.
Find the
sids
of suppliers who supply some red or green part.
SELECT
C.sid
FROM
Catalog C, Parts P
WHERE
(P.color = “red” OR P.color = “green”) AND P.pid = C.pid
3.
Find the
sids
of suppliers who supply some red part or are at 221 Packer A
ve.
SELECT
S.sid
FROM
Suppliers S
WHERE
S.address = “221 Packer Ave”
OR S.sid IN (SELECT
C.sid
FROM
Parts P, Catalog C
WHERE
P.color = “red AND P.pid = C.pid)
4.
Find the
sids
of suppliers who supply some red part and some green part.
SELECT
C.sid
FROM
Pa
rts P, Catalog C
WHERE
P.color = “red” AND P.pid = C.pid
AND EXISTS
(SELECT
P2.pid
FROM
Parts P2, Catalog C2
WHERE
P2.color = “green” AND
C2.sid =C.sid AND P2.pid = C2.pid)
5.
Find the
sids
of suppliers who supply every part.
SELECT
S.sid
FROM
Supplier
s S
WHERE
NOT EXISTS
(SELECT
S1.sid, P.pid
FROM
Suppliers S1, Parts P
WHERE
S.sid = S1.sid
EXCEPT
SELECT
C.sid, C.pid
FROM
Catalog C
WHERE
C.sid = S.sid)
6.
Find the
sids
of suppliers who supply every red part.
SELECT
S.sid
FROM
Suppliers S
WHERE
NO
T EXISTS
(SELECT
S1.sid, P.pid
FROM
Suppliers S1, Parts P
WHERE
S.sid = S1.sid AND P.color = “red”
EXCEPT
SELECT
C.sid, C.pid
FROM
Catalog C
WHERE
C.sid = S.sid)
7.
Find the
sids
of suppliers who supply every part which is red or green.
SELECT
S.sid
FROM
Suppliers S
WHERE
NOT EXISTS
(SELECT
P.pid, S2.sid
FROM
Parts P, Suppliers S2
WHERE
(P.color = “red” OR P.color = “green”) AND
S.sid = S2.sid
EXCEPT
SELECT
C.pid, C.sid
FROM
Catalog C
WHERE
C.sid = S.sid)
8.
Find the
sids
of suppliers who su
pply every red part or supply every green part.
SELECT
S.sid
FROM
Suppliers S
WHERE
NOT EXISTS
(SELECT
P.pid, S2.sid
FROM
Parts P, Suppliers S2
WHERE
P.color = “red” AND S.sid = S2.sid
EXCEPT
SELECT
C.pid, C.sid
FROM
Catalog C
WHERE
C.sid = S.si
d)
UNION
SELECT
S.sid
FROM
Suppliers S
WHERE
NOT EXISTS
(SELECT
P.pid, S2.sid
FROM
Parts P, Suppliers S2
WHERE
P.color = “green” AND S.sid = S2.sid
EXCEPT
SELECT
C.pid, C.sid
FROM
Catalog C
WHERE
C.sid = S.sid)

Related Questions

Navigate

• Browse (All)
• Browse 3
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.