SQL Table

Topic:
 
1  SQL - Create Table
2  SQL - Show Tables
3  SQL - Rename Table
4  SQL - Truncate Table
5  SQL - Clone Tables
6  SQL - Temporary Tables
7  SQL - Alter Tables
8  SQL - Drop Table
9  SQL - Delete Table
10 SQL - Constraints

The SQL CREATE TABLE Statement

Syntax

Following is the basic syntax of a SQL CREATE TABLE statement

The CREATE TABLE statement is used to define and create a new table within a database. It specifies the table name, along with the column names, data types, and optional constraints for each column.

Here’s the syntax:

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
    columnN datatype constraints
);

• table_name: The name of the table you want to create.
• column1, column2, ..., columnN: The names of the columns within the table.
• datatype: The data type that each column will hold (e.g., INT, VARCHAR, DATE, etc.).
• constraints: Optional constraints that can be applied to columns, such as PRIMARY KEY, NOT NULL, UNIQUE, etc.

Here’s an example using a hypothetical “Products” table:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL,
    Category VARCHAR(50),
    Price DECIMAL(10, 2),
    InStock BOOLEAN
);

In this example:

• ProductID is an integer column that serves as the primary key for the table.
• ProductName is a non-null string column that will store the product names.
• Category is a string column that will store the category of each product.
• Price is a decimal column with a precision of 10 and a scale of 2, used to store product prices.
• InStock is a boolean column that stores whether the product is currently in stock.

This SQL statement creates a table named “Products” with the specified columns and data types. It’s important to customize the column names, data types, and constraints based on the requirements of your specific database schema.

After creating a table, you can use the DESC (or DESCRIBE) command in SQL to display the structure or description of the table. This command provides information about the columns, their data types, and any constraints applied to them.

Here’s the syntax to use the DESC command:

DESC table_name;

Or you can also use:

DESCRIBE table_name;

For example, if you have created a table named “Products” as shown in the previous example, you can use the DESCcommand to see its structure:

DESC Products;

The output will provide information about the columns, their data types, and any constraints:

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| ProductID  | int(11)      | NO   | PRI | NULL    | auto_increment|
| ProductName| varchar(100) | NO   |     | NULL    |                |
| Category   | varchar(50)  | YES  |     | NULL    |                |
| Price      | decimal(10,2)| YES  |     | NULL    |                |
| InStock    | tinyint(1)   | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

This output shows the column names, data types, nullability, keys, default values, and any additional information about each column in the “Products” table. This information can help you verify that the table has been created according to your design.

SQL CREATE TABLE IF NOT EXISTS

The CREATE TABLE IF NOT EXISTS statement is used to create a table in a database only if it doesn't already exist. This is useful to prevent errors that could occur if you attempt to create a table that's already present in the database.

Here’s the syntax of the CREATE TABLE IF NOT EXISTS statement:

CREATE TABLE IF NOT EXISTS table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
    columnN datatype constraints
);

You can use this statement in the same way you would use a regular CREATE TABLE statement. If the table specified by table_name doesn't exist, it will be created with the provided structure. If the table already exists, no action will be taken, and no error will be thrown.

Here’s an example:

CREATE TABLE IF NOT EXISTS Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100),
    DateOfBirth DATE
);

In this example, if the “Customers” table doesn’t exist, it will be created with the specified columns and constraints. If the table already exists, this statement will have no effect.

SQL — Show Tables (Listing Tables)

To list the tables in a database, you can use the appropriate command for the specific database management system you’re using. Here are some common database systems and their respective commands to show or list tables:

MySQL: Use the SHOW TABLES command to list all the tables in the current database.

SHOW TABLES;

PostgreSQL: Use the \dt command in the PostgreSQL command-line interface to list all tables.

\dt

SQL Server: Use the SELECT statement with the sys.tables system catalog view to list tables in SQL Server.

SELECT name FROM sys.tables;

Oracle: You can use the USER_TABLES view to list tables in Oracle for the current user.

SELECT table_name FROM user_tables;

SQLite: In SQLite, you can query the sqlite_master table to list tables.

SELECT name FROM sqlite_master WHERE type='table';

SQL RENAME TABLE Statement

The SQL RENAME TABLE statement is used to change the name of an existing table in a database. The exact syntax can vary slightly depending on the database management system you are using. Here's a general representation of the syntax:

RENAME TABLE old_table_name TO new_table_name;

Replace old_table_name with the current name of the table you want to rename, and new_table_name with the new name you want to assign to the table.

SQL ALTER TABLE Statement

he ALTER TABLE statement can be used to change or modify the structure of an existing table i.e. using this statement you can add/delete columns, create/destroy indexes, change the datatypes of the existing columns, rename the columns and, we can even rename the table.

ALTER TABLE table_name RENAME [TO|AS] new_table_name

SQL TRUNCATE TABLE Statement

The SQL TRUNCATE TABLE statement is used to quickly and efficiently remove all rows from a table, effectively resetting the table's data content without removing its structure. It is often faster than using the DELETE statement to remove all rows, especially for large tables, because it doesn't log individual row deletions. However, it's important to note that TRUNCATE TABLE cannot be rolled back, and it typically requires fewer system resources than DELETE.

Here’s the basic syntax of the TRUNCATE TABLE statement:

TRUNCATE TABLE table_name;

Replace table_name with the name of the table you want to truncate.

Example usage:

TRUNCATE TABLE Employees;

In this example, the Employees table will be emptied, removing all rows while keeping the table structure intact. Be cautious when using TRUNCATE TABLE as it's a powerful command that can't be undone. Always make sure you have a backup of your data before performing such operations.

SQL — Clone Tables

MySQL, you can clone a table by using the CREATE TABLE ... SELECT statement. This statement creates a new table with the same structure as an existing table and copies the data from the existing table into the new one. Here's a simple example:

Syntax

Following is the basic syntax to perform simple cloning in MySQL−

CREATE TABLE new_table SELECT * FROM original_table;

Assuming you have an existing table named “SourceTable” and you want to create a new table called “CloneTable” that has the same structure and data:

CREATE TABLE CloneTable AS
SELECT * FROM SourceTable;

In this example, the CloneTable will be created as a clone of SourceTable with the same columns, data types, and data. Keep in mind that this approach copies all the data, but it does not copy indexes, constraints, or triggers. You might need to recreate those elements on the cloned table if necessary.

Temporary Tables in MySQL

temporary tables are tables that exist only for the duration of a session. They are useful for storing intermediate results or temporary data within a specific session and are automatically dropped when the session ends. Temporary tables can be very handy for complex queries or procedures that require intermediate storage.

To create a temporary table in MySQL, you can use the CREATE TEMPORARY TABLE statement. Here's how you do it:

CREATE TEMPORARY TABLE temp_table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
    columnN datatype constraints
);

You can use the same syntax as creating a regular table, but with the TEMPORARY keyword before the TABLE keyword. Here's an example:

CREATE TEMPORARY TABLE temp_orders (
    OrderID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Quantity INT
);

In this example, the temp_orders table is created as a temporary table that exists only for the current session. When the session ends (e.g., when you log out or close the connection), the temporary table will be automatically dropped.

SQL — ALTER TABLE Statement

The SQL ALTER TABLE command is a part of Data Definition Language (DDL) and modifies the structure of a table. The ALTER TABLE command can add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself.

Syntax

Following is the basic syntax of an ALTER TABLE command −

ALTER TABLE table_name [alter_option ...];

Where, the alter_option depends on the type of operation to be performed on a table. This article will discuss such important operations one by one.

ALTER TABLE — ADD Column

If you need to add a new column to a table, you should use the ADD COLUMN option along with ALTER TABLE statement as shown below −

ALTER TABLE table_name ADD column_name datatype;

Example — Following is the example to ADD a New Column to an existing table −

ALTER TABLE CUSTOMERS ADD SEX char(1);

ALTER TABLE — DROP COLUMN

If you need to drop an existing column from a table, you should use the DROP COLUMN option along with ALTER TABLE statement as shown below.

ALTER TABLE table_name DROP COLUMN column_name;

Example — Following is the example to DROP sex column from the existing table.

ALTER TABLE CUSTOMERS DROP COLUMN SEX;

SQL — DROP Table

The SQL DROP TABLE statement is used to permanently remove an existing table from a database. This action deletes both the table structure and all the data contained within it. It's an irreversible operation, so make sure to use it with caution and back up your data before executing it.

Here’s the basic syntax of the DROP TABLE statement:

DROP TABLE table_name;

Replace table_name with the name of the table you want to drop.

For example, if you have a table named “Customers” and you want to delete it:

DROP TABLE Customers;

SQL — Delete Table

In SQL, there’s a distinction between deleting the data within a table (using the DELETE statement) and deleting the entire table structure (using the DROP TABLE statement). Here's a brief explanation of each:

Syntax

Following is the basic syntax for using the DELETE command in SQL −

DELETE FROM table_name;

Deleting specific rows based on single condition

We can use the SQL DELETE statement to delete specific rows from a table based on a single condition using the WHERE clause.

Syntax

Following is the syntax for deleting specific rows based on single condition

DELETE FROM table_name
WHERE condition;

SQL — Constraints

Constraints in SQL are rules that define how the data within a table should behave. They ensure data integrity, maintain consistency, and prevent incorrect or undesirable data from being inserted, updated, or deleted. SQL supports various types of constraints that you can apply to columns in a table. Here are some common types of constraints:

Primary Key Constraint: The primary key constraint enforces the uniqueness and non-null values of a column or a set of columns. It uniquely identifies each row in the table.

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

Foreign Key Constraint: A foreign key constraint establishes a relationship between two tables. It ensures that values in one table’s column match values in another table’s primary key.

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Unique Constraint: The unique constraint ensures that values in a column or a set of columns are unique across the table. Unlike the primary key, unique constraints can allow null values.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

Check Constraint: A check constraint specifies a condition that must be true for each row in a table.

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2),
    Quantity INT,
    CHECK (Price > 0 AND Quantity >= 0)
);

Not Null Constraint: The not null constraint ensures that a column does not contain null values.

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100) NOT NULL
);