REQUIRED: Use the alias table technique for each query. For each exercise provid
ID: 3870309 • Letter: R
Question
REQUIRED: Use the alias table technique for each query. For each exercise provide the SQL command as well as query results in your document. If the results are long, provide a representative sample.
Ex. 6. Create a list of all customers from the United States who are located in the states of Washington or Oregon.
Ex. 7. Notice that some records in the Customers table have not been assigned to a region. Write a query that will display all customers where the region is empty. (Hint: What is the term for “nothingness” in computing?)
Ex. 8. You will need to use the wildcard character ( * ) to answer this question. Create a list of all Products that contain some form of chocolate in the product name. The query should result in more than one record. Be careful what you choose as the text between the wildcard characters. Include the Product Name, Category and Price fields in the results.
Ex. 9. For each order in the OrderDetails table, a product has a unit price and a quantity. Calculate the total revenues for each record (line) in the OrderDetails table. Display only those records with OrderIDs between and including 10400 and 10415. (41 rows should display).
Ex. 10. In Ex. 9 you obtained the total revenues for each specific order (ie., each line or record) in the OrderDetails table. Notice that there are duplicate values in the OrderID column. Aggregate all of these into one unique result. You will need to add the SUM function.
BONUS Exercise. (For up to 3 Bonus points.) You may write your own SQL statement. State the question you are obtaining data for and provide the SQL code and results below:
Explanation / Answer
Select c.* from customers as c where country = 'United States' and state = 'Washington' or state = 'Oregon';
Ex. 7
Select c.* from customers as c where region IS NULL;
Ex. 8
Select p.productName,p.category,p.price from Products as p where p.productName like '%chocolate%';
Ex. 9
Select od.unitPrice * od.quantity as TotalRevenue from OrderDetails as od where od.OrderID between 10400 and 10415;
Ex. 10.
Select SUM(od.unitPrice * od.quantity) as TotalRevenue from OrderDetails as od ;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.