Lab 1

# 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