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
RANK | NAME | COUNTRY | MEDAL |
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_id | category_name | description |
---|---|---|
1 | Beverages | Soft drinks, coffees, teas, beers, and ales |
2 | Condiments | Sweet and savory sauces, relishes, spreads, and seasonings |
3 | Confections | Desserts, candies, and sweet breads |
4 | Dairy Products | Cheeses |
5 | Grains/Cereals | Breads, crackers, pasta, and cereal |
6 | Meat/Poultry | Prepared meats |
7 | Produce | Dried fruit and bean curd |
8 | Seafood | Seaweed 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_name | description |
---|---|
Beverages | Soft drinks, coffees, teas, beers, and ales |
Condiments | Sweet and savory sauces, relishes, spreads, and seasonings |
Confections | Desserts, candies, and sweet breads |
Dairy Products | Cheeses |
Learn more : SQL Tutorial , SQL Interview Questions