The University of Texas

CS 327e

Fall 2004

Lab 4 and 5

Due: 2004-11-05 Friday 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 1 correct SQL statement per query. Clearly state any assumptions you make.

NOTE: Refer to last semester’s Lab4 for some practice queries.

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

EMPLOYEE

AUTHORS

JOBS

DISCOUNTS

TITLEAUTHOR

STORES

SALES

PUBLISHERS

ROYSCHED

TITLES

NOTE: Blanks in the Excel spreadsheet imply null data. (e.g. the publisher Lucerne Publishing state is NULL).

NOTE: In all of the queries below, when asked for

a) Author, give their first and last name.

b) Title, give only their description (titles.title).

c) Year, give the 4 digit year 19xx.

d) Publisher, give their name.

e) Store, give their name.

Query1) Give all authors who live on Avenues, and don’t have a “0” in their phone number. (10 points)

Query2) Give the year, along with the number of employees hired in that year. (You can assume that all hiring years have the ‘19xx’ format.) (10 points)

Query3) For each state that an author resides in, give me the count of contract and non-contract authors. (10 points)

{HINT: Use CASE statements, along with a special kind of JOIN}

Query4) Give me all USA Publishers that have “Pub” in their names. (10 points)

Query5) For each store where a sale occurred, give me

· The total quantity of titles sold in that store

· The average quantity of titles (show only 2 decimal places) sold in that store

· The total quantity of titles sold across all stores

· The average quantity of titles (show only 2 decimal places) sold across all stores

The resultant data should be sorted by “The total quantity of titles sold in that store

” in descending order.

(20 points)

{HINT: Use the SQL Server CONVERT/CAST function to get average in 2 decimal places}

Query6) Give the names of authors, and their titles, along with their quantities sold (must be at least 32 quantities of this title), who have not published a single psychology title. (20 points)

Query7) Give the titles, along with their authors and publishers, that didn’t sell. (20 points)

{HINT: Not all titles have authors and vice versa}

Query8) Give the authors, the state they live in, their title, the store in which a sale of this title occurred, the store state, for all out-of-state sales. An out-of-state sale is a sale occurring in a store whose state is not the same as the author’s state. (20 points)

Query9) For each state in which an author lives in, give the following measures from stores that are in the same state as the author’s.

· The total number of sales that occurred

· The total number of unique titles sold

· The total quantity of titles sold

· The Percent to total of quantities sold. This is calculated as

(A/B) * 100.00

Where

A = The total quantity of titles sold for this state.

B = The total quantity of titles sold across all states.

· The average quantity of titles sold

NOTE: If there is a state in which an author resides, but no stores in that state had any sales, return “0” for all of the measures.

NOTE: Sort the data by “The Percent to Total” measure (descending), followed by state (ascending). (20 points)

Query10) Give the name(s) of publishers who sold the most quantity of psychology titles. (10 points)

Query11) Give the name(s) of above-average authors. Above Average authors are authors who sold more than the average sales across all authors. Average sales across all authors is defined as the total sales across all authors, divided by the total number of authors.(10 points)

Query12) Give the name(s) of above-average selling authors. Above Average authors are authors who sold more than the average sales across all selling authors. Average sales across all selling authors is defined as the total sales across all authors, divided by the total number of authors who had at least 1 sale. (10 points)

Query13) Give the names of authors whose titles have a “?”, or who live in California. (10 points)

Query14) For each (selling author, title), list all the other distinct selling authors and their titles who have sold at least 1 title of the same title type. Note that if a selling author does not have any other authors selling the same title type, then don’t list that author. Columns returned will include

· AuthorFirstName

· AuthorLastName

· TitleSold

· OtherAuthorFirstName

· OtherAuthorLastName

· OtherTitleSold

(20 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: 11/4/2004 3:20:39 PM