SQL High-Frequency Interview Questions Practical Exercises

SQL Interview High-Frequency Questions Practical Exercises

Core Concepts

SQL is fundamental in data manipulation and analysis, making it a cornerstone of any database-related role. High-frequency interview questions often focus on basic syntax, data types, and query structure.

SELECT Statement
The SELECT statement is the backbone of SQL queries. It retrieves data from one or multiple tables.

SELECT employee_name, department_name  
FROM employees  
WHERE department_name = 'Sales';  

Data Types
SQL supports various data types (e.g., INT, VARCHAR, DATE). Understanding type compatibility is critical to avoid errors.

Operators
Operators like = (equality), <> (not equal), LIKE (pattern matching), and IN (range) are frequently tested.

---

Data Manipulation

SELECT
Use SELECT to retrieve specific columns or rows.

SELECT * FROM products WHERE price > 100;  

WHERE Clause
Filters rows based on conditions.

SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';  

GROUP BY
Groups rows by specified columns to aggregate data.

SELECT department_name, SUM(order_count) AS total_orders  
FROM orders  
GROUP BY department_name;  

HAVING Clause
Filters groups based on aggregate conditions.

SELECT department_name, SUM(order_count) AS total_orders  
FROM orders  
GROUP BY department_name  
HAVING total_orders > 100;  

---

Joins

Joins combine rows from two or more tables. Common types include:

INNER JOIN
Returns rows that have matching values in both tables.

SELECT employees.employee_name, departments.department_name  
FROM employees  
INNER JOIN departments ON employees.department_id = departments.id;  

LEFT JOIN
Returns all rows from the left table, even if there are no matches in the right table.

SELECT employees.employee_name, departments.department_name  
FROM employees  
LEFT JOIN departments ON employees.department_id = departments.id;  

RIGHT JOIN
Similar to LEFT JOIN but returns all rows from the right table.

SELECT employees.employee_name, departments.department_name  
FROM employees  
RIGHT JOIN departments ON employees.department_id = departments.id;  

FULL JOIN
Returns all rows from both tables.

SELECT employees.employee_name, departments.department_name  
FROM employees  
FULL JOIN departments ON employees.department_id = departments.id;  

---

Optimization

Efficient queries are crucial for performance. Key practices include:

Indexes
Indices speed up data retrieval.

CREATE INDEX idx_name ON employees(employee_name);  

Query Execution Plans
Analyze EXPLAIN output to identify bottlenecks.

EXPLAIN SELECT * FROM employees WHERE employee_name = 'John';  

Avoiding Subqueries
Subqueries can be inefficient. Use JOIN or WHERE clauses instead.

---

Advanced Topics

Subqueries
Nested queries can solve complex problems.

SELECT employee_name, department_name  
FROM employees  
WHERE department_name = (SELECT department_name FROM departments WHERE id = 1);  

CTEs (Common Table Expressions)
Break down complex queries into manageable parts.

WITH ranked_orders AS (  
  SELECT order_id, order_date, SUM(order_count) AS total_orders  
  FROM orders  
  GROUP BY order_id, order_date  
)  
SELECT * FROM ranked_orders  
WHERE total_orders > 100;  

Window Functions
Rank, dense_rank, and row_number functions.

SELECT employee_name, department_name,  
ROW_NUMBER() OVER (PARTITION BY department_name ORDER BY order_count DESC) AS rank  
FROM employees;  

Transactions
Ensure data consistency with BEGIN TRANSACTION, COMMIT, and ROLLBACK.

---

Practice Exercises

Exercise 1: Employee and Department Data
Retrieve all employees in the 'Sales' department and their total orders.
Exercise 2: Product and Order Data
Find products with a price over $100 and the number of orders per product.
Exercise 3: Joining Tables
Combine employees and orders tables to show employee names and their order counts.
Exercise 4: Optimization Challenge
Optimize a query that retrieves data from a large table by adding appropriate indexes.
Exercise 5: Subquery Practice
Use a subquery to find employees who have orders in the '2023-04' month.