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:

CREATE TABLE table_name
( 
  column_name1 datatype [NULL | NOT NULL],
  column_name2 datatype [NULL | NOT NULL],
  column_name3 datatype [NULL | NOT NULL],
  ...
);

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:

CREATE TABLE sql(
	Regno int NOT NULL,
	Name varchar(30),
	Age int,
	Place varchar(30)
);

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:

RegnoNameAgePlace

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:

CREATE TABLE new_table_name AS
    SELECT column1, column2,...
    FROM existing_table
    WHERE ...;

Examples:

Let’s consider the following “Student” table,

IDStudent NameMark1Mark2Total
1diya7281153
2rita91 89180
3 payal8876164
4 anuk 87 85172
5 nair 83 92 175

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):

-- SYNTAX

CREATE TABLE new_table_name
AS (SELECT * FROM old_table_name
    WHERE condition);

	
-- EXAMPLE

CREATE TABLE Toppers
AS (SELECT * FROM Student
    WHERE Total > 170);

Output:

IDStudent NameMark1Mark2Total
2rita 9189180
 4anuk 87 85 172
 5 nair 83 92 175

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:

-- SYNTAX

CREATE TABLE new_table_name
  AS (
	SELECT column_name1,column_name2...
      FROM old_table_name
      WHERE condition
);

-- EXAMPLE

CREATE TABLE Average
  AS (SELECT id,name
      FROM Student
      WHERE Total < 170);

Output:

IDStudent Name
1diya
3payal

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:

IDNameGrade
1diyaD
2ritaA
3payalC
4anukB
5nairB

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:

-- SYNTAX

CREATE TABLE new_table_name
  AS (
	SELECT table_name.column1,table_name.column2...
      FROM table_name1,table_name2
      WHERE condition
);

-- EXAMPLE

CREATE TABLE multiple
  AS (
	SELECT Student.id,Grade.name,Student.Total,Grades.Grade
      FROM Student,Grade
      WHERE Student.id = Grade.id
      AND Student.Total > 170
);

Output:

IDNameTotalGrade
2rita180A
4anuk172B
5nair175B

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 🙂