Top 25 SQL Interview Questions

Mon Mar 4, 2024

1. What is SQL? Briefly explain its purpose and applications.

Answer: SQL (Structured Query Language) is a specialized language used to interact with relational databases. Its primary purpose involves:

Retrieving data: Selecting specific data from tables based on various criteria
Manipulating data: Inserting, updating, and deleting data within tables
Managing data: Defining tables, columns, data types, constraints, and user permissions

SQL finds applications in various fields, including:

Data analysis and reporting
Web development
Business intelligence
Data warehousing
Scientific computing

2. Explain the different types of SQL statements (e.g., SELECT, INSERT, UPDATE, DELETE).
Answer: SQL involves different statements to interact with databases:

SELECT: This statement retrieves data from one or more tables based on specific criteria.
INSERT: This statement adds new data to a table, specifying column values for each new row.
UPDATE: This statement modifies existing data in a table, based on specific conditions and updating specific columns.
DELETE: This statement removes rows from a table, based on specific conditions.

3. What are the different data types used in SQL?
Answer: Common data types in SQL include:

Numeric: Store numeric values (e.g., INT, DECIMAL)
Character: Store text data (e.g., CHAR, VARCHAR)
Date and Time: Store date and time information (e.g., DATE, TIME, DATETIME)
Boolean: Store True/False values (e.g., BOOLEAN)

4. Describe the difference between a primary key and a foreign key.

Answer:

Primary key: Uniquely identifies each row in a table, ensuring no duplicates exist. Each table typically has only one primary key.
Foreign key: References the primary key of another table, establishing a relationship between tables. A table can have multiple foreign keys referencing different primary keys in other tables.

5. Explain the concept of normalization in database design.

Answer: Normalization is a process of organizing data in a database to:

Reduce data redundancy: Minimize storing the same data in multiple places, improving data consistency and storage efficiency.
Improve data integrity: Reduce the risk of data inconsistencies arising from updates or deletions in one table affecting other tables.
Enhance data efficiency: Improve query performance by avoiding unnecessary data retrieval.

6. How do you write a query to filter data based on specific conditions (WHERE clause)

Answer: The WHERE clause allows you to filter data within the SELECT statement based on specific conditions. For example:

SELECT * FROM customers WHERE city = 'New York'; 
This query selects all columns (*) from the customers table where the city column value is equal to 'New York'.

7. Explain the purpose of the GROUP BY clause and how it is used.
Answer: The GROUP BY clause groups rows in a table based on one or more columns. It is often used with aggregate functions (e.g., SUM, COUNT, AVG) to summarize data for each group.
For example:

SELECT country, COUNT(*) AS total_customers FROM customers GROUP BY country; 
This query groups customers by their country and counts the number of customers in each group using the COUNT(*) aggregate function.

8. Describe the difference between aggregate functions (e.g., SUM, COUNT, AVG) and scalar functions.
Answer:

Aggregate functions: Operate on groups of data and return a single summarized value for each group. Examples include SUM, COUNT, AVG, MIN, MAX.
Scalar functions: Operate on individual values and return a single value for each input value. Examples include UPPER, LOWER, SUBSTRING.

9. What are common table expressions (CTEs) and how are they beneficial?

Answer: Common table expressions (CTEs) are temporary named result sets defined within a single SQL query. They improve:

Readability: Break down complex queries into smaller, more understandable steps.
Reusability: Can be referenced multiple times within the same query, avoiding code repetition.

10. Write a query to retrieve employees with a salary above a specific amount.

Answer:

SELECT * FROM employees WHERE salary > [desired_salary_amount];

This query selects all columns (*) from the employees table where the salary is greater than the specified [desired_salary_amount]. Replace [desired_salary_amount] with the specific numerical value you want to filter by.

11. Explain the different types of joins in SQL.

Answer: SQL offers various join types to combine data from multiple tables:

INNER JOIN: Returns rows where there's a match in the join condition for both tables.
LEFT JOIN: Returns all rows from the left table and matching rows from the right table, even if there's no match in the right table.
RIGHT JOIN: Returns all rows from the right table and matching rows from the left table, even if there's no match in the left table.
FULL JOIN: Returns all rows from both tables, including unmatched rows from each side.

12. How do you handle NULL values in your queries?

Answer: NULL refers to the absence of a value. Handle them carefully to avoid unexpected results:

IS NULL and IS NOT NULL: Check if a column value is NULL or not.
COALESCE: Returns the first non-NULL value from a list of expressions.
CASE statements: Handle different scenarios based on NULL values.

13. What are transactions in SQL and explain the ACID properties?

Answer: A transaction is a series of database operations treated as a single unit. ACID properties ensure data integrity in transactions:

Atomicity: All operations within the transaction succeed or fail together.
Consistency: The database state transitions from one valid state to another.
Isolation: Concurrent transactions do not interfere with each other's data.
Durability: Once committed, changes made by the transaction become permanent.

14. Differentiate between DELETE and TRUNCATE statements.

Answer:

DELETE: Removes specific rows from a table based on a WHERE clause, allowing rollback if needed.
TRUNCATE: Deletes all rows from a table, faster but irreversible. Use with caution!

15. Explain the concept of views in SQL and their benefits.

Answer: Views are virtual tables based on a SELECT query, offering a simplified or filtered representation of the underlying data. Benefits include:

Data security: Restricts access to specific columns or rows.
Data simplification: Presents complex queries with a user-friendly interface.
Data abstraction: Hides the underlying structure of the tables.

16. Describe how to write a query to find the nth highest salary in a table without using TOP or LIMIT.

Answer: This can be achieved by using a subquery and window functions:

SELECT employee_id, salary FROM (   SELECT employee_id, salary, RANK() OVER (ORDER BY salary DESC) AS rank   FROM employees ) AS ranked_employees WHERE rank = [desired_rank]; 
This query uses a subquery to rank employees by salary in descending order and then filters for the desired rank ([desired_rank]).

17. Write a query to retrieve employees with a salary above a specific amount and their department names.

Answer: This involves joining the employees and departments tables:

SELECT e.employee_id, e.salary, d.department_name FROM employees AS e INNER JOIN departments AS d ON e.department_id = d.department_id WHERE e.salary > [desired_salary_amount]; 
This query utilizes an INNER JOIN to match employees with their respective departments based on the department_id and then filters for employees with salaries exceeding the specified amount.

18. Explain how to write a query to create a new table based on existing data.

Answer: Use the CREATE TABLE statement to define the new table structure and optionally populate it with data:

CREATE TABLE new_table (   column1 data_type,   column2 data_type,   ... );  INSERT INTO new_table (column1, column2, ...) SELECT column1, column2, ... FROM existing_table; 
This example defines a new table schema and then populates it with selected data from the existing_table using an INSERT statement.

19. Explain the best practices for writing efficient and maintainable SQL code.

Answer: Consider these best practices:

Use clear and descriptive names for tables, columns, and variables.
Properly indent code for readability.
Use comments to explain complex logic.
Optimize queries for performance (e.g., using indexes, avoiding unnecessary joins).
Follow coding standards and conventions.

20. Describe a real-world scenario where you used SQL to solve a specific problem.

Answer: Craft a response that showcases your experience by outlining the specific problem, your approach using relevant SQL concepts.

21. Explain the purpose of indexes in SQL and how they impact query performance.
Answer: Indexes are data structures that speed up data retrieval by allowing faster searching and sorting of specific columns. They improve query performance by:

Reducing disk access: Indexes act like an organized directory, enabling the database to locate relevant data quickly without scanning the entire table.
Optimizing joins: Indexes can also be used to efficiently join tables based on the indexed columns.

Using indexes strategically can significantly improve query performance, especially for large datasets.

22. What are stored procedures and triggers in SQL, and how are they used?
Answer:

Stored procedures: Pre-compiled SQL code blocks that can be reused with different parameters. They offer:
- Modularization: Break down complex logic into reusable units, improving code maintainability and reducing redundancy.
- Encapsulation: Hide implementation details, promoting security and data integrity.
- Parameterization: Allow dynamic input, making them adaptable to various scenarios.
Triggers: Database objects that automatically execute specific actions (e.g., inserting data, updating data) based on certain events (e.g., inserting, updating, deleting data in a table). They can be used for:
- Enforcing data integrity: Automatically perform validations or data modifications when specific events occur.
- Auditing: Log changes made to the database.
- Cascading operations: Automatically propagate changes to related tables.

23. Briefly explain the difference between SQL and NoSQL databases.

Answer:

SQL (Structured Query Language): Designed for relational databases with a structured table-based schema and enforces relationships between data. It uses SQL language for data manipulation and retrieval.
NoSQL (Not Only SQL): Non-relational databases offer flexible data models, often used for unstructured or semi-structured data. They are not limited to a single, predefined schema and use various query languages or APIs for data access.

The choice between SQL and NoSQL depends on the specific needs of the application and the characteristics of the data.

24. Describe the concept of user permissions and access control in SQL.

Roles: Predefined sets of permissions granted to users.
Grants: Assign specific permissions to individual users or roles. These permissions can control various operations like SELECT, INSERT, UPDATE, and DELETE on specific tables or columns.
Revoking permissions: Grants can be revoked to restrict access when needed.

Proper user permission management is crucial for maintaining data security and integrity.

25. Explain how to write a query to handle potential SQL injection vulnerabilities.

Answer: SQL injection is a security threat where malicious code is injected into an SQL query through user input. To prevent this, use parameterized queries:

Prepare statements: Predefined SQL statements with placeholders for user input.
Bind parameters: Bind actual user input to the placeholders separately from the query string. This prevents the input from being interpreted as part of the SQL code.

By using parameterized queries, you can significantly reduce the risk of SQL injection vulnerabilities.

Top 25 SQL Interview Questions

You may also be interested in