Top 46 SQL Coding Interview Questions and Answers

SQL (Structured Query Language) is a tool used to manage and manipulate data in relational databases. It remains one of the most essential skills for data professionals, software developers, and business analysts in various industries. Mastering SQL coding questions is important to demonstrate the ability to work with data. This guide will help you build your knowledge step-by-step with questions testing your SQL proficiencies. By practising the commonly asked SQL coding interview questions and answers, you will be ready to tackle the interview confidently.

SQL Basic Coding Interview Questions and Answers for Freshers

In this section, we will cover the basic SQL coding interview questions that freshers may encounter in an interview. These questions will help you understand fundamental SQL concepts, such as data retrieval, filtering, and basic aggregation. By practicing the following questions, you can build a strong foundation in SQL that will be essential for future roles as data professionals.

Q1. What is the SQL query to select all columns from a table named ‘employees’?

Sample Answer: To retrieve all columns from the ‘employees’ table, we use the SELECT statement. This statement fetches all the data without any filtering.

SELECT * FROM employees;

Q2. How do we filter records in SQL using a WHERE clause?

Sample Answer: The WHERE clause is used to specify conditions for filtering records. For example, if we want to find employees with a salary greater than 50,000, we can write:

SELECT * FROM employees
WHERE salary > 50000;

Q3. How can we count the number of records in a table?

Sample Answer: We can count the total number of records in a table using the COUNT() function. This is useful for quickly getting an overview of how many entries exist.

SELECT COUNT(*) FROM employees;

Q4. What SQL statement do we use to retrieve distinct values from a column?

Sample Answer: By using the DISTINCT keyword, we can get distinct values from a specific column. For example, if we want to find unique job titles in the ‘employees’ table:

SELECT DISTINCT job_title FROM employees;

Q5. How do you sort the results of a query in SQL?

Sample Answer: We can sort the results of our query using the ORDER BY clause. For instance, if we want to sort employees by their joining date in descending order, we would write:

SELECT * FROM employees
ORDER BY joining_date DESC;

Pro Tip: Query-related questions are widely asked in interviews at companies looking for SQL experts. Refer to our blog on SQL query interview questions and answers for more details.

Q6. What is the SQL query to find the average salary of employees?

Sample Answer: To find the average salary of employees, we can use the AVG() function, which calculates the mean value of a numeric column.

SELECT AVG(salary) FROM employees;

Q7. How do we limit the number of results returned by a query?

Sample Answer: We can limit the number of results using the LIMIT clause. For example, to retrieve only the first 5 records from the ‘employees’ table:

SELECT * FROM employees
LIMIT 5;

Q8. How can we retrieve data from multiple tables?

Sample Answer: To retrieve data from multiple tables, we can use JOIN. For example, to get employee names along with their department names from two tables:

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

Q9. What is the SQL query to update a record in a table?

Sample Answer: To update an existing record, we use the UPDATE statement. For instance, to increase the salary of an employee named John:

UPDATE employees
SET salary = salary * 1.10
WHERE name = 'John';

Q10. How do we delete records from a table?

Sample Answer: We can delete records from a table using the DELETE statement. If we want to remove employees with a specific job title, we would do it like this:

DELETE FROM employees
WHERE job_title = 'Intern';

Pro Tip: Amazon frequently requires professionals to deal with its data using SQL. You can go through the commonly asked Amazon SQL job interview questions to improve your chances of landing a job at the company.

Q11. What SQL query do we use to insert a new record into a table?

Sample Answer: To insert a new record into a table, we can use the INSERT INTO statement. For example, to add a new employee:

INSERT INTO employees (name, salary, job_title)
VALUES ('Alice', 60000, 'Developer');

Q12. How can we retrieve the maximum salary from the employees’ table?

Sample Answer: We can find the maximum salary using the MAX() function, which returns the highest value from a specified column.

SELECT MAX(salary) FROM employees;

Q13. What is the SQL query to retrieve records using pattern matching?

Sample Answer: To perform pattern matching, we use the LIKE operator. For example, to find employees whose names start with ‘A’:

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

Pro Tip: For entry-level jobs, the interviewer asks questions based on SQL operators. Going through these SQL coding interviews and answers can give you an edge over other candidates and land you a job.

Q14. How do we get the total number of distinct job titles in the ‘employees’ table?

Sample Answer: We can combine the COUNT() function with DISTINCT to count unique job titles in the table:

SELECT COUNT(DISTINCT job_title) FROM employees;

Q15. What SQL query do we use to group records by a specific column?

Sample Answer: To group records by a specific column, we can use the GROUP BY clause. For instance, if we want to count the number of employees in each department:

SELECT department_id, COUNT(*) AS num_employees
FROM employees
GROUP BY department_id;

Pro Tip: Practice the SQL programming job interview questions consistently and apply for the top in-demand IT jobs in the future.

SQL Coding Interview Questions and Answers for Mid-Level Professionals

Now that we are done with the basic questions, let us explore mid-level professionals SQL coding interview questions. These questions delve deeper into SQL functionalities and concepts, focusing on more complex queries, performance optimization, and data manipulation techniques. By familiarizing yourself with these topics, you can demonstrate your proficiency and understanding of SQL in real-world scenarios.

Q16. How can we use the CASE statement in SQL?

Sample Answer: The CASE statement allows us to perform conditional logic within our queries. For example, we can categorize employee salaries into different ranges:

SELECT name, 
       CASE 
           WHEN salary < 50000 THEN 'Low'
           WHEN salary BETWEEN 50000 AND 100000 THEN 'Medium'
           ELSE 'High'
       END AS salary_range
FROM employees;

Pro Tip: You should be aware of case statements in SQL as it’s a topic of choice for interviewers looking to evaluate your knowledge of handling data effectively through SQL.

Q18. What is the SQL query to retrieve the second-highest salary from a table?

Sample Answer: To find the second highest salary, we can use a subquery with the DISTINCT keyword. This helps us to ignore duplicate values. Here’s a code:

SELECT MAX(salary) 
FROM employees 
WHERE salary < (SELECT MAX(salary) FROM employees);

Pro Tip: To understand the approach for answering similar SQL programming job interview questions, check out our blog on how to find second-highest salary in SQL.

Q19. How do we perform a LEFT JOIN and what is its significance?

Sample Answer: A LEFT JOIN retrieves all records from the left table and the matched records from the right table. If there is no match, NULL values will appear. This is useful for identifying records that may not have corresponding entries in another table:

Here’s a code:

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

Q20. What is the SQL query to update multiple columns in a table?

Sample Answer: To update multiple columns simultaneously, we can specify each column in the SET clause. For example, if we want to change an employee’s job title and salary:

UPDATE employees
SET job_title = 'Senior Developer', salary = 80000
WHERE name = 'Alice';

Q21. How can we find duplicate records in a table?

Sample Answer: To identify duplicate records, we can use the GROUP BY clause along with HAVING to filter groups with more than one occurrence. For instance, to find employees with the same name:

SELECT name, COUNT(*) AS count
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;

Q22. What is the SQL query to retrieve records with a specific condition on a joined table?

Sample Answer: When working with joined tables, we can apply conditions on both tables. For example, to find employees in a specific department with a certain salary range:

SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.department_name = 'Sales' AND e.salary > 60000;

Q23. How do we use COALESCE in SQL?

Sample Answer: The COALESCE function returns the first non-null value from a list of expressions. This helps handle null values effectively. For example, if we want to display a default value when the salary is null:

SELECT name, COALESCE(salary, 'Not Specified') AS salary
FROM employees;

Q24. What is the SQL query to delete records based on a condition from multiple tables?

Sample Answer: To delete records based on a condition involving multiple tables, we typically use a DELETE statement with a JOIN. For example, to remove employees who belong to a specific department:

DELETE e
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.department_name = 'Interns';

Pro Tip: Questions around delete queries are frequently asked in SQL interviews. Preparing coding problems related to the DELETE query in SQL can help you better answer such SQL coding job interview questions.

Q25. How can we calculate the total salary expense for each department?

Sample Answer: To calculate total expenses, we can use the SUM() function along with GROUP BY. This helps us summarize the salaries for each department:

SELECT d.department_name, SUM(e.salary) AS total_salary
FROM employees e
JOIN departments d ON e.department_id = d.id
GROUP BY d.department_name;

Q26. How do we create an index on a table, and why is it important?

Sample Answer: Creating an index on a table improves query performance by allowing the database to find data more quickly. For example, to create an index on the ‘name’ column of the “employees” table:

CREATE INDEX idx_name ON employees(name);

Here’s the code:

CREATE INDEX idx_name ON employees(name);
CREATE PROCEDURE GetEmployeesByDepartment(IN dept_name VARCHAR(255))
BEGIN
    SELECT * FROM employees
    WHERE department_id = (SELECT id FROM departments WHERE department_name = dept_name);
END;

Q28. How can we implement pagination in SQL?

Sample Answer: Pagination is implemented using LIMIT and OFFSET clauses. This is particularly useful for retrieving data in manageable chunks, such as displaying results on a website. Here’s the code:

SELECT * FROM employees
ORDER BY name
LIMIT 10 OFFSET 20;  -- Retrieves 10 records starting from the 21st record

Q29. What is the SQL query to create a temporary table?

Sample Answer: Temporary tables are used to store intermediate results. We can create a temporary table like this:

CREATE TEMPORARY TABLE temp_employees AS
SELECT * FROM employees WHERE salary > 70000;

Q30. How do we implement a recursive query using Common Table Expressions (CTEs)?

Sample Answer: We can use CTEs to perform recursive queries, which are useful for hierarchical data. For instance, to find all employees under a specific manager:

WITH RECURSIVE EmployeeCTE AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN EmployeeCTE ec ON e.manager_id = ec.id
)
SELECT * FROM EmployeeCTE;

Q31. What is the SQL query to perform a union of two tables?

Sample Answer: We can use the UNION operator to combine the results of two queries, removing duplicates. For example, to get a list of all employee names from two different tables:

SELECT name FROM employees
UNION
SELECT name FROM contractors;

Pro Tip: You can make attempts to learn about SQL union. This can give you a better understanding of tackling such SQL coding job interview questions.

Advanced SQL Coding Interview Questions for Experienced Professionals

In this section, we will tackle advanced SQL programming interview questions and answers designed for experienced candidates. These questions focus on complex SQL operations, performance optimization techniques, and data manipulations. Mastering these concepts will enable you to tackle real-world challenges effectively and demonstrate our expertise in SQL during your interviews.

Q32. How can we optimize a slow-running query?

Sample Answer: To optimize a slow-running query, we can analyze the execution plan and add appropriate indexes. For instance, if we frequently search by a specific column, creating an index on that column can significantly improve performance:

CREATE INDEX idx_employee_name ON employees(name);

Q33. What is the SQL query to implement window functions?

Sample Answer: Window functions allow us to perform calculations across a set of table rows related to the current row. For example, we can calculate the running total of salaries:

SELECT name, salary,
       SUM(salary) OVER (ORDER BY id) AS running_total
FROM employees;

Pro Tip: Practicing interview questions related to SQL functions can improve your chances of landing a SQL developer job.

Q34. How do we use the MERGE statement in SQL?

Sample Answer: The MERGE statement allows us to perform INSERT, UPDATE, or DELETE operations in a single statement. This is useful for synchronizing tables. For instance, updating employee records based on a temporary staging table:

MERGE INTO employees AS target
USING staging_table AS source
ON target.id = source.id
WHEN MATCHED THEN
    UPDATE SET target.salary = source.salary
WHEN NOT MATCHED THEN
    INSERT (id, name, salary) VALUES (source.id, source.name, source.salary);

Q35. What is the SQL query to find gaps in a sequence of numbers?

Sample Answer: To identify gaps in a sequence, we can use a LEFT JOIN with a sequence table. For example, finding missing employee IDs:

SELECT a.id + 1 AS missing_id
FROM (SELECT id FROM employees) a
LEFT JOIN (SELECT id FROM employees) b ON a.id + 1 = b.id
WHERE b.id IS NULL;

SELECT *
FROM (SELECT month, product, sales FROM sales_data) AS source
PIVOT (SUM(sales) FOR month IN ([January], [February], [March])) AS pvt;

Pro Tip: SQL programming questions are frequently encountered in technical interviews. For effective preparation, refer to our comprehensive SQL roadmap, which provides structured guidance and key topics to focus on.

Q37. What is the SQL query to implement a recursive CTE?

Sample Answer: We can use recursive Common Table Expressions (CTEs) to navigate hierarchical data structures like organizational charts. Here’s how to find all the subordinates of a manager:

WITH RECURSIVE EmployeeCTE AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE manager_id = 'MGR001'
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN EmployeeCTE ec ON e.manager_id = ec.id
)
SELECT * FROM EmployeeCTE;

Q38. How can we implement full-text search in SQL?

Sample Answer: Full-text search allows us to perform complex queries against textual data. For instance, to find employees whose names contain specific keywords:

SELECT name
FROM employees
WHERE CONTAINS(name, 'John OR Doe');

Q39. What is the SQL query to perform data normalization?

Sample Answer: Data normalization in SQL involves organizing tables to reduce redundancy. For example, we can split a table into two, separating employee details from department details:

-- Create a new department table
CREATE TABLE departments (
    id INT PRIMARY KEY,
    department_name VARCHAR(255)
);

-- Insert unique departments from the employee's table
INSERT INTO departments (id, department_name)
SELECT DISTINCT department_id, department_name FROM employees;

Pro Tip: You should be able to understand the difference between primary key and secondary key in SQL in order to answer such SQL coding interview questions.

Q40. How do we handle transactions in SQL?

Sample Answer: Transactions ensure that a series of operations are completed successfully. If one operation fails, we can roll back the transaction to maintain data integrity:

BEGIN TRANSACTION;
UPDATE employees SET salary = salary * 1.1 WHERE performance_rating = 'Excellent';

IF @@ERROR <> 0
BEGIN
    ROLLBACK TRANSACTION;
END
ELSE
BEGIN
    COMMIT TRANSACTION;
END

Q41. What is the SQL query to use dynamic SQL?

Sample Answer: Dynamic SQL allows us to construct SQL statements at runtime. This is particularly useful for building flexible queries:

DECLARE @sql AS NVARCHAR(MAX);
SET @sql = 'SELECT * FROM employees WHERE salary > ' + CAST(@salary AS NVARCHAR);
EXEC sp_executesql @sql;

Q42. How can we aggregate data using advanced SQL functions?

Sample Answer: We can use advanced SQL functions like ROLLUP and CUBE to perform multidimensional analysis. For example, calculating total sales by product and region:

SELECT product, region, SUM(sales)
FROM sales_data
GROUP BY ROLLUP(product, region);

Q43. What is the SQL query to create a materialized view?

Sample Answer: A materialized view stores the result of a query physically, improving performance for complex queries. For instance, to create a materialized view for frequently accessed sales data:

CREATE MATERIALIZED VIEW mv_sales_summary AS
SELECT product, SUM(sales) AS total_sales
FROM sales_data
GROUP BY product;

Q44. How do we perform data extraction using ETL processes?

Sample Answer: ETL (Extract, Transform, Load) processes are essential for data integration. We can extract data from different sources, transform it, and load it into a target database. 

Here’s a simplified extraction step:

INSERT INTO target_table (column1, column2)
SELECT source_column1, source_column2
FROM source_table
WHERE condition;

Q45. How can we use EXPLAIN to analyze query performance?

Sample Answer: Using the EXPLAIN statement helps us understand how the SQL engine executes our queries. This allows us to identify potential bottlenecks. 

Here’s the code:

EXPLAIN SELECT * FROM employees WHERE salary > 60000;

Q46. What is the SQL query to manage database backups and recovery?

Sample Answer: Managing backups is crucial for data protection. We can use the following command to back up a database.

BACKUP DATABASE MyDatabase
TO DISK = 'C:\backups\MyDatabase.bak';

Conclusion

Proficiency in SQL goes beyond just learning different types of SQL commands. It involves understanding how to manipulate data efficiently and knowing when to use specific techniques for complex queries. Practising SQL coding interview questions and answers helps you to attempt questions confidently and also refines your skills in real-world applications, where data processing and analysis are important. If you are considering pursuing a career as an SQL developer, you can check out our blog on how to become a SQL developer.

FAQs