Make sure to join the Telegram Grop to recieve all Topic Notes as well as Regular Jobs & Internship Updates.

3) SQL Table

In SQL, a table is a structured collection of data organized into rows and columns. Let’s break it down:Rows: Each row represents a specific object (e.g., a book, a customer, an order). Columns: Columns store specific types of data related to those objects (e.g., author, price, quantity).


Learn SQL Tables with Codepect

At Codepect, we love to share knowledge and help aspiring developers become experts. In this section, we will illustrate how SQL Tables work with a real-life example. We will create a table, insert data, and then perform SQL queries to retrieve the relevant data.

3.1 SQL Table Definition

table in a database is a structured collection of data, organized into rows and columns. Here’s an alternative explanation:

  • Table Structure:

    • A table consists of rows (also known as records or tuples) and columns (also called fields).
    • Each row represents a specific entity (e.g., an employee, a product, or an order).
    • Columns store specific types of data related to those entities (e.g., name, address, salary).
  • Example: Employee Table:

    • Consider an “Employee” table:
Ankit      Lucknow       15000
Raman      Allahabad     18000
Mike       New York      20000

    • In this example:
      • “Employee” is the table name.
      • “EMP_NAME,” “ADDRESS,” and “SALARY” are column names.
      • Each row contains data for an individual employee (e.g., “Ankit,” “Lucknow,” and 15000).

Tables serve as a fundamental building block for organizing and managing data within a relational database system.

SQL Table Variable

In SQL Server, a table variable is a special type of local variable used to store data temporarily, similar to a temporary table. Here are some key points about table variables:

  1. Declaration Syntax:

    • To declare a table variable, use the following syntax:
					DECLARE @MyTableVariable TABLE (
    Column1 DATATYPE,
    Column2 DATATYPE,
    -- Add more columns as needed


Replace Column1Column2, etc., with actual column names and data types.

    1. Usage:

      • You can insert data into a table variable using INSERT INTO.
      • Update and delete rows within the table variable as needed.
    2. Storage Location:

      • Contrary to common misconception, table variables are stored in the tempdb database, not in memory.
      • Their lifecycle starts at declaration and ends at the batch’s completion.

3.2 SQL Create Table

  1. CREATE TABLE statement allows you to create a new table within a database. Here’s the basic syntax:

					CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    -- Add more columns as needed

  • table_name: Specify the name of the new table.
  • column1column2, etc.: Define the column names.
  • datatype: Specify the data type for each column (e.g., varchar, integer, date).

For example, let’s create a table called “Persons” with columns for PersonID, LastName, FirstName, Address, and City:

					CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)


The PersonID column holds integers, while the other columns store character data (with a maximum length of 255 characters). Once created, you can populate this table using the INSERT INTO statement.

3.3 SQL Drop Table

The SQL DROP TABLE statement allows you to remove an entire table from a database, including all its data and structure. Once a table is dropped, all information stored within it is permanently deleted, and the table cannot be recovered.

Be cautious when using this command, as it results in the loss of complete data stored in the table. To drop a table, use the following syntax:

Replace table_name with the actual name of the table you want to delete. If you want to prevent errors when dropping a table that doesn’t exist, you can use the DROP TABLE IF EXISTS

					DROP TABLE table_name;


3.4 SQL Delete Table

  1. In SQL, the DELETE TABLE statement allows you to remove an entire table from a database, including all its data and structure. Here’s how it works:


      • The DROP TABLE statement deletes an existing table.
      • Syntax: DROP TABLE table_name;
      • Be cautious when using this command, as it permanently removes the table and all its data.

      • The TRUNCATE TABLE statement deletes data inside a table but keeps the table structure intact.
      • Syntax: TRUNCATE TABLE table_name;
    2. Example:

      • To drop the existing table “Shippers,” use
					DROP TABLE Shippers;


To delete data inside a table (without dropping the table itself), use:

					TRUNCATE TABLE table_name;


3.5 SQL Rename Table

  1. In SQL, you can use the RENAME TABLE statement to change the name of an existing table. This operation updates the metadata without modifying the table’s structure or data

  1. Here’s how you can do it:

    1. MySQL Syntax:

      • To rename a table in MySQL, use the following syntax:
					RENAME TABLE old_table_name TO new_table_name;


Replace old_table_name with the current table name and new_table_name with the desired new name.

  1. SQL Server Syntax:

    • In SQL Server, you can use the sp_rename stored procedure:
					EXEC sp_rename 'old_table_name', 'new_table_name';


The procedure returns either 0 or a non-zero value as a result.

Remember to choose the appropriate syntax based on your database system! 

3.6 SQL Truncate Table

 In SQL, the TRUNCATE TABLE statement efficiently removes all rows from a table, effectively resetting it to an empty state. Unlike the DELETE statement, which removes rows one by one and generates transaction logs, TRUNCATE TABLE deallocates data pages without logging individual row deletions

					TRUNCATE TABLE table_name;


Replace table_name with the actual name of the table you want to truncate. Keep in mind that TRUNCATE TABLE:

  • Is faster than DELETE for large tables.
  • Doesn’t fire delete triggers associated with the table.
  • Resets auto-increment columns (or identity, sequence, etc.) to their starting values.

3.7 SQL Copy Table

To create a copy of an existing table in SQL, you have a few options. Let’s explore them:

  1. Cloning with Structure and Data:

    • If you want an exact copy of the original table, including both its structure (column names, data types) and data, follow these steps:
					CREATE TABLE new_table LIKE original_table;
INSERT INTO new_table SELECT * FROM original_table;

    • Replace new_table with the desired name for the new table and original_table with the existing table you want to clone.
  1. Simple Copy (Structure and Data):

    • If you only need a copy of the structure and data (without considering column attributes and indexes), use a one-liner:
					CREATE TABLE new_table AS SELECT * FROM original_table;

  • This creates a new table named new_table with the same columns and data as original_table.

3.8 SQL Temp Table

    •  A temporary table in SQL exists only temporarily within your database. It’s useful for storing data during a session but isn’t needed permanently. These tables are handy for holding intermediate result sets from complex queries or calculations. They’re created for short-term use, typically within the duration of a database session or transaction.
    • If you need to work with data repeatedly within a session, temporary tables provide a convenient workspace! 

To create a temporary table in SQL, you have a few options depending on your database system. Here are some common ways:


id INT,
name VARCHAR(50),
age INT


SELECT id, name, age INTO #temp_table FROM original_table;

Using INTO within a SELECT Query

USE your_database;
SELECT name, age INTO #MaleStudents FROM student WHERE gender = ‘Male’;

3.9 SQL Alter Table

    • The ALTER TABLE statement in SQL allows you to modify an existing table. Here are some common operations you can perform using ALTER TABLE:

To add a new column to an existing table

					ALTER TABLE table_name ADD column_name datatype;


To delete a column from a table (not supported in all databases):

					ALTER TABLE table_name DROP COLUMN column_name;


To rename a column

					ALTER TABLE table_name RENAME COLUMN old_name TO new_name;


To modify the data type of a column

					ALTER TABLE table_name ALTER COLUMN column_name datatype;

					SELECT *  FROM Your_Time
WHERE focus <> 0;


A Leading Coding Community For Undergrads !