DOC PREVIEW
SJSU CMPE 226 - Extra Assign Key Answers

This preview shows page 1 out of 4 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 4 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 4 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

WHERE Vendors.vid = Inventory.vid and Inventory.did = Devices.didWHERE Vendors.vid = Inventory.vid and Inventory.did = Devices.didWHERE Vendors.vid = Inventory.vid and Inventory.did = Devices.didWHERE vname NOT IN (SELECT vendors-disk.vnameFROM vendors-disk AS VDWHERE Vendors.vid = Inventory.vid and Inventory.did = Devices.didDatabase SystemsCmpE 226 Extra Assignment – Key Answers Spring 2003Part 1:Assume the following three relations:Vendors(vid, vname, vcity)Where vid is the identifier of a vendor (primary key), vname is the vendor’s name, and vcity is the vendor’s city.Devices(did, speed, ram, hd, type)Where did is the device identifier (primary key), speed is the speed of the processor in thedevice, hd is the size of the hard disk in the device, type is the device’s type, i.e. computer, printer, …Inventory(vid, did, cost)Inventory specify which vendor makes which devices at which cost (vid and did are primary key)Write the following queries n SQL:1. List the vendor name for vendors located in San Jose.SELECT vnameFROM VendorsWHERE vcity = “San Jose”2. List all devices supplied by “YourStore” and not supplied by “CircuitCity” where “YourStore” and “CircuitCity” are vendors’ names.SELECT didFROM Vendors, InventoryWHERE Vendors.vid = Inventory.vid and vname = “YourStore”and did NOT IN (SELECT vidFROM Vendors, InventoryWHERE Vendors.vid = Inventory.vid and vname = “CircuitCity”)3. Find the device with the cheapest price for vendor “CircuitCity”SELECT didFROM Vendors, Devices, InventoryWHERE Vendors.vid = Inventory.vid and Inventory.did = Devices.didand vname = “CirvuitCity”and cost = (SELECT min (cost)FROM Vendors, Devices, InventoryWHERE Vendors.vid = Inventory.vid and Inventory.did = Devices.did and vname = “CircuitCity”)ORCREATE VIEW sup-cost ASSELECT vname, did, costFROM Vendors, Devices, InventoryWHERE Vendors.vid = Inventory.vid and Inventory.did = Devices.didSELECT didFROM sup-costWHERE vname = “CircuitCity”and SELECT min (cost)FROM sup-costWHERE vname = “CircuitCity”)4. List the vendor name for vendors who supply all the hard disk sizes that vendor “YourStore” Supplies.SELECT vnameFROM Vendors as VWHERE NOT EXISTS (SELECT hd FROM Vendors, Devices, Inventory WHERE Vendors.vid = Inventory.vid and Inventory.did = Devices.did and vname = “YourStore” and hd NOT IN (SELECT hd FROM Vendors, Devices, Inventory WHERE Vendors.vid = Inventory.vid and Inventory.did = Devices.did and Vendors.vid = V.vid))5. List the vendor name for vendors who supply none of the hard disk sizes that vendor “YourStore” Supplies.SELECT vnameFROM Vendors as VWHERE NOT EXIST (SELECT hd FROM Vendors, Devices, Inventory WHERE Vendors.vid = Inventory.vid and Inventory.did = Devices.did and vname = “YourStore” and hd IN (SELECT hd FROM Vendors, Devices, Inventory WHERE Vendors.vid = Inventory.vid and Inventory.did = Devices.did and Vendors.vid = V.vid))ORSELECT vnameFROM Vendors WHERE vname NOT IN (SELECT Vendors.vname FROM Vendors as V, Devices as D, Inventory as IWHERE Vendors.vid = Inventory.vid and Inventory.did = Devices.didand V.vid = I.vid and I.did = D.didand V.vname = “YourStore”and Devices.hd = D.hd)ORCREATE VIEW vendors-disk ASSELECT vname, hdFROM Vendors, Devices, Inventory WHERE Vendors.vid = Inventory.vid and Inventory.did = Devices.did SELECT vnameFROM vendors-diskWHERE vname NOT IN (SELECT vendors-disk.vname FROM vendors-disk AS VD WHERE vendors-disk.hd = VD.hd and VD.name = “YourStore”)ORSELECT vnameFROM Vendors as VWHERE NOT EXISTS (SELECT hd FROM Vendors, Devices, Inventory WHERE Vendors.vid = Inventory.vid and Inventory.did = Devices.did and vname = “YourStore”) INTERSECT (SELECT hd FROM Vendors, Devices, Inventory WHERE Vendors.vid = Inventory.vid and Inventory.did = Devices.did and Vendors.vid = V.vid))Part 2:Using the given spatiotemporal input relations, express the following queries in SQL:1. Relations: Snow: which describes the snow cover on the top of the mountainRock: which describes a rocky area on its side.Query (a) -- What part of the rock will be free from snow at time 10?Rock (x, y, time)Snow (x, y, time)select x, y from Rock where time=10 and x, y not in (select x, y from Snow)2. Relations:Mall: which describes the shops in a mall.Customer: which describes customers visit the mallQuery (b) -- Which pair of customers are one or more floors at least partially directly above or below each other at any time?Mall (ShopName, Floor) Customer(CustomerName, ShopName, Time)Query b:Select C1.CustomerName, C2.CustomerNameFrom CustomerName C1, CustomerName C2, Mall M1, Mall M2Where C1.ShopName=M1.ShopName and C2.ShopName=M2.ShopName and M1.Floor<>M2.Floor and C1.Time=C2.TimeQuery (c) -- Which pair of customers visit the same shop at the same time?Query c:Assume: One shop can only be on one floor.Select C1.CustomerName, C2.CustomerNameFrom CustomerName C1, CustomerName C2Where C1.Time = C2.Time and C1.ShopName =


View Full Document

SJSU CMPE 226 - Extra Assign Key Answers

Documents in this Course
SQL-99

SQL-99

71 pages

XML

XML

52 pages

XML

XML

14 pages

Chapter 9

Chapter 9

45 pages

Load more
Download Extra Assign Key Answers
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Extra Assign Key Answers and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Extra Assign Key Answers 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?