SQL Interview Question (MNC)

Challenge yourself with real-world scenarios and enhance your ability to write efficient and optimized SQL queries. This collection is perfect for self-paced learning, interview preparation, or simply brushing up on your SQL knowledge.

Q1. Print the Output using the below Source and Target tables. 

Notes: 

CREATE TABLE source(id INT, name VARCHAR(5));
CREATE TABLE target(id INT, name VARCHAR(5));

INSERT INTO source VALUES(1,'A'),(2,'B'),(3,'C'),(4,'D');
INSERT INTO target VALUES(1,'A'),(2,'B'),(4,'X'),(5,'F');

Solution:

Method 1

SELECT COALESCE(s.id,t.id) AS ID 
, CASE
WHEN t.name IS NULL 
THEN 'New in source'
WHEN s.name IS NULL 
THEN 'New  in target'
ELSE 'Mismatch'
END AS COMMENT
FROM source AS s
FULL OUTER JOIN target AS t 
ON s.id=t.id
WHERE s.name != t.name
OR s.name IS NULL 
OR t.name IS NULL;

Method 2

WITH act as (
SELECT *, 'Source' AS table_name FROM source
UNION ALL
SELECT *, 'Target' AS table_name FROM target
)
SELECT Id 
, CASE 
WHEN MIN(name) != MAX(name) THEN 'Mismatch'
WHEN MIN(table_name) = 'source' THEN 'New in source'
ELSE 'New in target'
END AS Comment
FROM act
GROUP BY id
HAVING COUNT(*) = 1
OR (COUNT(*) =2 AND MIN(name) != MAX(name));

All the best 👍