SQL Commands

Let’s explore Commands in SQL

SQL (Structured Query Language) commands are used to interact with relational databases.

SQL commands are categorized based on their purpose -  such as defining, manipulating, controlling, or querying data.

The main categories with short explanations and Examples are as follows.

Data Definition Language (DDL)

DDL commands define and modify the structure of database objects (tables, schemas etc..)

Create:  Creates a new Database or table.

Ex: CREATE TABLE employees (id INT, name VARCHAR(50));

ALTER: Modifies an existing database object.

Ex: ALTER TABLE employees ADD salary DECIMAL(10, 2);

DROP: Deleted a database or table permanently.

          Ex: DROP TABLE employees;

TRUNCATE: Removes all data from a table but keeps the structure.

          Ex: TRUNCATE TABLE emplyoyees;

RENAME: Changes the name of a table.

          Ex: RENAME TABLE employees TO Staff;

Data Manipulation Language (DML)

          DML commands handle data inside tables.

SELECT:  Retrieve data from one or more tables.

          Ex: SELECT * FROM employees;

INSERT:  Adds new records into a table.

          Ex: INSERT INTO employees (id, name) VALUES (1, ‘Alice’);

UPDATE: Modifies existing records.

          Ex: UPDATE employees SET salary = 5000 WHERE id = 1;

Data Control Language (DCL)

DCL manages user and permissions.

GRANT:  Gives privileges to users.

          Ex: GRANT SELECT, INSERT on employees TO user1;

REVOKE:  Removes privileges.

          Ex: REVOKE INSERT ON employees FROM user1;

Transaction Control Language (TCL)

COMMIT: Saves all changes made during the transaction.

          Ex: COMMIT;

ROLLBACK: Undoes changes since the last COMMIT.

          Ex: ROLLBACK;

SAVEPOINT: Sets a point to rollback to later.

          Ex: SAVEPOINT sp1;

RELEASE SAVEPOINT:  Sets characteristics for a transaction.

          Ex: SET TRANSACTION READ ONLY;

Data Query Language (DQL)

Sometimes treated as pert of DML, DQL is focused purely on querying data.

SELECT:  Retrieves data (with conditions, joins, grouping, etc.)

          Ex: SELECT name, Salary FROM employees

WHERE salary > 3000;

Common SQL Clauses and Keywords:

WHERE: This command filters rows.

          Ex: SELECT * from employees

WHERE department = ‘HR’;

ORDER BY: This command sorts results.

          Ex: SELECT * FROM employees

 ORDER BY salary DESC ;

GROUP BY: Groups rows for aggregation.

          Ex: SELECT department, COUNT (*)  FROM employees

GROUP BY Department;

HAVING: Filter groups (used with GROUP BY).

          Ex: SELECT department, COUNT (*)  FROM employees

GROUP BY Department  HAVING count (*) > 5;

JOIN: Combines rows from multiple tables.

          Ex: SELECT * FROM employees

 JOIN departments

ON employees.dept_id = departments.id;

JOIN types:

          INNER JOIN- Returns records that have matching values in both tables.

          Ex: SELECT employees.name, departments.dept_name FROM employees

INNER JOIN departments

ON employees.dept_id = departments.dept_id;

          LEFT JOIN: Returns all records from the left table and matching records from the left table.

          Ex: SELECT employees.name, departments.department_name FROM employees

LEFT JOIN departments ON employees.dept_id = departments.id;

          RIGHT JOIN: Returns all records from the right table.

          Ex: SELECT employees.name, departments.department_name FROM employees

RIGHT JOIN departments

ON employees.dept_id = departments.id;

LEFT OUTTER JOIN: Returns all records when there’s a match in either table.

          Ex: : SELECT employees.name, departments.department_name FROM employees

FULL OUTER  JOIN departments

ON employees.dept_id = departments.id;

UNION: Combines results of multiple queries.

          Ex: SELECT name FROM employees UNION SELECT name FROM managers;

LIKE: Pattern matching.

          Ex: SELECT name FROM employees UNION SELECT name FROM managers WHERE name like A%;

IN: Match from a list.

          Ex: SELECT department, COUNT (*)  FROM employees GROUP BY Department WHERE department IN (‘HR’, ‘Finance’);

BETWEEN: It helps with Rage condition.

          Ex: SELECT * FROM employees WHERE salary BETWEEN 3000 AND 5000;

DISTINCT: this command helps you to Restricts number of results.

          Ex: SELECT * FROM employees LIMIT 10;

CONCLUSION:  SQL provides a complete set of commands to create, manage, and retrieve data efficiently from relational databases.

          By mastering there commands from DDL, DML to JOINs and clauses enables precise and efficient database operations.