Categories of SQL command to know for Data Analysis

Table of Contents

Data Definition Language (DDL)

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 Employee (Employee Id INTEGER PRIMARY KEY, First name CHAR (50) NULL, Last name CHAR (75) NOT NULL);

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 Employee ADD PRIMARY KEY (employee_pk);

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

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

DQL Command

Data Query Language
DQL is used to fetch the data from the database.

It uses only one command:

SELECT
a. SELECT: This is the same as the projection operation of relational algebra. It is used to select the attribute based on the condition described by WHERE clause.

Syntax:

SELECT expressions
FROM TABLES
WHERE conditions;

DML COMMAND

The database can be changed by using DML commands. It is in charge of making any kind of database modifications.
DML commands cannot permanently save all database changes since they are not auto-committed. They are rollbackable.

The four primary DML commands in SQL are as follows:

SELECT Command
INSERT Command
UPDATE Command
DELETE Command

SELECT DML Command

In Structured Query Language, the most significant data manipulation command is SELECT. The records from the chosen table are shown by the SELECT command. Additionally, by utilising the WHERE clause, it displays a specific record from a certain column.

Examples of SELECT Command
Example 1: This example shows all the values of every column from the table.

SELECT * FROM Student;
This SQL statement displays the following values of the student table:

Example 2: The values of every column in the table are displayed in this example.

SELECT Emp_Id, Emp_Salary FROM Employee;

INSERT DML Command

Another crucial data manipulation operation in SQL is Put, which enables users to insert data into database tables.

Example 1: This illustration demonstrates how to add a record to a database table.

INSERT INTO Student (Stu_id, Stu_Name, Stu_Marks, Stu_Age) VALUES (106, mahesh, 89, 19);