SQL Guide: Beginner to Master
1. Introduction
SQL (Structured Query Language) is used for interacting with relational databases.
It allows data querying, insertion, updating, and deletion.
Example:
SELECT * FROM employees;
SQL Guide: Beginner to Master
2. Beginner Level
1. SELECT - Retrieve data from a table.
Syntax: SELECT column1, column2 FROM table_name;
Example:
SELECT name, age FROM customers;
2. WHERE - Filter records.
Syntax: SELECT * FROM table WHERE condition;
Example:
SELECT * FROM customers WHERE city = 'New York';
3. AND, OR, NOT - Combine conditions.
Example:
SELECT * FROM products WHERE price > 100 AND stock < 50;
4. ORDER BY - Sort results.
Example:
SELECT * FROM employees ORDER BY age DESC;
5. LIMIT - Limit number of rows returned.
Example:
SELECT * FROM employees LIMIT 10;
SQL Guide: Beginner to Master
3. Intermediate Level
1. INSERT - Add data into a table.
Syntax: INSERT INTO table_name (column1, column2) VALUES (value1, value2);
Example:
INSERT INTO customers (name, city) VALUES ('Alice', 'Chicago');
2. UPDATE - Modify existing data.
Syntax: UPDATE table_name SET column1 = value1 WHERE condition;
Example:
UPDATE customers SET city = 'Boston' WHERE name = 'Alice';
3. DELETE - Remove data.
Syntax: DELETE FROM table_name WHERE condition;
Example:
DELETE FROM customers WHERE name = 'Alice';
4. LIKE, IN, BETWEEN
Example:
SELECT * FROM customers WHERE name LIKE 'A%';
SELECT * FROM orders WHERE status IN ('Pending', 'Shipped');
SELECT * FROM orders WHERE date BETWEEN '2024-01-01' AND '2024-12-31';
SQL Guide: Beginner to Master
5. CREATE TABLE
Example:
CREATE TABLE employees (
id INT PRIMARY KEY,
name TEXT,
department TEXT
);
6. JOINs
Example (INNER JOIN):
SELECT [Link], [Link]
FROM employees
INNER JOIN departments ON employees.dept_id = [Link];
SQL Guide: Beginner to Master
4. Advanced Level
1. GROUP BY and HAVING
Example:
SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5;
2. Subqueries
Example:
SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
3. Views
Example:
CREATE VIEW high_earners AS SELECT * FROM employees WHERE salary > 70000;
4. Indexes
Example:
CREATE INDEX idx_name ON employees(name);
5. Transactions
Example:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
SQL Guide: Beginner to Master
5. Master Level
1. Window Functions
Example:
SELECT name, department, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS
rank
FROM employees;
2. CTE (Common Table Expressions)
Example:
WITH TopSellers AS (
SELECT employee_id, SUM(amount) AS total_sales
FROM sales
GROUP BY employee_id
SELECT * FROM TopSellers WHERE total_sales > 10000;
3. Performance Tips:
- Use EXPLAIN to understand query execution
- Avoid SELECT * in production
- Use proper indexing
4. Real-World Use:
- PostgreSQL for enterprise-level analytics
- MySQL for web applications
SQL Guide: Beginner to Master
- SQLite for mobile apps and lightweight projects
SQL Guide: Beginner to Master
6. Practice & Resources
Practice Tips:
- Start with small queries, build confidence.
- Use platforms like SQLZoo, LeetCode, W3Schools.
- Build a sample database for a store or school and run test queries.
Mini-Project Idea:
CREATE TABLE students (
id INT PRIMARY KEY,
name TEXT,
grade INT,
subject TEXT
);
-- Get students who scored more than 85 in Math
SELECT name FROM students WHERE grade > 85 AND subject = 'Math';