Databases

Part of the Interview Prep Roadmap. Coverage ratings are explained there.

SQL Querying

SELECT, WHERE, and filtering basics, then JOINs, aggregates, subqueries, and window functions.

Coverage: rich

Resources · SQLBolt — interactive lessons from SELECT upward · SQL — LeetCode — the Top SQL 50 study plan

Query Basics

• SELECT, FROM, and WHERE
• Comparison and logical operators
• ORDER BY and LIMIT
• DISTINCT
• Pattern matching with LIKE and wildcards
• NULL handling

Practice

• What SELECT 5/6 returns — Brain Station 23
• LIKE with a literal underscore — Brain Station 23
• Which is not a valid SQL data type — Brain Station 23
• Find the unique column of a table — WellDev

Aggregation

• COUNT, SUM, AVG, MIN, MAX
• GROUP BY
• HAVING vs. WHERE
• Aggregate behavior with NULLs

Practice

• Company with the lowest total employee count — Relisource
• Student name and course count — Relisource
• Show all duplicate rows in a table — WellDev

Joins

• INNER JOIN
• LEFT, RIGHT, and FULL OUTER JOIN
• Cross joins and self joins
• Join conditions and cardinality

Practice

• Left join vs. full join, and when to use each — Exabyting, with answer
• Joins and composite keys — Exabyting, with answer
• Maximum salary per department — Relisource
• Join across employee, supervisor and branch tables — ShopUp
• Join two or more tables from a schema — Sonargaon University

Subqueries & Set Operations

• Scalar and correlated subqueries
• IN, EXISTS, ANY, ALL
• UNION, INTERSECT, EXCEPT
• Common table expressions

Practice

• Second highest salary — WellDev
• A subquery combined with a join — ShellBeeHaken
• Products matching a price condition — WellDev

Advanced Querying

• Window functions (ROW_NUMBER, RANK, LAG, LEAD)
• Logical order of query execution
• Query optimization basics

Practice

• Order of SQL query execution — WellDev
• Reorder a scrambled query so it runs — Brain Station 23

Data Modification

• INSERT, UPDATE, DELETE
• Transactions and rollback

Practice

• DELETE vs. TRUNCATE vs. DROP — WellDev
• Select and update across a schema — National University

Database Design & Theory

Keys and schema design, then normalization, indexing, ACID properties, views, and choosing a database model for the shape of the data.

Coverage: rich

Resources · Database Design — freeCodeCamp · Use The Index, Luke — how indexes actually work · Notes: Database resources

Relational Model Fundamentals

• Tables, rows, and columns
• Primary, foreign, candidate, and composite keys
• Relationship cardinality: one-to-one, one-to-many, many-to-many
• Entity-relationship diagrams

Practice

• How many types of relationships exist — weDevs
• Modelling a many-to-many relationship — Brain Station 23, with answer
• Which statement about keys is incorrect — Brain Station 23
• Design an ERD for a restaurant system — Therap
• Why a DBMS instead of a file system — Stamford University

Normalization

• Functional dependencies
• 1NF, 2NF, 3NF, BCNF
• Insertion, update, and deletion anomalies
• Denormalization and its justification

Practice

• Normalization, its benefits and normal forms — bKash
• Normalize a student table — DSI
• Normalize to 1NF, 2NF and 3NF — Therap
• Normalize and identify primary/foreign keys — Therap
• Normalization vs. denormalization — Therap

Indexing

• Clustered vs. non-clustered indexes
• B-tree and hash indexes
• Composite indexes
• Read and write trade-offs

Practice

• What indexing is and why we need it — Exabyting, with answer
• Can a column with duplicates be indexed — Exabyting, with answer
• Indexing a user_email column — Exabyting, with answer
• What is indexing — Brain Station 23
• Which is not a way to improve performance — Brain Station 23

Transactions & Concurrency

• ACID properties
• Isolation levels
• Locking and deadlocks
• Dirty reads, non-repeatable reads, and phantom reads

Practice

• What a database transaction is — Therap
• The ACID properties — Exabyting, with answer
• Concurrency control and why it matters — bKash, with answer
• Deadlocks and how to prevent them — bKash
• A read-then-update race on a field — Priyo, with answer
• Which expansion of ACID is correct — Brain Station 23

Database Objects

• Views and materialized views
• Stored procedures and triggers
• Constraints

Practice

• Purpose of a view — Brain Station 23
• Triggers and cascading — WellDev
• Stored procedure vs. function vs. trigger — Therap

Database Selection

• Relational, document, key-value, and graph models
• SQL vs. NoSQL trade-offs
• Matching a model to the shape of the data

Practice

• SQL vs. NoSQL — Exabyting, with answer
• SQL or NoSQL for cache-to-RAM storage — WellDev
• Best model for unstructured data — Brain Station 23
• Which statement about RDBMS is false — Brain Station 23, with answer