10 QA SQL Interview Questions and Answers

SQL is a fundamental skill for Quality Assurance (QA) professionals, enabling them to validate data integrity, perform backend testing, and ensure that applications interact correctly with databases. Mastery of SQL allows QA engineers to write efficient queries, understand database schemas, and troubleshoot data-related issues, making it an indispensable tool in their toolkit.

This article offers a curated selection of SQL questions tailored for QA roles. By working through these questions, you will enhance your ability to test and validate data effectively, ensuring robust and reliable software performance.

QA SQL Interview Questions and Answers

1. Write a SQL query to retrieve all records from a table named ‘Employees’ where the ‘Department’ is ‘Sales’.

To retrieve all records from a table named ‘Employees’ where the ‘Department’ is ‘Sales’, use:

SELECT * FROM Employees WHERE Department = 'Sales';

2. Write a SQL query to join two tables, ‘Orders’ and ‘Customers’, on the ‘CustomerID’ field.

To join two tables, ‘Orders’ and ‘Customers’, on the ‘CustomerID’ field, use:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

3. Write a SQL query to calculate the average salary from a table named ‘Salaries’.

To calculate the average salary from a table named ‘Salaries’, use:

SELECT AVG(salary) AS average_salary
FROM Salaries;

4. Write a SQL query to find employees who earn more than the average salary using a subquery.

To find employees who earn more than the average salary, use a subquery:

SELECT employee_id, employee_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

The subquery calculates the average salary, and the main query selects employees earning above this average.

5. Write a SQL query using a window function to rank employees based on their salaries within each department.

Use the RANK() window function to rank employees based on their salaries within each department:

SELECT 
    employee_id,
    department_id,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM 
    employees;

The PARTITION BY clause divides the result set by department_id, and ORDER BY sorts by salary.

6. Write a SQL query to identify and remove duplicate records from a table named ‘Products’.

To identify and remove duplicate records from ‘Products’, use a CTE and the DELETE statement:

WITH CTE AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY product_id) AS row_num
    FROM 
        Products
)
DELETE FROM CTE
WHERE row_num > 1;

The CTE assigns a row number to each duplicate, and the DELETE statement removes duplicates.

7. Write a SQL query to create a stored procedure that updates employee salaries based on their performance rating.

Create a stored procedure to update employee salaries based on performance ratings:

CREATE PROCEDURE UpdateEmployeeSalaries()
BEGIN
    UPDATE Employees
    SET Salary = CASE
        WHEN PerformanceRating = 'A' THEN Salary * 1.10
        WHEN PerformanceRating = 'B' THEN Salary * 1.05
        WHEN PerformanceRating = 'C' THEN Salary * 1.02
        ELSE Salary
    END;
END;

8. Write a SQL query to execute a dynamic SQL statement that selects data from a table whose name is passed as a parameter.

Execute a dynamic SQL statement with a table name passed as a parameter:

DECLARE @TableName NVARCHAR(128)
SET @TableName = 'YourTableName'

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = N'SELECT * FROM ' + QUOTENAME(@TableName)

EXEC sp_executesql @SQL

The QUOTENAME function safely includes the table name, and sp_executesql executes the SQL statement.

9. Discuss the challenges and strategies for handling NULL values in SQL queries.

Handling NULL values in SQL queries can be challenging. Strategies include:

• Using IS NULL and IS NOT NULL: Filter records with NULL values.
• COALESCE Function: Returns the first non-NULL value in a list.
• NULLIF Function: Returns NULL if two expressions are equal.
• Handling NULLs in Aggregations: Special handling for functions like COUNT, SUM, AVG.

Example:

SELECT 
    COALESCE(column_name, 'default_value') AS column_name
FROM 
    table_name
WHERE 
    column_name IS NOT NULL;

10. Explain how to use the EXPLAIN plan to analyze and optimize SQL queries.

The EXPLAIN plan analyzes and optimizes SQL queries by detailing how the database engine executes a query. It provides insights into execution order, index use, and operation costs.

Example:

EXPLAIN SELECT * FROM employees WHERE department_id = 10;

Key elements include:

• Table Access Methods: Indicates access methods like full table scan or index scan.
• Join Methods: Describes join methods such as nested loops or hash joins.
• Cost Estimates: Estimates the computational cost of operations.
• Index Usage: Shows index usage and selection for the query.

Analyzing these elements helps identify optimization opportunities, such as adding an index to improve performance.