15 Tricky SQL Interview Questions for Experienced Users

SQL interview questions for experienced users usually contain some trick(y) questions. Interviewers use them to test your gumption, which is typically a result of extensive experience and a high level of SQL knowledge. 

I will show you 15 tricky SQL interview questions (mostly coding) in this article. Mind you, these are not necessarily complex questions. Actually, that’s their main characteristic: they seem very easy and straightforward, but that’s the interviewer trying to deceive you. But as an experienced SQL user, you should be able to recognize the traps and avoid them.

I’ll show you how to do that, but I can’t prepare for the interview instead of you. So, how should you approach interview preparation?

Interview Preparation as an Experienced SQL User

Solid foundations in SQL basic and intermediate topics are prerequisites for considering yourself an experienced SQL user. If you don’t know where you land on the SQL knowledge spectrum, we have something new for you: our SQL Skills Assessment. You can take the test and assess your level of SQL. It’s a free feature; you can take one test every 30 days. At the end of the test, you get an overall score on your SQL knowledge. There are detailed results for six competency areas: Basic SQL Queries, SQL JOINs, Standard SQL Functions, Basic SQL Reports, Intermediate SQL Reports, and Complex SQL Reports.

After the assessment, you can go to our Advanced SQL track for more practice. It consists of three main interactive courses that cover details of window functions, GROUP BY extensions, and recursive queries. The topics are spread throughout 395 coding challenges, so you’ll write plenty of code – which has been shown to be the most efficient way of learning SQL. After the course, you’ll be at home with advanced SQL topics.

The learning track will give you knowledge, no doubt about that. However, employers rely on experienced users to leverage SQL in solving real-life problems. But in life, things are rarely straightforward; actual problems tend not to be SQL  textbook examples tailored for learning. So, you should go beyond examples from the course. You need to work on flexibility and creativity, seeing potential pitfalls in advance and avoiding them in your SQL code. That’s what the interviewers are looking for from experienced users. Because of that, preparing for the interview with straightforward SQL questions is not enough. You should also brush up on the tricky questions, as the interviewers like to use them to try and catch you off guard.

Some of the common tricky SQL interview questions for experienced users are presented below.

Question 1: Select Freelancers and Their Task Info

Write a query that selects all freelancers along with their task info:

• Task title
• Task type and subtype
• Due date

Include freelancers that don’t have any tasks assigned.

Dataset: The dataset is of a company that employs freelancers on certain tasks. It consists of three tables. The first table is freelancer. You can find the script here.

id	first_name	last_name
1	Bob	Franklin
2	Dionne	Ravanelli
3	Marek	Lewandowski
4	Francois	Cousteau
5	Emma	Biesa

The second table is a dictionary of different task types named task_category. Here’s the script.

id	task_type	task_subtype
1	Blog article	SQL
2	Blog article	Python
3	Blog article	Career
4	Social media post	LinkedIn
5	Social media post	Other social media

The third table shows the details of the assigned work freelancers are doing for our company. The table is named task, with the script here.

id	task_category_id	title	freelancer_id	date_assigned	due_date	completed_date
1	2	Working With Pandas in Python	5	2023-11-30	2023-12-15	2023-12-15
2	4	Promote Advanced SQL Learning Track	4	2023-12-18	2023-12-20	2023-12-20
3	1	Working With LEFT JOIN in SQL	1	2023-12-08	2024-03-01	NULL
4	3	What Does a Data Analyst Do?	2	2023-12-20	2024-02-01	2024-02-10
5	4	Promote Working With Pandas in Python	4	2024-01-15	2024-01-18	2024-01-18
6	2	Python Libraries You Should Know	1	2024-01-15	2024-02-15	2024-02-15
7	1	Using COUNT in SQL	2	2024-01-20	2024-02-15	2024-02-15
8	1	Filtering Data in SQL	5	2024-02-20	NULL	NULL

Answer: This question tests your skills in joining three tables and choosing the correct join type.

Here’s the solution: 

SELECT f.first_name,
	 f.last_name,
	 t.title,
	 t.due_date,
	 tc.task_type,
	 tc.task_subtype
FROM freelancer f
JOIN task t
ON f.id = t.freelancer_id
JOIN task_category tc
ON t.task_category_id = tc.id;

Explanation: To get all the required info, you need to join all three tables. First, join the tables freelancer and task on the freelancer ID. To add the third table, you again need to write the JOIN keyword. Then, state in the ON clause that you’re joining tables on the task category ID.

The join type you use must be JOIN. It’s because of the possibility that there are some freelancers who don’t have any tasks yet. You need only those who have.

Output: Here’s the query output:

first_name	last_name	title	due_date	task_type	task_subtype
Emma	Biesa	Working With Pandas in Python	2023-12-15	Blog article	Python
Francois	Cousteau	Promote Advanced SQL Learning Track	2023-12-20	Social media post	LinkedIn
Bob	Franklin	Working With LEFT JOIN in SQL	2024-03-01	Blog article	SQL
Dionne	Ravanelli	What Does a Data Analyst Do?	2024-02-01	Blog article	Career
Francois	Cousteau	Promote Working With Pandas in Python	2024-01-18	Social media post	LinkedIn
Bob	Franklin	Python Libraries You Should Know	2024-02-15	Blog article	Python
Dionne	Ravanelli	Using COUNT in SQL	2024-02-15	Blog article	SQL
Emma	Biesa	Filtering Data in SQL	NULL	Blog article	SQL

Question 2: What Are OUTER JOINs and When Do You Use Them?

Answer: This question wants to see if you really understand how outer joins work and how they’re different from other joins.

OUTER JOINs are one of the distinct join categories in SQL, along with INNER JOINs and CROSS JOINs.

The following joins belong to the OUTER JOIN family:

• LEFT (OUTER) JOIN
• RIGHT (OUTER) JOIN
• FULL (OUTER) JOIN

The main characteristic of all OUTER JOINs is that they join tables in a way where one table is dominant, so all its data will be shown. The second table is subordinated so that the query will show only the matching rows from that table. If there are non-matching rows, they will appear as NULL.

So, OUTER JOINs should be used when you want to show non-matching rows as well as matching rows within the tables.

Each of the above outer joins works on that principle, but here’s how they differ:

• LEFT JOIN shows all the data from the first (left) table and only the matching rows from the second (right) table. If there are non-matching rows, they are shown as NULL.

• RIGHT JOIN shows all the data from the second (right) table and only the matching rows from the first (left) table. The non-matching rows are shown as NULL.

• FULL JOIN combines a LEFT JOIN and RIGHT JOIN. It shows all the data from both tables. In other words, it will show all the rows – matching and non-matching rows from the left table. Then, it will add all the rows from the right table that can’t be found in the left table. Where there’s non-matched data, you will see NULLs.

Question 3: Select Freelancer and Task Info, Part 2

Write a query that returns:

• Freelancers’ first and last names.
• The titles of their assigned tasks.
• Task type and subtype.
• Task due dates.

Include all freelancers, even those that don’t have any tasks.

Dataset: Same as Question 1.

Answer: Yet another SQL interview question for the experienced user. Here, you need to show that you understand the relationships between the tables. You need to use LEFT JOIN to join the three tables. You need to use LEFT JOIN as a first join. But you need to be aware that the relationship between the tables ‘forces’ you to use LEFT JOIN again as a second join.

SELECT f.first_name,
	 f.last_name,
	 t.title,
	 t.due_date,
	 tc.task_type,
	 tc.task_subtype
FROM freelancer f
LEFT JOIN task t
ON f.id = t.freelancer_id
LEFT JOIN task_category tc
ON t.task_category_id = tc.id;

Explanation: The query is very similar to the one in Question 1. So your first join is LEFT JOIN, as you need to output all the freelancers, not only those with a task assigned. In other words, the relationship is such that a task must have a freelancer assigned, but a freelancer doesn’t need to have a task assigned.

However, when you join the third table, you again need LEFT JOIN. Why is that? It’s because a task has to have a type and subtype. At the same time, each available task type doesn’t need to be among the assigned tasks. If you used INNER JOIN here instead, it would ‘cancel’ the first LEFT JOIN and skew your output.

Output: Here’s how your output should look:

first_name	last_name	title	due_date	task_type	task_subtype
Emma	Biesa	Working With Pandas in Python	2023-12-15	Blog article	Python
Francois	Cousteau	Promote Advanced SQL Learning Track	2023-12-20	Social media post	LinkedIn
Bob	Franklin	Working With LEFT JOIN in SQL	2024-03-01	Blog article	SQL
Dionne	Ravanelli	What Does a Data Analyst Do?	2024-02-01	Blog article	Career
Francois	Cousteau	Promote Working With Pandas in Python	2024-01-18	Social media post	LinkedIn
Bob	Franklin	Python Libraries You Should Know	2024-02-15	Blog article	Python
Dionne	Ravanelli	Using COUNT in SQL	2024-02-15	Blog article	SQL
Emma	Biesa	Filtering Data in SQL	NULL	Blog article	SQL
Marek	Lewandowski	NULL	NULL	NULL	NULL

Using INNER JOIN as the second join would remove this last row, which shows a freelancer without an assigned task. If there’s no task, there’s also no task type. And INNER JOIN doesn’t show non-matching rows. That’s why LEFT JOIN is needed here.

Question 4: Select Freelancer Info for Projects Due in 2024

Write a query that selects:

• All freelancers
• Their task titles
• Tasks’ due dates

Include only projects with a due date in 2024.

Dataset: Same as in the previous question.

Solution: The question wants to lure you into writing a query that uses the WHERE clause to filter the data, as shown below:

SELECT f.first_name,
	 f.last_name,
	 t.title,
	 t.due_date
FROM freelancer f
LEFT JOIN task t
ON f.id = t.freelancer_id
WHERE t.due_date > '2023-12-31';

But that’s not the correct answer. To get the required output, the filtering condition in WHERE has to be moved to a joining condition, like this:

SELECT f.first_name,
	 f.last_name,
	 t.title,
	 t.due_date
FROM freelancer f
LEFT JOIN task t
ON f.id = t.freelancer_id AND t.due_date > '2023-12-31';

Explanation: In the first query, using WHERE would return only the data for the tasks with the due date in 2024. That would exclude all the freelancers that don’t have an assigned task, but also the tasks that don’t have – for various reasons – a due date.

So, instead, we move the filtering condition to the ON clause. The first condition joins the tables on the freelancer ID. The second condition is added using the keyword AND. This way, you include all the freelancers but filter out the projects that were due in 2023.

Output: Here’s the correct output:

first_name	last_name	title	due_date
Bob	Franklin	Working With LEFT JOIN in SQL	2024-03-01
Dionne	Ravanelli	What Does a Data Analyst Do?	2024-02-01
Francois	Cousteau	Promote Working With Pandas in Python	2024-01-18
Bob	Franklin	Python Libraries You Should Know	2024-02-15
Dionne	Ravanelli	Using COUNT in SQL	2024-02-15
Emma	Biesa	NULL	NULL
Marek	Lewandowski	NULL	NULL

Despite Emma Biesa having a project titled ‘Filtering Data in SQL’, its due date is NULL, so the value in the column title is also NULL. In other words, Emma Biesa’s project doesn’t match the joining condition.

On the other hand, the output looks the same for Marek Lewandowski. This time, it’s because Marek doesn’t have a project assigned at all.

Question 5: Show All Employees and Their Managers

Dataset: The question provides you with the table employees. Here’s the script.

The table is a list of employees.

id	first_name	last_name	manager_id
1	John	Borisov	2
2	Linda	Johnson	8
3	Frank	Ranieri	NULL
4	Nina	Bowie	1
5	Tamara	Felipe	NULL
6	Simon	Fyodorov	8
7	Lana	Hopkins	NULL
8	Tom	Bonfa	1
9	Maria	Fox	1
10	Victor	Ivanchich	2

Solution: Since there’s only one table, you need to show you know that a table can be joined with itself. In other words, solve the question by applying a self-join.

This is done in the following way:

SELECT e.first_name AS employee_first_name, 
	 e.last_name AS employee_last_name, 
	 m.first_name AS manager_first_name,
	 m.last_name AS manager_last_name
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.id;

Explanation: Self-join is simply a table that’s joined with itself. Basically, by giving one table different aliases, you’re making SQL think you’ve joined two different tables.

Our ‘first’ table has the alias e. We will use it to show employees' names.

The ‘second’ joined table’s alias is m; it will serve to show managers’ names.

In this case, you need to join them using LEFT JOIN because the question requires you to list all the employees. This also includes employees who have no managers. If you used INNER JOIN, you would get only employees that have a manager.

The table is self-joined on the condition that the manager’s ID is equal to the employee’s ID. That’s how you get the managers’ names of each employee.

Output: Here’s the list of employees and their superiors:

employee_first_name	employee_last_name	manager_first_name	manager_last_name
John	Borisov	Linda	Johnson
Linda	Johnson	Tom	Bonfa
Frank	Ranieri	NULL	NULL
Nina	Bowie	John	Borisov
Tamara	Felipe	NULL	NULL
Simon	Fyodorov	Tom	Bonfa
Lana	Hopkins	NULL	NULL
Tom	Bonfa	John	Borisov
Maria	Fox	John	Borisov
Victor	Ivanchich	Linda	Johnson

NULLs as manager’s names mean the respective employee doesn’t have a superior.

Question 6: Show Therapists and Their First and Second Languages

Write a query that returns all therapists with their first and second languages.

Dataset: This dataset is from a collective psychotherapy practice intended for ex-pats. Several therapists provide therapy, and they each do that in two languages.

The list of the languages is in the table language. Here’s the script.

id	language_name
1	English
2	Dutch
3	Russian
4	Polish
5	Croatian

The list of therapists can be found in the table therapist. Here’s the script.

id	first_name	last_name	first_language_id	second_language_id
1	Maya	Hoekstra	2	1
2	Lana	Mayakovski	3	1
3	Marija	Abramović	5	2
4	Jan	Nowak	4	1
5	Francis	Gordon	1	2

Solution: One of the many SQL interview questions for experienced users, this task requires you to showcase skills in joining three tables. However, here one table is joined twice. You need to recognize this, because the table therapist references the table language in two columns: first_language_id and second_language_id.

The solution should look like this:

SELECT t.first_name,
	 t.last_name,
	 fl.language_name AS first_language_name,
	 sl.language_name AS second_language_name
FROM therapist t
JOIN language fl
ON t.first_language_id = fl.id
JOIN language sl
ON t.second_language_id = sl.id;

Explanation: First, we join the table therapist with the table language, the latter being given the alias fl (as in ‘first language’). We’ll use it to show the therapist’s first language, i.e. their native language. That’s why the join condition looks for where the first language ID is the same as the language ID. This will result in the name of the first language being shown.

In the next step, we again join the table language. This time, it has the alias sl for ‘second language’. The join takes the second language ID and looks for it in language. That’s how we get the name of the second language.

To show the first and second language, we select the language_name column – once from the fl ‘table’ and the second time from the sl ‘table’ – and give the columns appropriate names.

Output: Here’s the output:

first_name	last_name	first_language_name	second_language_name
Jan	Nowak	Polish	English
Lana	Mayakovski	Russian	English
Maya	Hoekstra	Dutch	English
Francis	Gordon	English	Dutch
Marija	Abramović	Croatian	Dutch

Question 7: Show the Number of Freelancers with Assigned Tasks

Dataset: The freelancer dataset used in Questions 1, 3, and 4.

Solution: This tricky interview question leads you to use the COUNT() aggregate function. It seems very easy, with a simple query that uses only one table. But, the question wants you to be hasty and write the following query:

SELECT COUNT(freelancer_id) AS number_of_working_freelancers
FROM task;

However, you need to show you’re smarter than that and write a query that uses COUNT(DISTINCT freelancer_id) instead of COUNT(freelancer_id).

SELECT COUNT(DISTINCT freelancer_id) AS number_of_working_freelancers
FROM task;

Explanation: Why is the first query wrong? Well, COUNT(freelancer_id) will count every instance of a freelancer's ID. This means it will also count duplicates as another freelancer. (Remember, each freelancer can have multiple tasks.)

To avoid this, just add DISTINCT in this expression. This will eliminate duplicates – i.e. each freelancer will be counted only once.

Output: The first query will return this:

number_of_working_freelancers
8

You know that’s wrong because you know your data. The table freelancer has only five freelancers listed, so it can’t be true that more freelancers are working than there are freelancers.

So, the correct output is the one below. There are four freelancers because we know one is unassigned, i.e. he’s not working.

number_of_working_freelancers
4

Question 8: Show the Number of Tasks by Task Type and Subtype

Dataset: Same as above.

Solution: Here, you must recognize that you need to use an aggregate function and group the output by two columns.

SELECT task_type,
	 task_subtype,
	 COUNT(*) AS number_of_tasks
FROM task_category tc
JOIN task t
ON tc.id = t.task_category_id
GROUP BY task_type, task_subtype;

Explanation: To get the output, you need to join the tables task_category and task on the task category ID.

Then, select the task type and subtype, and use COUNT(*), which will simply count the number of rows, which equals the number of tasks. Each row is one task.

After that, use GROUP BY to group data by task type. However, the question asks you to aggregate data on the task subtype level, too, so you need to add it in GROUP BY. All the columns in GROUP BY must be separated by a comma.

Output: The ‘Social media post’ task type appears only once, as there are no other subtypes in the active tasks.

On the other hand, the ‘Blog article’ task type appears three times, each with a different task subtype. The number_of_tasks column represents the number of tasks per subtype.

task_type	task_subtype	number_of_tasks
Social media post	LinkedIn	2
Blog article	SQL	3
Blog article	Python	2
Blog article	Career	1

Question 9: Show the Number of Active Tasks by Task Type and Subtype

Write a query that shows the number of active tasks by task type and subtype.

Include only those categories with more than two tasks.

Dataset: Same as above.

Solution: This common SQL interview question will test if you recognize that you need to use HAVING instead of WHERE to filter the output. You might want to solve the question like this:

SELECT task_type,
	 task_subtype,
	 COUNT(*) AS number_of_tasks
FROM task_category tc
JOIN task t
ON tc.id = t.task_category_id
WHERE COUNT(*) > 2
GROUP BY task_type, task_subtype;

That’s wrong, so you need to replace WHERE with HAVING:

SELECT task_type,
	 task_subtype,
	 COUNT(*) AS number_of_tasks
FROM task_category tc
JOIN task t
ON tc.id = t.task_category_id
GROUP BY task_type, task_subtype
HAVING COUNT(*) > 2;

Explanation: This query is basically the same as the one from the previous question. The additional requirement is to show only task types and subtypes with more than two active tasks.

The first query won’t return anything except an error saying aggregate functions can’t be used in WHERE. That’s, of course, because WHERE filters data before aggregation.

So you first need to aggregate data using COUNT(*) to find the number of active tasks by type and subtype. Only after that can you look for those categories with more than two tasks.

In other words, you must use HAVING, as it filters data after aggregation. You simply use the aggregation from the column number_of_tasks and state a condition that the count must be greater than two.

Output:

task_type	task_subtype	number_of_tasks
Blog article	SQL	3

Question 10: What’s Wrong with This Query?

Dataset: Same as above.

Solution: The question gives you a query:

SELECT first_name,
	 last_name,
	 task_type,
	 task_subtype,
	 COUNT(task_category_id) AS task_count
FROM freelancer f
JOIN task t
ON f.id = t.freelancer_id
JOIN task_category tc
ON t.task_category_ID = tc.id
GROUP BY first_name, last_name, task_type
ORDER BY last_name;

Your answer should be that this query won’t work because the column task_subtype is not listed in the GROUP BY clause. The corrected query should look like this:

SELECT first_name,
	 last_name,
	 task_type,
	 task_subtype,
	 COUNT(task_category_id) AS task_count
FROM freelancer f
JOIN task t
ON f.id = t.freelancer_id
JOIN task_category tc
ON t.task_category_ID = tc.id
GROUP BY first_name, last_name, task_type, task_subtype
ORDER BY last_name;

Explanation: Why must the column task_subtype appear in GROUP BY? The rule in SQL is that all the columns (except those containing aggregate functions) must appear in GROUP BY. This is something you should know and be able to recognize in the query immediately.

Output: The corrected output will now work and return the following result. It shows freelancers and the number of their tasks by type and subtype.

first_name	last_name	task_type	task_subtype	task_count
Emma	Biesa	Blog article	Python	1
Emma	Biesa	Blog article	SQL	1
Francois	Cousteau	Social media post	LinkedIn	2
Bob	Franklin	Blog article	Python	1
Bob	Franklin	Blog article	SQL	1
Dionne	Ravanelli	Blog article	Career	1
Dionne	Ravanelli	Blog article	SQL	1

Question 11: Show All Freelancers and the Number of Their Tasks

Dataset: Same as above.

Solution: In this question, you could easily be drawn to writing a query that uses COUNT(*) to find the number of tasks, like so:

SELECT first_name,
	 last_name,
	 COUNT(*) AS task_count
FROM freelancer f
LEFT JOIN task t
ON f.id = t.freelancer_id
GROUP BY first_name, last_name;

Yes, you wisely used LEFT JOIN to return freelancers without a task. However, you should use COUNT(task_category_id) instead of COUNT(*) …

SELECT first_name,
	 last_name,
	 COUNT(task_category_id) AS task_count
FROM freelancer f
LEFT JOIN task t
ON f.id = t.freelancer_id
GROUP BY first_name, last_name;

… right?

Explanation: Don’t fall for that trick! I’m sure you’re aware that COUNT(*) shouldn’t be used in combination with LEFT JOIN.

You use LEFT JOIN to include freelancers without the task. Those freelancers will have no matching values in the right table, so they will be shown as NULL. Unfortunately, COUNT(*) doesn’t ignore NULLs, so they will be counted as regular values.

Instead, you need to use COUNT(task_category_id). This way, you will count only non-NULL values.

Output: Take a look at the output of the first (incorrect) query:

first_name	last_name	task_count
Dionne	Ravanelli	2
Marek	Lewandowski	1
Emma	Biesa	2
Bob	Franklin	2
Francois	Cousteau	2

Marek Lewandowski has one task. But we know this can’t be right, as he has no tasks assigned. The output shows the count of one because COUNT(*) counted the NULL value (non-matching row).

The output of the solution query rightly shows that Marek’s task count is zero:

first_name	last_name	task_count
Dionne	Ravanelli	2
Marek	Lewandowski	0
Emma	Biesa	2
Bob	Franklin	2
Francois	Cousteau	2

Question 12: Show the Number of Completed Tasks by Completion Date

Write a query that shows the number of completed tasks by completion date. Include NULLs as a separate date category.

Dataset: Same as above.

Solution: This question tries to trick you into thinking that you somehow need to explicitly state a condition where all the tasks without the completion date will be counted together under the NULL category as a date.

But the solution is simpler than you think:

SELECT completed_date,
	 COUNT(id) AS completed_task_count
FROM task
GROUP BY completed_date
ORDER BY completed_date ASC;

Explanation: As you can see, the above query doesn’t refer to NULLs in any way. It simply selects the completion date and uses COUNT() on the task ID column to count the number of completed tasks.

Of course, the output needs to be grouped by the completion date. It is also sorted from the oldest to the newest date, which is not necessary but looks nicer.

By writing such a query, you show an understanding that the NULL values are not counted separately. All the NULL values will be shown as one category – NULL.

Output: As you can see, all the tasks without the completion date are shown in one row:

completed_date	completed_task_count
2023-12-15	1
2023-12-20	1
2024-01-18	1
2024-02-10	1
2024-02-15	2
NULL	2

Question 13: Show Employees with Their Departments and Salaries

Write a query that shows employees, their departments, and their salaries.

Include only employees with a salary lower than their department average.

Dataset: This SQL interview question uses the table salaries. You can find the script here.

id	first_name	last_name	department	salary
1	Benny	Gilhespy	Sales	5,293.47
2	Benetta	Featherstonhaugh	Engineering	2,214.55
3	Karla	Stiell	Sales	2,070.45
4	Sissie	Seabon	Accounting	5,077.42
5	Genna	Beche	Accounting	7,451.65
6	Kirsten	Fernandez	Engineering	7,533.13
7	Pen	Fredy	Sales	7,867.54
8	Tish	Calderbank	Sales	4,103.19
9	Gallard	Philipet	Accounting	7,220.06
10	Walsh	Kleinholz	Accounting	4,000.18
11	Carce	Wilkenson	Accounting	3,991.00
12	Tami	Langrish	Sales	5,588.34
13	Shayne	Dearn	Engineering	2,785.92
14	Merla	Ingilson	Engineering	2,980.36
15	Keely	Patifield	Sales	2,877.92

Solution: The tricky part here is to recognize that the query can be very short if you know how to use correlated subqueries.

It should be done like this:

SELECT id, 
	 first_name,
	 last_name,
	 department,
	 salary
FROM salaries s1
WHERE salary < (SELECT AVG(salary)
				FROM salaries s2
				WHERE s1.department = s2.department);

Explanation: So, the query first lists all the required columns from the table salaries. I’ve given the table an alias, s1.

Then, I use the WHERE clause to compare each employee’s salary with the departmental average. The departmental average is calculated in the special type of a subquery – a correlated subquery.

What’s so special about it? Well, this subquery is correlated because it references the data from the main query. This happens in the WHERE clause of a subquery: the department from the s1 table (appearing in the main query) has to be the same as the department in the s2 table, which appears in the subquery. This condition will enable the AVG() aggregate function to calculate the departmental average of the department where this particular employee works.

Output: The table below shows only employees whose salaries are below the average of their respective departments' salaries.:

id	first_name	last_name	department	salary
2	Benetta	Featherstonhaugh	Engineering	2,214.55
3	Karla	Stiell	Sales	2,070.45
4	Sissie	Seabon	Accounting	5,077.42
8	Tish	Calderbank	Sales	4,103.19
10	Walsh	Kleinholz	Accounting	4,000.18
11	Carce	Wilkenson	Accounting	3,991.00
13	Shayne	Dearn	Engineering	2,785.92
14	Merla	Ingilson	Engineering	2,980.36
15	Keely	Patifield	Sales	2,877.92

Question 14: What’s the Difference Between UNION and UNION ALL?

Answer: To answer this, you obviously must know the difference between the two most common set operators in SQL.

Both operators vertically merge the outputs of two or more queries. UNION does that by excluding duplicate rows. In other words, if the same rows appear in both queries, they will be shown only once. You can think about it as DISTINCT in the set operators' world.

On the other hand, UNION ALL shows all the rows from both queries, including duplicates. You can read more about the difference between UNION and UNION ALL in our guide.

Question 15: Show Selected Books with Their Author and Subtitle

Write a query that selects a book’s author, title, and subtitle – but only for those books where the subtitle includes the word ‘woman’. Include books without subtitles.

Dataset: The table used in this example is books, and the script is here.

id	first_name	last_name	department	salary
2	Benetta	Featherstonhaugh	Engineering	2,214.55
3	Karla	Stiell	Sales	2,070.45
4	Sissie	Seabon	Accounting	5,077.42
8	Tish	Calderbank	Sales	4,103.19
10	Walsh	Kleinholz	Accounting	4,000.18
11	Carce	Wilkenson	Accounting	3,991.00
13	Shayne	Dearn	Engineering	2,785.92
14	Merla	Ingilson	Engineering	2,980.36
15	Keely	Patifield	Sales	2,877.92

Solution: The straightforward part here is that you need to look for the word ‘woman’ in the subtitle. However, how do you also include books without subtitles – i.e. with NULL values?

The answer is you need to explicitly handle NULLs to include them in the output, like this:

SELECT 
  author,
  title,
  subtitle
FROM books
WHERE subtitle ILIKE '%woman%' OR subtitle IS NULL;

Explanation: Your answer includes two conditions in WHERE. The first condition looks for the word ‘woman’ in the subtitle. You do that either using LIKE (if your database is case-insensitive) or ILIKE (if your database is case-sensitive, like PostgreSQL). To look for the word anywhere in a string, you need to surround it with ‘%’. Since you’re looking for a string, all that must be written in single quotes.

Now, you can add another filtering condition where you state that the subtitle must be NULL using the IS NULL operator. The two conditions are joined using the OR keyword, as they can’t be satisfied at the same time: if there’s no subtitle, it can’t contain the word ‘woman’.

Output: Here’s the output showing all the data that satisfies either condition:

author	title	subtitle
Miljenko Jergović	Sarajevo Marlboro	NULL
Tilar J. Mazzeo	Irena's Children	The Extraordinary Story of the Woman Who Saved 2,500 Children from the Warsaw Ghetto
Olga Tokarczuk	Primeval and Other Times	NULL
Thomas Hardy	Tess of the d' Urbevilles	A Pure Woman Faithfully Presented
Sylvia Plath	Ariel	NULL
Toni Morrison	Jazz	NULL
Haben Girma	Haben	The Deafblind Woman Who Conquered Harvard Law

More Interview Resources for Experienced SQL Users

These 15 exercises cover some of the most common ‘trick’ SQL interview questions for experienced users. Having all these solutions should make you aware of the traps set up for you and how to avoid them.

But you shouldn’t stop now! There’s no such thing as too much preparation for a job interview. So, I recommend looking at an additional 25 advanced SQL query examples or another 27 advanced SQL interview questions.

 

You should also practice what you’ve learned here. Here are some ideas about practicing advanced SQL with our courses and some advanced SQL practice exercises to get you started.

Use this in conjunction with our Advanced SQL track and Advanced SQL Practice track, and you’ll be well-prepared for your next job interview!