Mastering SQL Commands: A Complete Guide

Hey Everyone! 👋 Today, I will break down the types of SQL commands. If you don’t know me yet, I’m Dhyuthidhar Saraswathula, and I love writing about Computer Science topics. Today, we’re diving into the backbone of SQL operations – SQL Commands.

Let’s start by understanding the five major types of SQL commands. And yes, the abbreviations might look similar, but their purpose is unique! Let’s explore each in more detail.

---

The Five Types of SQL Commands 🛠

---

1. Data Definition Language (DDL)

DDL commands are responsible for defining and modifying the structure of the database schema.

• CREATE: Used to create objects like tables, views, or indexes.

• DROP: Used to delete objects like tables, views, or indexes.

• TRUNCATE: Deletes all data in a table but retains the table structure (schema).

• ALTER: Used to modify the structure of an existing object.

🔧 Example:

CREATE TABLE Employees (
    emp_id INT,
    emp_name VARCHAR(100),
    position VARCHAR(50),
    salary DECIMAL
);

---

2. Data Control Language (DCL)

DCL commands handle the authorization and permissions of database users.

• GRANT: Gives specific privileges to a user (like SELECT, INSERT, etc.).

• REVOKE: Revokes the previously granted permissions from a user.

🔧 Example:

GRANT SELECT, INSERT ON Employees TO user1;
REVOKE INSERT ON Employees FROM user1;

---

3. Data Manipulation Language (DML)

DML commands are used to manipulate data stored in tables.

• INSERT: Adds new data into a table.

• DELETE: Removes data from a table (can be filtered with a WHERE clause).

• UPDATE: Updates existing data in a table.

🔧 Example:

INSERT INTO Employees (emp_id, emp_name, position, salary)
VALUES (1, 'John Doe', 'Manager', 50000);

UPDATE Employees
SET salary = 60000
WHERE emp_id = 1;

DELETE FROM Employees
WHERE emp_id = 1;

---

4. Data Query Language (DQL)

DQL is primarily concerned with data retrieval from the database.

• SELECT: Used to fetch data from the table based on conditions and filters.

🔧 Example:

SELECT emp_name, salary
FROM Employees
WHERE salary > 30000;

---

5. Transaction Control Language (TCL)

TCL commands help manage the changes made by DML operations and control transactions.

• COMMIT: Saves the changes permanently.

• ROLLBACK: Reverts changes made in the current transaction.

• SAVEPOINT: Sets a point in a transaction that you can return to using ROLLBACK.

📝 Example with a Transaction: Let’s say you’re managing two accounts and transferring money from one to another. If something goes wrong, you can ROLLBACK to undo the transaction.

BEGIN TRANSACTION;

UPDATE Accounts
SET balance = balance - 100
WHERE AccountID = 1;

UPDATE Accounts
SET balance = balance + 100
WHERE AccountID = 2;

-- Something went wrong, undo the transaction
ROLLBACK;

Or, if everything goes well:

-- Save the changes permanently
COMMIT;

🔖 Using Savepoints:

SAVEPOINT halfway;

-- More updates
UPDATE Accounts
SET balance = balance + 50
WHERE AccountID = 2;

-- Oops, undo only part of the changes
ROLLBACK TO halfway;

-- Commit the rest
COMMIT;

---

Important SQL Concepts 📌

• SQL Precedence for Boolean Operators: The order is NOT > AND > OR. Always keep this in mind when writing complex conditions.

• COUNT( < column > ): Counts the number of non-null rows in the column.

• NOT EQUAL TO (<> or !=):

    SELECT * FROM Employees WHERE Department <> 'HR';
  

• NOT Operator: Excludes rows that meet the specified condition.

    SELECT * FROM Employees WHERE NOT Department = 'HR';
  

• Sorting on Non-selected Columns: You can sort by a column that is not in the SELECT list.

    SELECT emp_name FROM Employees ORDER BY salary DESC;
  

---

Final Thoughts 💡

SQL is like a recipe book for data analysis – you’ve got the ingredients, and now you know how to use them! Each command, like an ingredient, plays a specific role in helping you manage and manipulate data efficiently.

Once you get the hang of these basic commands, you’ll quickly cook up insightful queries! 🔥

Happy querying, and don’t forget to keep practicing!