IEOR 215: Analysis and Design of
Prof. Ken Goldberg, UC Berkeley
Below is a commercial system to demonstrate SQL,
where you can type in queries using online forms:
SQL tutorial and demonstration
Please try a few exercises. Section 11 contains
the interactive form, and links to the two tables:
- customers(customerid, firstname, lastname,
- items_ordered(customerid, order_date, item,
Note that this SQL interpreter does not support
sub-SELECT statements or all SQL operators.
Write queries for the following (note that you can run the SQL query real-time
in the dialog box at the bottom of each page):
- Retrieve the total amount purchased by each
customer, ordered by the customer with the highest total first.
- Retrieve customers (firstname, lastname)
from Arizona or Oregon that made purchases over $20.
- If a customer made single orders of
$20-$50, we will apply a discount of 10%. However, if a customer made a
single order >$50, we will apply a flat discount of $10. Retrieve the
customers who qualify for such discounts and display their order price and
order price after discounts were applied. (this can be done as 2 separate
queries. How would you do it in one?)