Chapter 2 SQL Questions
2.53 Explain why you cannot use a subquery in your answer to question 2.52.
All information contained in the inventory table. Nothing is dependent on anything outside of the table. All of the rows in inventory are included, there is nothing to exclude what rows are included in the total items ordered and total items on hand
2.35 Explain the difference between the SQL built-in functions COUNT and SUM.
COUNT: Counts the number of rows in the table SUM: Calculates the sum of all values (numeric columns only)
2.54 Explain how subqueries and joins differ.
Other quizlet
2.55 Write an SQL statement to join WAREHOUSE and INVENTORY and include all rows of WAREHOUSE in your answer, regardless of whether they have any INVENTORY. Run this statement.
SELECT * FROM Warehouse LEFT JOIN inventory ON warehouse.warehouseID = inventory.WarehouseID GROUP BY warehouse.warehouseID, inventory.sku
2.22 Write an SQL statement to display all of the columns using the SQL asterisk (*) wildcard character.
SELECT * FROM inventory
2.23 Write an SQL statement to display all data on products having a QuantityOnHand greater than 0.
SELECT * FROM inventory WHERE QuantityOnHand > 0
2.34 Write an SQL statement that uses all of the SQL built-in functions on the QuantityOn-Hand column. Include meaningful column names in the result.
SELECT COUNT(QuantityOnHand) AS CountQ, SUM(QuantityOnHand) AS SumQ, AVG(QuantityOnHand) AS AvgQ, MIN(QuantityOnHand) AS MinQ, MAX(QuantityOnHand) AS MaxQ FROM inventory
2.24 Write an SQL statement to display the SKU and SKU_Description for products having QuantityOnHand equal to 0.
SELECT SKU, SKU_Description FROM inventory WHERE QuantityOnHand = 0
2.32 Write an SQL statement to show a unique SKU and SKU_Description for all products having a description that includes the word 'Climb'.
SELECT SKU, SKU_Description FROM inventory WHERE SKU_Description LIKE '%Climb%'
2.31 Write an SQL statement to show a unique SKU and SKU_Description for all products having an SKU description starting with 'Half-dome'.
SELECT SKU, SKU_Description FROM inventory WHERE SKU_Description LIKE 'Half-dome%'
2.33 Write an SQL statement to show a unique SKU and SKU_Description for all products having a 'd' in the third position from the left in SKU_Description.
SELECT SKU, SKU_Description FROM inventory WHERE SKU_Description LIKE '__d%'
2.25 Write an SQL statement to display the SKU, SKU_Description, and WarehouseID for products having QuantityOnHand equal to 0. Sort the results in ascending order by WarehouseID.
SELECT SKU, SKU_Description, WarehouseID FROM inventory WHERE QuantityOnHand = 0 ORDER BY WarehouseID asc
2.27 Write an SQL statement to display SKU, SKU_Description, and WarehouseID for all products that have a QuantityOnHand equal to 0 and a QuantityOnOrder greater than 0. Sort the results in descending order by WarehouseID and in ascending order by SKU.
SELECT SKU, SKU_Description, WarehouseID FROM inventory WHERE QuantityOnHand = 0 AND QuantityOnOrder > 0 ORDER BY WarehouseID desc, SKU asc
2.28 Write an SQL statement to display SKU, SKU_Description, and WarehouseID for all products that have a QuantityOnHand equal to 0 or a QuantityOnOrder equal to 0. Sort the results in descending order by WarehouseID and in ascending order by SKU.
SELECT SKU, SKU_Description, WarehouseID FROM inventory WHERE QuantityOnHand = 0 OR QuantityOnOrder = 0 ORDER BY WarehouseID desc, SKU asc
2.26 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.
SELECT SKU, SKU_Description, WarehouseID FROM inventory WHERE QuantityOnHand > 0 ORDER BY WarehouseID desc, SKU asc
2.29 Write an SQL statement to display the SKU, SKU_Description, WarehouseID, and QuantityOnHand for all products having a QuantityOnHand greater than 1 and less than 10. Do not use the BETWEEN keyword.
SELECT SKU, SKU_Description, WarehouseID, QuantityOnHand FROM inventory WHERE QuantityOnHand > 1 AND QuantityOnHand <10
2.30 Write an SQL statement to display the SKU, SKU_Description, WarehouseID, and QuantityOnHand for all products having a QuantityOnHand greater than 1 and less than 10. Use the BETWEEN keyword.
SELECT SKU, SKU_Description, WarehouseID, QuantityOnHand FROM inventory WHERE QuantityOnHand BETWEEN 2 and 9 Might be better way to do this? Wasn't sure how to exclude 1 and 10 with the between clause
2.56 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.
SELECT SKU, SKU_description, Department FROM sku_data WHERE Sku IN (SELECT SKU FROM catalog_sku_2013 WHERE CatalogPage OR DateOnWebsite is NOT NULL UNION SELECT SKU FROM catalog_sku_2014 WHERE CatalogPage OR DateOnWebsite is NOT NULL)
2.57 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, Department FROM sku_data WHERE Sku IN (SELECT SKU FROM catalog_sku_2013 WHERE CatalogPage is NOT NULL UNION SELECT SKU FROM catalog_sku_2014 WHERE CatalogPage is NOT NULL)
2.58 Write an SQL statement to display the SKU, SKU_Description, and Department of all SKUs that appear in both the Cape Codd 2013 Catalog (either in the printed catalog or on the Web site) and the Cape Codd 2014 catalog (either in the printed catalog or on the Web site).
SELECT SKU, SKU_description, Department FROM sku_data WHERE Sku IN (SELECT t1.sku FROM catalog_sku_2013 as t1 JOIN catalog_sku_2014 as t2 ON t1.sku = t2.sku WHERE t1.CatalogPage OR t1.DateOnWebSite IS NOT NULL AND t2.CatalogPage OR t2.DateOnWebSite is NOT NULL)
2.45 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.
SELECT Sku, sku_description, inventory.WarehouseID from inventory, warehouse where warehouse.WarehouseID in (select warehouseID from warehouse where manager like 'Lucille Smith') order by SKU asc, warehouseid asc
2.36 Write an SQL statement to display the WarehouseID and the sum of QuantityOnHand, grouped by WarehouseID. Name the sum TotalItemsOnHand and display the results in descending order of TotalItemsOnHand.
SELECT WarehouseID, SUM(QuantityOnHand) AS TotalItemsOnHand FROM inventory GROUP BY WarehouseID ORDER BY TotalItemsOnHand desc
2.52 Write an SQL statement to display the WarehouseID, the sum of QuantityOnOrder and sum of QuantityOnHand, grouped by WarehouseID and QuantityOnOrder. Name the sum of QuantityOnOrder as TotalItemsOnOrder and the sum of QuantityOnHand as TotalItemsOnHand. Use only the INVENTORY table in your SQL statement.
SELECT WarehouseID, SUM(QuantityOnOrder) AS TotalItemsOnOrder, SUM(QuantityOnHand) AS TotalItemsOnHand FROM inventory GROUP BY WarehouseID, QuantityOnOrder
2.21 Write an SQL statement to display all of the columns without using the SQL asterisk (*) wildcard character.
SELECT WarehouseID, SKU, SKU_Description, QuantityOnHand, QuantityOnOrder FROM inventory
2.20 Write an SQL statement to display unique WarehouseIDs.
SELECT distinct warehouseID FROM inventory
2.47 Write an SQL statement to show the SKU, SKU_Description, WarehouseID for all items stored in a warehouse managed by 'Lucille Smith'. Use a join using JOIN ON syntax.
SELECT oi.sku, sku_description, sd.warehouseid FROM inventory AS oi JOIN warehouse AS sd ON oi.WarehouseID=sd.warehouseID WHERE sd.Manager LIKE 'Lucille Smith' ORDER BY oi.sku, sd.warehouseID
Write an SQL statement to display SKU and SKU_Description.
SELECT sku, sku_description FROM inventory
Write an SQL statement to display SKU_Description and SKU.
SELECT sku_description, sku FROM inventory
2.50 Write an SQL statement to show the WarehouseID and average QuantityOnHand of all items stored in a warehouse managed by 'Lucille Smith'. Use a join using JOIN ON syntax.
SELECT t1.WarehouseID, AVG(t1.QuantityOnHand) AS AvgQuanOnHanSmith FROM inventory as t1 INNER JOIN warehouse as t2 ON t1.WarehouseID = t2.WarehouseID WHERE t2.Manager LIKE 'Lucille Smith' GROUP BY SKU
2.49 Write an SQL statement to show the WarehouseID and average QuantityOnHand of all items stored in a warehouse managed by 'Lucille Smith'. Use a join, but do not use JOIN ON syntax.
SELECT t1.WarehouseID, AVG(t1.QuantityOnHand) AS AvgQuanOnHanSmith FROM inventory as t1 JOIN warehouse as t2 WHERE t1.WarehouseID = t2.WarehouseID AND t2.Manager LIKE 'Lucille Smith' GROUP BY SKU
2.51 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 t2.WarehouseID, WarehouseCity, WarehouseState, Manager, SKU, SKU_Description, QuantityOnHand FROM warehouse as t1 JOIN inventory as t2 ON t1.warehouseID = t2.warehouseID WHERE t1.Manager LIKE 'Lucille Smith'
2.19 Write an SQL statement to display WarehouseID.
SELECT warehouseID FROM inventory
2.48 Write an SQL statement to show the WarehouseID and average QuantityOnHand of all items stored in a warehouse managed by 'Lucille Smith'. Use a subquery.
SELECT warehouseID, AVG(QuantityOnHand) AS AvgQuanOnHanPerWare FROM inventory WHERE warehouseID IN (SELECT WarehouseID FROM Warehouse WHERE Manager LIKE 'Lucille Smith') GROUP BY SKU
2.44 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.
select concat(inventory.sku_description, ' is located in ', warehouse.warehousecity) as ItemLocation from inventory, warehouse
2.46 Write an SQL statement to show the SKU, SKU_Description, and WarehouseID for all items stored in a warehouse managed by 'Lucille Smith'. Use a join, but do not use JOIN ON syntax.
select oi.sku, sku_description, sd.warehouseid from inventory as oi left join warehouse as sd on oi.WarehouseID=sd.warehouseID order by oi.sku, sd.warehouseID
2.41 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. Use the IN keyword.
select sku, sku_description, inventory.warehouseid, warehousecity, warehousestate from inventory, warehouse where warehouse.warehouseID in (select warehouseID from warehouse where WarehouseCity in ('Atlanta', 'Bangor', 'Chicago')) order by warehouse.warehousecity, sku asc, inventory.warehouseid asc
2.43 Write an SQL statement to display the SKU, SKU_Description, WarehouseID, WarehouseCity, and WarehouseState of all items not stored in the Atlanta, Bangor, or Chicago warehouse. Use the NOT IN keyword.
select sku, sku_description, inventory.warehouseid, warehousecity, warehousestate from inventory, warehouse where warehouse.warehouseID in (select warehouseID from warehouse where WarehouseCity not in ('Atlanta', 'Bangor', 'Chicago')) order by warehouse.warehousecity, sku asc, inventory.warehouseid asc
