What should I actually practise to be ready for a SQL interview?
Practise the five things that come up in almost every SQL interview, and do it on a schema that you know by heart.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(120) NOT NULL,
city VARCHAR(80) NOT NULL
);
So what are the five things?
The first one is the joins. The inner join, the left join, and how to join three tables without multiplying the rows.
The second one is the grouping. The group by, the aggregate functions, and the difference between the where clause and the having clause.
The third one is the subqueries. A scalar one, a derived table, and the exists form.
The fourth one is the window functions. The row number, the sum over a partition, the lag and the lead, and the running total.
The fifth one is the performance. What an index does, what the plan looks like, and why a function around a column stops an index from being used.
So why practise on a schema that you know? Because then the only thing you have to think about is the question. If you are reading the table definition while you write the query, you are doing two jobs at once, and the interview is about the query.
The next two questions in this topic look at the questions themselves and at how to practise.