445-hw3-solutions - CMPSCI 445 Homework 3 SOLUTIONS 100...

Info iconThis preview shows pages 1–2. Sign up to view the full content.

View Full Document Right Arrow Icon
SOLUTIONS 100 Points Due October 1st, 5pm. Typed solutions preferred. If hand-written, solutions must be legible . 1. (30 pts) More on SQL Consider a database consisting of these three tables: Suppliers(sid: integer , sname: varchar(50), address: varchar(60)) Parts(pid: integer , pname: varchar(50), color: varchar(20)) Catalog(sid: integer, pid: integer , cost: real) Please answer the following questions, referring to this schema and the sample data shown in Figure 1. (a) Write an SQL query to find the distinct sids of suppliers who supply only red parts. Answer: SELECT DISTINCT C.sid FROM Catalog C WHERE NOT EXISTS ( SELECT * FROM Parts P WHERE P.pid = C.pid AND P.color <> Red ) (b) For the following SQL query: (i) write an English sentence describing the relation it computes, and (ii) show the result of the query on the sample data in Fig. 1 SELECT S.sname FROM Suppliers S WHERE NOT EXISTS ( ( SELECT P.pid FROM Parts P WHERE P.color = Red ) EXCEPT ( SELECT C.pid FROM Catalog C, Parts P
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 2
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 05/01/2011 for the course ECON 102 taught by Professor Erus during the Fall '09 term at Boğaziçi University.

Page1 / 5

445-hw3-solutions - CMPSCI 445 Homework 3 SOLUTIONS 100...

This preview shows document pages 1 - 2. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online