Introduction to SQL commands
SQL commands categories into five types which are: DDL, DML, DCL, TCL, and DQL. All those types play an important role, and are important to understand.
All RDBSM databases use those five types of SQL code. Some provide more or less functionalities and do things differently, but for the most part, all do the core use cases.
Here’s a summary illustration of the five SQL command types:
Data Definition Language – DDL
DDL commands are used to manipulate database objects. We create, modify (alter) and delete DB objects like Tables, Views, Materialized Views, and fFunctions using the Data Manipulation Language
The commands used in DDL are:
- Create
- Alter
- Drop
- Truncate
Create
The create SQL clause creates objects in the database, like creating a table or a View.
example:
CREATE TABLE table_name(
column1 DATATYPE1
column2 DATATYPE2 CONSTRAINTS
.....
);Alter
Alter is used to modify existing objects inside the database, like modifying a tables’s column name
example:
DROP TABLE table_name;Drop
The drop command deletes objects inside the database, for example deleting a table
example:
ALTER TABLE table_name RENAME TO new_table_name; Truncate
Truncate is used to remove all the data in a table at once
example:
TRUNCATE TABLE table_name;Data Manipulation Language – DML
The usage of Data Manipulation Language is to load, manipulate and remove data from the database.
Most common DML clauses are:
- Insert
- Update
- Delete
Insert
the Insert command is use to load data (records) into an existing table in our database
example
INSERT INTO table_name(column1, column2, column3)
VALUES('value1','value2','value3');Update
the update clause is required for updating one or multiple records (values of a specific row or rows)
example:
UPDATE table_name
SET column_name1 = 'value1'
WHERE column_name2 = 'value2';Delete
As the name applies, the delete command is used to delete data from the table (delete one or multiple records in a specific table inside the database).
example:
DELETE FROM table_name
WHERE column_name = 'value1';Data Query Language – DQL
Data query language is used for retrieving or viewing data from one or many tables. All that can be achieved using one SQL clause which is select.
Select
The select command is the most used command in SQL since it allow us to use the stored data. If you are a Data/Business Analyst, DQL is the most likely SQL type you will need for your work.
Some people argue that DQL should be a part of DML since the select command is used to retrieve data, hence it falls under the category of data manipulation.
Example:
SELECT column_name
FROM table_name
WHERE join/filter;Data Control Language – DCL
Data control language specific usage is to deal with users permissions and rights on the database as well as few other DB system related controls.
The SQL commands for DCL are:
- Grant
- Revoke
GRANT INSERT, UPDATE, DELETE
ON table_name
TO user_name;Transaction Control Language – TCL
As the name applies, Transactions Control language deal with the transactions within the database.
Some of the TCL commandas are:
- COMMIT
- ROLLBACK
- SAVEPOINT
- SET TRANSACTION
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'name1';
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'name2';
-- oops ... forget that and use name3's account
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'name3';
COMMIT;
Importance of SQL commands by role
At this point is it important to mention that knowing all of SQL is the best way to go about it, but also you should keep in mind that depending on your role, some commands types important than others. For example, a Data Engineer must know all types, but for the most of the time, he will be doing DDL and DML in order to make Data pipelines , and a Data/Business Analysts would use DQL most since they retrieve data in most of their time for processing and reporting purposes.
Conclusion
There are five types of SQL command, and all of them combined makes a master at SQL. However, one better starts with what’s most useful for his role and improve from there.
