Updated July 5, 2023
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. The 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 into the table 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 table rows 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 current records in the table. Whenever the requirement arises, and we do not want certain records, the delete statement is used along with the 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 happens that we want to rename the table sometime after we have created 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., needs to remain as it is, we can use the 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 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 to 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 a Table in SQL?
In SQL, the SELECT statement is used as a flexible mechanism 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 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 data fetching 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];Using the above query, let us see how to fetch the data with the specified condition. 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 to only those students whose age is more than 10 years. Here the ‘WHERE’ clause will limit the query’s result to the specified condition.
| 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 another 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 the 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, the INSERT INTO statement will require the target table to be present; first, we must create the database’s target table structure. 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 the INSERT INTO statement, data types of columns in source and target tables must match with one another.
Conclusion
In RDBMS, the tables store, retrieve, maintain, and manipulate data effective. Using 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 the best for their designs and implementations.
Recommended Articles
We hope that this EDUCBA information on “Table in SQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
