SQL Exercises

Create a directory in your home area and copy the customer.unload, customer_contracts.unload contracts.unload, and contract_items.unload files from /mokihana/homes/cs594ir_sp03/public into it.

Connect to your database using psql, and do the following:

  1. Create four tables as follows:
    2.

    customers customer_contracts contracts contract_items

    customer_id

    int4

    customer_id

    int4

    contract

    int4

    contract

    int4

    name

    text

    contract

    int4

    description

    text

    item

    int4

    address

    text

    start_date

    date

    start_date

    date

    price

    float4

    city

    text

    end_date

    date

    end_date

    date

    		    

    state

    char(2)

    		            

    zip

    varchar(9)

    		            

    start_date

    date

    		            

    These tables are an extension of the example used in the SQL Introduction; the idea behind them is that our fictional company has several contracts to which customers can belong to get special pricing. The customers table is where the list of customers is kept, and it is tied by the customer_id to the customer_contracts table, which specifies which contracts a customer belongs to and for what date range they belong to a contract. The customer_contracts tables is tied by the contract field to the contracts table, which contains the list of contracts and includes a description of the contract and the date range during which it was available. The contracts table is then tied by the contract field to the contract_items table, which specifies what items are part of a contract and the price of each item under the contract.

  2. Load the data from the unload files into the appropriate tables (contracts.unload into contracts, etc.) and look at it.

  3. Insert the following records:

    4. Into customers:

    customer_id

    name

    address

    city

    state

    zip

    start_date

    5

    We’ve Got It All

    17 Hickory Way

    Atlanta

    GA

    30301

    10/20/98

    Into customer_contracts:

    customer_id

    contract

    start_date

    end_date

    5

    9

    1/1/00

    12/31/01

  4. Update this record from the contract_items table:
    5.

    contract

    item

    price

    5

    1

    2.30

    To read:

    contract

    item

    price

    5

    1

    2.45

  5. Create queries to answer the following questions; save the queries to files named X.sql and unload the results to files named X.unload, where X is the letter of the question:

    1. How many customers are there per state? (Hint: COUNT(*) is an aggregate function.)
    2. What is the average price of products that are on contract 5?
    3. What are the contracts, products, and prices for contracts active at some point during 2000?
    4. What items are on contract by customers from Tennessee now and in the past? Create a list with each item appearing only once and in order by item number.
      5.