Categories of SQL command to know for Data Analysis

what are the DDL commands?

DDL modifies the structure of the table by adding, removing, or changing tables, among other things.
All DDL commands are automatically committed, which permanently saves all database modifications.
The following commands are included in DDL:

Create

This command builds a new table and has a predefined syntax. The CREATE statement syntax is:

CREATE TABLE [table name] ([column definitions]) [table parameters];

For example:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);

CREATE TABLE employees:

This part of the statement indicates that you are creating a new table named “employees.”

(id INT PRIMARY KEY,):

This line defines the first column of the table, named “id.”
INT specifies that it is an integer data type, meaning it will store whole numbers.
PRIMARY KEY indicates that the “id” column will be the primary key for the table. A primary key is a unique identifier for each record in the table.

(name VARCHAR(100),):

This line defines the second column of the table, named “name.”
VARCHAR(100) specifies that it is a variable character field, capable of storing up to 100 characters. This column is typically used for storing names or textual information.

(salary DECIMAL(10, 2)):

This line defines the third column of the table, named “salary.”
DECIMAL(10, 2) specifies a decimal data type with a total of 10 digits, where 2 digits are reserved for the decimal part. This is commonly used for storing monetary values.

In summary, the SQL statement is creating a table named “employees” with three columns: “id” for unique identification, “name” for storing names, and “salary” for storing decimal values such as salaries. The “id” column is designated as the primary key, ensuring each record in the table has a unique identifier.
 

Alter

An alter command modifies an existing database table. This command can add up additional column, drop existing columns and even change the data type of columns involved in a database table.

An alter command syntax is:

ALTER object type object name parameters;

For example:

ALTER TABLE employees
ADD department VARCHAR(50);

In this example, we added a unique primary key to the table to add a constraint and enforce a unique value. The constraint “employee_pk” is a primary key and is on the Employee table.

Drop

A drop command is used to delete objects such as a table, index or view. A DROP statement cannot be rolled back, so once an object is destroyed, there’s no way to recover it.

Drop statement syntax is:

DROP object type object name;

For example:

DROP TABLE employees;

In this example, we’re deleting the Employee table.

Truncate

Similar to DROP, the TRUNCATE statement is used to quickly remove all records from a table. However, unlike DROP that completely destroys a table, TRUNCATE preserves its full structure to be reused later.Truncate statement syntax is:

TRUNCATE TABLE table_name;

For example:

TRUNCATE TABLE employees;

COMMENT:

This statement allows you to add comments to a table or column, which can be useful for documentation purposes.

COMMENT ON COLUMN employees.salary IS 'The salary of the employee';