SQL (Structured Query Language) is language for managing relational databases. Used for CRUD operations, querying, updating, deleting data. Standard language across databases.

Organized collection of structured data stored in tables. Accessed/managed using database management systems (DBMS). Example: MySQL, PostgreSQL, SQL Server, Oracle.

Collection of rows and columns. Rows represent records, columns represent attributes. Example: Users table with id, name, email columns.

Unique identifier for each row. Cannot be NULL, must be unique. Example: id INT PRIMARY KEY

Reference to primary key in another table. Maintains referential integrity. Example: FOREIGN KEY (user_id) REFERENCES Users(id)

Defines type of data column can hold: INT, VARCHAR, DATE, BOOLEAN, DECIMAL. Ensure data integrity.

Retrieves data from table. Basic syntax: SELECT column_name FROM table_name WHERE condition;

Filters rows based on condition: SELECT * FROM Users WHERE age > 18; Can use operators: =, !=, <, >, <=, >=, LIKE, IN, BETWEEN.

Adds new rows to table: INSERT INTO Users (name, email) VALUES ('John', 'john@email.com');

Modifies existing rows: UPDATE Users SET email='new@email.com' WHERE id=1;

Removes rows from table: DELETE FROM Users WHERE id=1; Use WHERE to avoid deleting all data.

Removes duplicates from results: SELECT DISTINCT city FROM Users;

Sorts results: SELECT * FROM Users ORDER BY name ASC; (ASC ascending, DESC descending)

Restricts number of rows returned: SELECT * FROM Users LIMIT 10; Returns first 10 rows.

Skips rows: SELECT * FROM Users LIMIT 10 OFFSET 5; Skips first 5, returns next 10.

COUNT(), SUM(), AVG(), MIN(), MAX(). Example: SELECT COUNT(*) FROM Users;

Groups rows by column: SELECT city, COUNT(*) FROM Users GROUP BY city; Shows count per city.

Filters groups: SELECT city, COUNT(*) FROM Users GROUP BY city HAVING COUNT(*) > 5;

Combine rows from multiple tables. Types: INNER, LEFT, RIGHT, FULL. Example: SELECT * FROM Users INNER JOIN Orders ON Users.id = Orders.user_id;

Returns matching rows from both tables: SELECT * FROM Users INNER JOIN Orders ON Users.id = Orders.user_id;

Returns all rows from left table + matching from right: SELECT * FROM Users LEFT JOIN Orders ON Users.id = Orders.user_id;

Returns all rows from right table + matching from left: SELECT * FROM Users RIGHT JOIN Orders ON Users.id = Orders.user_id;

Returns all rows from both tables: SELECT * FROM Users FULL OUTER JOIN Orders ON Users.id = Orders.user_id;

Cartesian product of two tables. Returns all combinations: SELECT * FROM Users CROSS JOIN Orders;

Query within query: SELECT * FROM Users WHERE id IN (SELECT user_id FROM Orders);

Pattern matching: SELECT * FROM Users WHERE name LIKE 'J%'; (% wildcard for any chars, _ for single char)

Check membership: SELECT * FROM Users WHERE city IN ('NYC', 'LA', 'SF');

Range check: SELECT * FROM Users WHERE age BETWEEN 20 AND 30;

Unknown or missing data. Use IS NULL or IS NOT NULL: SELECT * FROM Users WHERE phone IS NULL;

CONCAT(), SUBSTRING(), LENGTH(), UPPER(), LOWER(), TRIM(). Example: SELECT UPPER(name) FROM Users;

ABS(), ROUND(), CEIL(), FLOOR(), POWER(). Example: SELECT ROUND(price, 2) FROM Products;

NOW(), DATE(), EXTRACT(), DATE_ADD(), DATE_DIFF(). Example: SELECT NOW() FROM dual;

Modify table structure: ALTER TABLE Users ADD COLUMN phone VARCHAR(10);

Create new table: CREATE TABLE Users (id INT PRIMARY KEY, name VARCHAR, email VARCHAR(100));

Delete entire table: DROP TABLE Users; Removes table and data.

Rules for data: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT.

Column must have value: CREATE TABLE Users (name VARCHAR NOT NULL);

All values unique in column: CREATE TABLE Users (email VARCHAR(100) UNIQUE);

Default value if not specified: CREATE TABLE Users (status VARCHAR(10) DEFAULT 'active');

Validates data: CREATE TABLE Users (age INT CHECK (age >= 18));

Speeds up queries: CREATE INDEX idx_name ON Users(name); Slows down INSERT/UPDATE but speeds SELECT.

Atomicity (all or nothing), Consistency (valid state), Isolation (independent), Durability (persistent).

COMMIT saves changes. ROLLBACK undoes changes. Example: START TRANSACTION; ... COMMIT; or ROLLBACK;

Combine results from two queries: SELECT name FROM Users UNION SELECT name FROM Customers;

UNION with duplicates: SELECT name FROM Users UNION ALL SELECT name FROM Customers;

Returns rows from first query not in second: SELECT id FROM Users EXCEPT SELECT id FROM BannedUsers;

Returns rows in both queries: SELECT id FROM Users INTERSECT SELECT id FROM Subscribers;

Join table with itself: SELECT a.name, b.name FROM Users a, Users b WHERE a.manager_id = b.id;

Create index for faster queries: CREATE INDEX idx_email ON Users(email);

Shows query execution plan. Example: EXPLAIN SELECT * FROM Users WHERE id = 1; Helps optimize queries.

Aggregate over partitions without grouping: SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) as rank FROM Users;

Divide rows into groups: SELECT dept, name, AVG(salary) OVER (PARTITION BY dept) FROM Users;

ROW_NUMBER(), RANK(), DENSE_RANK(). ROW_NUMBER gives unique, RANK/DENSE_RANK handle ties.

Temporary named result set: WITH temp AS (SELECT * FROM Users WHERE active=1) SELECT * FROM temp;

CTE referencing itself. Useful for hierarchical data: WITH RECURSIVE temp AS (...) SELECT * FROM temp;

Virtual table from query: CREATE VIEW ActiveUsers AS SELECT * FROM Users WHERE active=1; SELECT * FROM ActiveUsers;

Reusable SQL code: CREATE PROCEDURE GetUsers() BEGIN SELECT * FROM Users; END; CALL GetUsers();

Automatic code on table events: CREATE TRIGGER update_timestamp BEFORE UPDATE ON Users FOR EACH ROW SET NEW.updated_at = NOW();

Organize data to reduce redundancy. Normal forms: 1NF, 2NF, 3NF, BCNF. Improve data integrity.

Atomic values only (no repeating groups). Each column has single value. Example: no arrays in cells.

1NF + all non-key columns depend on entire primary key. No partial dependencies.

2NF + no transitive dependencies. Non-key columns depend only on primary key.

Combine tables for performance. Trade normalized design for query speed. Use carefully.

Subquery returning single value: SELECT name, (SELECT COUNT(*) FROM Orders WHERE user_id = Users.id) FROM Users;

Subquery referencing outer query: SELECT * FROM Users u WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.user_id = u.id);

Subquery in FROM clause: SELECT * FROM (SELECT * FROM Users WHERE active=1) t WHERE t.age > 18;

Conditional logic: SELECT name, CASE WHEN age < 18 THEN 'Minor' ELSE 'Adult' END as status FROM Users;

Returns first non-NULL value: SELECT COALESCE(phone, email, 'No contact') FROM Users;

Replace NULL with value: SELECT IFNULL(phone, 'N/A') FROM Users; (MySQL/PostgreSQL: COALESCE)

UNION, INTERSECT, EXCEPT. Combine/compare results from multiple queries.

Remove all rows from table (faster than DELETE): TRUNCATE TABLE Users; Cannot rollback in some DBs.

Create custom functions: CREATE FUNCTION calcAge(birthDate) RETURNS INT BEGIN RETURN YEAR(NOW()) - YEAR(birthDate); END;

CONCAT() function or + operator. Example: CONCAT(first_name, ' ', last_name) or first_name + ' ' + last_name

Store/query JSON: JSON_EXTRACT(), JSON_CONTAINS(). Example: SELECT JSON_EXTRACT(data, '$.name') FROM Users;

Search text content efficiently: SELECT * FROM Articles WHERE MATCH(content) AGAINST('keyword');

Rename column in result: SELECT name AS user_name FROM Users;

Rename table in query: SELECT u.name FROM Users u WHERE u.id = 1;

Improve query performance: use indexes, avoid SELECT *, optimize joins, use LIMIT. Analyze execution plans.

Fetching parent, then child for each parent (inefficient). Solve with joins or batch queries.

Prevent concurrent modifications. Types: row lock, table lock. Can cause deadlocks.

Two transactions waiting on each other. Detect/resolve with SHOW ENGINE INNODB STATUS. Use timeouts.

Backup data: DUMP, BACKUP commands. Restore: RESTORE. Ensure data safety.

Copy data to another server. Master-slave setup. Improves availability and load distribution.

Prevent SQL injection: SELECT * FROM Users WHERE id = ?; Use placeholders instead of concatenation.

Malicious SQL in user input. Example: input = "' OR '1'='1"; Prevent with parameterized queries.

Control permissions: GRANT SELECT ON Users TO user; REVOKE SELECT ON Users FROM user;

Organize tables into groups: CREATE SCHEMA myschema; CREATE TABLE myschema.Users (...);

Generate unique numbers: CREATE SEQUENCE id_seq; Use in INSERT for auto-increment.

Horizontal partitioning across servers. Each shard holds subset of data. Improves scalability.

Computed columns not stored physically: CREATE TABLE Users (age INT, birth_year INT, VIRTUAL year_age AS (YEAR(NOW()) - birth_year));

Sorting/comparison rules: DEFAULT COLLATE utf8_general_ci; Affects case sensitivity.

Validate column values: ALTER TABLE Users ADD CONSTRAINT chk_age CHECK (age >= 18);

Delete child records when parent deleted: FOREIGN KEY (parent_id) REFERENCES Parent(id) ON DELETE CASCADE;

BEFORE: execute before operation. AFTER: execute after operation. Example: BEFORE UPDATE, AFTER INSERT.

Store query results in memory for speed. Some DBs have native caching. Also application-level caching.

Analyze EXPLAIN output. Identify slow queries, sequential scans, missing indexes. Optimize accordingly.

Tables that exist during session: CREATE TEMPORARY TABLE temp (...); Automatically dropped.

Multiple servers acting as one database. Improves availability and load balancing.

Reuse database connections. Improves performance by reducing connection overhead.

Version-controlled schema changes. Tools: Flyway, Liquibase. Track all changes, enable rollback.

101-150. Advanced Topics (abbreviated)

101-110: Advanced window functions (LAG, LEAD, FIRST_VALUE, LAST_VALUE), pivot tables, XML/JSON operations, custom aggregates. 111-120: Query tuning, index strategies (B-tree, Hash, Full-text), statistics and cost estimation, parallel query execution. 121-130: Distributed SQL, ACID/MVCC, transaction isolation levels (READ COMMITTED, REPEATABLE READ, SERIALIZABLE), consistency models. 131-140: Advanced triggers and stored procedures, event handling, database automation, resource management. 141-150: NoSQL vs SQL, polyglot persistence, cloud databases (AWS RDS, Azure SQL, Google Cloud SQL), blockchain databases, time-series databases.