• Articles
  • Tutorials
  • Interview Questions

How to Create and Drop Tables in SQL?

Tutorial Playlist

How to Create and Drop Table in SQL

A database contains data, and to store data we need a table. Tables are the fundamental level of a database to store data. It comprises columns, which contain a set of data values of a particular type, and rows, which contains a single data item. In this tutorial section, we will discuss on:

Watch this How to Create Table in SQL video

How to Create Table in SQL?

Let’s learn how to create table in SQL with the following example.
Table name will be ‘employee’, and this table comprises employee ID, employee name, employee salary, age, gender and the department in which the employee works
Steps to Create a Table in SQL
Step 1: Creating a table which involves naming the table
Step 2: Defining its columns which are part of the table
Step 3: Assigning a data type to each column
Steps to Create a Table in SQL 1

Syntax of create table in SQL with an example

CREATE TABLE  tablename(
column1 datatype,
column2 datatype,
…
columnN datatype,
PRIMARY KEY(one or more columns));

where CREATE TABLE is the keyword, tablename is the name of the table name, columns1 to columnN are the set of columns, and PRIMARY KEY is a constraint followed by a semicolon
Let’s create an employee table

Create the table, employee(
e_id int not null,
e_name varchar(20),
e_salary int,
e_age int,
e_gender varchar(20),
e_dept varchar (20),
primary key(e_id)
);
  • Not null is the constraint used to signify that all cells of this column must have a value
  • Varchar stands for variable length character, and the value passed is the maximum length of the character
  • Primary key constraints help uniquely identify all records from the table, and a table cannot have more than one primary key

Syntax

  • After writing the query, click on the execute button to check for errors
  • Once the query is executed, a message appears like ‘Commands completed successfully’

Become a Database Architect

How to Drop Table in SQL?

It is used to delete a table from the database.

Syntax of Drop Table in SQL with an example

DROP TABLE TableName;

where DROP TABLE is the keyword and tablename is the name of the table followed by a semicolon

  • Now, let us see an example of dropping a table
DROP TABLE test;

SQL Drop Table

  • After writing the query, click on the execute button to check for errors
  • Once the query is executed, a message appears like ‘Commands completed successfully’.

We have successfully created the employee table and had a quick brief on how to drop a table in SQL! In the next tutorial section, we will discuss on how to insert values into the table.

Wish to crack SQL job interviews? Intellipaat’s SQL Interview Questions are meant only for you!

Course Schedule

Name Date Details
SQL Training 29 Jun 2024(Sat-Sun) Weekend Batch
View Details
SQL Training 06 Jul 2024(Sat-Sun) Weekend Batch
View Details
SQL Training 13 Jul 2024(Sat-Sun) Weekend Batch
View Details

About the Author

Data Engineer

As a skilled Data Engineer, Sahil excels in SQL, Business Intelligence, and database management. He has contributed to projects at companies like Bajaj and Tata. With a background in software engineering, he crafted efficient solutions for data pipelines, analytics, and software integration, driving insights and innovation.