Sql Queries asked in job interviews

This article demonstrates some commonly asked SQL queries in a .NET/SQL job interview.
Let’s create Employee table and insert data as shown below 
to perform basic sql query operation.
EmpID INT Identity,
Empname Varchar(100),
EmpSal Decimal (10,2),
DEPARTMENT varchar(100),
JOINING_DATE varchar(100)

INSERT INTO #Employee VALUES ('Krishna',50000,'System Administrator','10/01/2011');
INSERT INTO #Employee VALUES ('John',30000,'Network Engineer','23/08/2011');
INSERT INTO #Employee VALUES ('Vicky',22000,'Help Desk','23/01/2012');
INSERT INTO #Employee VALUES ('Mayur',35000,'System Administrator','05/04/2012');
INSERT INTO #Employee VALUES ('Nitin',25000,'Help Desk','20/05/2011');
INSERT INTO #Employee VALUES ('Sagar',25000,'Network Engineer','25/01/2011');

One of Basic query
 (1) How can we find the highest salary in a table?
Answer:The aggregate function SQL MAX() is used to find the maximum 
value or highest value of a certain column or expression. This function is useful to 
determine the largest of all selected values of a column.
SELECT MAX(Empsal) as salary FROM #Employee

(2) SQL Query to find second highest salary of Employee.
Answer: There are many ways to find second highest salary of Employee in SQL,
 you can either use SQL Join or Sub query to solve this problem. Here is SQL query using 
Sub query.
SELECT MAX(Empsal) as Salary FROM #Employee
where EmpSal not in(SELECT MAX(Empsal) FROM #Employee)

(3) Query to find Max Salary from each department.
Answer:  For this use Group By clause is used for grouping the records of the database 
table(s).This clause creates a single row for each group and this process is called aggregation.
To use group by clause we have to use at least one aggregate function in Select statement. 
We can use group by clause without where clause.
SELECT DEPARTMENT, MAX(empsal) as salary FROM #Employee GROUP BY 

(4) Write an SQL Query find number of employees whose DOJ is 
between 01/01/2011 to 31/12/2012.
Answer: Use BETWEEN operator selects values within a range. 
The values can be numbers, text, or dates.
SELECT COUNT(*) from #Employee WHERE JOINING_DATE BETWEEN '01/01/2011' 
AND '31/12/2012'

(5) Write an SQL Query find number of employees according to 
Department whose DOJ is between 01/01/2011 to 31/12/2012.
SELECT COUNT(*) as total,DEPARTMENT from #Employee 
WHERE JOINING_DATE BETWEEN'01/01/2011' AND '31/12/2012' 

(6) Write an SQL Query to find employee whose Salary is equal or 
greater than 30000
SELECT EmpName FROM #Employee WHERE EmpSal >=30000

(7) SQL Query to find name of employee whose name Start with ‘K’
Answer: The LIKE operator is used to search for a specified pattern in a column.
SELECT * FROM #Employee WHERE EmpName like 'k%'




Post a Comment