SDEV-Homework 3 SQL Solved

Step 1: Initializing your database
Download the files needed for this homework from here
Start your postgres instance
sudo -u postgres psql
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 


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
 
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
4 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)</today’s
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
 
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
 
17
Find all products that are cost less than the average unit price
52 rows
 
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
 
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
 
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
 
21
The company ‘Leka Trading’ was blacklisted by the regulators. List out all the product which were being supplied from this supplier.
3 rows
 
Bonus Question 


Serial No
Query
Number of rows returned
 
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
 
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