Top 80+ Advanced SQL Interview Questions & Answers

Updated on December 17, 2024

Article Outline

80+ Advanced SQL Interview Questions with Answer Conclusion FAQs

Knowing SQL (Structured Query Language) is essential for anyone working with databases. It makes it possible for users to work with data that would include expanding, retrieving, and examining it, and that is why it becomes useful for data analysts, software developers, and database administrators. SQL remains one of the core tools for the proper operation and management of data systems in many companies.

In this article, we are going to share with you a bunch of over 75+ advanced interview questions within the domain of SQL and their answers so you can be ready for the tough technical interviews. By the end of this article, we hope that you will grasp the concept of advanced SQL concepts and therefore will be in a position to answer even the most difficult interview questions you can find.

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:

Join Type	Description	Example Usage
INNER JOIN	Returns records with matching values in both tables.	Fetching employees and their departments.
LEFT JOIN (LEFT OUTER JOIN)	Returns all records from the left table and matched records from the right table.	Listing all customers and their orders.
RIGHT JOIN (RIGHT OUTER JOIN)	Returns all records from the right table and matched records from the left table.	Listing all orders and their customers.
FULL OUTER JOIN	Returns records when there is a match in one of the tables.	Combining all employees and managers.
CROSS JOIN	Returns the Cartesian product of both tables.	Generating all possible combinations.

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?

Aspect	Local Variables	Global Variables
Scope	Limited to the declaring block	Available across the session
Lifetime	Temporary, within function execution	Persistent throughout the session
Usage	Specific to a function or procedure	Shared data across multiple procedures

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?

Feature	TRUNCATE	DROP
Data Removal	All rows removed	Table and all data removed
Structure	Table structure remains	Table structure deleted
Speed	Faster for large tables	Depends on table size
Recovery	Limited recovery options	Complete removal

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:

SELECT Name, Salary

FROM Employees

ORDER BY Salary DESC, Name ASC;

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

Aspect	GROUP BY	ORDER BY
Purpose	Groups rows into aggregates based on column values.	Sorts the result set based on column values.
Aggregate Functions	Used with aggregate functions like SUM(), AVG().	Does not require aggregate functions.
Result Set	Reduces the number of rows.	Maintains the same number of rows.
Order	No implicit sorting of groups.	Explicitly sorts rows in ascending or descending order.

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:

SELECT Department, COUNT(*) AS EmployeeCount

FROM Employees

GROUP BY Department

HAVING COUNT(*) > 10;

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

We are given the following Employee table:

ID	Name	Salary
1	John	50000
2	Alice	60000
3	Bob	55000
4	Charlie	70000

Query:

SELECT MAX(Salary) AS SecondHighestSalary

FROM Employee

WHERE Salary < (SELECT MAX(Salary) FROM Employee);

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:

SELECT Subject, MAX(Marks) AS HighestMarks

FROM Student

GROUP BY Subject;

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:

ID	Name	JoinDate
1	John	2022-01-15
2	Alice	2021-05-10
3	Bob	2022-09-20
4	Charlie	2020-11-01

Query:

SELECT *

FROM Employee

WHERE YEAR(JoinDate) = 2022;

This query selects all employees who joined in the year 2022.

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:

CREATE TABLE Orders (

OrderID int PRIMARY KEY,

CustomerID int,

FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ON DELETE CASCADE

);

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

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

Aspect	UNION	UNION ALL
Duplicates	Removes duplicate rows.	Retains duplicate rows.
Performance	Slower due to duplicate elimination.	Faster as no duplicate checking is performed.
Use Case	When unique results are required.	When all rows, including duplicates, are needed.

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.

Aspect	Clustered Index	Non-Clustered Index
Storage	Alters the physical order of table rows.	Separate from the actual table data.
Quantity	One per table.	Multiple allowed per table.
Performance	Faster for range queries.	Slower for range queries compared to clustered indexes.
Use Case	Suitable for primary key or sorting purposes.	Suitable for searching on non-key columns.

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:

SELECT column_name

FROM table_name

WHERE column_name BETWEEN value1 AND value2;

Example:

SELECT * FROM Products

WHERE Price BETWEEN 10 AND 20;

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:

ID	Name	Email
1	John	john@example.com
2	Alice	alice@example.com
3	Bob	john@example.com
4	Diana	diana@example.com

Query:

SELECT Email, COUNT(*) AS DuplicateCount

FROM Users

GROUP BY Email

HAVING COUNT(*) > 1;

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).

SELECT ProductID, SUM(Quantity * Price) AS TotalSales

FROM Sales

GROUP BY ProductID;

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.

Aspect	Primary Key	Foreign Key
Definition	Uniquely identifies each record in a table.	Establishes a link between two tables.
Uniqueness	Values must be unique.	Values can be repeated.
Nullability	Cannot have NULL values.	Can contain NULL values.
Purpose	Ensures data integrity within a table.	Maintains referential integrity between tables.

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:

SELECT Name

FROM Employees

WHERE Name LIKE 'A%';

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.

Aspect	CHAR	VARCHAR
Length	Fixed-length.	Variable-length.
Storage	Pads with spaces to match the defined length.	Stores only the entered characters.
Performance	Faster for fixed-length data.	More efficient for varying-length data.
Use Case	Suitable for uniform data lengths like postal codes.	Suitable for non-uniform data lengths like names.

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?

Aspect	Scalar Functions	Aggregate Functions
Input	Operate on single values.	Operate on a set of values.
Output	Return a single value for each input.	Return a single summarised value.
Examples	UPPER(), LOWER(), GETDATE().	SUM(), COUNT(), AVG().
Use Case	Used for row-level operations.	Used for group-level operations.

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:

WITH SalesSummary AS (

SELECT SalesPerson, SUM(Amount) AS TotalSales

FROM Sales

GROUP BY SalesPerson

)

SELECT * FROM SalesSummary WHERE TotalSales > 100000;

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:

ID	Name	ManagerID
1	John	NULL
2	Alice	1
3	Bob	1
4	Charlie	2

Query:

SELECT M.Name AS ManagerName, E.Name AS TeamMember

FROM Employee E

JOIN Employee M ON E.ManagerID = M.ID;

This query pairs each manager with their respective team members.

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

Aspect	BETWEEN Operator	IN Operator
Purpose	Selects values within a range	Selects values matching a list
Syntax	BETWEEN value1 AND value2	IN (value1, value2, …)
Use Case	Continuous ranges (numbers, dates)	Specific, discrete values
Example	Age BETWEEN 18 AND 30	Country IN (‘USA’, ‘Canada’, ‘UK’)

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?

Feature	Primary Key	Unique Constraint
Purpose	Uniquely identifies each record in a table	Ensures all values in a column are unique
Null Values	Does not allow NULL values	Allows NULL values (except in some databases)
Number per Table	Only one primary key per table	Multiple unique constraints allowed

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:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE TABLE_NAME = 'YourTableName';

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.

Aspect	COALESCE	ISNULL
Purpose	Returns the first non-NULL value from a list.	Replaces NULL with a specified value.
Arguments	Accepts multiple arguments.	Accepts only two arguments.
Standard	ANSI standard function.	Specific to certain databases (e.g., SQL Server).
Use Case	More flexible for handling multiple fallback values.	Simpler when only one fallback value is needed.

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:

SELECT Department, COUNT(*) AS EmployeeCount

FROM Employees

GROUP BY Department;

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

Aspect	WHERE	HAVING
Purpose	Filters rows before grouping.	Filters groups after grouping.
Aggregate Functions	Cannot be used with aggregate functions.	Works with aggregate functions like SUM().
Execution	Applied to individual rows.	Applied to aggregated data.
Use Case	Used to filter raw data.	Used to filter grouped results.

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:

MERGE INTO TargetTable AS T

USING SourceTable AS S

ON T.ID = S.ID

WHEN MATCHED THEN

UPDATE SET T.Value = S.Value

WHEN NOT MATCHED THEN

INSERT (ID, Value) VALUES (S.ID, S.Value);

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?

Aspect	DELETE	TRUNCATE
Operation Type	DML (Data Manipulation Language).	DDL (Data Definition Language).
Filter Rows	Can delete specific rows using a WHERE clause.	Deletes all rows; no filtering.
Rollback	Supports rollback if within a transaction.	Rollback may not be supported in some databases.
Speed	Slower as it logs each row deletion.	Faster as it deallocates data pages directly.
Triggers	Triggers are fired.	Triggers are not fired.

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:

CREATE FUNCTION GetEmployeeName (emp_id INT) RETURN VARCHAR2 AS

BEGIN

RETURN (SELECT Name FROM Employees WHERE ID = emp_id);

END;

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:

CREATE TABLE Example (

Fixed CHAR(10),

Variable VARCHAR(10)

);

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.

SELECT Name, Marks

FROM Student

ORDER BY Marks DESC

LIMIT 3;

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:

WITH CTE_Name AS (

SELECT column1, column2

FROM table_name

WHERE condition

)

SELECT *

FROM CTE_Name;

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:

SELECT * FROM Employees

ORDER BY Name COLLATE Latin1_General_CS_AS;

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:

SELECT ProductID, ProductName, Price

FROM Products

WHERE Price > 100;

This query fetches all products priced above $100.

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

We are given the following Employee table:

ID	Name	Salary
1	John	50000
2	Alice	75000
3	Bob	85000
4	Charlie	65000

Query:

SELECT Name, Salary

FROM Employee

WHERE Salary BETWEEN 50000 AND 80000;

This query retrieves employees with salaries between $50,000 and $80,000.

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:

CREATE INDEX idx_employee_department ON Employees(DepartmentID);

SELECT Employees.Name, Departments.DepartmentName

FROM Employees

JOIN Departments ON Employees.DepartmentID = Departments.ID;

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:

.frm: Stores the table format.
.MYD: Contains the actual data.
.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:

ID	Name	DepartmentID
1	John	101
2	Alice	102
3	Bob	101
4	Charlie	103

Query:

SELECT DepartmentID, COUNT(*) AS EmployeeCount

FROM Employee

GROUP BY DepartmentID;

This query counts the number of employees in each department.

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:

SELECT Name

FROM Employees

WHERE DepartmentID = (SELECT ID FROM Departments WHERE DepartmentName = 'Sales');

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:

SELECT UPPER(Name) AS UpperName, COUNT(*) OVER() AS TotalEmployees

FROM Employees;

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:

ID	Name	Salary	DepartmentID
1	John	50000	101
2	Alice	75000	102
3	Bob	55000	101
4	Charlie	80000	102

Query:

SELECT DepartmentID, AVG(Salary) AS AverageSalary

FROM Employee

GROUP BY DepartmentID;

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:

ID	Name	Salary	DepartmentID
1	John	50000	101
2	Alice	75000	102
3	Bob	55000	101
4	Charlie	80000	102

Query:

SELECT Name, Salary

FROM Employee

WHERE Salary > (SELECT AVG(Salary) FROM Employee);

Question: What will be the output of the query?

Answer:

Name	Salary
Alice	75000
Charlie	80000

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:

ProductID	ProductName	Price	Stock
101	Laptop	1200	25
102	Keyboard	50	75
103	Monitor	200	100
104	Mouse	20	10

Query:

SELECT ProductName, Stock

FROM Products

WHERE Stock > 50;

Question: What will be the output of the query?

Answer:

ProductName	Stock
Keyboard	75
Monitor	100

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:

ID	Name	DepartmentID	Salary
1	John	101	50000
2	Alice	102	75000
3	Bob	101	55000
4	Charlie	103	80000

Query:

SELECT DepartmentID, COUNT(*) AS EmployeeCount

FROM Employee

GROUP BY DepartmentID

HAVING COUNT(*) > 1;

Question: What will be the output of the query?

Answer:

DepartmentID	EmployeeCount
101	2

74. Fetch the Most Expensive Product.

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

Table Data:

ProductID	ProductName	Price
101	Laptop	1200
102	Keyboard	50
103	Monitor	200
104	Mouse	20

Query:

SELECT ProductName, Price

FROM Products

WHERE Price = (SELECT MAX(Price) FROM Products);

Question: What will be the output of the query?

Answer:

ProductName	Price
Laptop	1200

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:

OrderID	CustomerName	TotalAmount
101	John	200
102	Alice	500
103	Bob	150
104	Charlie	600

Query:

SELECT CustomerName, TotalAmount

FROM Orders

WHERE TotalAmount > 300;

Question: What will the query output be?

Answer:

CustomerName	TotalAmount
Alice	500
Charlie	600

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:

CustomerID	CustomerName
1	John
2	Alice
3	Bob
4	Charlie

Orders Table:

OrderID	CustomerID
101	1
102	2

Query:

SELECT CustomerName

FROM Customers

WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders);

Question: What will be the output of the query?

Answer:

CustomerName

Bob

Charlie

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:

ID	Name	Subject	Marks
1	Alice	Math	95
2	Bob	Math	89
3	Charlie	Science	92
4	Diana	Science	98

Query:

SELECT Subject, MAX(Marks) AS HighestMarks

FROM Student

GROUP BY Subject;

Question: What will be the output of the query?

Answer:

Subject	HighestMarks
Math	95
Science	98

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:

ProductID	ProductName
101	Laptop
102	Keyboard
103	Monitor

Sales Table:

SaleID	ProductID
201	101
202	102

Query:

SELECT ProductName

FROM Products

WHERE ProductID NOT IN (SELECT ProductID FROM Sales);

Question: What will be the output of the query?

Answer:

ProductName

Monitor

79. Calculate Total Revenue Per Category.

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

Table Data:

Category	Price	QuantitySold
Electronics	1200	10
Furniture	500	5
Electronics	200	20
Furniture	100	15

Query:

SELECT Category, SUM(Price * QuantitySold) AS TotalRevenue

FROM Products

GROUP BY Category;

Question: What will be the output of the query?

Answer:

Category	TotalRevenue
Electronics	16000
Furniture	3250

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:

OrderID	CustomerID	OrderDate
101	1	2023-01-15
102	2	2023-01-10
103	1	2023-02-05
104	2	2023-03-01

Query:

SELECT CustomerID, MIN(OrderDate) AS EarliestOrderDate

FROM Orders

GROUP BY CustomerID;

Question: What will be the output of the query?

Answer:

CustomerID	EarliestOrderDate
1	2023-01-15
2	2023-01-10

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

Aspect	INNER JOIN	OUTER JOIN
Definition	Returns rows with matching values in both tables.	Returns rows with or without matches in one or both tables.
Types	No subtypes.	Includes LEFT JOIN, RIGHT JOIN, and FULL JOIN.
Result Set	Contains only matching rows.	Contains matching and non-matching rows (with NULLs for missing values).
Use Case	To fetch only common data.	To fetch complete data, including unmatched rows.

Also Read: Mostly Asked DBMS Interview Questions With Answers

Get curriculum highlights, career paths, industry insights and accelerate your technology journey.

Download brochure

Conclusion

It is crucial to grasp SQL’s core principles in order to use and handle data in today’s resourceful world. Advanced SQL features such as joins, functions, and optimisation techniques allow you to manage intricate datasets and maintain data consistency. These kinds of skills are especially in demand in technical positions such as system analysts, DBAs, and other data-focused developers.

This blog helps you prepare for answering advanced SQL interview questions and demonstrates your competence to prospective employers. Continuous practice and comprehension of these topics will enable you to develop SQL skills that every organisation regards immensely. Explore SQL with Hero Vired’s Certificate Program in DevOps & Cloud Engineering with Microsoft and get a professional certification.

FAQs

What are the primary responsibilities of an SQL developer?

The role of the SQL Developers includes creating, writing queries, improving performance and security, and ensuring that the data is reliable.

How necessary are SQL skills for data analysts?

SQL is very important to data analysts whose work involves the extraction, modification and analysis of data stored in relational databases.

What certifications can help in landing an SQL-related job?

Certifications such as Microsoft SQL Server Certificate, Oracle Database Certificate, and PostgreSQL Certificate may boost your respect.

What tools are commonly used by SQL professionals?

Database administrators often use SQL Server Management Studio, MySQL Workbench, and pgAdmin to manage databases.

How can I prepare for an SQL job interview?

Practice writing queries, understand database design principles, and review advanced concepts like indexing and query optimisation.

What industries demand SQL professionals?

Industries like finance, healthcare, e-commerce, and IT heavily rely on SQL for data management and analysis.

Is knowledge of NoSQL databases necessary for SQL jobs?

While not mandatory, understanding NoSQL complements SQL skills and expands job opportunities.

Updated on December 17, 2024

Link

Upskill with expert articles

View all

Free courses curated for you

Basics of Python

5 Hrs. duration

Beginner level

9 Modules

Certification included

1800+ Learners

View

Essentials of Excel

4 Hrs. duration

Beginner level

12 Modules

Certification included

2200+ Learners

View

Basics of SQL

12 Hrs. duration

Beginner level

12 Modules

Certification included

2600+ Learners

View

Hero Vired is a leading LearnTech company dedicated to offering cutting-edge programs in collaboration with top-tier global institutions. As part of the esteemed Hero Group, we are committed to revolutionizing the skill development landscape in India. Our programs, delivered by industry experts, are designed to empower professionals and students with the skills they need to thrive in today’s competitive job market.

Data Science

Accelerator Program in Business Analytics & Data Science

Integrated Program in Data Science, AI and ML

Accelerator Program in AI and Machine Learning

Advanced Certification Program in Data Science & Analytics

Certification Program in Data Analytics

Technology

Certificate Program in Full Stack Development with Specialization for Web and Mobile

Certificate Program in DevOps and Cloud Engineering

Certificate Program in Application Development

Certificate Program in Cybersecurity Essentials & Risk Assessment

Finance