Key SQL Command Types to Master in DBMS
Curated by
cdteliot
4 min read
4,957
3
SQL commands in Database Management Systems (DBMS) are essential for interacting with and manipulating data. These commands are typically categorized into five main types: Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), Transaction Control Language (TCL), and Data Query Language (DQL), each serving specific functions in database operations and management.
1. Understanding Data Definition Language (DDL): Key Commands for Database Structure
Data Definition Language (DDL) is a subset of SQL commands used to define, modify, and manage the structure of database objects. These commands allow users to create, alter, and delete database schemas and their components. DDL commands are primarily used by database administrators and developers to establish and maintain the foundation of a database system.
DDL commands are auto-commit operations, meaning that changes made using these commands are immediately applied and cannot be rolled back
This command creates a table named "Employees" with four columns: EmployeeID, FirstName, LastName, and Department
This command adds a "Salary" column to the "Employees" table
This command completely removes the "Employees" table and all its data from the database
This command deletes all rows from the "Employees" table but keeps the table structure intact
This renames the "Employees" table to "Staff"
This adds a comment to the "Employees" table in the data dictionary
Command | Description | Syntax |
---|---|---|
CREATE | Creates new database objects such as tables, indexes, views, and stored procedures | CREATE TABLE table_name (column1 data_type, column2 data_type, ...); |
ALTER | Modifies the structure of existing database objects | ALTER TABLE table_name ADD COLUMN column_name data_type; |
DROP | Removes existing database objects | DROP TABLE table_name; |
TRUNCATE | Removes all records from a table, including allocated space | TRUNCATE TABLE table_name; |
RENAME | Changes the name of an existing database object | RENAME TABLE old_table_name TO new_table_name; |
COMMENT | Adds comments to the data dictionary | COMMENT 'comment_text' ON TABLE table_name; |
1
. This characteristic distinguishes them from Data Manipulation Language (DML) commands, which can be undone if necessary.
The CREATE command is used to establish new database objects. For example, to create a new table, you would use the following syntax:
sqlCREATE TABLE Employees ( EmployeeID INT, FirstName VARCHAR(50), LastName VARCHAR(50), Department VARCHAR(50) );
2
.
The ALTER command allows for modifications to existing database structures. For instance, to add a new column to an existing table:
sqlALTER TABLE Employees ADD COLUMN Salary DECIMAL(10,2);
2
.
The DROP command is used to remove database objects. To delete a table:
sqlDROP TABLE Employees;
2
.
TRUNCATE is used to quickly remove all records from a table:
sqlTRUNCATE TABLE Employees;
3
.
The RENAME command changes the name of an existing database object:
sqlRENAME TABLE Employees TO Staff;
3
.
Lastly, the COMMENT command allows adding descriptive text to database objects:
sqlCOMMENT ON TABLE Employees IS 'This table stores employee information';
3
.
DDL commands are crucial for establishing and maintaining the structure of a database. They provide the foundation upon which data can be stored, manipulated, and retrieved efficiently.3 sources
2. Mastering Data Manipulation Language (DML): Essential SQL Commands
Data Manipulation Language (DML) commands are essential SQL operations used to interact with and modify data stored in database tables. These commands allow users to insert new records, update existing data, delete information, and retrieve specific data sets from the database.
The main DML commands in SQL are:
The SELECT command is the most commonly used DML operation, allowing users to query and retrieve data from database tables
The UPDATE command modifies existing records in a table. It typically uses a WHERE clause to specify which records should be updated
DELETE removes records from a table based on specified conditions
It's important to note that while SELECT is considered a DQL (Data Query Language) command by some sources, it is often grouped with DML commands due to its data manipulation capabilities
Command | Description | Basic Syntax |
---|---|---|
SELECT | Retrieves data from one or more tables | SELECT column1, column2 FROM table_name WHERE condition; |
INSERT | Adds new records into a table | INSERT INTO table_name (column1, column2) VALUES (value1, value2); |
UPDATE | Modifies existing records in a table | UPDATE table_name SET column1 = value1 WHERE condition; |
DELETE | Removes records from a table | DELETE FROM table_name WHERE condition; |
1
2
. It can be combined with various clauses and functions to filter, sort, and aggregate data as needed.
INSERT is used to add new records to a table. It specifies the table name, column names, and the values to be inserted3
4
. For example:
sqlINSERT INTO Students (StudentID, Name, Age) VALUES (1, 'John Doe', 20);
3
4
. For instance:
sqlUPDATE Students SET Age = 21 WHERE StudentID = 1;
3
4
. Like UPDATE, it often uses a WHERE clause to target specific records:
sqlDELETE FROM Students WHERE StudentID = 1;
1
2
. DML commands, except for SELECT, directly modify the data stored in database tables2
.
When using DML commands, it's crucial to be cautious, especially with UPDATE and DELETE operations, as they can affect multiple records if not properly conditioned. Always use appropriate WHERE clauses to target specific data and consider using transactions for complex operations to ensure data integrity4
.4 sources
3. Mastering Data Control Language (DCL): Managing Database Security and Access
Data Control Language (DCL) commands are used to manage access privileges and security in database systems. These commands allow database administrators to control who can access specific database objects and what operations they can perform. The two primary DCL commands are GRANT and REVOKE.
The GRANT command is used to give specific permissions to users or roles. Privileges that can be granted include SELECT, INSERT, UPDATE, DELETE, and more, depending on the database system
This command gives user1 the ability to select and insert data into the Employees table
This command removes the INSERT privilege on the Employees table from user1
Command | Description | Basic Syntax |
---|---|---|
GRANT | Assigns specific privileges to users or roles | GRANT privilege_type ON object_name TO user_name; |
REVOKE | Removes previously granted privileges from users or roles | REVOKE privilege_type ON object_name FROM user_name; |
1
. For example:
sqlGRANT SELECT, INSERT ON Employees TO user1;
2
.
The REVOKE command is used to remove previously granted privileges. It follows a similar syntax to the GRANT command:
sqlREVOKE INSERT ON Employees FROM user1;
2
.
Some database systems, like Microsoft SQL Server, include an additional DCL command called DENY, which explicitly prohibits a user from having a specific privilege, even if it's granted through a role or group membership3
.
DCL commands can be applied to various database objects, including tables, views, and stored procedures. The specific privileges available and the exact syntax may vary slightly between different database management systems4
.
It's important to note that to use DCL commands, a user typically needs administrative privileges. In most systems, only users with the appropriate level of access can grant or revoke privileges5
.
By effectively using DCL commands, database administrators can implement the principle of least privilege, ensuring that users have only the access they need to perform their tasks, thus enhancing database security.5 sources
4. Mastering Transaction Control Language (TCL): Ensuring Data Consistency in SQL
Transaction Control Language (TCL) commands are essential for managing database transactions, ensuring data consistency and integrity. These commands allow users to control the behavior of transactions, which are sequences of database operations treated as a single unit of work.
The COMMIT command is used to save all changes made in a transaction permanently to the database. Once a COMMIT is issued, the changes become permanent and visible to other users
This transaction updates a student's name and then commits the change, making it permanent
This transaction attempts to update a student's name but then rolls back the change, leaving the original name intact
This creates a savepoint, performs an update, and then rolls back to the savepoint, undoing only the update operation
Command | Description | Syntax |
---|---|---|
COMMIT | Permanently saves the changes made in a transaction | COMMIT; |
ROLLBACK | Undoes all changes made in the current transaction | ROLLBACK; |
SAVEPOINT | Creates a point within a transaction to which you can later roll back | SAVEPOINT savepoint_name; |
1
. For example:
sqlUPDATE STUDENT SET STUDENT_NAME = 'Mathew' WHERE STUDENT_NAME = 'Mahtwe'; COMMIT;
1
.
The ROLLBACK command is used to undo changes made in the current transaction, reverting the database to its previous state. This is useful when errors occur or when you want to discard changes1
. For instance:
sqlUPDATE STUDENT SET STUDENT_NAME = 'Stewart' WHERE STUDENT_NAME = 'Mathew'; ROLLBACK;
1
.
The SAVEPOINT command creates a point within a transaction to which you can later roll back. This allows for more granular control over transactions2
. For example:
sqlSAVEPOINT S1; UPDATE STUDENT SET AGE = 15 WHERE STUDENT_ID = 100; ROLLBACK TO S1;
1
.
It's important to note that in some database systems, an AUTOCOMMIT feature may be available. When enabled, each individual statement is automatically committed, eliminating the need for explicit COMMIT commands1
.
TCL commands are crucial for maintaining database consistency, especially in multi-user environments or when performing complex operations involving multiple tables. They provide a mechanism to ensure that all parts of a transaction are completed successfully before making changes permanent, or to discard changes if any part of the transaction fails3
4
.5 sources
5. Mastering Data Query Language (DQL): Retrieving Data Efficiently in SQL
Data Query Language (DQL) is a subset of SQL used primarily for retrieving data from databases. The SELECT statement is the cornerstone of DQL, allowing users to extract specific information from one or more tables.
The SELECT statement is highly versatile and can be used to perform complex queries. For example, you can use it to retrieve data from multiple tables, apply various functions, and perform calculations on the fly
This query joins the customers and orders tables, counts the number of orders for each customer, filters for customers with more than 5 orders, and sorts the results in descending order of order count
This query selects the name and email of the customer who has placed the most orders
DQL Component | Description | Example |
---|---|---|
SELECT | Specifies the columns to retrieve | SELECT name, email FROM customers; |
FROM | Indicates the table(s) to query | SELECT * FROM orders; |
WHERE | Filters rows based on conditions | SELECT * FROM products WHERE price > 100; |
ORDER BY | Sorts the result set | SELECT * FROM employees ORDER BY hire_date DESC; |
GROUP BY | Groups rows that have the same values | SELECT country, COUNT(*) FROM customers GROUP BY country; |
HAVING | Specifies a search condition for a group | SELECT country, COUNT() FROM customers GROUP BY country HAVING COUNT() > 10; |
JOIN | Combines rows from two or more tables | SELECT c.name, o.order_date FROM customers c JOIN orders o ON c.customer_id = o.customer_id; |
1
2
.
sqlSELECT c.name, COUNT(o.order_id) AS order_count FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.name HAVING COUNT(o.order_id) > 5 ORDER BY order_count DESC;
1
.
DQL also supports subqueries, allowing you to nest one SELECT statement inside another. For instance:
sqlSELECT name, email FROM customers WHERE customer_id = ( SELECT customer_id FROM orders GROUP BY customer_id ORDER BY COUNT(*) DESC LIMIT 1 );
1
.
It's important to note that while some sources classify SELECT as part of DML, it is more commonly considered the primary command of DQL due to its focus on data retrieval rather than modification2
3
5
.
DQL commands are read-only operations that do not alter the database state. They allow users to query and analyze data without the risk of inadvertently modifying or deleting information. This makes DQL an essential tool for reporting, data analysis, and information retrieval in database systems3
5
.5 sources
Related
What are some advanced uses of the SELECT statement in DQL
How can I combine multiple tables using JOIN in DQL
What are the differences between the WHERE and HAVING clauses in DQL
How do subqueries enhance data retrieval in DQL
Can you explain the purpose of the GROUP BY clause in DQL