The University of Texas

CS 327e

Spring 2004

Lab 4 Solution

Due: 2004-03-26 Friday 8.30 a.m.

Total points = 200. There is NO late submission allowed. In other words, you receive a 0 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)

SELECT t.title AS Pub_Store_Titles

FROM titles t, sales s, publishers p, stores st

WHERE

t.title_id = s.title_id AND

p.pub_id = t.pub_id AND

s.stor_id = st.stor_id AND

st.state = p.state

Pub_Store_Titles

Straight Talk About Computers

Secrets of Silicon Valley

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)

SELECT t.title AS TITLE, a.au_fname AS AUTHOR_FIRSTNAME, a.au_lname AS AUTHOR_LASTNAME, a.state AS AUTHOR_STATE, p.state AS PUBLISHERS_STATE

FROM titles t, publishers p, authors a, titleauthor ta

WHERE

a.au_id = ta.au_id AND

ta.title_id = t.title_id AND

t.pub_id = p.pub_id AND

p.state <> a.state

TITLE AUTHOR_FIRSTNAME AUTHOR_LASTNAME AUTHOR_STATE PUBLISHERS_STATE

Prolonged Data Deprivation: Four Case Studies Johnson White CA MA

You Can Combat Computer Stress! Marjorie Green CA MA

Sushi, Anyone? Michael O'Leary CA DC

Sushi, Anyone? Burt Gringlesby CA DC

Emotional Security: A New Algorithm Charlene Locksley CA MA

Fifty Years in Buckingham Palace Kitchens Reginald Blotchet-Halls OR DC

Sushi, Anyone? Akiko Yokomoto CA DC

Silicon Valley Gastronomic Treats Innes del Castillo MI DC

The Gourmet Microwave Michel DeFrance IN DC

Computer Phobic AND Non-Phobic Individuals: Behavior Variations Stearns MacFeather CA DC

Computer Phobic AND Non-Phobic Individuals: Behavior Variations Livia Karsen CA DC

Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean Sylvia Panteley MD DC

The Gourmet Microwave Anne Ringer UT DC

Is Anger the Enemy? Anne Ringer UT MA

Is Anger the Enemy? Albert Ringer UT MA

Life Without Fear Albert Ringer UT MA

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

SELECT a.au_fname AS Author_First_Name, a.au_lname AS Author_Last_Name, author_qty.au_qty AS Quantity

FROM authors a,

(SELECT ta.au_id AS au_id, sum(title_qty.totalQty) AS au_qty

FROM titleauthor ta, titles t,

(SELECT s.title_id AS title_id, sum (s.qty) AS totalQty

FROM sales s

GROUP BY s.title_id) title_qty

WHERE

t.title_id = ta.title_id AND

t.title_id = title_qty.title_id

GROUP BY ta.au_id

) author_qty

WHERE

a.au_id = author_qty.au_id AND

author_qty.au_qty

=

(SELECT max(author_qty.au_qty)

FROM

(SELECT ta.au_id AS au_id, sum(title_qty.totalQty) AS au_qty

FROM titleauthor ta, titles t,

(SELECT s.title_id AS title_id, sum (s.qty) AS totalQty

FROM sales s

GROUP BY s.title_id) title_qty

WHERE

t.title_id = ta.title_id AND

t.title_id = title_qty.title_id

GROUP BY ta.au_id

) author_qty

)

Author_First_Name Author_Last_Name Quantity

Anne Ringer 148

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)

SELECT DISTINCT st.state AS state

FROM stores st,

(

SELECT st.state AS state, sum (s.qty) AS qty

FROM stores st, sales s

WHERE s.stor_id = st.stor_id

GROUP BY st.state

) state_qty

WHERE st.state = state_qty.state

AND

state_qty.qty

<

(

SELECT sum (s.qty)/count(distinct st.state) AS avg_qty_by_state

FROM stores st, sales s

WHERE s.stor_id = st.stor_id

)

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

state

OR

WA

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)

SELECT a.au_fname AS Author_First_Name, a.au_lname AS Author_Last_Name

FROM authors a

WHERE a.city like '%la%'

UNION

SELECT a.au_fname AS Author_First_Name, a.au_lname AS Author_Last_Name

FROM authors a, titleauthor ta, titles t

WHERE

a.au_id = ta.au_id

AND

ta.title_id = t.title_id

AND

len (t.title) <= 20

Author_First_Name Author_Last_Name

Akiko Yokomoto

Albert Ringer

Anne Ringer

Burt Gringlesby

Charlene Locksley

Dean Straight

Dirk Stringer

Livia Karsen

Marjorie Green

Meander Smith

Michael O'Leary

Stearns MacFeather

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)

SELECT a.au_fname, a.au_lname, t.title, total_qty.total_title_qty AS total_qty_sold

FROM authors a, titleauthor ta, titles t,

(

SELECT s.title_id AS title_id, sum(s.qty) AS total_title_qty

FROM sales s

GROUP BY s.title_id

HAVING sum(s.qty) >= 26

) total_qty

WHERE

a.au_id = ta.au_id AND

ta.title_id = t.title_id AND

t.type = 'psychology’ AND

t.price <= 15.00 AND

total_qty.title_id = t.title_id

au_fname au_lname title total_qty_sold

Anne Ringer Is Anger the Enemy? 108

Albert Ringer Is Anger the Enemy? 108

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)

SELECT

st.stor_name AS Store_Name,

st.city AS Store_City,

st.state AS Store_State,

store_total.Store_Qty_Sold AS Store_Qty_Sold,

CONVERT (NUMERIC(5,2), ((store_total.Store_Qty_Sold * 1.00 /store_state_total.State_Qty_Sold) * 100.00)) AS Pct_State_Qty_Sold

FROM stores st,

(

SELECT s.stor_id AS stor_id, sum(qty) AS Store_Qty_Sold

FROM sales s

GROUP BY s.stor_id

) store_total,

(

SELECT st.state AS state, sum(s.qty) AS State_Qty_Sold

FROM sales s, stores st

WHERE s.stor_id = st.stor_id

GROUP BY st.state

) store_state_total

WHERE

st.state = store_state_total.state AND

st.stor_id = store_total.stor_id

ORDER BY st.state ASC, Store_Qty_Sold ASC

Store_Name Store_City Store_State Store_Qty_Sold Pct_State_Qty_Sold

Fricative Bookshop Fremont CA 60 21.82

News & Brews Los Gatos CA 90 32.73

Barnum's Tustin CA 125 45.45

Bookbeat Portland OR 80 100.00

Eric the Read Books Seattle WA 8 5.80

Doc-U-Mat: Quality Laundry and Books Remulade WA 130 94.20

.

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

SELECT a.au_fname, a.au_lname

FROM authors a

WHERE

a.au_id not in

(

SELECT DISTINCT ta.au_id

FROM titleauthor ta, titles t, sales s

WHERE ta.title_id = t.title_id AND

t.title_id = s.title_id

)

au_fname au_lname

Meander Smith

Morningstar Greene

Dirk Stringer

Heather McBadden

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)

SELECT

st.stor_name AS Store_Name,

s.ord_num AS Order_Number,

a.au_fname AS Author_First_Name,

a.au_lname AS Author_Last_Name,

t.title AS Title,

sum(s.qty) AS Qty_Sold,

CONVERT (NUMERIC(5,2), ((sum(s.qty)) * 1.00/max(AQS.Author_Qty_Sold)) * 100.00) AS Pct_Author_Qty_Sold,

CONVERT (NUMERIC(5,2), ((sum(s.qty)) * 1.00/max(OQS.Order_Qty_Sold)) * 100.00) AS Pct_Order_Qty_Sold,

CONVERT (NUMERIC(5,2), ((sum(s.qty)) * 1.00/max(SQS.Store_Qty_Sold)) * 100.00) AS Pct_Store_Qty_Sold

FROM

sales s, stores st, titles t, titleauthor ta, authors a,

(

SELECT ta.au_id AS au_id, sum(s.qty) AS Author_Qty_Sold

FROM sales s, titles t, titleauthor ta

WHERE s.title_id = t.title_id AND

t.title_id = ta.title_id

GROUP BY ta.au_id

) AQS,

(

SELECT s.ord_num AS ord_num, sum(s.qty) AS Order_Qty_Sold

FROM sales s

GROUP BY s.ord_num

) OQS,

(

SELECT s.stor_id AS stor_id, sum(s.qty) AS Store_Qty_Sold

FROM sales s

GROUP BY s.stor_id

) SQS

WHERE

s.stor_id = st.stor_id

AND

s.title_id = t.title_id

AND

t.title_id = ta.title_id

AND

ta.au_id = a.au_id

AND

ta.au_id = AQS.au_id

AND

s.ord_num = OQS.ord_num

AND

s.stor_id = SQS.stor_id

GROUP BY

st.stor_name,

s.ord_num,

a.au_fname,

a.au_lname,

t.title

ORDER BY

Store_Name,

Order_Number,

Author_First_Name,

Author_Last_Name,

Title,

Qty_Sold

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
Barnum's	A2976	Ann	Dull	Secrets of Silicon Valley	50	100	100	40
Barnum's	A2976	Sheryl	Hunter	Secrets of Silicon Valley	50	100	100	40
Barnum's	QA7442.3	Albert	Ringer	Is Anger the Enemy?	75	56.39	100	60
Barnum's	QA7442.3	Anne	Ringer	Is Anger the Enemy?	75	50.68	100	60
Bookbeat	423LL922	Anne	Ringer	The Gourmet Microwave	15	10.14	100	18.75
Bookbeat	423LL922	Michel	DeFrance	The Gourmet Microwave	15	37.5	100	18.75
Bookbeat	423LL930	Abraham	Bennet	The Busy Executive's Database Guide	10	66.67	100	12.5
Bookbeat	423LL930	Marjorie	Green	The Busy Executive's Database Guide	10	20	100	12.5
Bookbeat	P723	Michael	O'Leary	Cooking with Computers: Surreptitious Balance Sheets	25	55.56	100	31.25
Bookbeat	P723	Stearns	MacFeather	Cooking with Computers: Surreptitious Balance Sheets	25	55.56	100	31.25
Bookbeat	QA879.1	Cheryl	Carson	But Is It User Friendly?	30	100	100	37.5
Doc-U-Mat: Quality Laundry and Books	N914008	Albert	Ringer	Is Anger the Enemy?	20	15.04	100	15.38
Doc-U-Mat: Quality Laundry and Books	N914008	Anne	Ringer	Is Anger the Enemy?	20	13.51	100	15.38
Doc-U-Mat: Quality Laundry and Books	N914014	Anne	Ringer	The Gourmet Microwave	25	16.89	100	19.23
Doc-U-Mat: Quality Laundry and Books	N914014	Michel	DeFrance	The Gourmet Microwave	25	62.5	100	19.23
Doc-U-Mat: Quality Laundry and Books	P3087a	Albert	Ringer	Life Without Fear	25	18.8	29.41	19.23
Doc-U-Mat: Quality Laundry and Books	P3087a	Charlene	Locksley	Emotional Security: A New Algorithm	25	100	29.41	19.23
Doc-U-Mat: Quality Laundry and Books	P3087a	Johnson	White	Prolonged Data Deprivation: Four Case Studies	15	100	17.65	11.54
Doc-U-Mat: Quality Laundry and Books	P3087a	Livia	Karsen	Computer Phobic AND Non-Phobic Individuals: Behavior Variations	20	100	23.53	15.38
Doc-U-Mat: Quality Laundry and Books	P3087a	Stearns	MacFeather	Computer Phobic AND Non-Phobic Individuals: Behavior Variations	20	44.44	23.53	15.38
Eric the Read Books	6871	Abraham	Bennet	The Busy Executive's Database Guide	5	33.33	100	62.5
Eric the Read Books	6871	Marjorie	Green	The Busy Executive's Database Guide	5	10	100	62.5
Eric the Read Books	722a	Albert	Ringer	Is Anger the Enemy?	3	2.26	100	37.5
Eric the Read Books	722a	Anne	Ringer	Is Anger the Enemy?	3	2.03	100	37.5
Fricative Bookshop	QQ2299	Dean	Straight	Straight Talk About Computers	15	100	100	25
Fricative Bookshop	TQ456	Innes	del Castillo	Silicon Valley Gastronomic Treats	10	100	100	16.67
Fricative Bookshop	X999	Marjorie	Green	You Can Combat Computer Stress!	35	70	100	58.33
News & Brews	D4482	Albert	Ringer	Is Anger the Enemy?	10	7.52	100	11.11
News & Brews	D4482	Anne	Ringer	Is Anger the Enemy?	10	6.76	100	11.11
News & Brews	P2121	Akiko	Yokomoto	Sushi, Anyone?	20	100	25	22.22
News & Brews	P2121	Burt	Gringlesby	Sushi, Anyone?	20	100	25	22.22
News & Brews	P2121	Michael	O'Leary	Sushi, Anyone?	20	44.44	25	22.22
News & Brews	P2121	Reginald	Blotchet-Halls	Fifty Years in Buckingham Palace Kitchens	20	100	25	22.22
News & Brews	P2121	Sylvia	Panteley	Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean	40	100	50	44.44

Last Updated: 3/29/2004 3:19:03 PM