+ INNER JOINS: Join three tables: Using the Order Details, OrderHeaders, and Products tables, determine for...

Transcribed Image Text:

+ INNER JOINS: Join three tables: Using the Order Details, OrderHeaders, and Products tables, determine for each product the average price received in 2019 and compare this amount to the list price (the unit price in the Products table). In your output display (1) ProductID, (ii) AveragePriceReceived (i.e., average sales unit price in 2019 for each product where sales unit price is UnitPrice*(1- Discount) from the OrderDetails table), (iii) UnitPrice from the products table, and (iv) a new calculated field that calculates the percentage difference between AveragePriceReceived and the UnitPrice in the products table. Name this field PercentPrice Difference. 14 SELECT * FROM OrderDetails; 15 SELECT * FROM OrderHeaders; 16 SELECT FROM Products; Grid view MN +55 7 00 Form view OrderID ProductID 10248 10248 10248 10249 10249 10250 10250 10250 10251 10251 Status 72 14 42925 2 65 1 UnitPrice 14 9.8 34.8 18.6 42,4 16.8 16.8 15.6 Quantity Total rows loaded: 2155 12 Discount оооооо 10 35 0.150000006 15 0.150000006 6 0.050000001 15 0.050000001 [25:14:10] Query finished 10.001 second(s), [23:16:38] Error while executing SQL query on database 'Northwind Origina [23:23:09] Query finished in 0.000 second(s), or 2 13 14 SELECT * FROM OrderDetails; 15 SELECT FROM OrderHeaders; 16 SELECT * FROM Products; 17 Grid view 3 V x K ← 1 1 2 3 4 529 co on 6 7 8 9 Form view Status OrderID Customer II Employeell 10249 TRADH 10252 SUPRD 10250 HANAR 10251 VICTE 10255 RICSU 10253 HANAR 10248 WILMK 10256 WELLI 10257 HILAA 10251 CHOPS ➜ ice Products.UnitPrice); Total rows loaded: 830 OrderDate RequiredDa ShippedDate ShipVia 6 2017-04-26 2017-06-07 2017-05-01 1 4 2017-04-30 2017-05-28 2017-05-02 2 4 2017-04-29 2017-05-27 2017-05-03 2 3 2017-04-29 2017-05-27 2017-05-06 1 9 2017-05-03 2017-05-31 2017-05-06 3 3 2017-05-01 2017-05-15 2017-05-07 2 5 2017-04-25 05-23 2017-05-07 3 3 2017-05-06 2017-06-03 2017-05-08 2 4 2017-05-07 2017-06-04 2017-05-13 3 5 2017-05-02 2017-05-30 2017-05-11 2 Freight ShipName 11.61 Toms Spezialitäten 51.3 Suprêmes délices 65.83 Hanari Carnes 41.34 Victuailles en stock 148.33 Richter Supermarkt 58.17 Hanari Carnes 32.38 Vins et alcools Chevalier 13.97 Wellington Importadora 81.91 HILARION-Abastos 22.09 Chan-cuau Chinara [23:30:50] LITOI WHILE Executiny se query off data or wint nighttrapshear SELECT SYyntax error [23:23:09] Query finished in 0.000 second(s). [23:23:56] Query finished in 0.002 second(s). ShipAddress Luisenstr. 48 Boulevard Tirou, 255 Rua do Paço, 67 2, rue du Commerce Starenweg 5 Rua do Paço, 67 59 rue de l'Abbaye Rua do Mercado, 12 Carrera 22 con Ave. Carlos Soublette #8-35 Haunter 21 I ShipCity Münster Charleroi Rio de Janeiro RJ Lyon Genève Rio de Janeiro RJ Reims Resende SP San Cristóbal Táchira Born ShipRegion NULL NULL NHEE NULE ShipPostal ShipCountry 44087 Germany B-6000 Belgium 05454-876 Brazil 69004 France 1204 Switzerland 05454-876 Brazil 51100 France 08737-363 Brazil 5022 Venezuela 2012 Switzerland 14 SELECT * FROM OrderDetails; 15 SELECT * FROM OrderHeaders; 16 SELECT * FROM Products; Grid view Form view ANTS SNOO 1 2 3 4 8 ProductID ProductName Status 1 Chai 2 Chang 1 3 Aniseed Syrup 4 Chef Anton's Cajun Seasoning 5 Chef Anton's Gumbo Mix 6 Grandma's Boysenberry Spread 7 Uncle Bob's Organic Dried Pears 8 Northwoods Cranberry Sauce 9 Mishi Kobe Niku 10 lura [23:25:09] Query Tinished in 0.000 second(s). [23:23:56] Query finished in 0.002 second(s). [23:24:13] Query finished in 0.000 second(s). Total rows loaded: 77 Supplier ID CategoryII QuantityPerUnit 1 10 boxes x 20 bags 1 1 1 24-12 oz bottles 1 2 12-550 ml bottles 2 48 - 6 oz jars 2 36 boxes 2 12-8 oz jars 7.12 - 1 lb pkgs. 2 12-12 oz jars 6 18 500 g pkgs. 9 12 200 maliars WEN NI 2 2 3 3 3 4 A CANON UnitPrice UnitsInStox UnitsOnOr ReorderLev Discontinue 10 FALSE 39 25 FALSE 25 FALSE 0 FALSE 0 TRUE 25 FALSE 10 FALSE 0 FALSE 0 TRUE A FALSE 19 22 21.35 25 30 40 97 21 13 53 120 15 6 29 0 40 70 0 0 0 0 0 0 + INNER JOINS: Join three tables: Using the Order Details, OrderHeaders, and Products tables, determine for each product the average price received in 2019 and compare this amount to the list price (the unit price in the Products table). In your output display (1) ProductID, (ii) AveragePriceReceived (i.e., average sales unit price in 2019 for each product where sales unit price is UnitPrice*(1- Discount) from the OrderDetails table), (iii) UnitPrice from the products table, and (iv) a new calculated field that calculates the percentage difference between AveragePriceReceived and the UnitPrice in the products table. Name this field PercentPrice Difference. 14 SELECT * FROM OrderDetails; 15 SELECT * FROM OrderHeaders; 16 SELECT FROM Products; Grid view MN +55 7 00 Form view OrderID ProductID 10248 10248 10248 10249 10249 10250 10250 10250 10251 10251 Status 72 14 42925 2 65 1 UnitPrice 14 9.8 34.8 18.6 42,4 16.8 16.8 15.6 Quantity Total rows loaded: 2155 12 Discount оооооо 10 35 0.150000006 15 0.150000006 6 0.050000001 15 0.050000001 [25:14:10] Query finished 10.001 second(s), [23:16:38] Error while executing SQL query on database 'Northwind Origina [23:23:09] Query finished in 0.000 second(s), or 2 13 14 SELECT * FROM OrderDetails; 15 SELECT FROM OrderHeaders; 16 SELECT * FROM Products; 17 Grid view 3 V x K ← 1 1 2 3 4 529 co on 6 7 8 9 Form view Status OrderID Customer II Employeell 10249 TRADH 10252 SUPRD 10250 HANAR 10251 VICTE 10255 RICSU 10253 HANAR 10248 WILMK 10256 WELLI 10257 HILAA 10251 CHOPS ➜ ice Products.UnitPrice); Total rows loaded: 830 OrderDate RequiredDa ShippedDate ShipVia 6 2017-04-26 2017-06-07 2017-05-01 1 4 2017-04-30 2017-05-28 2017-05-02 2 4 2017-04-29 2017-05-27 2017-05-03 2 3 2017-04-29 2017-05-27 2017-05-06 1 9 2017-05-03 2017-05-31 2017-05-06 3 3 2017-05-01 2017-05-15 2017-05-07 2 5 2017-04-25 05-23 2017-05-07 3 3 2017-05-06 2017-06-03 2017-05-08 2 4 2017-05-07 2017-06-04 2017-05-13 3 5 2017-05-02 2017-05-30 2017-05-11 2 Freight ShipName 11.61 Toms Spezialitäten 51.3 Suprêmes délices 65.83 Hanari Carnes 41.34 Victuailles en stock 148.33 Richter Supermarkt 58.17 Hanari Carnes 32.38 Vins et alcools Chevalier 13.97 Wellington Importadora 81.91 HILARION-Abastos 22.09 Chan-cuau Chinara [23:30:50] LITOI WHILE Executiny se query off data or wint nighttrapshear SELECT SYyntax error [23:23:09] Query finished in 0.000 second(s). [23:23:56] Query finished in 0.002 second(s). ShipAddress Luisenstr. 48 Boulevard Tirou, 255 Rua do Paço, 67 2, rue du Commerce Starenweg 5 Rua do Paço, 67 59 rue de l'Abbaye Rua do Mercado, 12 Carrera 22 con Ave. Carlos Soublette #8-35 Haunter 21 I ShipCity Münster Charleroi Rio de Janeiro RJ Lyon Genève Rio de Janeiro RJ Reims Resende SP San Cristóbal Táchira Born ShipRegion NULL NULL NHEE NULE ShipPostal ShipCountry 44087 Germany B-6000 Belgium 05454-876 Brazil 69004 France 1204 Switzerland 05454-876 Brazil 51100 France 08737-363 Brazil 5022 Venezuela 2012 Switzerland 14 SELECT * FROM OrderDetails; 15 SELECT * FROM OrderHeaders; 16 SELECT * FROM Products; Grid view Form view ANTS SNOO 1 2 3 4 8 ProductID ProductName Status 1 Chai 2 Chang 1 3 Aniseed Syrup 4 Chef Anton's Cajun Seasoning 5 Chef Anton's Gumbo Mix 6 Grandma's Boysenberry Spread 7 Uncle Bob's Organic Dried Pears 8 Northwoods Cranberry Sauce 9 Mishi Kobe Niku 10 lura [23:25:09] Query Tinished in 0.000 second(s). [23:23:56] Query finished in 0.002 second(s). [23:24:13] Query finished in 0.000 second(s). Total rows loaded: 77 Supplier ID CategoryII QuantityPerUnit 1 10 boxes x 20 bags 1 1 1 24-12 oz bottles 1 2 12-550 ml bottles 2 48 - 6 oz jars 2 36 boxes 2 12-8 oz jars 7.12 - 1 lb pkgs. 2 12-12 oz jars 6 18 500 g pkgs. 9 12 200 maliars WEN NI 2 2 3 3 3 4 A CANON UnitPrice UnitsInStox UnitsOnOr ReorderLev Discontinue 10 FALSE 39 25 FALSE 25 FALSE 0 FALSE 0 TRUE 25 FALSE 10 FALSE 0 FALSE 0 TRUE A FALSE 19 22 21.35 25 30 40 97 21 13 53 120 15 6 29 0 40 70 0 0 0 0 0 0