The Ultimate SQL Cheat Sheet: Commands, Joins, and Functions Reference

SQL (Structured Query Language) is the standard language for managing and manipulating relational databases. Whether you're working with MySQL, PostgreSQL, SQL Server, or SQLite, these core commands form the foundation of your database interactions.

Use this cheat sheet as a quick reference for common SQL syntax and best practices.

---

1. Basic Data Retrieval

The most common SQL task is retrieving data from a table.

Command	Description	Example
SELECT	Specifies the columns to retrieve	SELECT name, age
FROM	Specifies the table to retrieve from	FROM users
WHERE	Filters the result set based on a condition	WHERE age > 18
ORDER BY	Sorts the result set (ASC or DESC)	ORDER BY created_at DESC
LIMIT	Restricts the number of rows returned	LIMIT 10
DISTINCT	Returns only unique values	SELECT DISTINCT city

Example Query:

SELECT name, email
FROM users
WHERE country = 'US'
ORDER BY name ASC
LIMIT 5;

---

2. SQL Joins: Combining Data

Joins allow you to retrieve data from multiple tables based on a related column.

• INNER JOIN: Returns rows when there is a match in both tables.
• LEFT (OUTER) JOIN: Returns all rows from the left table, and the matched rows from the right table.
• RIGHT (OUTER) JOIN: Returns all rows from the right table, and the matched rows from the left table.
• FULL (OUTER) JOIN: Returns rows when there is a match in one of the tables.

Join Example:

SELECT orders.id, users.name
FROM orders
INNER JOIN users ON orders.user_id = users.id;

---

3. Aggregation and Grouping

Aggregate functions perform a calculation on a set of values and return a single value.

Function	Description
COUNT()	Returns the number of rows
SUM()	Returns the total sum of a numeric column
AVG()	Returns the average value
MIN()	Returns the smallest value
MAX()	Returns the largest value

GROUP BY and HAVING: GROUP BY is used with aggregate functions to group the result set by one or more columns. HAVING is used to filter groups.

SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

---

4. Data Modification (DML)

Commands for adding, updating, and removing data.

• INSERT: Add new rows.

  INSERT INTO users (name, email) VALUES ('John Doe', '[email protected]');
  

• UPDATE: Modify existing rows.

  UPDATE users SET email = '[email protected]' WHERE id = 1;
  

• DELETE: Remove rows.

  DELETE FROM users WHERE id = 1;
  

---

5. Schema Definition (DDL)

Commands for defining and managing the database structure.

• CREATE TABLE: Create a new table.

  CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2)
  );
  

• ALTER TABLE: Modify an existing table structure (add/drop columns).

  ALTER TABLE users ADD last_login TIMESTAMP;
  

• DROP TABLE: Delete a table and all its data.

  DROP TABLE temp_data;
  

• Indexes: Used to speed up data retrieval.

  CREATE INDEX idx_user_email ON users(email);
  

---

Common Questions (FAQ)

Q: SQL vs NoSQL: Which one should I choose?

A: Use SQL (Relational) when your data is structured, you need complex joins, and ACID compliance is a priority. Use NoSQL (Non-relational) for unstructured data, high scalability requirements, and rapid development cycles where schemas change frequently.

Q: What are the best practices for database design?

A:

1. Normalization: Reduce data redundancy.
2. Naming Conventions: Use consistent, lowercase, underscore-separated names.
3. Primary Keys: Every table should have a unique identifier.
4. Use Indexes Wisely: They speed up reads but slow down writes.

Q: What is a SQL Injection and how can I prevent it?

A: SQL Injection is a vulnerability where an attacker can execute malicious SQL statements. Prevention: Never concatenate user input directly into queries. Use Prepared Statements (Parameterized Queries) provided by your programming language's database driver.

Q: How can I optimize SQL performance?

A:

• Avoid SELECT *; only fetch the columns you need.
• Use EXPLAIN to analyze query execution plans.
• Ensure proper indexing on columns used in WHERE and JOIN clauses.
• Avoid using subqueries when a JOIN is more efficient.

---

Related Tools on Tool3M

• SQL Formatter: Clean up and format your SQL queries for better readability.
• JSON to CSV: Convert your database exports between JSON and CSV formats.