-REQUIRED: Use the alias table technique for each query. For each exercise provi
ID: 3873567 • Letter: #
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.
Tables Include:
Categories -CategoryID -CategoryName -Description -Picture
Customers -CustomerID -CompanyName -ContactName -ContactTitle -Address -City -Region - PostalCode -Country -Phone -Fax
Employees -EmployeeID -LastName -FirstName -Title -TitleOfCourtesy -BirthDate -HireDate -Address -City -Region -PostalCode -Country -HomePhone -Extension -Photo -Notes -ReportsTo- Resume -Photo -Graph -Voice
Order Details - OrderID -ProductID -UnitPrice -Quantity -Discount
Orders -OrderID -CustomerID -EmployeeID -OrderDate -RequiredDate -ShippedDate -ShipVia -Freigt -ShipName -ShipAddress -ShipCity -ShipRegion -ShipPostalCode -ShipCountry
Products -ProductID -ProductName -SupplierID -CategoryID -QuantityPerUnit -UnitPrice -UnitsInStock -UnitsOnOrder -ReorderLevel- Discontinued -WebSource
Shippers -ShipperID -CompanyName -Phone
Supplier -SupplierID -CompanyName -ContactName -ContactTitle -Address -City -Region -PostalCode -Country -Phone -Fax -HomePage
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.
Ex. 11. 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
The following are the desired sql statements for the given queries. To provide query results one needs to run these sql commands on the tables already created.
Ex 6.
SELECT * FROM Customers AS C
WHERE C.Country='United States' AND C.Region IN ('Washington','Oregon');
Ex 7.
SELECT * FROM Customers AS C
WHERE C.Region IS NULL;
Ex 8.
SELECT P.ProductName, P.UnitPrice, C.CategoryName
FROM Categories C, Products P
WHERE P.CategoryID = C.CategoryID AND P.ProductName LIKE '%chocolate%'
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.