354.3-08.SampleMidterm

354.3-08.SampleMidterm - CMPT-354 D1 Fall 2008 Instructor:...

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

View Full Document Right Arrow Icon
CMPT-354 D1 Fall 2008 Instructor: Martin Ester TA: Gustavo Frigo Sample Midterm Exam with Solution Time: 60 minutes Problem 1 (Queries in relational algebra and SQL) Consider the following schema of a product database : Parts(pid: integer , pname: string, color: string) Suppliers(sid: integer , sname: string, address: string) Catalog(sid: integer, pid:integer , price: real) The Catalog records that some Supplier sid supplies Part pid at a given price. Formulate each of the following queries in relational algebra (RA) and in SQL. Name all attributes in the result appropriately. If the query cannot be explained in a language, state this and explain why not. a) For every green Part that is supplied by some Supplier, list the number of these Suppliers and the average price in descending order of that number. The query cannot be formulated in RA, since RA has no group-by and aggregation operations. SELECT COUNT (sid) AS numberSuppliers, AVG(price) AS avgPrice FROM Catalog C, Parts P WHERE C.pid = P.pid AND P.color = ‘green’
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 10/07/2009 for the course CS 12601 taught by Professor Kewang during the Spring '09 term at Simon Fraser.

Page1 / 4

354.3-08.SampleMidterm - CMPT-354 D1 Fall 2008 Instructor:...

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