SQL Queries for Interview

Problem 1: Combine Two Tables

Explanation:

Write a SQL query to combine two tables, Person and Address, by PersonId, ensuring that if an address is missing, it still appears in the output.

SQL Approach:

• Use LEFT JOIN to ensure all persons appear, even if they have no address.

SQL Query:

SELECT p.FirstName, p.LastName, a.City, a.State
FROM Person p
LEFT JOIN Address a
ON p.PersonId = a.PersonId;

Example:

Input:

Person Table:
+----------+------------+-----------+
| PersonId | FirstName  | LastName  |
+----------+------------+-----------+
| 1        | John       | Doe       |
| 2        | Jane       | Smith     |
+----------+------------+-----------+

Address Table:
+----------+----------+-------+
| PersonId | City     | State |
+----------+----------+-------+
| 1        | New York | NY    |
+----------+----------+-------+

Output:

+------------+-----------+----------+-------+
| FirstName  | LastName  | City     | State |
+------------+-----------+----------+-------+
| John       | Doe       | New York | NY    |
| Jane       | Smith     | NULL     | NULL  |
+------------+-----------+----------+-------+

Edge Cases Considered:

• No address for a person → Should show NULL for city and state.
• All persons have addresses → Should still correctly join.

---

Problem 2: Second Highest Salary

Explanation:

Write a SQL query to get the second highest salary from the Employee table. If there is no second highest salary, return NULL.

SQL Approach:

• Use LIMIT & OFFSET or DISTINCT with ORDER BY.

SQL Query:

SELECT (SELECT DISTINCT Salary 
        FROM Employee 
        ORDER BY Salary DESC 
        LIMIT 1 OFFSET 1) AS SecondHighestSalary;

Example:

Input:

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

Output:

+-------------------+
| SecondHighestSalary |
+-------------------+
| 200               |
+-------------------+

Edge Cases Considered:

• Only one salary present → Should return NULL.
• All salaries are the same → Should return NULL.

---

Problem 3: Employees Earning More Than Their Managers

Explanation:

Find employees who earn more than their managers.

SQL Approach:

• Use SELF JOIN to compare employee salary with their manager’s salary.

SQL Query:

SELECT e1.Name AS Employee
FROM Employee e1
JOIN Employee e2
ON e1.ManagerId = e2.Id
WHERE e1.Salary > e2.Salary;

Example:

Input:

+----+-------+--------+----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+----------+
| 1  | Joe   | 70000  | 3        |
| 2  | Henry | 80000  | 4        |
| 3  | Sam   | 60000  | NULL     |
| 4  | Max   | 90000  | NULL     |
+----+-------+--------+----------+

Output:

+----------+
| Employee |
+----------+
| Joe      |
+----------+

Edge Cases Considered:

• Employee without a manager → Should not be included.
• All employees earn less than their managers → Should return an empty result.

---

Problem 4: Big Countries

Explanation:

Find all countries that have either a population of at least 25 million or an area of at least 3 million square km.

SQL Approach:

• Use WHERE clause to filter based on population and area.

SQL Query:

SELECT name, population, area
FROM World
WHERE population >= 25000000 OR area >= 3000000;

Example:

Input:

+---------+------------+---------+
| name    | population | area    |
+---------+------------+---------+
| USA     | 331000000  | 9834000 |
| India   | 1380000000 | 3287000 |
| Canada  | 38000000   | 9985000 |
| Japan   | 126000000  | 377975  |
+---------+------------+---------+

Output:

+---------+------------+---------+
| name    | population | area    |
+---------+------------+---------+
| USA     | 331000000  | 9834000 |
| India   | 1380000000 | 3287000 |
| Canada  | 38000000   | 9985000 |
+---------+------------+---------+

Edge Cases Considered:

• Countries meeting only one condition → Should still be included.
• Countries below both thresholds → Should not appear in results.

---