Chapter4PracticeExercises

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.

SELECT 'SHIPPED' AS ShipStatus, OrderId, OrderDate FROM Orders WHERE ShipDate IS NOT NULL UNION SELECT 'NOT SHIPPED', OrderID, OrderDate FROM Orders WHERE ShipDate IS NULL ORDER BY OrderDate

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

SELECT [FirstName], LastName, Line1, City, [State], ZipCode FROM Customers JOIN Addresses ON Customers.CustomerID = Addresses.CustomerID AND Customers.ShippingAddressID = Addresses.AddressID

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].

SELECT [FirstName], LastName, Line1, City, [State], ZipCode FROM Customers JOIN Addresses ON Customers.CustomerID = Addresses.CustomerID WHERE Customers.EmailAddress = '[email protected]'

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.

SELECT c.LastName, c.FirstName, OrderDate, p.ProductName, ItemPrice, DiscountAmount, Quantity FROM Customers AS c JOIN Orders AS o ON c.CustomerID = o.CustomerID JOIN OrderItems AS oi ON o.OrderID = oi.OrderID JOIN Products AS p ON oi.ProductID = p.ProductID ORDER BY LastName, OrderDate, ProductName

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.

SELECT p1.ProductID, p1.ProductName, p1.ListPrice FROM Products AS p1 JOIN Products AS p2 ON p1.ProductID <> p2.ProductID AND p1.ListPrice = p2.ListPrice

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.

SELECT CategoryName, ProductID FROM Categories LEFT JOIN Products ON Categories.CategoryID = Products.CategoryID WHERE ProductID IS NULL

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.

SELECT ProductName, CategoryName, ListPrice FROM Products JOIN Categories ON Categories.CategoryID = Products.CategoryID --WHERE CategoryName = 'Guitars' ORDER BY CategoryName, ProductName