SQL & Databases interview questions

What is a B-tree index and how does it work?

Explain the difference between JOIN and SUBQUERY.

What is ACID and why is it important for transactions?

When should you use a CTE instead of a subquery?

Explain how a window function works and provide an example of its use.

What is replication and what types exist?

Compare BASE and ACID. In what scenarios is it better to use each model?

Explain how partitioning works in PostgreSQL.

What is the difference between data normalization and denormalization, and when should each be applied?

What would you do if a JOIN operation in a distributed database took too long?

Modify the query: an anomaly is defined as three times the average over the two preceding and two following days, excluding the day itself.

Что посмотреть надо, что проверить надо, что дополнительно запросить надо?

Why кстати из таблицы table1? Где у них не будет?

Может ли быть у тебя тут такое, что левый индекс обгонит правый?

Каким кандидатом будем считать, если у нас 2000 кандидатов на каждый запрос?

What types of databases are there, and which have you worked with?

What can non-relational databases be used for?

Is PostgreSQL row-oriented or column-oriented?

By simplifying it, do you mean avoiding a subquery?

We will use this query as a subquery.