Starting from:
$30
Assignment 2: SQL
Assignment 2: SQL
In this assignment, you will practice working with SQL (Structured Query Language). You will
install MySQL, create a database, and populate it using some scripts. You will then query the
database to answer some questions about the datasets.
What To Do
Part 1: Installing MySQL
You will download and install MySQL. How you do this will depend on what type of OS your
computer is running. If you are having trouble installing MySQL on your system, you can always
spin up a VM with a different OS.
Step 1. Download MySQL Community Server 8.0. You can find the correct distribution for your
OS here.
Step 2. Install the MySQL Community Server 8.0 Developer version. The developer version
comes with MySQL Workbench, a handy GUI (graphic user interface) for managing and
running queries against a MySQL database.
Figure 1. MySQL Workbench interface
Note, that your installation package may require Python 3.7 to be installed on your system.
Make sure you install the Standalone MySQL Server NOT the InnoDB Cluster or other options.
Step 3. If you did not install MySQL Workbench you may install an alternative tool. There is
SQLYog. This is a great tool but only works on a Windows PC. It comes with a free 14-day trial.
You can download it here.
A great open source alternative is DBeaver. The community edition is free and it comes with
versions for many different OS builds and works fine with MySQL. You can download it here.
You are not required to use a GUI and may use the default mysqlsh command line interface.
Part 2: Creating and populating the database
Now, you will create the database and populate it.
Step 1. Download ‘sqlScripts.zip’ here. You will need the scripts in this folder to populate your
database.
Step 2. Make sure your instance of MySQL is running. Using your query editor (e.g., MySQL
Workbench), connect to the MySQL instance.
Step 3. Create the database Northwinds with the MySQL “create database
northwinds;” command. For this assignment, you can let MySQL use all the default options.
Now, tell MySQL to use this database by typing “use northwinds;” so it knows which
database to run your queries against. You now have an empty database.
Step 4. You will populate the database with some tables using the scripts provided in
‘sqlScript.zip’. The Northwinds database will consist of 8 tables. There are 9 scripts, one for
creating each table and one to verify that everything worked OK. The scripts are all named
xxxxxxx.txt. Here is a list of the tables:
● Suppliers
● Shippers
● Customers
● Employees
● Products
● Categories
● Orders
● Order_details
You must open up each script, copy and paste the SQL statements into your query editor, and
execute the script. First, create all 8 tables, then run the “verify” script.
Note, each script begins with a command to DROP the table before it creating it. This allows you
to run the script over and over as needed. The very first time you run this script, it will throw an
error when it tries to DROP the table because it does not exist yet. You can ignore this error.
When you create your tables, they may not immediately appear in your query editor. You may
have to refresh your editor for them to appear.
If you are using the command line, you can enter SHOW TABLES and MySQL will show you all
the tables in your database.
After running the verify script, you should see the following tables and row counts:
Figure 2. The tables and table rows in the Northwinds database as shown in MySQL Workbench
Part 3: Query problems
You will create and execute queries against the Northwinds database and answer some
questions about the dataset. You will be required to submit all of your queries and the answers
to the questions (one file with only the queries and one file with the answers or results of your
queries). You should copy and paste your queries and answers into a separate document or
use the builtin save features in MySQL Workbench.
Note, for each problem, the number of rows you should expect in your answer set is listed in
parenthesis at the end of the problem statement. Some queries will produce NO answer set.
Step 1. Save your queries in a file named “sqlQueries.sql” and change the permissions of the
file to make it executable. Save your answer set (results of your queries and your answers to
the questions) in a document (.txt, .docx, etc.) named “answers.*”.
Step 2. Create queries to answer the following problems/questions:
1. Create an alphabetical listing (LastName, FirstName) of all employees not living in the
USA who have been employed with Northwinds for at least 5 years as of today. (3)
2. Prepare a Reorder List for products currently in stock. Products in stock have at least
one unit in the inventory. Show ProductID, ProductName, UnitsInStock, and
UnitPrice for products whose inventory level is at or below the ReorderLevel. (17)
3. What is the name and unit price of the most expensive product sold by Northwinds? Use
a subquery. (1)
4. Create a list of the products in stock which have an inventory value (“inventory value” =
UnitsInStock * UnitPrice) over $2,000. Show the answer set columns as
ProductID, ProductName, and “Total Inventory Value” in order of descending
inventory value (highest to lowest). (13)
5. List the ShipCountry and a count of orders for all the orders that shipped outside the
USA during September 2013 in ascending country sequence. (9)
6. List the CustomerID and CompanyName of the customers who have more than 20
orders. (3)
7. Create a Supplier Inventory Report (by SupplierID) showing the total value of their
inventory in stock (“inventory value” = UnitsInStock * UnitPrice). List only those
suppliers from whom Northwinds receives more than 3 different items. Show
SupplierID and “Total Inventory Value”. (4)
8. Create a Supplier Price List showing the suppliers’ CompanyName, and products’
ProductName and UnitPrice for all products from suppliers located in the USA. Sort
the list in order from highest price to lowest price. (12)
9. Create an Employee Order List showing, in alphabetical order (by full name), the
LastName, FirstName, Title, Extension, and “Number of Orders” for each
employee who has more than 100 orders. (4)
10. Create an Orders Exception List showing the CustomerID and the CompanyName of
all customers who have no orders on file. (2)
11. Create an Out of Stock List showing the suppliers’ CompanyName, ContactName,
and the products’ CategoryName, CategoryDescription, ProductName, and
UnitsOnOrder for all products that are out of stock (UnitsInStock = 0). (5)
12. List the ProductName, SupplierName, suppliers’ Country and UnitsInStock for
all the products that come in a bottle or bottles. (11 or 12 depending on your
assumptions)
13. Create a new table named “nwtopitems” with the following columns: ItemID (integer),
ItemCode (integer), ItemName (varchar(40)), InventoryDate (DATE), SupplierID
(integer), ItemQuantity (integer), and ItemPrice (decimal(9,2)). None of these
columns can be NULL. Include a PRIMARY KEY constraint on ItemID. (No answer set
needed)
14. Populate the new table “nwtopitems” using these columns from the “nwproducts”
table ...
ProductID - ItemID
CategoryID - ItemCode
ProductName - ItemName
Today’s date - Inventory Date
UnitsInStock - ItemQuantity
UnitPrice - ItemPrice
SupplierID - SupplierID
… only for products that have an inventory value greater than $2,500 (“inventory value”
= UnitsInStock * UnitPrice). (No answer set needed)
15. Delete the rows in “nwtopitems” for suppliers from Canada. (2 rows deleted. No
answer set needed)
16. Add a new column to the “nwtopitems” table called InventoryValue (decimal(9,2))
after the inventory date. (No answer set needed)
17. Update the “nwtopitems” table, setting the InventoryValue column equal to the
ItemPrice multiplied by the ItemQuantity. (No answer set needed)
18. Drop the “nwtopitems” table. (No answer set needed)
What To Turn In
You will submit a single ZIP file containing your “sqlQueries.sql” and your “answers” files on
Canvas.
In this assignment, you will practice working with SQL (Structured Query Language). You will
install MySQL, create a database, and populate it using some scripts. You will then query the
database to answer some questions about the datasets.
What To Do
Part 1: Installing MySQL
You will download and install MySQL. How you do this will depend on what type of OS your
computer is running. If you are having trouble installing MySQL on your system, you can always
spin up a VM with a different OS.
Step 1. Download MySQL Community Server 8.0. You can find the correct distribution for your
OS here.
Step 2. Install the MySQL Community Server 8.0 Developer version. The developer version
comes with MySQL Workbench, a handy GUI (graphic user interface) for managing and
running queries against a MySQL database.
Figure 1. MySQL Workbench interface
Note, that your installation package may require Python 3.7 to be installed on your system.
Make sure you install the Standalone MySQL Server NOT the InnoDB Cluster or other options.
Step 3. If you did not install MySQL Workbench you may install an alternative tool. There is
SQLYog. This is a great tool but only works on a Windows PC. It comes with a free 14-day trial.
You can download it here.
A great open source alternative is DBeaver. The community edition is free and it comes with
versions for many different OS builds and works fine with MySQL. You can download it here.
You are not required to use a GUI and may use the default mysqlsh command line interface.
Part 2: Creating and populating the database
Now, you will create the database and populate it.
Step 1. Download ‘sqlScripts.zip’ here. You will need the scripts in this folder to populate your
database.
Step 2. Make sure your instance of MySQL is running. Using your query editor (e.g., MySQL
Workbench), connect to the MySQL instance.
Step 3. Create the database Northwinds with the MySQL “create database
northwinds;” command. For this assignment, you can let MySQL use all the default options.
Now, tell MySQL to use this database by typing “use northwinds;” so it knows which
database to run your queries against. You now have an empty database.
Step 4. You will populate the database with some tables using the scripts provided in
‘sqlScript.zip’. The Northwinds database will consist of 8 tables. There are 9 scripts, one for
creating each table and one to verify that everything worked OK. The scripts are all named
xxxxxxx.txt. Here is a list of the tables:
● Suppliers
● Shippers
● Customers
● Employees
● Products
● Categories
● Orders
● Order_details
You must open up each script, copy and paste the SQL statements into your query editor, and
execute the script. First, create all 8 tables, then run the “verify” script.
Note, each script begins with a command to DROP the table before it creating it. This allows you
to run the script over and over as needed. The very first time you run this script, it will throw an
error when it tries to DROP the table because it does not exist yet. You can ignore this error.
When you create your tables, they may not immediately appear in your query editor. You may
have to refresh your editor for them to appear.
If you are using the command line, you can enter SHOW TABLES and MySQL will show you all
the tables in your database.
After running the verify script, you should see the following tables and row counts:
Figure 2. The tables and table rows in the Northwinds database as shown in MySQL Workbench
Part 3: Query problems
You will create and execute queries against the Northwinds database and answer some
questions about the dataset. You will be required to submit all of your queries and the answers
to the questions (one file with only the queries and one file with the answers or results of your
queries). You should copy and paste your queries and answers into a separate document or
use the builtin save features in MySQL Workbench.
Note, for each problem, the number of rows you should expect in your answer set is listed in
parenthesis at the end of the problem statement. Some queries will produce NO answer set.
Step 1. Save your queries in a file named “sqlQueries.sql” and change the permissions of the
file to make it executable. Save your answer set (results of your queries and your answers to
the questions) in a document (.txt, .docx, etc.) named “answers.*”.
Step 2. Create queries to answer the following problems/questions:
1. Create an alphabetical listing (LastName, FirstName) of all employees not living in the
USA who have been employed with Northwinds for at least 5 years as of today. (3)
2. Prepare a Reorder List for products currently in stock. Products in stock have at least
one unit in the inventory. Show ProductID, ProductName, UnitsInStock, and
UnitPrice for products whose inventory level is at or below the ReorderLevel. (17)
3. What is the name and unit price of the most expensive product sold by Northwinds? Use
a subquery. (1)
4. Create a list of the products in stock which have an inventory value (“inventory value” =
UnitsInStock * UnitPrice) over $2,000. Show the answer set columns as
ProductID, ProductName, and “Total Inventory Value” in order of descending
inventory value (highest to lowest). (13)
5. List the ShipCountry and a count of orders for all the orders that shipped outside the
USA during September 2013 in ascending country sequence. (9)
6. List the CustomerID and CompanyName of the customers who have more than 20
orders. (3)
7. Create a Supplier Inventory Report (by SupplierID) showing the total value of their
inventory in stock (“inventory value” = UnitsInStock * UnitPrice). List only those
suppliers from whom Northwinds receives more than 3 different items. Show
SupplierID and “Total Inventory Value”. (4)
8. Create a Supplier Price List showing the suppliers’ CompanyName, and products’
ProductName and UnitPrice for all products from suppliers located in the USA. Sort
the list in order from highest price to lowest price. (12)
9. Create an Employee Order List showing, in alphabetical order (by full name), the
LastName, FirstName, Title, Extension, and “Number of Orders” for each
employee who has more than 100 orders. (4)
10. Create an Orders Exception List showing the CustomerID and the CompanyName of
all customers who have no orders on file. (2)
11. Create an Out of Stock List showing the suppliers’ CompanyName, ContactName,
and the products’ CategoryName, CategoryDescription, ProductName, and
UnitsOnOrder for all products that are out of stock (UnitsInStock = 0). (5)
12. List the ProductName, SupplierName, suppliers’ Country and UnitsInStock for
all the products that come in a bottle or bottles. (11 or 12 depending on your
assumptions)
13. Create a new table named “nwtopitems” with the following columns: ItemID (integer),
ItemCode (integer), ItemName (varchar(40)), InventoryDate (DATE), SupplierID
(integer), ItemQuantity (integer), and ItemPrice (decimal(9,2)). None of these
columns can be NULL. Include a PRIMARY KEY constraint on ItemID. (No answer set
needed)
14. Populate the new table “nwtopitems” using these columns from the “nwproducts”
table ...
ProductID - ItemID
CategoryID - ItemCode
ProductName - ItemName
Today’s date - Inventory Date
UnitsInStock - ItemQuantity
UnitPrice - ItemPrice
SupplierID - SupplierID
… only for products that have an inventory value greater than $2,500 (“inventory value”
= UnitsInStock * UnitPrice). (No answer set needed)
15. Delete the rows in “nwtopitems” for suppliers from Canada. (2 rows deleted. No
answer set needed)
16. Add a new column to the “nwtopitems” table called InventoryValue (decimal(9,2))
after the inventory date. (No answer set needed)
17. Update the “nwtopitems” table, setting the InventoryValue column equal to the
ItemPrice multiplied by the ItemQuantity. (No answer set needed)
18. Drop the “nwtopitems” table. (No answer set needed)
What To Turn In
You will submit a single ZIP file containing your “sqlQueries.sql” and your “answers” files on
Canvas.
1 file (290.3KB)
