a,b,c,d Use both INVENTORY and WAREHOUSE tables to write SOL statements for the

Question

a,b,c,d

Use both INVENTORY and WAREHOUSE tables to write SOL statements for the following queries. Make sure to include the screen capture of the SQL output that you get after executing each of the query. (5 points each) 3. a) Display the SKU, SKU_Description, WarehouselD for all items stored in a warehouse managed by Lucille Smith. Use a subquery. b) Display the SKU, SKU_Description, and WarehouselD for all items stored in a warehouse managed by 'Lucille Smith'. Use a join, but do not use JOIN ON syntax. Display the SKU, SKU_Description, WarehouselD for all items stored in a warehouse managed by 'Lucille Smith'. Use a join using JOIN ON syntax. c) d) Display the WarehouselD, WarehouseCity, WarehouseState, Manager, SKU, SKU_Description, and QuantityOnHand of all items with a Manager of Lucille Smith'. Use a join using JOIN ON syntax.

Explanation / Answer

A) SELECT SKU, SKU_Description, WarehouseID FROM INVENTORY WHERE WarehouseID = (SELECT WarehouseID FROM WAREHOUSE WHERE Manager='Lucille Smith');

Please refer above query (A) contains two queries main query and subquery. Inside parentheses query is subquery. rest of all will main query. First execute inside parentheses query called subquery. It will get one warehouseId from warehouse table where manager is 'Lucille Smith'. after execute subquery main query will execute. so it will print SKU, SKU_Description, WarehouseID where warehouseId result of subquery.

Example: subquery get only one warehouseId= 12345
So main query is look like SELECT SKU, SKU_Description, WarehouseID FROM INVENTORY WHERE WarehouseID =12345;

Note* sub querys always return only one column

B) SELECT SKU, SKU_Description, WAREHOUSE.WarehouseID FROM INVENTORY join WAREHOUSE WHERE INVENTORY.WarehouseID=WAREHOUSE.WarehouseID AND Manager = 'Lucille Smith';

*Notice that relationship between the two tables is the "WarehouseID" column.

*when process match INVENTORY WarehouseID and WAREHOUSE WarehouseID it will search for manager is Lucille Smith then it will create one virtual table in process and disply the SKU, SKU_description, warehouseId.

C)SELECT SKU, SKU_Description, WAREHOUSE.WarehouseID FROM INVENTORY JOIN WAREHOUSE ON INVENTORY.WarehouseID = WAREHOUSE.WarehouseID WHERE Manager = 'Lucille Smith';

ON is a part of each individual join.
ON can only refer to the fields of previously used tables.
rest of things same as question B.

D)SELECT WAREHOUSE.WarehouseID, WarehouseCity, WarehouseState, Manager, SKU, SKU_Description, QuantityOnHand FROM INVENTORY JOIN WAREHOUSE ON INVENTORY.WarehouseID = WAREHOUSE.WarehouseID WHERE Manager = 'Lucille Smith';

* Again relationship between the two tables INVENTORY and WAREHOUSE is the "WarehouseID" column so it will create virtual table in its process where manager is Lucille Smith.

*This query select more parameter than question (C) warehouseId of warehouse table, warehouseCity, WarehouseState, Manager, SKU, SKU_Description and QuantityOnHand.

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

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