Sample Database Queries

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’;