Homework 2 SQL Solution

Overview




This homework is worth 10 points (out of 100) toward your final grade. It is due on Sunday, March 11, at 11:55 p.m. Late submissions will be penalized 50% during a 3-day grace period up until Wednesday, Feb 28, 11:55 p.m. After that time, no late work will be accepted. Your homework submission should be a document saved and submitted as a PDF file via the link found in the Homework section of the Week 7 Moodle Feb 26 – Mar 4 -- which is the same place where you got this file.




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




Become familiar with SQL language & syntax for SELECT queries, DDL and DML



Become familiar with a tool of your choice for building and submitting queries (whether command mode or GUI.)



Successfully run the scripts necessary to create a sample database consisting of 8 tables, verify that your database is correctly built.



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







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.



























































































CSCI 3287 Database Systems Page 2

CSCI3287 Database Systems




Homework # 2 -- SQL







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







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







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.




List all the information in the Categories table. (8)



List the Company Name, Address, City, State/Province (Region), Zip (Postal Code), and Phone Number for all suppliers located in France or Germany. (6)



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)



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)



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)



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)



What is the name and unit price of the most expensive product sold by Northwinds? Use a sub query. (1)



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)



List the ProductID, Product Name, Unit Price for all products that come in cans that have been discontinued. (2)



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







What is the average price (rounded to two decimal places) of all the products sold by Northwinds? (1)



How many Northwinds customers are from France? (1)



List the CustomerID and CompanyName of the customers who have more than 20 orders.
(3)




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)



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)



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)



Create an ORDERS EXCEPTION LIST showing the CustomerID and the CompanyName of all customers who have no orders on file. (2)



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)



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…)






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
















Create an “Employees Orders” VIEW listing employee lastname, firstname, and the total count of each employee’s orders. (No answer set needed.)



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



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




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




Delete the rows in Top_Items for suppliers from Canada. (2 rows deleted. No answer set needed.)



Add a new column to the Top_Items table called InventoryValue ((decimal (9,2))) after the inventory date. No answer set needed.



Update the Top_Items table, setting the InventoryValue column equal to ItemPrice times ItemQuantity. (No answer set needed.)



List all columns in the Top_Items table. (7 rows, answer set required.)



Drop the Top_Items table. No answer set needed.