할수있다!긍정왕

데이터 삽입 시, PRIMERY KEY나 UNIQUE KEY가 중복되었을 경우 지정한 데이터만 UPDATE하는 명령어를 의미

CREATE TABLE 새로만들테이블명 AS

SELECT * FROM 복사할테이블명 [WHERE 절]

UNION ALL은 행의 합집합

OUTER JON은 열의 합집합

HR 계정을 테이블 컬럼 Comment보는 방법 

SELECT * FROM ALL_COL_COMMENTS WHERE TABLE_NAME = 'EMPLOYEES';

 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;

문제

1. 연봉이 12000 이상되는 직원들의 LAST_NAME 및 연봉을 조회한다.

 (결과 예제)
LAST_NAME SALARY
 ------------------------- ----------
 Hartstein 13000
 King 24000
 Kochhar 17000
 De Haan 17000
 Russell 14000
 Partners 13500

//답 : 여기서 ASC를 더 해줬습니다.

SELECT LAST_NAME,SALARY FROM EMPLOYEES WHERE SALARY>12000 ORDER BY SALARY ASC;  

 2. 사원번호가 176 인 사람의 LAST_NAME 과 부서 번호를 조회한다.

 (결과 예제)
LAST_NAME DEPARTMENT_ID
 ------------------------- -------------
 Taylor 80

//답

SELECT LAST_NAME,DEPARTMENT_ID FROM EMPLOYEES WHERE EMPLOYEE_ID=176;

 3. 연봉이 5000 에서 12000의 범위 이외인 사람들의 
    LAST_NAME 및 연봉을 조회힌다.

 (결과 예제)

LAST_NAME SALARY
 ------------------------- ----------
 OConnell 2600
 Grant 2600
 Whalen 4400
 Hartstein 13000
 King 24000
 ...

//답 :
SELECT LAST_NAME,SALARY FROM EMPLOYEES WHERE NOT SALARY BETWEEN 5000 AND 12000;

 4. 1998/02/20 일부터 1998/05/01 사이에 고용된 사원들의 LAST_NAME 사번, 고용일자를 조회한다.
- 고용일자 순으로 정렬한다.

 (결과 예제)

LAST_NAME JOB_ID HIRE_DATE
 ------------------------- ---------- ----------
 Fleaur SH_CLERK 1998/02/23
 Urman FI_ACCOUNT 1998/03/07
 Matos ST_CLERK 1998/03/15
 Bloom SA_REP 1998/03/23
 Taylor SA_REP 1998/03/24
 Olsen SA_REP 1998/03/30
 Patel ST_CLERK 1998/04/06
 Livingston SA_REP 1998/04/23
 Walsh SH_CLERK 1998/04/24

//답

SELECT LAST_NAME,EMPLOYEE_ID,HIRE_DATE  FROM EMPLOYEES WHERE HIRE_DATE BETWEEN '1998-02-20' AND '1998-05-01'; 

 5. 20 번 및 50 번 부서에서 근무하는 모든 사원들의 
    LAST_NAME 및 부서 번호를 알파벳순으로 조회한다.

 (결과 예제)

LAST_NAME DEPARTMENT_ID
 ------------------------- -------------
 Atkinson 50
 Bell 50
 Bissot 50
 Bull 50
 Cabrio 50
 Chung 50
 ...

//답

SELECT LAST_NAME,DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID=20 OR DEPARTMENT_ID=50 

ORDER BY LAST_NAME ASC;

 6. 20 번 및 50 번 부서에 근무하며, 연봉이 5000 ~ 12,000 사이인 사원들의 LAST_NAME 및 연봉을 조회한다.

 (결과 예제)

EMPLOYEES SALARY
 ------------------------- ----------
 Fay 6000
 Weiss 8000
 Fripp 8200
 Kaufling 7900
 Vollman 6500
 Mourgos 5800

//답 : 저는 찾기 쉽게 이름을 알파벳순서대로 해보았습니다.

SELECT LAST_NAME,SALARY FROM EMPLOYEES WHERE SALARY 

BETWEEN 5000 AND 12000 AND DEPARTMENT_ID=20 OR DEPARTMENT_ID=50 ORDER BY LAST_NAME ASC;

 7. 1994년도에 고용된 모든 사람들의 LAST_NAME 및 고용일을 조회한다.

 (결과 예제)
LAST_NAME HIRE_DATE
 ------------------------- ----------
 Mavris 1994/06/07
 Baer 1994/06/07
 Higgins 1994/06/07
 Gietz 1994/06/07
 Greenberg 1994/08/17
 Faviet 1994/08/16
 Raphaely 1994/12/07

답 :

SELECT LAST_NAME,HIRE_DATE FROM EMPLOYEES WHERE HIRE_DATE BETWEEN '1994-01-01' AND '1994-12-31';

 8. 매니저가 없는 사람들의 LAST_NAME 및 JOB_ID 를 조회한다.
 (결과 예제)

LAST_NAME JOB_ID
 ------------------------- ----------
 King AD_PRES

답 :

SELECT LAST_NAME,JOB_ID FROM EMPLOYEES WHERE MANAGER_ID IS NULL;

 9. 커미션을 버는 모든 사원들의 LAST_ANME, 연봉 및 커미션을 조회한다.
- 연봉 역순, 커미션 역순차로 정렬한다.

 (결과 예제)
LAST_NAME SALARY COMMISSION_PCT
 ------------------------- ---------- --------------
 Russell 14000 .4
 Partners 13500 .3
 Errazuriz 12000 .3
 Ozer 11500 .25
 Cambrault 11000 .3
 ...

답 :

SELECT LAST_NAME,SALARY,COMMISSION_PCT FROM EMPLOYEES WHERE COMMISSION_PCT IS NOT NULL 

ORDER BY SALARY DESC;

 10. LAST_NAME 의 네번째 글자가 a 인 사원들의 LAST_NAME 을 조회한다.

 (결과 예제)
LAST_NAME
 -------------------------
 Doran
 Errazuriz
 Fleaur
 Kumar
 McCain
 Pataballa
 Sciarra
 Sewall
 Tuvault
 Urman

답 :

SELECT LAST_NAME FROM EMPLOYEES WHERE LAST_NAME LIKE '___a%';

출처: https://cocomo.tistory.com/117 [Cocomo Coding]