How to CREATE TABLE in SQL

How to CREATE TABLE in SQL

3 mins read3.7K Views Comment
clickHere
Vikram
Vikram Singh
Assistant Manager - Content
Updated on Mar 20, 2023 12:19 IST

When you are working with the large dataset sometime you want to segregate some data and inspect them separately or sometimes you don’t have any table or dataset in that case you have to create your own table. In this article we will discuss how to create, alter and delete table in SQL.

2022_07_MicrosoftTeams-image-225.jpg

Table of Content

Table in SQL

Tables are database objects that contain all the data and these data are arranged in columns and rows. 

  • In the table, the column represents a field and each row represents a unique record.
  • Fields in the table are associated with unique data type
  • A user-defined table can have up to 1024 columns

Must Read: Introduction to SQL

Must Read: SQL SELECT

Create Table in SQL

To create Tables in SQL, we must have the table’s name, the column’s name, the data type of the column, and the size ( size is used to define the maximum length of data that can be input into the column).

Syntax

 
CREATE TABLE table_name(
column_name_1 datatype(size) column_constraints,
column_name_2 datatype(size) column_constraints,
..
..
);
Copy code

Must Read: SQL Comparison and Arithmetic Operator

Must Read: Types of Keys in Database

Now, let’s take an example to understand how to create a table in SQL

Example – 1: Create an employee table that contains Employee ID (as a primary key), name, gender, department, and Salary.

 
CREATE TABLE Employee(
EmployeeID int PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Gender text NOT NULL,
Department VARCHAR(30),
Salary VARCHAR (20)
);
Copy code

Note: 

1. Mainly there are three data types that are used while creating the tables:

int:

  •  it is used to store numbers 
  • Example: 10, 20, 11, -15

text:

  • Used to store the text of length 65535 characters
  • Example: Shiksha Online is an online platform to get the best course.

varchar(size):

  • Used to store the variable character of user-defined length
  • Example: Shiksha Online

2. Here PRIMARY KEY, NOT NULL all are SQL constraints.

Databases consist of many tables, sometime it may be possible that you are creating any table that already exists in your database. So, if you will create another table with the same name that will create a mess.

So, let’s see how to tackle this situation.

Must Read: What is the difference between SQL and MySQL?

Must Read: Difference between SQL and NoSQL

CREATE TABLE IF NOT EXISTS

The above-discussed situation is very simple to tackle, we just have to replace CREATE TABLE with CREATE TABLE IF NOT EXISTS in the above-given syntax.

Example – 2: Create an employee table that contains Employee ID (as a primary key), name, gender, department, and Salary if it doesn’t exist in the database.

 
CREATE TABLE IF NOT EXISTS Employee(
EmployeeID int PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Gender text NOT NULL,
Department VARCHAR(30),
Salary VARCHAR (20)
);
Copy code

Must Read: SQL ORDER BY

Must Read: SQL WHERE

Create a table using another table

In SQL, we can create a table using the records of an existing table. It can also be used as a backup of data.

Syntax

 
CREATE TABLE table_name
AS(
SELECT column_names
FROM original_table_name
);
Copy code

Must Read: Subqueries in SQL

Must Read: Aggregate and Scalar Function in SQL

ALTER TABLE

Alter means make something different without changing it completely similarly ALTER command in SQL is used to perform the similar task in table like:

  • Rename table name

Syntax

 
ALTER TABLE previous_name RENAME new_name;
Copy code
  • Add a new column in the table

Syntax

 
ALTER TABLE table_name add column_name data_type(size);
Copy code
  • Delete columns from the existing table

Syntax

 
ALTER TABLE table_name
DROP COLUMN column_name;
Copy code

Must Read: How to Create, Insert, and Delete SQL views?

Must Read: Introduction to Normalization

DELETE TABLE

In SQL, a table can be deleted in two ways:

  • Records are deleted with the structure of a table

DROP command is used to delete all the records with the structure of a table.

Syntax

 
DROP TABLE table_name;
Copy code
  • Records are deleted without removing the structure of a table

DELETE FROM command is used to delete all the records without any changes in the structure of the table

Syntax

 
DELETE FROM table_name;
Copy code

Must Read: SQL LIMITS

Must Check: SQL Online Course and Certifications

Conclusion

In this article, we have discussed different methods how to create, alter and delete table in SQL.

Hope this article will help in your data science/data analysis journey.

About the Author
author-image
Vikram Singh
Assistant Manager - Content

Vikram has a Postgraduate degree in Applied Mathematics, with a keen interest in Data Science and Machine Learning. He has experience of 2+ years in content creation in Mathematics, Statistics, Data Science, and Mac... Read Full Bio

Comments