Solving 3 tricky SQL Interview Queries
This is a blog based on my YouTube video where I mentioned and solved 3 tricky SQL interview queries.
If you are planning to attend an SQL interview then be sure to solve these kind of tricky SQL Queries since these are kind of favourite questions that an interviewer might ask especially if you are 2+ years of experience.
Now, let’s straight away get into solving these queries. However, if you wish to get detailed understanding of the query written then do watch my YouTube video mentioned below:
QUERY 1: Print meaningful comments
Write an SQL query to display the correct message (meaningful message) from input comments_and_translation table.
Input:
Expected Output:
Dataset:
Download the table structure and the table data to solve this query by clicking on the below button
Solution:
The solution to this query is mentioned below. The detailed explanation of this query is mentioned in my YouTube video. Link to my YouTube video at the beginning of the blog.
-- Solution using CASE statement
SELECT CASE WHEN translation IS NULL THEN comment ELSE translation END AS output
FROM comments_and_translations;
-- Solution using a built in function
SELECT COALESCE(translation, comment) AS output
FROM comments_and_translations;
QUERY 2: Derive desired output
Using the Source and Target table, write a query to arrive at the Output table as shown in below image. Provide the solution without using subqueries.
Input & Expected Output:
SOURCE and TARGET are the input tables. OUTPUT table indicates the expected output.
Dataset:
Download the table structure and the table data to solve this query by clicking on the below button
Solution:
The solution to this query is mentioned below. The detailed explanation of this query is mentioned in my YouTube video. Link to my YouTube video at the beginning of the blog.
-- Solution using Left, Right, Inner JOIN and UNION operator
SELECT s.id, 'Mismatch' AS Comment
FROM source s
JOIN target t ON s.id = t.id AND s.name <> t.name
UNION
SELECT s.id, 'New in source' AS Comment
FROM source s
LEFT JOIN target t ON s.id = t.id
WHERE t.id IS NULL
UNION
SELECT t.id, 'New in target' AS Comment
FROM source s
RIGHT JOIN target t ON s.id = t.id
WHERE s.id IS NULL;
— Solution using FULL OUTER JOIN
select
case when t.id is null then s.id
when s.id is null then t.id
when (s.id=t.id and s.name <> t.name) then s.id
end as id
,case when t.id is null then 'New in source'
when s.id is null then 'New in target'
when (s.id=t.id and s.name <> t.name) then 'Mismatch'
end as id
from source s
full join target t on t.id=s.id
where t.id is null
or s.id is null
or (s.id=t.id and s.name <> t.name)
QUERY 3: IPL Matches
There are 10 IPL team. write an sql query such that each team play with every other team just once.
Also write another query such that each team plays with every other team twice.
Input:
Expected Output: (showing limited data only)
Dataset:
Download the table structure and the table data to solve this query by clicking on the below button
Solution:
The solution to this query is mentioned below. The detailed explanation of this query is mentioned in my YouTube video. Link to my YouTube video at the beginning of the blog.
-- Each team plays with every other team JUST ONCE.
WITH matches AS
(SELECT row_number() over(order by team_name) AS id, t.*
FROM teams t)
SELECT team.team_name AS team, opponent.team_name AS opponent
FROM matches team
JOIN matches opponent ON team.id < opponent.id
ORDER BY team;
-- Each team plays with every other team TWICE.
WITH matches AS
(SELECT row_number() over(order by team_name) AS id, t.*
FROM teams t)
SELECT team.team_name AS team, opponent.team_name AS opponent
FROM matches team
JOIN matches opponent ON team.id <> opponent.id
ORDER BY team;
