[SOLVED] ITSE2409 Lesson 4- Chapter 4

Chapter 4

How to retrieve data from two or more tables

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 [email protected].

3. 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 customer, but only return addresses that are the shipping address for a customer.

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 aren’t equal but the ListPrice column is equal.)

   Sort the result set by ProductName.