What is a “database management system”?
What is a “relational data model”?
Define the terms “simple”, “composite”, “candidate” and “alternate” keys.
What is a “primary key”? What are the criteria for choosing one?
Can you give an example where we have composite key and no partial dependency ?
What is “denormalization”? What is it used for?
What are the types of relationships in a database? Give examples.
Is primary key always indexed ?
Give example of use of composite primary key
What are “indexes”? What are they used for? What are their advantages and disadvantages?
I was sure that Composite Index (Multi-column) is b-tree
How EXPLAIN shows B-tree vs. hash usage in query plans ?
What is the difference between clustered and non-clustered indexes?
How do i define my clustered index ?
Does it make sense to index data that has a small number of possible values?
When is a full scan of a data set more advantageous than index access?
What is Low Selectivity problem, when we speaking about indexes ?
What are the main properties of a transaction?
What are the levels of transaction isolation?
What problems can arise with concurrent access using transactions?
BEGINNER
What is the difference between INNER JOIN and LEFT JOIN?
What is normalization? Why is it important?
What are the different types of JOINs in SQL?
Any disadvantages of using index ?
What is an Index Page in a Database?
Understanding b-tree and leaf pages in db
What is Fragmentation in a Database?
Why Fragmentation of leaf pages in b-tree is Bad
What is the difference between WHERE and HAVING clauses?
What about composite primary key and many unique constraints ?
What is the purpose of the GROUP BY clause?
Is primary key only about uniquness ?
What is the difference between DELETE, TRUNCATE, and DROP?
How to Fetch the Top N Records in SQL ?
What is the default sort order of ORDER BY?
What is the purpose of DISTINCT?
How would you find records that exist in one table but not another?
Explain ACID properties in databases.
What are Aggregate Functions in SQL?
How would you update all rows in a table to set a column value to a default?
What is the difference between CHAR and VARCHAR?
Why is CHAR Sometimes Faster Than VARCHAR?
How is a correlated subquery different from a regular subquery?
What is the difference between UNION and UNION ALL?
How do you prevent SQL injection?
Is primary key only about uniquness ?
MIDDLE
What are the differences between clustered and non-clustered indexes?
How would you optimize a slow SQL query?
How would you design a schema for a social network?
What is a deadlock in a database?
When we start transaction, what is locked, table or row ?
How can you detect and resolve deadlocks?
Explain the concept of sharding in databases.
How would you implement pagination in SQL?
Why, when we seek for pages no shifting from inserts/deletes on other rows ?
What if some rows were deleted ?
Give examples with Sum(), Avg(), Max()
WindowFunctions.Explain ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
Middle.If i don’t use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so i take all rows ?
What are CTEs (Common Table Expressions)?
What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?
What is a trigger? How and when would you use it?
How does database partitioning work?
Do i see different tables in some sql explorer like dataGrip, for example ?
Partitions and sharding, differenes
Explain the difference between OLTP and OLAP.
What is optimistic vs. pessimistic locking?
How can you enforce referential integrity?
How would you handle schema migrations in a production database?
How do you design a database for multi-tenant architecture?
What is a star schema? How is it different from a snowflake schema?
How does an index-only scan work?
Explain write-ahead logging (WAL).
How would you track changes (auditing) in a table?
What is a read replica and why would you use it?
How does a database optimizer work?
What is query execution plan (EXPLAIN)?
ADVANCED
How would you design a high-availability database system?
What is eventual consistency ?
How do you implement eventual consistency in a distributed database?
What is multi-version concurrency control (MVCC)?
Is MVCC implementation of optimisti lock ?
How would you implement full-text search in SQL?
How does a bloom filter help in database optimization?
Explain acid compliance in distributed systems
How would you handle database failover?
Explain the architecture of Amazon Aurora.
How would you store and query time-series data efficiently?
How would you implement a priority queue in a relational database?