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).
Discover
Learn SQL Tables with Codepect
- Create a table to store data
- Insert data into the table
- Perform SQL queries to retrieve data
3.1 SQL Table Definition
A 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:
EMP_NAME ADDRESS SALARY
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).
- In this example:
Tables serve as a fundamental building block for organizing and managing data within a relational database system.
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:
Declaration Syntax:
- To declare a table variable, use the following syntax:
DECLARE @MyTableVariable TABLE (
Column1 DATATYPE,
Column2 DATATYPE,
-- Add more columns as needed
);
Replace Column1, Column2, etc., with actual column names and data types.
Usage:
- You can insert data into a table variable using
INSERT INTO. - Update and delete rows within the table variable as needed.
- You can insert data into a table variable using
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
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.column1,column2, 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
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:
DROP TABLE:
- The
DROP TABLEstatement 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:
- The
TRUNCATE TABLEstatement deletes data inside a table but keeps the table structure intact. - Syntax:
TRUNCATE TABLE table_name;
- The
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
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
Here’s how you can do it:
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.
SQL Server Syntax:
- In SQL Server, you can use the
sp_renamestored procedure:
- In SQL Server, you can use the
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:
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:
- 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_tablewith the desired name for the new table andoriginal_tablewith the existing table you want to clone.
- Replace
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_tablewith the same columns and data asoriginal_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:
CREATE TEMPORARY TABLE temp_table (
id INT,
name VARCHAR(50),
age INT
);
SELECT id, name, age INTO #temp_table FROM original_table;
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;
