1.
Basic SELECT Queries
1. Select all columns from a table:
SELECT * FROM table_name;
2. Select specific columns:
SELECT column1, column2 FROM table_name;
3. Select distinct values:
SELECT DISTINCT column1 FROM table_name;
4. Select rows with a specific condition:
SELECT * FROM table_name WHERE condition;
5. Select rows with multiple conditions:
SELECT * FROM table_name WHERE condition1 AND condition2;
Sorting Data
6. Order results by one column:
SELECT * FROM table_name ORDER BY column1 ASC;
7. Order results by multiple columns:
SELECT * FROM table_name ORDER BY column1 ASC, column2 DESC;
Aggregating Data
8. Count total records:
SELECT COUNT(*) FROM table_name;
9. Count records with a condition:
SELECT COUNT(*) FROM table_name WHERE condition;
10. Get the average of a column:
SELECT AVG(column_name) FROM table_name;
11. Get the maximum value in a column:
SELECT MAX(column_name) FROM table_name;
12. Get the minimum value in a column:
SELECT MIN(column_name) FROM table_name;
13. Sum the values in a column:
SELECT SUM(column_name) FROM table_name;
Grouping Data
14. Group by a single column:
SELECT column1, COUNT(*) FROM table_name GROUP BY column1;
15. Group by multiple columns:
SELECT column1, column2, COUNT(*) FROM table_name GROUP BY column1, column2;
16. Group by and filter groups:
SELECT column1, COUNT(*) FROM table_name GROUP BY column1 HAVING COUNT(*) > value;
Joining Tables
17. Inner join two tables:
SELECT a.column1, b.column2 FROM table1 a JOIN table2 b ON a.common_column =
b.common_column;
18. Left join two tables:
SELECT a.column1, b.column2 FROM table1 a LEFT JOIN table2 b ON a.common_column =
b.common_column;
19. Right join two tables:
SELECT a.column1, b.column2 FROM table1 a RIGHT JOIN table2 b ON a.common_column =
b.common_column;
20. Full outer join two tables:
SELECT a.column1, b.column2 FROM table1 a FULL OUTER JOIN table2 b ON a.common_column =
b.common_column;
Inserting Data
21. Insert a single row:
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
22. Insert multiple rows:
INSERT INTO table_name (column1, column2) VALUES (value1a, value2a), (value1b, value2b);
Updating Data
23. Update a single column:
UPDATE table_name SET column1 = value1 WHERE condition;
24. Update multiple columns:
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
8. Deleting Data
25. Delete specific rows:
DELETE FROM table_name WHERE condition;
26. Delete all rows (use with caution):
DELETE FROM table_name;
Working with NULLs
27. Select rows with NULL values:
SELECT * FROM table_name WHERE column_name IS NULL;
28. Select rows where a column is NOT NULL:
SELECT * FROM table_name WHERE column_name IS NOT NULL;
Using Aliases
29. Using aliases for column names:
SELECT column1 AS alias_name FROM table_name;
30. Using aliases for table names:
SELECT a.column1 FROM table_name AS a;
Subqueries
31. Subquery in SELECT statement:
SELECT column1, (SELECT column2 FROM table2 WHERE condition) AS alias_name FROM table1;
32. Subquery in WHERE statement:
SELECT * FROM table_name WHERE column1 IN (SELECT column1 FROM table2 WHERE condition);
12. Set Operations
33. UNION of two SELECT statements:
SELECT column1 FROM table1 UNION SELECT column1 FROM table2;
34. UNION ALL (includes duplicates):
SELECT column1 FROM table1 UNION ALL SELECT column1 FROM table2;
Conditional Logic
35. Using CASE in SELECT statement:
SELECT column1,
CASE WHEN condition THEN result1
WHEN condition THEN result2
ELSE result3 END AS alias_name
FROM table_name;
Limiting Results
36. Limit the number of rows returned:
SELECT * FROM table_name LIMIT n;
37. Offset rows in results:
SELECT * FROM table_name LIMIT n OFFSET m;
Date and Time Functions
38. Current date:
SELECT CURRENT_DATE;
39. Current time:
SELECT CURRENT_TIME;
40. Difference between two dates:
SELECT DATEDIFF(date1, date2);
String Functions
41. Concatenating strings:
SELECT CONCAT(column1, ' ', column2) FROM table_name;
42. Length of a string:
SELECT LENGTH(column_name) FROM table_name;
Using Indexes
43. Create an index:
CREATE INDEX index_name ON table_name (column_name);
Views
44. Create a view:
CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;
Managing Transactions
45. Begin a transaction:
BEGIN;
46. Commit a transaction:
COMMIT;
47. Rollback a transaction:
ROLLBACK;
Other Operations
48. Check the structure of a table:
DESCRIBE table_name;
49. Show all databases:
SHOW DATABASES;
50. Select a database to use:
USE database_name;