CIS 336 iLab 4 Introduction to Select, Insert, Update, and Delete Sta CIS 336 iLab 4 Introduction to Select, Insert, Upd | Seite 2

Select unit_price as Retail_Price from items;
Select unit_price as RetailPrice from items;
**Any calculated fields MUST be given an alias (and note above NO SPACES in alias). For
example:
select unit_price * 2 from items; –this does NOT work in EDUPE MySQL
This will work:
select unit_price * 2 as NewPrice from items;
Deliverables
• Lab Report (Answer Sheet) containing both the student-created SQL command(s) for each
exercise, and the output showing the results obtained. Be sure your name is on the file.
LAB STEPS: Complete each of the exercises below.
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”.