6.10.3 Sub Queries (Oracle) For each information request below, formulate a sing

Question

6.10.3 Sub Queries (Oracle)

For each information request below, formulate a single SQL query to produce the required information. In each case, you should display only the columns requested. All of these queries require the DISTINCT keyword.

1.List the name and list price of products that were sold on a sale for which the shipping cost was over $60. Use a subquery in the WHERE clause.

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

3.List the names and list prices of products that sold only in quatity of 2. (in other words, IN quantity of 2 and NOT IN any other quantities). Use 2 subqueries in the WHERE clause.

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

5. List the names of manufacturers of black sneakers that cost over $70. Use a subquery in the WHERE clause. Note: Categories are lower case in the database; Colors have their first letter capitalized.

Customer CustomerID FirstName LastName StreetAddress City State PostalCode Country Phone Manufacturer ManufacturerID ManufacturerName Address Address2 City State PostalCode Phone Product ProductID ProductName ManufacturerlD Composition ListPrice Gender Category Color Description ax Contact Saleltem ProductID ItemSize SalelD Quantity SalePrice Purchaseltem ProductID ItemSize PuchaseID Quantity PurchasePrice Inventoryltem ProductID ItemSize QtyOnHand ItemSize ItemSize Sale SalelD SaleDate CustomerlD Purchase Employee EmployeelD FirstName LastName Address City State PurchaselD PurchaseDate EmployeelD ExpectedDeliveryDate ManufacturerlD Shipping ax Shipping SalaryEmployee EmployeelD Salary WageEmployee EmployeelD Wage MaxHours Phone ManagerlD SSN EmailAddress HireDate

Explanation / Answer

Sql Queries are given below:

2.)

SELECT DISTINCT Product.ProductName, Product.ListPrice from Product INNER JOIN SaleItem ON Product.ProductID = SaleItem.ProductID WHERE SaleItem.Quantity = 3

3.)

SELECT DISTINCT Product.ProductName, Product.ListPrice from Product INNER JOIN SaleItem ON Product.ProductID = SaleItem.ProductID WHERE SaleItem.Quantity = 2

4.)

SELECT DISTINCT Product.ProductName from Product INNER JOIN Manufacturer ON Product.ManufacturerID = Manufacturer.ManufacturerID WHERE Product.Category = 'boots'
AND Manufacturer.State = 'New Hampshire'

5.)

SELECT DISTINCT Manufacturer.ManufacturerName from Product INNER JOIN Manufacturer ON Product.ManufacturerID = Manufacturer.ManufacturerID WHERE Product.Category = 'sneakers'
AND Product.Color = 'Black' AND Product.ListPrice > 70

Related Questions

Navigate

• Browse (All)
• Browse 6
• Subjects

---

---

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