Question

In: Computer Science

Given the tables: SKU_DATA (SKU, SKU_Description, Department, Buyer) WAREHOUSE (WarehouseID, WarehouseCity, WarehouseState, Manager, Squarefeet) INVENTORY (WarehouseID,...

  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

Solutions

Expert Solution

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:


Related Solutions

USE FOLLOWING TABLES TO ANSWER FOLLOWING QUESTIONS: WAREHOUSE (WarehouseID, WarehouseCity, WarehouseState, Manager, SquareFeet) INVENTORY (WarehouseID, SKU,...
USE FOLLOWING TABLES TO ANSWER FOLLOWING QUESTIONS: WAREHOUSE (WarehouseID, WarehouseCity, WarehouseState, Manager, SquareFeet) INVENTORY (WarehouseID, SKU, SKU_Description, QuantityOnHand, QuantityOnOrder) 1.Write an SQL statement to show a unique SKU and SKU_Description for all products having an SKU description starting with ‘Half-dome’. 2.Write an SQL statement to show a unique SKU and SKU_Description for all products having a description that includes the word 'Climb'. 3.Write an SQL statement to show a unique SKU and SKU_Description for all products with a ‘d’ in...
A: Paul Peterson is the inventory manager for Office Supplies, Inc., a large office supply warehouse....
A: Paul Peterson is the inventory manager for Office Supplies, Inc., a large office supply warehouse. The annual demand for paper punches is 20,000 units. The ordering cost is $100 per order, and the carrying cost is $5 per unit per year. What is the equation/formula to compute the economic order quantity? What is the EOQ? B: Paul Peterson is considering manufacturing hole-punch devices. As in Part A, above, the annual demand is 20,000 units. The setup cost is $100...
1)You are in charge of managing the inventory of a well-established SKU that has very steady...
1)You are in charge of managing the inventory of a well-established SKU that has very steady demand of 330 items per month, with no visible trend or seasonality. The cost of the product is 135 $/item and your firm uses an annual holding charge of 0.2 $/$/year (recall this is the same thing as saying 20 percent per year). You have found that there are two components to the ordering costs: labor and transportation. Labor costs are 57 $/order and...
Evaluate Honda Canada inventory and warehouse performances?
Evaluate Honda Canada inventory and warehouse performances?
The warehouse manager at Johnson Cor. is considering installing additional insulation in the warehouse. He estimates...
The warehouse manager at Johnson Cor. is considering installing additional insulation in the warehouse. He estimates that the annual savings would be a reduction in heating cost from the current $16,000 per year to approximately $12,000 per year. Johnson Corp. Desires to earn 8% ROI, and the cost of installing the insulation is expected to be $20,000. How many years does the warehouse have to run in order to justify the insulation project?
You are a warehouse manager who has observed that the current warehouse operation has certain weaknesses....
You are a warehouse manager who has observed that the current warehouse operation has certain weaknesses. You have found new solutions which would replace the earlier ones in order to patch up these weaknesses. While performing a qualitative analysis of the revised plan for the operations, which of the following options would you consider? The safety of the staff if the new solutions are implemented The difficulty of managing the new solutions The safety of the inventory itself with the...
Saul, a cotton merchant, had 50 bales of cotton in his warehouse. Brown, a prospective buyer,...
Saul, a cotton merchant, had 50 bales of cotton in his warehouse. Brown, a prospective buyer, inspected the bales in the warehouse, and entered into a signed written contract with Saul to buy the bales at a price of $1,000 per bale. Delivery was to be made by Saul to Brown’s factory in two weeks. Two days before the delivery date, Saul’s warehouse and the cotton that Brown had inspected were destroyed by fire caused by lightning. Brown demanded that...
Examine the structures of the DEPARTMENT and ASSET tables: DEPARTMENT ------------------------- DEPT_ID NUMBER(9) NOT NULL DEPT_ABBR...
Examine the structures of the DEPARTMENT and ASSET tables: DEPARTMENT ------------------------- DEPT_ID NUMBER(9) NOT NULL DEPT_ABBR VARCHAR2(4) DEPT_NAME VARCHAR2(25) NOT NULL MGR_ID NUMBER ASSET ----------- ASSET_ID NUMBER(9) NOT NULL ASSET_VALUE FLOAT ASSET_DESCRIPTION VARCHAR2(25) DEPT_ID NUMBER(9) The DEPT_ID column of the ASSET table has a FOREIGN KEY constraint referencing the DEPARTMENT table. You attempt to update the ASSET table using this statement: UPDATE asset SET dept_id =(SELECT dept_id FROM department WHERE dept_name =(SELECT dept_name FROM department WHERE dept_abbr = 'FINC')), asset_value...
Betty's Book and Music Store has two service departments, Warehouse and Data Center. Warehouse Department costs...
Betty's Book and Music Store has two service departments, Warehouse and Data Center. Warehouse Department costs are allocated on the basis of warehouse-hours. Data Center Department costs are allocated based on the number of computer hours. The costs of departments, warehouse-hours and number of computer hours are as follows: Support Departments Operating Departments Warehouse Data Center Music Books Departmental costs $ 60,000 $40,000 $60,000 $70,000 Warehouse-hours - 400 200 400 Computer hours 250 - 375 375 The total cost accumulated...
Betty's Book and Music Store has two service departments, Warehouse and Data Center. Warehouse Department costs...
Betty's Book and Music Store has two service departments, Warehouse and Data Center. Warehouse Department costs are allocated on the basis of warehouse-hours. Data Center Department costs are allocated based on the number of computer hours. The costs of departments, warehouse-hours and number of computer hours are as follows: Support Departments Operating Departments Warehouse Data Center Music Books Departmental costs $ 60,000 $40,000 $60,000 $70,000 Warehouse-hours - 400 200 400 Computer hours 250 - 375 375 The total cost accumulated...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT