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 tutorial

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.

This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf

Related Searches to Create Table in SQL