CIS 336 All Assignments CIS 336 All Assignments | Page 50

**There can be NO SPACES in alias names given to a column. For example:
Select unit_price as “Retail Price “ from items; –this does NOT work in EDUPE MySQL.
Any of the following WILL WORK:
Select unit_price as “RetailPrice” from items;
Select unit_price as “Retail_Price” from items;
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 to determine the total number of items on each order. Display the order_id
and the total with a heading of TotalItems (note no spaces). Filter to only display information
for order_id of 600 or higher.
2. Re-do query 1 but filter to only show those orders with more than 2 items ordered. Sort by
the number of items ordered, lowest to highest.
3. The order_details table has a quantity for each item ordered. Show the total amount charged
for each item on the order (quantity times price). Display order_id, the item id, the unit price,
the quantity times price of the item labeled as “Itemtotal” (note NO spaces). Sort by order id
and filter to only display those order ids between 400 and 700.
4. Write a query to display the total amount for each order: show the order id and total. Sort by
descending order on the total and only display orders with a total of $40 or more.