#3 Write a SELECT statement that joins the Customers table to the Addresses table and returns these columns:

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.

Answer:

SELECT FirstName, LastName, Line1, City, State, ZipCode

FROM Customers JOIN Addresses

ON

Customers.CustomerID = Addresses.CustomerID

AND

Customers.ShippingID = AddressID;

#2 Write a SELECT statement that returns these column names and data from the OrderItems table:

Write a SELECT statement that returns these column names and data from the OrderItems table:

ItemID The ItemID column

ItemPrice The ItemPrice column

DiscountAmount The DiscountAmount column

Quantity The Quantity column

PriceTotal A column that’s calculated by multiplying the item price with the quantity

DiscountTotal A column that’s calculated by multiplying the discount amount with the quantity

ItemTotal A column that’s calculated by subtracting the discount amount from the item price and then multiplying by the quantity

Only return rows where the ItemTotal is greater than 500.
Sort the result set by item total in descending sequence.

Answer:

 SELECT

ItemID, ItemPrice, DiscountAmount, Quantity,

ItemPrice * Quantity AS PriceTotal,

DiscountAmount * Quantity AS DiscountTotal,

(ItemPrice - DiscountAmount) * Quantity

AS ItemTotal

FROM OrderItems

WHERE (ItemPrice - DiscountAmount) * Quantity > 500

ORDER BY ItemTotal DESC;

#1 Write a SELECT statement that returns one column from the Customers table named FullName..

1.Write a SELECT statement that returns one column from the Customers table named FullName that joins the LastName and FirstName columns.

Format this column with the last name, a comma, a space, and the first name like this:

Doe, John

Sort the result set by last name in ascending sequence.

Return only the contacts whose last name begins with letters from M to Z.

Answer:

SELECT

LastName + ', ' + FirstName AS FullName
FROM Customers
WHERE LastName >= 'M'
ORDER BY FullName;