10 SQL Assessment Interview Questions and Answers

SQL remains a cornerstone of database management and manipulation, essential for handling structured data in various applications. Its ability to efficiently query, update, and manage data makes it indispensable for roles in data analysis, software development, and database administration. SQL’s widespread adoption across industries underscores its importance as a fundamental skill for technical professionals.

This article offers a curated selection of SQL questions designed to test and enhance your understanding of key concepts and practical applications. By working through these questions, you will be better prepared to demonstrate your SQL proficiency and problem-solving abilities in an interview setting.

SQL Assessment Interview Questions and Answers

1. Describe the concept of database normalization and why it is important.

Database normalization involves structuring a relational database to reduce redundancy and improve data integrity. It typically involves dividing a database into multiple tables and defining relationships between them. The process is done in stages, known as “normal forms,” with the most common being:

• First Normal Form (1NF): Ensures that the table has a primary key and that all columns contain atomic values.
• Second Normal Form (2NF): Ensures that all non-key attributes are fully functionally dependent on the primary key.
• Third Normal Form (3NF): Ensures that attributes are not only fully functionally dependent on the primary key but also non-transitively dependent.

Normalization is important for maintaining data consistency, efficient data management, optimized queries, and reducing anomalies.

2. What is an index, and how does it improve query performance?

An index in SQL is a database object that enhances data retrieval speed on a table at the cost of additional storage. Indexes are created on columns frequently used in WHERE clauses, JOIN conditions, or ORDER BY clauses. By creating an index, the database can quickly locate data without a full table scan.

Types of indexes include:

• Clustered Index: Sorts and stores data rows in the table based on the index key. Each table can have only one clustered index.
• Non-Clustered Index: Creates a separate structure from the data rows, with pointers back to the original table rows. A table can have multiple non-clustered indexes.

Example:

CREATE INDEX idx_last_name ON employees(last_name);
SELECT * FROM employees WHERE last_name = 'Smith';

Creating an index on the ‘last_name’ column allows the database to quickly locate rows where the last name is ‘Smith’, improving query performance.

3. Write a SQL query using a subquery to find employees who earn more than the average salary in the “Employees” table.

A subquery in SQL is a query nested inside another query, used for operations requiring multiple steps. For example, to find employees earning more than the average salary:

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

The subquery calculates the average salary, and the outer query selects employees with salaries above this average.

4. Explain the ACID properties in the context of database transactions.

ACID properties ensure the integrity and reliability of database transactions:

• Atomicity: Ensures a transaction is treated as a single unit, either completely succeeding or failing.
• Consistency: Ensures a transaction brings the database from one valid state to another.
• Isolation: Ensures concurrent transactions do not affect each other.
• Durability: Guarantees that once a transaction is committed, it remains so, even in the event of a system failure.

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

Window functions perform calculations across a set of table rows related to the current row. For example, to rank employees by salary 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 RANK() function assigns a rank based on salary within each department, using PARTITION BY to divide the result set.

6. What are stored procedures, and what are their advantages?

Stored procedures are precompiled collections of SQL statements stored under a name and processed as a unit. They can accept input parameters, return output parameters, and provide multiple result sets.

Advantages:

• Performance: Precompiled and stored in the database, leading to faster execution.
• Maintainability: Easier management and updates by encapsulating SQL logic.
• Security: Restrict direct access to tables by granting permissions on stored procedures.
• Reusability: Can be reused across applications, promoting code reuse.
• Consistency: Centralizes business logic, ensuring consistent application.

7. Describe some common practices for securing a SQL database.

Securing a SQL database involves several practices:

• Authentication and Authorization: Use strong passwords and role-based access control.
• Encryption: Encrypt data at rest and in transit.
• Regular Updates and Patching: Keep software up to date with security patches.
• Backup and Recovery: Regularly back up the database and test recovery processes.
• Monitoring and Auditing: Implement logging and review audit logs.
• Least Privilege Principle: Grant minimum access necessary for job functions.
• Network Security: Use firewalls and limit database access to specific IPs.
• Input Validation: Validate and sanitize user inputs to prevent SQL injection.

8. Write a SQL query to optimize the retrieval of the top 10 highest-paid employees from a large “Employees” table.

To retrieve the top 10 highest-paid employees from a large “Employees” table:

SELECT employee_id, employee_name, salary
FROM Employees
ORDER BY salary DESC
LIMIT 10;

9. Explain the use of Common Table Expressions (CTEs) and provide an example.

Common Table Expressions (CTEs) simplify complex queries by breaking them into manageable parts. Defined using the WITH keyword, they can be referenced multiple times within the main query.

Example:

WITH Sales_CTE AS (
    SELECT 
        SalesPersonID, 
        SUM(SalesAmount) AS TotalSales
    FROM 
        Sales
    GROUP BY 
        SalesPersonID
)
SELECT 
    SalesPersonID, 
    TotalSales
FROM 
    Sales_CTE
WHERE 
    TotalSales > 10000;

The CTE Sales_CTE calculates total sales for each salesperson, and the main query selects those with total sales over 10,000.

10. How do you handle NULL values in SQL queries? Provide examples.

In SQL, NULL values represent missing or unknown data. Handling NULL values is important for accurate data retrieval. Common methods include:

• IS NULL and IS NOT NULL: Filter records with or without NULL values.
• COALESCE: Returns the first non-NULL value in a list.
• NULLIF: Returns NULL if two arguments are equal.

Examples:

SELECT * FROM employees WHERE manager_id IS NULL;
SELECT * FROM employees WHERE manager_id IS NOT NULL;
SELECT employee_id, COALESCE(manager_id, 'No Manager') AS manager_id FROM employees;
SELECT employee_id, NULLIF(manager_id, 0) AS manager_id FROM employees;