Top 80+ Advanced SQL Interview Questions & Answers

80+ Advanced SQL Interview Questions with Answer

1.   What is an Index in SQL?

An index in SQL allows for quicker retrieval of rows as opposed to scanning through the entire database which smartens up an operations speed seriousness. It helps avoid searching all the records in a particular table each time a user needs to find a certain piece of information. It is put similarly to a book index– one can always locate specific rows without looking through the entire table.

Types of Indexes:

• Clustered Index: Sorts and stores the data rows based on the index key.
• Non-Clustered Index: Maintains a separate structure from the data rows.
• Use Cases: Ideal for columns frequently used in WHERE clauses or JOIN operations.

Example:

CREATE INDEX idx_employee_name ON Employees(Name);

2.  Are NULL Values Equal to Zero?

No, NULL values are not equal to zero in SQL.

Explanation:

• NULL: Represents the absence of a value or unknown data.
• Zero: A numerical value representing ‘0’.

Differences:

• NULL indicates missing information.
• Zero is a defined, actual value.

3.  What is a Join in SQL, and What Are the Types of Joins?

A join in SQL is used to combine rows from two or more tables based on a related column between them.

Types of Joins:

4.  Name Different Types of Case Manipulation Functions Available in SQL.

SQL provides several functions to manipulate the case of strings:

• UPPER(): Converts all characters in a string to uppercase.

SELECT UPPER(‘sql’) AS UpperCase;

• LOWER(): Converts all characters in a string to lowercase.

SELECT LOWER(‘SQL’) AS LowerCase;

• INITCAP(): Capitalises the first letter of each word.

SELECT INITCAP(‘sql interview’) AS InitCap;

• LTRIM(): Removes leading spaces from a string.
• RTRIM(): Removes trailing spaces from a string.
• CONCAT(): Concatenates two strings (though not strictly a case function).

5.  What Are Local and Global Variables, and What Are Their Differences?

6.  Name the Function Used to Remove Spaces at the End of a String

The RTRIM() function is used to remove trailing spaces from a string in SQL.

Example:

SELECT RTRIM(‘Hello World   ‘) AS TrimmedString;

— Output: ‘Hello World’

7.  What is the Difference Between TRUNCATE and DROP Statements?

8. Which Operator is Used in Queries for Pattern Matching?

The LIKE operator is used in SQL queries for pattern matching.

Usage:

• Wildcard Characters:
  • % : Represents zero or more characters.
  • _ : Represents a single character.

Example:

SELECT * FROM Customers

WHERE Name LIKE ‘A%’;  — Names starting with ‘A’

9.  Define SQL ORDER BY Statement.

The ORDER BY statement in SQL is used to sort the result set of a query based on one or more columns.

Features:

• Sorting Order: Ascending (ASC) or Descending (DESC).
• Multiple Columns: Can sort by multiple columns in a specific sequence.

Example:

10.  List the differences between GROUP BY and ORDER BY.

11. Explain the SQL HAVING Statement

The HAVING statement in SQL is used to filter groups of rows created by GROUP BY based on a specified condition.

Key Points:

• Used with Aggregate Functions: Such as COUNT(), SUM(), AVG().
• Filters Groups: Unlike WHERE, which filters individual rows.

Example:

12.  Write an SQL Query to Fetch the Second Highest Salary from the Employee Table.

We are given the following Employee table:

Query:

This query finds the maximum salary that is less than the highest salary, giving the second highest salary.

13. Given a Student Table with Columns (ID, Name, Marks), Write an SQL Query to Find the Highest Marks in Each Subject.

Query:

This query retrieves the highest marks for each subject in the Student table.

14. Write a Query to Fetch All Employees Who Joined in the Year 2022 from the Employee Table.

We are given the following Employee table:

Query:

15.  What is the ON DELETE CASCADE Constraint?

The ON DELETE CASCADE constraint regulates ethical rules on how a delete operation will be propagated from the parent relational table to its child relational table. The child table records will be deleted without any further action that would need to be performed by the user or application to embed the clause within the child relationships.

Example:

In this example, deleting a customer will automatically delete all their associated orders.

16. What are the differences between UNION and UNION ALL operators?

17.  Name the Operator Used in Queries for Appending Two Strings

To append two strings in SQL, you can use different operators depending on the database:

• Oracle and PostgreSQL: Use the || operator.
  • Example: ‘Hello’ || ‘ World’ results in ‘Hello World’.
• SQL Server and MySQL: Use the CONCAT() function.
  • Example: CONCAT(‘Hello’, ‘ World’) results in ‘Hello World’.

18.  Explain SQL AND OR Statements with an Example

The AND and OR operators combine multiple conditions in a WHERE clause.

• AND Operator: Returns true if all conditions are true.
• OR Operator: Returns true if at least one condition is true.

Example:

SELECT * FROM Employees

WHERE Department = ‘Sales’ AND Salary > 50000;

SELECT * FROM Employees

WHERE Department = ‘Sales’ OR Department = ‘Marketing’;

In the first query, it selects employees in Sales with salaries over 50,000. In the second, it selects employees in either Sales or Marketing.

19. Explain the differences between clustered and non-clustered indexes.

20. Define the BETWEEN Statement in SQL

The BETWEEN operator selects values within a specified range. It is inclusive of the start and end values.

Syntax:

Example:

This retrieves products priced between 10 and 20 units.

21.  Write an SQL Query to Retrieve Duplicate Rows Based on the Email Column in the Users Table.

We are given the following Users table:

Query:

This query identifies email addresses that appear more than once in the Users table.

22.  Write an SQL Query to Find the Total Sales for Each Product from the Sales Table with Columns (ProductID, Quantity, Price).

This query calculates the total sales for each product based on the quantity and price.

23. List the differences between primary keys and foreign keys.

24. Write an SQL Query to Find the Names of Employees Starting with ‘A’

To find employee names that start with ‘A’, use the LIKE operator with a wildcard %.

Query:

This selects all employees whose names begin with the letter ‘A’.

25.  What is a Cursor in SQL?

A cursor is one of many database objects that can be used to fetch and manipulate individual records. It enables procedures to be executed on single rows of data returned by the execution of a query, thus facilitating various operations that otherwise cannot be accomplished through standard SQL commands alone.

26. Compare CHAR and VARCHAR data types in SQL.

27. What is the Main Use of the NVL Function in SQL?

The NVL function replaces NULL values with a specified value. It’s commonly used in Oracle databases to handle NULL values in expressions.

Example:

SELECT NVL(commission, 0) FROM Employees;

This replaces any NULL commission values with 0.

28. Why Do We Use COMMIT and ROLLBACK Commands?

These commands manage transactions in SQL to ensure data integrity.

• COMMIT: Saves all changes made during the current transaction to the database permanently.
• ROLLBACK: Undoes all changes made during the current transaction, reverting the database to the last committed state.

They allow controlled data manipulation and error recovery.

29.  What are the differences between scalar functions and aggregate functions in SQL?

30. Explain the WITH Clause in SQL

The WITH clause, also known as a Common Table Expression (CTE), allows you to define a temporary named result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.

Example:

This creates a temporary result set SalesSummary to simplify complex queries.

31.  Write a Query to List All Managers and Their Corresponding Team Members from an Employee Table with ManagerID.

We are given the following Employee table:

Query:

This query pairs each manager with their respective team members.

32.  What is the Difference Between BETWEEN and IN Operators in SQL?

33. What is a Trigger?

A trigger is essentially a default or stored command that runs automatically when a defined event occurs for a specified table or view, like an INSERT command or an UPDATE command. Triggers can be used for enforcing business rules, maintaining audit trails and synchronisation of Tables.

34. What is the Difference Between COALESCE and ISNULL?

• COALESCE:
  • Returns the first non-NULL expression in a list.
  • Standard SQL function.
  • Can handle multiple arguments.
• ISNULL:
  • Replaces NULL with a specified replacement value.
  • Specific to certain databases like SQL Server.
  • Accepts only two arguments.

35.  What are Nested Triggers?

As the terminology suggests, a nested trigger is a trigger that triggers any other trigger. This is when a trigger fires an insert, update or delete command which triggers another stored trigger or two stored triggers consecutively. They can be useful for implementing certain complicated business rules but should be used sparingly due to the problems they can introduce.

36.  What is the Difference Between Primary Key and Unique Constraints?

37. What is a Live Lock?

A live lock occurs when two or more transactions keep retrying an operation due to conflicts, but no progress is made because each keeps stepping aside for the other. Unlike a deadlock, where processes wait indefinitely, live locks are constantly active but unable to proceed.

38.  How Can You Find the Available Constraint Information in a Table?

You can query the database’s metadata tables to retrieve constraint information.

Example in SQL Server:

This returns details about constraints like primary keys, foreign keys, and unique constraints for the specified table.

39. How Do We Avoid Getting Duplicate Entries in a Query Without Using the DISTINCT Keyword?

Use the GROUP BY clause to aggregate identical rows.

Example:

SELECT Name, Age

FROM Employees

GROUP BY Name, Age;

This groups identical Name and Age combinations, effectively eliminating duplicates.

40.  What is the Difference Between NVL and NVL2 Functions?

• NVL(expr1, expr2):
  • If expr1 is NULL, returns expr2; else returns expr1.
  • Example: NVL(NULL, ‘Default’) returns ‘Default’.
• NVL2(expr1, expr2, expr3):
  • If expr1 is not NULL, returns expr2; else returns expr3.
  • Example: NVL2(NULL, ‘Not Null’, ‘Is Null’) returns ‘Is Null’.

They help in handling NULL values with more control over the output.

41.  Compare the functionalities of COALESCE and ISNULL.

42. What Are ACID Properties?

ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure reliable transaction processing in databases.

• Atomicity: Transactions are all-or-nothing.
• Consistency: Transactions bring the database from one valid state to another.
• Isolation: Concurrent transactions do not interfere with each other.
• Durability: Once committed, transactions persist even in case of failures.

43. What is the Need for Group Functions in SQL?

Group functions, also known as aggregate functions, perform calculations on sets of rows and return a single value. They are essential for summarising data.

Common Group Functions:

• COUNT(): Counts rows.
• SUM(): Adds values.
• AVG(): Calculates average.
• MAX(): Finds the maximum value.
• MIN(): Finds the minimum value.

Example:

44. What is the difference between HAVING and WHERE clauses in SQL?

45. What is the Purpose of a MERGE Statement?

The MERGE statement in SQL allows you to perform INSERT, UPDATE, or DELETE operations in a single statement based on the results of a join between two tables. It simplifies data synchronisation tasks.

Example:

46.  How Can You Fetch Common Records from Two Tables?

To retrieve common records from two tables, you can use the INTERSECT operator or perform an INNER JOIN.   Using INTERSECT: FROM TableA INTERSECT SELECT Name FROM TableB;   Using INNER JOIN: SELECT A.Name FROM TableA A INNER JOIN TableB B ON A.Name = B.Name;

47. What Are Some Major Differences Between SQL and NoSQL?

SQL Databases:

• Structure: Relational, table-based.
• Schema: Fixed schema.
• Scalability: Vertical scaling.
• Use Cases: Complex queries and transactions.

NoSQL Databases:

• Structure: Non-relational, can be document, key-value, graph, etc.
• Schema: Flexible or dynamic schema.
• Scalability: Horizontal scaling.
• Use Cases: Big data, real-time web apps.

48.  What are the differences between DELETE and TRUNCATE commands in SQL?

49.  What Do You Understand About an SQL Sandbox?

An SQL sandbox is referred to as a dev environment where the user has the ability to use SQL commands, formulate and run queries without affecting the database that is in use. This makes it ideal for testing, learning, and also in the development stage, which is good practice.

Features:

• Isolation: Changes do not impact live data.
• Safety: Prevents accidental data loss.
• Flexibility: Allows trying out new queries and procedures.

50. What Are the Advantages of PL/SQL Functions?

PL/SQL functions offer several benefits:

• Modularity: Encapsulate reusable code.
• Performance: Reduce network traffic by executing multiple operations in a single call.
• Security: Control access to data through function permissions.
• Maintainability: Simplify complex operations with clear, organised code.

Example:

51.  What is the Difference Between Cluster and Non-Cluster Indexes?

Clustered Index:

• Determines the physical order of data in the table.
• Only one per table.
• Improves performance for range queries.

Non-Clustered Index:

• Separate from the table data.
• Multiple per table allowed.
• Ideal for columns frequently searched but not used for sorting data.

52.  What is the SQL Query to Display the Current Date?

In SQL Server:

SELECT GETDATE() AS CurrentDate;

In Oracle:

SELECT SYSDATE FROM dual;

In PostgreSQL:

SELECT CURRENT_DATE;

53.  What is the Difference Between SQL DELETE and SQL TRUNCATE Commands?

DELETE:

• Removes specified rows using a WHERE clause.
• Can be rolled back.
• Triggers are fired.

TRUNCATE:

• Removes all rows without a WHERE clause.
• Faster and uses fewer system resources.
• Cannot be rolled back in some systems.
• Resets table identity.

54. What Are the Differences Between CHAR and VARCHAR Data Types in SQL?

CHAR:

• Fixed-length.
• Pads with spaces to match defined length.
• Suitable for data with consistent length.

VARCHAR:

• Variable-length.
• Stores only the actual characters.
• More efficient for varying data sizes.

Example:

55.  What is SQL Injection, and How Can You Prevent It?

SQL Injection is a vulnerability within a database whereby unwanted SQL statements are inserted in the input and this gives the attacker the ability to either manipulate or gain access to the database.

Prevention Methods:

• Use Prepared Statements: Parameterised queries prevent injection.
• Validate Input: Ensure user inputs meet expected formats.
• Least Privilege: Restrict database permissions.
• Escape Inputs: Properly handle special characters.

Example of Prepared Statement:

PREPARE stmt FROM ‘SELECT * FROM Users WHERE Username = ? AND Password = ?’;

EXECUTE stmt USING @username, @password;

56.  Write an SQL Query to Fetch the Top 3 Students with the Highest Marks in the Student Table.

This query fetches the top 3 students with the highest marks.

57.  What is a Common Table Expression (CTE) in SQL?

A Common Table Expression (CTE) is a temporary result set defined within a WITH clause that can be referenced in subsequent queries. It enhances query readability and organisation, especially for complex operations.

Syntax:

58.  What is Collation in SQL?

Collation in SQL defines the set of rules for comparing and sorting strings. It determines how data is ordered and whether string comparisons are case-sensitive or accent-sensitive.

Key Aspects:

• Sort Order: Determines the sequence of characters.
• Case Sensitivity: Defines if ‘A’ is equal to ‘a’.
• Accent Sensitivity: Treats accented characters as distinct.

Example:

59. Given a Products Table with Columns (ProductID, ProductName, Price), Write a Query to Fetch Products Priced Above $100.

We are given the following Products table:

60.  Write an SQL Query to Find Employees Whose Salaries Are Between $50,000 and $80,000.

We are given the following Employee table:

Query:

61.  Explain Query Optimisation in SQL.

Query optimisation can be simply put as the transformation of a SQL statement into its best executable form with the aim of utilising fewer resources and taking a shorter time to process the statement. There are different ways to write a query, for example, to select only one record from a table, which has many records, the database’s query optimiser will investigate several methods for the efficient performance of this query.

Optimisation Techniques:

• Indexing: Speeds up data retrieval.
• Avoiding Unnecessary Columns: Select only required data.
• Using Joins Appropriately: Optimise join types and conditions.
• Analysing Execution Plans: Identify and address bottlenecks.

Example: Using indexes to optimise a JOIN operation:

62.  Where is the MyISAM Table Stored in the SQL Database?

The MyISAM storage engine is specific to MySQL. MyISAM tables are stored as three separate files within the database directory:

1. .frm: Stores the table format.
2. .MYD: Contains the actual data.
3. .MYI: Holds the index information.

63.  What Do You Understand About Data Integrity, and How Do You Ensure It in SQL?

Data integrity refers to the accuracy and consistency of data within a database. It ensures that data remains reliable and is not corrupted.

Ensuring Data Integrity:

• Constraints: Use primary keys, foreign keys, UNIQUE, NOT NULL, and CHECK constraints.
• Transactions: Utilise COMMIT and ROLLBACK to maintain consistency.
• Normalization: Organise data to reduce redundancy.
• Access Controls: Restrict permissions to prevent unauthorised changes.

64.  Write an SQL Query to Count the Number of Employees in Each Department from the Department Table.

We are given the following Employee table:

Query:

65.  What Are the Advantages of Normalization in SQL?

Data normalization is the arrangement of database tables in such a way as to eliminate duplication and to enhance the integrity of data. In addition, it makes sure that every unit of data is inputted into the system only one time.

Advantages:

• Eliminates Duplicate Data: Reduces storage costs.
• Improves Data Integrity: Prevents anomalies during insert, update, and delete operations.
• Enhances Query Performance: Streamlines data retrieval.
• Simplifies Maintenance: Easier to manage and update data structures.

66. What Do You Understand About Subqueries in SQL?

A subquery is one of the contained queries in a parent or outer SQL query. It may be included in SELECT, INSERT, UPDATE or DELETE statements in order to carry out activities that are dependent on the results of a subquery.

Types:

• Scalar Subquery: Returns a single value.
• Correlated Subquery: References columns from the outer query.
• Uncorrelated Subquery: Independent of the outer query.

Example:

67.  What Are Scalar and Aggregate Functions in SQL?

Scalar Functions:

• Operate on a single value.
• Return a single value.
• Examples: UPPER(), LOWER(), GETDATE()

Aggregate Functions:

• Operate on a set of values.
• Return a single summarising value.
• Examples: COUNT(), SUM(), AVG()

Example:

68.  What Do You Understand About Collation in SQL?

Note: This question was previously answered. To avoid repetition, consider skipping or acknowledging.

Collation in SQL defines the rules for sorting and comparing string data, including case and accent sensitivity. It affects how ORDER BY clauses sort data and how string comparisons are performed.

69. What Are Views in SQL, and What Are Their Advantages?

Views are virtual tables created by querying one or more tables. They do not store data physically but provide a way to simplify complex queries and enhance security.

Advantages:

• Simplify Complex Queries: Encapsulate complex joins and calculations.
• Enhance Security: Restrict user access to specific data.
• Maintain Consistency: Provide a consistent interface to the data.
• Encourage Reusability: Reuse common query logic.

70. Write an SQL Query to Calculate the Average Salary for Each Department from the Employee Table.

We are given the following Employee table:

Query:

This query calculates the average salary for each department.

71. Find Employees Earning Above Average Salary.

Given a table Employee having the following columns: ID, Name, Salary, and DepartmentID, execute the query below.

Table Data:

Query:

Question: What will be the output of the query?

Answer:

72. List Products With Stock Greater Than 50.

Given a table Products having the following columns: ProductID, ProductName, Price, and Stock, execute the query below.

Table Data:

Query:

Question: What will be the output of the query?

Answer:

73. Identify Departments With More Than One Employee.

Given a table Employee having the following columns: ID, Name, DepartmentID, and Salary, execute the query below.

Table Data:

Query:

Question: What will be the output of the query?

Answer:

74. Fetch the Most Expensive Product.

Given a table Products having the following columns: ProductID, ProductName, and Price, execute the query below.

Table Data:

Query:

Question: What will be the output of the query?

Answer:

75. List Customers Who Placed Orders Above $300.

Given a table Orders having the following columns: OrderID, CustomerName, and TotalAmount, execute the query below.

Table Data:

Query:

Question: What will the query output be?

Answer:

76. Fetch Customers Who Have Not Placed Any Orders.

Given two tables: Customers with columns CustomerID and CustomerName, and Orders with columns OrderID and CustomerID, execute the query below.

Customers Table:

Orders Table:

Query:

Question: What will be the output of the query?

Answer:

77. Find the Highest Scoring Students in Each Subject.

Given a table Student having the following columns: ID, Name, Subject, and Marks, execute the query below.

Table Data:

Query:

Question: What will be the output of the query?

Answer:

78. Identify Products That Have Never Been Sold.

Given two tables: Products with columns ProductID and ProductName, and Sales with columns SaleID and ProductID, execute the query below.

Products Table:

Sales Table:

Query:

Question: What will be the output of the query?

Answer:

79. Calculate Total Revenue Per Category.

Given a table Products with columns Category, Price, and QuantitySold, execute the query below.

Table Data:

Query:

Question: What will be the output of the query?

Answer:

80. Find the Earliest Order Date for Each Customer.

Given a table Orders having the following columns: OrderID, CustomerID, and OrderDate, execute the query below.

Table Data:

Query:

Question: What will be the output of the query?

Answer:

81. Explain the differences between INNER JOIN and OUTER JOIN.

Also Read: Mostly Asked DBMS Interview Questions With Answers