SQL interview questions and answers:

1.Display employees who joined in 2000 and doing job that has maximum salary more than 10000.

select first_name employees,to_char(hire_date,'yyyy') year, max_salary

from employees join jobs using(job_id)

where to_char(hire_date,'yyyy') = 2000

and

max_salary > 10000

2.Display departments where the name of the manager is MICHAEL.

select department_name,first_name || ' ' || last_name manager

from employees join departments using(department_id)

where last_name like '%Michael' or first_name like 'Michael%'

3.Display jobs where the minimum salary is less than salary of employee 105.

select job_id,min_salary,employee_id,salary

from employees join jobs using(job_id)

where employee_id = 105 and min_salary < salary

4.Display employees who have underscore in their email address

select first_name,email from employees

where email in '_';

-- no data found

5.Display employee name and manager name of the employee.

select e.first_name "employee name",m.first_name "manager name"

from employees e,employees m

where e.manager_id = m.employee_id

6.Display number of employees joined in each year into department 30.

select hire_date,department_id,count(department_id) "no of employees"

from employees

group by department_id,hire_date

having department_id =30

7.Display job id, department id and sum of salary by including all possible dimensions.

select job_id,department_id,sum(salary)

from employees
group by cube(department_id,job_id)

order by department_id,job_id

8.Display employee name and job title of jobs where salary of employee is between minimum and
maximum salary for job.

select first_name employee, job_title,salary,min_salary,max_salary

from employees join jobs using(job_id)

where salary between min_salary and max_salary;

9.Display how many employees have commission percentage and how many do not have.

10.Display first name, job title, department name of employees who joined on 28th Feb.

11.Display details of jobs where the minimum salary is greater than 10000.
12.Display the first name and join date of the employees who joined between 2002 and 2005.

13.Display first name and join date of the employees who is either IT Programmer or Sales Man.

14.Display employees who joined after 1st January 2008.

15.Display details of employee with ID 150 or 160.

16.Display first name, salary, commission pct, and hire date for employees with salary less than 10000.
17.Display job Title, the difference between minimum and maximum salaries for jobs with max salary in
the range 10000 to 20000.

18.Display first name, salary, and round the salary to thousands

 .

19.Display details of jobs in the descending order of the title

20.Display employees where the first name or last name starts with S.
21.Display employees who joined in the month of May.

22.Display details of the employees where commission percentage is null and salary in the range 5000 to
10000 and department is 30.
23Display first name and date of first salary of the employees.

24.Display first name and experience of the employees.

25.Display first name of employees who joined in 2001.

26.Display first name and last name after converting the first letter of each name to upper case and the
rest to lower case.

27.Display the first word in job title.

28.Display the length of first name for employees where last name contain character ‘b’ after 3rd
position.

select first_name,length(first_name) length,last_name,instr(last_name,'b',3) position

from employees

29.Display first name in upper case and email address in lower case for employees where the first name
and email address are same irrespective of the case.

30.Display employees who joined in the current year.

31.Display the number of days between system date and 1st January 2011.
32.Display how many employees joined in each month of the current year.

33.Display manager ID and number of employees managed by the manager.

34.Display employee ID and the date on which he ended his previous job.

35.Display number of employees joined after 15th of the month.

35.Display number of employees joined after 15th of the month.

--57 employees joined after the 15th of month.

select * from employees

where to_char(hire_date,'dd')>15;

36.Display the country ID and number of cities we have in the country.

select country_id,count(*) as cities

 from locations

group by country_id;

37.Display average salary of employees in each department who have commission percentage.

select department_id,round(avg(salary)) avg_salary

from employees

where commission_pct is not null

group by department_id;

38.Display job ID, number of employees, sum of salary, and difference between highest salary and
lowest salary of the employees of the job.

select

job_id,count(*) employees,sum(salary),max(salary) highest,min(salary) lowest,max(salary)-

min(salary) difference

from

employees

group by
 job_id;

39.Display job ID for jobs with average salary more than 10000.

select job_id, avg(salary)

from employees

group by job_id

having avg(salary) > 10000

40.Display years in which more than 10 employees joined.

select count(*) employees,to_char(hire_date,'yyyy') year

 from employees

group by to_char(hire_date,'yyyy') having count(*)>10;

41.Display departments in which more than five employees have commission percentage.

select department_id

from employees

where commission_pct is not null

group by department_id

having count(commission_pct) > 5

42.Display employee ID for employees who did more than one job in the past.

select employee_id

from job_history

group by employee_id

having count(employee_id) > 1

--select * from job_history

43.Display department ID, year, and Number of employees joined.

select department_id,to_char(hire_date,'yyyy') year,count(employee_id)

from employees

group by department_id,to_char(hire_date,'yyyy')

order by department_id

44.Display departments where any manager is managing more than 5 employees.

select distinct department_id

from employees

group by department_id,manager_id

having count(employee_id) > 5

45.Change salary of employee 115 to 8000 if the existing salary is less than 6000.

update employees_table

set salary = 8000

where employee_id = 115

and salary < 6000;

46.Change job ID of employee 110 to IT_PROG if the employee belongs to department 10 and the
existing job ID does not start with IT.

update employees_table

set job_id = 'it_prog'

where employee_id = 110

and
department_id = 10

and

job_id not like 'IT%'

select employee_id,job_id,department_id from employees_table where employee_id = 110

47.Display department name and number of employees in the department.

select department_name,count(employee_id) employees from

select d.department_name, e.employee_id

from departments d, employees e

where d.department_id = e.department_id

group by department_name;
48.Display job title, employee ID, number of days between ending date and starting date for all jobs in
department 30 from job history.

select j.job_title,jh.employee_id,-(start_date - end_date) days

from jobs j, job_history jh

where j.job_id = jh.job_id

and

jh.department_id = 30

(or)

select job_title,employee_id,-(start_date - end_date) days

from jobs natural join job_history

where department_id = 30

select * from jobs

select * from job_history

--no data found--

49.Display department name and manager first name.

select d.department_name,e.first_name manager_name

from employees e,departments d

where e.department_id = d.department_id

50.Display department name, manager name, and city.

select d.department_name,l.city,e.first_name manager_name

from departments d, locations l,employees e

where d.location_id = l.location_id

and e.department_id = d.department_id

select * from departments

select * from locations

51.Display job title, department name, employee last name, starting date for all jobs from 2000 to 2005.

select j.job_title,d.department_name,e.last_name employee,to_char(start_date,'yyyy')

from employees e,departments d,jobs j,job_history jh

where e.department_id = d.department_id

and

j.job_id = jh.job_id

and

e.job_id = j.job_id

and

to_char(start_date,'yyyy') between 2000 and 2005

--no data found

select * from employees

select * from departments

select * from jobs;

select * from job_history;

52.Display job title and average salary of employees

select job_title,avg(salary) average

from employees e,jobs j

where e.job_id = j.job_id

group by job_title

53.Display job title, employee name, and the difference between maximum salary for the job and salary
of the employee.

select job_title,first_name employee, max_salary-salary diff

from employees natural join jobs

54.Display last name, job title of employees who have commission percentage and belongs to
department 30.

select last_name,job_title

from employees natural join jobs

where commission_pct is not null

and

department_id = 30

--no data found

55.Display details of jobs that were done by any employee who is currently drawing more than 15000 of
salary.

select j.*,salary,first_name

from employees e,jobs j

where e.job_id = j.job_id

and

salary > 15000

56.Display department name, manager name, and salary of the manager for all managers whose
experience is more than 5 years.

select d.department_name, e.first_name manager, e.salary,round((sysdate - hire_date)/365) exp

from employees e,departments d

where e.department_id = d.department_id

and round((sysdate - hire_date)/365) > 5

57.Display employee name if the employee joined before his manager.

select e.first_name employee,e.hire_date employee_join,m.first_name manager,m.hire_date

manager_join

from employees e, employees m

where e.manager_id = m.employee_id

and e.hire_date < m.hire_date

58.Display employee name, job title for the jobs employee did in the past where the job was done less
than six months.

select e.first_name employee,j.job_title,round(months_between(end_date,start_date)/12) months

from employees e,jobs j,job_history jh

where e.job_id = j.job_id

and e.employee_id = jh.employee_id

and round(months_between(end_date,start_date)/12)<6

select round(months_between(sysdate,start_date)/12),sysdate,start_date from job_history

59.Display employee name and country in which he is working.

select e.first_name employee,l.city country

from employees e,departments d,locations l

where e.department_id = d.department_id

and

d.location_id = l.location_id

60.Display department name, average salary and number of employees with commission within the
department.

select d.department_name,round(avg(salary)),count(commission_pct) employees

from employees e, departments d

where e.department_id = d.department_id

group by department_name
61.Display the month in which more than 5 employees joined in any department located in Sydney.

select to_char(hire_date,'month') month

from employees e,departments d,locations l

where e.department_id = d.department_id

and

d.location_id = l.location_id

and l.city = 'Sydney'

group by to_char(hire_date,'month')

having count(*) > 5

or

SELECT TO_CHAR(HIRE_DATE,'MON-YY')

FROM EMPLOYEES JOIN DEPARTMENTS USING (DEPARTMENT_ID) JOIN LOCATIONS USING

(LOCATION_ID)

WHERE CITY = 'Sydney'

GROUP BY TO_CHAR(HIRE_DATE,'MON-YY')

HAVING COUNT(*) > 5

--no data found--

62.Display details of departments in which the maximum salary is more than 10000.

select department_name,max(salary)

from employees e,departments d

where e.department_id = d.department_id

group by department_name

having max(salary) > 10000

63.Display details of departments managed by ‘Smith’.

select d.department_name, concat(first_name,last_name) name

from employees e,departments d

where e.department_id = d.department_id

and concat(first_name,last_name) like '%Smith'

or concat(first_name,last_name) like 'Smith%'

64.Display jobs into which employees joined in the current year.

select j.*,first_name

from jobs j,employees e

where e.job_id = j.job_id

and to_char(hire_date,'yyyy') = to_char(sysdate,'yyyy');

--no data found

select e.* from employees e

where to_char(hire_date,'yyyy') = to_char(sysdate,'yyyy')

65.Display employees who did not do any job in the past.

select * from employees

where employee_id not in

select employee_id from job_history

66.Display job title and average salary for employees who did a job in the past.

select job_title,avg(salary),

from employees natural join jobs

where employee_id in

(select employee_id from job_history)

group by job_title
--67.Display country name, city, and number of departments where department has more than 5
employees. (wrong)

select c.country_name,l.city,d.department_id,count(employee_id)

from countries c,locations l,departments d,employees e

where c.country_id = l.country_id and l.location_id = d.location_id

and e.department_id = d.department_id

group by country_name,city,employee_id

having e.employee_id>5

select * from countries;

select * from locations;

select * from departments;

select * from jobs;

select * from employees;

68.Display details of manager who manages more than 5 employees.

select m.first_name manager,count(m.employee_id) "no of employees"

from employees e,employees m

where e.manager_id = m.employee_id

group by m.employee_id,m.first_name

having count(m.employee_id) > 5

69.Display employee name, job title, start date, and end date of past jobs of all employees with
commission percentage null.

select e.first_name employee,j.job_title,jh.start_date,jh.end_date

from employees e,jobs j,job_history jh

where e.job_id = j.job_id

and e.employee_id = jh.employee_id

and e.commission_pct is null

70.Display the departments into which no employee joined in last two years.

select department_name from departments

where department_id not in

select department_id from employees

where floor((sysdate - hire_date)/365) < 2

71.Display the details of departments in which the max salary is greater than 10000 for employees who
did a job in the past.

select department_id,employee_id,max(salary) from employees

where employee_id in

(select employee_id from job_history)

group by department_id,employee_id

having max(salary) > 10000

72.Display details of current job for employees who worked as IT Programmers in the past.

select job_id current_job,salary,employee_id from employees

where employee_id in

select employee_id from job_history

where job_id = 'IT_PROG'

73.Display the details of employees drawing the highest salary in the department.

select * from

select employee_id,department_id,salary, dense_rank() over(partition by department_id order by salary

desc) rank

from employees e

)
where rank = 1;

74.Display the city of employee whose employee ID is 105.

select e.employee_id,l.city

from employees e,departments d,locations l

where e.department_id = d.department_id

and

d.location_id = l.location_id

and

e.employee_id = 105
75.Display third highest salary of all employees

select * from

select e.*, dense_rank() over(order by salary desc) rank from employees e

where rank = 3;