Homework 3 SQL Solution

Overview:

    1. Start a postgresql instance and run the script northwind.sql.

    2. Once the data is loaded, write the queries to answer the questions below.

    3. Turn in a file with the QUERY and the RESULT for each question. We have included an example .txt file with a format for your submission; feel free to use this or your own version, but remember to include both query and result!

Step 1:

Start your postgresql instance:

sudo​ -u postgres psql

Run the northwind.sql script to import data:

\i <path_to_northwind.sql>;

e.g. ​\i Downloads/northwind.sql;

Step 2-3:

You must create and execute queries against the northwinds database to fulfill the requirements of this assignments. For each question, you must submit your query AND the result of the query. Each question has an associated number of rows that you should expect in resulting query.

    1. Create an alphabetical listing (last name, first name) of all ​employees​not living the in the UK who have been employed by Northwinds for at least 5 years as of the due date of this assignment (2019-04-14). (5 rows)
    2. Prepare a reorder list for ​products​that currently have at least one unit in stock but are (strictly) below their reorder level. Display the product ID, name, quantity in stock, and unit price for each matching product. (17 rows)

    3. What is the name and unit price of the least expensive ​product​sold by Northwinds? Use a subquery. (1 row)

    4. Create a list of the products in stock which have an inventory value (number of units in stock * unit price) under $200. Display the product ID, product name, and “total inventory value” in ascending total inventory value order (lowest to highest). (15 rows)

    5. List the country and a count of ​orders ​for all orders that shipped from that country for all countries other than the USA during August 1996. (10 rows)

    6. List the customer ID of the customers who have less than 4 orders in descending alphabetical order (Z-A). (10 rows)

    7. Create a supplier inventory report that shows the total value of each suppliers inventory in stock (total value = sum over all units of (units in stock * unit price)). List only those suppliers who supply more than 3 different items. (4 rows)

    8. Create a supplier price list showing the supplier company name, product name, and unit price for all products from suppliers located in France. Sort the list on unit price in descending order (highest to lowest). ​hint: must use both the products table and the suppliers table ​(5 rows)
    9. Create an employee order list showing the last name, first name, title, extension, and number of orders for each employee who has less than 75 orders. ​Hint: must use both the employees table and the orders table ​(5 rows)

    10. Create a NEW table named top_items with the following items: item_id (integer), item_code (integer), item_name (varchar(40)), inventory_date (DATE), supplier_id (integer), item_quantity (integer), and item_price (decimal (9,2)). None of these columns can be null. Include a PRIMARY KEY constraint on item_id. (No answer set needed, just the create table command).

    11. Populate the new table top_items with items from products for those products whose inventory value is greater than $2500. The corresponding columns are the following:

        a. product_id -> item_id

        b. category_id -> item_code

        c. product_name -> item_name

        d. <today’s date> -> inventory_date

        e. units_in_stock -> item_quantity

        f. unit_price -> item_price

        g. supplier_id -> supplier_id

Hint: this entails an INSERT with a SELECT query as the insert value. (No answer set needed, just the populate command) (9 rows inserted)

    12. Delete the rows in top_items for items with item_quantity less than 50. (No answer set deleted, just the delete command) (4 rows deleted)

    13. Add a new column to the top_items table called inventory_value (decimal (9,2)), with a default value of 0. (No answer set needed, just the column add command).
    14. Update the top_items table, setting the inventory_value column equal to item_price * item_quantity. (No answer set needed, just the update command)

    15. Drop the top_items table. (No answer set table, just the drop command)