SQL Cheatsheet

--------------------------------------------------
I. Basic Querying
--------------------------------------------------

1. SELECT: Retrieve data from one or more columns.

Syntax: SELECT column1, column2 FROM table_name;
All Columns: SELECT * FROM table_name;

2. SELECT DISTINCT: Retrieve only unique values from a column.

Syntax: SELECT DISTINCT column1 FROM table_name;

3. WHERE: Filter rows based on a condition.

Syntax: SELECT column1 FROM table_name WHERE condition;
Operators: =, != or <>, >, <, >=, <=
Example: WHERE column_name = 'some_value';
Example: WHERE column_name > 100;

4. ORDER BY: Sort the result set.

Syntax (Ascending): SELECT c1, c2 FROM table ORDER BY c1 ASC; (ASC is default)
Syntax (Descending): SELECT c1, c2 FROM table ORDER BY c2 DESC;
Multiple Sorts: SELECT c1, c2 FROM table ORDER BY c1 ASC, c2 DESC;

5. LIMIT: Restrict the number of rows returned.

Syntax: SELECT column1 FROM table_name LIMIT 10;
Example: SELECT c1 FROM table ORDER BY c2 DESC LIMIT 5;

--------------------------------------------------
II. Filtering Operators (for WHERE and HAVING)
--------------------------------------------------

6. AND / OR: Combine multiple conditions.

Syntax (AND): WHERE condition1 AND condition2;
Syntax (OR): WHERE condition1 OR condition2;
Precedence: WHERE (condition1 AND condition2) OR condition3;

7. BETWEEN: Select values within a given range (inclusive).

Syntax: WHERE column_name BETWEEN value1 AND value2;
Example: WHERE date_column BETWEEN '2023-01-01' AND '2023-12-31';

8. IN: Select values that match any value in a list.

Syntax: WHERE column_name IN (value1, value2, value3);
Example: WHERE country_code IN ('US', 'CA', 'MX');

9. LIKE: Search for a specified pattern in a column (strings).

Wildcards:
%: Represents zero, one, or multiple characters.
_: Represents a single character.
Examples:
WHERE column_name LIKE 'A%'; (Starts with A)
WHERE column_name LIKE '%Z'; (Ends with Z)
WHERE column_name LIKE '%word%'; (Contains "word")
WHERE column_name LIKE 'J_ne'; (e.g., Jane, June)

--------------------------------------------------
III. Aggregate Functions (often used with GROUP BY)
--------------------------------------------------
10. COUNT(): Counts rows or non-NULL values.
Total Rows: SELECT COUNT(*) FROM table_name;
Non-NULL in Column: SELECT COUNT(column_name) FROM table_name;
Unique Non-NULL: SELECT COUNT(DISTINCT column_name) FROM table_name;

11. Other Aggregates:

SUM(column_name): Sum of values.
AVG(column_name): Average of values.
MIN(column_name): Minimum value.
MAX(column_name): Maximum value.
Example: SELECT AVG(price), MAX(price) FROM products;

--------------------------------------------------
IV. Grouping Data
--------------------------------------------------

12. GROUP BY: Groups rows with the same values into summary rows.
Syntax: SELECT column1, COUNT(*) FROM table_name GROUP BY column1;
Example: SELECT category, AVG(price) FROM products GROUP BY category;
Multiple Columns: SELECT country, city, COUNT(*) FROM customers GROUP BY
country, city;

13. HAVING: Filters groups created by GROUP BY.

Syntax: SELECT c1, COUNT(*) FROM table GROUP BY c1 HAVING COUNT(*) > 5;
Example: SELECT category, AVG(price) FROM products GROUP BY category HAVING
AVG(price) > 50.00;

--------------------------------------------------
V. String Functions
--------------------------------------------------

14. LENGTH(string): Returns the length of a string.

Example: SELECT LENGTH(product_name) FROM products;

15. LOWER(string) / UPPER(string): Converts string to lowercase/uppercase.

Example: SELECT LOWER(email), UPPER(first_name) FROM users;

16. LEFT(string, num_chars) / RIGHT(string, num_chars): Extracts characters.

Example: SELECT LEFT(zip_code, 5), RIGHT(phone_number, 4) FROM addresses;

17. Concatenation: Combines strings.

Standard (PostgreSQL, Oracle): first_name || ' ' || last_name
Function (MySQL, PostgreSQL, SQL Server): CONCAT(first_name, ' ', last_name)
Example: SELECT first_name || ' ' || last_name AS full_name FROM users;

18. POSITION(substring IN string): Returns starting position of substring (0 if not

found).
Example: SELECT POSITION('@' IN email) FROM users;

19. SUBSTRING(string FROM start FOR length) (Standard SQL, PostgreSQL)

SUBSTRING(string, start, length) (MySQL, SQL Server)
Extracts a substring.
Example: SELECT SUBSTRING(description FROM 1 FOR 50) AS short_desc FROM
articles;
Example: SELECT SUBSTRING(phone_number, 2, 3) FROM contacts;

--------------------------------------------------
VI. Date/Time Functions & Intervals
--------------------------------------------------
20. EXTRACT(field FROM source): Extracts parts from date/timestamp/interval.
Fields: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DOW (day of week), DOY (day of
year).
Example: SELECT EXTRACT(YEAR FROM order_date) AS order_year FROM orders;
Example: SELECT EXTRACT(MONTH FROM birth_date) FROM employees;

21. TO_CHAR(timestamp_or_interval, format): Converts timestamp/interval to string.

(PostgreSQL, Oracle. SQL Server: FORMAT(), MySQL: DATE_FORMAT())
Example: SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');
Example: SELECT TO_CHAR(order_date, 'Mon DD, YYYY') FROM orders;
Formats: YYYY, MM, DD, HH24, MI, SS.

22. Intervals: Represents a duration.

Examples:
SELECT NOW() + INTERVAL '1 day';
SELECT order_date + INTERVAL '7 hours';
SELECT delivery_date - INTERVAL '3 months';
SELECT age(timestamp1, timestamp2); (PostgreSQL: returns interval)
SELECT some_date_column - INTERVAL '5 year';

--------------------------------------------------
VII. Conditional Expressions
--------------------------------------------------

23. CASE WHEN: If-then-else logic.

Syntax:
SELECT column_name,
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE else_result
END AS new_column_name
FROM table_name;
Example:
SELECT order_total,
CASE
WHEN order_total > 1000 THEN 'High Value'
WHEN order_total > 500 THEN 'Medium Value'
ELSE 'Low Value'
END AS order_category
FROM orders;

24. CASE WHEN with Aggregates:

Example:
SELECT
SUM(CASE WHEN status = 'Completed' THEN amount ELSE 0 END) AS
total_completed_amount,
COUNT(CASE WHEN priority = 'High' THEN 1 END) AS high_priority_orders
FROM tasks;

--------------------------------------------------
VIII. Other Useful Concepts
--------------------------------------------------

25. Aliases: Rename columns or tables in results.

Column Alias: SELECT column_name AS new_name FROM table_name;
Table Alias: SELECT t1.colA, t2.colB FROM table_one t1 JOIN table_two t2 ON
t1.id = t2.t1_id;
 (AS is optional for table aliases)

26. Comments:
Single-line: -- This is a comment
Multi-line: /* This is a
multi-line comment */

27. Mathematical Operators:

+, -, *, / (Division), % (Modulo)
Example: SELECT price * 1.1 AS price_with_tax FROM products;

28. Mathematical Functions (examples, vary by SQL dialect):

ROUND(number, [decimals]): Rounds a number.
ABS(number): Absolute value.
CEIL(number) / CEILING(number): Smallest integer >= number.
FLOOR(number): Largest integer <= number.
POWER(base, exponent) / POW(base, exponent): base^exponent.
SQRT(number): Square root.
Example: SELECT ROUND(AVG(score), 2) FROM test_results;

--------------------------------------------------
Tips for Recollection
--------------------------------------------------
* Practice Regularly: Work on small challenges.
* Understand the "Why": Know why a command is used.
* Break Down Problems: Tackle complex queries in parts.
* Refer to Documentation: For specifics of your SQL database.
--------------------------------------------------