/*
Effective 3-4 Hour Database Revision Summary + SQL Basics & How To Practice
==========================================================================
**I. FAST DATABASE REVISION Summary**
- **Definition:** A *database* is a structured collection of related data, managed via a *DBMS*
(Database Management System).
- **Popular DBMS:** MySQL, PostgreSQL, Oracle, SQL Server.
- **Schema:** Defines tables, columns, data types, and relationships.
- **Levels of abstraction:**
- Physical (storage)
- Logical (tables, schema)
- View (user perspective)
- **Keys:**
- Primary Key (unique, not null)
- Foreign Key (links to primary in another table)
- **Constraints:** Uniqueness, NOT NULL, CHECK, DEFAULT, Referential integrity.
- **Normalization:** Organizes data to reduce redundancy (1NF, 2NF, 3NF).
- **ER Model:** Diagram of entities (object types), attributes, and relationships.
- **Data Independence:** Change storage/schema without affecting applications.
**II. SQL BASICS**
- **SQL (Structured Query Language):** Standard language for relational databases.
**Core SQL Commands:**
- *CREATE TABLE*: Define table structure.
- `CREATE TABLE students (id INT PRIMARY KEY, name VARCHAR(30), age INT);`
- *INSERT*: Add new records.
- `INSERT INTO students (id, name, age) VALUES (1, 'Anna', 21);`
- *SELECT*: Retrieve data.
- `SELECT name, age FROM students WHERE age > 18;`
- *UPDATE*: Modify data.
- `UPDATE students SET age = 22 WHERE id = 1;`
- *DELETE*: Remove data.
- `DELETE FROM students WHERE id = 1;`
- *ALTER TABLE*: Change schema (add/drop column).
- `ALTER TABLE students ADD COLUMN email VARCHAR(50);`
- *DROP TABLE*: Remove table completely.
- `DROP TABLE students;`
- **Constraints:** Enforce rules, e.g., `UNIQUE`, `NOT NULL`, `CHECK (age > 0)`.
**Advanced SQL:**
- *Joins*: Combine data from multiple tables
- `SELECT * FROM students INNER JOIN enrollments ON [Link] = enrollments.student_id;`
- Types: INNER, LEFT, RIGHT, FULL JOIN
- *Group By & Aggregates*:
- `SELECT age, COUNT(*) FROM students GROUP BY age;`
- *Subquery*: Query in a query.
- `SELECT name FROM students WHERE id IN (SELECT student_id FROM enrollments);`
- *Views*: Save queries as virtual tables.
- `CREATE VIEW adult_students AS SELECT * FROM students WHERE age >= 18;`
- *Indexes*: Speed up lookups.
- `CREATE INDEX idx_age ON students (age);`
- *Transactions*: Make multiple changes atomic (all or nothing).
- `BEGIN; ... COMMIT;` or `ROLLBACK;`
**III. HOW TO PRACTICE SQL**
1. **Install a Local DBMS:** MySQL, PostgreSQL, SQLite (easy for beginners).
2. **Online Practice Platforms:**
- LeetCode (Database section)
- SQLZoo, Hackerrank, Mode Analytics, W3Schools SQL Tryit
3. **Steps:**
- Create a database and tables.
- Populate tables with sample data.
- Practice SELECTs with WHERE, JOINs, GROUP BY, ORDER BY.
- Update and delete data, try different constraints.
- Write increasingly complex multi-table queries.
- Experiment with errors and fixing them.
4. **Tips:**
- Start small, build up to more complex queries.
- Read real-world example schemas (Northwind, Sakila).
- Take notes on common mistakes and resolutions.
- Use comments (`-- this is a comment`) in SQL scripts.
**Key SQL Practice Resources:**
- [Link]
- [Link]
- [Link]
- [Link]
**In 3-4 hours:**
- Revise schema design and normalization.
- Practice core SQL operations, writing and reading queries.
- Try basic SELECTs, JOINs, subqueries, GROUP BY, and constraints.
- Read example exam questions to test conceptual understanding.
Good luck!
*/