Course Web Pages - Spring 2012 - LIBR 242-01/10 Greensheet - Sample Codes - SQL Tables

Creating/Modifying/Deleting Tables in Database

1. Assuming that a database and the associated table space are defined. The next step of implementing the database is to create tables in the table space. Note the sequence of table creation. Tables without foreign key references should be created first. The system will not allow you to create a table if the table contains foreign keys and if the table(s) referred to by these foreign keys are not yet defined in the system.


CREATE TABLE CUSTOMER (
	CUS_CODE	CHAR (3)	NOT NULL,
	CUS_LNAME	CHAR (15)	NOT NULL,
	CUS_FNAME	CHAR (15)	NOT NULL,
	CUS_INITIAL	CHAR (1),
	CUS_INDATE	DATE		NOT NULL,
	CUS_ADDRESS1	CHAR (40)	NOT NULL,
	CUS_PHONE1	CHAR (12)	NOT NULL,
	CUS_BALANCE	NUMBER (8, 2)	NOT NULL,
	PRIMARY KEY (CUS_CODE));


CREATE TABLE PRODUCT (
	PROD_CODE	CHAR (10)	NOT NULL,
	PROD_NAME	CHAR (20)	NOT NULL,
	PROD_MANDATE	DATE		NOT NULL,
	PROD_MODEL	CHAR (10)	NOT NULL,
	PRIMARY KEY (PROD_CODE));


CREATE TABLE INVOICE (
	INV_NUM		NUMBER (6, 0)	NOT NULL,
	CUS_CODE	CHAR (3)	NOT NULL,
	ISSUE_DATE	DATE		NOT NULL,
	DUE_DATE	DATE		NOT NULL,
	PAID_DATE	DATE,
	PRIMARY KEY (INV_NUM),
	FOREIGN KEY (CUS_CODE) REFERENCES CUSTOMER);


CREATE TABLE LINE (
	INV_NUM		NUMBER (6, 0)	NOT NULL,
	LINE_NUM	NUMBER (3, 0)	NOT NULL,
	PROD_CODE	CHAR (10)	NOT NULL,
	LINE_UNITS	NUMBER (3, 0)	NOT NULL,
	LINE_PRICE	NUMBER (8, 2)	NOT NULL,
	PRIMARY KEY (INV_NUM, LINE_NUM),
	FOREIGN KEY (INV_NUM) REFERENCES INVOICE,
	FOREIGN KEY (PROD_CODE) REFERENCES PRODUCT);

2. To view the definition of a table (CUSTOMER, e.g.) already in the database,


DESCRIBE CUSTOMER;

3. To add a column (field) into already defined table,


ALTER TABLE CUSTOMER ADD(EMAIL CHAR(20));

4. To change the data type of a defined field,


ALTER TABLE CUSTOMER MODIFY(CUS_CODE NUMBER(3, 0));

5. If you make a mistake in defining the table (such as missing a field, misnaming a field, mis-specifying field name or data type), you can simply wipe out the table,


DROP TABLE LINE;

and then recreate the table like nothing ever happened.

6. To list all tables you have created in your table space (under your user account):


SELECT TABLE_NAME FROM USER_TABLES;

Select here to return to the sample codes page.

Programs

Courses

Creating/Modifying/Deleting Tables in Database