Comprehensive SQL Interview Questions and Answers
1. What is SQL, and why is it important in data analytics?
SQL (Structured Query Language) is a standard language for managing relational databases. It is
crucial in data analytics for retrieving, filtering, aggregating, and analyzing structured data efficiently.
Example Query:
SELECT * FROM employees WHERE department = 'Sales';
2. Difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN
SQL joins are used to combine records from multiple tables based on a related column.
- INNER JOIN: Returns only matching rows.
- LEFT JOIN: Returns all rows from the left table and matching rows from the right.
- RIGHT JOIN: Returns all rows from the right table and matching rows from the left.
- FULL OUTER JOIN: Returns all rows when there is a match in either table.
Example:
SELECT employees.name, departments.department_name FROM employees
INNER JOIN departments ON employees.department_id = departments.id;
3. Difference between WHERE and HAVING
- WHERE filters rows before aggregation.
- HAVING filters groups after aggregation.
Example:
SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) >
5;
4. How to use GROUP BY and HAVING in a query?
GROUP BY is used to group rows with similar values, and HAVING filters those groups.
Example:
SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary)
> 50000;
5. Query to find duplicate records in a table
To find duplicate records, use GROUP BY and HAVING:
SELECT name, COUNT(*) FROM employees GROUP BY name HAVING COUNT(*) > 1;
6. Retrieve unique values from a table
Use DISTINCT to return unique records:
SELECT DISTINCT department FROM employees;
7. Aggregate functions: COUNT(), SUM(), AVG(), MIN(), MAX()
- COUNT(): Counts rows.
- SUM(): Adds numeric values.
- AVG(): Calculates the average.
- MIN()/MAX(): Finds minimum/maximum values.
Example:
SELECT department, SUM(salary) FROM employees GROUP BY department;
8. Purpose of DISTINCT keyword
DISTINCT removes duplicate values from the results.
Example:
SELECT DISTINCT job_title FROM employees;
1. Query to find second-highest salary
Using LIMIT and OFFSET:
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;
2. What are subqueries?
A subquery is a query within another query.
Example:
SELECT name FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);
3. What is a Common Table Expression (CTE)?
CTEs improve query readability and recursion.
Example:
WITH dept_salary AS (SELECT department, AVG(salary) AS avg_salary FROM employees
GROUP BY department)
SELECT * FROM dept_salary WHERE avg_salary > 50000;
4. Window functions: ROW_NUMBER(), RANK(), DENSE_RANK()
- ROW_NUMBER(): Assigns unique row numbers.
- RANK(): Skips ranks for duplicates.
- DENSE_RANK(): No rank gaps for duplicates.
Example:
SELECT name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary
DESC) AS rank FROM employees;
5. UNION vs UNION ALL
- UNION: Combines results, removes duplicates.
- UNION ALL: Combines results, keeps duplicates.
Example:
SELECT name FROM employees WHERE department = 'Sales'
UNION
SELECT name FROM employees WHERE department = 'Marketing';
6. What are indexes and how they improve performance?
Indexes speed up data retrieval by optimizing searches.
Example:
CREATE INDEX idx_employee_name ON employees(name);
7. Query to calculate total sales per month
Use GROUP BY with an aggregate function:
SELECT MONTH(order_date) AS month, SUM(sales) FROM orders GROUP BY
MONTH(order_date);
1. Optimizing slow-running SQL queries
- Use indexes.
- Avoid SELECT *.
- Optimize joins and subqueries.
- Use EXPLAIN PLAN for query optimization.
2. Views in SQL
A view is a virtual table based on a query.
Example:
CREATE VIEW high_salary_employees AS SELECT * FROM employees WHERE salary > 70000;
3. Difference between stored procedure and function
- Stored Procedure: Executes multiple statements, returns nothing or multiple results.
- Function: Returns a single value, used in SQL expressions.
Example Stored Procedure:
CREATE PROCEDURE GetHighSalaries() AS SELECT * FROM employees WHERE salary >
70000;
4. Difference between TRUNCATE, DELETE, and DROP
- TRUNCATE: Deletes all rows, resets identity.
- DELETE: Deletes specific rows with WHERE.
- DROP: Deletes the table permanently.
5. Windowing functions and analytics
Used for running totals, rankings, and moving averages.
6. Using PARTITION BY and ORDER BY in window functions
Example:
SELECT name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary
DESC) AS rank FROM employees;
7. Handling NULL values with COALESCE, ISNULL
- COALESCE: Returns the first non-null value.
- ISNULL: Replaces NULL with a default value.
Example:
SELECT name, COALESCE(salary, 0) FROM employees;
