RSRobince Studio

DBMS and SQL interview revision notes

Relational databases: SQL, tables, JOINs, transactions. Written for developers preparing for an interview who want to refresh their DBMS and SQL fast. Free, no sign-up.

19 topics in these notes

  1. What is DBMS / RDBMS?
  2. Tables, rows, columns, keys
  3. SELECT, FROM, WHERE
  4. INSERT, UPDATE, DELETE
  5. Data types
  6. PRIMARY KEY, UNIQUE, NOT NULL
  7. WHERE (AND, OR, IN, LIKE, BETWEEN)
  8. ORDER BY, LIMIT, OFFSET
  9. Aggregate functions (COUNT, SUM, AVG)
  10. GROUP BY, HAVING
  11. JOINs (INNER, LEFT, RIGHT)
  12. Subqueries
  13. Normalization (1NF, 2NF, 3NF)
  14. Indexes
  15. Transactions (ACID, COMMIT, ROLLBACK)
  16. Foreign key & referential integrity
  17. Views
  18. SQL injection & parameterized queries
  19. Node + SQL (pg, Prisma/Sequelize)

What is DBMS / RDBMS?

Simple real-life example (Understand first — very important for understanding)

  • Think of a big, organized filing system: data is stored in tables (like spreadsheets) so you can add, find, update, and delete rows in a structured way.
  • A DBMS is the software that keeps that data safe, lets many users or apps use it at once, and lets you ask questions in a standard language (SQL).
  • Real-world idea: one reliable place for your app's data, with rules and queries so you get exactly what you need.

Real-world idea: structured, reliable storage. A DBMS stores data in tables and lets you query and update it with a common language.

Technical definition

  • DBMS (Database Management System): software to create, manage, and query databases. RDBMS stores data in tables (rows and columns) with relationships.
  • Data is structured; schema is fixed (columns and types). Use SQL (Structured Query Language) to query and modify. Examples: PostgreSQL, MySQL, SQLite, SQL Server.
  • ACID properties (atomicity, consistency, isolation, durability) for reliability. Good for transactional, relational data.

Simple practical example

Data is structured; schema is fixed (columns and types).

Code example

-- Tables: users (id, name), orders (id, user_id, total)
-- SQL: SELECT * FROM users WHERE id = 1;
--     SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id;

Why it matters

Backend and full-stack roles often use both SQL and NoSQL. "Difference between SQL and NoSQL?" — tables vs documents, schema, JOINs vs embedding/reference.

Interview tip

"RDBMS: tables, rows, columns, SQL, ACID. Use when data is highly relational and schema is stable. PostgreSQL/MySQL are common."

Tables, rows, columns, keys

Simple real-life example (Understand first — very important for understanding)

  • A table is like a spreadsheet: rows are records (e.g. one person per row), columns are fields (name, email, age).
  • A primary key is like a unique ID for each row (e.g. customer ID) so you can always point to exactly one record.
  • Keys and tables give you a clear, consistent way to organize and find data.

Real-world idea: tables hold rows and columns; keys uniquely identify each row so you can link and find data.

Technical definition

  • Table: named collection of rows; each row has the same columns. Column: attribute (e.g. name, email); has a data type.
  • Primary key: column(s) that uniquely identify a row; not null, unique. One per table. Often auto-increment ID.
  • Candidate key: any set of columns that could uniquely identify a row. Foreign key: column that references primary key of another table (relationship).

Simple practical example

Primary key: column(s) that uniquely identify a row; not null, unique.

Code example

CREATE TABLE users (
  id    INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255) UNIQUE NOT NULL,
  name  VARCHAR(100)
);

Why it matters

Every table has a primary key; foreign keys define relationships. "What is a primary key?" is a basic interview question.

Interview tip

"Table = rows + columns. Primary key = unique row identifier. Foreign key = reference to another table's primary key."

SELECT, FROM, WHERE

Simple real-life example (Understand first — very important for understanding)

  • Like asking the filing clerk: "Show me all rows where the city is Mumbai" or "Give me just the name and email columns."
  • SELECT is what columns you want; WHERE is the condition that filters which rows you get.
  • You can combine many conditions (and, or, not) to narrow down exactly the data you need.

Real-world idea: ask for specific columns and only the rows that match your conditions. SELECT and WHERE are the basis of reading data.

Technical definition

  • SELECT col1, col2 FROM table_name — choose columns; SELECT * all columns. FROM specifies the table(s).
  • WHERE condition — filter rows. Example: WHERE age >= 18 AND status = 'active'. Condition is evaluated per row.
  • Order of clauses: SELECT → FROM → WHERE (then GROUP BY, HAVING, ORDER BY, LIMIT). Filter early to reduce data.

Simple practical example

WHERE condition — filter rows.

Code example

SELECT id, name, email FROM users WHERE age >= 18;
SELECT * FROM orders WHERE status = 'pending' AND created_at > '2024-01-01';

Why it matters

SELECT and WHERE are the foundation of every query. "Write a query to find users over 18" — SELECT with WHERE.

Interview tip

"SELECT columns FROM table WHERE condition. Prefer listing columns over * for clarity and performance."

INSERT, UPDATE, DELETE

Simple real-life example (Understand first — very important for understanding)

  • Insert: add a new row (like adding a new contact to the list).
  • Update: change one or more columns in rows that match a condition (e.g. set status to 'active' where id = 5).
  • Delete: remove rows that match a condition. Always use a condition so you don't wipe the whole table.

Real-world idea: add, change, and remove rows. Insert, update, and delete are the three ways you modify data.

Technical definition

  • INSERT INTO table (col1, col2) VALUES (v1, v2) — add row(s). INSERT INTO table VALUES (...) for all columns in order. Multiple rows: VALUES (a,b), (c,d).
  • UPDATE table SET col = value WHERE condition — modify rows. Always use WHERE (or you update all rows).
  • DELETE FROM table WHERE condition — remove rows. No WHERE = delete all (truncate is different — resets identity).

Simple practical example

UPDATE table SET col = value WHERE condition — modify rows.

Code example

INSERT INTO users (name, email) VALUES ('Alice', 'a@b.com');
UPDATE users SET name = 'Alice Smith' WHERE id = 1;
DELETE FROM sessions WHERE expires_at < NOW();

Why it matters

CRUD in SQL: INSERT create, SELECT read, UPDATE update, DELETE delete. Forgetting WHERE on UPDATE/DELETE is a classic mistake.

Interview tip

"INSERT with column list and VALUES. UPDATE/DELETE: always use WHERE to avoid updating/deleting everything."

Data types

Simple real-life example (Understand first — very important for understanding)

  • Like choosing the right box for each item: numbers go in number columns, dates in date columns, text in text columns.
  • Types (integer, decimal, text, date, boolean) keep data consistent and let the database do math and comparisons correctly.
  • Picking the right type saves space and avoids errors (e.g. storing a date as text can break sorting).

Real-world idea: each column has a type so the database stores and compares values correctly.

Technical definition

  • Numeric: INT, BIGINT, SMALLINT; DECIMAL(n,m), FLOAT — use DECIMAL for money. String: VARCHAR(n), CHAR(n), TEXT.
  • Date/time: DATE, TIME, DATETIME, TIMESTAMP. Store in UTC; convert for display. Boolean: BOOLEAN (or TINYINT in MySQL).
  • JSON/JSONB (PostgreSQL) for semi-structured data. BLOB for binary. Choose type that matches domain and size.

Simple practical example

Date/time: DATE, TIME, DATETIME, TIMESTAMP.

Code example

price DECIMAL(10, 2)
email VARCHAR(255)
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
metadata JSONB

Why it matters

Wrong types cause errors or poor performance. "How do you store money?" — DECIMAL. "Dates?" — TIMESTAMP with timezone/UTC.

Interview tip

"Use DECIMAL for money; VARCHAR with limit for strings; TIMESTAMP for dates. Store UTC; convert in app."

PRIMARY KEY, UNIQUE, NOT NULL

Simple real-life example (Understand first — very important for understanding)

  • Like rules on a form: "This field is required" (NOT NULL), "This must be unique" (UNIQUE), "This is the main ID" (PRIMARY KEY).
  • Constraints protect data quality so you can't accidentally enter two identical IDs or leave a required field empty.
  • The database enforces these rules for every insert and update.

Real-world idea: rules that keep data valid. Constraints ensure uniqueness, required values, and referential integrity.

Technical definition

  • PRIMARY KEY: unique, not null, one per table; often auto-increment. UNIQUE: column(s) must be unique (null often allowed once).
  • NOT NULL: column cannot be null. DEFAULT value: value when not specified on INSERT.
  • Constraint names help in error messages: CONSTRAINT pk_users PRIMARY KEY (id). Violations cause insert/update to fail.

Simple practical example

NOT NULL: column cannot be null.

Code example

id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(255) UNIQUE NOT NULL,
name VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

Why it matters

Constraints enforce data integrity. Duplicate primary key or unique = error. "What happens if you insert duplicate primary key?" — error, rollback.

Interview tip

"PRIMARY KEY = unique + not null. UNIQUE = no duplicates. NOT NULL = required. DEFAULT for optional default value."

WHERE (AND, OR, IN, LIKE, BETWEEN)

Simple real-life example (Understand first — very important for understanding)

  • Like asking "who is older than 18?" or "whose name starts with 'A'?" or "who is in this list of IDs?"
  • Operators (=, <, >, IN, LIKE, BETWEEN, IS NULL) let you build precise conditions in WHERE.
  • Using the right operator makes your query clear and efficient.

Real-world idea: filter rows with comparisons and patterns. Operators in WHERE let you express exactly what you want.

Technical definition

  • AND, OR — combine conditions. Use parentheses for clarity: WHERE (a = 1 OR a = 2) AND b = 3.
  • IN (v1, v2, v3) — match any value; equivalent to multiple OR. NOT IN — exclude. BETWEEN x AND y — inclusive range.
  • LIKE 'pattern': % any chars, _ one char. LIKE '%test%' contains 'test'. Case: ILIKE (PostgreSQL) or LOWER(col) LIKE .... IS NULL / IS NOT NULL for nulls.

Simple practical example

IN (v1, v2, v3) — match any value; equivalent to multiple OR.

Code example

WHERE status IN ('active', 'pending')
WHERE name LIKE 'A%'
WHERE age BETWEEN 18 AND 65
WHERE email IS NOT NULL

Why it matters

Complex filters use AND/OR/IN/LIKE. NULL comparison: col = NULL is wrong; use IS NULL.

Interview tip

"IN for multiple values; LIKE with % and _; BETWEEN inclusive; use IS NULL not = NULL."

ORDER BY, LIMIT, OFFSET

Simple real-life example (Understand first — very important for understanding)

  • Order: like sorting a list by name, or by date newest first.
  • Limit: like "give me only the first 10" so you don't get a huge result; often used with order for "top 10" or "latest 5."
  • Together they give you sorted, bounded results—essential for pagination and top-N queries.

Real-world idea: sort the result and take only how many you need. ORDER BY and LIMIT control order and size of the result.

Technical definition

  • ORDER BY col [ASC|DESC] — sort rows. Multiple: ORDER BY name ASC, created_at DESC. NULLs: check DB (NULLS FIRST/LAST in PostgreSQL).
  • LIMIT n — return at most n rows. OFFSET m — skip first m rows. Pagination: LIMIT 10 OFFSET 20 (page 3, 10 per page).
  • Large OFFSET is slow (DB still scans skipped rows). Cursor-based pagination (WHERE id > last_id ORDER BY id LIMIT 10) scales better.

Simple practical example

LIMIT n — return at most n rows.

Code example

SELECT * FROM users ORDER BY created_at DESC LIMIT 10 OFFSET 20;

Why it matters

Lists need ordering and pagination. "How do you paginate?" — LIMIT/OFFSET or cursor. "Why is OFFSET slow?" — full scan up to offset.

Interview tip

"ORDER BY col ASC/DESC; LIMIT n OFFSET m for pagination. Mention cursor-based pagination for large datasets."

Aggregate functions (COUNT, SUM, AVG)

Simple real-life example (Understand first — very important for understanding)

  • Like asking "how many?" "what's the total?" "average?" "min or max?" over a set of rows.
  • Aggregates (COUNT, SUM, AVG, MIN, MAX) compute one value from many rows.
  • You use them in reports and dashboards when you need a summary, not every row.

Real-world idea: summarize many rows into one number. Aggregates answer "how many," "total," "average," and similar questions.

Technical definition

  • COUNT(*) or COUNT(col) — number of rows (COUNT(col) excludes nulls). SUM(col), AVG(col), MIN(col), MAX(col).
  • Aggregates collapse rows to one (or one per group with GROUP BY). Use with or without GROUP BY. COUNT(DISTINCT col) for unique values.
  • Filter aggregated rows with HAVING, not WHERE (WHERE filters before aggregate, HAVING after).

Simple practical example

Aggregates collapse rows to one (or one per group with GROUP BY).

Code example

SELECT COUNT(*) FROM users;
SELECT AVG(amount), SUM(amount) FROM orders WHERE status = 'completed';

Why it matters

Reporting and dashboards use COUNT, SUM, AVG. "Total sales per region?" — GROUP BY + SUM. COUNT(*) vs COUNT(col) — nulls.

Interview tip

"COUNT(*), SUM, AVG, MIN, MAX. COUNT(col) excludes nulls. HAVING for filter on aggregates; WHERE before aggregate."

GROUP BY, HAVING

Simple real-life example (Understand first — very important for understanding)

  • Like "total sales per region" or "count of orders per customer": you group rows by a column, then aggregate within each group.
  • GROUP BY defines the groups; HAVING filters which groups to keep (e.g. only groups where count > 10).
  • So you get one row per group with the aggregate you asked for.

Real-world idea: summarize by category. GROUP BY and HAVING let you get totals or counts per group and filter those groups.

Technical definition

  • GROUP BY col1, col2 — group rows with same values; then aggregates apply per group. Non-aggregated columns in SELECT must be in GROUP BY.
  • HAVING condition — filter groups (after aggregation). Example: HAVING COUNT(*) > 5 or HAVING SUM(amount) > 1000.
  • Order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. WHERE filters rows; HAVING filters groups.

Simple practical example

HAVING condition — filter groups (after aggregation).

Code example

SELECT country, COUNT(*) AS cnt FROM users GROUP BY country HAVING COUNT(*) > 10;

Why it matters

"Count users per country", "orders with more than 3 items" — GROUP BY and HAVING. Very common in SQL interviews.

Interview tip

"GROUP BY columns; SELECT only grouped columns or aggregates. HAVING to filter on aggregate result."

JOINs (INNER, LEFT, RIGHT)

Simple real-life example (Understand first — very important for understanding)

  • Like having two lists (customers and orders) and wanting one table that shows "customer name + their orders": you join on the common key (e.g. customer_id).
  • JOIN brings together rows from two or more tables when a condition matches (usually equality on a key).
  • Inner join keeps only matches; left join keeps all from the left table even if there is no match on the right.

Real-world idea: combine related data from different tables. Joins link rows by matching keys so you see the full picture.

Technical definition

  • INNER JOIN: only rows where both tables match the ON condition. FROM a INNER JOIN b ON a.id = b.a_id.
  • LEFT JOIN: all rows from left table; right side NULL if no match. Use for "all users and their orders (if any)". RIGHT JOIN = reverse. FULL OUTER = both sides (all rows).
  • Multiple JOINs: FROM a JOIN b ON ... JOIN c ON .... Alias tables: FROM users u JOIN orders o ON u.id = o.user_id. Avoid Cartesian product (missing or wrong ON).

Simple practical example

LEFT JOIN: all rows from left table; right side NULL if no match.

Code example

SELECT u.name, o.total FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- LEFT: all users, orders if exist
SELECT u.name, o.total FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

Why it matters

Relational data is in multiple tables; JOINs combine them. "Difference between INNER and LEFT JOIN?" — only matches vs include all left.

Interview tip

"INNER = only matching rows. LEFT = all left + match from right (NULL if no match). Always specify ON condition."

Subqueries

Simple real-life example (Understand first — very important for understanding)

  • Like asking a question inside a question: "Find everyone whose age is above the average"—the inner question gets the average, the outer uses it.
  • A subquery is a SELECT used inside another SELECT (or INSERT/UPDATE/DELETE) to compute a value or a list used in the main query.
  • Use them when one step depends on the result of another query.

Real-world idea: use the result of one query inside another. Subqueries help when the condition or value comes from the data itself.

Technical definition

  • Subquery: SELECT inside another query. In WHERE: WHERE col IN (SELECT id FROM other) or WHERE col = (SELECT MAX(x) FROM t) (scalar).
  • In FROM: FROM (SELECT ... FROM t) AS sub — derived table. Must have alias. Use for multi-step logic or when JOIN is awkward.
  • EXISTS: WHERE EXISTS (SELECT 1 FROM other WHERE other.id = main.id) — often faster than IN for large sets. Correlated subquery = references outer query.

Simple practical example

In FROM: FROM (SELECT .

Code example

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

Why it matters

"Users who have at least one order" — EXISTS or IN with subquery. "Find max and who has it" — subquery in WHERE or JOIN.

Interview tip

"Subquery in WHERE (IN, =, EXISTS) or FROM (derived table with alias). EXISTS for 'has at least one' often better than IN."

Normalization (1NF, 2NF, 3NF)

Simple real-life example (Understand first — very important for understanding)

  • Like keeping one master list of customers and referring to it from orders, instead of copying the customer name into every order row.
  • Normalization reduces duplication: you store each fact once and link tables by keys, so updates (e.g. change of address) happen in one place.
  • Trade-off: more tables and joins, but less redundancy and fewer inconsistencies.

Real-world idea: store each fact once and link by keys. Normalization reduces duplication and keeps data consistent.

Technical definition

  • 1NF: atomic values (no repeating groups; each cell single value). One value per column per row.
  • 2NF: 1NF + no partial dependency (non-key columns depend on full primary key). If PK is composite, every non-key column must depend on all of it.
  • 3NF: 2NF + no transitive dependency (non-key column must not depend on another non-key). Split table so each non-key depends only on the key.
  • Denormalization: sometimes add redundancy for read performance (e.g. cache computed column); trade-off with consistency.

Simple practical example

2NF: 1NF + no partial dependency (non-key columns depend on full primary key).

Code example

-- 1NF: no list in one cell; separate rows or table
-- 2NF: order_items (order_id, product_id, qty) not (order_id, product_name, qty) if product_name depends only on product_id
-- 3NF: user_id, user_name in same table? If user_name depends only on user_id, move to users table

Why it matters

"What is normalization?" and "Explain 1NF, 2NF, 3NF" are classic DBMS interview questions. Reduces redundancy and update anomalies.

Interview tip

"1NF: atomic values. 2NF: no partial dependency on PK. 3NF: no transitive dependency. Normalize to reduce redundancy; denormalize for read perf when needed."

Indexes

Simple real-life example (Understand first — very important for understanding)

  • Like an index at the back of a book: instead of scanning every row, the database can jump to rows that match a value or range.
  • You create indexes on columns you often filter or sort by; reads get faster, writes may be slightly slower because the index must be updated.
  • Primary keys are usually indexed automatically; add indexes for other columns you query often.

Real-world idea: speed up lookups. Indexes let the database find and sort rows without scanning the whole table.

Technical definition

  • Index: structure that speeds up search (and often sort) on column(s). Without index, full table scan. Create: CREATE INDEX idx_name ON table(col); unique: CREATE UNIQUE INDEX ....
  • B-tree (default): good for equality and range (=, <, >, ORDER BY). Composite index: (a, b) helps queries on (a) or (a, b); order of columns matters.
  • Trade-off: indexes speed reads but slow writes (insert/update/delete must update index). Don't over-index. Use EXPLAIN to see if index is used.

Simple practical example

B-tree (default): good for equality and range (=, <, >, ORDER BY).

Code example

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);

Why it matters

"Why is my query slow?" — often missing index on WHERE/JOIN/ORDER BY columns. "When not to index?" — small table, heavy write, rarely queried column.

Interview tip

"Index speeds WHERE, JOIN, ORDER BY. B-tree default. Composite index column order matters. Trade-off: faster read, slower write; use EXPLAIN."

Transactions (ACID, COMMIT, ROLLBACK)

Simple real-life example (Understand first — very important for understanding)

  • Like a single bank transfer: debit one account and credit another—either both happen or neither; you never want half-done state.
  • A transaction groups several changes; if any step fails, all are rolled back so the database stays consistent.
  • Use transactions when one logical action updates more than one row or table.

Real-world idea: all changes together or none. Transactions keep multi-step updates consistent and safe.

Technical definition

  • Transaction: group of statements as one unit — all commit or all roll back. BEGIN; (or START TRANSACTION), COMMIT;, ROLLBACK;.
  • ACID: Atomicity (all or nothing), Consistency (constraints hold), Isolation (concurrent transactions don't see partial state), Durability (committed data persists after crash).
  • Isolation levels: Read Uncommitted, Read Committed, Repeatable Read, Serializable. Higher = less anomaly, more locking. Default varies (e.g. Read Committed in PostgreSQL).

Simple practical example

ACID: Atomicity (all or nothing), Consistency (constraints hold), Isolation (concurrent transactions don't see partial state), Durability (committed data persists after crash).

Code example

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- or ROLLBACK on error

Why it matters

Transfers, double-entry — must be atomic. "What is ACID?" and "When do you use a transaction?" are standard.

Interview tip

"Transaction: BEGIN, statements, COMMIT or ROLLBACK. ACID: atomicity, consistency, isolation, durability. Use for multi-statement consistency."

Foreign key & referential integrity

Simple real-life example (Understand first — very important for understanding)

  • Like a rule: "This column (e.g. customer_id in orders) must point to a real row in that other table (customers)."
  • Foreign keys enforce relationships: you can't have an order for a customer that doesn't exist, and you can optionally say what to do on delete (e.g. cascade).
  • They keep references valid so you don't end up with orphaned or invalid links.

Real-world idea: links between tables that the database enforces. Foreign keys keep relationships valid.

Technical definition

  • Foreign key: column(s) that reference primary key (or unique) of another table. FOREIGN KEY (user_id) REFERENCES users(id). Ensures referenced row exists (or NULL if allowed).
  • ON DELETE: what to do when referenced row is deleted — CASCADE (delete child rows), SET NULL, RESTRICT (default — prevent delete if referenced), NO ACTION.
  • ON UPDATE: similar for key updates. Referential integrity = no orphan rows; DB enforces it when FK is defined.

Simple practical example

ON DELETE: what to do when referenced row is deleted — CASCADE (delete child rows), SET NULL, RESTRICT (default — prevent delete if referenced), NO ACTION.

Code example

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

Why it matters

FKs maintain consistency. "What happens if you delete a user who has orders?" — RESTRICT error or CASCADE deletes orders. Design choice.

Interview tip

"Foreign key references primary key of other table. ON DELETE CASCADE/SET NULL/RESTRICT. Ensures no orphan references."

Views

Simple real-life example (Understand first — very important for understanding)

  • Like a saved query with a name: instead of writing the same long query every time, you define a view and then SELECT from it like a table.
  • Views don't store data; they show the result of a query whenever you use them, so they always reflect current data.
  • Use them to simplify complex queries or to give users access to a subset of columns or rows.

Real-world idea: a named, reusable query. Views let you hide complexity and reuse the same definition everywhere.

Technical definition

  • View: virtual table defined by a SELECT query. CREATE VIEW active_users AS SELECT * FROM users WHERE status = 'active'. Query it like a table: SELECT * FROM active_users.
  • Simplifies complex queries; can grant access to view instead of base tables. Some views are updatable (simple views); others read-only.
  • Materialized view (PostgreSQL etc.): stores result physically; refresh periodically. Good for expensive aggregations; trade-off freshness vs performance.

Simple practical example

Simplifies complex queries; can grant access to view instead of base tables.

Code example

CREATE VIEW order_summary AS
SELECT user_id, COUNT(*) AS order_count, SUM(total) AS total_spent
FROM orders GROUP BY user_id;

Why it matters

Reusable query, security (hide columns), abstraction. "What is a view?" — saved query as virtual table. Materialized view for reporting.

Interview tip

"View = saved SELECT, queried like table. Use for reuse and security. Materialized view = stored result, refresh for reporting."

SQL injection & parameterized queries

Simple real-life example (Understand first — very important for understanding)

  • Like someone slipping a fake instruction into a form: if you paste user input directly into a query, an attacker could add extra SQL and change or steal data.
  • Prevention: never build SQL by string concatenation with user input; use parameterized queries (placeholders) so input is always treated as data, not as code.
  • This is a critical security habit in any app that talks to a database.

Real-world idea: treat user input as data, never as part of the query. Parameterized queries prevent SQL injection.

Technical definition

  • SQL injection: attacker puts SQL in input (e.g. ' OR '1'='1) so the concatenated query does something unintended. Never build SQL by string concatenation with user input.
  • Parameterized queries (prepared statements): placeholders for values; driver escapes/binds them. SELECT * FROM users WHERE id = ? with param [userId]. Prevents injection.
  • In Node: client.query('SELECT * FROM users WHERE id = $1', [id]) (pg); never query('SELECT ... WHERE id = ' + id). Same in any language.

Simple practical example

Parameterized queries (prepared statements): placeholders for values; driver escapes/binds them.

Code example

// BAD
db.query("SELECT * FROM users WHERE email = '" + email + "'");
// GOOD
db.query('SELECT * FROM users WHERE email = $1', [email]);

Why it matters

Security 101. "How do you prevent SQL injection?" — parameterized queries only; never concatenate user input into SQL.

Interview tip

"Never concatenate user input into SQL. Use parameterized queries (placeholders + bind values). Prevents SQL injection."

Node + SQL (pg, Prisma/Sequelize)

Simple real-life example (Understand first — very important for understanding)

  • Your Node app sends SQL to the database through a driver or library; the driver handles connection, pooling, and parameters.
  • You use placeholders (e.g. $1, ? or named params) for values so user input is never concatenated into the query.
  • Same SQL concepts (SELECT, WHERE, JOINs, transactions) apply; the driver just lets Node run them and get results back.

Real-world idea: Node sends parameterized SQL and gets back rows. Use a driver and never concatenate user input into SQL.

Technical definition

  • pg (node-postgres): low-level; const client = await pool.connect(); client.query('SELECT * FROM users WHERE id = $1', [id]); client.release(). Use connection pool. Parameterized with $1, $2.
  • Sequelize / Prisma: ORM — define models, use JS/TS API; they generate SQL. Migrations, relations, validation. Prisma: schema file, type-safe client. Sequelize: Model.define, associations.
  • Choose: raw pg for full control and simple queries; ORM for complex models, migrations, and less SQL. Always use parameterized queries (ORM does by default).

Simple practical example

Sequelize / Prisma: ORM — define models, use JS/TS API; they generate SQL.

Code example

const { Pool } = require('pg');
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const res = await pool.query('SELECT * FROM users WHERE id = $1', [userId]);

Why it matters

Backend Node apps often use PostgreSQL or MySQL. "How do you connect Node to SQL?" — pg or mysql2; "ORM?" — Sequelize or Prisma. Know parameterized queries.

Interview tip

"pg or mysql2 for Node; use pool and parameterized queries. ORM: Sequelize or Prisma for models, migrations, relations; still parameterized under the hood."

Practise the questions

Read the top 50 DBMS and SQL interview questions and answers to test yourself after revising.

More Databases notes

MongoDB · Mongoose · All developer notes