23F - Dudeja, Jai - PROG8651 - Group Assignment 1

Group Assessment #1 SQL Queries & Output PROG8651 – SECTION #8 GROUP MEMBER NAMES - JAI DUDEJA, RICHARD SAJAN, KHUSHBOO PAHWA, JERRY GEORGE 9/29/2023 CONESTOGA COLLEGE – PROGRAM 1372 – 23F

Friday, September 29, 2023 Group Assessment #1 PROG8651 – SECTION #8 Question #1: Within the PRODUCT schema, show all records from the ProductCategory table. SELECT * FROM ProductCategory ; Question #2: Within the ORDERS schema, show all data from the CustomerOrder and ShipmentMethod tables:  Only show records where there are row matches between the PK and FK fields.  The join of equality (“equijoin”) must be listed in the WHERE statement. SELECT DISTINCT * FROM CustomerOrder , ShipmentMethod WHERE CustomerOrder . ShipmentMethodID = ShipmentMethod . ShipmentMethodID ; Question #3: Within the Orders schema, without using a WHERE statement, write a single SQL statement that shows the following data:  From the CustomerOrder table, show the OrderID and TotalPaid.  From the ShipmentMethod table, show the shipment mode.  Hint, you’ll need to make use of the ‘ON’ command. SELECT OrderID , TotalPaid , ShipmentMode FROM CustomerOrder JOIN ShipmentMethod ON CustomerOrder . ShipmentMethodID = ShipmentMethod . ShipmentMethodID ;

Friday, September 29, 2023 Group Assessment #1 PROG8651 – SECTION #8 Question #7: Within the PRODUCT schema, list the description for all products that have a product review rang of 4 or higher. SELECT ProductDescription FROM Product WHERE ProductID IN ( SELECT ProductID FROM Review WHERE Rating >= 4 ); Question #8: Within the Orders schema, list the total number of orders grouped by the different types of shipment modes. SELECT COUNT (*) AS TotalOrders FROM CustomerOrder JOIN ShipmentMethod ON CustomerOrder . ShipmentMethodID = ShipmentMethod . ShipmentMethodID GROUP BY ShipmentMethod . ShipmentMode ; Question #9: Within the PRODUCT and Orders schema, list all product details for products that are not included on any order. SELECT * FROM Product WHERE ProductID NOT IN ( SELECT DISTINCT ProductID FROM ProductSelection );

Friday, September 29, 2023 Group Assessment #1 PROG8651 – SECTION #8 Question #10: Within the Orders schema, Display OrderID and CustomerID for any orders that have an actual shipping date that occurred in the month of September. SELECT OrderID , CustomerID FROM CustomerOrder WHERE SUBSTRING ( ActualDateShipped , 6 , 2 ) = '09' ; Question #11: Within the Orders schema, Display OrderID and CustomerID for any orders that have an estimated shipping date that occurred before the year of 2022. SELECT OrderID , CustomerID FROM CustomerOrder WHERE YEAR ( CONVERT ( DATE , EstDateShipped , 120 )) < 2022 ; Question #12: Within the Orders schema, Display OrderID and CustomerID for any orders that have an actual shipping date that occurred in the month of October but did not occur in the Year of 2023. SELECT OrderID , CustomerID FROM CustomerOrder WHERE SUBSTRING ( ActualDateShipped , 6 , 2 ) = '10' AND SUBSTRING ( ActualDateShipped , 1 , 4 ) <> '2023' ; Question #13: Within the PRODUCT schema, list the item with the greatest (highest) price. SELECT TOP 1 * FROM Product ORDER BY Price DESC ; Question #14: Within the Orders schema, using only the ProductSelection table, display the three Orders that have the greatest (highest) prices, grouped by OrderID. SELECT TOP 3 OrderID , MAX ( UnitPriceSold ) AS Price FROM ProductSelection GROUP BY OrderID ORDER BY Price DESC

Friday, September 29, 2023 Group Assessment #1 PROG8651 – SECTION #8 FROM CustomerOrder WHERE OrderID IN ( SELECT OrderID FROM ProductSelection WHERE ProductID IN ( SELECT ProductID FROM Product WHERE Package = 'Gift Box' ) ); Question #19: Within the ORDERS and PRODUCT schema, show any order [OrderID] and Customer [CustomerID] where there is a single item which has a weight greater than 1 kilogram.  Evaluate the weight of each item independently, not the combined weight.  Consider all possibilities. SELECT OrderID , CustomerID FROM CustomerOrder WHERE OrderID IN ( SELECT OrderID FROM ProductSelection WHERE ProductID IN ( SELECT ProductID FROM Product WHERE WeightQty > 1 ) GROUP BY ProductSelection . OrderID HAVING COUNT ( ProductSelection . OrderID ) = 1 ); Question #20: Within the ORDERS and PRODUCT schemas, for each Customer Order, display the OrderID, CustomerID, ProductID and the Description for each of the products. SELECT CustomerOrder . OrderID , CustomerOrder . CustomerID , ProductSelection . ProductID , Product . ProductDescription AS Descriptions FROM (( CustomerOrder JOIN ProductSelection ON CustomerOrder . OrderID = ProductSelection . OrderID ) JOIN Product ON ProductSelection . ProductID = Product . ProductID );

Friday, September 29, 2023 Group Assessment #1 PROG8651 – SECTION #8