SQL Quick Reference: Simplifying Database Management

SQL Cheatsheet

This blog comprehensively guides the most important SQL commands and operations. It covers basic queries, joins, subqueries, indexes, and more advanced concepts.

Table of Contents

1. SQL Basics
2. Data Definition Language (DDL)
3. Data Manipulation Language (DML)
4. Data Query Language (DQL)
5. Data Control Language (DCL)
6. Joins
7. Subqueries
8. Indexes
9. Aggregation Functions
10. Grouping and Sorting
11. Transactions
12. Advanced SQL
13. Best Practices

---

SQL Basics

Structure of a SQL Query

SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column
LIMIT n;

Commenting in SQL

• Single-line comment: -- This is a comment
• Multi-line comment:

  /* This is a 
     multi-line comment */

---

Data Definition Language (DDL)

Creating a Table

CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
);

Example:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    hire_date DATE
);

Altering a Table

Adding a Column

ALTER TABLE table_name
ADD column_name datatype;

Dropping a Column

ALTER TABLE table_name
DROP COLUMN column_name;

Modifying a Column

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

Renaming a Table

ALTER TABLE old_table_name
RENAME TO new_table_name;

Dropping a Table

DROP TABLE table_name;

Creating an Index

CREATE INDEX index_name
ON table_name (column_name);

Dropping an Index

DROP INDEX index_name;

---

Data Manipulation Language (DML)

Inserting Data into a Table

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Example:

INSERT INTO employees (id, name, age, hire_date)
VALUES (1, 'John Doe', 30, '2022-01-01');

Updating Data in a Table

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example:

UPDATE employees
SET age = 31
WHERE id = 1;

Deleting Data from a Table

DELETE FROM table_name
WHERE condition;

Example:

DELETE FROM employees
WHERE id = 1;

---

Data Query Language (DQL)

Selecting Data from a Table

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column
LIMIT n;

Example:

SELECT * FROM employees;
SELECT name, age FROM employees WHERE age > 30;

Wildcards

• *: Select all columns
• %: Wildcard for zero or more characters (in LIKE clause)
• _: Wildcard for exactly one character (in LIKE clause)

Example:

SELECT * FROM employees WHERE name LIKE 'J%';

---

Data Control Language (DCL)

Granting Permissions

GRANT permission ON object TO user;

Example:

GRANT SELECT, INSERT ON employees TO 'user1';

Revoking Permissions

REVOKE permission ON object FROM user;

Example:

REVOKE SELECT ON employees FROM 'user1';

---

Joins

INNER JOIN

Returns rows when there is a match in both tables.

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

LEFT JOIN (or LEFT OUTER JOIN)

Returns all rows from the left table, and matched rows from the right table. If no match, NULL values will appear for columns from the right table.

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

RIGHT JOIN (or RIGHT OUTER JOIN)

Returns all rows from the right table, and matched rows from the left table. If no match, NULL values will appear for columns from the left table.

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

FULL OUTER JOIN

Returns rows when there is a match in one of the tables.

SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;

---

Subqueries

Subquery in SELECT

SELECT column1, (SELECT column2 FROM table2 WHERE condition) AS alias
FROM table1;

Subquery in WHERE

SELECT column1
FROM table1
WHERE column2 IN (SELECT column2 FROM table2 WHERE condition);

Subquery in FROM

SELECT alias.column1
FROM (SELECT column1 FROM table2 WHERE condition) AS alias;

---

Indexes

Creating an Index

CREATE INDEX index_name
ON table_name (column1, column2);

Dropping an Index

DROP INDEX index_name;

Unique Index

Ensures that all values in a column (or group of columns) are unique.

CREATE UNIQUE INDEX index_name
ON table_name (column_name);

---

Aggregation Functions

COUNT

Counts the number of rows that match a specific condition.

SELECT COUNT(*) FROM table_name WHERE condition;

SUM

Returns the sum of values in a column.

SELECT SUM(column_name) FROM table_name;

AVG

Returns the average of values in a column.

SELECT AVG(column_name) FROM table_name;

MIN and MAX

Returns the minimum and maximum values in a column.

SELECT MIN(column_name), MAX(column_name) FROM table_name;

---

Grouping and Sorting

GROUP BY

Groups rows that have the same values into summary rows.

SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1;

HAVING

Filters groups after applying GROUP BY.

SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1
HAVING COUNT(*) > 5;

ORDER BY

Sorts the result set in ascending or descending order.

SELECT column1, column2
FROM table_name
ORDER BY column1 DESC;

---

Transactions

Starting a Transaction

BEGIN TRANSACTION;

Committing a Transaction

COMMIT;

Rolling Back a Transaction

ROLLBACK;

---

Advanced SQL

CASE WHEN

Conditional logic inside a query.

SELECT column1,
       CASE
           WHEN condition THEN 'Result 1'
           WHEN condition THEN 'Result 2'
           ELSE 'Default'
       END AS alias
FROM table_name;

UNION and UNION ALL

• UNION: Combines the result sets of two or more queries (removes duplicates).
• UNION ALL: Combines result sets (keeps duplicates).

SELECT column FROM table1
UNION
SELECT column FROM table2;

SELECT column FROM table1
UNION ALL
SELECT column FROM table2;

---

Best Practices

• Use JOIN instead of subqueries when possible for better performance.
• Index frequently searched columns to speed up queries.
• Avoid SELECT * and specify only the columns you need.
• Use LIMIT for large result sets to restrict the number of rows returned.
• Normalize your data to avoid redundancy and improve consistency.
• Use WHERE clauses instead of HAVING to filter data before aggregation.
• Test queries for performance, especially for large datasets.
• Use transactions to ensure data consistency, especially for operations that involve multiple DML statements.

---

Conclusion

This SQL cheatsheet covers all the essential SQL commands and techniques you’ll need for working with relational databases. Whether you are querying, inserting, updating, or joining data, this guide will help you work more effectively with SQL.