Home » SQL

SQL - CREATE Command (to create Database and Table)

SQL - CREATE Command: Here, we will learn how to create a database and a table using CREATE command in SQL?
Submitted by Shubham Singh Rajawat, on August 09, 2017

CREATE is a DDL command used to create table or database in SQL.

1. Creating Database

A new Database can be created in SQL by using the CREATE DATABASE statement. For e.g. to create a new Database of name 'MyDatabase' following statement should be executed.

CREATE DATABASE MyDatabase;

If a Database of name MyDatabase does not exist it will create a new database else it will show error.

ERROR 1007
Can’t create database ‘MyDatabase’ ; database exists

And Database will not be created unless you change the name of the Database.

2. Creating Table

A new Table can be created in SQL by using the CREATE TABLE statement. A new table can be created by using following syntax.

CREATE TABLE table_name
(column_name  datatype [constraint],
 column_name  datatype [constraint],
 column_name  datatype [constraint],...);

Where,
column_name is the name of the column
datatype is the attribute that tells what kind of data a column can store

Following are the data types in SQL

1. char(size)
   A string of characters of length size. size can have a maximum value of 255 characters.
2. date
   It stores date
3. varchar(size)
   A string of character of length size.
4. numeric(maxsize)
   Number with maximum number of digits maxsize.

Constraints

constraints are the rules for the column.

Following are the possible values of a constraint can have:

1. NOT NULL
   The column must have some value cannot be left empty.
2. UNIQUE
   No two values can be same inside this column.
3. PRIMARY KEY
   Each record in the table is uniquely identified by this column.

For example: to create a table student with columns name, roll no, age, date of birth, following statement needs to be executed

CREATE TABLE student
( roll_no numeric(15) PRIMARY KEY NOT NULL,
name varchar(25),
age numeric(2), 
dob date);