Query writing and relational algebra Expression Write the following sql queries

Question

Query writing and relational algebra Expression Write the following sql queries using the Cape Codd DB on bottom

8. Find out other warehouses whose average quantity on hand is not smaller than That of Atlanta warehouse.

9. List sku’s and descriptions for all products whose sku starts with a "2". Use the BETWEEN operator. You may assume all sku's have 6 digits.

10. List the warehouses that currently have on average less than 225 items in stock of the products they carry. Use the HAVING operator.

11. List all order items for products currently out of stock in Atlanta. Use a subquery.

12. Get a list of buyers and their departments for any products out of stock (at any warehouse, not all warehouses). Use a join.

13. Get a list of departments and the total items on order for each department, sorted from largest to smallest quantity.

Explanation / Answer

8. average can be calculated as follows select Avg(QuantityOnHand) from INVENTORY WHERE warehouse='Atlanta' which gives 232.75. All other warehouses have average less than this, which can be verified via

select warehouse, Avg(QuantityOnHand) from INVENTORY group by warehouse;
+------------+---------------------+
| warehouse | Avg(QuantityOnHand) |
+------------+---------------------+
| Atlanta | 232.7500 |
| Chicago | 217.0000 |
| New Jersey | 228.1250 |
| Seattle | 143.7500 |
+------------+---------------------+

final query : select warehouse from INVENTORY group by warehouse having Avg(QuantityOnHand)> (select Avg(QuantityOnHand) from INVENTORY WHERE warehouse='Atlanta' );

output: 0 rows.

9.select sku,sku_description from SKU_DATA where sku between 200000 and 299999; ( we could have used sku like '2%' but asked to use between.

o/p:

+--------+--------------------------+
| sku | sku_description |
+--------+--------------------------+
| 201000 | Half-dome Tent |
| 202000 | Half-dome Tent Footprint |

+--------+--------------------------+

10.select * from INVENTORY having QuantityOnHand<225;
+--------+------------+----------------------------+----------------+-----------------+
| SKU | Warehouse | Description | QuantityOnHand | QuantityOnOrder |
+--------+------------+----------------------------+----------------+-----------------+
| 100100 | Chicago | Std. Scuba Tank, Yellow | 100 | 50 |
| 100100 | New Jersey | Std. Scuba Tank, Yellow | 100 | 0 |
| 100100 | Seattle | Std. Scuba Tank, Yellow | 200 | 0 |
| 100200 | Atlanta | Std. Scuba Tank, Magenta | 200 | 30 |
| 100200 | Chicago | Std. Scuba Tank, Magenta | 75 | 75 |
| 100200 | New Jersey | Std. Scuba Tank, Magenta | 100 | 100 |
| 101100 | Atlanta | Dive Mask, Small Clear | 0 | 500 |
| 101100 | Chicago | Dive Mask, Small Clear | 0 | 500 |
| 101200 | Atlanta | Dive Mask, Med Clear | 100 | 500 |
| 101200 | Chicago | Dive Mask, Med Clear | 50 | 500 |
| 201000 | Atlanta | Half-dome Tent | 2 | 100 |
| 201000 | Chicago | Half-dome Tent | 10 | 250 |
| 201000 | Seattle | Half-dome Tent | 0 | 250 |
| 202000 | Atlanta | Half-dome Tent Footprint | 10 | 250 |
| 202000 | Chicago | Half-dome Tent Footprint | 1 | 250 |
| 202000 | New Jersey | Half-dome Tent Footprint | 100 | 0 |
| 202000 | Seattle | Half-dome Tent Footprint | 0 | 200 |
| 301000 | New Jersey | Light Fly Climbing Harness | 0 | 250 |
| 301000 | Seattle | Light Fly Climbing Harness | 0 | 250 |
| 302000 | Seattle | Locking carabiner | 0 | 1000 |
+--------+------------+----------------------------+----------------+-----------------+

11. to get all out of stock items in atlanta. we can use

select sku from INVENTORY where warehouse='Atlanta' and QuantityOnHand<QuantityOnOrder;

and now to get all order items from above query

select * from ORDER_ITEM where sku in(select sku from INVENTORY where warehouse='Atlanta' and QuantityOnHand<QuantityOnOrder);
+-------------+--------+----------+--------+---------------+
| OrderNumber | SKU | Quantity | Price | ExtendedPrice |
+-------------+--------+----------+--------+---------------+
| 2000 | 101100 | 4 | 50.00 | 200.00 |
| 3000 | 101100 | 2 | 50.00 | 100.00 |
| 2000 | 101200 | 2 | 50.00 | 100.00 |
| 3000 | 101200 | 1 | 50.00 | 50.00 |
| 1000 | 201000 | 1 | 300.00 | 300.00 |
| 1000 | 202000 | 1 | 130.00 | 130.00 |

12.select distinct buyer,department from SKU_DATA a,INVENTORY b where b.QuantityOnHand<b.QuantityOnOrder and a.sku=b.sku;
+--------------+--------------+
| buyer | department |
+--------------+--------------+
| Nancy Meyers | Water Sports |
| Cindy Lo | Camping |
| Jerry Martin | Climbing |
+--------------+--------------+

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

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