Two scripts are provided:

• stl-create and
• stl-drop.

The script stl-create will create the StL DB schema for you. It will also populate the tables with mock data. The script stl-drop is provided for convenience. It will drop your copy of StL DB from your DB2 schema space. If you mess things up, you can always drop StL DB and then re-create it.

To create the StL DB in your DB2 schema space:

• % db2 -tvf stl-create

Read the schema definition in stl-create for StL DB to understand the design and what the StL DB is about.

There are 15 queries that you are to write. Each is worth one point for a total of 15 points for Project III. I am the manager and I provide you with English specifications for the queries that I want answered from the database. You are providing SQL implementations of these queries for StL DB to do the job.

Each query is given a name, q1-q15, for bookkeeping. You have successfully implemented the SQL query if you get the same result when run in DB2 on StL DB. (And, of course, you have not cheated by making a simple query on purpose that manages to print the same results!) Otherwise, your query is unsuccessful. Grading per query is all-or-nothing. Either your query produces the right results, or it does not. The queries will be verified by a software tool we designed for that purpose. Make absolutly sure that the order of your attributes in the output is the same as specified for each query. Otherwise, our marking tool will say that your query is incorrect!

• q1: Find all distinct customer names of customers who purchased a book with price greater than the average price of all books. [name]
• q2: Return the distinct customer name and address of customers that purchased before July 5/1999. [name, city]
• q4: Report the customers that did not buy any humorous books. (If they did not buy any books, they qualify as well, of course.) [cid, name, city]
• q5: Report the customers that bought all the humorous books. [cid, name, city]
• q6: Print out all the books that were never bought. [title, year, language, cat, weight]
• q8: Output the distinct name and addresses of the customers who bought books under a club offer that was not the cheapest offer available to them. [name, city]
• q9: For each book, find the maximum nubmer of times it was bought using the same offer (club). (If the book was never bought, do not include it. Also, count only different acts of purchase, that is, if two copies of the book were bought at the same time, count it only once.) [title, year, off_count]
• q10: List the most recently published book(s) for each language. (If there are multiple such books, list them all). [title, year, language]
• q11: For each day, list the number of books bought that day. (Of course, we only care about the days referenced in the database.) [date, book_count]
• q12: List the average shipping cost of humorous books. [avg_cost]

Honesty: For projects, you are permitted to confer with others, seek advice, and (to a reasonable extent) help. However, remember that copying someone else's queries and claiming them as your own work is plagiarism. You must do your own work.

For each query, write your query in a file with the corresponding name as above. Have all of your query files together in a directory named query.

Include a file called INFO in your directory query. Put your name and student# in this file. Do not forget this!

When you are finished, use the submit command on ARIEL to turn in your directory query with your work.

% submit 3421M StL query