Write an SQL statement to display the SKU, SKU_Description, WarehouseID, Warehou

Question

Write an SQL statement to display the SKU, SKU_Description, WarehouseID, WarehouseCity, and WarehouseState for all items stored in the Atlanta, Bangor, or Chicago warehouse. Provide a screenshot of the result as well.

Create a VIEW to display the SKU, SKU_Description, WarehouseID, WarehouseCity, and WarehouseState of all items not stored in the Atlanta, Bangor, or Chicago warehouse. Provide a screenshot of the records in the view as well.

Write an SQL statement to produce a single column called ItemLocation that combines the SKU_Description, the phrase “is located in”, and WarehouseCity. Do not be concerned with removing leading or trailing blanks. Provide a screenshot of the result as well.

Write an SQL statement to show the SKU, SKU_Description, WarehouseID for all items stored in a warehouse managed by ‘Lucille Smith’. Use a subquery. Provide a screenshot of the result as well.

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. Provide a screenshot of the result as well.

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

1.

SELECT SKU, SKU_Description, w.WarehouseID, WarehouseCity, WarehouseState FROM
INVENTORY, WAREHOUSE w
WHERE WarehouseCity in ('Atlanta','Bangor','Chicago');

2.

CREATE OR REPLACE VIEW VW_DISP_DETAILS as
SELECT SKU, SKU_Description, WAREHOUSE.WarehouseID, WarehouseCity, WarehouseState FROM
INVENTORY, WAREHOUSE WHERE WarehouseCity NOT in ('Atlanta','Bangor','Chicago');
SELECT * FROM VW_DISP_DETAILS;

3.we use + smbol to concatente

SELECT (SKU_Description+' is in a warehouse in '+WarehouseCity) AS ItemLocation FROM INVENTORY;

4.--Subquery query iside a qurey
SELECT SKU, SKU_Description, WarehouseID FROM INVENTORY WHERE WarehouseID = (SELECT WarehouseID FROM WAREHOUSE WHERE Manager='Lucille Smith');

5.--JOIN keyword implicitly says inner join and we use alisas to join the keys from diferent tables
SELECT SKU, SKU_Description, ware.WarehouseID FROM INVENTORY invntry JOIN WAREHOUSE warehse ON invntry.WarehouseID = warehse.WarehouseID WHERE Manager = 'Lucille Smith';

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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