SQL Select and Joins

What they ask

Alongside the algorithm items, the Cairo online assessment has a short SQL block. Nothing exotic: SELECT with filters, aggregation, and the difference between join types. Some items are multiple choice (which query returns rows with no match on the right side?), others ask you to type the query.

Questions in this area, for example, using two tables:

• employees(id, name, dept_id, salary)
• departments(id, name, city)

1. List every department in Cairo together with its employee count, including departments that currently have zero employees.
2. Return the names of employees who earn more than the average salary of their own department.
3. Explain, in one sentence each, what INNER JOIN, LEFT JOIN and FULL OUTER JOIN return for these two tables.

Example

For item 1, a department "Research" in Cairo with no employees should still appear with count 0, which is the whole point: an inner join would drop it.

What they look for

• Knowing that LEFT JOIN plus COUNT(e.id) (not COUNT(*)) gives a true zero for empty departments.
• Correlated subquery or a window function for item 2, and the ability to say which one you would pick and why.
• Reading the question carefully: filters that belong in ON versus WHERE change the result of an outer join, and the MCQs probe exactly that.