Assignment 3

Submission: Submit your answers to Canvas. Please post only one plain text (.txt) document. Be sure to put your name in the text file.

On stores7

1. Show all customers (first and last names, city, name of state, and phone number in MD5 encryption) for all customers who live in Florida, followed by those in Arizona, and finally those in Oklahoma. (Hint: ORDER BY FIELD, MD5() function)
2. For each customer, add up the total amount spent purchasing items made by the company 'Karsten'. Include those customers who have bought nothing (with $0 next to their name). Do not worry about the shipping charge. Give the customer's number, first and last name, city, state, and amount spent (The order need not have been shipped yet - just the order made). Order all the results by last name, then by first name.
3. For each customer, add up the total amount spent by that customer. Include the shipping charge. You do not need to include those who have ordered nothing. (Be careful not to add up the shipping charge multiple times.)
4. List all items from the stock table that have never been ordered.
5. Find the order number, customer number, and shipping weight of the order(s) whose shipping weight is closest to the average shipping weight.
6. (CIS 551 only) Consider the total spent by each customer (exclusive of shipping charges). Find those customers whose total is within one standard deviation of the average amount.

On company

7. Write a query that makes the following consistency check: Ideally if a project belongs to a department (project.dnum), then the location of that project (project.plocation) should be in the list of that department's locations (dept_locations.dlocation). Your query should list the department name, project name, and project location for any case where this is not true. Note that in a consistent data set, this will return zero rows.
8. Determine which project has the maximum number of allocated hours. You should include ties, if any. (Try not to use LIMIT, since you don't know whether there are ties.)
9. List the names of all employees who work on any of the three projects with the most allocated hours (the sum of the hours from the works_on table). Also give the name of those projects the person works on. Order the results by last name of the employees. (Use of LIMIT is ok.)

On sakila

10. For each film whose title starts with the letter 'I', list the amount of money spent on each film (the sum of the amount field from the payment table). Include the zeroes.