Question

1. Given the tables: SKU_DATA (SKU, SKU_Description, Department, Buyer)

WAREHOUSE (WarehouseID, WarehouseCity, WarehouseState, Manager, Squarefeet)

INVENTORY (WarehouseID, SKU, SKU_Description, QuantityOnHand, QuantityOnOrder)

1. Write an SQL statement to display SKU and SKU_Description.
2. Write an SQL statement to display the SKU, SKU_Description, and WarehouseID for products having QuantityOnHand greater than 0. Sort the results in descending order by WarehouseID and ascending order by SKU.
3. 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
4. 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
5. Write an SQL statement to show the WarehouseID and average QuantityOnHand of all items stored in a warehouse managed by ‘Lucille Smith’.

All for MYSQL Database

Answer 1

Before explaining the queries firstly the creation and insertion of data in those tables must be done. So, here these three tables are created along with their attributes and also some data is inserted.

The data inserted in the tables are assumed to what the particular attribute can store. Insert statements are not shown as it is not necessary. Here is the contents of these three tables.

SKU_DATA:

WAREHOUSE:

INVENTORY:

The above data will be helpful to keep track of the result of the SQL queries.

a. Write an SQL statement to display SKU and SKU_Description.

SQL Code:

SELECT SKU, SKU_Description 
FROM SKU_DATA;

(I)The above SQL query shows two fields of the table SKU_DATA. By using select statement these two required fields which are SKU and SKU_Description are selected.

(ii) Here the table name must be written to represent from where the data is fetched. So, after writing the field names from SKU_DATA is written.

Screenshot:

b. Write an SQL statement to display the SKU, SKU_Description, and WarehouseID for products having QuantityOnHand greater than 0. Sort the results in descending order by WarehouseID and ascending order by SKU.

SQL Code:

SELECT SKU, SKU_Description, WarehouseID
FROM INVENTORY
WHERE QuantityOnHand > 0
ORDER BY WarehouseID 
DESC, SKU;

(I) All the required fields are already present in the table inventory. So, from the table fields SKU, SKU_Description and WarehouseID is selected. After that we have to provide condition which is quantityOnHand must be greater than 0, it is written using where clause along with the condition.

(ii) As it is suggested the warehouseid will be sorted in descending order and sku so Orderby is used followed by the field WarehouseID and the keyword DESC written for the descending order. In the second field the field SKU is written.

(iii) If nothing provided, order by will sort the data in ascending order that's why here desc must be written for sorting it in descending order. For the same reason nothing is written for the field SKU which will be automatically sort in ascending order.

Screenshot:

c. 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.

SQL Code:

SELECT 
Inv.SKU, Inv.SKU_Description, WRE.WarehouseID, WRE.WarehouseCity, WRE.WarehouseState
FROM INVENTORY Inv, WAREHOUSE WRE
WHERE Inv.WarehouseID=WRE.WarehouseID
AND (WRE.WarehouseCity = 'Atlanta' OR WRE.WarehouseCity = 'Bangor' OR WRE.WarehouseCity = 'Chicago');

(I) The above question requires to join two tables which are warehouse and inventory. Both the tales have the common attribute between them, WarehouseID to which we will perform joining. In the SQL query a short name for the tables is given at the time of writing the from statement Inventory as Inv and Warehouse as were.

(ii) The required fields are selected using those short names like Inv.SKU, wre.warehouseCity. For the condition where clause is used where warehouseIds of both the tables will be the same.

(iii)There is one more condition which states that the data will be shown from the city Atlanta, Bangor and Chicago. For this the warehouseCity is checked with the given cities using OR. Two conditions are combined into one using the AND operator.

Screenshot:

d. 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.

SQL Code:

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

(I) The above query selects the fields present in the table inventory, SKU, SKU_Description and WarehouseID using Select statement.

(ii) As it is suggested to use subquery So in the condition the subquery is written where only those warehouseIDs will be selected whose manager is Lucille Smith. Where clause is used using IN along with the subquery. The returned value which is warehouseId present in the warehouse table, of the subquery is checked with the warehouseID in the inventory table.

Screenshot:

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

SQL Code:

SELECT Inv.WarehouseID, AVG(Inv.QuantityOnHand)
FROM INVENTORY Inv, WAREHOUSE wre
WHERE Inv.WarehouseID = wre.WarehouseID
AND wre.Manager = 'Lucille Smith'
GROUP BY Inv.WarehouseID;

(I) Here also the joining of tables are done using by giving short names to the tables and condition checking the warehouseIDs of both the tables. For calculating average of a particular field aggregate function AVG is used with the field.

(ii) The select statement selects the warehouseID of the inventory table and the avg of quantityonhand. The where clause checks two condition- one is for the warehouseID for both the tables and the manager of the warehouse. The manager of warehouse must be Lucille Smith which is done like that wre.Manager='Lucille Smith'.

(iii) Also group by clause is used for grouping all the data and calculating the average value for quantity on hand. The grouping is done using the warehouseID of the inventory table.

Screenshot: