Table in SQL

Introduction on Table in SQL

A Table in SQL can be described as an assemblage of data or records, which should be arranged in rows and columns format. In a database, the tables are expected to be in finite number, the Columns are expected to be a finite number, while the Rows can be infinite, as columns represent the field and rows represent the data or records. A most commonly used SQL commands for tables are Create, delete, Rename & Alter.

How to Create a Table in SQL?

The syntax to create a table is as below:

CREATE TABLE table_name (
COLUMN1 datatype PRIMARY KEY,
COLUMN2 datatype,
COLUMN3 datatype,
......
);

Let us create the table ‘STUDENTS’ as below:

CREATE TABLE STUDENTS (
ROLL INT PRIMARY KEY,
NAME VARCHAR (50),
AGE INT,
SUBJECT VARCHAR (50)
);

We can insert values to the table ‘STUDENTS’ as below:

INSERT INTO STUDENTS VALUES (8,'Ram',12,'ENG');
INSERT INTO STUDENTS VALUES (11,'Priya',11,'MATH');
INSERT INTO STUDENTS VALUES (9,'Rahul',10,'SCIENCE');

After inserting the data, we can see the data inserted to the table as below:

SELECT * FROM STUDENTS;

ROLL	NAME	AGE	SUBJECT
8	Ram	12	ENGLISH
11	Priya	11	MATH
9	Rahul	10	SCIENCE

How to Drop Table in SQL?

The syntax to drop a table is as below:

DROP TABLE table_name;

DROP TABLE STUDENTS;

The above query will drop the table ‘STUDENTS’ i.e. the data and table definition for the table will be removed. So we need to be careful before executing a drop statement as all the information related to the table will be removed from the database.

How to Delete Table in SQL?

When we use the DELETE statement without the WHERE clause, all the rows of a table shall be deleted, but the table structure will remain the same. The syntax for the same is as below:

DELETE FROM table_name;

The delete statement is used in SQL to delete the records that exist in the table currently. Whenever the requirement arises and we do not want certain records, the delete statement is used along with Where clause to remove those records. The syntax for the same is as below:

DELETE FROM table_name WHERE [condition];

DELETE FROM STUDENTS WHERE SUBJECT= ‘MATH’;

The above query will provide the below result:

ROLL	NAME	AGE	SUBJECT
8	Ram	12	ENGLISH
9	Rahul	10	SCIENCE

How to Rename Table in SQL?

It so happens that sometime after we have created the table, we want to rename it. ALTER TABLE statement is used to rename the table.

ALTER TABLE table_name RENAME TO table_name_new;

If we want to alter the table name ‘STUDENTS’, it can be done as below.

ALTER TABLE STUDENTS RENAME TO STUDENT_NEW;

How to Truncate Table?

When the need arises to delete the data inside the table and the table structure such as columns etc need to remain as it is, we can use TRUNCATE statement which only deletes the data from the table but not the table.

The syntax for using the TRUNCATE statement is as below:

TRUNCATE TABLE table_name;

TRUNCATE TABLE STUDENTS;

The above query will delete the data from the table ‘STUDENTS’. The table would look like as below after the TRUNCATE operation:

SELECT * FROM STUDENTS;

ROLL	NAME	AGE	SUBJECT

Also, in other words, we can say that the TRUNCATE statement performs the same operation which can be done using the DELETE statement but without the usage of the WHERE clause.

The advantage of using the TRUNCATE statement is that we do not need to drop the table and re-create the table. Also, it is an efficient way to clear the records from the table in such cases where we do not need to worry about the Rollback.

How to Alter Table In SQL?

We use the ALTER table statement to modify the columns which exist in the tables currently. Also with this same statement, we can drop or add different constraints on the table.

Below is the syntax to add a new column to the existing table:

ALTER TABLE table_name ADD (column_name1 datatype, column_name2 datatype... column datatype);

Let us add another column ‘ADDRESS’ to the existing table ‘STUDENTS’.

ALTER TABLE STUDENTS ADD ADDRESS VARCHAR (50);

SELECT * FROM STUDENTS;

ROLL	NAME	AGE	SUBJECT	ADDRESS
8	Ram	12	ENGLISH
11	Priya	11	MATH
9	Rahul	10	SCIENCE

In the above query, the column ADDRESS is added to the table.

If we want to drop a column, it can be done by the below syntax:

ALTER TABLE table_name DROP COLUMN column_name;

Let us see the below example by dropping the column ADDRESS.

ALTER TABLE table_name DROP COLUMN ADDRESS;

The above query will provide the below result.

ROLL	NAME	AGE	SUBJECT
8	Ram	12	ENGLISH
11	Priya	11	MATH
9	Rahul	10	SCIENCE

Here we can see that the table is modified as the column ADDRESS is now removed from the table ‘STUDENTS’.

With the help of the ALTER statement, we can modify one or more columns at once.

The syntax to modify the column is as below.

ALTER TABLE STUDENTS MODIFY column_name datatype;

ALTER TABLE STUDENTS MODIFY SUBJECT VARCHAR (30);

How to Fetch Data from Table in SQL?

In SQL, the SELECT statement is used as one of the flexible mechanisms to retrieve data from the tables in databases. The syntax of a query with SELECT is as below:

SELECT column1, column2... FROM table_name;

In the above query, we need to put the columns for which we want to retrieve data from the table. In case we want to fetch the data for all the columns or the data of the entire table, we can do so by the usage of the below syntax:

SELECT * FROM table_name;

SELECT * FROM STUDENTS;

ROLL	NAME	AGE	SUBJECT
8	Ram	12	ENGLISH
11	Priya	11	MATH
9	Rahul	10	SCIENCE

Herewith the usage of a wildcard – ‘ * ’ will retrieve all the rows and columns of the table ‘STUDENTS’. This is the simplest form of the SELECT statement. However, if the requirement arises where we need to restrict the fetching of data according to a few specific conditions, then we need to use the WHERE clause. The syntax for this is as below:

SELECT column1, column2...
FROM table_name
WHERE [condition];

Let us see how to fetch the data with the specified condition using the above query. Suppose we want to select the student’s ROLL and SUBJECT whose age is greater than 10 from the ‘STUDENTS’ table.

SELECT ROLL, SUBJECT FROM STUDENTS WHERE AGE > 10;

The above query will restrict the results for only those students whose age is more than 10 years. Here the ‘WHERE’ clause will limit the result of the query to the condition specified.

ROLL	NAME	AGE	SUBJECT
8	Ram	12	ENGLISH
11	Priya	11	MATH

How to Copy Table in SQL?

We can copy the data from one table to the other by using either SELECT INTO or INSERT INTO statements. While using a SELECT INTO statement, the database does not need to have the target table to which the data will be copied to be there. But in case of using the INSERT INTO statement, the target table to which data will be copied should exist in the database.

SELECT * INTO table_new FROM table_old;

The above query will copy all the columns from table_old to table_new.

Also if we want to copy only a few columns from one to the other table, we can use the below syntax:

SELECT column1, column2...  INTO table_new FROM table_old;

Let us take an example where we want to copy the data from the table to another table ‘STUDENT_DETAILS’ and it can be performed as below:

SELECT * INTO STUDENT_DETAILS FROM STUDENTS;

Another table ‘STUDENT_DETAILS’ shall be created as below:

ROLL	NAME	AGE	SUBJECT
8	Ram	12	ENGLISH
11	Priya	11	MATH
9	Rahul	10	SCIENCE

The syntax for copying data using the INSERT INTO statement is as below:

INSERT INTO table_new(column1, column2... )
SELECT column1, column2... FROM table_old WHERE [condition];

As discussed above that the INSERT INTO statement will require the target table to be present, first we need to create the target table structure in the database. After creating the structure of table STUDENT_DETAILS, we shall go for the below query.

INSERT INTO STUDENT_DETAILS (ROLL, NAME, AGE)
SELECT ROLL, NAME, AGE FROM STUDENTS
WHERE SUBJECT = ‘SCIENCE’;

The above query will give the below result:

ROLL	NAME	AGE	SUBJECT
9	Rahul	10	SCIENCE

We should keep it in the notice that while using INSERT INTO statement, data types of columns in source and target tables must match with one another.

Conclusion

In RDBMS, the tables make the storing, retrieving, maintaining and manipulating of data in a most effective manner. With the usage of different commands, we can arrange and manipulate the data according to the business requirement. It is up to the designer/developer to see the flexibilities of the different commands and operations to choose best for their designs and implementations.

Recommended Articles

This is a guide to Table in SQL. Here we discuss the Introduction, and How to Create, Drop, Delete, Rename, Truncate, Alter, Fetch Data, and Copy table in SQL. You may also look at the following articles to learn more–

1. Foreign Key in SQL
2. Transactions in SQL
3. SQL Views
4. Database in SQL​