CIS 336 help Making Decisions/uophelp.com CIS 336 help Making Decisions/uophelp.com | Page 46

1. Write a query that displays a list of all customers showing the customer first name, last
name, and phone number. Sort the results by customer last name, then first name.
2. Write a query that displays each customer name as a single field in the format
―firstnamelastname‖ with a heading of Customer, along with their phone number with a
heading of Phone. Use the IN operator to only display customers in New York, New
Jersey, or Washington D.C. Sort the results by phone number.
3. Write a query that will list all the cities that have customers with a heading of Cities.
Only list each city once (no duplicates) and sort in descending alphabetical order.
4. Write a query that displays the title of each item along with the price (with a heading
of Original) and a calculated field reflecting the price with a 25% discount (with a
heading of Sale). Display the sale price with two decimal places using the ROUND
function. Sort by price from lowest to highest.
5. Write a query that displays the customer_first_name, customer_last_name, and
customer_city from the customers table. Use the LIKE operator to only display customers
that reside in any zipcode beginning with 4.
6. Write a query that displays the order id and order date for any orders placed from
March 1, 2014 through April 30, 2014. Do this WITHOUT using the BETWEEN clause.
Format the date field as Month dd, yyyy and use a heading of ―Ordered‖.
7. Write a query that displays the order id and order date for any orders placed during the
month of May, 2014. Do this using the BETWEEN clause. Format the date field as
mm/dd/yy and use a heading of ―Ordered‖.
8. Write a query which displays the order id, customer id, and the number of days
between the order date and the ship date (use the DATEDIFF function). Name this
column ―Days‖ and sort by highest to lowest number of days. Only display orders where
this result is 15 days or more.
9. Write a query which displays the order id, customer id and order date for all orders that
have NOT been shipped, sorted by order date with the most recent order at the top.
10. The Marketing Department has requested a new report of shipped orders for which
the order was placed on either a Saturday or a Sunday. Write a query which displays the
order id, order date, shipped date, along with a calculated column labeled ―Order_Day‖
showing the day of the week the order was placed (use the DAYNAME function). Only
display orders that have shipped and were placed on a Saturday or Sunday. Sort by order
date with most recent orders at the top.