Assignment 2: SQL Solution

Description

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.

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)

12. 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)

12. 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)

12. Delete the rows in “​nwtopitems​” for suppliers from Canada. (2 rows deleted. No answer set needed)

12. Add a new column to the “​nwtopitems​” table called ​InventoryValue​(decimal(9,2)) after the inventory date. (No answer set needed)

12. Update the “​nwtopitems​” table, setting the ​InventoryValue​column equal to the ItemPrice​multiplied by the ​ItemQuantity​. (No answer set needed)

12. 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.