Description
This homework will give you hands-on practice in working with SQL. You will first create a database and insert sample data using scripts provided. The database will then be used to answer the query problems given below.
You will be using MySQL workbench to write SQL for this assignment, the tool downloaded during the class. If you haven’t downloaded yet, you will need to download and install MySQL workbench on your computer. Downloads are available at: https://dev.mysql.com/downloads/workbench/ Choose the download file that matches your computer’s OS and version. Installation files are also available in Canvas.
Creating the Database
You need to download the “HW_3_SQL_NorthWinds.sql” script file from Canvas, and execute it. It will create sample database and tables, and then load the tables with data. The script runs fine as-is without any modification. The script begins with a command to DROP the table before creating it. This allows you to run the script over and over as needed.
Before executing scripts, make sure your MySQL instance is running and Safe Updates is unchecked (Edit – SQL Editor – Uncheck Safe Updates).
There is an ERD for this database in the Canvas called “HW_3_ SQL_Northwinds_ERD.pdf”. You should download and print ERD and keep it handy while doing the homework. It will be helpful to have tables, column names and their relationship in front of you when writing SQL queries.
CSCI 3287: Design and Analysis of Page 1
The HW_3_SQL_NorthWinds.sql contains 9 scripts for creating each of 8 tables, and one to verify that the data count.
-
hwSuppliers
-
hwShippers
-
hwCustomers
-
hwEmployees
-
hwProducts
-
hwCategories
-
hwOrders
-
hwOrderdetails
After running scripts, run the ‘Verify’ script, as below. You should see the following 8 tables and row counts for each.
Note: Please let instructor know if your tables/counts are different.
— Verify row counts
USE HW_3_SQL_NorthWinds;
SELECT table_name, table_rows
FROM information_schema.tables
WHERE TABLE_NAME LIKE ‘HW%’;
CSCI 3287: Design and Analysis of Data Systems Page 2
Query Problems
-
You must submit BOTH your SQL and your ANSWER SET in pdf.
-
Display ONLY columns / attributes that are asked to show in questions.
-
Make sure to use table / column aliases, where applicable. Use a meaningful derived column names as asked in question, e.g. SalesPrice * Quantity = Total Revenue so your result column should display as Total Revenue.
-
All Questions are equally weighted.
Questions:
-
Show a list the Company Name and Country for all Suppliers located in Japan or Germany.
-
Show a list of Products’ Product Name, Quantity per Unit and Unit Price for products with a Unit Price less than $7 but more than $ 4.
-
Show a list of Customers’ Company Name, Contact Title and City whose Country is USA and City is Portland OR Country is Canada and City is Vancouver.
-
Show a list the Contact Name and Contact Title for all Suppliers with a SupplierID from 5 to
-
(inclusive) and sort in descending order by ContactName.
-
Show a product name and unit price of the least expensive Products (i.e. lowest unit price)? You MUST use a Sub Query.
-
Show a list of Ship Country and their Order Counts that is shipped outside the USA between May 4th and 5th 2015.
-
Show a list of all employees with their first name, last name and hiredate (formated to mm/dd/yyyy) who are NOT living in the USA and have been employed for at least 5 years.
-
Show a list of Product Name and their ‘Inventory Value’ (Inventory Value = units in stock multiplied by their unit price) for products whose ‘Inventory Value’ is over 3000 but less than 4000.
-
Show a list of Products’ product Name, Unit in Stock and ReorderLevel level whose Product Name starts with ‘S’ that are currently in stock (i.e. at least one Unit in Stock) and inventory level is at or below the reorder level.
CSCI 3287: Design and Analysis of Data Systems Page 3
-
Show a list of products’ Product Name and Units In Stock whose Product Name starts with ‘F’ and Rank them based on UnitsInStock from highest to lowest (i.e. highest UnitsInStock rank = 1, and so on). Display rank number as well.
-
Show a list of products’ Product Name and Unit Price for ProductID from 1 to 5 (inclusive) and Rank them based on UnitPrice from lowest to highest. Display rank number as well.
-
Show a list of employees’ first name, last name, country and date of birth (formatted to mm/dd/yyyy) who were born after 1984 and Rank them by date of birth (oldest employee rank 1st, and so on) for EACH country, i.e. Rank number should reset/restart for EACH country.
-
-
The End.
-
CSCI 3287: Design and Analysis of Data Systems Page 5
