/******************************************************************************************************************************************/ /* The JW Trading Company is planning to do their strategic planning session and needs some input data for the sessions by 11:59 on */ /* April 4. As a Georgian graduate, you have been selected as the best possible person to query the database and provide the leadership */ /* team the necessary insights to bring the company to the next level. */ /* */ /* Listed below are a series of requested reports, please write the appropriate queries. As the information is very sensitive, your */ /* Director will review the queries to ensure they are complete, so they MUST be easily readable. Be sure to spread the queries across */ /* multiple lines as follows: */ /* */ /* SELECT [column names] */ /* FROM [TABLE name] (if JOINs are required, only put 1 JOIN per line) */ /* WHERE [conditions] */ /* HAVING [conditions] */ /* */ /* Please embed your queries into this file and save it named as comp2003-assignment 2-student num.sql */ /* */ /* The resulting queries should have CLEAR headers as the leadership team will not understand SQL. So if you need to use something like */ /* SUM(quantity), you should rename the column as '# of units sold'. */ /* */ /* If you have any questions or clarifications, please contact Jaret Wright at jaret.wright@georgiancollege.ca, see him in class or */ /* during the office hours. */ /* */ /* */ /* Student Name: Armagan Tekdoner */ /* Assignment #2 */ /* */ /* */ /* Question Marks available Marks earned */ /* 1 1 1 */ /* 2 2 2 */ /* 3 2 2 */ /* 4 2 1 */ /* 5 2 2 */ /* 6 2 1 */ /* 7 3 2 */ /* 8 4 3 */ /* 9 4 3 */ /* 10 3 3 */ /* 11 3 2 */ /* 12 4 3 */ /* 13 2 2 */ /* 14 4 4 */ /* 15 4 4 */ /* 16 4 4 */ /* 17 3 2 */ /* 18 3 1.5 */ /* 19 4 4 */ /* 20 4 3 */ /* total 60 49.5 */ /******************************************************************************************************************************************/ USE winter2013; /******************************************************************************************************************************************/ /*1. How many employees does the company have? (1 mark) */ /* Marks earned: 1 */ /******************************************************************************************************************************************/ SELECT COUNT(employeeID) AS 'Number of Employees' FROM employees1; -- there are 9 employees /******************************************************************************************************************************************/ /*2. Show all employees who have made sales that were shipped to Seattle. Employee names should only be shown once for each employee */ /* List the employee ID, first and last name. (2 marks) */ /* Marks earned: 2 */ /******************************************************************************************************************************************/ SELECT DISTINCT orders1.employeeID, -- Renames the columns for the management firstName AS 'First name of the employee', lastName AS 'Last name of the employee' FROM employees1 INNER JOIN orders1 ON employees1.employeeID = orders1.employeeID -- specifies the city WHERE shipcity = 'Seattle'; /* the result is 1 Nancy Davolio 2 Andrew Fuller 3 Janet Leverling 4 Margaret Peacock 5 Steven Buchanan 7 Robert King 8 Laura Callahan */ /******************************************************************************************************************************************/ /*3. List all the products that need to be reordered including how many are currently in stock, how many are on order and the reorder */ /* level. Be sure to account for both units in stock and already on order (2 marks) */ /* Marks earned: 2 */ /******************************************************************************************************************************************/ -- Renames the column for the management SELECT DISTINCT productName AS 'Products to be reordered', -- provides info about the inventory unitsInStock, unitsOnOrder, reorderLevel FROM products1 -- specifies the condition of reordering WHERE reorderLevel >= unitsInStock + unitsOnOrder; /* the result is Chef Anton's Gumbo Mix 0 0 0 Alice Mutton 0 0 0 Thüringer Rostbratwurst 0 0 0 Nord-Ost Matjeshering 10 0 15 Perth Pasties 0 0 0 Outback Lager 15 10 30 testSP 0 0 0 test 0 0 0 */ /******************************************************************************************************************************************/ /*4. Select the 10 most popular products. (2 marks) */ /* Marks earned: 1 */ /* JW comment - You had most of the query, but you needed to order by SUM(quantity), otherwise you are only looking at the volumes from */ /* the first order */ /******************************************************************************************************************************************/ -- renames the column SELECT DISTINCT productName AS '10 most popular products', quantity 'The amount they sold' FROM Order_Details -- joins the two adjacent tables in which the relevant data are stored INNER JOIN products1 ON Order_Details.productID = products1.productID ORDER BY quantity desc -- limits the output by 10 LIMIT 10; /* the answer is Wimmers gute Semmelknödel 130 Chartreuse verte 130 Pâté chinois 120 Jack's New England Clam Chowder 120 Sirop d'érable 120 Rhönbräu Klosterbier 120 Schoggi Schokolade 120 Manjimup Dried Apples 120 Perth Pasties 120 Rogede sild 110 */ /******************************************************************************************************************************************/ /*5. select the 10 customers with the highest # of orders (2 marks) */ /* Marks earned: 2 */ /******************************************************************************************************************************************/ -- displays the name and renames the column SELECT DISTINCT customers1.contactName AS 'Top 10 consumers', -- counts the orders placed and renames the column*/ COUNT(orderID) AS 'Number of orders' FROM customers1 -- joins the table in which each order is stored to customers table JOIN orders1 ON orders1.customerID = customers1.customerID GROUP BY customers1.contactName -- sorts the results ORDER BY COUNT(orderID) desc LIMIT 10; /* the answer is Jose Pavarotti 31 Roland Mendel 30 Horst Kloss 28 Maria Larsson 19 Patricia McKenna 19 Christina Berglund 18 Paula Wilson 18 Carlos Hernández 18 Laurence Lebihan 17 Pirkko Koskitalo 15 */ /******************************************************************************************************************************************/ /*6. Show the total of each order (in $) without a discount (2 marks) */ /* Marks earned: 1 */ /* JW comment - each order has more than 1 item on it, so you need to SUM them with ROUND(SUM(unitPrice*quantity),2)) */ /******************************************************************************************************************************************/ -- displays the order id, eliminates duplicate results, and renames the column SELECT orderID AS 'Processed Order Number', -- adds the $ sign, calculates the revenue, includes cents, and names the new column CONCAT('$', ROUND(unitPrice*quantity,2)) AS 'Revenue per Order with no Discount' -- specifies the table in which sales data are stored FROM Order_Details -- excludes discounted orders WHERE discount = 0.0 GROUP BY orderID -- sorts the results ORDER BY unitPrice*quantity DESC; /* 613 rows returned with $15810 being the highest, $4.80 the lowest */ /******************************************************************************************************************************************/ /*7. Show the total of each order (in $) including the discount rate (3 marks) */ /* Marks earned: 2 */ /* JW comment - this has the problem as question 6, you need to use SUM */ /******************************************************************************************************************************************/ -- displays the order id, eliminates duplicate results, and renames the column SELECT DISTINCT orderID AS 'Processed Order Number', -- adds the $ sign, calculates sum, makes the discount if applicable otherwise multiples with 1, adds cents, and names the new column CONCAT('$', ROUND(unitPrice*quantity*(1-discount),2)) AS 'Net Revenue per each Order' -- specifies the table in which sales data are stored FROM Order_Details GROUP BY orderID -- sorts the results ORDER BY unitPrice*quantity*(1-discount) DESC; /* 830 rows returned with $15810 the highest, $4.80 the lowest /******************************************************************************************************************************************/ /*8. Show the total amount spent per customer including the discount rate (4 marks) */ /* Marks earned: 3 */ /* JW comment - the same problem as the previous questions, you need to SUM the orders - why else would you use GROUP BY? */ /******************************************************************************************************************************************/ -- displays the name and renames the column SELECT DISTINCT customers1.contactName AS 'Customer Name', -- adds the $ sign, displays total revenue per item and renames the column CONCAT('$', ROUND(unitPrice*quantity*(1-discount),2)) AS 'Total amount spent' FROM customers1 -- joins the table to access the Order_Details table JOIN orders1 ON orders1.customerID = customers1.customerID -- then joins the table in which each order is stored JOIN Order_Details ON Order_Details.orderID = orders1.orderID -- customers are the base of the calculaions GROUP BY customers1.contactName -- sorts the results ORDER BY unitPrice*quantity*(1-discount) desc; -- 89 rows returned, Pascale Cartrain being the best customer with $2462.40 /******************************************************************************************************************************************/ /*9. Show the 10 customer names with the highest dollar value of orders (4 marks) */ /* Marks earned: 3 */ /* JW comment - same challenge as above, otherwise well done */ /******************************************************************************************************************************************/ -- displays the name and renames the column SELECT DISTINCT customers1.contactName AS 'The 10 biggest spenders', -- adds the $ sign, displays total revenue per item, and renames the column CONCAT('$', ROUND(unitPrice*quantity*(1-discount),2)) AS 'Total amount they spent' FROM customers1 -- joins the table that enables access to Order_Details table JOIN orders1 ON orders1.customerID = customers1.customerID -- then joins the table in which each order is stored JOIN Order_Details ON Order_Details.orderID = orders1.orderID -- customers are the key to the calculations GROUP BY customers1.contactName -- sorts the results ORDER BY unitPrice*quantity*(1-discount) desc LIMIT 10; /* result Pascale Cartrain $2462.40 Jean Fresnière $1600.00 Anabela Domingues $1296.00 Rene Phillips $1125.00 Manuel Pereira $990.00 Frédérique Citeaux $936.00 Carlos Hernández $877.50 Ann Devon $864.00 Hari Kumar $739.48 Peter Franken $735.00 */ /******************************************************************************************************************************************/ /*10. find total sales made in Germany (3 marks) */ /* Marks earned: 3 */ /******************************************************************************************************************************************/ -- displays the required datum, adds the currency, calculates the sum and rounds it, and names the column created SELECT CONCAT('CAD ', ROUND(SUM(unitPrice*quantity*(1-discount)),2)) AS 'Total sales, Germany' FROM orders1 -- joins the table in which order details are stored JOIN Order_Details ON Order_Details.orderID = orders1.orderID -- specifies the country condition WHERE shipcountry = 'Germany' -- sorts the results ORDER BY unitPrice*quantity*(1-discount) desc; -- the answer is 'CAD 230284.63' /******************************************************************************************************************************************/ /*11. Show the total # of units sold for each product, even those that have never been purchased from lowest to highest (3 marks) */ /* Marks earned: 2 */ /* JW comment - you need to use SUM(quantity) to get the total # of units sold, otherwise you are only looking at the first order */ /******************************************************************************************************************************************/ SELECT quantity AS 'Total number of units sold', products1.productName AS 'Product Name' FROM Order_Details -- RIGHT OUTER JOIN includes items with null values RIGHT OUTER JOIN products1 ON Order_Details.productID = products1.productID GROUP BY productName -- sorts data as required ORDER BY quantity ASC; -- 79 units returned /******************************************************************************************************************************************/ /*12. Show the order total for each product, even those that have never been purchased, from lowest to highest (4 marks) */ /* Marks earned: 3 */ /* JW comment - when grouping objects, be sure to pick a UNIQUE identified. Product ID would ensure a proper grouping, but product */ /* allows for some items to be grouped, even when they shouldn't be */ /******************************************************************************************************************************************/ -- displays the product SELECT DISTINCT products1.productID, products1.productName AS 'Product Name', -- calculates the actual total revenue after discounts and renames the column ROUND(SUM(Order_Details.unitPrice*quantity*(1-discount)),2) AS 'Order Total' FROM Order_Details -- RIGHT OUTER JOIN includes items with null values RIGHT OUTER JOIN products1 ON Order_Details.productID = products1.productID -- customers are the key to the calculations GROUP BY productName -- sorts data as required ORDER BY ROUND(SUM(Order_Details.unitPrice*quantity*(1-discount)),2) ASC; -- 79 products returned, totalling from 2 NULL's to 141396.73 /******************************************************************************************************************************************/ /*13. Show the complete category & product list, ordered by category then product (2 marks) */ /* Marks earned: 2 */ /* JW comment - I let it go, but you should group by ProductID, not name to ensure the uniqueness */ /******************************************************************************************************************************************/ -- shows everything in two tables, hopefully it is what this question asks SELECT * FROM categories -- joins two table in which all related details are stored JOIN products1 ON categories.categoryID = products1.categoryID -- ordered by category then product ORDER BY categoryName, productName ASC; -- 77 rows returned /* ----------------------------------------------------------- */ -- below is an alternative query that displays only the 2 mentioned columns SELECT categoryName, productName FROM categories -- joins two table in which all related details are stored JOIN products1 ON categories.categoryID = products1.categoryID -- ordered by category then product ORDER BY categoryName, productName ASC; -- 77 rows returned /******************************************************************************************************************************************/ /*14. Show total amount of discounts given on sales for each country. This should be ordered highest to lowest (4 marks) */ /* Marks earned: 4 */ /******************************************************************************************************************************************/ SELECT ROUND(SUM(unitPrice*quantity*(discount)),2) AS 'Total amount of discounts given on sales', shipcountry AS 'for each country' FROM orders1 -- joins the table in which order details are stored JOIN Order_Details ON Order_Details.orderID = orders1.orderID -- displays all destination locations GROUP BY shipcountry -- sorts the results from highest to lowest ORDER BY ROUND(SUM(unitPrice*quantity*(discount)),2) desc; -- 21 rows returned /******************************************************************************************************************************************/ /*15. Show total sales by employee. List the employee ID, first and last name (4 marks) */ /* Marks earned: 4 */ /******************************************************************************************************************************************/ SELECT employees1.employeeID, firstName AS 'First name of the employee', lastName AS 'Last name of the employee', -- total sales is below ROUND(SUM(unitPrice*quantity*(1-discount)),2) AS 'Total Sales he/she made' FROM employees1 -- joins the table that enables access to Order_Details table JOIN orders1 ON employees1.employeeID = orders1.employeeID -- then joins the table from which total sales can be found out JOIN Order_Details ON orders1.orderID = Order_Details.orderID -- each employee is listed GROUP BY employees1.employeeID -- management has been assumed to be familiar with first names more than last names ORDER BY firstName ASC; -- the answer is /* 2 Andrew Fuller 166537.75 9 Anne Dodsworth 77308.07 3 Janet Leverling 202812.84 8 Laura Callahan 126862.28 4 Margaret Peacock 232890.85 6 Michael Suyama 73913.13 1 Nancy Davolio 192107.60 7 Robert King 124568.23 5 Steven Buchanan 68792.28 */ /******************************************************************************************************************************************/ /*16. Show the most expensive product in each category along with the price. Hint: Nested queries could be helpful here (4 marks) */ /* Marks earned: 4 */ /******************************************************************************************************************************************/ -- displays required columns and renames them SELECT productName AS 'The most expensive product', categoryName AS 'of its category', -- displays cents and not additional digits ROUND(products1.unitPrice,2) AS 'and its price' FROM categories JOIN products1 ON products1.categoryID = categories.categoryID WHERE unitPrice = (SELECT MAX(unitPrice) FROM products1 WHERE categories.categoryID = products1.categoryID) GROUP BY categories.categoryID; -- the answer is /* Côte de Blaye Beverages 263.50 Vegie-spread condiments 43.90 Sir Rodney's Marmalade confections 81.00 Raclette courdavault Dairy products1 55.00 Gnocchi di nonna Alice Grains/Cereals 38.00 Thüringer Rostbratwurst Meat/Poultry 123.79 Manjimup Dried Apples produce 53.00 Carnarvon Tigers Seafood 62.50 */ /******************************************************************************************************************************************/ /*17. Show by country, the # of orders and total shipping cost paid to each shipping carrier (3 marks) */ /* Marks earned: 2 */ /* JW comment: You need to use the SUM(freight) to add all the shipping costs, you are only seeing the first freight cost in your */ /* query */ /******************************************************************************************************************************************/ SELECT shipcountry AS 'Export Destination', COUNT(orderID) AS 'Number of Orders shipped', ROUND(freight,2) AS 'Total shipping cost paid' FROM orders1 GROUP BY shipcountry -- highest shipping cost comes first ORDER BY freight DESC; -- the code is self-explanatory, that is why there is not much comment -- 29 rows returned, Ireland being the 'leader' /******************************************************************************************************************************************/ /*18. List ALL the suppliers ordered by total # of units sold (3 marks) */ /* Marks earned: 1.5 */ /* JW comment: What is the easiest way to ensure you GROUP BY each unique supplier? Use the keyfield for the supplier table, which */ /* will be supplierID. In order to add up all of the quantities sold, you need to use SUM(quantity) otherwise you are */ /* only looking at the first order */ /******************************************************************************************************************************************/ /* There is some ambiguity in this question. Does "All the suppliers" mean their names or ID's or something else? I assumed it was company names. I construct a query based on the keywords given, hoping not to lose marks for not understanding what exactly was required. */ SELECT -- displays 2 required columns and renames them companyName AS 'Supplier Company', quantity AS 'Total # of units sold' FROM suppliers -- accesses Order_Details via products1 JOIN products1 ON suppliers.supplierID = products1.supplierID JOIN Order_Details ON products1.productID = Order_Details.productID GROUP BY companyName -- sorts data as required ORDER BY quantity DESC; -- 29 rows returned /******************************************************************************************************************************************/ /*19. Which supplier provides the most product in terms of total revenue (total $ sales). Be sure to take into account the discount */ /* rate (4 marks) */ /* Marks earned: 4 */ /******************************************************************************************************************************************/ SELECT -- displays 2 required columns and renames them companyName AS 'Supplier Company', ROUND(SUM(Order_Details.unitPrice*quantity*(1-discount)),2) AS 'Total Sales he/she made' FROM suppliers -- accesses Order_Details via products1 JOIN products1 ON suppliers.supplierID = products1.supplierID JOIN Order_Details ON products1.productID = Order_Details.productID GROUP BY companyName -- sorts data as required ORDER BY ROUND(SUM(Order_Details.unitPrice*quantity*(1-discount)),2) DESC; -- 49 rows returned /******************************************************************************************************************************************/ /*20. Show the employee that sold the most in 2012 (including discounts) Note: the discount is a % of the cost (4 marks) */ /* Marks earned: 3 */ /* JW comment - you need to include the SUM function when calculating the total for each employee or else you are only looking at the */ /* first record */ /******************************************************************************************************************************************/ SELECT -- displays 3 required columns and renames them firstName AS 'First name of the employee', lastName AS 'Last name of the employee', MAX(ROUND(Order_Details.unitPrice*quantity*(1-discount),2)) AS 'Total sales made by this employee in 2012' FROM employees1 -- accesses Order_Details via orders1 JOIN orders1 ON employees1.employeeID = orders1.employeeID JOIN Order_Details ON orders1.orderID = Order_Details.orderID -- specifies the condition WHERE orderDate >= 20120101 AND orderDate <= 20121231; -- the answer is -- Nancy Davolio 15810.00