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.

**** I am having trouble with these questions on my assignment. These must be single SQL queries Only columns requested should be displayed. Queries should not produce duplicate records unless asked. *****

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

SQL Queries

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.

Query

SELECT ManufactureName,Category,ListPrice from Product,Manufacturer where (select ProductName from Product where category='sneakers' and color='BLACK' and ListPrice>70);

Explanation

We need
* ManufactureName for listing name of manufactures from Manufacturer table and Category and ListPrice from Product table they both are connected with ManufacturerID as a common key.
* In Where clause select ProductName from Product where 'sneakers' under category and 'BLACK' under color having ListPrice greater than $70.

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.

Query

SELECT ProductName from Product where (select ProductName from Product,Manufacture where Category='boots' and Manufacturer.state='New Hampshire' and Product.ManufactureId=Manufacturer.ManufactureId);

Explanation

We need
* ProductName for listing name of Products from Product table, Here Manufacturer table and Product table they both are connected with ManufacturerID as a common key.
* In Where clause select ProductName from Product table where Category='boots' and Manufacturer.state='New Hampshire' from Manufacture table.

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

Query

SELECT ProductName,ListPrice from Product where (select ProductID from Product,SaleItem where Quantity=3 and SaleItem.ProductID=Product.ProductID);

Explanation

We need
* ProductName for listing name of Products and ListPrice from Product table
* In Where clause select ProductID from Product and SaleItem where Products sold in quantity of 3.
* Here SaleItem table and Product table they both are connected with ProductID as a common key.

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---

---

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