Slide 9.7: SQL CREATE TABLE statement (cont.)

Slide 9.6: SQL CREATE TABLE statement
Slide 9.8: SQL INSERT INTO statement
Home Print version

SQL CREATE TABLE Statement (Cont.)

An Example of CREATE TABLE Statement
The example creates a table called “person” that contains five columns: person_id, last_name, first_name, address, and city. The person_id column is of type CHAR and will hold a fixed-length string.

The last_name, first_name, address, and city columns are of type VARCHAR and will hold variable-length strings, and the maximum length for these fields is 255 characters.

SQL> DROP TABLE person;
SQL> CREATE TABLE person (
  2    person_id  CHAR(4) PRIMARY KEY,
  3    last_name  VARCHAR(32) NOT NULL,
  4    first_name VARCHAR(32),
  5    address    VARCHAR(32),
  6    city       VARCHAR(16) );

The column last_name is with a constraint NOT NULL, which will not let the column take a NULL value.

Create Table Using Another Table
After the table person is created, the table can now be filled with data by using the SQL INSERT INTO statement. Another way to create and populate a table at the same time is to use the CREATE TABLE ... SELECT FROM statement. A copy of the existing table content will be entered into the new table.

The new table gets the same column definitions. All columns or specific columns can be selected. If you create a new table using an existing table, the new table will be filled with the existing values from the old table. For example, the SQL statement creates a new table called “test_table” (which is a copy of the “Customers” table):

SQL> DROP TABLE Customers;
SQL> CREATE TABLE Customers (
  2    CustomerID   INTEGER PRIMARY KEY,
  3    CustomerName VARCHAR(32),
  4    ContactName  VARCHAR(32),
  5    Address      VARCHAR(32),
  6    City         VARCHAR(16),
  7    PostalCode   CHAR(6),
  8    Country      VARCHAR(16) );

SQL> INSERT INTO Customers VALUES(
     ...

SQL> DROP TABLE test_table;
SQL> CREATE TABLE test_table AS
  2    SELECT CustomerName, ContactName
  3      FROM Customers;

Demonstration
Below is an SQL test area from W3Schools, which uses the well-known Northwind sample database. The tables here are for read only because of the problem of embedding the scripts. For a fully working example, check this by using Chrome.

Result:

The Database includes:

Tablename	Record
Customers	91
Categories	8
Employees	10
OrderDetails	518
Orders	196
Products	77
Shippers	3
Suppliers	29

W3C WebSQL

WebSQL Browser Support