Data Definition Language (DDL)
DDL changes the format of the table, such as creating a table, deleting a table, and altering a table.
All the command of DDL is auto-committed which means it permanently save all the changes in the database. Below are the DDL commands and their syntax.
CREATE TABLE Creates a new table in the database. It allows you to specify the name of the table and the name of each column in the table.
CREATE TABLE table_name (column_1 datatype, column_2 datatype, column_3 datatype);
ALTER TABLE Let’s you alter the table as per your requirement.
For example, you can add, remove and rename the columns as shown below.
-- Add a column ALTER TABLE table_name ADD column_name datatype;
-- Remove a column ALTER TABLE table_name DROP COLUMN column_name;
-- Rename a column ALTER TABLE table_name CHANGE COLUMN old_name new_name;
ALTER lets you change the table name itself with the below syntax.
ALTER TABLE old_table_name RENAME TO new_table_name;
It is used to delete both the structure and record stored in the table.
DROP TABLE table_name;
It is used to delete all the rows from the table and free the space containing the table.
TRUNCATE TABLE table_name;
Data Manipulation Language
DML commands are used to Customize the database, and it is responsible for all forms of changes in the database.
The command of DML is not auto-committed, which means it can’t permanently save all the changes in the database. They can be rollback. Below are the DML commands and their syntax.
INSERT Statements are used to add a new row to a table.
INSERT INTO table_name (column_1, column_2, column_3) VALUES (value_1, value_2, value_3);
UPDATE statements allow you to edit rows in a table.
UPDATE table_name SET column_name = value WHERE condition;
DELETE statements are used to remove rows from a table.
DELETE FROM table_name WHERE some_column = some_value;
Data Control Language
DCL commands grants and takes back authority from any database user. It works as shown below.
GRANT command is for giving users access to a database.
GRANT SELECT, UPDATE ON My_TABLE TO FIRST_USER, SECOND_USER;
It is used to take back permissions from the user.
REVOKE SELECT, UPDATE ON My_TABLE TO FIRST_USER, SECOND_USER;
Transaction Control Language
Transaction Control Language — as its name suggests — is used to control the actions done by other non-auto-committed commands such as
As I mentioned, DML commands are not auto-committed so TCL commands can be used with the DML. Below are the commonly used TCL statements.
This is used for saving every transaction to the database.
DELETE FROM VENDOR WHERE AGE = 29; COMMIT;
This command aims to undo the transactions that are not saved to the database.
DELETE FROM VENDOR WHERE AGE = 29; ROLLBACK;
This is used for returning a transaction to a specific point without affecting the whole transaction.
DQL is used to fetch the data from the database. SELECT is the only and essential command widely and commonly used by all data analysts and scientists.
The prime purpose of this statement is to get data from a database. Every query will begin with SELECT, followed by the names of columns you want to get from the table.
A particular column from the table can be selected with,
SELECT column_name FROM table_name;
And all the columns can be determined by using * as shown below,
SELECT * FROM table_name;
This article has taught you about the various SQL commands and simple SQL queries with examples. Some of the main concepts learned in this article are as follows:
Data Definition Language(DDL) helps you define the database structure or schema.
Data Manipulation Language (DML) allows you to modify the database instance by inserting, changing, and deleting its data.
DCL (Data Control Language) includes commands like GRANT and REVOKE, which help give “rights & permissions.”
Transaction control language or TCL commands deal with the transaction within the database.
Data Query Language (DQL) is used to fetch the data from the database.
The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.