Nov 21

Frequently asked SQL Queries in Interviews

Today we discuss about the most frequently asked SQL queries in the interviews.

In my previous article we discussed we created two tables i.e. Employee and Department with dummy data. I use same table and data from there except department.

CREATE TABLE [dbo].[Employee](
	[EmployeeId] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeName] [varchar](50) NOT NULL,
        [Salary] [int] NOT NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
	[EmployeeId] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO

-- Insert employee data
INSERT INTO [Employee] ([EmployeeName], [Salary]) VALUES ('Rohit', 200000)
GO
INSERT INTO [Employee] ([EmployeeName], [Salary]) VALUES ('Pranav', 100000)
GO
INSERT INTO [Employee] ([EmployeeName], [Salary]) VALUES ('Akshay', 125000)
GO
INSERT INTO [Employee] ([EmployeeName], [Salary]) VALUES ('Prakash', 50000)
GO
INSERT INTO [Employee] ([EmployeeName], [Salary]) VALUES ('Anjali', 50000)
GO
INSERT INTO [Employee] ([EmployeeName], [Salary]) VALUES ('Sudha', 25000)
GO
INSERT INTO [Employee] ([EmployeeName], [Salary]) VALUES ('Dinesh', 20000)
GO
INSERT INTO [Employee] ([EmployeeName], [Salary]) VALUES ('Meetanshu', 22000)
GO
INSERT INTO [Employee] ([EmployeeName], [Salary]) VALUES ('Robert', 150000)
GO

Now, execute SELECT query on newly created table and sort data by descending order it will show you  following result

Employee Id Employee Name Salary
1 Rohit 200000
9 Robert 150000
3 Akshay 125000
2 Pranav 100000
4 Prakash 50000
5 Anjali 50000
6 Sudha 25000
8 Meetanshu 22000
7 Dinesh 20000

Employee and Salary based Questions

Top 3 Employees with maximum salary

SELECT TOP 3 EmployeeId, EmployeeName, Salary 
FROM Employee 
ORDER BY Salary DESC

Top 3 Employees with minimum salary 

SELECT TOP 3 EmployeeId, EmployeeName, Salary 
FROM Employee 
ORDER BY Salary ASC

Third highest salary of employee

SELECT MIN(Salary)
FROM(
		SELECT DISTINCT TOP 3 Salary
		FROM Employee
		ORDER BY Salary DESC
) AS Result

Third lowest salary of employee (to make you confuse)

SELECT MAX(Salary)
FROM(
		SELECT DISTINCT TOP 3 Salary
		FROM Employee
		ORDER BY Salary ASC
) AS Result

Fifth highest salary of employee

SELECT MIN(Salary)
FROM(
		SELECT DISTINCT TOP 5 Salary
		FROM Employee
		ORDER BY Salary DESC
) AS Result

Fifth lowest salary of employee

SELECT MAX(Salary)
FROM(
		SELECT DISTINCT TOP 5 Salary
		FROM Employee
		ORDER BY Salary ASC
) AS Result

Nth highest salary of employee (Suppose 7th)

SELECT TOP 1 Salary
FROM (
      SELECT DISTINCT TOP 7 Salary
      FROM Employee
      ORDER BY Salary DESC
      ) AS Emp
ORDER BY Salary ASC

Nth lowest salary of employee (Suppose 7th)

SELECT TOP 1 Salary
FROM (
      SELECT DISTINCT TOP 7 Salary
      FROM Employee
      ORDER BY Salary ASC
      ) AS Emp
ORDER BY Salary DESC

Customer and Account based Questions

We already discussed these questions on my previous post hence posting only queries.

Customer having maximum number of accounts

SELECT c.CustomerName, COUNT(ca.AccountId) AS AccountCount
FROM @Customer c
INNER JOIN @CustomerAccount ca ON c.CustomerId = ca.CustomerId
INNER JOIN @Account a ON a.AccountId = ca.AccountId
GROUP BY ca.CustomerId, c.CustomerName
ORDER BY AccountCount DESC

Accounts having maximum number of customer

SELECT a.AccountName, COUNT(ca.CustomerId) AS CustomerCount
FROM @Customer c
INNER JOIN @CustomerAccount ca ON c.CustomerId = ca.CustomerId
INNER JOIN @Account a ON a.AccountId = ca.AccountId
GROUP BY ca.AccountId, a.AccountName
ORDER BY CustomerCount DESC

Leave a Reply