07 - SQL CREATE.

Before starting with SQL in detail, we need to understand the requirements to create database/tables and then execute the queries. Follow below steps to create database.

  1. Get the requirements as how the data needs to be used, what will be input and output of the application.
  2. What will be the logical relationship between different data.
  3. Based on the data and normalization form, plan the number of tables required, what will be the relationship between tables.
  4. Identify the primary and foreign keys of each table and set the constraints for each table.

Once this is done, we are ready to start with the creation of databases, tables and retrieving data using queries. 

SQL CREATE    

As part of the Data Definition Language (DDL) under SQL, CREATE keyword in used to create databases and tables. 

Please note, for example purpose would be using MySQL.

a. CREATE Database.

Below screenshot shows the command used to check how many databases have been in created in your Database Management System(DBMS). Use the command “SHOW DATABASES;”

+--------------------------------------+
| Database                       |
+--------------------------------------+
| information_schema             |
| creditdb                       |
| mysql                          |
| performance_schema             |
| sakila                         |
| test                           |
+--------------------------------------+

6 rows in set (0.03 sec)

 SQL Syntax for Database creation is as below:

 CREATE DATABASE databaseName

Where databaseName should be unique. If you use same name, then you will get error that database already exists.

For example let us create a database for Employee system, then use below statement. 

 Mysql>CREATE DATABASE employeedb;

 Please note, you should have administrative rights to create databases.

 Once the database is created you would receive message as below:

  mysql>  create database employeedb;

  Query OK, 1 row affected (0.01 sec)

Once the database is created, you can check it in the database list as below:

+---------------------------------------+
| Database                       |
+---------------------------------------+
| information_schema             |
| creditdb                       |
| employeedb                     |
| moviesapp                      |
| mysql                          |
| performance_schema             |
| sakila                         |
| test                           |
| world                          |
+---------------------------------------+

9 rows in set (0.03 sec)

b. CREATE Table.

The SQL CREATE TABLE statement allows you to create and define tables in a database.

Please note that while creating tables, the table name should be unique in the database, else you would get an error message and table would not be created.

Syntax:

CREATE TABLE tablename

( column1 datatype NULL/NOT NULL,

  Column2 datatype NULL/NOT NULL

…………………

…………………

  PRIMARY KEY (column name or names),

 FOREIGN KEY(column name) REFERENCES Tablename(column name)

)

Note: Foreign key constraint is optional.

Example1:

To understand about the creation of the table, consider the example where we need to create a table capturing the details of employees under database employeedb

Mysql>CREATE TABLE employee(

            Emp_ID           INT                  NOT NULL,

            Emp_Name    Varchar(20)     NOT NULL,

            AGE                INT                  NOT NULL,

            Phone_Num    INT);

Once the table is created, you can verify if the table has been created correctly or not using DESC or DESCRIBE command

+-------------------+-----------------+---------+--------+---------+--------+
| Field      | Type              | Null | Key    | Default | Extra |
+-------------------+-----------------+---------+--------+---------+--------+
| Emp_ID     | int(11)           | NO   |        | NULL    |       |
| Emp_Name   | varchar(20)       | NO   |        | NULL    |       |
| AGE        | int(11)           | NO   |        | NULL    |       |
| Phone_Num  | int(11)           | YES  |        | NULL    |       |
+-------------------+-----------------+---------+--------+---------+--------+

4 rows in set (0.19 sec)

Example 2:

But here the Emp_ID is a unique number for each employee and hence can be used as primary key for the table. Hence the table with primary key should be created as below:

Mysql>CREATE TABLE employee(

            Emp_ID           INT                  NOT NULL,

            Emp_Name    Varchar(20)     NOT NULL,

            AGE                INT                  NOT NULL,

            Phone_Num    INT,

           PRIMARY KEY(Emp_ID));

Once the table is created, you can verify if the table has been created correctly or not  using DESC or DESCRIBE command

Mysql>DESC employee;

The screenshot is as below:

+--------------------+--------------------+------+-----+------------+------------+
| Field      | Type           | Null | Key | Default  | Extra     |
+--------------------+--------------------+------+-----+------------+------------+
| Emp_ID     | int(11)        | NO   | PRI | NULL     |           |
| Emp_Name   | varchar(20)    | NO   |     | NULL     |           |
| AGE        | int(11)        | NO   |     | NULL     |           |
| Phone_Num  | int(11)        | YES  |     | NULL     |           |
+--------------------+--------------------+------+-----+------------+------------+

Example 3:

Now suppose there is one more table of Departments where the department details are stored. Each employee would belong to some department hence the department would be related column, and so need to create foreign key constraint for relationship with Department table.

First create the Departments table.

Mysql>CREATE TABLE DEPARTMENTS(

            DEPT_ID        INT                              NOT NULL,

            DEPT_NAME VARCHAR(20)           NOT NULL,

            PRIMARY KEY (DEPT_ID));

And now create employee table with the foreign key as well.

Mysql>CREATE TABLE employee(

            Emp_ID           INT                  NOT NULL,

            Emp_Name    Varchar(20)     NOT NULL,

            AGE                INT                  NOT NULL,

            Phone_Num    INT,

            Dept_ID           INT                  NOT NULL,

PRIMARY KEY(Emp_ID),

FOREIGN KEY(DEPT_ID) REFERENCES DEPARTMENTS(DEPT_ID));

Once the table has been created successfully verify the table structure using DESC or DESCRIBE tablename statement

Mysql>DESC employee;

+-------------------+-----------------+-------+----------+---------+-----------------+
| Field          | Type               | Null  | Key     | Default | Extra           |
+-------------------+-----------------+-------+----------+---------+-----------------+
| Emp_ID         | int(11)            | NO    | PRI     | NULL    |                 |
| Emp_Name       | varchar(20)        | NO    |         | NULL    |                 |
| AGE            | int(11)            | NO    |         | NULL    |                 |
| Phone_Num      | int(11)            | YES   |         | NULL    |                 |
| Dept_ID        | int(11)            | NO    | MUL     | NULL    |                 |
+-------------------+-----------------+-------+----------+---------+-----------------+
5 rows in set (0.06 sec)

CREATE TABLE AS:

CREATE TABLE AS statement is used to create and insert records using data from multiple tables. The new table is created from an existing table by copying the existing data.

Creating TABLE with same columns as old table:

CREATE TABLE table_name1

AS {SELECT * FROM old_table

WHERE CONDITION};

Example 1:

Creating new table with all columns as other table.

Consider a table of Customers, suppose we have to create a table with details of old customers in a new table then we need to query as below.

CREATE TABLE OLD_CUSTOMER

AS (SELECT * FOM CUSTOMER

WHERE CREATE_DATE < 01-01-2001);

This would create a new table OLD_CUSTOMER, with all the columns as CUSTOMER and would records which have CREAE_DATE < 01-01-2001.

Creating new table with selected columns from other table.

CREATE TABLE table_name1

AS {SELECT col1, col2, col3,….colN FROM old_table

WHERE CONDITION}; 

Example 2:

Consider same example as creating table of old customers but only with selected columns. Then the command would be.

CREATE TABLE OLD_CUSTOMER

AS (SELECT EMP_ID, EMP_NAME, ADDR, EMAIL_ID, PHONE, CREATE_DATE  FROM CUSTOMER

 WHERE CREATE_DATE < 01-01-2001); 

This would create OLD_CUSTOMER with only the selected columns. 

Like us on Facebook