ISM218 HW2-SJ

docx

School

Forsyth Technical Community College *

*We aren’t endorsed by this school

Course

218

Subject

Industrial Engineering

Date

Dec 6, 2023

Type

docx

Pages

Uploaded by JusticeUniverseKangaroo379

ISM218 Homework 02 (100 points) Online Submission through Canvas Course Site General Instructions 1. Rename this file with your full name before submission. 2. Paste your solution below each question (a sample is given). 3. Use SQL platform to run the queries. 4. Make sure the details of your provided illustrations are clear. ZAGI RETAIL SALES DATABASE (RELATIONAL SCHEMA)

SAMPLE QUESTION Display the CustomerName and CustomerZip for all customers (Use Customer table). Paste the screenshot like below (your user id, query code, and query result table must be visible).

Question 1 to 10 (10 points each) 1. Display the ProductID, ProductName, and ProductPrice for products with a ProductPrice of $100 or higher (use table Product). USE HW2; select productid, productname, productprice from product where productprice >= 100

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

2. Display the ProductID, ProductName, ProductPrice, and VendorName for all products. Sort the results by ProductID (use tables Product and Vendor). USE HW2; SELECT p.ProductID, p.ProductName, p.ProductPrice, v.VendorName FROM Product p JOIN Vendor v ON p.VendorID = v.VendorID ORDER BY p.ProductID;

3. Display the ProductID, ProductName, ProductPrice, VendorName, and CategoryName for all products. Sort the results by ProductID (use tables Product, Vendor, and Category). USE HW2; SELECT ProductID, ProductName, ProductPrice, VendorName, CategoryName FROM Product JOIN Vendor ON Product.VendorID = Vendor.VendorID JOIN Category ON Product.CategoryID = Category.CategoryID ORDER BY ProductID;

4. Display the RegionID, RegionName, and number of stores in the region for all regions (use tables Region and Store). USE HW2; SELECT R.RegionID, R.RegionName, COUNT(S.StoreID) AS NumberOfStores FROM REGION R LEFT JOIN STORE S ON R.RegionID = S.RegionID GROUP BY R.RegionID, R.RegionName;

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

5. Display the ProductID, ProductName, and ProductPrice for products in the category whose CategoryName value is Camping. Sort the results by ProductID (use tables Product and Category). USE HW2; SELECT ProductID,ProductName, ProductPrice FROM Product JOIN Category ON Product.CategoryID = Category.CategoryID WHERE CategoryName = 'Camping' ORDER BY ProductID;

6. For each product category, display the CategoryID, CategoryName, and average price of a product in the category (use tables Product and Category). USE HW2; SELECT C.CategoryID, C.CategoryName, AVG(P.ProductPrice) AS AveragePrice FROM Category AS C LEFT JOIN Product AS P ON C.CategoryID = P.CategoryID GROUP BY C.CategoryID, C.CategoryName;

7. For each product category, display the CategoryID and the total number of items purchased in the category (use tables Product and Soldvia). USE HW2; SELECT P.CategoryID, SUM(S.Quantity) AS TotalItemsPurchased FROM Product AS P JOIN Soldvia AS S ON P.ProductID = S.ProductID GROUP BY P.CategoryID;

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

8. Display the TID and the total number of items (of all products) sold within the transaction for all sales transactions whose total number of items (of all products) sold within the transaction is greater than five (use table Soldvia). USE HW2; SELECT TID, SUM(Quantity) AS TotalItemsSold FROM Soldvia GROUP BY TID HAVING SUM(Quantity) > 5;

9. Display the ProductID and ProductName of the cheapest product (use table Product). USE HW2; SELECT ProductID, ProductName FROM Product WHERE ProductPrice = (SELECT MIN(ProductPrice) FROM Product);

10. Display the ProductID, ProductName, and VendorName for products whose price is below the average price of all products (use tables Product and Vendor). USE HW2; SELECT P.ProductID, P.ProductName, V.VendorName FROM Product P INNER JOIN Vendor V ON P.VendorID = V.VendorID WHERE P.ProductPrice < (SELECT AVG(ProductPrice) FROM Product);

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

ISM218 HW2-SJ

Related Documents