Write the following queries in SQL. 1. #prods whose name begins with a 'p' and are...

  

Transcribed Image Text:

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 (quantity0), (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 infnot 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 We consider product, depot and stock of HWZ. #prod should be prodid #dep should be depid The primary key of product is pcosid. The primary kay of depot is dapid. 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 (quantity0), (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 infnot 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 We consider product, depot and stock of HWZ. #prod should be prodid #dep should be depid The primary key of product is pcosid. The primary kay of depot is dapid. 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. . ● . . ●

Answer rating: 100% (QA)

1 prods whose name begins with a p and are less than 30000 SQL SELECT ... View the full answer