Homework # 2 -- SQL

Database Systems 
Overview

This homework will give you hands-on practice in working with SQL (Structured Query Language.) In this
homework you will create a database and populate it using scripts provided. The database you create
will then be used for various queries/problems in this homework.
Objectives
1. Become familiar with SQL language & syntax for SELECT queries, DDL and DML
2. Become familiar with a tool of your choice for building and submitting queries (whether
command mode or GUI.)
3. Successfully run the scripts necessary to create a sample database consisting of 8 tables, verify
that your database is correctly built.
4. Use SQL your database to answer the assigned problems.
Step One: Downloading and Installing MySQL
For this homework assignment you will need to download and install MySQL on your computer. How
you do this will depend on what type of computer you have and what Operating System it is running.
You will want to download MySQL Community Server 5.7.* (The current release number changes
from time to time. Use the most recent. As of this writing, it is 5.7.21.
Do NOT download “MySQL Cluster” software. That is a much different product.
This page (below) contains online documentation links where you can find help with the download and
install if you need it.
The download you need can be found here: choose the download file that matches your computer’s OS
and version.
https://dev.mysql.com/downloads/mysql/

CSCI3287 Database Systems
Homework # 2 -- SQL
CSCI 3287 Database Systems Page 2
Step Two: Choose Your Preferred Query Editor Tool
In order to create SQL queries and run them against your MySQL database, you will need a tool or a
“window” through which you can create and execute queries, and then view/copy/export the answer
set.
The default is the mysqld command line interface. This comes with MySQL and is similar to using the
Linux shell. If you choose to do your queries via the MySQL command line interface, you do not need to
download/install any query tool.
However, managing and running queries against MySQL databases is simpler, faster and easier if you use
a GUI (graphic user interface) tool. There are many available.
You have seen your instructor use SQLYog in class. It is a great tool, but it only works on a Windows PC.
It comes with a free 14-day trial. SQLYog can be downloaded here:
https://www.webyog.com/product/sqlyog
You can use MySQL Workbench. You have seen your instructor use MySQL Workbench in class to
create an ERD (Entity Relationship Diagram) data model in class and then generate SQL to create tables.
It is free. You can use MySQL Workbench to build and submit queries against your database. It is
available here: https://dev.mysql.com/downloads/workbench/ They offer versions for Windows,
Linux, MAC. Here is an example of what theMySQL Workbench query editor looks like.
CSCI3287 Database Systems
Homework # 2 -- SQL
CSCI 3287 Database Systems Page 3
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. https://dbeaver.jkiss.org/download/
Another alternative for MAC users is DataGrip. https://www.jetbrains.com/datagrip/ They offer a free
30-day trial, and a special free edition for students that you can sign up for.
Creating the Tables
Once you have selected your query editor, you need to download some scripts from Moodle and create
your database and tables, and then load the tables with data.
Before you can create your database, you need to make sure that your instance of MySQL is running.
Then using your query editor, you must connect to the MySQL instance.
Creating the database is simple. You can simply tell MySQL "create database northwinds;"
and it will do the rest. For this homework, you can let MySQL take all defaults. Once you create your
database, tell SQL "use northwinds; " so it knows which database to run your queries against.
When you first create your database it will be empty and contains no tables.
Next we will create the TABLES. The SQL statements to create the tables for your Homework2 database
can be found on the Moodle site with the other Homework Two files. We will be using the sample
database you saw in class called “Northwinds”. There is a model of this database on the class’ Moodle
site for WEEK FIVE called “Northwinds Database Model” (northwinds.pdf.) It is also included with the
files for Homework Two in the WEEK SEVEN materials. You should download and print this diagram and
keep it handy when you are doing the homework. It is very helpful to have table and column names in
front of you when writing SQL queries.
The Northwinds database consists of 8 tables. The HW1_scripts folder contains 9 script files, one for
creating each table, and one to verify that everything worked OK. (These scripts are all named
xxxxxxxxx.txt.)
 Suppliers
 Shippers
 Customers
 Employees
 Products
 Categories
 Orders
 Order_details
CSCI3287 Database Systems
Homework # 2 -- SQL
CSCI 3287 Database Systems Page 4
To complete this homework, you must open up each of these 8 script files, copy the SQL statements,
paste the SQL statements into your query editor and execute the script.
Note: The script begins with a command to DROP the table before it creates it. This allows you to run
the script over and over as needed. The VERY FIRST time you run this script, it will get an error
when it tries to DROP the table because the table does not yet exist. Don’t worry if you see this
error the first time you execute the script.
Note: GUI Query Editor Users: After you run some queries to create your tables, you might expect the
new tables to immediately appear under the “object explorer” on the left side of your GUI query
editor. They will eventually show up, but to see them appear right away, you will need to click
on the “refresh” icon (ifyour tool has one.) If this icon does not appear, then click somewhere
within your “object explorer” and the newly created tables should appear.
If you are using the command line editor, you can enter SHOW TABLES and MySQL will show you all
the tables in your database.
After creating all 8 tables, run the “verify” script. You should see the following tables and row counts for
each.
Preparing Your Homework Submission
Your results for this homework assignment should be captured in a document (such as a .txt file, MS
Word or similar tool.) Please then save your final deliverable document as a PDF. Use the link found in
the Homework Assignment section of WEEK SEVEN in the Moodle site to submit your work for grading.
If you are doing PAIR PROGRAMMING on this assignment, please be sure to identify the name of your
programming partner on your submission. You must EACH submit your own results document for this
homework.
You must turn in BOTH your SQL and your ANSWER SET (unless otherwise specified.)
CSCI3287 Database Systems
Homework # 2 -- SQL
CSCI 3287 Database Systems Page 5
For each problem where a multi-row answer set is created, the number of rows you should expect in
your answer set is listed. Some queries will product NO answer set.
Query Problems
For this homework you must create and execute queries against the Northwinds database to fulfill the
requirements listed below. For each query requirement the number of rows to expect in your answer
set is listed in parentheses.
1. List all the information in the Categories table. (8)
2. List the Company Name, Address, City, State/Province (Region), Zip (Postal Code), and Phone
Number for all suppliers located in France or Germany. (6)
3. List the Contact Name and Title for all suppliers with a Supplier ID number ranging from 5 to 20
(inclusive.) They should be listed in Contact Name order. (16)
4. Create an Economy Products report listing all Products including Product Name, Quantity per
Unit, Unit Price and Units in Stock for products with a Unit Price less than $10.00. (11)
5. Prepare a Reorder List for products currently in stock. (Products in stock have at least one unit
in inventory.) Show Product ID, Name, Quantity in Stock and Unit Price for products whose
inventory level is at or below the reorder level. (17)
6. Create an alphabetical listing (Last Name, First Name) of all employees not living in the USA who
have been employed with Northwinds for at least 5 years as of today. (3)
7. What is the name and unit price of the most expensive product sold by Northwinds? Use a sub
query. (1)
8. Create a list of the products in stock which have an inventory value (the number of units in stock
multiplied by the unit price) over $2000. Show the answer set columns as Product ID, Product
Name and “Total Inventory Value” in order of descending inventory value (highest to lowest.)
(13)
9. List the ProductID, Product Name, Unit Price for all products that come in cans that have been
discontinued. (2)
10. List the country and a count of Orders for all the orders that shipped outside the USA during
September 2013 in ascending country sequence. (9)
CSCI3287 Database Systems
Homework # 2 -- SQL
CSCI 3287 Database Systems Page 6
11. What is the average price (rounded to two decimal places) of all the products sold by
Northwinds? (1)
12. How many Northwinds customers are from France? (1)
13. List the CustomerID and CompanyName of the customers who have more than 20 orders.
(3)
14. Create a Supplier Inventory report (by Supplier ID) showing the total value of their inventory in
stock. (“value of inventory” = UnitsInStock * UnitPrice.) List only those suppliers from
whom Northwinds receives more than 3 different items. (4)
15. Create a SUPPLIER PRICE LIST showing the Supplier CompanyName, ProductName and
UnitPrice for all products from suppliers located in the United States of America. Sort the
list in order from HIGHEST price to LOWEST price. (12)
16. 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)
17. Create an ORDERS EXCEPTION LIST showing the CustomerID and the CompanyName of all
customers who have no orders on file. (2)
18. Create an OUT OF STOCK LIST showing the Supplier CompanyName, Supplier ContactName,
Product CategoryName, CategoryDescription, ProductName and UnitsOnOrder
for all products that are out of stock (UnitsInStock = 0). (5)
19. List the productname, suppliername, supplier country and UnitsInStock for all the
products that come in a bottle or bottles. (11 or 12 depending on your assumptions…)
20. Create a TOP CUSTOMER by COUNTRY LIST. List the Customer Company Name,
Customer Country, and the value (rounded to two decimal places) of
all their orders sorted by highest to lowest value for all customers with a total order value
greater than $30,000. (20)
(HINT: the value of an order is UnitPrice times Quantity less the discount.)
(Another HINT: you can convert the output format of a number by using the CAST command.
For example: CAST (unitprice*quantity)AS DECIMAL(9,2)) will format the
result of the multiplication into a decimal number with 9 total digits and 2 digits to the right of
the decimal. )
CSCI3287 Database Systems
Homework # 2 -- SQL
CSCI 3287 Database Systems Page 7
21. Create an “Employees Orders” VIEW listing employee lastname, firstname, and the total
count of each employee’s orders. (No answer set needed.)
22. Run a query against the Employee Orders view listing the employees and their order counts in
order from largest to smallest number of orders. (9 rows, one for each employee.)
23. Create a NEW table named “Top_Items” 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.)
24. Populate the new table “Top_Items” using these columns from the nwProducts table.
ProductID  ItemID
ProductType  ItemCode
ProductName  ItemName
Today’s date  Inventory Date
UnitsInStock  ItemQuantity
UnitPrice  ItemPrice
SupplierID  SupplierID
for those products whose inventory value is greater than $2,500. (No answer set needed.)
(HINT: the inventory value of an Item is ItemPrice times ItemQuantity. )
25. Delete the rows in Top_Items for suppliers from Canada. (2 rows deleted. No answer set
needed.)
26. Add a new column to the Top_Items table called InventoryValue ((decimal (9,2))) after the
inventory date. No answer set needed.
27. Update the Top_Items table, setting the InventoryValue column equal to ItemPrice times
ItemQuantity. (No answer set needed.)
28. List all columns in the Top_Items table. (7 rows, answer set required.)
29. Drop the Top_Items table. No answer set needed.