Skip to document
Welcome to StudocuSign in to access the best study resources
Guest user
0followers
Upload
HomeMy LibraryAsk AI
  • You don't have any recent items yet.
My Library
  • You don't have any courses yet.
  • You don't have any books yet.
  • You don't have any Studylists yet.
Premium
This is a Premium Document. Some documents on Studocu are Premium. Upgrade to Premium to unlock it.

Assignment 2-Solutions

Prof Dimitri
Course

Advanced Database Systems (CS 434)

10 Documents
Students shared 10 documents in this course
Academic year: 2020/2021
Uploaded by:
Anonymous Student
This document has been uploaded by a student, just like you, who decided to remain anonymous.
Technische Universiteit Delft

Comments

Please sign in or register to post comments.

Students also viewed

Related documents

Preview text

CS 631: DATA MANAGEMENT SYSTEMS DESIGN

ASSIGNMENT 2

EXERCISE 1 (SQL Queries) Consider the following schema:

SUPPLIERS (SID : integer , SNAME : string , CITY : string ) PARTS (PID : integer , PNAME : string , COLOR : string ) CATALOG (SID : integer , PID : integer , COST : real )

The key fields are underlined, and the domain of each field is listed after the field name. Thus, SID is the key for SUPPLIERS, PID is the key for PARTS, and SID and PID together form the key for CATALOG. The CATALOG relation lists the prices charged for parts by suppliers. CATALOG is a foreign key referring to SUPPLIERS and CATALOG is a foreign key referring to PARTS.

Write the following queries in SQL.

  1. Find the SIDs of suppliers who supply a red part and a green part.

SELECT C

FROM CATALOG C, PART P

WHERE C = P AND P = ‘red' AND C IN (SELECT C FROM CATALOG C, PART P WHERE C = P AND P = ‘green’)

(SELECT C

FROM CATALOG C, PART P

WHERE C = P AND P = ‘red') INTERSECT (SELECT C FROM CATALOG C, PART P WHERE C = P AND P = ‘green’)

(SELECT SID

FROM CATALOG NATURAL JOIN PART

WHERE COLOR = ‘red') INTERSECT (SELECT SID FROM CATALOG NATURAL JOIN PART WHERE COLOR = ‘green’)

  1. Find the SIDs of suppliers who supply a red part or a green part.

SELECT C FROM CATALOG C, PART P WHERE C = P AND (P = ‘red’ OR P = ‘green’

SELECT SID

FROM CATALOG NATURAL JOIN PART

WHERE COLOR = ‘red' OR P = ‘green’

( SELECT C

FROM CATALOG C, PART P

WHERE C = P AND P = ‘red’) UNION ( SELECT C FROM CATALOG C, PART P WHERE C = P AND P = ‘green’)

  1. Find the SNAMEs of suppliers who supply every red part and every green part.

SELECT S FROM SUPPLIER S WHERE NOT EXISTS (( SELECT P FROM PARTS P WHERE P=’red’ OR P='green') EXCEPT ( SELECT C FROM CATALOG C WHERE C = S))

SELECT S

FROM SUPPLIER S

WHERE NOT EXISTS ( SELECT *

FROM PARTS P

WHERE P = ‘red’ OR P=’green’ AND NOT EXISTS ( SELECT * FROM CATALOG C WHERE C = S AND C = P))

  1. Find the SNAMEs of suppliers who do not supply every red part.

SELECT S FROM SUPPLIER S WHERE EXISTS ( SELECT * FROM PARTS P WHERE P = ‘red’ AND NOT EXISTS ( SELECT * FROM CATALOG C WHERE C = S AND C = P))

Alternative: the following view computes the SIDs of suppliers who supply every red part. CREATE VIEW SUPPLIERS_ALL_RED_PARTS

FROM SUPPLIER S, CATALOG C

WHERE S = C AND C < ( SELECT MAX (COST)

FROM CATALOG

WHERE PID = C)

  1. For every part supplied by a supplier who is at the city of Newark, print the PID and the SID and the name of the suppliers who sell it at the highest price.

SELECT C, S, S

FROM SUPPLIERS S, CATALOG C

WHERE S = C AND

C IN ( SELECT PID

FROM CATALOG NATURAL JOIN SUPPLIERS

WHERE ADDRESS = ’NEWARK’) AND

C = ( SELECT MAX (COST)

FROM CATALOG

WHERE PID = C)

  1. For every part which has at least two suppliers, find its PID, its PNAME and the total number of suppliers who sell it.

SELECT PID, PNAME, COUNT (SID)

FROM PARTS NATURAL JOIN CATALOG

GROUP BY PID

HAVING COUNT (SID) > = 2

  1. Find the PIDs of parts supplied by every supplier who is at the city of Newark or by every supplier who is at the city of Trenton.

(SELECT P

FROM PARTS P

WHERE NOT EXISTS ( SELECT SID FROM SUPPLIERS WHERE ADDRESS = ’Newark’ EXCEPT ( SELECT SID FROM CATALOG WHERE PID = P))) UNION (SELECT P FROM PARTS P WHERE NOT EXISTS ( SELECT SID FROM SUPPLIERS WHERE ADDRESS = ’Trenton’ EXCEPT ( SELECT SID FROM CATALOG WHERE PID = P)))

(SELECT P

FROM PARTS P

WHERE NOT EXISTS ( SELECT *

FROM SUPPLIERS S

WHERE S. ADDRESS = ’Newark’ AND

NOT EXISTS ( SELECT *

FROM CATALOG C

WHERE C = P AND C = S)))

UNION

(SELECT P

FROM PARTS P

WHERE NOT EXISTS ( SELECT *

FROM SUPPLIERS S

WHERE S. ADDRESS = ’Trenton’ AND

NOT EXISTS ( SELECT *

FROM CATALOG C

WHERE C = P AND C = S)))

  1. Find the PIDs of parts supplied by every supplier who is at the city of Newark and by every supplier who is at the city of Trenton.

(SELECT P

FROM PARTS P

WHERE NOT EXISTS ( SELECT SID

FROM SUPPLIERS

WHERE ADDRESS = ’Newark’ OR ADDRESS = ’Trenton’

EXCEPT

( SELECT SID

FROM CATALOG

WHERE PID = P)))

(SELECT P

FROM PARTS P

WHERE NOT EXISTS ( SELECT *

FROM SUPPLIERS S

WHERE S = ’Newark’ OR S = ’Trenton’ AND NOT EXISTS ( SELECT * FROM CATALOG C WHERE C = P AND C = S)))

(SELECT P

FROM PARTS P

WHERE NOT EXISTS ( SELECT SID FROM SUPPLIERS WHERE ADDRESS = ’Newark’

EXCEPT

( SELECT SID FROM CATALOG WHERE PID = P)))

INTERSECT

(SELECT P

FROM PARTS P

WHERE NOT EXISTS ( SELECT SID FROM SUPPLIERS WHERE ADDRESS = ’Trenton’

EXCEPT

FROM (CATALOG NATURAL JOIN PARTS) NATURAL JOIN

(CATALOG AS C1(PID, SID1, COST1) NATURAL JOIN PARTS AS P1(PID1, PNAME1,

COLOR1)))

WHERE COLOR <> COLOR

  1. For every part which has a supplier, find its PID, PNAME, its average cost, maximum cost and

minimum cost.

SELECT PID, PNAME, MAX (COST), MIN (COST)

FROM PARTS NATURAL JOIN CATALOG

GROUP BY PID

Was this document helpful?
Premium
This is a Premium Document. Some documents on Studocu are Premium. Upgrade to Premium to unlock it.

Assignment 2-Solutions

Course: Advanced Database Systems (CS 434)

10 Documents
Students shared 10 documents in this course

University: New Jersey Institute of Technology

Was this document helpful?

This is a preview

Do you want full access? Go Premium and unlock all 7 pages

  • Access to all documents

  • Get Unlimited Downloads

  • Improve your grades

Upload

Share your documents to unlock

Already Premium?
CS 631: DATA MANAGEMENT SYSTEMS DESIGN
ASSIGNMENT 2
EXERCISE 1 (SQL Queries)
Consider the following schema:
SUPPLIERS (SID : integer, SNAME : string, CITY : string)
PARTS (PID : integer, PNAME : string, COLOR : string)
CATALOG (SID : integer, PID : integer, COST : real)
The key fields are underlined, and the domain of each field is listed after the field name. Thus, SID is the key
for SUPPLIERS, PID is the key for PARTS, and SID and PID together form the key for CATALOG. The
CATALOG relation lists the prices charged for parts by suppliers. CATALOG.SID is a foreign key referring to
SUPPLIERS.SID and CATALOG.PID is a foreign key referring to PARTS.PID.
Write the following queries in SQL.
1. Find the SIDs of suppliers who supply a red part and a green part.
SELECT C.SID
FROM CATALOG C, PART P
WHERE C.PID = P.PID AND P.COLOR = ‘red' AND
C.SID IN (SELECT C.SID
FROM CATALOG C, PART P
WHERE C.PID = P.PID AND P.COLOR = ‘green’)
(SELECT C.SID
FROM CATALOG C, PART P
WHERE C.PID = P.PID AND P.COLOR = ‘red')
INTERSECT
(SELECT C.SID
FROM CATALOG C, PART P
WHERE C.PID = P.PID AND P.COLOR = ‘green’)
(SELECT SID
FROM CATALOG NATURAL JOIN PART
WHERE COLOR = ‘red')
INTERSECT
(SELECT SID
FROM CATALOG NATURAL JOIN PART
WHERE COLOR = ‘green’)
2. Find the SIDs of suppliers who supply a red part or a green part.
SELECT C.SID
FROM CATALOG C, PART P
WHERE C.PID = P.PID AND (P.COLOR = ‘red’ OR P.COLOR = ‘green’

Why is this page out of focus?

This is a Premium document. Become Premium to read the whole document.

Why is this page out of focus?

This is a Premium document. Become Premium to read the whole document.

Students also viewed

Related documents