algodaily.com
 
Key SQL Command Types to Master in DBMS
User avatar
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.
CommandDescriptionSyntax
CREATECreates new database objects such as tables, indexes, views, and stored proceduresCREATE TABLE table_name (column1 data_type, column2 data_type, ...);
ALTERModifies the structure of existing database objectsALTER TABLE table_name ADD COLUMN column_name data_type;
DROPRemoves existing database objectsDROP TABLE table_name;
TRUNCATERemoves all records from a table, including allocated spaceTRUNCATE TABLE table_name;
RENAMEChanges the name of an existing database objectRENAME TABLE old_table_name TO new_table_name;
COMMENTAdds comments to the data dictionaryCOMMENT 'comment_text' ON TABLE table_name;
DDL commands are auto-commit operations, meaning that changes made using these commands are immediately applied and cannot be rolled back
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:
sql
CREATE TABLE Employees ( EmployeeID INT, FirstName VARCHAR(50), LastName VARCHAR(50), Department VARCHAR(50) );
This command creates a table named "Employees" with four columns: EmployeeID, FirstName, LastName, and Department
2
.
The ALTER command allows for modifications to existing database structures. For instance, to add a new column to an existing table:
sql
ALTER TABLE Employees ADD COLUMN Salary DECIMAL(10,2);
This command adds a "Salary" column to the "Employees" table
2
.
The DROP command is used to remove database objects. To delete a table:
sql
DROP TABLE Employees;
This command completely removes the "Employees" table and all its data from the database
2
.
TRUNCATE is used to quickly remove all records from a table:
sql
TRUNCATE TABLE Employees;
This command deletes all rows from the "Employees" table but keeps the table structure intact
3
.
The RENAME command changes the name of an existing database object:
sql
RENAME TABLE Employees TO Staff;
This renames the "Employees" table to "Staff"
3
.
Lastly, the COMMENT command allows adding descriptive text to database objects:
sql
COMMENT ON TABLE Employees IS 'This table stores employee information';
This adds a comment to the "Employees" table in the data dictionary
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.
javatpoint.com favicon
sqlshack.com favicon
geeksforgeeks.org favicon
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:
CommandDescriptionBasic Syntax
SELECTRetrieves data from one or more tablesSELECT column1, column2 FROM table_name WHERE condition;
INSERTAdds new records into a tableINSERT INTO table_name (column1, column2) VALUES (value1, value2);
UPDATEModifies existing records in a tableUPDATE table_name SET column1 = value1 WHERE condition;
DELETERemoves records from a tableDELETE FROM table_name WHERE condition;
The SELECT command is the most commonly used DML operation, allowing users to query and retrieve data from database tables
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 inserted
3
4
.
For example:
sql
INSERT INTO Students (StudentID, Name, Age) VALUES (1, 'John Doe', 20);
The UPDATE command modifies existing records in a table. It typically uses a WHERE clause to specify which records should be updated
3
4
.
For instance:
sql
UPDATE Students SET Age = 21 WHERE StudentID = 1;
DELETE removes records from a table based on specified conditions
3
4
.
Like UPDATE, it often uses a WHERE clause to target specific records:
sql
DELETE FROM Students WHERE StudentID = 1;
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
1
2
.
DML commands, except for SELECT, directly modify the data stored in database tables
2
.
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 integrity
4
.
geeksforgeeks.org favicon
docs.getdbt.com favicon
javatpoint.com favicon
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.
CommandDescriptionBasic Syntax
GRANTAssigns specific privileges to users or rolesGRANT privilege_type ON object_name TO user_name;
REVOKERemoves previously granted privileges from users or rolesREVOKE privilege_type ON object_name FROM user_name;
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
1
.
For example:
sql
GRANT SELECT, INSERT ON Employees TO user1;
This command gives user1 the ability to select and insert data into the Employees table
2
.
The REVOKE command is used to remove previously granted privileges. It follows a similar syntax to the GRANT command:
sql
REVOKE INSERT ON Employees FROM user1;
This command removes the INSERT privilege on the Employees table 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 membership
3
.
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 systems
4
.
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 privileges
5
.
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.
docs.ocient.com favicon
lifewire.com favicon
en.wikipedia.org favicon
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.
CommandDescriptionSyntax
COMMITPermanently saves the changes made in a transactionCOMMIT;
ROLLBACKUndoes all changes made in the current transactionROLLBACK;
SAVEPOINTCreates a point within a transaction to which you can later roll backSAVEPOINT savepoint_name;
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
1
.
For example:
sql
UPDATE STUDENT SET STUDENT_NAME = 'Mathew' WHERE STUDENT_NAME = 'Mahtwe'; COMMIT;
This transaction updates a student's name and then commits the change, making it permanent
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 changes
1
.
For instance:
sql
UPDATE STUDENT SET STUDENT_NAME = 'Stewart' WHERE STUDENT_NAME = 'Mathew'; ROLLBACK;
This transaction attempts to update a student's name but then rolls back the change, leaving the original name intact
1
.
The SAVEPOINT command creates a point within a transaction to which you can later roll back. This allows for more granular control over transactions
2
.
For example:
sql
SAVEPOINT S1; UPDATE STUDENT SET AGE = 15 WHERE STUDENT_ID = 100; ROLLBACK TO S1;
This creates a savepoint, performs an update, and then rolls back to the savepoint, undoing only the update operation
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 commands
1
.
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 fails
3
4
.
kaznu.kz favicon
scaler.com favicon
docs.deistercloud.com favicon
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.
DQL ComponentDescriptionExample
SELECTSpecifies the columns to retrieveSELECT name, email FROM customers;
FROMIndicates the table(s) to querySELECT * FROM orders;
WHEREFilters rows based on conditionsSELECT * FROM products WHERE price > 100;
ORDER BYSorts the result setSELECT * FROM employees ORDER BY hire_date DESC;
GROUP BYGroups rows that have the same valuesSELECT country, COUNT(*) FROM customers GROUP BY country;
HAVINGSpecifies a search condition for a groupSELECT country, COUNT() FROM customers GROUP BY country HAVING COUNT() > 10;
JOINCombines rows from two or more tablesSELECT c.name, o.order_date FROM customers c JOIN orders o ON c.customer_id = o.customer_id;
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
1
2
.
sql
SELECT 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;
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
1
.
DQL also supports subqueries, allowing you to nest one SELECT statement inside another. For instance:
sql
SELECT name, email FROM customers WHERE customer_id = ( SELECT customer_id FROM orders GROUP BY customer_id ORDER BY COUNT(*) DESC LIMIT 1 );
This query selects the name and email of the customer who has placed the most orders
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 modification
2
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 systems
3
5
.
linkedin.com favicon
oreilly.com favicon
byjus.com favicon
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