Use Oracle Notation Using the attached ERD, write SQL statements to perform the
ID: 3660173 • Letter: U
Question
Use Oracle Notation
Using the attached ERD, write SQL statements to perform the following activities.
1. Retrieve address of all customers who live in cities with names starting with letter S
2. Retrieve address of all customers who live in cities with names containing the word San
3. Retrieve address of all customers whose customer type is S, and zipcode contains the digit 9.
4. Retrieve address of all customers whose address line 1 contains Blvd, the customer type is S,
and zipcode begins with a 0 and has the digit 9 in it.
5. Retrieve name of all products which have the color Red
6. Retrieve name of all products for product class L
7. Retrieve name of all products where product class is not null
8. Retrieve name of all products whose product class is not M
9. Retrieve name of all products whose names include the word Helmet
10. Retrieve name of all products whose product class is not M and list price is $100 or more
11. Retrieve list of product names with no duplicates
12. Retrieve list of zip codes for customers with no duplicates
13. Retrieve list of addresses for customers with no duplicates
14. Display zip codes and the number of customers residing in each zip code (as NumCustomers)
15. Display different product lines, the number of products in each product line, and the average
list price of the products.
16. Display ProductID, and Total quantity sold (Sum of OrderQty) and Total Product Sales (Sum
of LineTotal) for each ProductID
17. Display ProductClass and Number of Products in the Product Class.
18. Display CustID, Number of Sales Orders As NumOrders, and Sum of TotalDue as TotalSales
for customers whose sales orders have totaled to greater than $100000
19. Display ProductID of products and their total sales (the sum of lineTotal) as
TotalProductSales.
20. Display ProductID of products that have sold more than 100 units (sum of OrderQty), and
more than 100 customers have purchased it.
Explanation / Answer
1) Select Address from Customers where City LIKE 'S%' 2) Select Address from Customers where City LIKE 'San%' 3) Select Address from Customers where Cust_Type='S' AND Zip_Code LIKE '%9%' 4) Select Address from Customers where Address IN (Blvd) AND Cust_Type='S' AND Zip_Code='0%9%' 5) Select Name From Product where Color='Red' 6) Select Name From Product where Class='L' 7) Select Name From Product where Class LIKE [!NULL] 8) Select Name From Product where Class LIKE [!M] 9) Select Name From Product where Name LIKE '%Helmet%' 10) Select Name From Product where Class LIKE [!M] AND Price>=100 11) Select distinct Name From Product 12) Select distinct zipcodes From Customers 13) Select distinct Address From Customers 14) Select Zipcodes,Count(Cust_Names) as NumCustomers From Customers group by Zipcodes 15) Select Product_Lines,Count(Products), Avg(Price) from Proucts group by product_Lines 16) Select ProductID, Sum(OrderQty), Sum(LineTotal) from Products group by ProductID 17) Selecct ProductClass,Count(Products) From Product_Class group by ProductClass 18) Select CustID,Count(Orders) As NumOrders,Sum(TotalDue) as TotalSales FROM Customers where Sum(Orders)>100000 group by CustID 19) Select ProductID, Sum(lineTotal) as TotalProductSales From Products group by Products 20) Select ProductID, Sum(OrderQty) as TotalProductSales From Products where Sum(OrderQty)>100 group by ProductID
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.