쿼리연습

 21. 동일한 직업을 가진 사원들의 총 수를 조회한다.

 

 (결과 예제)
JOB_ID COUNT(*)
 ---------- ----------
 AC_ACCOUNT 1
 AC_MGR 1
 AD_ASST 1
 AD_PRES 1
 AD_VP 2
 FI_ACCOUNT 5
 FI_MGR 1
 ...

답:

SELECT JOB_ID,COUNT(EMPLOYEE_ID) FROM EMPLOYEES  GROUP BY JOB_ID ORDER BY JOB_ID;

 

* 참고 

GROUP BY절 

일반적으로 특정그룹별 데이터를 필요로 할 경우에 GROUP BY절을 그룹함수와 함께 이용한다.

GROUP BY 절 이용시, SELECT에 지정한 칼럼은 GROUP BY절에 모두 포함해야한다.

 

SELECT [DISTINCT] 컬럼명 [ALIAS명]

 FROM 테이블명

 [WHERE 조건식]

 GROUP BY 컬럼이나 표현식;

*그룹함수

->다중 행 함수의 일종으로 여러 행들의 그룹이 모여서 단 하나의 결과를 돌려주는 함수를 말한다.

->WHERE절에서는 GROUP BY를 위해 사용하는 그룹함수를 절대 사용할 수가 없다. (So, HAVING절 사용함) 

ⓐCOUNT(a) : a행(row)의 개수를 돌려준다.

ⓑMAX(a) : a행 (row)의 최대값을 돌려준다.

ⓒAVG(a) : a행 (row)의 평균값을 돌려준다.

HAVING절

 SELECT [DISTINCT] 컬럼명 [ALIAS명]

 FROM 테이블명

 [WHERE 조건식]

 GROUP BY 컬럼이나 표현식

 HAVING 그룹조건식;

-WHERE절 : SELECT ~ FROM절에서 발췌된 데이터에 대한 제한조건을 부여하여 필요한 데이터만을 조회할 때 사용하는 조건절

-HAVING절 : 그룹함수를 사용해 GROUP BY절을 사용할 때 그룹들에 대한 제한 조건이 필요하여 사용하는, 그룹에 대한 조건절

=> 즉, SELECT의 조건은 WHERE절, GROUP BY절의 조건은 HAVING절이다.

 

-HAVING절이 있을 떄의 수행되는 순서

①WHERE : 발췌 대상 데이터가 아닌 것은 제거

②GROUP BY : 행들을 그룹화하고,

③GROUP FUNCTION : 그룹함수를 적용한다.

④HAVING : 마지막으로 그룹함수값의 조건에 맞는 것만 발췌하여 출력.

ORDER BY절

SELECT [DISTINCT] 컬럼명 [ALIAS명]

 FROM 테이블명

 [WHERE 조건식]

 [GROUP BY 컬럼이나 표현식

 HAVING 그룹조건식]

 ORDER BY 칼럼이나 표현식[ASC 또는 DESC];

-데이터를 보여줄 땐 정렬을 해야 보는 사람이 편하게 볼 수 있으며, 결과에 대한 분석을 빨리 할 수 있다.

-이를 가능케 하는 것이 ORDERY BY절로, 조회된 결과의 데이터들을 정렬하여 보기 좋게 만들어준다.

-기본적으로 ORDER BY는 오름차순 정렬이 되어 ASC 생략이 가능하다.

-또한 모든 절을 다 이용한다면, 작성 순서는 다음과 같다.

  WHERE절->GROUP BY절->ORDER BY절

 22. 매니저로 근무하는 사원들의 총 수를 조회한다.

 

 (결과 예제)
Manager #
 ----------
 18

 

답:

SELECT DISTINCT COUNT(EMPLOYEE_ID) FROM EMPLOYEES  WHERE EMPLOYEE_ID IN (SELECT DISTINCT MANAGER_ID FROM EMPLOYEES WHERE MANAGER_ID IS NOT NULL) ;

 

23. 사내의 최대 연봉 및 최소 연봉의 차이를 조회한다.

 

 (결과 예제)
DIFF
 ----------
 21900

 

답 :

SELECT MAX(SALARY)- MIN(SALARY) DIFF FROM EMPLOYEES; 

 

 24. 매니저의 사번 및 그 매니저 밑 사원들 중 최소 연봉을 받는 사원의 연봉을 조회한다.
- 매니저가 없는 사람들은 제외한다.
- 최소 연봉이 6000 미만인 경우는 제외한다.
- 연봉 기준 역순으로 조회한다.

 

 (결과 예제)
MANAGER_ID Salary
 ---------- ----------
 102 9000
 205 8300
 146 7000
 145 7000
 108 6900
 147 6200
 149 6200
 148 6100
 148 6100

답 :

SELECT MANAGER_ID ,MIN(SALARY) FROM EMPLOYEES WHERE MANAGER_ID IS NOT NULL GROUP BY MANAGER_ID HAVING MIN(SALARY) >= 6000 ORDER BY MIN(SALARY) DESC;

 

 25. 부서 명, 위치 ID, 각 부서 별 사원 총 수, 각 부서 별 평균 연봉을 조회한다.
- 평균 연봉은 소수점 2 자리까지만 표현한다.

 

 (결과 예제)

 

Name Location People # Salary
 ------------------------------ ---------- ---------- ----------
 Administration 1700 1 4400
 Marketing 1800 2 9500
 Sales 2500 34 8955.88
 Purchasing 1700 6 4150
 Finance 1700 6 8600
 IT 1400 5 5760
 Executive 1700 3 19333.33
 Shipping 1500 45 3475.56
 Accounting 1700 2 10150
 Human Resources 2400 1 6500
 Public Relations 2700 1 10000

답:
SELECT DEPARTMENT_NAME,LOCATION_ID,COUNT(EMPLOYEE_ID),ROUND(AVG(SALARY), 2) AVG_SALARY FROM EMPLOYEES A,DEPARTMENTS B 
WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID GROUP BY DEPARTMENT_NAME,LOCATION_ID ORDER BY LOCATION_ID;