Slide 14.5: SQL create table statement

Slide 14.4: SQL syntax (cont.)
Slide 14.6: SQL insert into statement
Home

SQL Create Table Statement

The CREATE TABLE statement is used to create a table in a database. Its syntax is given on the right. The data type specifies what type of data the column can hold.

 CREATE TABLE  table_name (
   column_name1  data_type,
   column_name2  data_type,
   ....... )

The example below shows how to create a table named Persons with five columns. The P_Id column is of type int and is the primary key. The LastName, FirstName, Address, and City columns are of type varchar with a maximum length of 32, 32, 64, and 16 characters, respectively.

This statement creates an empty Persons table as below. The empty table can be filled with data with the INSERT INTO statement.

 CREATE TABLE  Persons (
   P_Id      INT  PRIMARY KEY,
   LastName  VARCHAR(32),
   FirstName VARCHAR(32),
   Address   VARCHAR(64),
   City      VARCHAR(16) );

P_Id	LastName	FirstName	Address	City

The table below contains the most common data types in SQL:

Data Type	Description
`integer(size) int(size) smallint(size) tinyint(size)`	Hold integers only. The maximum number of digits are specified in parenthesis.
`decimal(size,d) numeric(size,d)`	Hold numbers with fractions. The maximum number of digits are specified in “size.” The maximum number of digits to the right of the decimal is specified in ‘d’.
`char(size)`	Hold a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis.
`varchar(size)`	Hold a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis.
`date(yyyymmdd)`	Hold a date.
`blob`	BLOB (Binary Large OBjects) can hold up to 65,535 bytes of data.

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