Question

Note: It is strongly recommended that you try out your solutions for these exercises on the MyGuitarShop database to make sure that they work.

Part 1: Chapter 3

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 a letter from M to Z.

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

ProductName               The ProductName column

ListPrice                       The ListPrice column

DiscountPercent           The DiscountPercent column

DiscountAmount          A column that’s calculated from the previous two columns

DiscountPrice               A column that’s calculated from the previous three columns

Sort the result set by discount price in descending sequence.

3.   Write a SELECT statement that returns these columns from the Orders table:

OrderID                       The OrderID column

OrderDate                    The OrderDate column

ShipDate                      The ShipDate column

Return only the rows where the ShipDate column contains a null value.

Part 2: Chapter 4

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

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

3. 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.)

Answer 1

1)

SELECT LastName || ', ' || FirstName AS FullName FROM Customers;

2)

SELECT LastName || ', ' || FirstName AS FullName FROM Customers ORDER BY LastName;

NOTE: As per Chegg policy I am allowed to answer specific number of questions (including sub-parts) on a single post. Kindly post the remaining questions separately and I will try to answer them. Sorry for the inconvenience caused.