zip

SDEV Homework3-SQL Solved

35.00 $

Category:

Description

5/5 - (4 votes)

 

  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: Initializing your database

  1. Start your postgres instance 
    sudo -u postgres psql
  2. Run the northwind.sql script to import data 
    \i <path_to_northwind.sql>;

    For eg:

    \i Downloads/northwind.sql;

Part 2: Create and Execute queries (100 points)

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.

Serial No Query Number of rows returned points
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 4
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 4
3 What is the name and unit price of the least expensive product sold by Northwinds? Use a subquery. 1 row 4
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 4
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
6 List the customer ID of the customers who have less than 4 orders in descending alphabetical order (Z-A). 10 rows 4
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 4
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 6
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		 4 rows 6
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 6
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)</today’s>		 9 rows inserted 6
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 4
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 4
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 4
15 Drop the top_items table. No answer set table, just the drop command 4
16 Create a list of employees’ first and last names as well as the number of unique customers they have sold to called “clients”. Only include employees who have sold to at least 50 unique clients. Display results in descending order by number of clients. 5 rows 6
17 Find all products that are cost less than the average unit price 52 rows 4
18 You’re Jeff Bezos and your employees have access to free Prime subscription. You’ve heard rumors that your employees are letting their neighbors and relatives use Prime. Find count of all employees that’ve ordered products to a different address (not their home address) in their city. 1 row 6
19 Create a list of employees and the number of orders they have completed and the number of unique clients sold to during the calendar year of 1998. Your table should display each employee’s first and last name, the number of unique clients, and the number of orders as order_count. 9 rows 6
20 ‘Janet Leverling’ wants to know the count of all the orders which were getting shipped from ‘Sweden’ and took less than a week time to ship. 1 row 4
21 The company ‘Leka Trading’ was blacklisted by the regulators. List out all the product which were being supplied from this supplier. 3 rows 4

Bonus Question (10 points)

Serial No Query Number of rows returned points
1 Create a list of employee names, the number of orders they have completed, and the number of customers they have sold to called “clients”. Only include employees that have either (a) more than 50 clients or (b) more than 70 orders. Your table should display each employee’s first and last name, the number of unique clients, and the number of orders as order_count. 6 rows 6
2 Find the average price of products that are supplied by companies that are based in the United States. Specify the name of company alongside the average price. 4 rows 4

 

  • HW3-8h9j81.zip

Related products