1. Write a query to find the name (first_name, last_name) and the salary of the employees who have a higher salary than the employee whose last_name='Bull'.
- select first_name, last_name, salary
from employees
where salary > (select salary from employees where last_name = 'Bull');
쿼리를 두 파트로 나누어 생각하면 쉽다.
Inner Query (select salary from employees where last_name = 'Bull');와
Outer Query select first_name, last_name, salary from employees where salary > 의 두 파트.
Inner 쿼리가 4100의 값을 리턴 했다면 결과적으로
select first_name, last_name, salary from employees where salary >(4100); 의 쿼리가 된다.
2. Write a query to find the name (first_name, last_name) of all employees who works in the IT department.
- select first_name, last_name
from employees
where department_id in(select department_id from departments where department_name='IT');
3. Write a query to find the name (first_name, last_name) of the employees who have a manager whom worked in a USA based department.
- SELECT first_name, last_name
FROM employees
WHERE manager_id in (
select employee_id
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location_id IN (
select location_id
from locations
where country_id='US')));
4. Write a query to find the name (first_name, last_name) of the employees who are managers.
- SELECT first_name, last_name FROM employees WHERE (employee_id IN (SELECT manager_id FROM employees));
5. Write a query to find the name (first_name, last_name), and salary of the employees whose salary is greater than the average salary.
6. Write a query to find the name (first_name, last_name), and salary of the employees whose salary is equal to the minimum salary for their job grade.
7. Write a query to find the name (first_name, last_name), and salary of the employees who earns more than the average salary and works in any of the IT departments.
8. Write a query to find the name (first_name, last_name), and salary of the employees who earns more than the earning of Mr. Bell.
9. Write a query to find the name (first_name, last_name), and salary of the employees who earn the same salary as the minimum salary for all departments.
10. Write a query to find the name (first_name, last_name), and salary of the employees whose salary is greater than the average salary of all departments.
11. Write a query to find the name (first_name, last_name) and salary of the employees who earn a salary that is higher than the salary of all the Shipping Clerk (JOB_ID = 'SH_CLERK'). Sort the results of the salary of the lowest to highest.
12. Write a query to find the name (first_name, last_name) of the employees who are not supervisors.
13. Write a query to display the employee ID, first name, last name, and department names of all employees.
14. Write a query to display the employee ID, first name, last name, salary of all employees whose salary is above average for their departments.
15. Write a query to fetch even numbered records from employees table.
16. Write a query to find the 5th maximum salary in the employees table.
17. Write a query to find the 4th minimum salary in the employees table.
18. Write a query to select last 10 records from a table.
19. Write a query to list the department ID and name of all the departments where no employee is working.
20. Write a query to get 3 maximum salaries.
21. Write a query to get 3 minimum salaries.
22. Write a query to get nth max salaries of employees.
'Database' 카테고리의 다른 글
| HR Data Base MySQL Aggregate Functions and Group by exercises (0) | 2019.09.10 |
|---|---|
| HR Data Base MySQL Restricting and Sorting Data statement exercises. (0) | 2019.09.09 |
| HR Data Base MySQL basic SELECT statement exercises. (0) | 2019.09.09 |
| hr 데이터 베이스 실습문제 풀이 - 12번까지 완료 (0) | 2019.09.09 |