-REQUIRED: Use the alias table technique for each query. For each exercise provi
ID: 3873608 • 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.
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 1. Write an SQL command that displays all of the fields and data from the Categories table. {Hint: Use the shortcut, fast method!}
Ex. 2. Create a phone list of the customer contacts that is sorted by the Company Name. Include the company name, contact name, phone and fax in the output. Don’t forget to sort! (Your results should be all 91 customers in alphabetical listing by company, but only do a screen capture of the top 10 or 15.)
Ex. 3. Rewrite the query you created in Ex. 2 to display ONLY those records from the USA. Alias the Company Name as Company and the Contact Name as Customer.
Ex. 4. Create a list of products and their categories with a price greater than or equal to $20. (Only 3 columns should be displayed) (For “categories” check the table design).
Ex. 5. Modify the query in Ex. 4. This time list only those products with a price between $40 and $90.
Explanation / Answer
Ex 1. Write an SQL command that displays all of the fields and data from the Categories table. {Hint: Use the shortcut, fast method!}
Ex. 2. Create a phone list of the customer contacts that is sorted by the Company Name. Include the company name, contact name, phone and fax in the output. Don’t forget to sort! (Your results should be all 91 customers in alphabetical listing by company, but only do a screen capture of the top 10 or 15.)
Ex. 3. Rewrite the query you created in Ex. 2 to display ONLY those records from the USA. Alias the Company Name as Company and the Contact Name as Customer.
Ex. 4. Create a list of products and their categories with a price greater than or equal to $20. (Only 3 columns should be displayed) (For “categories” check the table design).
Ex. 5. Modify the query in Ex. 4. This time list only those products with a price between $40 and $90.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.