SQL 기본 연산자와 절, 집합 연산과 집계함수

1. 기본 연산자

AS: column 별칭 지정

SELECT eno "사원번호", ename AS "사원이름", salary*12*1.1 AS "연봉"
  	FROM amp;

위와 같이 AS는 생략 가능하다.

DISTINCT: 중복되는 값 삭제

SELECT DISTINCT job_id FROM employees;

job_id column의 row 중 중복되지 않는 요소들만 출력

---

2. 절

ORDER BY 절: 정렬하여 검색

ORDER BY sal DESC, eno;

위의 예시의 경우 sal 컬럼을 기준으로 내림차순 정렬하되 값이 중복되는 row의 경우 두번째 정렬 기준인 eno 컬럼을 기준으로 오름차순 정렬한다.
순서상으로 where절 다음에 온다.
ORDER BY에는 컬럼명 대신 컬럼 인덱스(1부터 시작)가 올 수도 있다.

SELECT pname, section, hire_date
      FROM professor
      ORDER BY section, hire_date;

위의 예시는 학과(section) 별 교수의 정보를 부임일자 순으로 검색하는 예시이다.

WHERE 절

SELECT 절에서 선택한 column의 모든 row 중 조건에 맞는 일부 row만 검색

cf.
WHERE 절에서 부등호 연산(>, <,..)
검색 결과 문자열도 사전순으로 비교 연산 가능하다.

AND와 BETWEEN

WHERE salary >= 5000 AND salary <= 10000;
WHERE salary BETWEEN 5000 AND 10000;

OR와 IN

WHERE job_id = 'FI_MGR' OR job_id = 'FI_ACCOUNT';
WHERE job_id IN ('FI_MGR', 'FI_ACCOUNT');

NOT

WHERE NOT department_id = 10;
WHERE department_id ^= 10;
WHERE department_id != 10;
WHERE department_id <> 10;

IS NULL/IS NOT NULL

WHERE commission_pct IS NOT NULL;

LIKE: 특정 문자를 포함하는 문자열 검색

'김%' : '김'으로 시작하는 모든 문자열
'%과' : '과'로 끝나는 모든 문자열
'%김%' : '김'을 포함하는 모든 문자열

'화_' : '화'로 시작하는 2글자 문자열
'_등_' : '등'이 가운데에 들어가는 3글자 문자열

---

3. 집합 연산

1. UNION : 합집합, 중복 row 제거
2. UNIONALL : 합집합, 중복 row 포함
3. MINUS : 차집합
4. INTERSECT : 교집합

SELECT eno, ename, dno
	FROM emp
MINUS
SELECT eno, ename, dno
	FROM emp
    	WHERE dno = 10;

단, 집합 연산에서는 양쪽 피연산자의 column의 갯수와 자료형이 일치해야한다.

---

SQL 기본 연산 문제

1. 부임일이 1995년 이전인 정교수를 검색

SELECT * FROM professor
    WHERE hiredate < '95/1/1'
    AND orders = '정교수';

2. 이름이 2글자인 교수를 검색

SELECT * FROM professor
	WHERE pname LIKE '__';

3. 화학과 학생 중에 4.5 환산 학점이 3.5 이상인 학생을 검색

SELECT major, avr*4.5/4.0 as converted_avr, sname
      FROM student
      WHERE major = '화학'
      AND avr*4.5/4.0 >= 3.5;

AS로 지정한 이름은 바로 사용 불가

---

그룹 함수(집계 함수)

그룹 함수의 종류에는

sum(), avg(), max(), count(),...

가 있다.

그룹 함수는 특정 그룹의 통계를 구하는 함수이기 때문에, 그룹을 묶어주는 GROUP BY 절과 함께 와야한다.

4. GROUP BY 절

이때 주의해야할 점은 GROUP BY 절을 이용할 때에 조회한 Column을 SELECT하는 절에는 그룹 함수 혹은 GROUP BY의 대상 Column만 올 수 있다는 것이다.

처음 group by를 사용하다보면 이를 지키지 않아서 오류가 나는 경우가 매우 많은데, 그 경우 오라클에서 나오는 에러 메세지는 아래와 같다.

ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"

GROUP BY 예제

--부서별 인원수와 그 중 커미션을 받은 인원수
select department_id, count(*), count(commission_pct)
  from employees
  group by department_id
  order by department_id;

count(*)는 department_id로 묶은 그룹의 모든 인원의 수를, count(commission_pct)는 해당 그룹 중 commission_pct가 null이 아닌 인원의 수를 의미한다.

--학과별 학생 수
select count(*), major
  from student
  group by major;

--화학과의 학년별 평균 학점
select avg(avr), syear
  from student
  where major = '화학'
  group by syear;

이때 where 절은 group by syear로 묶은 그룹 중 major가 화학인 그룹을 한 번 더 걸러주는 역할을 하는데, 이것은 테이블에 있는 자료를 그대로 활용하기 때문에 where절로 쿼리가 가능하지만,
실행 중 결과가 나오는 sum, avg 등 그룹 함수의 결과를 기준으로 그룹을 한 번 더 거르고 싶은 경우에는 having 절을 이용한다.

where 절과 having절이 함께 올 경우에는 where 절이 먼저 실행된다.

HAVING

HAVING 예제

--급여 평균이 5000 이하인 부서
SELECT department_id, AVG(salary)
  from employees
  group by department_id
  having AVG(salary) < 5000;

따라서 아래의 두 가지 SQL문은 완전히 같은 쿼리를 의미한다.

--화학과가 아닌 학생들의 과별 평점평균
select avg(avr), major
  from student
  group by major
  having not major = '화학';

select avg(avr), major
  from student
  where major != '화학'
  group by major;

ROLLUP

ROLLUP은 각 그룹별 통계를 구해주는 함수이다.
GROUP BY로 묶은 그룹의 통계이기 때문에 늘 GROUP BY와 함께 사용된다.

---

문장 실행 순서

1. FROM 절 : 발췌 테이블 선택
2. WHERE 절 : 발췌 대상 데이터 필터링
3. GROUP BY 절 : 행들을 소그룹화
4. HAVING 절 : 그룹핑된 값의 조건에 맞는 것만 출력
5. SELECT 절 : 데이터 값을 출력/계산
6. ORDER BY 절 : 데이터 정렬