Create Table in SQL - sql - sql tutorial - learn sql
- The CREATE TABLE Statement is used to create tables to store data.
- Integrity Constraints like primary key, unique key, foreign key can be defined for the columns while creating the table.
- The integrity constraints can be defined at column level or table level.
- The implementation and the syntax of the CREATE Statements differs for different RDBMS.
- A commonly used CREATE command is the CREATE TABLE command.
- The typical usage is: CREATE TABLE [table name] ( [column definitions] ) [table parameters]
Syntax:
CREATE TABLE table_name
(column_name1 datatype,
column_name2 datatype,
... column_nameN datatype
);
- table_name - is the name of the table.
- column_name1, column_name2.... - is the name of the columns
- datatype - is the datatype for the column like char, date, number etc.
For Example:
- If you want to create the employee table, the statement would be like,
CREATE TABLE employee
( id number(5),
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10)
);
Create Table Using Another Table:
- A copy of an existing table can be created using a combination of the CREATE TABLE statement and the SELECT statement.
- The new table gets the same column definitions. All columns or specific columns can be selected.
- If you create a new table using an existing table, the new table will be filled with the existing values from the old table.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialSyntax:
Syntax:
CREATE TABLE new_table_name AS
SELECT column1, column2,...
FROM existing_table_name
WHERE ....;
Six types of constraints can be placed when creating a table:
- NOT NULL Constraint: Ensures that a column cannot have NULL value.
- DEFAULT Constraint: Provides a default value for a column when none is specified.
- UNIQUE Constraint: Ensures that all values in a column are different.
- CHECK Constraint: Makes sure that all values in a column satisfy certain criteria.
- Primary Key Constraint: Used to uniquely identify a row in the table.
- Foreign Key Constraint: Used to ensure referential integrity of the data.
Example:
- To create a customer table with the fields specified in the second paragraph above, we would type in
CREATE TABLE Customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date datetime);
- No constraints were specified in the above SQL statement. What if we want to add a constraint that says the default country is 'United States'?
- In other words, if no data is entered for the "Country" column, it would be set to 'United States.' In that case, we would type the following SQL:
CREATE TABLE Customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25) default 'United States',
Birth_Date datetime);
- Sometimes the table structure we need is the same as another table in the database.
- In this case, we can also use CREATE TABLE to make a copy of the table structure so we do not need to type all the column names, data types, and constraints in detail.
- The syntax for doing this is:
CREATE TABLE "table_name" AS
[SQL Statement];
- To copy both the structure and data of Table1 into Table2, we would issue the following SQL statement:
CREATE TABLE Table2 AS
SELECT * FROM Table1;
- To copy the structure of Table1 into Table2 without any data, we would issue the following SQL statement:
CREATE TABLE Table2 AS
SELECT * FROM Table1
WHERE 0 = 1;
- The WHERE 0 = 1 clause is always false. Therefore, no row of data will be copied from Table1 to Table2. Only the table structure is copied over.