EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials SQL Tutorial Table in SQL
Secondary Sidebar
SQL Tutorial
  • Advanced
    • MDF File in SQL Server
    • SQL Aliases
    • SQL Hosting
    • SQL Auto Increment
    • SQL Injection
    • SQL Wildcards
    • SQL Check
    • SQL Indexes
    • Select Distinct
    • SQL BETWEEN
    • SQLPlus spool
    • SQL Create Table
    • SQL Schema
    • Comparison Operators in SQL
    • SQL_plus
    • SQL Formatter
    • SQL LEFT INNER JOIN
    • SQL Plus Command
    • SQLPlus not found
    • SQL Injection Attack
    • Aggregate Functions in SQL
    • SQL REVOKE
    • SQL Select Distinct Count
    • IF ELSE Statement in SQL
    • SQL CASE Statement
    • SQL While Loop
    • SQL BIGINT
    • SQL Crosstab
    • SQL Wildcard Character
    • SQL INSTR()
    • SQL now
    • SQL synonyms
    • SQLite?export to csv
    • What is Procedure in SQL
    • Stored Procedure in SQL?
    • SQL Server Constraints
    • SQL DELETE ROW
    • Column in SQL
    • Table in SQL
    • SQL Virtual Table
    • SQL Merge Two Tables
    • SQL Table Partitioning
    • SQL Temporary Table
    • SQL Clone Table
    • SQL Rename Table
    • SQL LOCK TABLE
    • SQL Clear Table
    • SQL DESCRIBE TABLE
    • SQL Mapping
    • Cursors in SQL
    • AND in SQL
    • Wildcard in SQL
    • SQL FETCH NEXT
    • SQL Views
    • SQL Delete View
    • Triggers in SQL
    • SQL UPDATE Trigger
    • SQL AFTER UPDATE Trigger
    • SQL Update Statement
    • SQL DROP TRIGGER
    • SQL DROP Table
    • Types of SQL Views
    • SQL Port
    • SQL Clustered Index
    • SQL COMMIT
    • Distinct Keyword in SQL
    • PARTITION BY in SQL
    • SQL Set Operators
    • SQL UNION ALL
    • Metadata in SQL
    • SQL Bulk Insert
    • Array in SQL
    • SQL REGEXP
    • JSON in SQL
    • SQL For loop
    • EXPLAIN in SQL
    • ROLLUP in SQL
    • Escape Character SQL
    • SQL Cluster
    • SQL Backup
    • SQL Pattern Matching
    • SQL Users
    • ISNULL SQL Server
    • SQL pivot
    • SQL Import CSV
    • SQL if then else
    • SQL ignore-case
    • SQL Matches
    • SQL Search String
    • SQL Column Alias
    • SQL extensions
    • SQL Substring Function
    • Charindex SQL
  • Basic
    • What is SQL
    • Careers in SQL
    • Careers in SQL Server
    • IS SQL Microsoft?
    • SQL Management Tools
    • What is SQL Developer
    • Uses of SQL
    • How to Install SQL Server
    • What is SQL Server
    • SQL Quick References
    • SQL Like Wildcard
    • SQL Like with Multiple Values
    • SQL Examples
    • SQL Server Versions
    • SQL DROP DB
    • SQL Case Insensitive
    • SQL Expressions
    • Database in SQL
    • SQL Data Types
    • SQL Keywords
    • Composite Key in SQL
    • SQL WAITFOR
    • SQL Constraints
    • Transactions in SQL
    • First Normal Form
    • SQL Server Data Types
    • SQL Administration
    • SQL Variables
    • SQL Enum
    • SQL GROUP BY WHERE
    • SQL ROW
    • SQL EXECUTE
    • SQL EXCLUDE
    • SQL Performance Tuning
    • SQL UUID
    • Begin SQL
    • SQL Update Join
    • Cheat sheet SQL
  • Operators
    • SQL Operators
    • SQL Arithmetic Operators
    • SQL Logical Operators
    • SQL String Operators
    • Ternary Operator in SQL
  • Commands
    • SQL Commands
    • sqlplus set commands
    • SQL Alter Command
    • SQL Commands Update
    • SQL DML Commands
    • SQL DDL Commands
    • FETCH in SQL
  • Clause
    • SQL Clauses
    • SQL IN Operator
    • SQL SELECT DISTINCT Multiple Columns
    • SQL Null Values
    • SQL LIKE
    • SQL LIKE Query
    • SQL LIKE Operator
    • SQL LIKE Clause
    • SQL NOT Operator
    • SQL Minus
    • SQL WHERE Clause
    • SQL with Clause
    • SQL HAVING Clause
    • SQL HAVING Clause
    • SQL GROUP BY DAY
    • ORDER BY Clause in SQL
    • SQL ORDER BY CASE
    • SQL ORDER BY DESC
    • SQL ORDER BY DATE
    • SQL ORDER BY Alphabetical
    • SQL ORDER BY Ascending
    • SQL Order by Count
    • SQL GROUP BY Month
    • SQL GROUP BY Multiple Columns
    • SQL GROUPING SETS
  • Queries
    • SQL Insert Query
    • SQL SELECT Query
    • SQL SELECT RANDOM
    • SQL Except Select
    • SQL Subquery
    • SQL SELECT DISTINCT
    • SQL WITH AS Statement
  • Keys
    • SQL Keys
    • SQL Foreign Key
    • Primary Key in SQL
    • Foreign Key in SQL
    • Unique Key in SQL
    • SQL UNIQUE Constraint
    • SQL Primary Key
    • Alternate Key in SQL
    • SQL Super Key
  • Functions
    • SQL Date Function
    • SQL Server Functions
    • SQL String Functions
    • SQL Compare String
    • Timestamp to Date in SQL
    • SQL REGEX
    • SQL Window Functions
    • SQL Syntax
    • SQL CONCAT
    • SQL ALTER TABLE
    • SQL MOD()
    • SQL Timestamp
    • SQL Min and Max
    • SQL TO_DATE()
    • SQL DATEADD()
    • SQL DATEDIFF()
    • SQL HOUR()
    • SQLite? functions
    • ANY in SQL
    • LIKE Query in SQL
    • SQL NOT NULL
    • SQL NOT IN
    • SQL MAX()
    • SQL MIN()
    • SQL SUM()
    • SQL COUNT
    • SQL identity
    • SQL DELETE Trigger
    • SQL Declare Variable
    • SQL Text Search
    • SQL COUNT DISTINCT
    • SQL TEXT
    • SQL Limit Order By
    • BETWEEN in SQL
    • LTRIM() in SQL
    • TOP in SQL
    • SQL Select Top
    • Merge SQL
    • SQL TRUNCATE()
    • SQL UNION
    • SQL ALL
    • SQL INTERSECT
    • SQL Alias
    • SQL Server Substring
    • CUBE in SQL
    • SQL RANK()
    • SQL CTE
    • SQL LAG()
    • SQL MID
    • SQL avg()
    • SQL WEEK
    • SQL DELETE
    • SQL DATEPART()
    • SQL DECODE()
    • SQL DENSE_RANK()
    • SQL NTILE()
    • SQL NULLIF()
    • SQL Stuff
    • SQL Ceiling
    • SQL EXISTS
    • SQL LEAD()
    • SQL COALESCE
    • SQL BLOB
    • SQL ROW_NUMBER
    • SQL Server Replace
    • SQL Ranking Function
    • SQL Server Permission
  • T-SQL
    • T-SQL pivot
    • T-SQL Formatter
    • T-SQL TRY CATCH
    • T-SQL CTE
    • T-SQL CASE
    • T-SQL DATEPART
    • T-SQL Date Format
    • T-SQL ROUND
    • T-SQL Loop
    • T-SQL IIF
    • T-SQL Union
    • T-SQL CREATE TABLE
    • T-SQL INSERT
    • T-SQL Stuff
    • T-SQL ISNULL
    • T-SQL ADD Column
    • T-SQL DATEDIFF
  • Joins
    • Join Query in SQL
    • Types of Joins in SQL
    • Types of Joins in SQL Server
    • SQL Inner Join
    • SQL Join Two Tables
    • SQL Delete Join
    • SQL Left Join
    • LEFT OUTER JOIN in SQL
    • SQL Right Join
    • SQL Cross Join
    • SQL Outer Join
    • SQL Full Join
    • SQL Self Join
    • Natural Join SQL
    • SQL Multiple Join
  • SqlAlchemy
    • What is SQLAlchemy
    • SqlAlchemy ORM
    • SQLAlchemy count
    • SQLAlchemy update object
    • SQLAlchemy pip
    • SQLAlchemy Connection
    • SQLAlchemy Metadata
    • SQLAlchemy Raw SQL
    • SQLAlchemy Filter in List
    • SQLAlchemy Alias
    • SQLAlchemy unique
    • SQLAlchemy JSONB
    • SQLAlchemy Async
    • SQLAlchemy Types
    • SQLAlchemy Many to Many
    • SQLAlchemy Example
    • SQLAlchemy Model
    • SQLAlchemy Data Types
    • SQLAlchemy Filter
    • SQLAlchemy SQLite
    • SQLAlchemy DateTime
    • SQLAlchemy create_engine
    • SQLAlchemy Delete
    • SQLAlchemy Migrations
  • NoSQL
    • NoSQL Databases List
    • NoSQL Data Modeling
    • Types of NoSQL Databases
    • NoSQL Injection
    • NoSQL vs SQL Databases
    • NoSQL Use Cases
    • NoSQL Key Value
  • Interview Questions
    • SQL Interview Questions
    • Advance SQL Interview Questions
    • SQL Joins Interview Questions
    • SQL Server Interview Questions
    • SQL Current Month

Related Courses

JDBC Training Course

PHP course

Windows 10 Training

SQL Course Training

PL/SQL Certification Courses

Oracle Certification Courses

Table in SQL

By Priya PedamkarPriya Pedamkar

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. 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:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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 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’;

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,584 ratings)

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 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 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 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 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 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 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 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 the 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 data effectively. 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​ 
Popular Course in this category
SQL Training Program (7 Courses, 8+ Projects)
  7 Online Courses |  8 Hands-on Projects |  73+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

JDBC Training (6 Courses, 7+ Projects)4.9
PHP Training (5 Courses, 3 Project)4.8
Windows 10 Training (4 Courses, 4+ Projects)4.7
PL SQL Training (4 Courses, 2+ Projects)4.7
Oracle Training (14 Courses, 8+ Projects)4.7
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more