Relational Model & Sql Fundamentals
What are the differences between DELETE, TRUNCATE, and DROP in terms of transaction logging, speed, and rollback ability?
What is a CASE expression and how would you use it inside a SELECT or ORDER BY clause?
What are the main categories of SQL statements, DDL, DML, DCL, and TCL, and what does each include?
What is the relational model, and how do relations, tuples, attributes, and domains map to tables, rows, columns, and data types?
Why is using SELECT * generally discouraged in production queries?
Explain the logical order of a SQL query's execution. Why does SELECT happen after WHERE and GROUP BY, and how does this affect using aliases?
What is the MERGE statement (upsert), and when would you use it?
What factors should you consider when choosing data types for columns, such as CHAR vs VARCHAR or the right numeric type?
Joins & Set Operations
What is a Self-Join, and in what real-world scenario would you use one?
Explain the difference between JOIN and UNION.
What are the different types of JOINs and how do they handle non-matching rows?
Explain the difference between an INNER JOIN and an OUTER JOIN (LEFT/RIGHT/FULL).
Explain the difference between an INNER JOIN and a LEFT JOIN. When would you use a CROSS JOIN or a SELF JOIN in a real-world application?
What is the difference between UNION and UNION ALL, and when would you prefer one over the other?
When would you use a CROSS JOIN, and what are the performance risks?
What is the difference between the INTERSECT and EXCEPT set operators, and how do they differ from a JOIN?
What is the difference between the ON and USING clauses in a JOIN, and what is a NATURAL JOIN?
Explain the difference between a Nested Loop Join, a Hash Join, and a Merge Join. When would the query optimizer choose one over the others?
What are semi-joins and anti-joins, and how are they typically expressed in SQL?
Keys & Constraints
Explain the concept of referential integrity and how foreign keys enforce it.
What is the purpose of a Foreign Key constraint? Explain the difference between ON DELETE CASCADE and ON DELETE SET NULL in terms of data integrity.
What are CHECK, DEFAULT, and NOT NULL constraints, and how do they help enforce data integrity?
What is the difference between a primary key and a unique key, can a table have multiple unique keys, and can a unique key contain a NULL value?
What are 'Cascading Actions' like ON DELETE CASCADE in foreign keys, and what are the risks of using them in a complex schema?
What are the trade-offs of using a UUID or Auto-increment ID (Surrogate) versus a Social Security Number or Email (Natural) as a Primary Key?
What is referential integrity, and can you explain the different On Delete actions like CASCADE, SET NULL, and RESTRICT?
What is a candidate key, and how does it relate to primary keys and composite keys?
What are the trade-offs of using a UUID versus an auto-incrementing integer as a primary key in a distributed system?
Aggregation & Grouping
What is the difference between WHERE and HAVING, and why can't you use an aggregate function like SUM() in a WHERE clause?
What do the GROUP BY and HAVING clauses do, and what are the common aggregate functions like COUNT, SUM, AVG, MIN, and MAX?
How does the DISTINCT keyword work, and what are the performance considerations of using it?
How does COUNT(*) differ from COUNT(column) and COUNT(DISTINCT column)?
What are ROLLUP, CUBE, and GROUPING SETS, and when would you use them?
Subqueries & Ctes
What are the trade-offs between using a Subquery and a Common Table Expression (CTE)? When would you prefer a Recursive CTE?
What is the difference between IN and EXISTS, and which is generally more efficient?
What is the difference between a Correlated Subquery and a Non-correlated Subquery?
What do the ANY and ALL operators do in a subquery comparison, and can you give an example?
What is the difference between a scalar subquery and a derived table?
What is the difference between a temporary table and a CTE, and when would you choose one over the other?
What is a correlated subquery and why can it be a performance bottleneck?
Schema Design & Normalization
What is ER modeling, and how do you represent entities, relationships, and cardinality when designing a relational schema?
Explain the difference between 2NF and 3NF. Why might a high-scale production system choose to denormalize certain tables?
What is database normalization and what are the specific goals of 1NF, 2NF, and 3NF?
When and why would you intentionally denormalize a database schema?
Explain the difference between 3NF and BCNF. At what point does a system become 'over-normalized,' and when would you intentionally denormalize a schema?
What is a functional dependency, and how does it relate to the process of normalization?
What is 4NF (and 5NF), and what kind of anomaly does it address that BCNF does not?
Views Procedures & Triggers
What is the difference between a standard View and a Materialized View? When is the extra storage cost of a Materialized View justified?
What is an updatable view, and what conditions must a view meet to allow inserts and updates through it?
What is a trigger in a relational database, and what are some appropriate use cases and pitfalls of using them?
What is the difference between a stored procedure and a user-defined function in SQL?
What is a cursor, and why is it generally discouraged in favor of set-based operations?
What are the trade-offs of putting business logic in Stored Procedures versus the Application Layer, considering maintainability, version control, and database portability?
Indexing & Query Tuning
What is the physical difference between clustered and non-clustered indexes in how data is stored on disk, and why can you only have one clustered index?
What is a composite (multi-column) index, and can you explain the leftmost prefix rule and why column order matters?
What is a 'covering index,' and how does it enable an 'index-only scan'? Why is this significantly faster than a standard index seek?
When can adding an index actually hurt performance? Explain the trade-off between read speed and write throughput.
Explain the 'N+1 Query Problem' from a database perspective. Why does it happen, and how can it be mitigated?
How do you read an EXPLAIN plan, and what is the difference between an Index Seek and an Index Scan?
Explain the difference between an index scan and an index seek.
How do you use an EXPLAIN plan to diagnose a slow-running query?
What is a partial (filtered) index, and when is it useful?
What is the difference between a full table scan and an index seek, and when might a full scan actually be preferable?
How can caching query results help performance, and what are the risks of stale cached data?
What does it mean for a query to be 'SARGable', and why does using a function like WHERE YEAR(date) = 2024 often prevent index usage?
How does a B-Tree index work internally to speed up a query? When would a Hash index be more appropriate?
What is index fragmentation, and how does it affect performance?
How does the query optimizer use table statistics and cardinality estimates to choose an execution plan?
Transactions Concurrency & Locking
Explain the ACID properties and why they are important for a relational database.
When would you choose optimistic versus pessimistic locking for a high-concurrency web application?
Explain the difference between optimistic and pessimistic locking.
What is a deadlock, how does a database engine detect them, and what strategies can you use in application code to prevent them?
What is a savepoint in a transaction, and how do COMMIT, ROLLBACK, and SAVEPOINT work together?
How does MVCC allow a database to handle concurrent reads and writes without locking the entire table? What are the storage implications of keeping multiple versions of a row?
What are the necessary conditions for a deadlock to occur in a relational database? How does the database engine typically detect and resolve them?
What are the four standard transaction isolation levels and what concurrency phenomena does each prevent?
What is a Dirty Read, a Non-repeatable Read, and a Phantom Read, and which isolation levels prevent which?
What are the four standard SQL isolation levels? Explain the difference between 'Read Committed' and 'Repeatable Read' in terms of the anomalies they prevent.
What is a 'Phantom Read' versus a 'Non-Repeatable Read'? Which isolation level is required to prevent Phantoms?
What is a two-phase commit (2PC) and in what scenario would you need it?
What are the different transaction isolation levels, and what are the trade-offs between Read Committed and Serializable?
Scaling Partitioning & Replication
Explain the difference between vertical and horizontal partitioning.
Explain the difference between a Read Replica and Sharding.
What is Connection Pooling, and why is it necessary for high-scale applications?
How do Read Replicas help scale a relational database? What is 'Replication Lag,' and how does it affect application consistency?
Explain the difference between horizontal partitioning within one DB instance and sharding across multiple instances. When does an engineer need to move from one to the other?
At what point does a relational database typically hit the limits of vertical scaling versus horizontal scaling?
Oltp Olap & Data Warehousing
What is the difference between an OLTP and an OLAP database workload?
Explain the star schema vs the snowflake schema in the context of data warehousing.
What are fact tables and dimension tables in a data warehouse, and what role does ETL play?
How does the schema design for an OLTP transaction database differ from an OLAP reporting/analytics data warehouse using a star schema?
Query Techniques & Security
What do COALESCE and NULLIF do, and how do they help handle NULL values?
Can you explain the underlying mechanism of a SQL injection attack, and how do parameterized queries or prepared statements actually solve the problem at the protocol level?
How does SQL's three-valued logic work regarding NULL values?
What is a Window Function, and what is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?
How would you use SQL GRANT and REVOKE commands to secure a microservice's access to a database following the principle of least privilege?
How do you implement pagination in SQL, and what are the trade-offs between OFFSET/LIMIT and keyset (seek) pagination?
How do LAG() and LEAD() window functions work, and what problems do they solve?
What is a window function's frame clause (ROWS/RANGE BETWEEN), and how would you compute a running total or moving average?