For each information request below, formulate a single SQL query to produce the

Question

For each information request below, formulate a single SQL query to produce the required information.

1) List the names of manufactureres of black sneakers that cost over $70. Use a subquery in the Where clause. Note: Categories are lower case in the database; Colors are capitalized.

2) List the product names of boots made in New Hampshire. Use a subquery in the where clause. Category data is lower case in database.

3) List the name and list price of products that sold in quantity of 3. Use a subquery in the where clause.

4) List the names and list prices of products that sold only in quatity of 2. (in other words, in quantity of 2 but not in any other quantities). Use subqueries in the where clause.

5) List the name and list price of products that were sold on a sale for which the shipping cost was over $60. (use two levels of subqueries in where clauses.)

*** I need help with the problems above. I have already asked this question on here but didnt recieve a clear answer. I am looking for how to do the queries themselves and also an explanation of why you did what. If you could put the queries in a clear way to understand that would be awesome. For example if you could seperate the SELECT, FROM, WHERE , ETC clauses by typing them in all caps so I can see where a new line is supposed to begin. ****

Customer customerID First Name LastName Street Address City State PostalCode Count Phone Saleltem Product ID tem Size SaleID Quanti SalePrice Sale SaleID SaleDate Customer ID Ta Shipping Salary Employee EmployeeID Salary Wage Employee EmployeeID age MaxHours Product ProductID ProductName anufacturerID Composition List Price Gender Catego Colo Description Inventoryltem Product ID tem Size Qty OnHand ItemSize tem Size Employee EmployeeID First Name LastName ddress City State ZIP Phone ManagerID SSN Ema ddress HireDate Manufacturer ManufacturerID ManufacturerName Address1 ddress2 City State PostalCode Phone Fax Contact URL Purchaseltem Product ID tem Size PurchaseID Quanti PurchasePrice Purchase PurchaseID PurchaseDate Employee ID ExpectedDeliveryDate ManufacturerID Shipping

Explanation / Answer

1) List the names of manufactureres of black sneakers that cost over $70. Use a subquery in the Where clause. Note: Categories are lower case in the database; Colors are capitalized.

SELECT ManufacturerName FROM Manufacturer

WHERE ManufacturerID in (SELECT ManufacturerID FROM PRODUCT WHERE Category='sneakers' and Color='BLACK' and ListPrice>$70)

Note: Above query holds true considering the 'cost' mentioned here is nothing but the 'ListPrice' in Product table.

2) List the product names of boots made in New Hampshire. Use a subquery in the where clause. Category data is lower case in database.

SELECT ProductName FROM Product WHERE category='boots' and

ManufacturerID in (SELECT ManufacturerID FROM Manufacturer WHERE State='New Hampshire')

3) List the name and list price of products that sold in quantity of 3. Use a subquery in the where clause.

SELECT ProductName, ListPrice FROM Product WHERE ProductID in (SELECT ProductId FROM PurchaseItem WHERE Quantity=3)

4) List the names and list prices of products that sold only in quatity of 2. (in other words, in quantity of 2 but not in any other quantities). Use subqueries in the where clause.

SELECT ProductName, ListPrice FROM Product WHERE ProductID in (SELECT ProductId FROM PurchaseItem WHERE Quantity=2)

5) List the name and list price of products that were sold on a sale for which the shipping cost was over $60. (use two levels of subqueries in where clauses.)

SELECT ProductName, ListPrice FROM Product WHERE

ProductID in (SELECT ProductId FROM SaleItem WHERE

SaleID in (SELECT SaleID FROM Sale WHERE Shipping>$60))

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---

---

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