Use This Page As the Covering Page for Submitting Your Work, Filling out Your Student Number

CSE3421 – Summer 2007

Assignment 2

Due: July 12 (Thursday) 2007, 10:30pm

If you submit your assignment late but before July 14 (Saturday) 10:30pm, there will be a 20% deduction of your score for penalty. This assignment will not be accepted after July 14.

______

Use this page as the covering page for submitting your work, filling out your student number etc as follows:

Student Number ______

Family Name ______

Given Name ______

CS account # ______

This assignment involves developing SQL statements for the Grocery case. The DDL statements for creating the required tables (subset of case) and the insert statements for adding test data are provided on the class website. You should figure out how the tables are related before you work on this assignment. Please feel free to add your own test data.

Develop one SQL statement for each of the following queries. You must use the given table definitions. You are not allowed to modify the table definitions, nor create your own views.

1. Identify the customers who ordered items in the past which are now suspended. Eliminate duplicate lines in your output.

Show: customer name, item # of such items ordered by the customer, item name

Order by: customer name, item #

2. Identify the customers who have orders created in year 2006.

Show: customer name, order# of the orders created in 2006, number of items in such orders

Order by: customer name, order#

3. Identify the customers who have orders with more than $50 value.

Show: customer name, order# of such orders, value of the order

Order by: customer name, order#

4. Identify the drivers who have never delivered any goods for orders with more than $50 value.

Show: driver name

Order by: driver name

Identify the items that are currently available, but never ordered through the York Lane store (store code is ‘YKL’)

Show: item#, item name, and price of such items

Order by: item#

6. For each customer, determine the number of orders created in year 2006. If a customer has not created any order in year 2006, show 0 for that customer. Do this query without using outer join.

Show: customer #, customer name, # of orders created in 2006 (show 0 if none)

Order by: customer #

7. Do question 6 again, using outer join.

8. Identify the customers associated with the York Lane store (store code is ‘YKL’) who have been delivered grocery by all the drivers in that store.

Show: customer # and customer name

Order by customer #

Identify the customers who have more than 1 order created in 2006, each of which covers more than 2 items.

Show: customer #, customer name, order# of such orders, number of items in each of these orders

Order by: customer #, order#.

10. Determine the total of items that are currently offered (i.e. offered = ‘yes’) and that for items not currently offered (i.e. offered is ‘no’ or ‘suspended).

Sample Output: Not offered 9999

Offered 9999

Hint: You can put a constant in the output of SELECT. For example, the statement

Select ‘ABC’, E.name,..

From employee E

….

will display the constant ‘ABC’ in the 1st field in each row of the output

11. For each store, determine the total dollar value of each grocery item sold through that store and the percentage of this value of all the sales through that store.

Show: Store name, item #, item name, sales value of the item, % of the item’s sales

Order by: store name, item #

12. For each customer, determine the total dollar amount of all his/her orders created in 2006, and the number of different drivers who made deliveries to him/her for the 2006 orders. We want to count only those drivers with at most 2 driving records.

Show: customer #, customer name, total $ value of 2006 orders, driver count

Order by: Customer #

13. For each customer, determine which driver delivered grocery to him/her the most number of times. Also determine the total number of orders for each customer.

Show: customer #, name of the driver, total number of orders that this customer has ordered

(If there is more than 1 such driver for a given customer, show each such driver on a different line.)

Order by: Customer #

14. Determine the average number of days between orders for each customer. (If a customer has ordered less than twice, don’t show him/her.) For example: Assume customer 88 submitted orders on Nov 1 2005, Nov 1, 2005, Nov 13 2005 and Dec 13 2005. The number of days between the 1st and 2nd is therefore 0; between 2nd and 3rd is 12; and between 3rd and 4th is 30. The average is therefore (0+12+30)/3 = 14. The output for this customer should be: 88 14

Order by: Customer #

SQL features

You may find the following features useful. In the examples below, assume that project_start_date and project_end_date are columns of date type and have values June 1, 2002 and July 31, 2002 respectively.

Difference between 2 dates:

Example: SELECT days(project_end_date) – days(project_start_date) AS duration…

will display 60 (number of days between the 2 dates). Can use this in WHERE clause etc as well.

YEAR function:

Example, YEAR(project_end_date) will return 2002 (the year portion of the date)

Can use this in WHERE clause, SELECT etc.

CURRENT DATE:

Returns today's date

Example. …WHERE project_end_date = CURRENT DATE….

The WHERE clause result is true if you run this query on July 31, 2002, otherwise false.

ORDER BY

For example,

Select Department, employee#, salary,…

From..

Where…

Order By Employee# DESC

Result: The output will be sequenced by employee# in descending order

If you say ".. Order By Employee# ASC", it will be in ascending order.

IS NULL:

For example, …WHERE phone IS NULL ...

Result is true if phone contains no value.

IS NOT NULL works in a similar way.

CAST

For example,

Select ….CAST(sales AS DEC(5,2))…

The format of the output of sales will be 99.99, i.e. total 5 positions, including the decimal point and 2 fractional digits.

Temporary tables (But not views). This feature will be further discussed in class.

WITH

X (. . .) AS

(. . .),

Y (. . .) AS

(. . .),

Z (. . .)

Select . . .

If you like to learn more about these, please check the SQL Reference.

Hand in the following

1. Hand in a hard copy of the recorded session(s) for executing your SQL, showing your SQL statements and the execution output. Drop off the hard copy in the CSE3421 drop box in the Computer Science Building. Use standard size (8.5” X 11”) paper and have them stapled together. DO NOT use binders, folders, paper clips etc. Just pure standard size paper.

2. Send an electronic copy of your SQL statements to the class folder in Prism.

There should be a plain text file for each SQL statement (therefore a total of 14 files). Do not include “connect to c3421a…” in those files. The file names should be: Q1, Q2…Q14. Store the 14 files in a directory called SQL3421. Send your files by executing the submit command on Prism as follows:

% submit 3421 a2 SQL3421

You are required to hand in both the hard copy and submit the electronic files by the due date/time.

Note that if you submit the same file more than once, the latest copy will be stored in the system, and the date/time stamp will correspond to the latest copy. Therefore, do not resubmit after the due date; otherwise it will be considered late.

FAQ

1. Do I have to include the “connect to …”, “connect reset;”, “terminate:” statements in each of the files of that I submit?

Answer: No. Each of the files you submit should contain one SELECT statement, i.e. your solution to the query. You can test the queries in your files on Prism DB2 as follows:

% db2 connect to …

% db2 –tvf Q1

% db2 –tvf Q2

% db2 –tvf Q3

etc

% db2 connect reset

% db2 terminate

where % is the Prism prompt sign.

2. I developed my SQL statements on a PC; and they worked fine. But after I transferred the statements to Prism, they don’t work. Why?

Answer: Probably due to the CRLF (carriage return and line feed) characters in Windows; because UNIX/Linux use CR only. Try this: Execute the following on Prism: % dos2unix <your file>

to make file UNIX/Linux compatible

3. I submitted part of my assignment on time, and part late. What will happen to my mark?

Answer: Your whole assignment is considered late; hence subject to penalty as describe on page 1.

4. I submitted my electronic copy on time, but late in submitting hardcopy. What will happen to my mark?

Answer: Your whole assignment is considered late; hence subject to penalty as describe on page 1.

5. I submitted my hard copy on time, but late in submitting electronic copy. What will happen to my mark?

Answer: Your whole assignment is considered late; hence subject to penalty as describe on page 1.

Marking Scheme

Each query is worth 1 mark. The TA will run the query you submitted on Prism DB2:

If it produces the correct result, you will get 1 mark; otherwise 0

Please make sure that the SQL statement in each of the files you submit run successfully on Prism DB2. The TA will use a set of test data that is different from yours; hence don’t cook the output.

Expected Results

With the test data provided on the class web, the expected results are (exact spacing and exact column heading not important):

Dave B3 beef stew can
Dave T2 toothpaste
Dave X2 oil
Jessica B3 beef stew can
Jessica T2 toothpaste
Jessica X2 oil

Jessica X2 oil

Dave 1 1
Dave 2 3
Dave 3 3
Dave 4 1
Dave 5 1

Henry 11 3

Henry 12 1
Henry 12 1
Jessica 6 5
Jessica 7 4
Jessica 8 1

Dave 2 63.00
Henry 11 82.00

Debbie
Erica
Joe

B4 butter 5.00

10 Dave 5
20 Jessica 3
30 Sarah 0
40 Henry 2 1
50 Hamid 0
60 Susan 0
70 Sharon 0

10 Dave

10 Dave 2 3
10 Dave 3 3
20 Jessica 6 5
20 Jessica 7 4

10. (sequence of the 2 lines not important)

Not offered 5
Offered 5

11.

Elm Street C1 chicken thighs 15.00 16.85
Elm Street D2 detergent 60.00 67.41
Elm Street PC1 potato chips 14.00 15.73
Yorklane B3 beef stew can 22.00 13.09
Yorklane B68 bread 7.50 4.46
Yorklane C1 chicken thighs 9.00 5.35
Yorklane D2 detergent 9.00 5.35
Yorklane M12A milk 60.00 35.71
Yorklane PC1 potato chips 21.00 12.50
Yorklane T2 toothpaste 7.50 4.46
Yorklane X2 oil 32.00 19.04
12.

10 Dave 98.00 3
20 Jessica 70.00 2
40 Henry 89.00 7.00 1

13.

10 Joe 5

20 Erica 3

40 Suny 2

14.

10 16.75 19.25

20 1.00

40 171.00 153.00