Database Coding

1. Using the order_lines table from the database from lecture 2, write the SQL that will give the Min and Max values of the column "cost_each"

2. Using the week 2 homework database, show each customer number and count the instances of each customer_numb in the table orders.

Hint: follow this pattern (where the words TABLE and COLUMN are filling in for real names):

    SELECT COLUMN, Count(COLUMN)
    FROM TABLE
    GROUP BY COLUMN;

OR, if you'd like:

    SELECT COLUMN, Count(COLUMN) AS [COUNT OF CUSTOMER NUMBERS]
    FROM TABLE
    GROUP BY COLUMN;


3. Write the SQL to count how many authors have a last name that starts with S.

4. Find the sum of the cost line column

5. How many customers are in each state?

The "answer" is:
    SM    4
    SO    2
    ST    4
    SU    4

6. How many books on hand never ordered (the "answer" is 293)

7. Using the week 2 homework database, write the SQL that will show the average of the retail_price column in the books table

8. Write the SQL that will find the sum of the cost_line column in the table order_lines for customers who are in state "ST"

I guess we will need to do an inner join with tables customers and orders and order_lines to get the answer right?

Attached file(s):

WEEK2_ACCESS2000.mdb  View File