BeginnersBook

  • Home
  • Java
    • Java OOPs
    • Java Collections
    • Java Examples
  • C
    • C Examples
  • C++
    • C++ Examples
  • DBMS
  • Computer Network
  • Python
    • Python Examples
  • More…
    • jQuery
    • Kotlin
    • WordPress
    • SEO
    • JSON
    • JSP
    • JSTL
    • Servlet
    • MongoDB
    • XML
    • Perl

SQL – CREATE TABLE Statement

Last Updated: September 10, 2022 by Chaitanya Singh | Filed Under: SQL

CREATE TABLE statement is used for creating tables in a database. Tables are organized in rows and columns. Where columns are the attributes and rows are known as records.

Syntax:

CREATE TABLE tableName
(
columnName_1 data_type,
columnName_2 data_type,
columnName_3 data_type,
columnName_4 data_type,
....
....
PRIMARY KEY (Column_Name(s))
);

CREATE TABLE Example

SQL> CREATE TABLE EMPLOYEES(
   SSN  CHAR(10)         NOT NULL,
   EMP_NAME VARCHAR(35)  NOT NULL,
   EMP_AGE  INT          NOT NULL,
   EMP_ADDR  VARCHAR(40) ,     
   PRIMARY KEY (SSN)
);

The above statement would create a table named “EMPLOYEES” in the database having primary key as “SSN”.
Note: Since SSN is a primary key it cannot has duplicate values as this column is used for identifying the unique record (row) from table.

SQL> DESC CUSTOMERS;
+---------+---------------+------+-----+---------+
| Field   | Type          | Null | Key | Default | 
+---------+---------------+------+-----+---------+
| SSN     | int(11)       | NO   | PRI |         |       
| EMP_NAME| varchar(20)   | NO   |     |         |       
| EMP_AGE | int(11)       | NO   |     |         |      
| EMP_ADDR| char(25)      | YES  |     | NULL    |      
+---------+---------------+------+-----+---------+
5 rows in set (0.00 sec)

Once the table is created you can perform the other operations like insert, delete, truncate etc.

CREATE TABLE from an existing table

Syntax:

CREATE TABLE TableName AS
   SELECT (columnName_1, columnName_2...)
   FROM OldTableName
   WHERE Clause

Example:

Lets say we have a table STUDENT having below records:

+---------+---------------+------+
| RollNo  | Name          | Age  | 
+---------+---------------+------+
| 1234    | Abey          | 23   |  
| 256     | Ajeet         | 21   |    
| 22      | Chaitanya     | 22   |    
| 890     | Anuj          | 23   |    
| 123     | Rahul         | 18   |   
+---------+---------------+------+

Now I want to create a table named “AGE” from the above table. This is how I should be doing it:

SQL> CREATE TABLE AGE AS
   SELECT RollNo, Age
   FROM STUDENT;

The new table “AGE” would be having the following records:

+---------+------+
| RollNo  | Age  | 
+---------+------+
| 1234    | 23   |  
| 256     | 21   |    
| 22      | 22   |    
| 890     | 23   |    
| 123     | 18   |   
+---------+------+

Top Related Articles:

  1. Introduction to SQL
  2. DEFAULT Constraint in SQL
  3. SQL UPDATE Statement
  4. SQL – DROP Table Statement to delete the entire table
  5. SQL SELECT Database – USE Statement

About the Author

I have 15 years of experience in the IT industry, working with renowned multinational corporations. Additionally, I have dedicated over a decade to teaching, allowing me to refine my skills in delivering information in a simple and easily understandable manner.

– Chaitanya

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

SQL Tutorial

  • SQL Tutorial
  • SQL Introduction
  • SQL Syntax
  • SQL Data Types

SQL Database

  • SQL CREATE DB
  • SQL DROP DB
  • SQL Rename DB
  • SQL USE DB

SQL Queries

  • SQL Select
  • SQL Select Distinct
  • SQL Select Count
  • SQL Select Top
  • SQL Where
  • SQL AND, OR & NOT
  • SQL Order By
  • SQL Insert Into
  • SQL Insert Into SELECT
  • SQL Select Random
  • SQL Alias
  • SQL NULL Check
  • SQL Update
  • SQL Delete
  • SQL MIN, MAX
  • SQL SUM
  • SQL AVG

Copyright © 2012 – 2025 BeginnersBook . Privacy Policy . Sitemap