Using the attached ERD, write SQL statements to perform the following activities
ID: 439723 • Letter: U
Question
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.
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 *from ERD where citiesname like 's%' 2)select *from ERD where citiesname like '%san%' 3)select *from ERD where customertype like 's%' (select *from ERD where citiename like '%9%') 5)select *from product where productcolor like 'red%' 6)select *from product where productclass like 'l%' 7)select *from product where productcolor is not null 8)select *from product where productclass is not M
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.