Database

SQL databases are relational, use structured schemas with tables, and support ACID transactions (e.g., PostgreSQL, MySQL). NoSQL databases are non-relational, offer flexible schemas, and are designed for horizontal scaling (e.g., MongoDB, Cassandra). SQL is better for structured data with complex relationships; NoSQL is better for unstructured data and high-volume applications.

A database is an organised collection of data stored so it can be easily accessed, managed and updated. A DBMS (Database Management System) is the software used to create, query and manage databases, handling storage, security, concurrency and backups. Examples: MySQL, PostgreSQL, Oracle, SQL Server (relational) and MongoDB (NoSQL).

A primary key is a column (or set of columns) that uniquely identifies each row in a table. It must be unique and cannot be NULL, and each table can have only one primary key. Example: student_id in a students table.

A foreign key is a column in one table that refers to the primary key of another table, creating a relationship between them and enforcing referential integrity. Example: orders.customer_id referencing customers.customer_id ensures every order belongs to an existing customer.

• DELETE removes selected rows (with WHERE) or all rows; it can be rolled back and fires triggers. • TRUNCATE removes all rows quickly, keeps the table structure, and usually resets identity counters. • DROP deletes the entire table including its structure, data and indexes.

WHERE filters individual rows before grouping and cannot use aggregate functions. HAVING filters groups after GROUP BY and can use aggregates. Example: SELECT dept, AVG(salary) FROM employees WHERE status = 'active' GROUP BY dept HAVING AVG(salary) > 50000;

• INNER JOIN: only rows with matching values in both tables. • LEFT JOIN: all rows from the left table plus matches from the right (NULL where none). • RIGHT JOIN: all rows from the right table plus matches from the left. • FULL OUTER JOIN: all rows from both tables, matched where possible. • CROSS JOIN: every combination of rows (Cartesian product). • SELF JOIN: a table joined with itself, e.g., employees and their managers.

Normalization organises tables to reduce redundancy and avoid update anomalies. • 1NF: each column holds atomic (single) values and there are no repeating groups. • 2NF: in 1NF and every non-key column depends on the whole primary key (no partial dependency on part of a composite key). • 3NF: in 2NF and non-key columns depend only on the key, not on other non-key columns (no transitive dependency).

Denormalization intentionally adds redundancy — for example, storing a customer's name in the orders table — to reduce joins and speed up reads. It is common in data warehouses, reporting and read-heavy applications, at the cost of extra storage and more complex updates to keep data consistent.

ACID guarantees reliable transactions: • Atomicity: all operations in a transaction succeed or none do. • Consistency: a transaction moves the database from one valid state to another, respecting constraints. • Isolation: concurrent transactions don't interfere with each other. • Durability: once committed, changes survive crashes.

An index is a data structure (commonly a B-tree) that lets the database find rows quickly without scanning the whole table, like an index in a book. It speeds up WHERE filters, JOINs and ORDER BY on indexed columns. Trade-offs: indexes use extra storage and slow down INSERT/UPDATE/DELETE because they must be maintained.

A clustered index determines the physical order of data in the table, so a table can have only one (often on the primary key). A non-clustered index is a separate structure with pointers to the data rows; a table can have many. Clustered indexes are fast for range queries; non-clustered indexes are useful for frequently filtered columns.

Both combine the results of two SELECT queries with the same number and type of columns. UNION removes duplicate rows (which requires extra work), while UNION ALL keeps all rows including duplicates and is therefore faster. Use UNION ALL when duplicates are impossible or acceptable.

Aggregate functions compute one value from many rows: COUNT, SUM, AVG, MIN, MAX. GROUP BY groups rows that share values in specified columns so aggregates are calculated per group. Example: SELECT city, COUNT(*) AS customers FROM customers GROUP BY city; Every selected column must either be in GROUP BY or inside an aggregate.

A subquery is a query nested inside another query, e.g., in WHERE, FROM or SELECT. A correlated subquery references columns from the outer query, so it is evaluated once per outer row. Example (employees earning more than their department average): SELECT name FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept = e.dept);

A CTE is a named temporary result set defined with WITH that can be referenced in the main query, making complex SQL more readable. Recursive CTEs can handle hierarchical data like org charts. Example: WITH dept_avg AS ( SELECT dept, AVG(salary) AS avg_sal FROM employees GROUP BY dept ) SELECT * FROM dept_avg WHERE avg_sal > 60000;

Window functions perform calculations across a set of rows related to the current row without collapsing them into one row (unlike GROUP BY). Syntax: FUNCTION() OVER (PARTITION BY ... ORDER BY ...). Examples: ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), running totals with SUM() OVER (ORDER BY date), and moving averages. SELECT name, dept, salary, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank_in_dept FROM employees;

For salaries 100, 90, 90, 80 (descending): • ROW_NUMBER: 1, 2, 3, 4 — always unique. • RANK: 1, 2, 2, 4 — ties share a rank and the next rank is skipped. • DENSE_RANK: 1, 2, 2, 3 — ties share a rank with no gaps.

Several approaches: 1. SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); 2. SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1; 3. Using DENSE_RANK: SELECT salary FROM ( SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r FROM employees ) t WHERE r = 2; The DENSE_RANK version generalises to the Nth highest and handles duplicates.

Group by the columns that define a duplicate and keep groups with more than one row: SELECT email, COUNT(*) AS cnt FROM customers GROUP BY email HAVING COUNT(*) > 1; To delete duplicates while keeping one, use ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) and delete rows where the number is greater than 1.

NULL means a missing or unknown value — it is not zero or an empty string. Comparisons with = do not work; use IS NULL or IS NOT NULL. Most aggregates (SUM, AVG, COUNT(column)) ignore NULLs, while COUNT(*) counts all rows. Functions like COALESCE(col, default) replace NULLs with a value.

A view is a saved SQL query that acts like a virtual table; it simplifies complex queries and can restrict access to certain columns. It does not store data and runs the underlying query each time. A materialized view stores the query result physically, making reads fast, but it must be refreshed to reflect changes in the underlying tables.

A stored procedure is a saved block of SQL logic that can accept parameters and is executed on demand, useful for reusable business logic. A trigger is code that runs automatically in response to an event on a table, such as INSERT, UPDATE or DELETE — for example, writing to an audit log whenever a row changes.

• NOT NULL: column must have a value • UNIQUE: all values must be different • PRIMARY KEY: unique and not null identifier • FOREIGN KEY: enforces relationships between tables • CHECK: values must satisfy a condition (e.g., age >= 18) • DEFAULT: value used when none is provided

Isolation levels control how much concurrent transactions see of each other's changes: • Read Uncommitted: may see uncommitted changes (dirty reads). • Read Committed: only sees committed data; non-repeatable reads possible (default in PostgreSQL). • Repeatable Read: rows read once stay the same during the transaction; phantom reads may occur in some databases. • Serializable: transactions behave as if run one after another; safest but slowest.

• Document stores: JSON-like documents (MongoDB, Couchbase) • Key-value stores: simple key → value lookups, very fast (Redis, DynamoDB) • Column-family stores: wide tables optimised for large-scale writes (Cassandra, HBase) • Graph databases: nodes and relationships for connected data (Neo4j) They offer flexible schemas and horizontal scaling, often trading some consistency guarantees.

The CAP theorem states that a distributed data system can guarantee at most two of three properties at the same time during a network partition: • Consistency: every read gets the latest write • Availability: every request receives a response • Partition tolerance: the system keeps working despite network failures Since partitions are unavoidable in distributed systems, the practical choice is between consistency (CP) and availability (AP) when a partition occurs.

• Use EXPLAIN / EXPLAIN ANALYZE to read the execution plan • Add indexes on columns used in WHERE, JOIN and ORDER BY • Select only needed columns instead of SELECT * • Avoid functions on indexed columns in WHERE (they can prevent index use) • Filter early and reduce rows before joins • Replace correlated subqueries with joins or CTEs where appropriate • Use pagination (LIMIT) and keep table statistics up to date • Consider partitioning or materialized views for very large tables

• OLTP (Online Transaction Processing): many small, fast read/write transactions for day-to-day operations — orders, payments, bookings. Highly normalized. • OLAP (Online Analytical Processing): complex queries over large historical data for reporting and analysis. Often denormalized in data warehouses (star or snowflake schemas), e.g., Snowflake, BigQuery, Redshift.

A star schema is a data-warehouse design with a central fact table containing measurable events (e.g., sales amount, quantity) and foreign keys to surrounding dimension tables that describe them (date, product, customer, store). It is simple and fast for analytical queries and BI tools like Power BI and Tableau. A snowflake schema further normalizes the dimension tables.

CHAR(n) stores fixed-length strings and pads shorter values with spaces, which suits values of constant length such as country codes. VARCHAR(n) stores variable-length strings up to n characters and uses only as much space as needed, making it the usual choice for names, emails and addresses.