14. Create a query to obtain display employees having salaries equal to or greater than 150000.
How to Answer:
Explain that numeric filtering using SQL’s WHERE clause is crucial for payroll management, budgeting, and compensation benchmarking in enterprise applications. This enables backend APIs to provide filtered datasets to frontends like React.js and supports real-time analytics in scalable, containerized environments.
Sample Answer:
To retrieve employees with salaries equal to or above ₹150,000, use a conditional filter with the WHERE clause. This type of filtering is commonly used in payroll dashboards, HR analytics tools, and compliance systems.
Example:
SELECT EmpName FROM Employees WHERE Salary >= 150000;
Output:
EmpName
------------
Rajesh Kumar
Neha Sharma
Output Explanation:
This query selects employees whose salary is ₹150,000 or higher. In this case, Rajesh Kumar and Neha Sharma meet the criteria. Such filtered outputs support use cases like generating salary bands, identifying top earners, or calculating payroll taxes.
Use Case:
In an Indian enterprise HRMS system, a React.js frontend may allow users to filter employees by salary. A backend service, built in Python, Java, or C#, can run this query against a SQL database hosted in a Docker container. When orchestrated via Kubernetes, this ensures scalable delivery of high-earning employee data to payroll or analytics modules.
15. Write a query to fetch the year using a date.
How to Answer:
Explain that extracting the year component from a date is essential for generating time-based summaries, especially in financial, audit, and reporting modules common in Indian business systems. SQL’s YEAR() function enables precise temporal filtering for backend logic and frontend display.
Sample Answer:
To retrieve the current year from the database server’s system date, use the YEAR() function with GETDATE(). This is often used in dynamic reporting, filtering datasets, and generating year-wise statistics across domains such as finance and operations.
Example:
SELECT YEAR(GETDATE()) AS "Year";
Output:
Year
-----
2025
Output Explanation:
This query returns the current year (2025 in this case) from the system’s date and time. Such values are often used to build filters in reporting dashboards or determine financial year periods dynamically.
Use Case:
In full-stack applications, a Python or C# backend can use this query to fetch the current year and filter records accordingly (e.g., WHERE YEAR(InvoiceDate) = 2025). React.js frontends can then present annual reports, trends, or audits. When deployed inside Docker containers on Kubernetes, scalability and performance in data-intensive environments are ensured.
16. Create an SQL query to fetch EmpPostion and the total salary paid for each employee position.
How to Answer:
Explain that aggregating salary data by role is essential for budgeting, compensation benchmarking, and department-level financial analysis in Indian business systems. SQL’s SUM() function paired with GROUP BY allows efficient aggregation for both backend analytics and frontend visualization.
Sample Answer:
To calculate total salary per employee role, use the SUM() function along with GROUP BY EmpPosition. This group records by position and computes the cumulative salary for each role, enabling deeper financial insights and visual summaries.
Example:
SELECT EmpPosition, SUM(Salary) AS TotalSalary
FROM EmployeePosition
GROUP BY EmpPosition;
Output:
EmpPosition | TotalSalary
--------------------------
Manager | 250000
Developer | 340000
HR | 150000
Output Explanation:
The query aggregates salary values for each unique employee position. This helps finance teams evaluate salary distribution across roles, supporting data-driven decisions on workforce planning.
Use Case:
In a containerized backend built with Python or Java, this query can be run to serve summarized salary data via REST or GraphQL APIs. React.js frontends can then use this aggregated data to render salary distribution charts. With Kubernetes managing the Dockerized infrastructure, the architecture ensures scalability and responsiveness even during high data loads or reporting cycles.
Struggling to break into Data Science or level up your AI career? Join upGrads Professional Certificate Program in Data Science and AI with real-world projects and triple certification to gain the skills top employers demand, faster.
17. Write a query to find duplicate records from a table.
How to Answer:
Start by explaining that detecting duplicates is crucial in enterprise systems for maintaining clean, reliable datasets. Using SQL’s GROUP BY with HAVING COUNT(*) > 1 allows backend systems to identify data anomalies proactively, which is critical in large Indian organizations managing HR, payroll, or compliance data.
Sample Answer:
To detect duplicate employee records, group by fields that should uniquely identify an employee—such as EmpID, EmpFname, and Department. The HAVING clause filters these groups to show only those with more than one occurrence, indicating duplication.
Example:
SELECT EmpID, EmpFname, Department, COUNT(*) AS RecordCount
FROM EmployeeInfo
GROUP BY EmpID, EmpFname, Department
HAVING COUNT(*) > 1;
Output:
EmpID | EmpFname | Department | RecordCount
-------------------------------------------
108 | Amit | Sales | 2
112 | Priya | HR | 3
Output Explanation:
The result reveals duplicate entries for employees like Amit and Priya. Such redundancy can cause inconsistencies in reporting, payroll, and access control. Identifying and removing duplicates is key to ensuring accuracy.
Use Case:
This query can be used for automated validation routines before populating dashboards in a microservices-based architecture where Python or C# services fetch HR data. Clean data ensures that React.js or Next.js interfaces display accurate, trustworthy information, boosting end-user confidence. When deployed in Docker containers orchestrated by Kubernetes, this validation step can be integrated into continuous data quality pipelines.
18. Create a query to fetch the third-highest salary from the EmpPosition table.
How to Answer:
Start by explaining that retrieving the nth highest value is essential for rank-based compensation analysis. In Indian enterprises, this helps HR teams analyze salary bands or benchmark pay grades. SQL nested queries make this possible even in systems that don’t support advanced ranking functions. It’s a valuable pattern in backend services that power frontend dashboards.
Sample Answer:
To fetch the third-highest salary, we use nested TOP clauses with ORDER BY. The inner query retrieves the top three salaries, and the outer query selects the lowest among them—effectively the third-highest overall.
Example (SQL Server):
SELECT TOP 1 Salary
FROM (
SELECT TOP 3 Salary
FROM EmployeePosition
ORDER BY Salary DESC
) AS Temp
ORDER BY Salary ASC;
Output:
CopyEdit
Salary
-------
95000
Output Explanation:
The query returns ₹95,000, the third-highest salary in the EmployeePosition table. This approach is helpful when performing compensation analysis or setting salary thresholds in policy-driven organizations.
Use Case:
This logic is used in backend C# or Python services for salary analytics tools. When integrated into Dockerized microservices and orchestrated by Kubernetes, it enables responsive and scalable data access. React.js dashboards can use the result to display leaderboards, top-earner badges, or percentile bands, adding rich interactivity to enterprise HR software.
19. Write an SQL query to find even and odd records in the EmployeeInfo table.
How to Answer:
Begin by explaining that row classification using modulo operations is commonly used in frontend applications, especially for alternating row colors, enhancing data readability. SQL’s ROW_NUMBER() function, when combined with MOD(), enables even/odd row identification. This is useful in React.js tables styled with Bootstrap or Material UI and powered by efficient backend APIs.
Sample Answer:
To retrieve employee records in even-numbered row positions, we generate row numbers using the ROW_NUMBER() function, then apply the MOD() function to filter only even rows (MOD(rowno, 2) = 0).
Example:
SELECT EmpID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY EmpID) AS rowno, EmpID
FROM EmployeeInfo
) AS T
WHERE MOD(rowno, 2) = 0;
Output:
EmpID
-----
102
104
106
Output Explanation:
This query selects only those employee records that fall on even-numbered positions in the result set (e.g., 2nd, 4th, 6th). This supports backend logic used to differentiate row formatting for visual consistency in frontend tables.
Use Case:
You can implement this in backend Python or Java microservices to segment data before sending it to a React.js or Next.js UI. The frontend can then apply alternating styles (e.g., striped rows) for better readability. When deployed in Docker containers orchestrated by Kubernetes, this method ensures a fast and scalable user experience in data-intensive enterprise dashboards.
20. Create a query to fetch the list of employees of the same department.
How to Answer:
Explain that self-joins are used to relate rows within the same table. They are valuable in organizational contexts, such as listing employees who work in the same department. This technique supports features like team visualizations in React.js frontends, powered by backend APIs written in Python, Java, or C#.
Sample Answer:
To retrieve employees who belong to the same department but exclude themselves from the match, we use a self-join on the EmployeeInfo table with a condition that matches departments and filters out identical employee IDs.
Example:
SELECT DISTINCT E.EmpID, E.EmpFname, E.Department
FROM EmployeeInfo E
JOIN EmployeeInfo E1 ON E.Department = E1.Department AND E.EmpID != E1.EmpID;
Output:
EmpID | EmpFname | Department
-----------------------------
105 | Sunita | Marketing
109 | Rakesh | Marketing
111 | Anil | Finance
115 | Kavita | Finance
Output Explanation:
The query returns employees who share the same department with at least one other person, excluding self-pairs. This allows for clean team views, ensuring employees aren't redundantly paired with themselves.
Use Case:
Use this query in Python or C# backend services to dynamically generate department-based team lists. These lists can then be rendered in React.js or Next.js organizational dashboards. When deployed via Docker containers and orchestrated with Kubernetes, this pattern scales well for large enterprises with distributed teams.
Let’s explore how to effectively explain SQL Query Interview Questions & Answers in your upcoming interview.
