The Structured Query Language (SQL), as we all know, is a database language that allows us to execute specific operations on existing databases as well as construct new databases. To complete the tasks, SQL employs commands such as Create, Drop, and Insert.
These SQL commands are primarily divided into four groups:
- Data Definition Language (DDL)
- DQL stands for Data Query Language.
- DML (Data Manipulation Language)
- Data Control Language (DCL)
1) What Is DDL?
DDL, or data definition language, is a programming language for creating and modifying databases. It’s termed a language, but it’s more like syntax in and of itself. Alternatively, a sequence of statements allows the user to define or edit data structures and objects like data tables.
How to Use DDL Commands?
As previously stated, DDL is the component of the SQL syntax that deals with the elements of a database by executing commands (also known as statements) such as:
- CREATE
- ALTER
- DROP
- RENAME
- TRUNCATE
1) CREATE Statement
It’s only natural to begin from the beginning, with the CREATE command. It’s used to create complete databases and objects, as the name implies:
CREATE object_type object_name;
For example, if we wish to make a table, we must follow the rules as follows:
CREATE TABLE object_name (column_name data_type);
2) ALTER Statement
The CREATE command demonstrates how the ALTER command works, which is what we use to change existing objects. For example, we can use this DDL statement to change the columns in a table by adding, removing, or renaming them.
To add a column called “date of purchase,” start with the same structure, but instead of using CREATE, use ALTER before naming the object’s type and name. Following that, you write out the specific change:
ALTER TABLE sales ADD COLUMN date_of_purchase DATE;
3) DROP Statement
What happens if you need to remove a database object? Use the DROP statement in that case:
DROP object_type object_name;
For example, suppose you wish to delete the entire “customers” database using a single line of code:
DROP TABLE customers;
4) RENAME Statement
Another useful DDL tool is RENAME, which allows you to rename an object, such as a database table:
RENAME object_type object name TO new_object_name;
To give you an example, if we hadn’t dropped the “customers” table, we could have renamed it “customer data” as follows:
RENAME TABLE customers TO customer_data;
5) TRUNCATE Statement
If we wish to keep using the table as a database object, we can remove data without using DROP. TRUNCATE is the ideal DDL command to use in this situation:
TRUNCATE object_type object_name;
In the context of our example, if we only want to delete the values in our “customers” table, we can use the following code:
TRUNCATE TABLE customers;
2) What is DML?
It’s time to implement DML, or data manipulation language, after you’ve already set up your database – or loaded one that you’d like to work with. This SQL syntax allows you to manipulate existing data objects using a set of actions.
What Are the SQL DML Commands?
DML has various instructions – or statements – that we utilize as we work our way through data tables, as do all SQL syntax components:
- SELECT
- INSERT
- UPDATE
- DELETE
The instructions themselves contain a number of keywords or subqueries that indicate the operation we wish to do on the database.
1) SELECT Statement
Starting with the SELECT command, which is used to get data from database objects such as tables:
SELECT * FROM sales;
2) INSERT Statement
Inserting data into tables is done with the INSERT command. It allows you to add extra records or rows to the table while working with it. The keywords INTO and VALUES go hand in hand with this clause. Consider the following scenario:
INSERT INTO sales (purchase_number, date_of_purchase) VALUES (1, ‘20xx-xx-xx’);
3) UPDATE Statement
In SQL, we can select and insert data, but we can also update it. You can use the DML UPDATE command to update existing data in your tables. It has a somewhat distinct syntax that is best explained with an example.
The following code will allow us to change the previously inserted date of purchase number 1 – October 11, 2020 – to something else. That would be December 12, 2022 in our case:
UPDATE sales SET date_of_purchase = ‘2022-12-12’ WHERE purchase_number = 1;
4) DELETE Statement
The DELETE command is identical to DDL’s TRUNCATE, but there is one significant distinction. While TRUNCATE allows us to delete all of the records in a table, DELETE allows you to specify exactly what you want to delete.
The following line of code, for example, will delete all records from the “sales” table:
DELETE FROM sales;
3) What Is DCL?
The data control language (DCL) is essentially a SQL syntax that allows you to manage users’ access in a database using a pair of commands. Furthermore, database administrators can manage user access if they have full access to a database.
What Are the DCL Commands?
DCL has only two SQL statements:
- GRANT
- REVOKE
1) GRANT Statement
GRANT grants users certain permissions. You can use the following syntax to run the command:
GRANT type_of_permission ON database_name.table_name TO '@username'@'localhost'
You can grant a certain type of permission using this line of code, such as full or partial access to the resources in a specific data table.
2) REVOKE Statement
The REVOKE clause is used to revoke database user permissions and privileges, which is the polar opposite of the GRANT statement. Their syntax, however, is identical:
REVOKE type_of_permission ON database_name.table_name FROM 'username'@'localhost'
Essentially, you can cancel a privilege from someone instead of providing them permission.
4) What Is TCL?
It is critical to be in control of the transactions you conduct while dealing with relational database management systems in a professional setting. To put it another way, you want to know exactly what you’re doing in your database when you insert, delete, or update data.
What Are the TCL Statements?
You must be familiar with TCL’s commands in order to deal with it, specifically:
- COMMIT
- ROLLBACK
- SAVEPOINT
It’s vital to remember that these will only respond to the DML syntax components INSERT, DELETE, and UPDATE before we look at each one individually.
1) COMMIT Statement
The modifications you’ve made will be saved permanently in the database, and other users will be able to access the amended version.
Assume you want to edit a record in the “Customers” table by altering the fourth client’s last name from Akshay to Amit:
UPDATE customers SET last_name = 'Amit' WHERE customer_id = 4;
Your task isn’t over yet, though. The rest of the database system’s users won’t be able to tell that you’ve changed anything. You must add a COMMIT statement at the conclusion of the UPDATE block to finish the process:
UPDATE customers SET last_name = 'Amit' WHERE customer_id = 4 COMMIT;
COMMIT basically saves the transaction to the database. The changes are permanent and cannot be reversed once they have been made.
2) ROLLBACK Statement
The number of committed nations might swiftly grow. If you’re the administrator, for example, you might need to use COMMIT 20 times today.
As a result, you may inadvertently insert or alter some of your data. Fortunately, the transaction control language function ROLLBACK allows you to restore the database to its previous committed state, undoing any changes you don’t wish to retain permanently.
To use this feature, add ROLLBACK; to the end of your code:
UPDATE customers SET last_name = 'Amit' WHERE customer_id = 4 COMMIT; ROLLBACK;
3) SAVEPOINT Statement
Within a transaction, a save point is a logical rollback point. If an error happens after you create a save point, you can use the rollback feature to undo the activities you’ve done up to that point.
SAVEPOINT savepoint_name;
Related/References
- Microsoft Azure Data on Cloud Bootcamp: Hands-On Labs & Projects for Job-Ready Skills
- Azure Data Lake For Beginners: All you Need To Know
- Batch Processing Vs Stream Processing: All you Need To Know
- Introduction to Big Data and Big Data Architectures
Next Task For You
In our Azure Data Engineer training program, we will cover 50+ Hands-On Labs. If you want to begin your journey towards becoming a Microsoft Certified: Azure Data Engineer Associate by checking out our FREE CLASS.
Leave a Reply