SQL-the purpose of this assignment is for you to demonstrate knowledge of column alias and that you can execute multiple table queries. These problems require knowledge of both INNER and OUTER joins.

Log into Horizon.

Open Start > Microsoft SQL Server Tools 18 > Microsoft SQL Server Management Studio.

Click Connect to connect to the dateabase.

Complete the steps in the exercise document.

In a Word document, save a TEXT COPY of the query and a SCREENSHOT of the results for these steps:

Step 1

Step 2

Step 3

Step 4

Step 5

Step 6

Step 7

Tables

Chapter 4 Exercises 1. Write a SELECT statement that joins the Categories table to the Products table and returns these columns: CategoryName, ProductName, ListPrice. Sort the result set by CategoryName and then by ProductName in ascending order.

2. Write a SELECT statement that joins the Customers table to the Addresses table and returns these columns: FirstName, LastName, Line1, City, State, ZipCode. Return one row for each address for the customer with an email address of allan.sherwood@yahoo.com.

3. Write a SELECT statement that joins the Customers table to the Addresses table and returns these columns: FirstName, LastName, Line1, City, State, ZipCode. Code the join so only addresses that are the shipping address for a customer are returned.

4. Write a SELECT statement that joins the Customers, Orders, OrderItems, and Products tables. This statement should return these columns: LastName, FirstName, OrderDate, ProductName, ItemPrice, DiscountAmount, and Quantity. Use aliases for the tables.

Sort the final result set by LastName, OrderDate, and ProductName. 5. Write a SELECT statement that returns the ProductName and ListPrice columns from the Products table. Return one row for each product that has the same list price as another product. (Hint: Use a self-join to check that the ProductID columns arent equal but the ListPrice column is equal.) Sort the result set by ProductName.

6. Write a SELECT statement that returns these two columns: CategoryName The CategoryName column from the Categories table ProductID The ProductID column from the Products table Return one row for each category that has never been used. (Hint: Use an outer join and only return rows where the ProductID column contains a null value.)

7. Use the UNION operator to generate a result set consisting of three columns from the Orders table: ShipStatus A calculated column that contains a value of SHIPPED or NOT SHIPPED OrderID The OrderID column OrderDate The OrderDate column If the order has a value in the ShipDate column, the ShipStatus column should contain a value of SHIPPED. Otherwise, it should contain a value of NOT SHIPPED. Sort the final result set by OrderDate.

MyGuitarShop Database Diagrams Tables System Tables FileTables External Tables Graph Tables dbo.Addresses dbo.Administrators dbo.Categories dbo.Customers dbo.Orderltems dbo.Orders dbo.Products