SQL Topics: Tables

SQL: CREATE TABLE Statement

The CREATE TABLE statement allows you to create and define a table.

The basic syntax for a CREATE TABLE statement is:

CREATE TABLE table_name
( column1 datatype null/not null,
column2 datatype null/not null,
...
);

Each column must have a datatype. The column should either be defined as "null" or "not null" and if this value is left blank, the database assumes "null" as the default.

For example:

CREATE TABLE suppliers
(	supplier_id	number(10)	not null,
	supplier_name	varchar2(50)	not null,
	contact_name	varchar2(50)
);

Practice Exercise #1:

Create a customers table that stores customer ID, name, and address information. The customer ID should be the primary key for the table.

Solution:

The CREATE TABLE statement for the customers table is:

CREATE TABLE customers
(	customer_id	number(10)	not null,
	customer_name	varchar2(50)	not null,
	address	varchar2(50),
	city	varchar2(50),
	state	varchar2(25),
	zip_code	varchar2(10),
	CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

Practice Exercise #2:

Based on the departments table below, create an employees table that stores employee number, employee name, department, and salary information. The primary key for the employees table should be the employee number. Create a foreign key on the employees table that references the departments table based on the department_id field.

CREATE TABLE departments
(	department_id	number(10)	not null,
	department_name	varchar2(50)	not null,
	CONSTRAINT departments_pk PRIMARY KEY (department_id)
);

Solution:

The CREATE TABLE statement for the employees table is:

CREATE TABLE employees
(	employee_number	number(10)	not null,
	employee_name	varchar2(50)	not null,
	department_id	number(10),
	salary	number(6),
	CONSTRAINT employees_pk PRIMARY KEY (employee_number),
	CONSTRAINT fk_departments
	FOREIGN KEY (department_id)
	REFERENCES departments(department_id)
);

SQL: CREATE a table from another table

You can also create a table from an existing table by copying the existing table's columns.

It is important to note that when creating a table in this way, the new table will be populated with the records from the existing table (based on the SELECT Statement).

Syntax #1 - Copying all columns from another table

The basic syntax is:

CREATE TABLE new_table
AS (SELECT * FROM old_table);

For example:

CREATE TABLE suppliers
AS (SELECT *
FROM companies
WHERE id > 1000);

This would create a new table called suppliers that included all columns from the companies table.

If there were records in the companies table, then the new suppliers table would also contain the records selected by the SELECT statement.

Syntax #2 - Copying selected columns from another table

The basic syntax is:

CREATE TABLE new_table
AS (SELECT column_1, column2, ... column_n FROM old_table);

For example:

CREATE TABLE suppliers
AS (SELECT id, address, city, state, zip
FROM companies
WHERE id > 1000);

This would create a new table called suppliers, but the new table would only include the specified columns from the companies table.

Again, if there were records in the companies table, then the new suppliers table would also contain the records selected by the SELECT statement.

Syntax #3 - Copying selected columns from multiple tables

The basic syntax is:

CREATE TABLE new_table
AS (SELECT column_1, column2, ... column_n
FROM old_table_1, old_table_2, ... old_table_n);

For example:

CREATE TABLE suppliers
AS (SELECT companies.id, companies.address, categories.cat_type
FROM companies, categories
WHERE companies.id = categories.id
AND companies.id > 1000);

This would create a new table called suppliers based on columns from both the companies and categories tables.

Frequently Asked Questions

---

Question: How can I create a table from another table without copying any values from the old table?

Answer: To do this, the basic syntax is:

CREATE TABLE new_table
AS (SELECT * FROM old_table WHERE 1=2);

For example:

CREATE TABLE suppliers
AS (SELECT * FROM companies WHERE 1=2);

This would create a new table called suppliers that included all columns from the companies table, but no data from the companies table.

SQL: ALTER TABLE Statement

The ALTER TABLE statement allows you to rename an existing table. It can also be used to add, modify, or drop a column from an existing table.

Renaming a table

The basic syntax for renaming a table is:

ALTER TABLE table_name
RENAME TO new_table_name;

For example:

ALTER TABLE suppliers
RENAME TO vendors;

This will rename the suppliers table to vendors.

Adding column(s) to a table

Syntax #1

To add a column to an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
ADD column_name column-definition;

For example:

ALTER TABLE supplier
ADD supplier_name varchar2(50);

This will add a column called supplier_name to the supplier table.

Syntax #2

To add multiple columns to an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
ADD (	column_1	column-definition,
	column_2	column-definition,
	...
	column_n	column_definition );

For example:

ALTER TABLE supplier
ADD (	supplier_name	varchar2(50),
	city	varchar2(45) );

This will add two columns (supplier_name and city) to the supplier table.

Modifying column(s) in a table

Syntax #1

To modify a column in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
MODIFY column_name column_type;

For example:

ALTER TABLE supplier
MODIFY supplier_name varchar2(100) not null;

This will modify the column called supplier_name to be a data type of varchar2(100) and force the column to not allow null values.

Syntax #2

To modify multiple columns in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
MODIFY (	column_1	column_type,
	column_2	column_type,
	...
	column_n	column_type );

For example:

ALTER TABLE supplier
MODIFY (	supplier_name	varchar2(100)	not null,
	city	varchar2(75)		);

This will modify both the supplier_name and city columns.

Drop column(s) in a table

Syntax #1

To drop a column in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
DROP COLUMN column_name;

For example:

ALTER TABLE supplier
DROP COLUMN supplier_name;

This will drop the column called supplier_name from the table called supplier.

Rename column(s) in a table
(NEW in Oracle 9i Release 2)

Syntax #1

Starting in Oracle 9i Release 2, you can now rename a column.

To rename a column in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
RENAME COLUMN old_name to new_name;

For example:

ALTER TABLE supplier
RENAME COLUMN supplier_name to sname;

This will rename the column called supplier_name to sname.

Acknowledgements: Thanks to Dave M., Craig A., and Susan W. for contributing to this solution!

Practice Exercise #1:

Based on the departments table below, rename the departments table to depts.

CREATE TABLE departments
(	department_id	number(10)	not null,
	department_name	varchar2(50)	not null,
	CONSTRAINT departments_pk PRIMARY KEY (department_id)
);

Solution:

The following ALTER TABLE statement would rename the departments table to depts:

ALTER TABLE departments
RENAME TO depts;

Practice Exercise #2:

Based on the employees table below, add a column called salary that is a number(6) datatype.

CREATE TABLE employees
(	employee_number	number(10)	not null,
	employee_name	varchar2(50)	not null,
	department_id	number(10),
	CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

Solution:

The following ALTER TABLE statement would add a salary column to the employees table:

ALTER TABLE employees
ADD salary number(6);

Practice Exercise #3:

Based on the customers table below, add two columns - one column called contact_name that is a varchar2(50) datatype and one column called last_contacted that is a date datatype.

CREATE TABLE customers
(	customer_id	number(10)	not null,
	customer_name	varchar2(50)	not null,
	address	varchar2(50),
	city	varchar2(50),
	state	varchar2(25),
	zip_code	varchar2(10),
	CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

Solution:

The following ALTER TABLE statement would add the contact_name and last_contacted columns to the customers table:

ALTER TABLE customers
ADD (	contact_name	varchar2(50),
	last_contacted	date );

Practice Exercise #4:

Based on the employees table below, change the employee_name column to a varchar2(75) datatype.

CREATE TABLE employees
(	employee_number	number(10)	not null,
	employee_name	varchar2(50)	not null,
	department_id	number(10),
	CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

Solution:

The following ALTER TABLE statement would change the datatype for the employee_name column to varchar2(75):

ALTER TABLE employees
MODIFY employee_name varchar2(75);

Practice Exercise #5:

Based on the customers table below, change the customer_name column to NOT allow null values and change the state column to a varchar2(2) datatype.

CREATE TABLE customers
(	customer_id	number(10)	not null,
	customer_name	varchar2(50),
	address	varchar2(50),
	city	varchar2(50),
	state	varchar2(25),
	zip_code	varchar2(10),
	CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

Solution:

The following ALTER TABLE statement would modify the customer_name and state columns accordingly in the customers table:

ALTER TABLE customers
MODIFY (	customer_name	varchar2(50) not null,
	state	varchar2(2) );

Practice Exercise #6:

Based on the employees table below, drop the salary column.

CREATE TABLE employees
(	employee_number	number(10)	not null,
	employee_name	varchar2(50)	not null,
	department_id	number(10),
	salary	number(6),
	CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

Solution:

The following ALTER TABLE statement would drop the salary column from the employees table:

ALTER TABLE employees
DROP COLUMN salary;

Practice Exercise #7:

Based on the departments table below, rename the department_name column to dept_name.

CREATE TABLE departments
(	department_id	number(10)	not null,
	department_name	varchar2(50)	not null,
	CONSTRAINT departments_pk PRIMARY KEY (department_id)
);

Solution:

The following ALTER TABLE statement would rename the department_name column to dept_name in the departments table:

RENAME COLUMN department_name to dept_name;
ALTER TABLE departments

SQL: DROP TABLE Statement

The DROP TABLE statement allows you to remove a table from the database.

The basic syntax for the DROP TABLE statement is:

DROP TABLE table_name;

For example:

DROP TABLE supplier;

This would drop table called supplier.

SQL: Global Temporary tables

Global temporary tables are distinct within SQL sessions.

The basic syntax is:

CREATE GLOBAL TEMPORARY TABLE table_name ( ...);

For example:

CREATE GLOBAL TEMPORARY TABLE supplier
(	supplier_id	numeric(10)	not null,
	supplier_name	varchar2(50)	not null,
	contact_name	varchar2(50)
)

This would create a global temporary table called supplier .

SQL: Local Temporary tables

Local temporary tables are distinct within modules and embedded SQL programs within SQL sessions.

The basic syntax is:

DECLARE LOCAL TEMPORARY TABLE table_name ( ...);