Objective: This exercise is designed to let you practice writing SQL query state

Question

Objective: This exercise is designed to let you practice writing SQL query statements.

Consider the below schema of the university database (keys are in bold and underline):

Part(pid: string, pname: string, description: string, color: string)
Department(did: string, dname:string, address: string)
Supplier(sid:string,sname:string, address:string)
Order(did:string, sid: string, pid:string, time:string, quantity:real, price:real)

The key fields are underlined, and the domain of each field is listed after the field name. Thus pid is the key for Part, did is the key for Department, sid is the key for Supplier, and did, sid,and pid together form the key for Order. Write the following queries in SQL statements.
  

Find the names of departments.

Find the names and addresses of suppliers who supply red part.

Find the pids of parts which are ordered by IT dept. Note IT is a department name.

Find the department names who have ordered parts from Supplier DELL. Note DELL is a supplier name.

Find the dids of departments which have ordered both part A01 and A02.

Find the dids of departments which have ordered at least one part.

Find the names of departments which have ordered at least two parts.

Find the names of departments which have ordered all parts.

Find the dids of departments which have not ordered any product from Supplier S001. Note S001 is supplier ID.

“Find the pids of parts that are supplied by at least two different suppliers.”

Explanation / Answer

Find the names of departments.

    SELECT dname

    FROM Department;

Find the names and addresses of suppliers who supply red part.

    SELECT S.sname, S.address

    FROM Supplier S, Part P, Order O

    WHERE P.color=’red’

    AND p.pid=O.pid

    AND S.sid=O.sid;

Find the pids of parts which are ordered by IT dept. Note IT is a department name.

    SELECT O.pid

    FROM Order O, Department D,

    WHERE D.dname=’IT’

    AND O.did=D.did

Find the department names who have ordered parts from Supplier DELL. Note DELL is a supplier name.

    SELECT D.dname

    FROM Order O, Department D, supplier S

    WHERE S.sname=’DELL’

    AND O.sid=S.sid

    AND O.did=D.did;

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

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