95 – 703: Database Management SQL #3 (due on April 22, your class time)
Turn in a well formatted printout of your SQL statements as they were executed in the SQL*Plus environment and the results of running each statement (provide echo and feedback). Set the LINESIZE and PAGESIZE values optimally to ensure that there are no wrapping lines or repeated column headers in the results (refer to the “Formatting SQL Output” document available on the class website). You must copy the actual text into your MS Word document (do not copy a “screen shots” of your queries or results). Identify each answer clearly.
Use the script GA_script.txt (provided for SQL assignment #2) to create the database the answer the following questions. Each question should be answered by a single query unless requested otherwise. If a query generates NULL values in any column, replace the NULL value with an appropriate information/value. Any string comparison requires using LOWER or UPPER function to prevent case mismatch.
1.
2. 3.
4.
5.
6.
7.
For each item, list the item number, the item name, the number of orders on which they appeared and the average revenue (revenue per order) generated by the sale of the item on those orders. Include all items in the output. Replace each NULL with a zero and format the revenue as currency. Also, rank each item based on the total revenue it generated. Highest revenue should get rank #1.
List the ID and name of most popular items. List only the top three items. Make sure you address “ties” in your query.
How many items were sold to each city? On the list include the name of the city, item number, the item name, and the number of items sold to the city. Also include a grand total of the number of items sold.
Based on the previous query, list item-city pair with highest demand. That is, for each item, give the city with the highest demand for the item. Include the item number, item name, city, and the total quantity ordered.
We want to award the most productive sale representatives. Find out who is (are) the best sales representative(s) in terms of dollar value of sales, quantity of items sold, and number of orders processed. Include in the output the representative’s ID, name, and the total sales achieved (in dollar value, quantity of items sold, or number of orders received). The output should have three components (set of rows) each clearly identified (i.e., create a column that will identify what measure was used in each criterion).
For each customer, list the customer number, customer name, the number of orders he or she placed, the number of items (not the quantity) ordered on all of the orders, and the overall total purchase amount. Your list should include all customers, regardless of whether they placed any order or not and should be ordered by the total purchase amount. Replace null values with zeros in the numerical columns.
Who has ordered an item with “highest quantity” on any individual order? List the customer ID and customer name. If there is more than one customer then order the customers by credit limit from highest to lowest (show the credit limit in the result).
8. List the customer ID and customer name of the customer served by either rep number 20 or rep number 30, and has name that included two same consecutive letters (e.g., ‘ee’) or has an apostrophe in the name.
9. Who is the most valuable customer? Rank the customers based on the following criteria: 1) the number of orders a customer placed and 2) the average order purchase amount. Create two separate ranks (each for each criterion) List only those customers who got 1 or 2 on either of the two ranks. Include the customer ID and name, the number of orders the customer placed, the average order purchase, and their two ranks.
10. Use Regular Expression functions to list the item number and the item name of those items that have their number starting with either K or D and ending with either 2 or 3 (e.g., item KL62 should be on the list but item KV29 shouldn’t).