Practice SQL Questions 31-51HR Schema with Solutions
31. From the following tables, write a SQL query to find those employees whose salaries exceed 50% of their department's total salary bill. Return first name, last name.
with calc as (select department_id, sum(salary)/2 as half_total_salary
from employees
group by department_id)
select e.first_name,e.last_name
from employees e
join calc c on e.department_id=c.department_id and e.salary>c.half_total_salary;
32. From the following tables, write a SQL query to find those employees who are managers. Return all the fields of employees table.
select * from employees where employee_id in (
select distinct manager_id from employees where manager_id is not null);
33. From the following table, write a SQL query to find those employees who manage a department. Return all the fields of employees table.
select * from employees
where employee_id in (select distinct manager_id from employees);
34. From the following table, write a SQL query to search for employees who receive such a salary, which is the maximum salary for salaried employees, hired between january 1 1994 and December 31st, 1994. Return employee ID, first name, last name, salary, department name and city.
select e.employee_id,e.first_name,e.last_name,e.salary,d.department_name,l.city
from employees e
inner join departments d on e.department_id=d.department_id
inner join locations l on d.location_id=l.location_id
where salary >
(SELECT max(salary)FROM employees WHERE hire_date BETWEEN TO_DATE('01/01/1994', 'DD/MM/YYYY') AND TO_DATE('31/12/1994', 'DD/MM/YYYY'));
35. From the following tables, write a SQL query to find those departments that are located in the city of London. Return department ID, department name.
SELECT d.department_id, d.department_name
FROM departments d
JOIN locations l ON d.location_id = l.location_id
WHERE l.city = 'London';
36. From the following table, write a SQL query to find those employeeswho earn more than the average salary. Sort the result-set in descending order by salary. Return first name, last name, salary, and department ID.
with avg_salary as (
select avg(salary) as average_salary from employees)
select first_name,last_name,salary,department_id
from employees,avg_salary
where salary>average_salary
order by salary desc;
37. From the following table, write a SQL query to find those employees who earn more than the maximum salary for a department of ID 40. Return first name, last name and department ID.
select first_name,last_name,department_id
from employees
where salary >
(select max(salary) from employees where department_id=40);
38. From the following table, write a SQL query to find departments for a particular location. The location matches the location of the department of ID 30. Return department name and department ID.
select d.department_name,d.department_id
from departments d
join locations l on d.location_id=l.location_id
where l.location_id =
(select location_id from departments where department_id=30);
39. From the following table, write a SQL query to find employees who work for the department in which employee ID 201 is employed. Return first name, last name, salary, and department ID.
select first_name,last_name,salary,department_id
from employees
where department_id =
(select department_id from employees where employee_id=201);
40. From the following table, write a SQL query to find those employees whose salary matches that of the employee who works in department ID 40. Return first name, last name, salary, and department ID.
select first_name,last_name,salary,department_id
from employees
where salary =
(select salary from employees where department_id=40);
41. From the following table, write a SQL query to find those employees who work in the department 'Marketing'. Return first name, last name and department ID.
select e.first_name,e.last_name,e.department_id
from employees e
join departments d
on e.department_id=d.department_id and department_name='Marketing';
42. From the following table, write a SQL query to find those employees who earn more than the minimum salary of a department of ID 40. Return first name, last name, salary, and department ID.;
select first_name,last_name,salary,department_id
from employees
where salary >
(select min(salary) from employees where department_id=40);
43. From the following table, write a SQL query to find those employees who joined after the employee whose ID is 165. Return first name, last name and hire date.
select first_name,last_name,hire_date
from employees
where hire_date >
(select hire_date from employees where employee_id=165);
44. From the following table, write a SQL query to find those employees who earn less than the minimum salary of a department of ID 70. Return first name, last name, salary, and department ID.
select first_name,last_name,salary,department_id
from employees
where salary <
(select min(salary) from employees where department_id=70);
45. From the following table, write a SQL query to find those employees who earn less than the average salary and work at the department where Laura (first name) is employed. Return first name, last name, salary, and department ID.
with avg_salary as (
select avg(salary) as average_salary from employees)
select first_name,last_name,salary,department_id
from employees,avg_salary
where salary<average_salary and department_id = (select department_id from employees where first_name='Laura');
46. From the following table, write a SQL query to find those employees who earn the second-lowest salary of all the employees. Return all the fields of employees.
select * from employees
where salary =
(select distinct salary from
(select salary,dense_rank()over(order by salary asc) as rank from employees)
where rank=2);
47.From the following tables, write a SQL query to find those departments where the starting salary is at least 8000. Return all the fields of departments.
with min_salary as(select department_id,min(salary) as minimum_salary
from employees
group by department_id
having min(salary)>=8000)
select d.* from departments d,min_salary m
where d.department_id=m.department_id;
48. From the following table, write a SQL query to find those managers who supervise four or more employees. Return manager name, department ID.
with managers as (select manager_id,count(*)
from employees
group by manager_id
having count(*)>=4)
select e.first_name||' '||e.last_name as manager_name,e.department_id
from employees e,managers m
where e.employee_id=m.manager_id;
49. From the following tables, write a SQL query to find those departments where maximum salary is 7000 and above. The employees worked in those departments have already completed one or more jobs. Return all the fields of the departments.
with max_salary as(select department_id,max(salary) as maximum_salary
from employees
group by department_id
having max(salary)>=7000),
departments_jh as(select e.department_id
from employees e
inner join job_history jh
on e.employee_id=jh.employee_id
inner join max_salary m
on e.department_id=m.department_id
group by e.department_id
having count(*)>=1)
select d.* from departments_jh j
join departments d
on d.department_id=j.department_id;
50.From the following tables, write a SQL query to find the city of the employee of ID 134. Return city.
select l.city
from employees e
inner join departments d
on e.department_id=d.department_id
inner join locations l
on d.location_id=l.location_id
where employee_id = 134;
51. From the following table, write a SQL query to find those employees who earn the highest salary in a department. Return department ID, employee name, and salary.
with max_salary_dept as (
select employee_id,dense_rank()over(partition by department_id order by salary desc) as rnk from employees
),
rank_1 as (
select employee_id from max_salary_dept where rnk=1)
select e.employee_id,nvl(e.department_id,0)as department_id,(e.first_name||' '||e.last_name)as name,e.salary
from employees e,rank_1 r
where e.employee_id=r.employee_id;
Note : Please Report Any Errors If Found.
Comments
Post a Comment