SQL Commands - DDL, DML, DCL, TCL, DQL

SQL commands gives you an instructions to perform various operations on the data which is present in the database in the form of table.

These operation can be below

The most important operation of SQL is to run queries against a database.
Retrieve the records from the database
Insert records in the database
Update the record in the database
Delete the record from the database
Creation of new databases
Creation of tables, stored procedures and views in a database
Set permissions on tables, procedures, and views

SQL commands are majorly divided into 5 types such as DDL, DML, DCL, TCL, and DQL.

1. Data Definition Language (DDL)

DDL commands changes the structure of the table or a view like creating a table, deleting a table, altering a table etc.

All the command of DDL are auto-committed that means it permanently save all the changes in the database.

Here are some commands that come under DDL:

CREATE TABLE : Creates a new table in the database
ALTER TABLE : Adds, deletes, or modifies columns in a table
DROP TABLE : Deletes an existing table in the database
TRUNCATE TABLE : Deletes the data inside a table, but not the table itself
RENAME TABLE : To set a new name for any existing table

2. Data Manipulation Language (DML)

DML commands are used to manipulate the database.

The command of DML is not auto-committed that means it can't permanently save all the changes in the database when you try to do.

You have to explicitly commit the changes in the database, So that it can saved permanently. The changes can be rollback later, before session expires.

Here are some commands that come under DML:

INSERT INTO : Inserts new rows in a table
UPDATE : Updates existing rows in a table
DELETE : Deletes rows from a table
MERGE : To make changes in one table based on values matched from another table

3. Data Control Language

DCL commands are used to grant the access and take back the access from any database for partiaculr user.

Here are some commands that come under DCL:

GRANT : Grant the certain types of access to the user on database or table or views
REVOKE : Revoke the access from the user

4. Transaction Control Language

TCL commands can only use with DML commands like INSERT, DELETE and UPDATE.

Here are some commands that come under TCL:

COMMIT : is used to save all the transactions to the database
ROLLBACK : is used to undo transactions that have not already been saved to the database
SAVEPOINT : to roll the transaction back to a certain point without rolling back the entire transaction.

The Commit operations are not automatically done after any TCL command execution. Hence You have to explicitly commit the changes in the database, So that it can saved permanently.

5. Data Query Language

DQL command is used to fetch the data from the database. It uses only one command: SELECT

SELECT : is used to fetch the data from a database

SQL Keywords

There are many SQL keywords, all are list below in certain category.

I. Basic SQL Keywords

SELECT : is used to fetch the data from a database
FROM : is used to specifies which table to select or delete data. There will be always table name after a FROM keyword.
SELECT DISTINCT : is used to selects only distinct (different) values
AS : Renames a column or table with an alias
GROUP BY : Groups the result set (used with aggregate functions: COUNT, MAX, MIN, SUM, AVG)
HAVING : Used instead of WHERE with aggregate functions
UNION : Combines the result set of two or more SELECT statements (only distinct values)
UNION ALL : Combines the result set of two or more SELECT statements (allows duplicate
ORDER BY : Sorts the result set in ascending or descending order
ASC : Sorts the result set in ascending order
DESC : Sorts the result set in descending order
ALL : Returns true if all of the subquery values meet the condition
ANY : Returns true if any of the subquery values meet the condition
EXISTS : Tests for the existence of any record in a subquery
TOP : Specifies the number of records to return in the result set
LIMIT : Specifies the number of records to return in the result set
NOT NULL : A constraint that enforces a column to not accept NULL values
ROWNUM : Specifies the number of records to return in the result set
UNIQUE : A constraint that ensures that all values in a column are unique
CASE : Creates different outputs based on conditions

II. Database Keywords

CREATE DATABASE is used to create a new database
DROP DATABASE is used to delete an existing database
BACKUP DATABASE is used to create a back up of an existing database

III. Table Keywords

CREATE TABLE : Creates a new table in the database
INSERT INTO : Inserts new rows in a table
VALUES : Specifies the values of an INSERT INTO statement
INSERT INTO SELECT : Copies data from one table into another table
DROP TABLE : Deletes an existing table in the database
ALTER TABLE : Adds, deletes, or modifies columns in a table
ALTER COLUMN : Changes the data type of a column in a table
ADD : Adds a column in an existing tables
ADD CONSTRAINT : Adds a constraint after a table is already created
ALTER : Adds, deletes, or modifies columns in a table, or changes the data type of a column in a table
CHECK : A constraint that limits the value that can be placed in a column
COLUMN : Changes the data type of a column or deletes a column in a table
CONSTRAINT : Adds or deletes a constraint
DEFAULT : A constraint that provides a default value for a column
DELETE : Deletes rows from a table
DROP COLUMN : Deletes a column in a table
DROP CONSTRAINT : Deletes a UNIQUE, PRIMARY KEY, FOREIGN KEY, or CHECK constraint
DROP DEFAULT : Deletes a DEFAULT constraint
PRIMARY KEY : A constraint that uniquely identifies each record in a database table
FOREIGN KEY : A constraint that is a key used to link two tables together
UPDATE : Updates existing rows in a table
SELECT INTO : Copies data from one table into a new table
SET : Specifies which columns and values that should be updated in a table
TRUNCATE TABLE : Deletes the data inside a table, but not the table itself

IV. Joins Keywords

INNER JOIN is used to returns rows that have matching values in both tables
LEFT OUTER JOIN is used to returns all rows from the left table, and the matching rows from the right table
RIGHT OUTER JOIN is used to returns all rows from the right table, and the matching rows from the left table
FULL OUTER JOIN is used to returns all rows when there is a match in either left table or right table

V. Index, Views and Procedure Keywords

CREATE INDEX : Creates an index on a table (allows duplicate values)
CREATE UNIQUE INDEX : Creates a unique index on a table (no duplicate values)
DROP INDEX : Deletes an index in a table
CREATE VIEW : Creates a view based on the result set of a SELECT statement
CREATE OR REPLACE VIEW : Updates a view
DROP VIEW : Deletes a view
CREATE PROCEDURE : Creates a stored procedure
EXEC : Executes a stored procedure

VI. WHERE Clause Keywords

WHERE : Filters a result set to include only records that fulfill a specified condition
BETWEEN : Selects values within a given range
IN : Allows you to specify multiple values in a WHERE clause
LIKE : Searches for a specified pattern in a column
NOT : Only includes rows where a condition is not true
IS NULL : Tests for empty values
IS NOT NULL : Tests for non-empty values
OR : Includes rows where either condition is true
AND : Only includes rows where both conditions is true

SQL Commands - DDL, DML, DCL, TCL, DQL | SQL Keywords