Q# Expected Solution Key Concepts Tested
1 SELECT [Link] AS Emp, [Link] AS Mgr Self-joins, Aliasing
FROM Employees e JOIN Employees m
ON e.manager_id = m.emp_id WHERE
[Link] > [Link];
2 SELECT d.dept_name, AVG([Link]) GROUP BY vs HAVING
FROM Employees e JOIN Departments d
ON e.dept_id = d.dept_id GROUP BY
d.dept_name HAVING COUNT(e.emp_id)
> 3;
3 SELECT email FROM Subscribers GROUP Identifying duplicates
BY email HAVING COUNT(email) > 1;
4 SELECT [Link] FROM Employees e JOIN Logical comparison
Employees m ON e.manager_id =
m.emp_id WHERE e.hire_date <
m.hire_date;
5 UPDATE Employees SET salary = salary * UPDATE with
1.10 WHERE dept_id = (SELECT dept_id Subquery/Date math
FROM Departments WHERE dept_name =
'Sales') AND hire_date <
DATE_SUB(CURDATE(), INTERVAL 1
YEAR);
Question 1: Self-Joins
Goal: Find employees who earn more than their managers.
The Query:
SELECT
[Link] AS Employee_Name,
[Link] AS Manager_Name
FROM Employees e
JOIN Employees m ON e.manager_id = m.emp_id
WHERE [Link] > [Link];
The Logic:
Since both the employee and the manager are in the same Employees table, you have to join
the table to itself. We treat alias e as the employee and alias m as the manager. The link is
e.manager_id = m.emp_id.
Interviewer Tip: They are checking if you understand that a table can reference itself and if
you can manage aliases correctly.
Question 2: Aggregate Functions & Filtering
Goal: Average salary per department for departments with > 3 employees.
The Query:
SELECT
d.dept_name,
AVG([Link]) AS average_salary
FROM Employees e
JOIN Departments d ON e.dept_id = d.dept_id
GROUP BY d.dept_name
HAVING COUNT(e.emp_id) > 3;
The Logic:
● GROUP BY: Organizes the data by department.
● HAVING: This is the "intermediate" part. You cannot use WHERE with aggregate
functions (like COUNT). You must use HAVING to filter after the grouping is done.
Interviewer Tip: This tests the "Execution Order." Beginners often try to put the COUNT in a
WHERE clause, which will throw an error. Question 3: Handling Duplicates
Goal: Identify duplicate email addresses.
The Query:
SELECT email
FROM Subscribers
GROUP BY email
HAVING COUNT(email) > 1;
The Logic:
By grouping by the email column, any email that appears more than once will have a count
higher than 1.
Interviewer Tip: This is a classic "Data Cleaning" question. If you want to show off, you could
mention that using DISTINCT isn't enough here because DISTINCT shows unique values, while
HAVING COUNT > 1 specifically targets the "bad" data.
Question 4: Date Logic (Advanced Filtering)
Goal: Employees hired before their managers.
The Query:
SELECT [Link]
FROM Employees e
JOIN Employees m ON e.manager_id = m.emp_id
WHERE e.hire_date < m.hire_date;
The Logic:
In SQL, comparing dates is like comparing numbers. An "earlier" date is "less than" a "later"
date. Again, we use a self-join to align the employee's hire date next to their manager's hire
date.
Interviewer Tip: This tests your ability to handle Date data types and logic.
Question 5: DML & Constraints
Goal: 10% raise for Sales staff with > 1 year of tenure.
The Query:
UPDATE Employees
SET salary = salary * 1.10
WHERE dept_id = (SELECT dept_id FROM Departments WHERE dept_name = 'Sales')
AND hire_date < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);
The Logic:
● Subquery: We use a subquery to find the dept_id for 'Sales' because the Employees
table usually only stores the ID, not the name.
● Date Math: DATE_SUB(CURDATE(), INTERVAL 1 YEAR) calculates exactly one year ago
today. Any hire date smaller (older) than that qualifies.
Interviewer Tip: They want to see if you can safely update data using filters and if you know
how to perform basic date arithmetic.
Question 5B: The Basic Join & Filter
Scenario: You have two tables:
• Students: student_id, first_name, last_name, city
• Grades: student_id, subject, score
Task: Write a query to list the first_name and score of all students who live in 'New York' and
scored higher than 80 in 'Math'.
The Answer (Step-by-Step)
The Query:
SQL
SELECT s.first_name, [Link]
FROM Students s
JOIN Grades g ON s.student_id = g.student_id
WHERE [Link] = 'New York'
AND [Link] = 'Math'
AND [Link] > 80;
The Logic:
1. JOIN: You need data from two different tables, so you link them using their common
column, student_id.
2. WHERE: You apply three specific filters (City, Subject, and Score).
3. SELECT: You only pull the specific columns requested.