University of Washington
Due June 2 (Last day of class)For this homework please choose 4 of the 5 lettered (A - E) problems. If you do more than 4, you may receive extra credit.
Transform the following nested queries into semantically equivalent queries without subqueries, or argue that there is no such transformation.
SELECT R.c FROM R WHERE R.c = ( SELECT S.c FROM S WHERE S.d = 42 )
SELECT ssn FROM employee WHERE ssn = ( SELECT MAX(essn) FROM works_on )
SELECT fname FROM employee WHERE ssn = ( SELECT MAX(essn) FROM works_on )
Q1 is a query returning the name, max selling price, and max per day sales for all products sold in the northwest.
SELECT NorthWestProductSales.productName, NorthWestProductSales.price, MAX(Inventory.perDaySales) FROM NorthWestProductSales, Inventory WHERE NorthWestProductSales.productName = Inventory.productName and NorthWestProductSales.price = Inventory.price GROUP BY NorthWestProductSales.productName, NorthWestProductSales.price
V1 is a view of all products sold in the northwest, with their highest selling price.
CREATE VIEW NorthWestProductSales (productName, price) AS SELECT ProductSales.name, MAX(ProductSales.price) FROM ProductSales, NorthWesternStates WHERE ProductSales.zipcode = NorthWesternStates.zipcode GROUP BY ProductSales.name
Q2 is another query returning the name, max selling price, and max per day sales for all products sold in the northwest.
SELECT ProductSales.name, MAX(ProductSales.price), MAX(Inventory.perDaySales) FROM ProductSales, Inventory WHERE ProductSales.price = Inventory.price and ProductSales.name = Inventory.productName GROUP BY ProductSales.name
This question will familiarize you with the workings of a commericial database optimizer. You should be familiar enough with the operation of a typical query optimizer to be able to pose a query which forces the optimizer to choose a particular plan. You will need to use SQL Server. You can view the query plan in SQL Server by typing CTRL-L in the Query Analyzer.
Use the same database and relation instances you used for HW 1. Follow this link if you need to re-create this database. Do not add any rows or indices to this database -- create it precisely as we have given it to you. Answer the question below for each of the following join algorithms:
Write an SQL query whose execution plan contains the algorithm. The query must consist of exactly one query block ("select...from...where... group by...order by...", where the "where", "group by", and "order by" clauses are optional). The query need not return any rows.
YourBookstore.com has data from several sources. They have a relational source with basic book information. They have an XML source which has some additional information they buy from Amazon.com. And finally they have a source of reviews.
The relational source is a typical relational database, with primary keys and foreign keys as specified. On the other hand, Amazon.com is picky about how they will give out data. They do not want YourBookstore.com to be able to recreate their database completely, so in order to get any additional information the ISBN number for the particular book must be specified. The source of reviews allows fulltext keyword searches, with the ability to specify a search only on the book title, author and/or ratings fields.
The question of updating a record R in a recoverable manner was addressed in class. The following order of operations was declared to be the only one which works.
For each operation marked with (*) in the list above, argue whether it must come after the operation which preceeds it in order to achieve the goal of a recoverable update to record R. Be sure to state your assumptions clearly. (6 pts each + 1 pt for good measure)