Which of the following queries will return the first name of the employee who earns the highest salary?

Didn't get the answer.
Contact people of Talent-Mysql directly by clicking here

Queries based on the above tables:

Simple Queries:

 SELECT * FROM EMPLOYEE

2)List all the department details

        Select * from department      
3)List all job details

     Select * from job; 

4)List all the locations

    Select * from loc; 

5)List out first name,last name,salary, commission for all employees

      Select first_name, last_name, salary, commission from employee; 

6)List out employee_id,last name,department id for all employees and rename employee id as “ID of the employee”, last name as “Name of the employee”, department id as “department ID”

  Select employee_id “id of the employee”, last_name “name", department id as “department id” from employee; 

7)List out the employees anuual salary with their names only.

 Select last_name, salary*12 “annual salary” from employee

Where Conditions:

    select * from employee where last_name='smith';

9) List out the employees who are working in department 20

        select * from employee where dept=20;
10) List out the employees who are earning salary between 3000 and 4500

         select * from employee where salary  between 3000 and 4500;

11) List out the employees who are working in department 10 or 20

          select * from employee where dept_id in (10,20);

12) Find out the employees who are not working in department 10 or 30

           select * from employee where dept_id  not in (10,30) ;

13) List out the employees whose name starts with “S”

      select * from employee where last_nameis like 's%';

14) List out the employees whose name start with “S” and end with “H”

          select * from employee where last_name like 'S%H';

 
15) List out the employees whose name length is 4 and start with “S”

          select * from employee where last_name like 's____';
16) List out the employees who are working in department 10 and draw the salaries more than 3500

          select * from employee where dept_id=10 and salary >3500;

17)   list out the employees who are not receiving commission.

          select * from employee where comm is null;

Order By Clause:

           select emp_id,last_name from employee order  by emp_id;

19) List out the employee id, name in descending order based on salary column

        select emp_id,Last_name,salary from employee order by salary desc;

20)  List out the employee details according to their last_name in ascending order and salaries in 

                  descending  order

          Select * from employee  order by  last_name,salary desc;

21)  List out the employee details according to their last_name in ascending order and then on department_id in descending order.

    select * from employee order by last_name,dept_id desc;

Group By & Having Clause:

        select dept_id,count(*) from employee group by dept_id.

23) List out the department wise maximum salary, minimum salary, average salary of the employees

         select dept_id,count(*),max(salary),min(salary),avg(salary) from employee group by dept_id

24) List out the job wise maximum salary, minimum salary, average salaries of the employees.

       select job ,salary from employee count(*),max(salary),min(salary),avg(salary);

25) List out the no.of employees joined in every month in ascending order.

           Select to_char(hire_date,’month’)month, count(*) from employee group by    

                to_char(hire_date,’month’) order by month

Convert a numeric or date expression to a character String.

The format may be either a DATE format (YYYY=year, MM=month, DD=Day, HH=Hour, Mi=Minute )
or a NUMBER format (0999=include leading zero).

If no format is specified Oracle will use the default date format.

TO_CHAR will convert NCHAR, NVARCHAR2, CLOB, or NCLOB data to the database character set.

Examples

26) List out the no.of employees for each month and year, in the ascending order based on the year, month.

        select to_char(hire_date,'month')month,to_char(hire_date,'year')year,count(*) "no. of employees" from employee group by to_char(hire_date,'year')year,to_char(hire_date,'month')month orer by year,month;

27) List out the department id having atleast four employees.

     select dept_id,count(*) from employee group by dept_id  having count(*)>=4;

28) How many employees in January month.

    select to_char(hire_date,'month')month,count(*) from employee group by   to_char(hire_date,'month') having  to_char(hire_date,'month') ='jan';

29) How many employees who are joined in January or September month.

   select   to_char(hire_date,'month')month,count(*) from employee group by  to_char(hire_date,'month') having                    to_char(hire_date,'month') in ('jan,'sep'); 

30) How many employees who are joined in 1985.

      select   to_char(hire_date,'yyyy')year,count(*) from employee group by   to_char(hire_date,'yyyy') having

               to_char(hire_date,'yyyy')= 1985;

31) How many employees joined each month in 1985.

       select   to_char(hire_date,'yyyy')year,to_char(hire_date,'month')month ,count(*) from employee group by   to_char(hire_date,'yyyy') ,to_char(hire_date,'month')month  having  to_char(hire_date,'yyyy')= 1985;

  Select to_char(hire_date,’yyyy’)Year, to_char(hire_date,’mon’) Month, count(*) “No. of employees” from employee where to_char(hire_date,’yyyy’)=1985 group by to_char(hire_date,’yyyy’),to_char(hire_date,’mon’)

   The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. 

32) How many employees who are joined in March 1985.

       select   to_char(hire_date,'yyyy')year,to_char(hire_date,'month')month ,count(*) from employee group by   to_char(hire_date,'yyyy') ,to_char(hire_date,'month')month  having  to_char(hire_date,'yyyy')= 1985 ,to_char(hire_date,'moth')='mar';

  Select to_char(hire_date,’yyyy’)Year, to_char(hire_date,’mon’) Month, count(*) “No. of 

employees” from employee where to_char(hire_date,’yyyy’)=1985 and to_char(hire_date,’mon’)=’mar’ group by to_char(hire_date,’yyyy’),to_char(hire_date,’mon’)

    select dept_id ,count(*) from employee group by dept_id,to_char(hire_date,'yyyy'),to_char(hire_date,'month') having count(*)>.=3,to_char(hire_date,'yyyy')=1985,to_char(hire_date,'month')='april';

  Select department_id, count(*) “No. of employees” from employee where to_char(hire_date,’yyyy’)=1985 and to_char(hire_date,’mon’)=’apr’ group by to_char(hire_date,’yyyy’), to_char(hire_date,’mon’), department_id having count(*)>=3

Sub-Queries

       select * from employee where salary=(selct max(salary) from employee;

35) Display the employees who are working in Sales department

      select* from employee where dept_id in(select dept_id from department where department='sales');

          
36) Display the employees who are working as “Clerk”.

      select * from employee where job_id in (selct job_id from job where function='clerk');

37) Display the employees who are working in “New York”

      select * from employee where dept_id=(select dept_id from depatment where location id= (select location_id from location where regional_group='newyork');

38) Find out no.of employees working in “Sales” department.

     select count(*) from employee where dept_id in(select dept_id from department where name ='sales);

 
39) Update the employees salaries, who are working as Clerk on the basis of 10%.

     upadate  employee set  salary=salary*10/100 where job_id=(selct job_id from job where function='clerk'); 

    
40) Delete the employees who are working in accounting department.

           delete from  employee where dept_id=(select dept_id from department where name='accounting');

41) Display the second highest salary drawing employee details.

     select * from employee where salary=(selct max(salary) from employee where salary<(select max(salary) from employee));

42) Display the Nth highest salary drawing employee details

Select distinct e.salary from employee where & no-1=(select count(distinct salary) from employee where sal>e.salary)

Sub-Query operators: (ALL,ANY,SOME,EXISTS)

         select * from employee where salary>all(select salary from employee where dept_id=30);

44) List out the employees who earn more than the lowest salary in department 30.

            select * from employee where salray>any(select salry from employee where dept_id=30);   

    
45) Find out whose department has not employees.

          select * from employee e where not exists(selct dept_id from department d where d.dept_id=e.dept_id);

46) Find out which department does not have any employees.

  select dept_id from employee where dept_id=!(select  dept_id from department);

   Select name from department d where not exists (select last_name from employee e where d.department_id=e.department_id)

Co-Related Sub Queries:

47) .Find out the employees who earn greater than the average salary for their department.

   select * from employee e where salary>(select avg(salary) from employee where dept_id=e.dept_id);

Simple join

48.List our employees with their department names

          select last_name,dept_id,name, salary  from employee e,dept d where e.dept_id=d.dept_id;

     Select employee_id, last_name, name from employee e, department d where e.department_id=d.department_id 

49).Display employees with their designations (jobs)

 select last_name,job_id,function from employee e ,job j where j.job_id=e.job_id;

50).Display the employees with their department name and regional groups.

       select last_name, dept_id,location _id,regional _group from department d,employee e ,location  l where e.dept_id=d.dept_id and d.location_id=l.location_id;

51). How many employees who are working in different departments and display with department name.

    select count (*) from employee e depatment d where e.dept_id=e.dept_id group by name

52.How many employees who are working in sales department.

    select count(*) from employee e,department d where e.dept_id=d.dept_id group by name

having  name='sales';

53.Which is the department having greater than or equal to 5 employees and display the department names in ascending order.

    select count(*) dept_id,name from employee e,department d where e.dept_id=d.dept_id  group by name having count(*)>=5 order by name

54.How many jobs in the organization with designations.

    select count(*)  from employee e,job j e.job_id=j.job_id group by function;

55.How many employees working in “New York”.

   select count(*) ,location_id,dept_id from employee  e,location l,department d where e.dept_id=d.dept_id and dept_id=(select dept_id from depatment where location_id=(select loaction_id from location where regional_group='new york')))

   select count(*) from employee e,department d,location l where e.dept_id=d.dept_id ,d.location_id=l.location_id where regional_group='newyork'group by regional group;

Non – Equi Join:

56.Display employee details with salary grades.

   Select employee_id, last_name, grade_id from employee e, salary_grade s where salary between lower_bound and upper_bound order by last_name

57.List out the no. of employees on grade wise.

 select count(*) ,grade_id from employee salary_grade s where salary between lower_bound and upper_bound group by grade_id order by grade_id desc

58.Display the employ salary grades and no. of employees between 2000 to 5000 range of salary.

  select count(*), grade_id  from employee e ,salary_grade s where salary between lower_bound and upper_bound and lower_bound>= 2000 and upper_bound<5000 group by grade 

Self Join:

59.Display the employee details with their manager names.

  select last_name job_id ,function from employee e ,job j where e.job_id=j.job_id and job_id=(select job_id where function='manager')

  if we consider there is another table manager

 select last_name ,manager_id,name from employee e ,manager m where e.manager_id=m.manager_id group by name

60.Display the employee details who earn more than their managers salaries.

61.Show the no. of employees working under every manager.

Outer Join:

62.Display employee details with all departments.

   select * from employee e , department d  where e.dept_id=d.dept_id group by dept_id

63.Display all employees in sales or operation departments.

  select * from employee e depatment d where e.dept_id=d.dept_id and dept_id=(select dept_id where name in('sales' ,'operations');

Set Operators:

64.List out the distinct jobs in Sales and Accounting Departments.

     select function from job where job_id in(select job_id from employee where dept_id=(select dept_id where name='sales'))union select function from job where job_id in(select job_id from employee where dept_id=(select dept_id where name='accounting'))

65.List out the ALL jobs in Sales and Accounting Departments.

    select function from job where job_id in(select job_id from employee where dept_id=(select dept_id where name='sales'))union all select function from job where job_id in(select job_id from employee where dept_id=(select dept_id where name='accounting'))

66.List out the common jobs in Research and Accounting Departments in ascending order.

                 select function from job where job_id in(select job_id from employee where dept_id=(select dept_id where name='reasearch'))intersect select function from job where job_id in(select job_id from employee where dept_id=(select dept_id where name='accounting'))

Answers

1. SQL > Select * from employee;
2. SQL > Select * from department;
3. SQL > Select * from job;
4. SQL > Select * from loc;
5. SQL > Select first_name, last_name, salary, commission from employee;
6. SQL > Select employee_id “id of the employee”, last_name “name", department id as “department id” from employee;
7. SQL > Select last_name, salary*12 “annual salary” from employee
8. SQL > Select * from employee where last_name=’SMITH’;
9. SQL > Select * from employee where department_id=20
10. SQL > Select * from employee where salary between 3000 and 4500
11. SQL > Select * from employee where department_id in (20,30)
12. SQL > Select last_name, salary, commission, department_id from employee where department_id not in (10,30)
13. SQL > Select * from employee where last_name like ‘S%’
14. SQL > Select * from employee where last_name like ‘S%H’
15. SQL > Select * from employee where last_name like ‘S___’
16. SQL > Select * from employee where department_id=10 and salary>3500
17. SQL > Select * from employee where commission is Null
18. SQL > Select employee_id, last_name from employee order by employee_id
19. SQL > Select employee_id, last_name, salary from employee order by salary desc
20. SQL > Select employee_id, last_name, salary from employee order by last_name, salary desc
21. SQL > Select employee_id, last_name, salary from employee order by last_name, department_id desc
22. SQL > Select department_id, count(*), from employee group by department_id
23. SQL > Select department_id, count(*), max(salary), min(salary), avg(salary) from employee group by department_id
24. SQL > Select job_id, count(*), max(salary), min(salary), avg(salary) from employee group by job_id
25. SQL > Select to_char(hire_date,’month’)month, count(*) from employee group by to_char(hire_date,’month’) order by month
26. SQL > Select to_char(hire_date,’yyyy’) Year, to_char(hire_date,’mon’) Month, count(*) “No. of employees” from employee group by to_char(hire_date,’yyyy’), to_char(hire_date,’mon’)
27. SQL > Select department_id, count(*) from employee group by department_id having count(*)>=4
28. SQL > Select to_char(hire_date,’mon’) month, count(*) from employee group by to_char(hire_date,’mon’) having to_char(hire_date,’mon’)=’jan’
29. SQL > Select to_char(hire_date,’mon’) month, count(*) from employee group by to_char(hire_date,’mon’) having to_char(hire_date,’mon’) in (‘jan’,’sep’)
30. SQL > Select to_char(hire_date,’yyyy’) Year, count(*) from employee group by to_char(hire_date,’yyyy’) having to_char(hire_date,’yyyy’)=1985
31. SQL > Select to_char(hire_date,’yyyy’)Year, to_char(hire_date,’mon’) Month, count(*) “No. of employees” from employee where to_char(hire_date,’yyyy’)=1985 group by to_char(hire_date,’yyyy’),to_char(hire_date,’mon’)
32. SQL > Select to_char(hire_date,’yyyy’)Year, to_char(hire_date,’mon’) Month, count(*) “No. of employees” from employee where to_char(hire_date,’yyyy’)=1985 and to_char(hire_date,’mon’)=’mar’ group by to_char(hire_date,’yyyy’),to_char(hire_date,’mon’)
33. SQL > Select department_id, count(*) “No. of employees” from employee where to_char(hire_date,’yyyy’)=1985 and to_char(hire_date,’mon’)=’apr’ group by to_char(hire_date,’yyyy’), to_char(hire_date,’mon’), department_id having count(*)>=3
34. SQL > Select * from employee where salary=(select max(salary) from employee)
35. SQL > Select * from employee where department_id IN (select department_id from department where name=’SALES’)
36. SQL > Select * from employee where job_id in (select job_id from job where function=’CLERK’
37. SQL > Select * from employee where department_id=(select department_id from department where location_id=(select location_id from location where regional_group=’New York’))
38. SQL > Select * from employee where department_id=(select department_id from department where name=’SALES’ group by department_id)
39. SQL > Update employee set salary=salary*10/100 wehre job_id=(select job_id from job where function=’CLERK’)
40. SQL > delete from employee where department_id=(select department_id from department where name=’ACCOUNTING’)
41. SQL > Select * from employee where salary=(select max(salary) from employee where salary <(select max(salary) from employee))
42. SQL > Select distinct e.salary from employee where & no-1=(select count(distinct salary) from employee where sal>e.salary)
43. SQL > Select * from employee where salary > all (Select salary from employee where department_id=30)
44. SQL > Select * from employee where salary > any (Select salary from employee where department_id=30)
45. SQL > Select employee_id, last_name, department_id from employee e where not exists (select department_id from department d where d.department_id=e.department_id)
46. SQL > Select name from department d where not exists (select last_name from employee e where d.department_id=e.department_id)
47. SQL > Select employee_id, last_name, salary, department_id from employee e where salary > (select avg(salary) from employee where department_id=e.department_id)
48. SQL > Select employee_id, last_name, name from employee e, department d where e.department_id=d.department_id
49. SQL > Select employee_id, last_name, function from employee e, job j where e.job_id=j.job_id
50. SQL > Select employee_id, last_name, name, regional_group from employee e, department d, location l where e.department_id=d.department_id and d.location_id=l.location_id
51. SQL > Select name, count(*) from employee e, department d where d.department_id=e.department_id group by name
52. SQL > Select name, count(*) from employee e, department d where d.department_id=e.department_id group by name having name=’SALES’
53. SQL > Select name, count(*) from employee e, department d where d.department_id=e.department_id group by name having count (*)>=5 order by name
54. SQL > Select function, count(*) from employee e, job j where j.job_id=e.job_id group by function
55. SQL > Select regional_group, count(*) from employee e, department d, location l where e.department_id=d.department_id and d.location_id=l.location_id and regional_group=’NEW YORK’ group by regional_group
56. SQL > Select employee_id, last_name, grade_id from employee e, salary_grade s where salary between lower_bound and upper_bound order by last_name
57. SQL > Select grade_id, count(*) from employee e, salary_grade s where salary between lower_bound and upper_bound group by grade_id order by grade_id desc
58. SQL > Select grade_id, count(*) from employee e, salary_grade s where salary between lower_bound and upper_bound and lower_bound>=2000 and lower_bound<=5000 group by grade_id order by grade_id desc
59. SQL > Select e.last_name emp_name, m.last_name, mgr_name from employee e, employee m where e.manager_id=m.employee_id
60. SQL > Select e.last_name emp_name, e.salary emp_salary, m.last_name, mgr_name, m.salary mgr_salary from employee e, employee m where e.manager_id=m.employee_id and m.salary
61. SQL > Select m.manager_id, count(*) from employee e, employee m where e.employee_id=m.manager_id group by m.manager_id
62. SQL > Select last_name, d.department_id, d.name from employee e, department d where e.department_id(+)=d.department_id
63. SQL > Select last_name, d.department_id, d.name from employee e, department d where e.department_id(+)=d.department_id and d.department_idin (select department_id from department where name IN (‘SALES’,’OPERATIONS’))
64. SQL > Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name=’SALES’)) union Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name=’ACCOUNTING’))
65. SQL > Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name=’SALES’)) union all Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name=’ACCOUNTING’))
66. SQL > Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name=’RESEARCH’)) intersect Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name=’ACCOUNTING’)) order by function