CS 327e
Spring 2004
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