SQL CREATE TABLE Statement

This statement is used to create table in the database.

Syntax:

CREATE TABLE “TABLE NAME”
(“column1” “data type”,
“column2” “data type”,


“columnN” “data type”);

Example:

CREATE TABLE OLYMPIANS (

RANK INT NOT NULL,

NAME VARCHAR(30) NOT NULL,

COUNTRY VARCHAR(20) NOT NULL,

MEDAL VARCHAR(10) ,

PRIMARY KEY (RANK)

);

This will create a table like with below columns wit zero rows when we run a query SELECT * FROM OLYMPIANS

RANKNAMECOUNTRYMEDAL

CREATE TABLE …. AS Statement

You can create table from another table using this statement

Syntax:

CREATE TABLE new_tablename AS SELECT column1, column2,column5 from desired_table WHERE ….;

Example:

We have a table CATEGORIES

category_idcategory_namedescription
1BeveragesSoft drinks, coffees, teas, beers, and ales
2CondimentsSweet and savory sauces, relishes, spreads, and seasonings
3ConfectionsDesserts, candies, and sweet breads
4Dairy ProductsCheeses
5Grains/CerealsBreads, crackers, pasta, and cereal
6Meat/PoultryPrepared meats
7ProduceDried fruit and bean curd
8SeafoodSeaweed and fish

We need to create a new table EATABLEs.

CREATE TABLE EATABLE AS SELECT category_name, description from categories WHERE category_id <5;

category_namedescription
BeveragesSoft drinks, coffees, teas, beers, and ales
CondimentsSweet and savory sauces, relishes, spreads, and seasonings
ConfectionsDesserts, candies, and sweet breads
Dairy ProductsCheeses

Learn more : SQL Tutorial , SQL Interview Questions