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
- Table in SQL
- Create Table in SQL
- ALTER TABLE
- DELETE TABLE
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).
CREATE TABLE table_name(column_name_1 datatype(size) column_constraints,column_name_2 datatype(size) column_constraints,…..…..);
CREATE TABLE Employee(EmployeeID int PRIMARY KEY,Name VARCHAR(100) NOT NULL,Gender text NOT NULL,Department VARCHAR(30),Salary VARCHAR (20));
1. Mainly there are three data types that are used while creating the tables:
- it is used to store numbers
- Example: 10, 20, 11, -15
- Used to store the text of length 65535 characters
- Example: Shiksha Online is an online platform to get the best course.
- 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: 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));
CREATE TABLE table_nameAS(SELECT column_namesFROM original_table_name);
ALTER TABLE previous_name RENAME new_name;
- Add a new column in the table
ALTER TABLE table_name add column_name data_type(size);
- Delete columns from the existing table
ALTER TABLE table_nameDROP COLUMN column_name;
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
DELETE FROM table_name;
Download this article as PDF to read offlineDownload as PDF