What is Data Definition Language (DDL)

The Data Definition Language (DDL) is a part of SQL that defines and manages all the objects in an SQL database. These objects include tables, indices, sequences, views, and schemas, among others. Unlike the Data Manipulation Language (DML), which is concerned with the data within these objects, DDL's focus is on the structure of these objects.

Common DDL Commands: CREATE, ALTER, DROP, and TRUNCATE

There are four main DDL commands that SQL uses to manage its objects:

• CREATE
  This command is used to create a new object within the database. The syntax varies depending on the type of object, but the basic structure is CREATE <object type> <object name> (<parameters>).

• ALTER
  The ALTER command modifies an existing database object. This could involve adding a column to a table, changing the data type of a column, or renaming an object. The syntax is ALTER <object type> <object name> <modification command>.

• DROP
  When you no longer need an object, the DROP command allows you to permanently remove it from the database. The syntax is DROP <object type> <object name>.

• TRUNCATE
  This command is used to delete all the data from a table, but it leaves the table structure for future use. The syntax is TRUNCATE TABLE <table name>.

Examples of DDL

Let's walk through some examples of these commands:

/* Creating a new table */
CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DOB DATE
);

/* Altering the table to add a new column */
ALTER TABLE Employees ADD COLUMN Salary FLOAT;

/* Truncating the table to remove all data */
TRUNCATE TABLE Employees;

/* Dropping the table from the database */
DROP TABLE Employees;

What is Data Manipulation Language (DML)

The Data Manipulation Language (DML) is a subset of SQL that is used for inserting, selecting, updating, and deleting data in a database. DML allows users to work with the data that is stored inside database objects such as tables. As opposed to DDL which affects the structure of database objects, DML primarily impacts the data contained within these objects.

Common DML Commands: SELECT, INSERT, UPDATE, and DELETE

The main DML commands that you'll use in SQL are as follows:

• SELECT
  This command is used to query and retrieve data from one or more tables in your database. The syntax generally is SELECT <columns> FROM <table name> WHERE <condition>.

• INSERT
  The INSERT command allows you to insert data into a table. The syntax is INSERT INTO <table name> (<columns>) VALUES (<values>).

• UPDATE
  This command lets you update existing data within a table. The syntax is UPDATE <table name> SET <column = value> WHERE <condition>.

• DELETE
  With the DELETE command, you can remove existing records in a table. The syntax is DELETE FROM <table name> WHERE <condition>.

Examples of DML

I'll show you these commands with examples:

/* Inserting data into a table */
INSERT INTO Employees (ID, FirstName, LastName, DOB, Salary)
VALUES (1, 'John', 'Doe', '1980-07-15', 50000);

/* Selecting data from a table */
SELECT * FROM Employees;

/* Updating data in a table */
UPDATE Employees
SET Salary = 55000
WHERE ID = 1;

/* Deleting data from a table */
DELETE FROM Employees
WHERE ID = 1;

What is Data Control Language (DCL)

Data Control Language (DCL) is a subset of SQL commands that is used to control the permissions and access to the database objects. DCL commands are essential for maintaining security and managing access to the data stored within a database. They allow the database administrator to control who can access the data, and what they can do with it.

Common DCL Commands: GRANT and REVOKE

The main DCL commands are as follows:

• GRANT
  This command is used to provide privileges to database users. Privileges can include the ability to SELECT, INSERT, UPDATE, DELETE, and other actions on a database object. The syntax is GRANT <privileges> ON <object> TO <user>.

• REVOKE
  The REVOKE command is used to remove privileges granted to a database user. It is essentially the opposite of the GRANT command. The syntax is REVOKE <privileges> ON <object> FROM <user>.

Examples of DCL

Let's walk through some examples of these commands:

/* Granting SELECT and INSERT privileges to a user */
GRANT SELECT, INSERT ON Employees TO user1;

/* Revoking INSERT privilege from a user */
REVOKE INSERT ON Employees FROM user1;