HW-3 SQL Solution

This homework will give you hands-on practice in working with SQL. You will first create a database and insert sample data using scripts provided. The database will then be used to answer the query problems given below.

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 need to download the “HW_3_SQL_NorthWinds.sql” script file from Canvas, and execute it. It will create sample database and tables, and then load the tables with data. The script runs fine as-is without any modification. The script begins with a command to DROP the table before creating it. This allows you to run the script over and over as 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 for this database in the Canvas called “HW_3_ SQL_Northwinds_ERD.pdf”. You should download and print ERD and keep it handy while doing the homework. It will be helpful to have tables, column names and their relationship in front of you when writing SQL queries.








CSCI 3287: Design and Analysis of Data Systems    Page 1
HW-3 SQL

The HW_3_SQL_NorthWinds.sql contains 9 scripts for creating each of 8 tables, and one to verify that the data count.


    • hwSuppliers
    • hwShippers

    • hwCustomers

    • hwEmployees
    • hwProducts

    • hwCategories
    • hwOrders

    • hwOrderdetails


After running scripts, run the ‘Verify’ script, as below. You should see the following 8 tables and row counts for each.

Note: Please let instructor know if your tables/counts are different.

-- Verify row counts

USE HW_3_SQL_NorthWinds;

SELECT table_name, table_rows

FROM information_schema.tables

WHERE TABLE_NAME LIKE 'HW%';

























CSCI 3287: Design and Analysis of Data Systems    Page 2
HW-3 SQL

Query Problems



    • You must submit BOTH your SQL and your ANSWER SET in pdf.

    • 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 = Total Revenue so your result column should display as Total Revenue.

    • All Questions are equally weighted.



Questions:


    1. Show a list the Company Name and Country for all Suppliers located in Japan or Germany.

    2. Show a list of Products' 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 Customers' Company Name, Contact Title and City 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 Products (i.e. lowest unit price)? You MUST use a Sub Query.

    6. Show a list of Ship Country and their Order Counts that is shipped outside the USA between May 4th and 5th 2015.

    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 inventory level is at or below the reorder level.


CSCI 3287: Design and Analysis of Data Systems    Page 3
HW-3 SQL


    10. Show a Product Name, Unit Price 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 country and their customer's count that is greater than 8.

    13. Show a list of Orders' Ship Country, Ship City and their Order count for Ship Country 'Austria' and 'Argentina'.

    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, 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 as 'pkg' or 'pkgs' or 'jars' for a supplier’s country from Japan.

    20. Show a list of customer's company name, their Order’s ship name and total value of all their orders (rounded to 2 decimal places) for customer's from Mexico.

(value of order = (UnitPrice multiplied by Quantity) less discount).

    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.



CSCI 3287: Design and Analysis of Data Systems    Page 4
HW-3 SQL


    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.




        ◦ The End.  








































CSCI 3287: Design and Analysis of Data Systems    Page 5