$30
CSCI 3287: Design and Analysis of Data Systems Page 1 HW-4 SQL
This homework will give you hands-on practice in working with SQL. 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 first need to download the “HW-4 SQL.sql” script file from Canvas and execute it. It will create a database with tables and then load data into tables. The script runs fine as-is without any modification. The script begins with a command to DROP the table before creating it. This will allow you to run scripts over and over, if 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 HW-4 SQL ERD.pdf for this database in the Canvas. You should download and keep it handy while doing the homework. It will be helpful to have table and column names, and their relationship in front of you when writing SQL queries. CSCI 3287: Design and Analysis of Data Systems Page 2 HW-4 SQL After running HW-4 SQL.sql scripts, you should see HW_4_SQL database with the following 8 tables and row counts. Note: Please let instructor know if your tables and row counts are different. Notes: • Submit your SQL scripts and output (result set) in word or PDF in Canvas. • 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 = Revenue so your result column should display as Revenue. • Make sure to mark your answers with the question number. • All Questions are equally weighted. CSCI 3287: Design and Analysis of Data Systems Page 3 HW-4 SQL Write SQL scripts for below questions: 1. Show a list the Company Name and Country for all Suppliers located in Japan or Germany. 2. Show a list of Product Name, Quantity per Unit and Unit Price for products with a Unit Price less than $7 but more than $ 4. 3. Show a list of Company Name, City and Country for Customers whose Country is USA and City is Portland, OR Country is Canada and City is Vancouver. 4. Show a list the Contact Name and Contact Title for all Suppliers with a SupplierID from 5 to 8 (inclusive) and sort in descending order by ContactName. 5. Show a product name and unit price of the least expensive product (i.e., lowest unit price)? You MUST use a Sub Query. 6. Display Ship Country and their Order Count for all Ship Country except USA for Shipped Date between May 4th and 10th 2015 whose Order Count is greater than 3. 7. 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. 8. 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. 9. 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 Unit in Stock is at or below the reorder level. 10. Show a list of Product Name, Unit Price and Quantity Per Unit for all products, whose Quantity Per Unit has/measure in 'box' that have been discontinued (i.e., discontinued = 1). 11. Show a list of Product Name and their TOTAL inventory value (inventory value = UnitsInStock * UnitPrice) for Supplier's Country from Japan. 12. Show a list of customer's country and their count that is greater than 8. 13. Show a list of Orders' Ship Country, Ship City and their Order count for Ship Country 'Austria' or 'Argentina'. CSCI 3287: Design and Analysis of Data Systems Page 4 HW-4 SQL 14. Show a list of Supplier's Company Name and Product's Product Name for supplier's country from Spain. 15. What is the 'Average Unit Price' (rounded to two decimal places) of all the products whose ProductName ends with 'T'? 16. Show a list of employee's full name (i.e., firstname, lastname, e.g., Harrison Ford), title and their Order count for employees who has more than 120 orders. 17. Show a list customer's company Name and their country who has NO Orders on file (i.e., NULL Orders). 18. Show a list of Category Name and Product Name for all products that are currently out of stock (i.e. UnitsInStock = 0). 19. Show a list of products' Product Name and Quantity Per Unit, which are measured in 'pkg' or 'pkgs' or 'jars' for a supplier’s country from Japan. 20. Show a list of customer's company name, Order’s ship name and total value of all their orders (rounded to 2 decimal places) for customers from Mexico. (value of order = (UnitPrice * Quantity) less discount. Discount is given in % e.g., 0.10 means 10%). 21. Show a list of products' Product Name and suppliers' Region whose product name starts with 'L' and Region is NOT blank/empty. 22. Show a list of Order's Ship Country, Ship Name and Order Date (formatted as MonthName and Year, e.g. March 2015) for all Orders from 'Versailles' Ship City whose Customer's record doesn't exists in Customer table. 23. 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. 24. 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. 25. 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. Display rank number as well. The End.