select * from employee;
select deptname, empname, title, salary
from employee e, department d
where e.dno = d.deptno
order by deptname, salary desc;
select e.empname as manager, m.empname as employee
from employee e, employee m
where e.manager = m.empno;
select deptname as dept
from department;
select empname, deptname
from employee e, department d
where e.dno = d.deptno;
select empname, title
from employee
where title = (select title from employee where empname = ‘박영권’);
select empname
from employee
where dno in (select deptno from department where deptname = ‘영업’ or deptname = ‘개발’);
select e.empname
from employee e, department d
where e.dno = d.deptno and (deptname = ‘영업’ or deptname = ‘개발’);
select empname, title
from employee
where title = (select title from employee where empname = ‘박영권’) and empname <> ‘박영권’;
/*20번 및 50번 부서에서 근무하는 모든 사원들의 성과 부서 번호*/
select last_name, department_id
from employees
where department_id = 20 or department_id = 50
order by last_name;
/*20번 및 50번 부서에 근무하며 연봉이 5000 ~ 12000 성과 연봉*/
select last_name, salary
from employees
where (department_id = 20 or department_id = 50) and
(salary >= 5000 and salary <= 12000);
select last_name, salary
from employees
where department_id in (20,50) and salary >= 5000 and salary <= 12000;
/*매니저가 없는 사람들의 last name 및 job id*/
select last_name, job_id
from employees
where manager_id is null;
/*last_name에 네번째 글자가 a인 사람들의 last_name*/
select last_name
from employees
where last_name like ‘___a%’;
/*last_name 에 a 또는 e 글자가 있는 사람들의 last name*/
select last_name
from employees
where last_name like ‘%a%’ or last_name like ‘%e%’;
/*연봉이 2500 , 3500, 7500이 아니면서 직업이 sa_rep이나 st_clerk*/
select first_name, first_name, salary
from employees
where salary not in (2500, 3500, 7500) and job_id in (‘SA_REP’, ‘ST_CLERK’);
/*모든 사람들의 lat, name, 부서번호 및 부서 이름 조회*/
select e.last_name, e.department_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id;
/*부서번호 30의 모든 직업들을 중복없이 조회한다. 90 부서 또한 포함한다. */
select distinct e.job_id, j.job_title, e.department_id
from employees e, jobs j
where department_id in (30, 90) and e.job_id = j.job_id;
select distinct e.job_id, d.location_id
from employees e, departments d
where e.department_id in (30, 90) and e.department_id = d.department_id;
/*커미션을 버는 모든 사람들의 성, 부서명, 지역 id, 도시명*/
select e.last_name, d.department_name, l.location_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.commission_pct is not null;
/*자신의 매니저보다 먼저 고용된 사원들의 last name 및 고용일*/
select e.last_name, e.hire_date
from employees e
where e.hire_date < (select hire_date from employees where employee_id = e.manager_id);