Create Table in SQL

Shivani Kumari
Last Updated: May 13, 2022

Introduction

The database is a collection of data organized as tables, schemas, views, reports, etc. It allows efficient retrieval, insertion, or deletion of the data. 

For example, College data is organized about students, faculties, courses, staff, etc.,  bank data is collected about customers, account names, transactions, etc. This data management helps in easy retrievals, insertion, and deletion of data.

A table is a collection of related organized data. It contains columns and rows.

To create a table in SQL, we need to add the table’s name, define its columns, and define each column’s data type.

Syntax

CREATE TABLE table_name(


  column1 datatype,
  column2 datatype,
  .....
  columnN datatype,
);

 

The CREATE TABLE keyword tells the database to create a new table.

MySQL CREATE TABLE

Syntax

CREATE TABLE [IF NOT EXISTS] table_name(
  column_1_definition,
  column_2_definition,

   column_3_definition,

   .

   ..,
  table_constraints
) ENGINE=storage_engine;

 

Let's understand the syntax better.

  • After the CREATE TABLE keywords, you specify the table's name you want to create. Within a database, the table name must be unique. The IF NOT EXISTS statement is not necessary. It lets you see if the table you're creating is already in the database. MySQL will ignore the entire command if this is the case, and no new table will be created.
  • Second, in the column list part, you provide a list of the table's columns, separated by commas.
  • Finally, the ENGINE clause allows you to specify the table's storage engine. Any storage engine, such as InnoDB and MyISAM, can be used. If you don't select a storage engine, MySQL will use InnoDB by default.


The following is the syntax for defining a column:

columnName dataType(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT] column_constraint;

 

The following are the specific details:

  • The columnName specifies the column's name. Each column has its data type and length options, such as VARCHAR (255).
  • The NOT NULL constraint prevents the column from containing NULL values. A column may have extra constraints, such as CHECK and UNIQUE, in addition to the NOT NULL condition.
  • The DEFAULT provides the column's default value.
  • The AUTO_INCREMENT specifies that the column's value is automatically incremented by one whenever a new row is added to the database. A maximum of one AUTO_INCREMENT column exists in each table.
  • Table constraints such as UNIQUE, CHECK, PRIMARY KEY, and FOREIGN KEY can be defined after the column list.


A Foreign Key is a field in a table that refers to the PRIMARY KEY in a different table. The child table is the one with the foreign key, while the referenced or parent table has the primary key. 

A primary key is  set of columns( or a single column) that uniquely identifies the rows in that table. You can create a unique identifier for each entry in your database using the primary key. It's essential as it allows you to use primary keys to link your table to other tables. To set a column or a group of columns as the primary key, use the following syntax:

PRIMARY KEY (col1,col2,...)

 

Example:

In the below given an example, we created a new STUDENTS table with an ID as a primary key, and NOT NULL are the constraints showing that these fields cannot be NULL.

CREATE TABLE STUDENTS(
  ID   INT,              NOT NULL,
  NAME VARCHAR (25),     NOT NULL,
  AGE  INT,              NOT NULL,
  EMAIL  VARCHAR (50),      
  PRIMARY KEY (ID)
);

 

You can also use  DESC/ DESCRIBE command to check if the table is created or not as follows:

SQL > DESC STUDENTS;

 

DESC followed by table name.

Now, you have a STUDENT table available in your database.

Frequently Asked Questions

  1. What is DBMS?
    DBMS (database management system) is software that allows you to organize (create, retrieve, update, and manage) data in a database.
     
  2. What is SQL?
    SQL stands for Structured Query Language, used by databases.
     
  3. What is a database?
    A database is a collection of data that is organized in the form of tables, schemas, views, or reports, etc. It allows efficient retrieval, insertion, or deletion of the data. 

Key Takeaways

In this article, you learned to create new tables in SQL. Try out different examples with different schemas and databases to fully understand the concept.

Don't stop here, Ninja. Check out the Top 100 SQL Questions to get hands-on experience with frequently asked interview questions and land your dream job.

Click here. Enroll today, practice problems asked in technical interview rounds, and learn tricks to solve them faster and more confidently.

Apart from that, you can use CodeStudio to practice a wide range of DSA questions asked in lots of interviews. It will assist you in mastering efficient coding techniques, and you will also get interview experiences from people working in big companies.

Was this article helpful ?
1 upvote

Comments

No comments yet

Be the first to share what you think