Scribd
Upload
84 views3 pages

Quiz6 Solution PDF

This document is a quiz for a course on data management. It contains 5 multiple choice questions testing skills in relational calculus (TRC) and SQL for querying relational databases. The questions involve writing queries over schemas for suppliers, parts, and a catalog listing part prices. The questions test abilities like finding part names supplied by some supplier, supplier names supplying specific part types, supplier IDs supplying multiple part types, and identifying the supplier and part for the most expensive red part.

Uploaded by

salsabeel mohammed
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
84 views3 pages

Quiz6 Solution PDF

This document is a quiz for a course on data management. It contains 5 multiple choice questions testing skills in relational calculus (TRC) and SQL for querying relational databases. The questions involve writing queries over schemas for suppliers, parts, and a catalog listing part prices. The questions test abilities like finding part names supplied by some supplier, supplier names supplying specific part types, supplier IDs supplying multiple part types, and identifying the supplier and part for the most expensive red part.

Uploaded by

salsabeel mohammed
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 3

CS122A: Introduction to Data Management Spring 2018

Quiz 6: Declarative Queries _______________________________


Initial Score (out of 10)
Taken by: _______________________________________________ _______________________________
Name Student ID
● We will discuss the answers right after the quiz.
● You are to self-grade and record your actual ​initial​ score (above) as we do so.
● You should also record the correct answers for any problems that you miss.
● A full 10 points for taking this quiz will be included as part of your quiz grade.

Consider the following schema, where the key field(s) are underlined (e.g., ​sid ​is the key for
Supplier), and the domain of each field is listed after the italicized field name. The Catalog
relation lists the prices charged for parts by Suppliers. Write the following queries in the
indicated language (either the tuple relational calculus, denoted ​TRC​, or ​SQL​).

Suppliers (​sid​: integer​, ​sname​: string, ​address​: string)


Parts (​pid​: integer​, ​pname​: string, ​color​: string)
Catalog (​sid​: integer, ​pid​: integer​, ​cost​: decimal(8,2))

1) [​SQL​] Find the ​pnames​ of parts for which there is some supplier. (2 Pts)

SELECT DISTINCT p.pname


FROM Parts p, Catalog c
WHERE p.pid = c.pid

Alternative (one of several :-)):

SELECT DISTINCT p.pname


FROM Parts p
WHERE EXISTS
(SELECT * FROM Catalog c
WHERE c.pid = p.pid)

2) [​TRC​] Find the ​snames​ of Suppliers who supply a yellow part that costs < 100. (2 Pts)

{t(sname) | ∃s ∈ Suppliers (t.sname = s.sname ∧


(∃c IN Catalog (c.sid = s.sid ∧ c.cost < 100 ∧
(∃p IN Parts (p.pid = c.pid ∧ p.color = ‘yellow’ ))))}
CS122A: Introduction to Data Management Spring 2018

SQL :
SELECT DISTINCT sname
FROM Suppliers s, Catalog c, Parts p
WHERE s.sid = c.sid AND c.pid = p.pid AND p.color = 'yellow' AND c.cost < 100;

3) [​SQL​] Find the ​sids​ of suppliers who supply a yellow part and a blue part. (2 Pts)

SELECT c.sid
FROM Catalog c, Parts p
WHERE c.pid = p.pid AND p.color = 'yellow'
INTERSECT SELECT c1.sid
FROM Catalog c1, Parts p1
WHERE c1.pid = p1.pid AND p1.color = 'blue'

Alternative (MySQL):

SELECT sid1.sid FROM (SELECT c.sid


FROM Catalog c, Parts p
WHERE c.pid = p.pid AND p.color = 'yellow' ) AS sid1
JOIN (SELECT c1.sid
FROM Catalog c1, Parts p1
WHERE c1.pid = p1.pid AND p1.color = 'blue') AS sid2
ON sid1.sid = sid2.sid;

4) [​TRC​] Find the ​snames​ of suppliers who supply ONLY purple parts. (2 Pts)

{t(sname) | ∃s ∈ Suppliers (t.sname = s.sname ∧


(∄c IN Catalog
(∃p IN Parts (s.sid = c.sid ∧ c.pid = p.pid ∧
p.color ≠ ‘purple’)))}
SQL:
SELECT DISTINCT S.sname
FROM Suppliers S
WHERE S.sid NOT IN (SELECT C1.sid
FROM Parts P, Catalog C1
WHERE C1.pid = P.pid
AND p.color <> ‘purple’ )
CS122A: Introduction to Data Management Spring 2018

5) [​SQL​] Find the ​sid​ of a supplier who supplies the most expensive red part along with the
pname​ of the part. (2 Pts)

SELECT c.sid, p.pname


FROM Catalog c, Parts p
WHERE c.pid = p.pid AND p.color = 'red' ORDER BY c.cost DESC limit 1;

You might also like