WRITE SQL STATEMENTS FOR THE FOLLOWING Create a VIEW to show the SKU, SKU_Descri

Question

WRITE SQL STATEMENTS FOR THE FOLLOWING

Create a VIEW to show the SKU, SKU_Description, and WarehouseID for all items stored in a warehouse managed by ‘Lucille Smith’. Use an implicit/explicit join. Provide a screenshot of the records in the view as well.

Write an SQL statement to show the WarehouseID and average QuantityOnHand of all items stored in a warehouse managed by ‘Lucille Smith’. Provide a screenshot of the result as well.

Write an SQL statement to show the WarehouseID, WarehouseCity, WarehouseState, Manager, SKU, SKU_Description, and QuantityOnHand of all items with a Manager of ‘Lucille Smith’. Use a join using JOIN ON syntax.

Create a VIEW to join WAREHOUSE and INVENTORY and include all rows of WAREHOUSE in your answer, regardless of whether they have any INVENTORY. Provide a screenshot of the records in the view as well. Write an SQL statement to display the SKU, SKU_Description, and Department of all SKUs that appear in either the Cape Codd 2013 Catalog (either in the printed catalog or on the Web site) or the Cape Codd 2014 Catalog (either in the printed catalog or on the Web site) or both. NOTE: If it’s on the website, then the catalogpage is null.

Write an SQL statement to display the SKU, SKU_Description, and Department of all SKUs that appear in either the Cape Codd 2013 Catalog (only in the printed catalog itself) or the Cape Codd 2014 catalog (only in the printed catalog itself) or both.

Explanation / Answer

Hi, below is the full sql queries i have written, since you didn't gave the schema of your tables, it was difficult to interpret which attributes belong to which table.

I have written the queries based on my understanding, if any queries, i request you to update your question with the schema for perfect solution.
Hope this will help. Thanks

<---1.Create a VIEW to show the SKU, SKU_Description, and WarehouseID for all items stored in a warehouse managed by ‘Lucille Smith’.

CREATE VIEW VIEW1 AS
SELECT ITEM,SKU, SKU_Description,WarehouseID
FROM INVENTORY A1, WAREHOUSE A2
WHERE A1.ITEM = A2.ITEM AND A2.MANAGER = ‘Lucille Smith’;

<---2.Write an SQL statement to show the WarehouseID and average QuantityOnHand of all items stored in a warehouse managed by ‘Lucille Smith';

SELECT ITEM,WarehouseID,AVG(QuantityOnHand)
FROM WAREHOUSE
WHERE MANAGER = ‘Lucille Smith’
GROUP BY ITEM,WarehouseID;

<---3. Write an SQL statement to show the WarehouseID, WarehouseCity, WarehouseState, Manager, SKU, SKU_Description,
<---and QuantityOnHand of all items with a Manager of ‘Lucille Smith’. Use a join using JOIN ON syntax.

SELECT A1.ITEM,A1.WarehouseID, A1.WarehouseCity, A1.WarehouseState, A1.Manager, A2.SKU, A2.SKU_Description,A1.QuantityOnHand
FROM WAREHOUSE A1
LEFT JOIN INVENTORY A2 ON A1.ITEM = A2.ITEM
WHERE A1.MANAGER = ‘Lucille Smith’;

<---4.Create a VIEW to join WAREHOUSE and INVENTORY and include all rows of WAREHOUSE in your answer,
---regardless of whether they have any INVENTORY. Provide a screenshot of the records in the view as well.

CREATE VIEW VIEW2 AS
SELECT A1.*,A2.* FROM
INVENTORY A1, WAREHOUSE A2;

---5.Write an SQL statement to display the SKU, SKU_Description, and Department of all SKUs that appear in either the Cape Codd 2013 Catalog (
---either in the printed catalog or on the Web site) or the Cape Codd 2014 Catalog (either in the printed catalog or on the Web site) or both.
---NOTE: If it’s on the website, then the catalogpage is null.

SELECT SKU, SKU_Description,SKU_Department
FROM INVENTORY
WHERE catalogpage = 'Cape Codd 2013' OR catalogpage = 'Cape Codd 2014' OR catalogpage IS NULL OR (catalogpage = 'Cape Codd 2013' AND catalogpage = 'Cape Codd 2014');

---6.Write an SQL statement to display the SKU, SKU_Description, and Department of all SKUs that appear in either the Cape Codd 2013 Catalog
---(only in the printed catalog itself) or the Cape Codd 2014 catalog (only in the printed catalog itself) or both.

SELECT SKU, SKU_Description,SKU_Department
FROM INVENTORY
WHERE catalogpage = 'Cape Codd 2013' OR catalogpage = 'Cape Codd 2014' OR (catalogpage = 'Cape Codd 2013' AND catalogpage = 'Cape Codd 2014');

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.