Database Programming – INFT224

Bryant & Stratton College

Spring 2009

Lab 2

Experimenting with tables

1. Using your favorite tool (sqlplus or Oracle SQL Developer) and the code below, create the data structures – customers, item and order.

Note: any line that begins with 2 dashes is a comment and is not executed.

--You can copy and paste the code below to remove your old tables and

--recreate the new tables.

-- delete the tables.

drop table orders;

drop table customer;

drop table item;

-- create the customer table.

create table customer

(

cust_num number,

cust_name char(20),

address char(20),

credit_level number,

check (credit_level <=10),

primary key (cust_num)

);

-- insert data into the customer table.

insert into customer values (001, 'Kirk', 'Enterprise', 10);

insert into customer values (002, 'Spock', 'Enterprise', 9);

insert into customer values (003, 'Scotty', 'Enterprise', 8);

insert into customer values (004, 'Bones', 'Enterprise', 8);

insert into customer values (005, 'Gorn', 'PlanetoidArena', 1);

-- create the item table.

create table item

(

item_num number,

item_name char(20),

price number (9,2),

weight number,

primary key (item_num)

);

-- insert data into the item table.

insert into item values (125, 'phaser', 350, 2);

insert into item values (137, 'beam', 1500, 250);

-- create the table orders.

create table orders

(

ord_num number,

cust_num number not null,

item_num number not null,

quantity number (9,2),

total_cost number (9,2),

primary key (ord_num),

foreign key (cust_num) references customer(cust_num),

foreign key (item_num) references item(item_num)

);

-- insert data into the orders table.

insert into orders values (10012, 005, 125, 2, 700);

2. Verify that your data structures were created correctly by running the code below and comparing it with the modified structures in this document.

> describe tablename;

3. Insert the remaining rows into your table, using the insert command with data from page 219:

insert into tablename values ('value1','value2',..., ‘valuen’);

NOTE: only use single quotes around ALPHABETIC data. They are not needed for numeric data

4. View the all the rows and values within your tables. Make sure the data is correct, as we will need this data for several queries over the next couple labs.

> select * from tablename;

5. Try out the sample queries below. Similar to the insert statements, when filtering for string values, use single quotes ( ‘ ), but for numeric values, leave the quotes off.

- list the names of all customers in alphabetic order.

- list all customer information for customers with a credit level of 8.

- list all customer information for customers from ‘Enterprise’.

- list all item information for items costing over 1,100.00.

- list all order information for orders from customer 001.

- list all customer information for customers with a credit level of 8 or 6.

- list all item names that begin with s.