W24 - Group 5 - PROG8651 - Group Project 1

A SHWIN R AMAKUMAR N AIR , H ET P ATEL , M EHAKDEEP K AUR , T ZU -C HIEN H UNG PROG8651 2/02/24 Question #3: 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. SQL Query SELECT OrderID , TotalPaid , S . ShipmentMode FROM CUSTOMERORDER AS C JOIN SHIPMETHOD S ON C . ShippingMethodID = S . ShippingMethodID ; Snippet of output

A SHWIN R AMAKUMAR N AIR , H ET P ATEL , M EHAKDEEP K AUR , T ZU -C HIEN H UNG PROG8651 2/02/24 Question #8: Within the Orders schema, list the total number of orders grouped by the different types of shipment modes. SQL Query SELECT COUNT ( C . OrderID ) AS TotalNumberOfOrders , S . ShipmentMode FROM CUSTOMERORDER C JOIN SHIPMETHOD S ON C . ShippingMethodID = S . ShippingMethodID GROUP BY S . ShipmentMode ; Snippet of output Question #9: 9) Within the PRODUCT and Orders schema, list all product details for products that have are not included on any order. SQL Query SELECT PRODUCT .* FROM PRODUCT WHERE PRODUCTID IN ( SELECT PRODUCT . ProductID FROM PRODUCT EXCEPT SELECT PRODUCTSELECTION . ProductID FROM PRODUCTSELECTION ) ; Snippet of output

A SHWIN R AMAKUMAR N AIR , H ET P ATEL , M EHAKDEEP K AUR , T ZU -C HIEN H UNG PROG8651 2/02/24 Question #14: Within the Orders schema, using only the ProductSelecƟon table, display the three Orders that have the greatest (highest) prices, grouped by OrderID. SQL Query SELECT TOP 3 P . OrderID , SUM ( P . UnitPriceSold ) AS TotalUnitPrice FROM PRODUCTSELECTION P GROUP BY OrderID ORDER BY TotalUnitPrice DESC ; Snippet of output Question #15: 15) Within the Orders schema, count the total number of products that have been purchased on all orders - Do not group orders SQL Query SELECT COUNT ( ProductID ) AS TotalNumberOfProductPurchased FROM PRODUCTSELECTION ; Snippet of output

A SHWIN R AMAKUMAR N AIR , H ET P ATEL , M EHAKDEEP K AUR , T ZU -C HIEN H UNG PROG8651 2/02/24 Question #20: 20) Within the ORDERS and PRODUCT schemas, for each Customer Order, display the OrderID, CustomerID, ProductID and the DescripƟon for each of the products. SQL Query SELECT C . OrderID , C . CustomerID , P . ProductID , PR . Description FROM CUSTOMERORDER C JOIN PRODUCTSELECTION P ON C . OrderID = P . OrderID JOIN PRODUCT PR ON P . ProductID = PR . ProductID ; Snippet of output