Slide 7.4: SQL syntax (cont.)
Slide 7.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
The following is an SQL test area from W3Schools, which uses the well-known Northwind sample database and the tables are for read only.
For security reasons, the following demonstration may only work in Chrome.

SQL Statement:

Edit the SQL statement and click     to see the result, or    

Result:

Click “Run SQL” to execute the SQL statement above.
W3Schools has created an SQL database in your browser.
The menu to the right displays the database, and will reflect any changes.
Feel free to experiment with any SQL statement.
You can restore the database at any time.
The Database includes:
The Database includes:

TablenamesRecords
Customers91
Categories8
Employees10
OrderDetails518
Orders196
Products77
Shippers3
Suppliers29