How to retrieve data from a single table

Exercises

Enter and run your own SELECT statements

In these exercises, youll enter and run your own SELECT statements.

1. Write a SELECT statement that returns four columns from the Products table: ProductCode, ProductName, ListPrice, and DiscountPercent. Then, run this statement to make sure it works correctly. Add an ORDER BY clause to this statement that sorts the result set by list price in descending sequence. Then, run this statement again to make sure it works correctly.

This is a good way to build and test a statement, one clause at a time.

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

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

ProductName The ProductName column

ListPrice The ListPrice column

DateAdded The DateAdded column

Return only the rows with a list price thats greater than 500 and less than 2000. Sort the result set in descending sequence by the DateAdded column.

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

ProductName The ProductName

column ListPrice The ListPricecolumn

DiscountPercent The DiscountPercent column

DiscountAmount A column thats calculated from the previous two columns

DiscountPrice A column thats calculated from the previous three columns

Sort the result set by discount price in descending sequence.

5. 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 thats calculated by multiplying the item price with the quantity

DiscountTotal A column thats calculated by multiplying the discount amount with the quantity

ItemTotal A column thats 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

Work with nulls and test expressions

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

7. Write a SELECT statement without a FROM clause that creates a row with these columns:

Price 100 (dollars)

TaxRate .07 (7 percent)

TaxAmount The price multiplied by the tax

Total The price plus tax

To calculate the fourth column, add the expressions you used for the first and third columns.

Submit: a single text file containing your individual SQL statments from the "GuitarStore" exercises

You don't need to include any result information as I will be copying your

statement into a SQL window and executing them myself.

Please number each statement and leave a space between each SQL statement.