Frequently asked SQL queries in interview..

Join us on Facebook 

SQL Query to find second highest salary of Employee
SELECT MAX(Salary) from Employee WHERE Salary NOT IN (select MAX(Salary) from Employee );

OR

SELECT max(salary) FROM Employee WHERE salary < (SELECT max(salary) FROM Employee);

Query to find Max Salary from each department.
SELECT DeptID, MAX(Salary) FROM Employee GROUP BY DeptID.

SQL Query to find Max Salary from each department along with department name
SELECT DeptName, MAX(Salary) FROM Employee e RIGHT JOIN Department d ON e.DeptId = d.DeptID GROUP BY DeptName;

How do you find all employees which are also manager
SELECT e.name, m.name FROM Employee e, Employee m WHERE e.mgr_id = m.emp_id;

SQL query to show Employee (names) who have a bigger salary than their manager
SELECT e.emp_name FROM Employee e JOIN Employee m ON e.mngr_id = m.emp_id WHERE e.salary > m.salary;

SQL query to find Employees who have the biggest salary in their Department
SELECT a.emp_name, a.dept_id FROM Employee a JOIN
(SELECT a.dept_id, MAX(salary) as max_salary FROM Employee a JOIN Department b ON a.dept_id = b.dept_id GROUP BY a.dept_id) b
ON a.salary = b.max_salary AND a.dept_id = b.dept_id;

SQL query to list Departments that have less than 3 people in it
SELECT dept_id, COUNT(emp_name) as 'Number of Employee' FROM Employee GROUP BY dept_id HAVING COUNT(emp_name) < 3;

SQL query to show all Departments along with the number of people there
SELECT b.dept_name, COUNT(a.dept_id) as 'Number of Employee' FROM Employee a FULL OUTER JOIN Department b ON a.dept_id=b.dept_id GROUP BY b.dept_name;

SQL query to show all Employees that don't have a manager in the same department
SELECT a.emp_name FROM Employee a JOIN Employee b ON a.mngr_id = b.emp_id WHERE a.dept_id != b.dept_id;

SQL query to list all Departments along with the total salary there
SELECT b.dept_name, SUM(a.salary) as 'Total Salary' FROM Employee a FULL OUTER JOIN Department b ON a.dept_id = b.dept_id GROUP BY b.dept_name;

How to select first 5 records from a table
In Oracle,
SELECT * FROM EMPLOYEE  WHERE ROWNUM <= 5;

In SQL Server,
SELECT TOP 5 * FROM EMPLOYEE;

Also read : Inner and Outer Joins!..

SQL Query to display current date.
SQL has built in function called GetDate() which returns current timestamp.
SELECT GetDate();

SQL Query to check whether date passed to Query is date of given format or not.
SQL has IsDate() function which is used to check passed value is date or not of specified format ,it returns 1(true) or 0(false) accordingly.
SELECT  ISDATE('1/08/13') AS "MM/DD/YY";

SQL Query to find  year from date.
SELECT YEAR(GETDATE()) as 'Year';

SQL Query to print the name of distinct employee whose DOB is between 01/01/1960 to 31/12/1975.
SELECT DISTINCT EmpName FROM Employees WHERE DOB  BETWEEN ‘01/01/1960’ AND ‘31/12/1975’;

SQL Query find number of employees according to gender whose DOB is between 01/01/1960 to 31/12/1975.
SELECT COUNT(*), sex from Employees  WHERE  DOB BETWEEN ‘01/01/1960 ' AND ‘31/12/1975’  GROUP BY sex;

SQL Query to find employee whose Salary is equal or greater than 10000.
SELECT EmpName FROM  Employees WHERE  Salary>=10000;

SQL Query to find name of employee whose name Start with ‘H’
SELECT * FROM Employees WHERE EmpName like 'H%';

Also read : All about Normalization!

SQL Query to find name of employee whose name ends with ‘ani’
SELECT * FROM Employees WHERE EmpName like '%ani';

Find all Employee records containing the word "lal", regardless of whether it was stored as LAL, Lal, lAl or lal.
SELECT  * from Employees  WHERE  upper(EmpName) like upper('%lal%');

SQL query to find the duplicate rows.
SELECT * FROM EMPLOYEE e1 where rowid = (SELECT max(rowid) from EMPLOYEE e2 where e1.empno=e2.empno)

SQL query to delete the duplicate rows.
DELETE FROM EMPLOYEE e1 where rowid = (SELECT max(rowid) from EMPLOYEE e2 where e1.empno=e2.empno)

SQL query to find all the students whose marks are greater than average marks i.e. list of above average students.
SELECT StudentName, Marks FROM STUDENT_DETAILS HAVING Marks>AVG(Marks);

How to find duplicate records using GROUP BY in SQL Server?
It is possible to find duplicates using DISTINCT, ROW NUMBER as well as the GROUP BY approach.

Suppose we have a customer table, which contains duplicate customers.
CREATE TABLE CUSTOMER
(
FirstName VARCHAR(50),
LastName  VARCHAR(50),
MobileNo  VARCHAR(15)
);

To get the unique rows:
SELECT DISTINCT FirstName, LastName, MobileNo FROM CUSTOMER;

Finding duplicates using GROUP BY:
SELECT FirstName, LastName, MobileNo, COUNT(1) as custcount
FROM CUSTOMER GROUP BY FirstName, LastName, MobileNo;

In the above query, GROUP BY will show just one record for each combination of FirstName, LastName and MobileNo. Where the custcount shows how many times the row has been duplicated. custcount = 1 indicates that row appears only once.

Query to exclude rows that appear only once:
SELECT FirstName, LastName, MobileNo, COUNT(1) as custcount
FROM CUSTOMER GROUP BY FirstName, LastName, MobileNo
HAVING COUNT(1) > 1;

How to find nth highest salary in SQL?
Lets create a table,
Create table Employees
(
     Emp_ID int primary key identity,
     Emp_Name nvarchar(50),
     Emp_Gender nvarchar(50),
     Emp_Salary int
)

Insert into Employees values ('Ruben Onsu', 'Male', '1000000');
Insert into Employees values ('Julia Perez', 'Female', '1300000');
Insert into Employees values ('Andrew Simons', 'Male', '800000');
Insert into Employees values ('Martha Matthews', 'Female', '600000');
Insert into Employees values ('K Himaanshu Shuklaa', 'Male', '1550000');
Insert into Employees values ('Michael Jordan', 'Male', '1500000');
Insert into Employees values ('Siham Sisi', 'Male', '800000');

• Get the max salary:

select max(Emp_Salary) from Employees

• Get the second highest salary:

select max(Emp_Salary) from Employees
where Emp_Salary < (select max(Emp_Salary) from Employees)

• Get the third highest salary:

select top 1 Emp_Salary from
(
select top 3 Emp_Salary from  Employees
order by Emp_Salary desc
)
Result order by Emp_Salary

If you want the nth highest salary, simply replace 3 with 'n'.

• nth highest salary using Dense_rank sql server function:

WITH RESULT AS
(
select Emp_Salary, DENSE_RANK() over (order by Emp_Salary desc) as DR from  Employees
)
select Emp_Salary from RESULT
where RESULT.DR='4'

If you want the nth highest salary, simply replace 4 with 'n'.

-K Himaanshu Shuklaa..

Also Read:

• Difference between Stored Procedure and Function in Sql Server  
• Database Joins example!  
• Mysql Stored Procedures  
• All about Triggers in Sql Server & Oracle  
• Frequently asked SQL queries in interview..
• SQL Interview Questions And Answers 

Copyright © 2014 - ScrutinyByKHimaanshu