We consider product, depot and stock of HW2. • #prod should be prodid • #dep should be depid. • The primary key of product is prodid. The primary key of depot is depid. The (composite) primary key of stock is prodid, depid. • prodid in stock is a foreign key referring the primary key prodid in product. • depid in stock is a foreign key referring the primary key depid in depot. Write the following queries in SQL. 1. #prods whose name begins with a 'p' and are less than $300.00. 2. Names of the products stocked in "d2". (a) without in/not in (b) with in/not in 3. #prod and names of the products that are out of stock (quantity <= 0). (a) without in/not in (b) with in/not in
We consider product, depot and stock of HW2.
• #prod should be prodid
• #dep should be depid.
• The primary key of product is prodid. The primary key of depot is depid. The (composite) primary key of stock is prodid, depid.
• prodid in stock is a foreign key referring the primary key prodid in product.
• depid in stock is a foreign key referring the primary key depid in depot.
Write the following queries in SQL.
1. #prods whose name begins with a 'p' and are less than $300.00.
2. Names of the products stocked in "d2".
(a) without in/not in
(b) with in/not in
3. #prod and names of the products that are out of stock (quantity <= 0).
(a) without in/not in
(b) with in/not in
4. Addresses of the depots where the product "p1" is stocked.
(a) without exists/not exists and without in/not in
(b) with in/not in
(c) with exists/not exists
5. #prods whose price is between $250.00 and $400.00.
(a) using intersect.
(b) without intersect.
6. Number of products that are out of stock (quantity <= 0).
7. Average of the prices of the products stocked in the "d2" depot.
8. #deps of the depot(s) with the largest capacity (volume).
9. Sum of the stocked quantity of each product.
10. Products names stocked in at least 3 depots.
(a) using count
(b) without using count
11. #prod stocked in all depots.
(a) using count
(b) using exists/not exists
Trending now
This is a popular solution!
Step by step
Solved in 6 steps with 13 images
