SQL CREATE TABLE Statement & CREATE TABLE with SELECT statement

The SQL CREATE TABLE statement is used to create a table in a database.

Tables are organized into rows and columns; and each table must have a name.

SQL CREATE TABLE Statement Syntax

CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
….
);

The column_name parameters specify the names of the columns in the table. The data_type parameter specifies what type of data the column can hold (e.g. varchar, integer, decimal, date, etc.).
The size parameter specifies the maximum length of the column in the table.

SQL CREATE TABLE Example

Now we want to create a table called “CARS” that contains Nine columns: CARNAME, MPG, CYL, DISP,HP,VS,AM,GEAR and CARB.

We use the following CREATE TABLE statement:

Example of SQL CREATE TABLE Statement:

CREATE TABLE CARS(
carname varchar(40),
mpg decimal(4,1),
cyl int,
disp decimal(4,1),
hp int,
vs int,
am int,
gear int,
carb int
);

The CARNAME column is of type varchar and will hold characters. MPG and DISP column contains decimal data types.
CYL, HP, VS,AM,GEAR and CARB columns are of type integer.

The output of the above code result in creation of an empty table named CARS

carnamempgcyldisphpvsamgearcarb

The empty table can be filled with data with the INSERT INTO statement.
use the below insert code to insert the values into the table

insert into cars values('Mazda RX4',21,6,160,110,0,1,4,4);
insert into cars values('Mazda RX4 Wag',21,6,160,110,0,1,4,4);
insert into cars values('Datsun 710',22.8,4,108,93,1,1,4,1);
insert into cars values('Hornet 4 Drive',21.4,6,258,110,1,0,3,1);
insert into cars values('Hornet Sportabout',18.7,8,360,175,0,0,3,2);
insert into cars values('Valiant',18.1,6,225,105,1,0,3,1);
insert into cars values('Duster 360',14.3,8,360,245,0,0,3,4);
insert into cars values('Merc 240D',24.4,4,146.7,62,1,0,4,2);
insert into cars values('Merc 230',22.8,4,140.8,95,1,0,4,2);
insert into cars values('Merc 280',19.2,6,167.6,123,1,0,4,4);
insert into cars values('Merc 280C',17.8,6,167.6,123,1,0,4,4);
insert into cars values('Merc 450SE',16.4,8,275.8,180,0,0,3,3);
insert into cars values('Merc 450SL',17.3,8,275.8,180,0,0,3,3);
insert into cars values('Merc 450SLC',15.2,8,275.8,180,0,0,3,3);
insert into cars values('Cadillac Fleetwood',10.4,8,472,205,0,0,3,4);
insert into cars values('Lincoln Continental',10.4,8,460,215,0,0,3,4);
insert into cars values('Chrysler Imperial',14.7,8,440,230,0,0,3,4);
insert into cars values('Fiat 128',32.4,4,78.7,66,1,1,4,1);
insert into cars values('Honda Civic',30.4,4,75.7,52,1,1,4,2);
insert into cars values('Toyota Corolla',33.9,4,71.1,65,1,1,4,1);
insert into cars values('Toyota Corona',21.5,4,120.1,97,1,0,3,1);
insert into cars values('Dodge Challenger',15.5,8,318,150,0,0,3,2);
insert into cars values('AMC Javelin',15.2,8,304,150,0,0,3,2);
insert into cars values('Camaro Z28',13.3,8,350,245,0,0,3,4);
insert into cars values('Pontiac Firebird',19.2,8,400,175,0,0,3,2);
insert into cars values('Fiat X1-9',27.3,4,79,66,1,1,4,1);
insert into cars values('Porsche 914-2',26,4,120.3,91,0,1,5,2);
insert into cars values('Lotus Europa',30.4,4,95.1,113,1,1,5,2);
insert into cars values('Ford Pantera L',15.8,8,351,264,0,1,5,4);
insert into cars values('Ferrari Dino',19.7,6,145,175,0,1,5,6);
insert into cars values('Maserati Bora',15,8,301,335,0,1,5,8);
insert into cars values('Volvo 142E',21.4,4,121,109,1,1,4,2);

so the completed cars table will be

CARNAMEMPGCYLDISPHPVSAMGEARCARB
Mazda RX42161601100144
Datsun 71022.84108931141
Duster 36014.383602450034
Merc 28019.26167.61231044
Merc 450SE16.48275.81800033
Toyota Corolla33.9471.1651141
Pontiac Firebird19.284001750032
Ferrari Dino19.761451750156
Maserati Bora1583013350158
Volvo 142E21.441211091142

Note: we will be using this table for our further demos

 

CREATE TABLE with SELECT statement:

Other way to create table is

CREATE TABLE table_name AS
(select * from table_name_old WHERE condition1);

Example: lets create a cars_new table from cars table which has only 4 or 8 cyl

create table cars_new as (select * from cars where cyl=4 or cyl=8);

The resultant cars_new table will be

carnamempgcyldisphpvsamgearcarb
Datsun 71022.84108931141
Hornet Sportabout18.783601750032
Duster 36014.383602450034
Merc 240D24.44146.7621042
Merc 23022.84140.8951042
Merc 450SE16.48275.81800033
Merc 450SL17.38275.81800033
Merc 450SLC15.28275.81800033
Cadillac Fleetwood10.484722050034
Lincoln Continental10.484602150034
Chrysler Imperial14.784402300034
Fiat 12832.4478.7661141
Honda Civic30.4475.7521142
Toyota Corolla33.9471.1651141
Toyota Corona21.54120.1971031
Dodge Challenger15.583181500032
AMC Javelin15.283041500032
Camaro Z2813.383502450034
Pontiac Firebird19.284001750032
Fiat X1-927.3479661141
Porsche 914-2264120.3910152
Lotus Europa30.4495.11131152
Ford Pantera L15.883512640154
Maserati Bora1583013350158
Volvo 142E21.441211091142

previous small sql create table statement                                                                                                                next small sql create table statement