SQL Interview

Master Notes

EmployeeDetails & EmployeeSalary Tables

Complete Query Reference with Concepts, Alternatives & Extra Interview

Questions

■ Table Schemas

EmployeeDetails EmployeeSalary

EmpId, FullName, ManagerId, DateOfJoining, City EmpId, Project, Salary, Variable

■ SECTION 1 — BASIC SELECT & FILTERING

Q1. Print all records from EmployeeDetails table.

Concept: SELECT * fetches every column and every row from a table. Use sparingly on large tables.
■ Answer
SELECT * FROM EmployeeDetails;
✦ Best Practice — list columns explicitly
SELECT EmpId, FullName, ManagerId, DateOfJoining, City FROM EmployeeDetails;
■ In interviews always mention that SELECT * is bad for production; name columns explicitly.

Q2. Print details of the employee whose Employee ID is 1.

Concept: WHERE clause filters rows. = is exact match for numbers; quotes needed for strings.
■ Answer
SELECT * FROM EmployeeDetails WHERE EmpId = 1;
■ EmpId is numeric — no quotes needed. For string columns use single quotes: city = 'Jhansi'.

Q3. Print details of employees whose ManagerId is 100 AND City is Jhansi.
Concept: AND operator: BOTH conditions must be true for a row to be included.
■ Answer
SELECT * FROM EmployeeDetails WHERE ManagerId = 100 AND City = 'Jhansi';
■ AND narrows results (fewer rows). OR broadens results (more rows). Know the difference!

Q4. Print all distinct projects from EmployeeSalary.

Concept: DISTINCT removes duplicate values, returning only unique entries.
■ Answer
SELECT DISTINCT Project FROM EmployeeSalary;
✦ Alternative / Simpler Version
SELECT Project FROM EmployeeSalary GROUP BY Project;
■ DISTINCT works on combinations of columns too: SELECT DISTINCT col1, col2 ...

Q5. Fetch count of employees working in Project P1.

Concept: COUNT(*) counts all rows matching the WHERE condition.
■ Answer
SELECT COUNT(*) AS EmpCount FROM EmployeeSalary WHERE Project = 'P1';
✦ Alternative / Simpler Version
SELECT Project, COUNT(*) AS EmpCount FROM EmployeeSalary WHERE Project = 'P1' GROUP BY
Project;
■ COUNT(*) includes NULLs; COUNT(column) ignores NULLs.

Q6. Find maximum, minimum, and average salary.

Concept: Aggregate functions — MAX, MIN, AVG — compute a single value over a set of rows.
■ Answer
SELECT MAX(Salary) AS MaxSal, MIN(Salary) AS MinSal, AVG(Salary) AS AvgSal FROM
EmployeeSalary;
■ AVG ignores NULL values. Use COALESCE(Salary,0) if NULLs should count as 0.

Q7. Find employee IDs whose salary is between 9000 and 15000.
Concept: BETWEEN is inclusive on both ends: value >= lower AND value <= upper.
■ Answer
SELECT EmpId, Salary FROM EmployeeSalary WHERE Salary BETWEEN 9000 AND 15000;
✦ Alternative / Simpler Version
SELECT EmpId, Salary FROM EmployeeSalary WHERE Salary >= 9000 AND Salary <= 15000;

Q8. Print all Employee IDs who live in Jhansi OR whose ManagerId is 100.
Concept: OR operator: at least ONE condition must be true.
■ Answer
SELECT EmpId FROM EmployeeDetails WHERE City = 'Jhansi' OR ManagerId = 100;

Q9. Fetch employees who work on projects other than P2.

Concept: NOT / != / <> negates a condition.
■ Answer
SELECT EmpId FROM EmployeeSalary WHERE Project != 'P2';
✦ Three equivalent ways to write NOT EQUAL
SELECT EmpId FROM EmployeeSalary WHERE NOT Project = 'P2'; -- or: SELECT EmpId FROM
EmployeeSalary WHERE Project <> 'P2';
 ■ SECTION 2 — STRING FUNCTIONS

Q10. Display total salary (Salary + Variable) for each employee.

Concept: Arithmetic operators work directly in SELECT. Alias with AS for readable output.
■ Answer
SELECT EmpId, Salary + Variable AS TotalSalary FROM EmployeeSalary;
■■ If Variable is NULL, Salary + NULL = NULL. Use COALESCE: Salary + COALESCE(Variable,0).

Q11. Display employee names where the 2nd letter of the name is "a".
Concept: LIKE with wildcards: _ matches exactly one character, % matches zero or more.
■ Answer
SELECT FullName FROM EmployeeDetails WHERE FullName LIKE '_a%';
■ _ = any single char. % = any sequence. LIKE 'a%' → starts with a. LIKE '%a' → ends with a.

Q16. Fetch employee full names and replace space with "-".
Concept: REPLACE(string, old, new) substitutes all occurrences of old with new.
■ Answer
SELECT REPLACE(FullName, ' ', '-') AS FormattedName FROM EmployeeDetails;
■ REPLACE is case-sensitive in most databases.

Q17. Display EmpId and ManagerId concatenated together.

Concept: CONCAT joins multiple strings or values into one.
■ Answer
SELECT CONCAT(EmpId, '-', ManagerId) AS NewId FROM EmployeeDetails;
✦ Alternative / Simpler Version
-- MySQL pipe syntax not standard; use CONCAT SELECT CONCAT(EmpId, ManagerId) AS NewId
FROM EmployeeDetails;
■ Always add a separator (dash/space) between IDs for readability.

Q19. Fetch only the first name (string before the first space).
Concept: MID/SUBSTRING extracts part of a string. LOCATE finds position of a character.
■ Answer
SELECT MID(FullName, 1, LOCATE(' ', FullName) - 1) AS FirstName FROM EmployeeDetails;
✦ Cleaner MySQL-specific alternative
SELECT SUBSTRING_INDEX(FullName, ' ', 1) AS FirstName FROM EmployeeDetails;
■ SUBSTRING_INDEX is the cleanest MySQL way to split on a delimiter.

Q20. Uppercase employee names and lowercase city values.

Concept: UPPER() and LOWER() change character case of string columns.
■ Answer
SELECT UPPER(FullName) AS Name, LOWER(City) AS City FROM EmployeeDetails;

Q21. Update employee names by removing leading and trailing spaces.

Concept: LTRIM removes left spaces, RTRIM removes right spaces, TRIM removes both.
■ Answer
UPDATE EmployeeDetails SET FullName = TRIM(FullName);
✦ Explicit left + right trim (same result)
UPDATE EmployeeDetails SET FullName = LTRIM(RTRIM(FullName));

Q44. Produce output as "FullName(Role)".

Concept: CONCAT combines text columns and literal strings.
■ Answer
SELECT CONCAT(FullName, '(', Role, ')') AS EmployeeWithRole FROM EmployeeDetails;

Q45. Display total number of characters in employee name.

Concept: LENGTH() returns byte count; CHAR_LENGTH() returns character count (use for Unicode).
■ Answer
SELECT FullName, CHAR_LENGTH(TRIM(FullName)) AS NameLength FROM EmployeeDetails;
■ Always TRIM first to exclude accidental leading/trailing spaces from count.
■ SECTION 3 — DATE & TIME FUNCTIONS

Q22. Fetch all employees who joined in the year 2022.

Concept: YEAR(date) extracts the year part. Compare with = for exact year.
■ Answer
SELECT * FROM EmployeeDetails WHERE YEAR(DateOfJoining) = 2022;
■ Don't wrap 2022 in quotes — it is a number. Both work in MySQL but quotes invite bugs.

Q34. Print employees whose joining date is NOT in the last year.
Concept: Date arithmetic with INTERVAL moves a date backward or forward.
■ Answer
SELECT * FROM EmployeeDetails WHERE DateOfJoining < CURDATE() - INTERVAL 1 YEAR;
✦ Alternative / Simpler Version
SELECT * FROM EmployeeDetails WHERE DATEDIFF(CURDATE(), DateOfJoining) > 365;

Q36. Print all employees in the company for more than 4 years.
Concept: TIMESTAMPDIFF(YEAR, ...) gives the exact difference in years including month precision.
■ Answer
SELECT * FROM EmployeeDetails WHERE TIMESTAMPDIFF(YEAR, DateOfJoining, CURDATE()) > 4;
✦ Simpler but less precise (ignores month/day)
SELECT * FROM EmployeeDetails WHERE YEAR(CURDATE()) - YEAR(DateOfJoining) > 4;
■■ TIMESTAMPDIFF is more accurate; the YEAR subtraction trick can be off by one near anniversaries.

Q37. Print all employees with total years of service.

Concept: Computed columns can be aliased and included alongside all other columns.
■ Answer
SELECT *, TIMESTAMPDIFF(YEAR, DateOfJoining, CURDATE()) AS ServiceYears FROM
EmployeeDetails;

Q47. Display all employees who joined in January.

Concept: MONTHNAME() returns the full month name as a string.
■ Answer
SELECT * FROM EmployeeDetails WHERE MONTHNAME(DateOfJoining) = 'January';
✦ Faster — uses integer comparison instead of string
SELECT * FROM EmployeeDetails WHERE MONTH(DateOfJoining) = 1;

Q49. Print total experience in Years-Months-Days format.

Concept: TIMESTAMPDIFF with different units plus modulo gives precise breakdown.
■ Answer
SELECT CONCAT( TIMESTAMPDIFF(YEAR, DateOfJoining, CURDATE()), ' Yrs ',
TIMESTAMPDIFF(MONTH, DateOfJoining, CURDATE()) % 12, ' Mos ', FLOOR(DATEDIFF(CURDATE(),
DateOfJoining) % 30), ' Days' ) AS TotalExperience FROM EmployeeDetails;

Q51. Return employees who joined in the last 11 months.

Concept: INTERVAL keyword shifts dates. >= catches everything from that point until now.
■ Answer
SELECT * FROM EmployeeDetails WHERE DateOfJoining >= CURDATE() - INTERVAL 11 MONTH;
Q53. Return employees who joined on 12-Dec OR 1-Jan.
Concept: MONTH() and DAY() extract individual components for precise date matching.
■ Answer
SELECT * FROM EmployeeDetails WHERE (MONTH(DateOfJoining) = 12 AND DAY(DateOfJoining) =
12) OR (MONTH(DateOfJoining) = 1 AND DAY(DateOfJoining) = 1);
■ SECTION 4 — JOINS & SET OPERATIONS

Q12. Fetch all EmpIds present in EITHER table (UNION).

Concept: UNION returns all distinct rows from both queries. UNION ALL keeps duplicates.
■ Answer
SELECT EmpId FROM EmployeeDetails UNION SELECT EmpId FROM EmployeeSalary;
✦ UNION ALL — keeps duplicates, faster (no de-dup step)
SELECT EmpId FROM EmployeeDetails UNION ALL SELECT EmpId FROM EmployeeSalary;
■ UNION = distinct rows only. UNION ALL = all rows including duplicates. Use ALL when duplicates are
impossible/acceptable.

Q14. Fetch EmpIds present in BOTH tables.

Concept: IN with subquery finds matching keys. INNER JOIN is the JOIN equivalent.
■ Answer
SELECT EmpId FROM EmployeeDetails WHERE EmpId IN (SELECT EmpId FROM EmployeeSalary);
✦ INNER JOIN — same result, often faster on large tables
SELECT [Link] FROM EmployeeDetails ED INNER JOIN EmployeeSalary ES ON [Link] =
[Link];

Q15. Fetch EmpIds in EmployeeDetails but NOT in EmployeeSalary.

Concept: NOT IN / LEFT JOIN ... IS NULL finds rows with no match in the second table.
■ Answer
SELECT EmpId FROM EmployeeDetails WHERE EmpId NOT IN (SELECT EmpId FROM EmployeeSalary);
✦ LEFT JOIN approach — handles NULLs better than NOT IN
SELECT [Link] FROM EmployeeDetails ED LEFT JOIN EmployeeSalary ES ON [Link] =
[Link] WHERE [Link] IS NULL;
■■ NOT IN can misbehave if the subquery contains NULL values. LEFT JOIN IS NULL is safer.

Q26. Fetch employees who are also managers.

Concept: Self-join: join a table to itself to compare rows within the same table.
■ Answer
SELECT DISTINCT [Link] FROM EmployeeDetails E INNER JOIN EmployeeDetails M ON
[Link] = [Link];
■ Self-join is a classic interview topic. The key is aliasing the same table twice (E and M here).

Q32. Order employee names and salary by salary.

Concept: Cross-table queries can use implicit join (comma) or explicit JOIN. Explicit is better.
■ Answer
SELECT [Link], [Link] FROM EmployeeDetails ED INNER JOIN EmployeeSalary ES ON
[Link] = [Link] ORDER BY [Link];
✦ Old comma-style join (equivalent but less readable)
-- Old implicit join syntax (avoid in production) SELECT FullName, Salary FROM
EmployeeDetails E, EmployeeSalary ES WHERE [Link] = [Link] ORDER BY Salary;
■ Always prefer explicit JOIN syntax over comma-style joins in interviews and production code.

Q35. Print all employees who earn above average salary.

Concept: Scalar subquery in WHERE: the inner SELECT returns one value used for comparison.
■ Answer
SELECT ED.*, [Link] FROM EmployeeDetails ED INNER JOIN EmployeeSalary ES ON [Link]
= [Link] WHERE [Link] > (SELECT AVG(Salary) FROM EmployeeSalary);
■ SECTION 5 — GROUP BY, HAVING & AGGREGATION

Q25. Fetch project-wise count of employees sorted by count descending.

Concept: GROUP BY collapses rows sharing the same value. HAVING filters groups (like WHERE for
aggregates).
■ Answer
SELECT Project, COUNT(EmpId) AS ProjectCount FROM EmployeeSalary GROUP BY Project ORDER
BY ProjectCount DESC;
■ WHERE filters ROWS before grouping. HAVING filters GROUPS after grouping. Key interview distinction!

Q33. Print total salary going from each project.

Concept: SUM() totals all values in a group. Pair with GROUP BY to get per-group totals.
■ Answer
SELECT Project, SUM(Salary) AS TotalSalary FROM EmployeeSalary GROUP BY Project;

Q38. Print total employees in each project.

Concept: COUNT(*) with GROUP BY is the standard pattern for per-group counts.
■ Answer
SELECT Project, COUNT(*) AS TotalEmployees FROM EmployeeSalary GROUP BY Project;

Q39. Return list of all managers ordered by total employees managed.

Concept: Self-aggregation: group by ManagerId to count how many employees report to each.
■ Answer
SELECT ManagerId, COUNT(*) AS NumEmployees FROM EmployeeDetails GROUP BY ManagerId ORDER
BY NumEmployees DESC;

Q41. Select average salary from each project.

Concept: AVG with GROUP BY gives per-group averages.
■ Answer
SELECT Project, AVG(Salary) AS AvgSalary FROM EmployeeSalary GROUP BY Project;

Q41b. Select projects where total salary > max of average project salaries.
Concept: Nested subquery in HAVING: compute project averages in subquery, then filter by max.
■ Answer
SELECT Project, SUM(Salary) AS TotalSalary FROM EmployeeSalary GROUP BY Project HAVING
SUM(Salary) > ( SELECT MAX(AvgSal) FROM ( SELECT AVG(Salary) AS AvgSal FROM
EmployeeSalary GROUP BY Project ) AS Averages );
■ This is a multi-level aggregation pattern — very common in senior-level interviews.

Q27. Fetch records where ManagerId appears more than once.

Concept: HAVING COUNT(...) > 1 finds duplicate values — the classic duplicate-detection pattern.
■ Answer
SELECT * FROM EmployeeDetails WHERE ManagerId IN ( SELECT ManagerId FROM EmployeeDetails
GROUP BY ManagerId HAVING COUNT(ManagerId) > 1 );
✦ Window function approach — modern SQL
-- Using window function (more modern) SELECT * FROM ( SELECT *, COUNT(*) OVER(PARTITION
BY ManagerId) AS cnt FROM EmployeeDetails ) t WHERE cnt > 1;

Q56. Print average salary from each role.

Concept: Multi-table GROUP BY: join tables first, then group by a column from one of them.
■ Answer
SELECT [Link], AVG([Link]) AS AvgSalary FROM EmployeeDetails ED INNER JOIN
EmployeeSalary ES ON [Link] = [Link] GROUP BY [Link];

Q57. Print count, min, and max salary from each role.
Concept: Multiple aggregate functions can appear in the same SELECT alongside GROUP BY.
■ Answer
SELECT [Link], COUNT([Link]) AS EmpCount, MIN([Link]) AS MinSalary, MAX([Link])
AS MaxSalary FROM EmployeeDetails ED INNER JOIN EmployeeSalary ES ON [Link] = [Link]
GROUP BY [Link];
■ SECTION 6 — SUBQUERIES, EXISTS & COMPLEX FILTERS

Q21. Fetch employee names with salary between 5000 and 10000.
Concept: Subquery in WHERE/IN: filter one table based on conditions in another.
■ Answer
SELECT FullName FROM EmployeeDetails WHERE EmpId IN ( SELECT EmpId FROM EmployeeSalary
WHERE Salary BETWEEN 5000 AND 10000 );
✦ JOIN approach — usually more efficient
SELECT [Link] FROM EmployeeDetails ED INNER JOIN EmployeeSalary ES ON [Link] =
[Link] WHERE [Link] BETWEEN 5000 AND 10000;
■ Correlated subqueries run once per row; non-correlated run once. JOIN is often faster than IN with a subquery.

Q23. Fetch all employee records that have a salary record.

Concept: EXISTS returns TRUE if the subquery produces at least one row. Stops at first match (fast).
■ Answer
SELECT * FROM EmployeeDetails E WHERE EXISTS ( SELECT 1 FROM EmployeeSalary S WHERE
[Link] = [Link] );
✦ INNER JOIN — simpler, same result
SELECT ED.* FROM EmployeeDetails ED INNER JOIN EmployeeSalary ES ON [Link] = [Link];
■ EXISTS is faster than IN when the subquery result is large because it short-circuits.

Q40. Employees serving > 2 years and NOT in projects P2 or P3.

Concept: Combine NOT IN, subquery, and date math for multi-condition filters.
■ Answer
SELECT ES.* FROM EmployeeSalary ES WHERE Project NOT IN ('P2','P3') AND EmpId IN ( SELECT
EmpId FROM EmployeeDetails WHERE TIMESTAMPDIFF(YEAR, DateOfJoining, CURDATE()) > 2 );

Q48. Return ManagerIds not present as EmpId in EmployeeDetails.

Concept: Find "external" managers — people who manage someone but are not themselves employees.
■ Answer
SELECT DISTINCT ManagerId FROM EmployeeDetails WHERE ManagerId NOT IN ( SELECT EmpId
FROM EmployeeDetails WHERE EmpId IS NOT NULL );
■■ The IS NOT NULL guard is critical — NOT IN fails silently if any value in the subquery is NULL.

Q54. Employees with salary between (min+1000) and (max-1000).

Concept: Scalar subqueries inside BETWEEN dynamically compute boundary values.
■ Answer
SELECT * FROM EmployeeSalary WHERE Salary BETWEEN (SELECT MIN(Salary) FROM
EmployeeSalary) + 1000 AND (SELECT MAX(Salary) FROM EmployeeSalary) - 1000;
■ SECTION 7 — DDL, DML, WINDOW FUNCTIONS & ADVANCED

Q28. Fetch only odd rows from EmployeeSalary.

Concept: ROW_NUMBER() is a window function that assigns sequential numbers to rows.
■ Answer
SELECT EmpId, Project, Salary FROM ( SELECT *, ROW_NUMBER() OVER(ORDER BY EmpId) AS
RowNum FROM EmployeeSalary ) T WHERE RowNum % 2 = 1;
■ Window functions (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD) are must-know for senior interviews!

Q29. Fetch only even EmpId rows.

Concept: MOD(n, 2) = 0 identifies even numbers. % operator is the same in MySQL.
■ Answer
SELECT * FROM EmployeeDetails WHERE MOD(EmpId, 2) = 0;
✦ Alternative / Simpler Version
SELECT * FROM EmployeeDetails WHERE EmpId % 2 = 0;

Q30. Create a new table with data and structure copied from another.
Concept: CREATE TABLE ... SELECT copies both schema and data in one statement (MySQL).
■ Answer
CREATE TABLE EmployeeSalaryBackup SELECT * FROM EmployeeSalary;
✦ Two-step approach (structure first, then data)
-- Copy structure only (no data) CREATE TABLE EmployeeSalaryBackup LIKE EmployeeSalary;
-- Then insert data separately INSERT INTO EmployeeSalaryBackup SELECT * FROM
EmployeeSalary;

Q31. Fetch top N records (top 3 salaries).

Concept: ORDER BY DESC + LIMIT N retrieves the highest N values.
■ Answer
SELECT * FROM EmployeeSalary ORDER BY Salary DESC LIMIT 3;
■ For Nth highest salary without LIMIT: use correlated subquery or DENSE_RANK.

Q31b. Find the 3rd highest salary WITHOUT TOP/LIMIT.

Concept: Classic interview query using correlated subquery counting distinct higher salaries.
■ Answer
SELECT DISTINCT Salary FROM EmployeeSalary E1 WHERE 2 = ( SELECT COUNT(DISTINCT Salary)
FROM EmployeeSalary E2 WHERE [Link] > [Link] );
✦ DENSE_RANK approach — cleaner and works for any N
-- Modern approach with DENSE_RANK window function SELECT Salary FROM ( SELECT Salary,
DENSE_RANK() OVER(ORDER BY Salary DESC) AS rnk FROM EmployeeSalary ) T WHERE rnk = 3;
■ DENSE_RANK handles ties correctly. ROW_NUMBER would skip tied salaries.

Q42. Add a new column "Role" to EmployeeDetails.

Concept: ALTER TABLE ADD COLUMN modifies an existing table structure.
■ Answer
ALTER TABLE EmployeeDetails ADD COLUMN Role VARCHAR(255);

Q43. Update Role based on Salary + Variable condition using CASE.

Concept: CASE WHEN is SQL's if-else. UPDATE with JOIN modifies based on another table.
■ Answer
UPDATE EmployeeDetails ED INNER JOIN EmployeeSalary ES ON [Link] = [Link] SET
[Link] = CASE WHEN [Link] + [Link] < 20000 THEN 'Analyst' ELSE 'Sr Analyst' END;
■ CASE WHEN can also be used in SELECT for conditional display without updating data.

Q46. Display employees whose total salary > 20000 after 20% increase.
Concept: Calculated condition: check if current + 20% hike would cross a threshold.
■ Answer
SELECT ED.*, [Link], [Link] FROM EmployeeDetails ED INNER JOIN EmployeeSalary ES
ON [Link] = [Link] WHERE ([Link] * 1.20 + [Link]) > 20000 AND [Link] <
20000;
■ SECTION 8 — EXTRA INTERVIEW QUESTIONS (Must Know!)

■■ These questions are frequently asked in SQL interviews at top companies. Master them!

QEX1. Find the 2nd highest salary.

Concept: Classic Nth highest — use DENSE_RANK or subquery.
■ Answer
-- Method 1: Subquery SELECT MAX(Salary) FROM EmployeeSalary WHERE Salary < (SELECT
MAX(Salary) FROM EmployeeSalary); -- Method 2: DENSE_RANK (recommended) SELECT Salary
FROM ( SELECT Salary, DENSE_RANK() OVER(ORDER BY Salary DESC) AS rnk FROM EmployeeSalary
) T WHERE rnk = 2;

QEX2. Find employees with duplicate names.

Concept: GROUP BY + HAVING COUNT > 1 detects duplicates.
■ Answer
SELECT FullName, COUNT(*) AS cnt FROM EmployeeDetails GROUP BY FullName HAVING COUNT(*)
> 1;

QEX3. Delete duplicate rows keeping one copy.

Concept: Use ROW_NUMBER to tag duplicates, then delete tagged rows.
■ Answer
DELETE FROM EmployeeDetails WHERE EmpId NOT IN ( SELECT MIN(EmpId) FROM EmployeeDetails
GROUP BY FullName );

QEX4. Rank employees by salary within each project.

Concept: RANK / DENSE_RANK OVER (PARTITION BY ... ORDER BY ...) — window function partitioning.
■ Answer
SELECT EmpId, Project, Salary, RANK() OVER(PARTITION BY Project ORDER BY Salary DESC) AS
Rank, DENSE_RANK() OVER(PARTITION BY Project ORDER BY Salary DESC) AS DenseRank FROM
EmployeeSalary;

QEX5. Find cumulative (running) total of salary ordered by EmpId.

Concept: SUM() as a window function with ORDER BY gives running totals.
■ Answer
SELECT EmpId, Salary, SUM(Salary) OVER(ORDER BY EmpId ROWS UNBOUNDED PRECEDING) AS
RunningTotal FROM EmployeeSalary;

QEX6. Get the previous employee's salary using LAG.

Concept: LAG/LEAD access adjacent rows without a self-join.
■ Answer
SELECT EmpId, Salary, LAG(Salary, 1, 0) OVER(ORDER BY EmpId) AS PrevSalary, Salary -
LAG(Salary, 1, 0) OVER(ORDER BY EmpId) AS SalaryDiff FROM EmployeeSalary;

QEX7. Find employees who earn more than their manager.

Concept: Self-join on ManagerId to compare employee and manager salaries.
■ Answer
SELECT [Link] AS Employee, ES_E.Salary AS EmpSalary, [Link] AS Manager,
ES_M.Salary AS MgrSalary FROM EmployeeDetails E JOIN EmployeeDetails M ON [Link] =
[Link] JOIN EmployeeSalary ES_E ON [Link] = ES_E.EmpId JOIN EmployeeSalary ES_M ON
[Link] = ES_M.EmpId WHERE ES_E.Salary > ES_M.Salary;
QEX8. Pivot: show total salary per project as columns (P1, P2, ...).
Concept: Conditional aggregation with CASE WHEN creates a pivot table.
■ Answer
SELECT SUM(CASE WHEN Project = 'P1' THEN Salary ELSE 0 END) AS P1_Total, SUM(CASE WHEN
Project = 'P2' THEN Salary ELSE 0 END) AS P2_Total FROM EmployeeSalary;

QEX9. Find the department/project with the highest total salary.

Concept: ORDER BY + LIMIT 1 on an aggregated result.
■ Answer
SELECT Project, SUM(Salary) AS TotalSalary FROM EmployeeSalary GROUP BY Project ORDER BY
TotalSalary DESC LIMIT 1;

QEX10. Find employees who joined the same date as another employee.
Concept: Self-join on DateOfJoining to find matches, exclude same EmpId.
■ Answer
SELECT DISTINCT [Link], [Link] FROM EmployeeDetails A JOIN EmployeeDetails
B ON [Link] = [Link] AND [Link] <> [Link];

QEX11. What is the difference between WHERE and HAVING?

Concept: Conceptual question — very commonly asked.
■ Answer
-- WHERE filters individual ROWS before aggregation SELECT * FROM EmployeeSalary WHERE
Salary > 5000; -- HAVING filters GROUPS after aggregation SELECT Project, AVG(Salary)
FROM EmployeeSalary GROUP BY Project HAVING AVG(Salary) > 8000; -- Rule: HAVING can use
aggregate functions; WHERE cannot.

QEX12. What is the difference between RANK, DENSE_RANK, and ROW_NUMBER?

Concept: Window function distinction — asked in virtually every SQL interview.
■ Answer
SELECT EmpId, Salary, ROW_NUMBER() OVER(ORDER BY Salary DESC) AS row_num, -- Sequential;
no ties handled — 1,2,3,4 RANK() OVER(ORDER BY Salary DESC) AS rnk, -- Ties get same
rank; next rank skips — 1,1,3,4 DENSE_RANK() OVER(ORDER BY Salary DESC) AS dense_rnk --
Ties get same rank; next rank does NOT skip — 1,1,2,3 FROM EmployeeSalary;

QEX13. Find the median salary.

Concept: No native MEDIAN in MySQL; use ROW_NUMBER + COUNT trick.
■ Answer
SELECT AVG(Salary) AS Median FROM ( SELECT Salary, ROW_NUMBER() OVER(ORDER BY Salary) AS
rn, COUNT(*) OVER() AS cnt FROM EmployeeSalary ) t WHERE rn IN (FLOOR((cnt+1)/2),
CEIL((cnt+1)/2));

QEX14. Update salary of all P1 employees by 10%.

Concept: UPDATE with WHERE — simple but a common practical question.
■ Answer
UPDATE EmployeeSalary SET Salary = Salary * 1.10 WHERE Project = 'P1';

QEX15. Find employees who have not been assigned to any project (NULL project).
Concept: IS NULL checks for NULL values (= NULL does NOT work).
■ Answer
SELECT ED.* FROM EmployeeDetails ED LEFT JOIN EmployeeSalary ES ON [Link] = [Link]
WHERE [Link] IS NULL; -- These employees exist in EmployeeDetails but have NO row in
EmployeeSalary
■ SECTION 9 — QUICK REFERENCE CHEAT SHEET

Function / Clause Purpose Example

SELECT DISTINCT Unique values SELECT DISTINCT City FROM ED

WHERE Filter rows WHERE Salary > 5000

WHERE Salary BETWEEN 5000 AND

BETWEEN Range filter (inclusive)
10000

LIKE Pattern match WHERE Name LIKE 'A%'

IN / NOT IN Match list WHERE Project IN ('P1','P2')

IS NULL / IS NOT NULL NULL checks WHERE ManagerId IS NULL

GROUP BY Aggregate groups GROUP BY Project

HAVING Filter groups HAVING COUNT(*) > 1

ORDER BY Sort result ORDER BY Salary DESC

LIMIT Restrict rows LIMIT 5

INNER JOIN Matching rows only JOIN ES ON [Link] = [Link]

LEFT JOIN All left + matching right LEFT JOIN ES ON ...

UNION Combine distinct rows SELECT ... UNION SELECT ...

WHERE EXISTS (SELECT 1 FROM

EXISTS Check row existence
...)

CASE WHEN x>0 THEN 'Y' ELSE

CASE WHEN Conditional logic
'N' END

ROW_NUMBER() OVER(ORDER BY
ROW_NUMBER() Sequential row number
Salary)

RANK() OVER(ORDER BY Salary

RANK() Rank with gaps on ties
DESC)

DENSE_RANK() Rank without gaps DENSE_RANK() OVER(...)

LAG(Salary,1) OVER(ORDER BY
LAG / LEAD Previous / next row value
EmpId)

SUM(Salary) OVER(ORDER BY
SUM() OVER() Running total
EmpId)

YEAR/MONTH/DAY Extract date parts YEAR(DateOfJoining)

TIMESTAMPDIFF(YEAR,doj,CURDATE
TIMESTAMPDIFF Diff between dates
())

CONCAT Join strings CONCAT(A,'-',B)

REPLACE Replace substring REPLACE(Name,' ','-')

TRIM/LTRIM/RTRIM Remove spaces TRIM(FullName)

UPPER/LOWER Change case UPPER(FullName)

LENGTH/CHAR_LENGTH String length CHAR_LENGTH(FullName)

Function / Clause Purpose Example

COALESCE First non-NULL value COALESCE(Variable,0)

■■ Interview tip: Always think about NULL handling, indexing, and performance. Mention EXPLAIN/query plans
when discussing slow queries.

Good luck in your interview! ■ Practice every query on real data.