Queries

SELECT Statement

Basic SELECT

SELECT column1, column2 FROM $TABEL_NAME;

Select All Columns

SELECT * FROM $TABEL_NAME;

Select with Alias

SELECT column1 AS alias_name FROM $TABEL_NAME;

Select Distinct Values

SELECT DISTINCT column1 FROM $TABEL_NAME;

Select with Calculations

SELECT column1, column2 * 2 AS calculated_column FROM $TABEL_NAME;

---

Filtering

Basic WHERE Clause

SELECT * FROM $TABEL_NAME WHERE condition;

Comparison Operators

SELECT * FROM $TABEL_NAME WHERE column1 = 'value';SELECT * FROM $TABEL_NAME WHERE column1 != 'value';
SELECT * FROM $TABEL_NAME WHERE column1 > 100;
SELECT * FROM $TABEL_NAME WHERE column1 >= 100;
SELECT * FROM $TABEL_NAME WHERE column1 < 100;
SELECT * FROM table_name WHERE column1 <= 100;

Logical Operators

SELECT * FROM $TABEL_NAME WHERE condition1 AND condition2;
SELECT * FROM $TABEL_NAME WHERE condition1 OR condition2;
SELECT * FROM $TABEL_NAME WHERE NOT condition;

LIKE Pattern Matching

SELECT * FROM $TABEL_NAME WHERE column1 LIKE 'pattern%';
SELECT * FROM $TABEL_NAME WHERE column1 LIKE '%pattern';
SELECT * FROM $TABEL_NAME WHERE column1 LIKE '%pattern%';
SELECT * FROM $TABEL_NAME WHERE column1 LIKE '_pattern';

IN Operator

SELECT * FROM $TABEL_NAME WHERE column1 IN ('value1', 'value2', 'value3');

BETWEEN Operator

SELECT * FROM $TABEL_NAME WHERE column1 BETWEEN value1 AND value2;

NULL Values

SELECT * FROM $TABEL_NAME WHERE column1 IS NULL;
SELECT * FROM $TABEL_NAME WHERE column1 IS NOT NULL;

---

Sorting & Limiting

ORDER BY Ascending

SELECT * FROM $TABEL_NAME ORDER BY column1;
SELECT * FROM $TABEL_NAME ORDER BY column1 ASC;

ORDER BY Descending

SELECT * FROM $TABEL_NAME ORDER BY column1 DESC;

Multiple Column Sorting

SELECT * FROM $TABEL_NAME ORDER BY column1, column2 DESC;

Sort by Column Position

SELECT column1, column2 FROM $TABEL_NAME ORDER BY 2 DESC;

LIMIT Results

SELECT * FROM $TABEL_NAME LIMIT 10;

LIMIT with OFFSET

SELECT * FROM $TABEL_NAME LIMIT 10 OFFSET 20;
SELECT * FROM $TABEL_NAME LIMIT 20, 10;

Top N Results

SELECT * FROM $TABEL_NAME ORDER BY column1 DESC LIMIT 5;

---

Aggregate Functions

COUNT Function

SELECT COUNT(*) FROM $TABEL_NAME;
SELECT COUNT(column1) FROM $TABEL_NAME;
SELECT COUNT(DISTINCT column1) FROM $TABEL_NAME;

SUM Function

SELECT SUM(column1) FROM $TABEL_NAME;

AVG Function

SELECT AVG(column1) FROM $TABEL_NAME;

MIN and MAX Functions

SELECT MIN(column1) FROM $TABEL_NAME;
SELECT MAX(column1) FROM $TABEL_NAME;

Multiple Aggregates

SELECT COUNT(*), SUM(column1), AVG(column1), MIN(column1), MAX(column1) FROM $TABEL_NAME;

String Functions

SELECT CONCAT(column1, column2) FROM table_name;
SELECT LENGTH(column1) FROM $TABEL_NAME;
SELECT UPPER(column1) FROM $TABEL_NAME;
SELECT LOWER(column1) FROM $TABEL_NAME;
SELECT SUBSTRING(column1, 1, 3) FROM $TABEL_NAME;

Date Functions

SELECT NOW();
SELECT DATE(column1) FROM $TABEL_NAME;
SELECT YEAR(column1) FROM $TABEL_NAME;
SELECT MONTH(column1) FROM $TABEL_NAME;
SELECT DAY(column1) FROM $TABEL_NAME;
SELECT DATEDIFF(NOW(), column1) FROM $TABEL_NAME;

---

GROUP BY and HAVING

Basic GROUP BY

SELECT column1, COUNT(*) FROM $TABEL_NAME GROUP BY column1;

GROUP BY Multiple Columns

SELECT column1, column2, COUNT(*) FROM $TABEL_NAME GROUP BY column1, column2;

GROUP BY with Aggregates

SELECT column1, COUNT(*), SUM(column2), AVG(column2) FROM $TABEL_NAME GROUP BY column1;

HAVING Clause

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

HAVING with Multiple Conditions

SELECT column1, AVG(column2) FROM $TABEL_NAME GROUP BY column1 HAVING AVG(column2) > 100 AND COUNT(*) > 3;

WHERE vs HAVING

SELECT column1, COUNT(*) FROM $TABEL_NAME WHERE column2 > 50 GROUP BY column1 HAVING COUNT(*) > 2;

---

Joins

INNER JOIN

SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.table1_id;

LEFT JOIN

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.table1_id;

RIGHT JOIN

SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.table1_id;

FULL OUTER JOIN (MySQL Alternative)

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.table1_idUNIONSELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.table1_id;

CROSS JOIN

SELECT * FROM table1 CROSS JOIN table2;

SELF JOIN

SELECT t1.column1, t2.column1 FROM table1 t1 INNER JOIN table1 t2 ON t1.manager_id = t2.id;

Multiple Table JOIN

SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.table1_idINNER JOIN table3 ON table2.id = table3.table2_id;

JOIN with WHERE

SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.table1_id WHERE table1.column1 = 'value';

JOIN with GROUP BY

SELECT table1.column1, COUNT(*) FROM table1 INNER JOIN table2 ON table1.id = table2.table1_id GROUP BY table1.column1;