Sql – Create Command – Explanations and Examples

In this session, let us see how to create Databases and Tables in SQL. The first step in SQL is creation of database. Collection of tables, views, procedures constitutes a database.

Create Database:

In order to create a database, the CREATE DATABASE statement is used in SQL.

Syntax:

The basic syntax used to create a database is as follows:

CREATE DATABASE [database_name];

Example: 

Two or more databases can be created using CREATE DATABASE statement as shown below,

CREATE DATABASE sql; 
CREATE DATABASE demo; 
CREATE DATABASE data;

To check if the databases have been created or not, SHOW DATABASE statement as follows,

SHOW DATABASES;

Output:-
Databases:
sql
demo
data

Creating Tables:

To create a table, we use CREATE TABLE statement. In order to create and define a table the following syntax is used:

Syntax:

Unable to retrieve the Code part.
Please reload again.
Notify us if the problem still persists.
Till we work on this you can view code on URL below.
Please visit - https://github.com/HiteshGarg/codingeek/blob/master//SQL/Basic/create-command/create-syntax.sql

In the above syntax,

• table_name: name of the table you wish to create.
• column_name: column_name1, column_name2 represents the columns you want to create in the mentioned table. By default it assumes the value as NULL.
• datatype: It is the datatype of a particular column and that column will accept only the mentioned datatype.

Example:

Unable to retrieve the Code part.
Please reload again.
Notify us if the problem still persists.
Till we work on this you can view code on URL below.
Please visit - https://github.com/HiteshGarg/codingeek/blob/master//SQL/Basic/create-command/create-example.sql

Arguments:

• The Regno, Age column is of integer type and cannot contain null values.
• The Name, Place columns are of type varchar and holds a maximum of 30 characters which can contain null values.

It creates an empty table called “sql” with fields Regno, Name, Age, Place as shown below:

To see the structure of the table, the following command is used:

 
DESCRIBE sql; 

Describe command displays the table details like name, type, null or not null as shown below:

Output:-

Name                        Null?     Type
--------------------------- --------  ------
 REGNO                      NOT NULL    INT
 NAME                                 VARCHAR(30)
 AGE                        NOT NULL    INT
 PLACE                                VARCHAR(30)

Create a table using another table:

To create a new table by copying the fields from the existing table, “CREATE TABLE AS” statement is used.

Syntax:

Unable to retrieve the Code part.
Please reload again.
Notify us if the problem still persists.
Till we work on this you can view code on URL below.
Please visit - https://github.com/HiteshGarg/codingeek/blob/master//SQL/Basic/create-command/createas-syntax.sql

Examples:

Let’s consider the following “Student” table,

1) Copying all columns from another table:

We can create a new table by copying all the fields from another table based on the condition using the following syntax(along with an example):

Unable to retrieve the Code part.
Please reload again.
Notify us if the problem still persists.
Till we work on this you can view code on URL below.
Please visit - https://github.com/HiteshGarg/codingeek/blob/master//SQL/Basic/create-command/createas-syntax1.sql

Output:

This will create a table “Toppers” from table Student with all fields.

2) Copying selected columns from another table:

In order to create new table by copying only the selected columns from old table based on specific condition, following syntax is used:

Unable to retrieve the Code part.
Please reload again.
Notify us if the problem still persists.
Till we work on this you can view code on URL below.
Please visit - https://github.com/HiteshGarg/codingeek/blob/master//SQL/Basic/create-command/createas-syntax2.sql

Output:

This will create a table “Average” with selected fields id, name from the table Student.

3) Copying selected columns from multiple tables:

Let us consider another table “Grades” to clearly understand the concept:

We can copy selected fields from two or more tables and create a new table based on some condition. The following syntax is used for that:

Unable to retrieve the Code part.
Please reload again.
Notify us if the problem still persists.
Till we work on this you can view code on URL below.
Please visit - https://github.com/HiteshGarg/codingeek/blob/master//SQL/Basic/create-command/createas-syntax3.sql

Output:

This will create a new table called “multiple” with the specified fields from both the tables.

Knowledge is of no value unless you put it into practice!!

Do share and subscribe and comment below for any suggestion, query or opinion.
Keep Coding! Happy Coding 🙂

Recommended -