10 SQL Hands On Interview Questions and Answers

SQL remains a cornerstone of database management and manipulation, essential for handling structured data in various industries. Its ability to efficiently query, update, and manage large datasets makes it a critical skill for roles involving data analysis, backend development, and business intelligence. SQL’s declarative nature and widespread adoption across different database systems underscore its importance in the tech landscape.

This article offers a curated selection of practical SQL questions designed to test and enhance your hands-on skills. By working through these examples, you’ll gain the confidence and proficiency needed to tackle real-world SQL challenges and impress potential employers during technical interviews.

SQL Hands On Interview Questions and Answers

1. Write a query to select employees whose salary is greater than 50000.

To select employees with a salary greater than 50,000, use a basic SQL SELECT statement with a WHERE clause to filter based on the salary condition.

SELECT * FROM employees
WHERE salary > 50000;

2. Write a query to group employees by department and count the number of employees in each department.

To group employees by department and count them, use the GROUP BY clause with the COUNT function to aggregate data based on a specific column.

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

3. Write a query to join the employees table with the departments table on the department ID.

To join the employees table with the departments table on the department ID, use the SQL JOIN clause to combine rows from both tables based on a related column.

SELECT employees.*, departments.*
FROM employees
JOIN departments ON employees.department_id = departments.department_id;

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

To find employees earning more than the average salary, use a subquery to calculate the average salary and compare each employee’s salary to this average.

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

5. Write a query to concatenate the first name and last name of employees into a single column called full_name.

To concatenate the first and last names of employees into a single column called full_name, use the SQL CONCAT function or the concatenation operator.

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

Alternatively, use the concatenation operator (||) in databases that support it, such as PostgreSQL:

SELECT first_name || ' ' || last_name AS full_name
FROM employees;

6. Write a query to rank employees by their salary within each department using a window function.

To rank employees by salary within each department, use the RANK() window function. The PARTITION BY clause divides the result set into partitions, and the ORDER BY clause orders the employees within each department by salary.

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

7. Write a transaction that transfers 1000 units from account A to account B, ensuring atomicity.

In SQL, a transaction is a sequence of operations performed as a single logical unit of work. Transactions ensure the ACID properties: Atomicity, Consistency, Isolation, and Durability. Atomicity ensures that all operations within the transaction are completed successfully; otherwise, the transaction is aborted.

Here is an example of a transaction that transfers 1000 units from account A to account B:

BEGIN TRANSACTION;

UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 'A';

UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 'B';

COMMIT;

8. Write a trigger that updates the last_updated column in the employees table whenever an employee’s record is updated.

To create a trigger that updates the last_updated column in the employees table whenever an employee’s record is updated, use the following SQL code:

CREATE TRIGGER update_last_updated
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    SET NEW.last_updated = NOW();
END;

9. Write a query to handle NULL values in a column, replacing them with a default value.

Handling NULL values in SQL is common. To replace NULL values with a default value, use the COALESCE function, which returns the first non-NULL value in a list of arguments.

SELECT 
    column_name,
    COALESCE(column_name, 'default_value') AS column_with_default
FROM 
    table_name;

10. Write a query using a Common Table Expression (CTE) to simplify a complex query.

A Common Table Expression (CTE) is a temporary result set that you can reference within a query. CTEs simplify complex queries by breaking them down into more manageable parts, improving readability and maintainability.

Here is an example of using a CTE:

WITH Sales_CTE AS (
    SELECT 
        SalesPersonID, 
        SUM(SalesAmount) AS TotalSales
    FROM 
        Sales
    GROUP BY 
        SalesPersonID
)
SELECT 
    s.SalesPersonID, 
    s.TotalSales, 
    e.FirstName, 
    e.LastName
FROM 
    Sales_CTE s
JOIN 
    Employees e
ON 
    s.SalesPersonID = e.EmployeeID
WHERE 
    s.TotalSales > 10000;