Lab 1

# The University of Texas

CS 327e

Spring 2004

### Lab 4

Due: 2004-03-29 Monday 8.30 a.m.

Total points = 200. There is NO late submission allowed. In other words, you receive 0 points if you submit late!

PURPOSE:

The purpose of this lab is to learn about SELECT statement in SQL.

IMPLEMENTATION:

For this lab, consider the PUBS database in the Excel spreadsheet, as well its schema, both of which are zipped up and shown below. Just as in lab1, you will need to import this spreadsheet into tables in your account.

For each of the English statement,

a) Submit the correct SELECT statement, along with

b) Resultant data from the PUBS database.

NOTE: There is more than 1 correct SQL answer to solve each query. You are only required to submit only 1 correct SQL statement per query. Clearly state any assumptions you make.

For these queries, consider the following tables from the schema:

TITLES

AUTHORS

TITLEAUTHOR

STORES

SALES

PUBLISHERS

Query1) Give the titles that got sold in the same state as their publishers. (10 points)

Query2) Give the names of titles, authors, their state, and their publisher’s state, who have authored titles from out-of-state publishers. (20 points)

Query3) Give the name(s) of highest-selling (quantity) author and their quantity sold. (20 points)

Query4) Give the states who sold less than the average quantity per state. (The average quantity per state is computed by taking the total quantity sold and dividing it by the number of distinct states in which these titles got sold.) (20 points)

NOTE: Assumes all stores.state are non-NULL entries.

Query5) Give the distinct names of authors who live in a city that contains ‘la’ in its city name, or whose titles are at most 20 characters long, or both. {HINT: Use UNION keyword}. (20 points)

Query6) Give the names of authors, and their titles, along with their quantities sold, who have published psychology titles for $15.00 or less, and have sold at least 26 of those titles. (20 points)

Query7) For each store that sold at least 1 title, give the following:

Store_Name, Store_City, Store_State, Store_Qty_Sold, Pct_State_Qty_Sold

Where

Store_Qty_Sold (= N say) is the total of all title quantities that were sold at the store;

Pct_State_Qty_Sold = (N / (D)) * 100.00

Where D is the total of all title quantities that were sold in the state to which this store belonged to.

Make sure the final results are sorted in ascending order by state, followed by ascending order by Store_Qty_Sold.

{HINT: Use the CONVERT function to get percentage in 2 decimal places.} (30 points)

Query8) Give the names of authors who didn’t sell a single title. (20 points)

Query9) For each (store, order, author, title) that was sold, compute the following:

a) Qty_Sold = (say N) = Total Quantity of this title sold in this order at this store.

b) Pct_Author_Qty_Sold = PAQS = (N/ (AQS)) * 100.00

Where AQS = Total quantity of titles sold that are authored by this author, across all orders and stores.

c) Pct_Order_Qty_Sold = POQS = (N/ (OQS)) * 100.00

Where OQS = Total quantity of titles sold in this order in this store.

d) Pct_Store_Qty_Sold = PSQS = (N/ (SQS)) * 100.00

Where SQS = Total quantity of titles sold in this store, across all orders for this store.

In other words, your resultant table will have the following 9 columns:

Store_Name, Order_Number, Author_First_Name, Author_Last_Name, Title, Qty_Sold, Pct_Author_Qty_Sold, Pct_Order_Qty_Sold, Pct_Store_Qty_Sold

The resultant data should be sorted by store name, order number, author first name, author last name, title, and quantity sold (all in ascending order). (40 points)

SUBMISSION:

Please submit a paper copy in class by the Due Date/Time.

Make sure you have written the following on your paper submission:

· First Name, Last Name

· Course Name

· Lab #

· EID (optional)

MAKE SURE YOU STAPLE THE PAGES BEFORE SUBMISSION.

Consult the class web page for

- Late submission policy

- Academic Dishonesty Policy

PUBS database schema [.zip file]

Last Updated: 3/25/2004 11:27:27 AM