How to CREATE TABLE in SQL
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.
Table of Content
Best-suited Data Analytics courses for you
Learn Data Analytics with these high-rated online courses
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, ….. …..);
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));
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));
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);
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;
- Add a new column in the table
Syntax
ALTER TABLE table_name add column_name data_type(size);
- Delete columns from the existing table
Syntax
ALTER TABLE table_nameDROP COLUMN column_name;
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;
- 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;
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.
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
Top Picks & New Arrivals
