📘 ALL SQL QUERIES FROM BASIC TO ADVANCED (TOPIC-WISE)
━━━━━━━━━━━━━━━━━━
🔹 1. DATA RETRIEVAL (SELECT Queries)
━━━━━━━━━━━━━━━━━━
1. SELECT
Retrieves all columns and rows.
SELECT * FROM employees;
2. SELECT Specific Columns
SELECT name, salary FROM employees;
3. SELECT DISTINCT
Retrieves unique values.
SELECT DISTINCT department FROM employees;
4. WHERE
Filter records based on a condition.
SELECT * FROM employees WHERE salary > 50000;
5. AND / OR / NOT
Combine multiple conditions.
SELECT * FROM employees WHERE salary > 50000 AND department = 'HR';
6. ORDER BY
Sort results.
SELECT * FROM employees ORDER BY salary DESC;
7. LIMIT / OFFSET
Limit number of rows.
SELECT * FROM employees LIMIT 5 OFFSET 10;
━━━━━━━━━━━━━━━━━━
🔹 2. FILTERING & PATTERN MATCHING
━━━━━━━━━━━━━━━━━━
8. IN
Matches values in a list.
SELECT * FROM employees WHERE department IN ('HR', 'IT');
9. BETWEEN
Matches values within a range.
SELECT * FROM employees WHERE salary BETWEEN 40000 AND 80000;
10. LIKE
Pattern match using % or _.
SELECT * FROM employees WHERE name LIKE 'A%';
11. IS NULL / IS NOT NULL
Check for missing values.
SELECT * FROM employees WHERE manager_id IS NULL;
━━━━━━━━━━━━━━━━━━
🔹 3. DATA MANIPULATION (DML)
━━━━━━━━━━━━━━━━━━
12. INSERT INTO
Add new records.
INSERT INTO employees (name, salary) VALUES ('John', 60000);
13. UPDATE
Modify existing records.
UPDATE employees SET salary = salary + 1000 WHERE id = 1;
14. DELETE
Delete records.
DELETE FROM employees WHERE id = 5;
━━━━━━━━━━━━━━━━━━
🔹 4. TABLE STRUCTURE (DDL)
━━━━━━━━━━━━━━━━━━
15. CREATE TABLE
Create new table.
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);
16. ALTER TABLE
Modify table structure.
ALTER TABLE employees ADD COLUMN age INT;
17. DROP TABLE
Delete entire table.
DROP TABLE employees;
18. TRUNCATE
Remove all rows but keep table structure.
TRUNCATE TABLE employees;
━━━━━━━━━━━━━━━━━━
🔹 5. JOINS (Combining Tables)
━━━━━━━━━━━━━━━━━━
19. INNER JOIN
Only matching records from both tables.
SELECT [Link], [Link]
FROM emp a
INNER JOIN salary b ON [Link] = b.emp_id;
20. LEFT JOIN
All records from left + matching from right.
SELECT * FROM a LEFT JOIN b ON [Link] = b.a_id;
21. RIGHT JOIN
All records from right + matching from left.
SELECT * FROM a RIGHT JOIN b ON [Link] = b.a_id;
22. FULL OUTER JOIN
All records from both tables.
SELECT * FROM a FULL JOIN b ON [Link] = b.a_id;
━━━━━━━━━━━━━━━━━━
🔹 6. GROUPING & AGGREGATES
━━━━━━━━━━━━━━━━━━
23. GROUP BY
Group similar rows.
SELECT department, COUNT(*) FROM employees GROUP BY department;
24. HAVING
Filter after grouping.
SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) >
2;
25. AGGREGATE FUNCTIONS
Perform summary operations.
SELECT COUNT(*), AVG(salary), MIN(salary), MAX(salary) FROM employees;
━━━━━━━━━━━━━━━━━━
🔹 7. ADVANCED SELECT LOGIC
━━━━━━━━━━━━━━━━━━
26. ALIAS (AS)
Rename column or table.
SELECT name AS employee_name FROM employees;
27. CASE
If-Else Logic.
SELECT name,
CASE
WHEN salary > 50000 THEN 'High'
ELSE 'Low'
END AS status
FROM employees;
28. SUBQUERY
Query inside another query.
SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
29. EXISTS
Check if subquery returns records.
SELECT name FROM employees
WHERE EXISTS (SELECT 1 FROM departments WHERE [Link] = employees.dept_id);
━━━━━━━━━━━━━━━━━━
🔹 8. VIEWS, INDEXES, TRANSACTIONS
━━━━━━━━━━━━━━━━━━
30. CREATE VIEW
Create a virtual table.
CREATE VIEW high_earners AS
SELECT * FROM employees WHERE salary > 70000;
31. CREATE INDEX
Speed up queries.
CREATE INDEX idx_name ON employees(name);
32. TRANSACTION
Group operations safely.
BEGIN;
UPDATE employees SET salary = salary + 500;
COMMIT;
33. COMMIT / ROLLBACK
Save or cancel changes.
COMMIT;
ROLLBACK;
━━━━━━━━━━━━━━━━━━
🔹 9. DATABASE OPERATIONS
━━━━━━━━━━━━━━━━━━
34. CREATE DATABASE
Create new database.
CREATE DATABASE company;
35. DROP DATABASE
Delete a database.
DROP DATABASE company;
36. RENAME DATABASE
Rename an existing database.
ALTER DATABASE oldname RENAME TO newname;
━━━━━━━━━━━━━━━━━━
🔹 10. FUNCTIONS
━━━━━━━━━━━━━━━━━━
37. STRING FUNCTIONS
Operate on text values.
SELECT UPPER(name), LENGTH(name) FROM employees;
38. DATE FUNCTIONS
Work with dates.
SELECT CURRENT_DATE, EXTRACT(YEAR FROM CURRENT_DATE);
━━━━━━━━━━━━━━━━━━
🔹 11. JSON & WINDOW FUNCTIONS (PostgreSQL)
━━━━━━━━━━━━━━━━━━
39. JSON FUNCTIONS
Work with JSON data.
SELECT data->'name', data->>'name' FROM json_table;
40. WINDOW FUNCTIONS
Advanced row-wise calculations.
SELECT name,
RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;