[SQL] 공부 Review

▶▶▶ 자신이 속한 부서의 평균연봉보다 많은 연봉을 받는 사원의 아이디, 부서아이디, 연봉, 부서의 평균연봉 조회 컬럼은 ALIAS를 지정하여 출력

 

SELECT

e.employee_id 사번, e.department_id 부서번호, e.salary 연봉, vt.AVG 평균연봉

FROM employees e, ( SELECT department_id, ROUND(AVG(salary),0) AVG

                                FROM employees

                                GROUP BY department_id) vt

WHERE e.department_id=vt.department_id

           AND e.salary>vt.AVG

ORDER BY vt.AVG DESC;

 

 

▶▶▶ employees 테이블에서 급여를 많이 받는 수서대로 조회했을때 6~10번째의 5명 사람들의

정보 출력

 

SELECT vt.RANKING, e.last_name LAST_NAME, e.first_name FIRST_NAME, e.salary SALARY

FROM employees e, (SELECT

                               RANK() OVER(ORDER BY SALARY DESC) RANKING, SALARY

                               FROM employees) vt

WHERE vt.RANKING>=6 AND vt.RANKING<=10 

           AND e.salary=vt.salary

GROUP BY e.last_name, e.first_name,e.salary,vt.RANKING

ORDER BY vt.RANKING;

 

▶▶▶ 직책이 sales manager인 사원들의 입사년도와 입사년도별 평균급여 출력

 

SELECT to_char(e.hire_date,'yyyy') 년도, to_char(AVG(e.salary),'99.999') 평균급여

FROM employees e, jobs j

WHERE e.job_id=j.job_id

           AND j.job_title='Sales manager'

GROUP BY to_char(e.hire_date,'yyyy')

ORDER BY 년도 ASC;

 

*ASC 와 DESC 차이

DESC 큰->낮은 순으로 출력

ASC 낮은->큰 순으로 출력

 

▶▶▶ 사원의 부서가 속한 도시가 Seattle이고 사원의 이름, 해당사원의 매니저이름,

사원의 부서이름을 출력

이떄 사원의 매니저가 없는 경우는 '<없음>'이라고 출력해라.

사원의 이름은 오름차순 정렬

 

SELECT e1.last_name 사원이름, NVL(e2.last_name,'<없음>') 매니저이름,d.department_name 부서이름

FROM employees e1, employees e2, departments d, locations l

WHERE e1.manager_id=e2.employee_id(+)

   AND e1.department_id=d.department_id

   AND d.location_id=l.location_id

   AND l.city='Seattle'

ORDER BY e1.last_name;

 

쿼리 정렬 default는 오름차순 정렬.

▶▶▶  부서별로 가장 적은 급여를 받고 있는 직원의 이름, 부서이름, 급여 출력

부서가 같은 경우 이름을 기준으로 오름차순 정렬

SELECT e.last_name 사원이름, d.department_name 부서이름, e.salary 급여

FROM employees e,departments d

WHERE e.department_id=d.department_id

   AND (e.salary,d.department_name) IN (SELECT                

           MIN(e1.SALARY),d1.department_name

           FROM employees e1,departments d1

           WHERE e1.department_id=d1.department_id

           GROUP BY d1.department_name)

ORDER BY d.department_name ASC, e.last_name ASC;

 

 

▶▶▶  각 도시에 있는 모든 부서 직원들의 평균급여를 조회하고자 한다.

평균급여가 가장 낮은 도시부터 도시명과 평균연봉 해당 도시의 직원수를 출력하시오.

단 도시에 근무하는 직원이 10명 이상인 곳은 제외

 

SELECT l.city 도시이름, AVG(e.salary) 평균급여, Count(*) 직원수

FROM employees e,departments d,locations l

WHERE e.department_id=d.department_id

   AND d.location_id=l.location_id

GROUP BY l.city

HAVING Count(*)<10

ORDER BY AVG(e.salary) ASC;

 

▶▶▶ 30번 부서에 근무하는 사원들의 직책별 평균급여가 2000이상인 직책과 그 직책의 평균급여 출력

 

SELECT

j.job_title 직책,

to_char(AVG(e.salary),'99,999') 평균급여

FROM employees e,jobs j

WHERE e.job_id=j.job_id

AND e.department_id=30

GROUP BY j.job_title;

▶▶▶ 1999년에 입사한 직원들의 사번,이름,성,부서명을 조회

부서에 배치되지 않은 직원일 경우 NOT ASSIGNED 출력

SELECT

employee_id 사번,

first_name 이름,

last_name 성,

NVL(department_name,'<NOT ASSIGNED>') 부서명

FROM employees e,departments d

WHERE e.department_id=d.department_id(+)

          AND hire_date BETWEEN '1999-01-01' AND '1999-12-31';

▶▶▶ 마케팅부서 직원 레코드 출력

SELECT

employee_id 사번,

first_name 이름,

last_name 성,

email 이메일,

phone_number 전화번호,

to_char(hire_date,'DD/MM/YYYY HH24:MI:SS')고용날짜,

job_id 직업사번,

salary 월급,

manager_id 매니저아이디,

department_id 부서사번

FROM employees

WHERE department_id=20;

▶▶▶ 자신의 매니저보다 연봉을 많이 받는 직원들의 성 연봉 출력

SELECT

e1.last_name 성,

e1.salary 연봉

FROM employees e1,employees e2

WHERE e1.manager_id=e2.employee_id

AND e1.salary>e2.salary;

// 같은테이블안에선 아무거나 조인 가능

▶▶▶ 업무명이 sales representative인 직원중ㅇ서 연봉이 9000이상 1000이하인 직원들의 이름 성 연봉 출력

SELECT

e.first_name 성,

e.last_name 이름,

e.salary 연봉

FROM employees e,jobs j

WHERE e.job_id=j.job_id

     AND j.job_title='Sales Representative'

     AND e.salary Between 9000 AND 10000;

▶▶▶jdbc 문제

==============jdbc==================

SELECT

'['||j.job_title||']',

AVG(e.salary)

FROM employees e,jobs j

WHERE e.job_id=j.job_id

AND e.salary>=10000

group by j.job_title

ORDER BY AVG(e.salary) DESC;

============jdbc1===================

SELECT

employee_id,

first_name,

salary

FROM employees

WHERE UPPER(first_name) LIKE('%AL%')

              AND salary>=4000;