50. CREATE A Table From Another Table

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.

Acknowledgements: We'd like to thank Dave M. for contributing to this solution!

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.

Acknowledgements: We'd like to thank Daniel W. for providing this solution!

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)