SQL Queries that very frequently ask by Interviewer

1) Find the Employee who get the second highest Salary.

Answer:  SELECT emp_name from emp WHERE emp_salary =(SELECT max(emp_salary) FROM emp WHERE emp_salary<(SELECT max(emp_salary) FROM emp);

OR

SELECT MAX(emp_name) FROM  emp WHERE  emp_name <(SELECT MAX(emp_name) FROM emp);

2) How Many employees are getting more salary than “Meera”

Answer: SELECT emp_name from emp WHERE emp_salary>(SELECT emp_salary FROM emp WHERE emp_name like ‘Meera’);

3) List maximum salaries department wise in descending order of salaries

Answer: SELECT emp_dept, max(emp_salary) FROM emp GROUP BY emp_dept ORDER BY max(emp_salary) DESC;

4) List out the sum of salaries of employee department wise.

Answer: SELECT emp_dept, sum(salary) FROM emp;

5) List out the sum of salaries of employee from HR department

Answer: SELECT emp_dept, sum(salary) FROM emp WHERE emp_dept = ‘HR';

6) List out the Employee names and Salary whose Salary is more than 25000

Answer: SELECT emp_name, emp_salary FROM emp WHERE emp_salary > 25000

7) List the employee name and salary of all employee whose salary is equal to the average salary of the all employees.

Answer: SELECT emp_name,emp_salary FROM emp WHERE emp_salary =(SELECT avg(emp_salary) FROM emp);

8)  List of the unique employee names

Answer: SELECT DISTINCT (emp_name) FROM emp;

9) How i can find the maximum salary and employee name from the table

Answer: SELECT emp_name, emp_salary FROM emp WHERE emp_salary=(SELECT max(emp_salary) FROM emp);

10) how i can concatenate the name and salary of the employees?

Answer: SELECT emp_name || ‘ ‘ || emp_salary “employee name with salary” FROM emp;

11) List out the employee names and emp_id in descending order of emp_id

Answer: SELECT emp_id, emp_name FROM emp ORDER BY emp_id DESC;

12) list out the employee names from DEV and QA department

Answer: SELECT emp_name, emp_dept FROM emp WHERE emp_dept IN (DEV, QA);

13) list out the employee names not from DEV and QA department

Answer: SELECT emp_name, emp_dept FROM emp WHERE emp_dept NOT IN (DEV, QA);

14) List out the name and Salary of the employee between 25000 to 35000

Answer: SELECT emp_name, emp_salary FROM emp WHERE emp_salary BETWEEN 25000 AND 35000;

15) List out the name and salary of employee from IT department whose salary is more than 25000

Answer: SELECT emp_name, emp_salary FROM emp WHERE emp_salary >25000 and emp_dept =’IT';AA