[Data Base] 2019.06.25. 기록. 문제풀이

1

select last_name  , salary*12
from employees
where last_name in (
select last_name
from employees
where salary*12 > 12000
)
/
__________________107 rows selected.

2

select last_name, department_id
from employees
where employee_id = 176 
/



___________________1 rows selected

3
select last_name , salary*12
from employees
where salary between 5000 and 12000 
/

_________________ 50 rows selected.

4

select last_name, employee_id, hire_date
from employees
where hire_date between '05/02/20' and '07/05/01'
order by hire_date asc
/

_________________ 57 rows selected.


5
select last_name, department_id
from employees
where department_id =20
or department_id =50
/

_________________ 47 rows selected.

6

select last_name, department_id
from employees
where (department_id =20
or department_id =50)
and salary between 5000 and 12000
/


_________________ 6 rows selected.

7
----7-1 > 08일에 고용된 사람 조회
select last_name, hire_date
from employees
where hire_date like '%08'
/

_________________ 1 rows selected.

----7-2 > 08년에 고용된 사람 조회
  1  select last_name, hire_date
  2  from employees
  3* where hire_date like '08%'
SQL> /
_________________ 11 rows selected.


8
select last_name, job_id
from employees
where manager_id is null
/

_________________ 1 rows selected.

9

select last_name, job_id
from employees
where manager_id is not null
/


_________________ 106 rows selected.

10

select last_name , salary*12 , commission_pct
from employees
where commission_pct is not null
and commission_pct >0
order by commission_pct asc
order by salary*12 desc
/

????????????????????


11
select last_name
from employees
where last_name like '___a%'
/
_________________ 10 rows selected.

12
select last_name
from employees
where last_name like '%a%'
or last_name like '%e%'
/

_________________ 87 rows selected.



13

select last_name
from employees
where salary <>2500
and salary <>3500
and salary <> 7000
and job_id <> 'SA_REP'
and job_id <> 'ST_CLERK'
/


_________________ 57 rows selected.



14

select last_name, department_id, employee_id
from employees
/

_________________ 107 rows selected.



15
select job_id
from employees
where department_id <30
or department_id =90
/

_________________ 6 rows selected.


16

select E.last_name, D.department_name, L.city
from employees E , locations L , departments D
where commission_pct is not null
group by L.location_id, D.location_id
group by D.department_name, E.department_name
/

ORA-01787: only one clause allowed per query block
??????????????????????




17


----17-2

select E.last_name, D.department_name, L.country_id, L.city
from employees E , locations L , departments D
where L.city like 'seattle'
and E.commission_pct is not null
/


_________________ no rows selected.

18

select last_name , hire_date
from employees
where hire_date > (
select hire_date
from employees
where last_name = 'Davies'
)
/

_________________ 81 rows selected.






19
------19-1

select last_name, hire_date
from employees
where hire_date < (
select hire_date
from employees
where manager_id is null
)

/
______________________ 10 rows selected.

-----19-2
select last_name, hire_date
from employees
where manager_id in (
select manager_id
freom employees
where hire_date 
/
+

select distinct manager_id, department_id
from departments
where manager_id is null
/



20

select max(salary*12), min(salary*12) , sum(salary*12), avg(salary*12)
from employees
group by salary*12
/

_________________ 58 rows selected.


21

select job_id, max(salary*12), min(salary*12) , sum(salary*12), avg(salary*12)
from employees
group by salary*12, job_id
/

_________________ 82 rows selected.


22 

select job_id, count(employee_id)
from employees
group by job_id
/

_________________ 19 rows selected.

> group by 이론 복습

23


25

select manager_id, min(salary*12)
from employees
where manager_id is not null
group by salary*12 , manager_id
having salary*12 >6000
order by salary*12 desc
/

26



27

select department_id, employee_id, hire_date
from employees
where department_id in (
select department_id
from employees
where last_name = 'Zlotkey'
)
/



_________________ 34 rows selected.



28

select last_name, employee_id
from employees
where salary*12 > (
select avg(salary*12)
from employees
)
/


_________________ 51 rows selected.



29