Selecting All Columns vs. Specific Columns

Question: Given an employees table with columns id, name, department, salary, hire_date, write SQL to query all employee information, and SQL to query only names and departments.

Solution:

-- Select all columns
SELECT * FROM employees;

-- Select specific columns
SELECT name, department FROM employees;

Explanation:

• SELECT * returns all columns; avoid in production (performance issues)
• Explicitly listing needed columns is best practice

Interviewer Perspective: This seems simple, but I'll follow up with "Why not use SELECT *?" to test your understanding of production best practices.

WHERE Clause Filtering

Question: Query the names and salaries of employees earning more than 50,000.

Solution:

SELECT name, salary 
FROM employees 
WHERE salary > 50000;

Explanation:

• The WHERE clause filters rows
• Supported comparison operators: =, >, <, >=, <=, <>, !=

Multiple Conditions (AND/OR)

Question: Query employees in the IT department earning over 60,000, OR any employee earning over 100,000 regardless of department.

Solution:

SELECT * 
FROM employees 
WHERE (department = 'IT' AND salary > 60000) 
   OR salary > 100000;

Explanation:

• Use parentheses to clarify precedence
• AND has higher precedence than OR; omitting parentheses may yield incorrect results

Interviewer Perspective: I'll intentionally leave out parentheses to see if the candidate catches the logic error.

IN and BETWEEN

Question: Query employees in IT, HR, or Finance departments who were hired between 2020-01-01 and 2023-12-31.

Solution:

SELECT * 
FROM employees 
WHERE department IN ('IT', 'HR', 'Finance')
  AND hire_date BETWEEN '2020-01-01' AND '2023-12-31';

Explanation:

• IN can replace multiple OR conditions for cleaner code
• BETWEEN is inclusive, including both boundary values

LIKE Pattern Matching

Question: Query employees whose names start with "J".

Solution:

SELECT * 
FROM employees 
WHERE name LIKE 'J%';

Explanation:

• % matches any number of characters
• _ matches a single character
• LIKE 'J%n' matches names starting with J and ending with n

Handling NULL Values

Question: Query employees who haven't been assigned to a department (department field is NULL).

Solution:

SELECT * 
FROM employees 
WHERE department IS NULL;

Explanation:

• NULL cannot be compared using = NULL or <> NULL
• Must use IS NULL or IS NOT NULL
• Any comparison with NULL returns NULL (neither TRUE nor FALSE)

Interviewer Perspective: This is a common mistake. Many candidates write = NULL; I'll ask "Why doesn't this work?"

ORDER BY Sorting

Question: Query all employees, sorted by salary descending, then by name ascending for equal salaries.

Solution:

SELECT * 
FROM employees 
ORDER BY salary DESC, name ASC;

Explanation:

• DESC for descending, ASC for ascending (default)
• Can sort by multiple columns; priority is left to right

LIMIT Results

Question: Query the top 3 highest-paid employees.

Solution:

SELECT * 
FROM employees 
ORDER BY salary DESC 
LIMIT 3;

Explanation:

• MySQL/PostgreSQL use LIMIT
• SQL Server uses TOP 3
• Oracle uses FETCH FIRST 3 ROWS ONLY

DISTINCT for Deduplication

Question: Query all unique departments in the company.

Solution:

SELECT DISTINCT department 
FROM employees;

Explanation:

• DISTINCT applies to the entire row, not individual columns
• SELECT DISTINCT department, salary deduplicates by the combination of both columns

Aliases (AS)

Question: Query employee names and annual salary (salary × 12), displaying column names as "Employee Name" and "Annual Salary".

Solution:

SELECT name AS "Employee Name", 
       salary * 12 AS "Annual Salary"
FROM employees;

Explanation:

• AS can be omitted
• Aliases can be used in ORDER BY
• Use double quotes for aliases with spaces

INNER JOIN

Question: Given employees and departments tables, query each employee's name and their department name.

Solution:

SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

Explanation:

• INNER JOIN returns only rows with matches in both tables
• Table aliases make SQL more concise
• Join condition specified with ON

LEFT JOIN

Question: Query all employees' names and department names, including employees without an assigned department.

Solution:

SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

Explanation:

• LEFT JOIN returns all rows from the left table; unmatched right table columns are NULL
• This is the most commonly used JOIN type in real business scenarios

Interviewer Perspective: I'll ask "What's the difference between INNER JOIN and LEFT JOIN?" and "When would you use LEFT JOIN?"

RIGHT JOIN

Question: Query all departments and their employees, including departments with no employees.

Solution:

SELECT d.department_name, e.name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

Explanation:

• RIGHT JOIN is symmetric to LEFT JOIN
• Rarely used in practice; can be rewritten as LEFT JOIN (swap table order)

FULL OUTER JOIN

Question: Query all employees and all departments, regardless of matches.

Solution:

-- PostgreSQL/SQL Server
SELECT e.name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

-- MySQL doesn't support FULL JOIN; use UNION
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
UNION
SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

Explanation:

• FULL OUTER JOIN returns all rows from both tables
• MySQL doesn't support it; use UNION to simulate

Self Join

Question: The employees table has id, name, manager_id columns. Query each employee's name and their direct manager's name.

Solution:

SELECT e.name AS employee_name, 
       m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Explanation:

• A self join joins a table to itself
• Use aliases to distinguish the two "copies"
• Common for hierarchical relationship queries

Multi-Table Joins

Question: Given employees, departments, projects tables, query employee names, department names, and project names they're involved in.

Solution:

SELECT e.name, d.department_name, p.project_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
LEFT JOIN employee_projects ep ON e.id = ep.employee_id
LEFT JOIN projects p ON ep.project_id = p.id;

Explanation:

• Can join multiple tables in sequence
• Pay attention to join order and conditions
• Many-to-many relationships require a junction table

CROSS JOIN

Question: Generate all combinations of employees and departments (Cartesian product).

Solution:

SELECT e.name, d.department_name
FROM employees e
CROSS JOIN departments d;

Explanation:

• CROSS JOIN returns the Cartesian product of two tables
• Result row count = table A rows × table B rows
• Rarely used in business; usually an unintentional error

JOIN Performance Optimization

Question: Why is a JOIN query slow? How to optimize?

Solution:

-- Ensure join columns have indexes
CREATE INDEX idx_emp_dept ON employees(department_id);
CREATE INDEX idx_dept_id ON departments(id);

-- Query only needed columns
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
WHERE e.salary > 50000;

Explanation:

• Create indexes on join columns
• Small table drives large table
• Avoid SELECT *
• Filter before joining

Interviewer Perspective: This tests practical experience. I'll follow up with "How do you analyze slow queries?"

Basic Aggregate Functions

Question: Query the total number of employees, average salary, highest salary, lowest salary, and total salary sum.

Solution:

SELECT COUNT(*) AS total_employees,
       AVG(salary) AS avg_salary,
       MAX(salary) AS max_salary,
       MIN(salary) AS min_salary,
       SUM(salary) AS total_salary
FROM employees;

Explanation:

• COUNT(*) counts rows
• COUNT(column) counts non-NULL values
• AVG automatically ignores NULL values

GROUP BY

Question: Query the number of employees and average salary for each department.

Solution:

SELECT department,
       COUNT(*) AS employee_count,
       AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

Explanation:

• GROUP BY groups by specified column
• Non-aggregated columns in SELECT must appear in GROUP BY
• Each group returns one row after grouping

HAVING for Filtering Groups

Question: Query departments with more than 5 employees.

Solution:

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

Explanation:

• WHERE filters rows; HAVING filters groups
• WHERE applies before grouping; HAVING applies after
• HAVING can use aggregate functions

Interviewer Perspective: I'll ask "What's the difference between WHERE and HAVING?"—a classic interview question.

GROUP BY Multiple Columns

Question: Query the number of employees for each department and level combination.

Solution:

SELECT department, level, COUNT(*) AS employee_count
FROM employees
GROUP BY department, level;

Explanation:

• Can group by multiple columns
• Finer grouping granularity

GROUP BY with ROLLUP

Question: Query the total salary for each department and the grand total for all departments.

Solution:

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department WITH ROLLUP;

Explanation:

• WITH ROLLUP generates subtotals and grand total rows
• Grand total row has NULL in the department column
• Supported in MySQL/SQL Server; PostgreSQL uses GROUPING SETS

Aggregate Functions and NULL

Question: The bonus column in employees has NULL values. How to calculate the average bonus?

Solution:

-- Method 1: AVG automatically ignores NULL
SELECT AVG(bonus) FROM employees;

-- Method 2: Treat NULL as 0
SELECT AVG(COALESCE(bonus, 0)) FROM employees;

-- Method 3: Only count employees with bonuses
SELECT AVG(bonus) FROM employees WHERE bonus IS NOT NULL;

Explanation:

• COALESCE(value, default) returns the first non-NULL value
• Business scenario determines how to handle NULL

DISTINCT with Aggregate Functions

Question: Count the number of distinct departments.

Solution:

SELECT COUNT(DISTINCT department) AS department_count
FROM employees;

Explanation:

• COUNT(DISTINCT column) counts unique values
• Can combine with other aggregates: SUM(DISTINCT salary)

ROW_NUMBER Ranking

Question: Rank employees by salary within each department, with ties broken by name.

Solution:

SELECT name, department, salary,
       ROW_NUMBER() OVER (
           PARTITION BY department 
           ORDER BY salary DESC, name
       ) AS rank
FROM employees;

Explanation:

• ROW_NUMBER() generates sequential numbers without duplicates
• PARTITION BY groups, similar to GROUP BY but doesn't collapse rows
• ORDER BY sorts within each partition

RANK and DENSE_RANK

Question: Query employee salary rankings, considering ties.

Solution:

SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC) AS rank_position,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_position
FROM employees;

Explanation:

• RANK(): Skips subsequent ranks on ties (1,2,2,4)
• DENSE_RANK(): Doesn't skip on ties (1,2,2,3)
• ROW_NUMBER(): Doesn't handle ties (1,2,3,4)

Interviewer Perspective: This is a high-frequency topic. I'll ask about the differences between the three ranking functions.

LAG and LEAD

Question: Query each employee's salary and the difference from the previous employee's salary.

Solution:

SELECT name, salary,
       LAG(salary) OVER (ORDER BY salary DESC) AS prev_salary,
       salary - LAG(salary) OVER (ORDER BY salary DESC) AS salary_diff
FROM employees;

Explanation:

• LAG(column, offset, default) gets value from N rows before
• LEAD(column, offset, default) gets value from N rows after
• Common for calculating period-over-period changes

SUM Window Function

Question: Calculate cumulative salary (ordered by salary descending).

Solution:

SELECT name, salary,
       SUM(salary) OVER (
           ORDER BY salary DESC
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS cumulative_salary
FROM employees;

Explanation:

• Aggregate functions can be used as window functions
• ROWS BETWEEN ... AND ... defines the window frame
• UNBOUNDED PRECEDING means the first row

NTILE Grouping

Question: Divide employees into 4 salary quartiles (top 25%, 25%-50%, 50%-75%, bottom 25%).

Solution:

SELECT name, salary,
       NTILE(4) OVER (ORDER BY salary DESC) AS salary_quartile
FROM employees;

Explanation:

• NTILE(n) divides data into n groups
• Each group has approximately equal row count
• Common for percentile analysis

Thought Process

Interviewers don't just want the right answer; they want to see how you think:

• Understand requirements: Restate the problem, confirm edge cases
• Analyze data: Ask about table structure, data volume, NULL handling
• Propose solution: Write a basic version first, then optimize
• Explain approach: Articulate why you wrote it this way

Example dialogue:

Interviewer: Query the highest-paid employee in each department.

Candidate: Let me clarify—can a department have multiple employees with the same highest salary? If so, should we return all of them or just one?

This kind of questioning demonstrates business thinking and thoroughness.

Optimization Awareness

Writing working SQL isn't hard; writing efficient SQL is. Interviewers test:

Index awareness:

-- Bad: Full table scan
SELECT * FROM employees WHERE YEAR(hire_date) = 2023;

-- Good: Uses index
SELECT * FROM employees 
WHERE hire_date >= '2023-01-01' AND hire_date < '2024-01-01';

Query optimization:

-- Bad: Subquery is slow
SELECT * FROM employees 
WHERE department_id IN (SELECT id FROM departments WHERE location = 'Beijing');

-- Good: JOIN is faster
SELECT e.* 
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
WHERE d.location = 'Beijing';

Avoid full table scans:

• Query only needed columns
• Use indexed columns in WHERE clause
• Avoid functions on indexed columns

Real-World Experience

Interviewers dig deeper to assess practical experience:

• "What's the most complex SQL query you've written?"
• "How would you optimize a query taking 10 seconds?"
• "How do you handle large data exports?"
• "What's the difference between window functions in MySQL and PostgreSQL?"

Prepare real examples—they're more convincing than memorized answers.

Edge Case Handling

Strong candidates proactively consider edge cases:

• How to handle NULL values?
• What if data is duplicated?
• What if the result set is empty?
• Will large datasets cause OOM?

-- Handle NULL values
SELECT COALESCE(department, 'Unassigned') AS department
FROM employees;

-- Handle division by zero
SELECT department, 
       SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) * 1.0 / 
       NULLIF(COUNT(*), 0) AS male_ratio
FROM employees
GROUP BY department;

Code Readability

Production SQL requires team collaboration—readability matters:

-- Bad: One line, hard to understand
SELECT e.name,d.department_name,COUNT(*) FROM employees e JOIN departments d ON e.department_id=d.id JOIN employee_projects ep ON e.id=ep.employee_id GROUP BY e.name,d.department_name HAVING COUNT(*)>2;

-- Good: Well-formatted, maintainable
SELECT 
    e.name AS employee_name,
    d.department_name AS department,
    COUNT(*) AS project_count
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
INNER JOIN employee_projects ep ON e.id = ep.employee_id
GROUP BY e.name, d.department_name
HAVING COUNT(*) > 2;

Smart Question Bank

• 500+ SQL interview questions, organized by difficulty and topic
• Each question includes standard solution, explanation, and interviewer perspective
• Supports MySQL, PostgreSQL, SQL Server, and more

AI Mock Interviews

• Realistic interview simulation with AI as the interviewer
• Follow-up questions based on your answers to test depth
• Real-time feedback with specific improvement points

Personalized Learning Path

• Diagnose your SQL level and generate a custom study plan
• Focus on weak areas, avoid ineffective repetition
• Track progress with visualized growth curves

Hands-On Practice Environment

• Online SQL editor supporting multiple databases
• Instant execution with result visualization
• Performance analysis to understand execution plans

Interview Technique Coaching

• How to answer "Why did you write it this way?"
• How to demonstrate optimization thinking
• How to handle questions you don't know

Start your SQL interview preparation journey: Try Interview AiBox Free