Top SQL Query Interview Questions and Answers (2024)

Today, nearly all organisations depend on the immersive power of database management and cloud computing. The number of total servers and databases all over the world is magnificent, nearly millions of servers hold billions of databases, and it is highly crucial to have someone with a good knowledge of data handling. SQL remains a cornerstone skill for many database professionals, analysts and developers. Learning SQL as the base component is very important for anyone preparing for SQL query interview questions or any related field.

In this tutorial, we will be looking at the Top SQL Query Interview Questions and Answers (2024) guide which will help you to get an idea about what types of questions are asked. Here, we have mentioned the general SQL questions, following the Advanced SQL query questions.

General SQL Query Interview Questions

1. Explain SQL and its importance in today’s world.

SQL stands for Structured Query Language, which is a standard language for managing and manipulating relational databases. SQL plays a very important role in data retrieval, data updation, data analysis, managing logs and records and also in driving essential applications, technologies or even organisations.

2. Explain the difference between SQL and MySQL.

3. What is a trigger in SQL?

A trigger is a database object which monitors and automatically executes some specified actions (either a single operation or multiple sets of operations) to change anything in a database. For example, it triggers events using INSERT, UPDATE or DELETE commands.

4. What is a view in SQL, and how is it different from a table?

A view does not store any data itself as it is not any kind of data variable, but it returns a data table by dynamically retrieving data from a given table.

5. What is a subquery, and how is it used in SQL?

A subquery is a nested query which is receding inside another outer query. This subquery helps to return data that can be used as a condition or value for the outer query, ultimately reducing the complexity or overall query in complex operations. It uses SELECT, INSERT, UPDATE and DELETE statements.

Example:

SELECT name

FROM employees

WHERE department_id IN (SELECT department_id FROM departments WHERE location = ‘New York’);

6. What is a foreign key, and how is it used in SQL?

Any foreign key in SQL will be a column or set of columns that maintain a relationship between two different database tables. It ensures referential integrity by creating a link between data in the referencing table (child table) and the referenced table (parent table).

Example:

CREATE TABLE employees (

employee_id INT PRIMARY KEY,

employee_name VARCHAR(50),

department_id INT,

FOREIGN KEY (department_id) REFERENCES departments(department_id)

);

7. Can you explain the concept of normalisation and its types?

Normalisation organises data in a database using highly efficient techniques. It includes creating tables and relationships, eliminating data wherever required, etc.

Types of normalisation:

1. First Normal Form (1NF): This ensures that each column in a table will contain unique values or atomic values, i.e. no repeating groups or arrays.

2. Second Normal Form (2NF): The tables in 1NF that are non-key columns should be fully dependent on the primary key.

3. Third Normal Form (3NF): This is to ensure that the tables in 2NF do not have transitive dependencies. No column depends on anything other than the primary key.

4. Boyce-Codd Normal Form (BCNF): This is another advanced version of 3NF, the determinant or determiner of any attribute must be a candidate key.

5. Fourth Normal Form (4NF): A table should not have multi-valued dependencies where one or more columns depend on a set of values which are not part of the primary key

6. Fifth Normal Form (5NF) or Domain-Key Normal Form(DK/NF): Every joint dependence must be implied by the candidate keys only, in any database table.

8. What is a spatial database?

A database which is optimised for storing and querying geographical data such as points, lines, shapes, polygonal coordinates and spatial relationships. Mainly used for location-based information storing and sorting through analysis of spatial data.

9. What is the BIT data type used for?

The BIT represents a binary digit (0 or 1) or a set of binary digits. It is used for storing bit values, and also to store boolean values. flags and other binary data.

10. What is a relational database? Give examples of other database models. How does it differ from other database models?

It is a type of database which stores and organises data with rows and columns manipulation in your database. It follows a structured approach, allowing data to be queried using SQL. Different types of other database models are:

• NoSQL
• Object-Oriented
• Key-Value
• Hierarchical/ Network

11. What is a database management system (DBMS), and how does SQL relate to it?

DBMS software helps to manage different databases, from private databases to large data blocks such as organisational data or a common database portal for all people in one country. Hence, database management systems help interact, sort, retrieve, or manipulate data, connecting to every database present. SQL works as a programming language for DBMS, which helps to modulate what data we need to modify or add new.

12. How can you add a NOT NULL constraint to an existing column?

To add a NOT NULL constraint, you can use the ALTER TABLE statement for an existing column.

ALTER TABLE table_name

ALTER COLUMN column_name SET NOT NULL;

13. Explain the use of the GROUP BY clause in SQL.

The GROUP BY clause helps us to group those rows which have the same values into summary rows. Therefore to perform calculations on each group, it is always used with aggregate functions like COUNT, SUM, AVG, etc.

14. What are case manipulation functions? Give some examples.

Case manipulations are used to change the case of any string present in the database. For example:

• UPPER: Converts any string to uppercase

SELECT UPPER(‘hello’) AS result;

— Output: ‘HELLO’

• LOWER: converts any string to lowercase

SELECT LOWER(‘Hello’) AS result;

— Output: ‘hello’

• INTCAP: this one converts the first character of each word in that string to uppercase, while the rest to lowercase

SELECT INITCAP(‘hello world’) AS result;

— Output: ‘Hello World’

15. What is the difference between the DROP and TRUNCATE statements?

16. Can you explain the concept of transactions in SQL and their properties (ACID properties)?

A transaction is a combination of one or more SQL operations which are executed as a single unit of workflow. These ensure data integrity and consistency, making sure all the operations are completed successfully. ACID is an acronym representing the four key properties of transactions.

1. Atomicity: Ensures all the operations within a transaction are completed, and if any operation fails, the entire transaction fails.
2. Consistency: It helps in transferring the database or its elements from one valid state to another, maintaining all rules and supporting continuous workflow. For example, after a transaction, all data constraints, such as accounting and balance handling, must be met.
3. Isolation: Here, two transactions cannot occur simultaneously, ensuring end-to-end process flow.
4. Durability: This ensures secure transactions as once a transfer is completed, it should remain as it is, and it should not be lost, even if the system crashes.

17. What are aggregate functions, and can you provide examples?

Aggregate functions are the functional blocks for any SQL query as they handle calculations returning a final value. These functions are used with the GROUP BY clause.

• COUNT(): GIves total number of rows in a set

SELECT COUNT(*) AS total_rows FROM employees;

• SUM(): Sums all the values of a numeric column

SELECT SUM(salary) AS total_salary FROM employees;

• AVG(): Average the specified numeric column

SELECT AVG(salary) AS average_salary FROM employees;

• MIN(): Finds the smallest value in a set

SELECT MIN(salary) AS minimum_salary FROM employees;

• MAX(): Find and return one largest value from the set

SELECT MAX(salary) AS maximum_salary FROM employees;

Example:

SELECT department_id, AVG(salary) AS average_salary

FROM employees

GROUP BY department_id;

18. What are the different types of SQL commands?

There are many commands in SQL which can be categorised into several types:

1. Data Manipulation Language (DML):

• SELECT
• UPDATE
• INSERT
• DELETE

2. Data Control Language (DCL):

• GRANT
• REVOKE

3. Data Definition Language (DDL):

• CREATE
• DROP
• TRUNCATE
• ALTER

4. Transaction Control Language (TCL):

• COMMIT
• ROLLBACK
• SAVEPOINT
• SET TRANSACTION

19. What are the different types of indexes?

To improve the overall performance of any data retrieval operations or methods, indexes are used. There are many different types of indexes that have different purposes and workflows. Some of the indexes are:

• Composite Index
• Clustered and Non-Clustered Index
• Primary Index
• Unique Index
• Bitmap Index
• B-Tree Index
• Function-Based Index
• Hash Index
• Full-Text Index

20. What are partitioned tables, and how do you create them?

Instead of using a very big query, partitioned tables in SQL allow us to divide queries into much smaller and manageable pieces which are called partitions. Each partition can be managed easily and independently, improving overall performance with easy maintenance of the database. Following is an example of how to create partitioned tables.

• Partition function defining data based on columns.

CREATE PARTITION FUNCTION pfRange (int)

AS RANGE LEFT FOR VALUES (100, 200, 300);

• A partition scheme is used to map the partition to filegroups.

CREATE PARTITION SCHEME psRange

AS PARTITION pfRange

TO (fg1, fg2, fg3, fg4);

• Finally, the partitioned table uses the scheme and the function to distribute data.

CREATE TABLE sales (

sale_id INT PRIMARY KEY,

sale_date DATE,

amount DECIMAL(10, 2)

)

ON psRange(sale_id);

Hence, the ‘sale_id’ column determines the partition based on defined ranges.

21. Describe the differences between SMALLINT, INT, and BIGINT.

22. Can you explain the purpose of the ENUM data type in SQL?

The enum data type is mostly used to define any column which has a predefined set of allowed rules. Therefore, it ensures data integrity by restricting the column to accept only the specified values.

23. What are the differences between a clustered index and a non-clustered index?

24. What is the difference between TEXT and BLOB data types?

25. What is the purpose of the AUTO_INCREMENT attribute in SQL?

This attribute can automatically generate a unique value for a column, especially when the column contains primary key values and each row is inserted into the table.

26. What is a self-join, and how would you use it?

One of the types of joining two databases is self-join, which joins the database table with itself. It is most useful when we need to compare rows within the same table. For example, you are given a database of ‘employees’ with some columns, applying self-join:

SELECT e1.name AS employee, e2.name AS manager

FROM employees e1

JOIN employees e2 ON e1.manager_id = e2.employee_id;

27. What is the difference between SQL and NoSQL databases?

28. What is a composite key? How do you create one?

Composite key in SQL mainly consists of two or more columns for uniquely identifying a particular row in a table. It can be created by combining multiple columns to form a unique constraint and then extracted through a composite key. For example:

CREATE TABLE employees (

emp_id INT,

dept_id INT,

PRIMARY KEY (emp_id, dept_id)

);

Here you can see the PRIMARY KEY constraint on ‘emp_id’ and ‘dept_id’, all together creating a composite key, hence considering the combination of both as unique.

29. What is the difference between UNION and UNION ALL?

30. How would you create a table with a foreign key that references another table?

For this, you can follow the below example format:

CREATE TABLE table_name (

column1 datatype PRIMARY KEY,

column2 datatype,

referenced_column datatype,

FOREIGN KEY (referenced_column) REFERENCES other_table (other_column)

);

Top Interview Questions Related to SQL Data Types and Tables

31. How can you rename an existing table in SQL?

ALTER TABLE old_table_name RENAME TO new_table_name;

32.Explain the process of dropping a table. What are the implications?

Dropping a table in SQL requires only a DROP TABLE statement. For Example:

DROP TABLE table_name;

After dropping a table, all the data, indexes, triggers and other constraints which are associated with that table also vanishes. Hence this action is permanent and cannot be undone or recovered once a table is dropped. It is highly crucial to use this statement with caution and may take any important data backup.

33. How do you alter an existing table to add a new column?

Syntax:

ALTER TABLE table_name ADD column_name datatype;

Example:

ALTER TABLE employees ADD birthdate DATE;

34. Explain the merge statement with an example

MERGE allows us to perform INSERT, UPDATE, and DELETE operations in a single statement based upon the results of a join between two databases or tables. For example, you want to merge two tables, ‘tableA’ and ‘tableB’:

MERGE INTO tableA AS t

USING tableB AS s

ON t.id = s.id

WHEN MATCHED THEN

UPDATE SET t.name = s.name, t.age = s.age

WHEN NOT MATCHED THEN

INSERT (id, name, age) VALUES (s.id, s.name, s.age)

WHEN NOT MATCHED BY SOURCE THEN

DELETE;

• MATCHED: this keyword updates existing rows in tableA if they match rows in tableB.
• NOT MATCHED: If rows are not matched, then it inserts new rows in the tableA.
• NOT MATCHED BY SOURCE: This one deletes any rows from tableA if they do not exist in tableB.

35. Can you explain the use of the DEFAULT constraint in a table column?

DEFAULT constraint specifies a default value for a column when there is no value present initially during the INSERT operation. For example:

CREATE TABLE employees (

id INT PRIMARY KEY,

name VARCHAR(50),

status VARCHAR(20) DEFAULT ‘Active’

);

36. What is the purpose of the CHECK constraint, and how is it used?

To ensure all the values in a column meet a given specific condition, the CHECK constraint is used.  Example:

CREATE TABLE employees (

id INT PRIMARY KEY,

name VARCHAR(50),

age INT,

CHECK (age >= 18)

);

37. How do you add a comment to a column in a table?

COMMENT ON COLUMN table_name.column_name IS ‘your comment here’;

38. Explain the difference between FLOAT and DECIMAL data types. When would you use each?

id INT PRIMARY KEY,

value FLOAT

id INT PRIMARY KEY,

amount DECIMAL(10, 2)

);

39. Explain the differences between CHAR and VARCHAR data types.

40. What are the implications of using the TIMESTAMP data type? How does it differ from DATETIME?

TIMESTAMP data type keeps records of both date and time to track changes in logs or records by automatically updating to the current standard date and time. Its values are stored in UTC and converted to the local time zone of the client upon usage. On the other hand, DAYTIME is better used for storing static date and time information which do not update dynamically with the timezones.

SQL Query Fundamental Interview Questions

41. Can you explain the basic structure of an SQL query?

The basic structure follows a specific syntax to chronically arrange a set of rules or conditions in a query. This allows a clear, non-complex and professional way of writing any query, where you can assign different conditions one by one. The basic structure consists of:

SELECT column1, column2

FROM table_name

WHERE condition

GROUP BY column

HAVING condition

ORDER BY column;

Example Table

42. Using SQL query, find the average salary of employees from the above table, which are hired before 2020 in each department.

SELECT Department, AVG(Salary) AS AverageSalary

FROM Employees

WHERE HireDate < ‘2020-01-01’

GROUP BY Department;

43. Retrieve the names of employees who have the highest salary in their respective departments.

SELECT e.FirstName, e.LastName, e.Department

FROM Employees e

WHERE e.Salary = (

SELECT MAX(Salary)

FROM Employees

WHERE Department = e.Department

);

44. Find the second-highest salary in the IT department.

SELECT MAX(Salary) AS SecondHighestSalary

FROM Employees

WHERE Salary < (

SELECT MAX(Salary)

FROM Employees

WHERE Department = ‘IT’

)

AND Department = ‘IT’;

45. List the employees whose hire date is within the last two years.

SELECT FirstName, LastName, HireDate

FROM Employees

WHERE HireDate >= DATEADD(year, -2, GETDATE());

46. Find the employees who have been hired for the longest duration but are not the highest paid in their department.

SELECT FirstName, LastName, Department, HireDate, Salary

FROM Employees

WHERE HireDate = (

SELECT MIN(HireDate)

FROM Employees

)

AND Salary < (

SELECT MAX(Salary)

FROM Employees

WHERE Department = Employees.Department

);

Practical Situation Based SQL Query Interview Questions

47. How do you enforce a unique constraint on multiple columns?

This unique key constraint makes sure that columns or combinations of columns that contain unique values across a table should not be lost. Hence it prevents duplicate entries, allowing each row to be uniquely identified. This can be done by using a UNIQUE key written before column names.

48. How do you handle NULL values in SQL?

NULL values represent missing or empty space in a database, where its position is not fixed and changes according to raw data entered into the database. To handle NULL values, there are several methods like:

1. Using IS NULL or IS NOT NULL to check if null columns exist or not in a table.

SELECT * FROM table_name WHERE column_name IS NULL;

SELECT * FROM table_name WHERE column_name IS NOT NULL;

2. Using COALESCE to replace NULL values with a specific value:

SELECT COALESCE(column_name, ‘N/A’) FROM table_name;

3. By using NULLIF to return NULL if two arguments are equal, or else it returns the first argument.

SELECT NULLIF(column_name, 0) FROM table_name;

4. Using IFNULL (for MySQL), ISNULL (SQL Server), and NVL (Oracle), for replacing NULL values with specific values just like COALESCE.

49. How do you handle database backups and restoration to ensure data availability and disaster recovery?

Ensuring data availability is crucial for disaster recovery because based on the amount of data available, regular backups and data checks can be conducted regularly at several intervals throughout the project. For many critical systems, backups should be scheduled frequently and stored with certain encryption methods to prevent data leaks or data threats. Transaction logs can help maintain data loss, and a well-documented disaster recovery plan provided by certain data expert organisations must be considered.

50. How do you implement role-based access control (RBAC) in a database to manage user permissions?

1. Create Roles: Assigning different roles to everyone based on the access levels such as admin, user, customer, etc.
2. Assigning Permissions: The second task after assigning roles is to assign different read and write permissions for everyone working.
3. Assign Users: Several roles can be assigned to users based on their responsibilities and skill sets.
4. Grant Role: Give necessary permission to their working databases and other resources needed for a perfect workflow in the organisation.
5. Manage Role Membership: Regularly review and update user roles to take on new responsibilities and challenges.

Conclusion

In conclusion, interview questions help you to find out the real situation where you have to answer random questions regarding your field. All you have to do is read all the related interview questions, documentation, theoretical parts and new updates coming to SQL and other related database technologies daily. Other than this, you can prepare by practising more complex queries, finding exact and on-point answers, solving database inequalities, handling null values, etc.