HW-4 SQL

CSCI 3287: Design and Analysis of Data Systems Page 1
HW-4 SQL
Overview
• Assignment points: 15 points or 15% toward your final grade.
• Submission: submit SQL scripts and output (result set) in Canvas in pdf or word doc.
• Make sure to mark your answers with the question number.
• This is open book exam, and any kind of resource materials are allowed.
This homework will give you hands-on practice in working with SQL. You will be using MySQL
workbench to write SQL for this assignment, the tool downloaded during the class. If you haven’t
downloaded yet, you will need to download and install MySQL workbench on your computer.
Downloads are available at: https://dev.mysql.com/downloads/workbench/
Choose the download file that matches your computer’s OS and version. Installation files are also
available in Canvas.
Creating the Database
You first need to download the “HW-4 SQL.sql” script file from Canvas and execute it. It will create
a database with tables and then load data into tables. The script runs fine as-is without any
modification. The script begins with a command to DROP the table before creating it. This will
allow you to run scripts over and over, if needed.
Before executing scripts, make sure your MySQL instance is running and Safe Updates is
unchecked (Edit - SQL Editor - Uncheck Safe Updates).
There is an ERD HW-4 SQL ERD.pdf for this database in the Canvas. You should download and
keep it handy while doing the homework. It will be helpful to have table and column names, and
their relationship in front of you when writing SQL queries.
CSCI 3287: Design and Analysis of Data Systems Page 2
HW-4 SQL
After running HW-4 SQL.sql scripts, you should see HW_4_SQL database with the following
8 tables and row counts.
Note: Please let instructor know if your tables and row counts are different.
Notes:
• Submit your SQL scripts and output (result set) in word or PDF in Canvas.
• Display ONLY columns / attributes that are asked to show in questions.
• Make sure to use table / column aliases, where applicable.
• Use a meaningful derived column names as asked in question, e.g. SalesPrice * Quantity =
Revenue so your result column should display as Revenue.
• Make sure to mark your answers with the question number.
• All Questions are equally weighted.
CSCI 3287: Design and Analysis of Data Systems Page 3
HW-4 SQL
Write SQL scripts for below questions:
1. Show a list the Company Name and Country for all Suppliers located in Japan or Germany.
2. Show a list of Product Name, Quantity per Unit and Unit Price for products with a Unit
Price less than $7 but more than $ 4.
3. Show a list of Company Name, City and Country for Customers whose Country is USA and
City is Portland, OR Country is Canada and City is Vancouver.
4. Show a list the Contact Name and Contact Title for all Suppliers with a SupplierID from 5 to
8 (inclusive) and sort in descending order by ContactName.
5. Show a product name and unit price of the least expensive product (i.e., lowest unit price)?
You MUST use a Sub Query.
6. Display Ship Country and their Order Count for all Ship Country except USA for Shipped
Date between May 4th and 10th 2015 whose Order Count is greater than 3.
7. Show a list of all employees with their first name, last name and hiredate (formated to
mm/dd/yyyy) who are NOT living in the USA and have been employed for at least 5 years.
8. Show a list of Product Name and their 'Inventory Value' (Inventory Value = units in stock
multiplied by their unit price) for products whose 'Inventory Value' is over 3000 but less than
4000.
9. Show a list of Products' product Name, Unit in Stock and ReorderLevel level whose
Product Name starts with 'S' that are currently in stock (i.e., at least one Unit in Stock) and Unit in
Stock is at or below the reorder level.
10. Show a list of Product Name, Unit Price and Quantity Per Unit for all products, whose
Quantity Per Unit has/measure in 'box' that have been discontinued (i.e., discontinued = 1).
11. Show a list of Product Name and their TOTAL inventory value (inventory value =
UnitsInStock * UnitPrice) for Supplier's Country from Japan.
12. Show a list of customer's country and their count that is greater than 8.
13. Show a list of Orders' Ship Country, Ship City and their Order count for Ship Country
'Austria' or 'Argentina'.
CSCI 3287: Design and Analysis of Data Systems Page 4
HW-4 SQL
14. Show a list of Supplier's Company Name and Product's Product Name for supplier's
country from Spain.
15. What is the 'Average Unit Price' (rounded to two decimal places) of all the products whose
ProductName ends with 'T'?
16. Show a list of employee's full name (i.e., firstname, lastname, e.g., Harrison Ford), title
and their Order count for employees who has more than 120 orders.
17. Show a list customer's company Name and their country who has NO Orders on file
 (i.e., NULL Orders).
18. Show a list of Category Name and Product Name for all products that are currently out of
stock (i.e. UnitsInStock = 0).
19. Show a list of products' Product Name and Quantity Per Unit, which are measured in 'pkg'
or 'pkgs' or 'jars' for a supplier’s country from Japan.
20. Show a list of customer's company name, Order’s ship name and total value of all their
orders (rounded to 2 decimal places) for customers from Mexico. (value of order = (UnitPrice *
Quantity) less discount. Discount is given in % e.g., 0.10 means 10%).
21. Show a list of products' Product Name and suppliers' Region whose product name starts with
'L' and Region is NOT blank/empty.
22. Show a list of Order's Ship Country, Ship Name and Order Date (formatted as MonthName
and Year, e.g. March 2015) for all Orders from 'Versailles' Ship City whose Customer's record
doesn't exists in Customer table.
23. Show a list of products' Product Name and Units In Stock whose Product Name starts with 'F'
and Rank them based on UnitsInStock from highest to lowest (i.e., highest UnitsInStock rank = 1,
and so on). Display rank number as well.
24. Show a list of products' Product Name and Unit Price for ProductID from 1 to 5 (inclusive)
and Rank them based on UnitPrice from lowest to highest. Display rank number as well.
25. Show a list of employees' first name, last name, country and date of birth (formatted to
mm/dd/yyyy) who were born after 1984 and Rank them by date of birth (oldest employee rank
1st, and so on) for EACH country i.e., Rank number should reset/restart for EACH country. Display
rank number as well.
 The End.  