오늘의 코딩순서 + 포인트
1) 그룹함수⭐
- 테이블의 전체 행을 하나 이상의 컬럼을 기준으로 그룹화하여 그룹별로 결과를 출력하는 함수
- 그룹함수는 통계적인 결과를 출력하는데 자주 사용
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
GROUP BY
- 전체 행을 group_by_expression을 기준으로 그룹화
- 특정 칼럼 값을 기준으로 테이블의 전체 행을 그룹별로 나누기 위한 절
- 예를 들어 교수 테이블에서 소속 학과별이나 직급별로 평균 급여를 구하는 경우
- GROUP BY절에 명시되지 않은 칼럼은 그룹함수와 함께 사용할 수 없음
- GROUP BY절 사용시 적용되는 규칙
- 그룹핑 전에 WHERE절을 사용하여 그룹 대상 집합을 먼저 선택
- GROUP BY절에는 반드시 칼럼 이름을 포함해야 하며 칼럼 별명은 사용할 수 없음
- 그룹별 출력 순서는 오름차순으로 정렬
- SELECT절에서 나열된 칼럼 이름이나 표현식은 GROUP BY절에서 반드시 명시
HAVING : GROUP BY 절에 의해 생성된 그룹별로 조건 부여
- 실행과정
- 테이블에서 WHERE절에 의해 조건을 만족하는 행 집합을 선택
- 행 집합을 GROUP BY절에 의해 그룹핑
- HAVING절에 의해 조건을 만족하는 그룹을 선택
- HAVING절과 WHERE절의 성능 차이
- HAVING: 내부 정렬 과정에 의해 그룹화된 결과 집합에 대해 검색 조건 실행
- WHERE: 그룹화하기 전에 먼저 검색 조건 실행
- 실무 데이터베이스 관점에서의 성능 차이==> WHERE절의 검색 조건을 먼저 실행하는 방법이 효율적임
그룹화하는 행 집합을 줄여서 내부 정렬 시간을 단축, SQL 처리 성능 향상
| 종류 | 의미 |
| ⭐COUNT | 행의 개수 출력 |
| ⭐ MAX | NULL을 제외한 모든 행에서 최대 값 |
| ⭐ MIN | NULL을 제외한 모든 행에서 최소 값 |
| ⭐ SUM | NULL을 제외한 모든 행의 합 |
| ⭐ AVG | NULL을 제외한 모든 행의 평균 값 |
| STDDEV | NULL을 제외한 모든 행의 표준편차 |
| VARIANCE | NULL을 제외한 모든 행의 분산 값 |
| GROUPING | 해당 칼럼이 그룹에 사용되었는지 여부를 1 또는 0으로 반환 |
| GROUPING SETS | 한 번의 질의로 여러 개의 그룹화 기능 |
1. ⭐ COUNT
- 테이블에서 조건을 만족하는 행의 갯수를 반환하는 함수
- 사용법: COUNT(*)
- '*'은 NULL을 포함한 모든 행의 개수
- DISTINCT는 중복되는 값을 제외한 행의 개수
- ALL은 중복되는 값을 포함한 행의 개수, 기본 값은 ALL
- expr 인수에서 사용 가능한 데이터 타입은 CHAR, VARCHAR2, NUMBER, DATE 타입
문제 1-1) 101번 학과 교수중에서 보직수당을 받는 교수의 수를 출력하여라
SELECT COUNT(*) , COUNT(comm)
--*:ROW의 수(여기서는 101번의 row의 수) ,(comm):줌 보고 적기
FROM professor
WHERE deptno = 101
;
2. ⭐ AVG
- 인수로 지정된 칼럼에 대해 조건을 만족하는 행중에서 NULL을 제외한 평균 값을 구하는 함수
- 숫자 데이터 타입에만 사용 가능
3. ⭐ SUM
- 인수로 지정된 칼럼에 대해 조건을 만족하는 행중에서 NULL을 제외한 합계를 구하는 함수
- 숫자 데이터 타입에만 사용 가능
etc. STDDEV, VARIANCE
- 인수로 지정된 칼럼에 대해 조건을 만족하는 행을 대상으로 표준편차와 분산을 구하는 함수
- 숫자 데이터 타입에만 사용 가능
- NULL은 연산에서 제외
문제 1-2) 102번 학과 학생들의 몸무게 평균과 합계를 출력하여라
SELECT AVG(weight), SUM(weight)
FROM student
WHERE deptno = 102
;
문제 1-3) 교수 테이블에서 급여의 표준편차와 분산을 출력해라
SELECT STDDEV(sal) , VARIANCE(sal)
FROM professor
;
문제 1-4) 학과별 학생들의 인원수, 몸무게 평균과 합계를 그룹으로 묶어서 출력하여라
SELECT count(*) , AVG(weight) , SUM(weight)
FROM student
GROUP BY deptno
;
문제 1-5) 교수 테이블에서 학과별로 교수 수와 보직수당을 받는 교수 수를 출력하여라,
단 학과별로 교수수가 2명 이상인 학과만 출력
SELECT deptno , COUNT(*) , COUNT(comm) --deptno: 여기서는 무엇에 대한 통계인지 알려주는 값
FROM professor
-- WHERE : 오류부분 줌 보고 쓰기
GROUP BY deptno -- GROUP BY를 안쓰면 총계값이 나옴, 기사 시험에 틀린문제 찾기로 많이 나옴
HAVING COUNT(*) > 1 -- 이상이라고 해서 2가 아님!! 자주 틀리는 문제 --HAVING은 GROUP BY다음
;
⭐문제 1-6) 학생 수가 4명 이상이고 평균키가 168이상인 학년에 대해서 학년, 학생 수, 평균 키, 평균 몸무게를 출력
단, 평균 키와 평균 몸무게는 소수점 두 번째 자리에서 반올림 하고, 출력순서는 평균 키가 높은 순부터 내림차순으로 출력하고 그 안에서 평균 몸무게가 높은 순부터 내림차순으로 출력 (일반함수, 그룹함수 종합문제!)
SELECT grade , COUNT(*)
, ROUND(AVG(height),1) avg_height
-- 소수점 두 번째 자리'에서' 반올림이기 때문에 2가 아닌 1
, ROUND(AVG(weight),1) avg_weight
FROM student
GROUP BY grade --일반 컬럼은 반드시 GROUP BY에 들어가야함
HAVING count(*) >= 4 and ROUND(AVG(height)) >= 168
ORDER BY avg_height DESC , avg_weight DESC
;
4. ⭐ MAX
- 인수로 지정된 칼럼에 대해 조건을 만족하는 행중에서 최대값을 구하는 함수
- 사용법은 AVG, SUM 함수와 동일하나 문자 데이터 타입에도 사용 가능
5. ⭐ MIN
- 인수로 지정된 칼럼에 대해 조건을 만족하는 행중에서 최소값을 구하는 함수
- 사용법은 AVG, SUM 함수와 동일하나 문자 데이터 타입에도 사용 가능
문제 1-7) 최근 입사 사원과 가장 오래된 사원의 입사일 출력 (emp)
SELECT max(firedate) , min(hiredate)
FROM emp
;
문제 1-8) 부서별 최근 입사 사원과 가장 오래된 사원의 입사일 출력 (emp)
SELECT deptno , max(hiredate) , min(hiredate)
FROM emp
GROUP BY deptno
;
문제 1-9) 부서별, 직업별 count & sum[급여] (emp)
SELECT deptno , job , COUNT(*) , sum(sal)
FROM emp
GROUP BY deptno , job
ORDER BY deptno , job
;
문제 1-10) 문제 1-10) 부서별 급여총액 3000이상인 사람 중 부서번호,부서별 급여최대를 출력하시오 (emp)
SELECT deptno , max(sal)
FROM emp
GROUP BY deptno
HAVING sum(sal) >= 3000
;
문제 1-11) 전체 학생을 소속 학과별로 나누고, 같은 학과 학생은 다시 학년별로 그룹핑하여 학과와 학년별 인원수, 평균 몸무게를 출력 (단, 평균 몸무게는 소수점 이하 첫번째 자리에서 반올림 ) (STUDENT)
SELECT deptno , grade , COUNT(*) , ROUND(AVG(weight))
-- 대분류 소분류
FROM student
GROUP BY deptno , grade
;
8. ROLLUP (실무에서는 많이 안쓰는데 시험에는 나옴)
- GROUP BY 절의 그룹 조건에 따라 전체 행을 그룹화하고 각 그룹에 대해 부분합을 구하는 연산자
- GROUP BY절에 칼럼의 수가 n개이면 ROLLUP 그뤂핑 조합은 n+1개
문제 1-12) 소속 학과별로 교수 급여 합계와 모든 학과 교수들의 급여 합계를 출력하여라
SELECT deptno , SUM(sal)
FROM professor
GROUP BY ROLLUP(deptno)
;
문제1-13) ROLLUP 연산자를 이용하여 학과 및 직급별 교수 수, 학과별 교수 수, 전체 교수 수를 출력하여라
SELECT deptno , position , COUNT(*)
FROM professor
GROUP BY ROLLUP(deptno , position)
;
9. CUBE
- ROLLUP에 의한 그룹 결과와 GROUP BY 절에 기술된 조건에 따라 그룹 조합을 만드는 연산자
- GROUP BY절에 칼럼의 수가 n개이면 CUBE 그룹핑 조합은 2n개
선생님: 실무에서 많이 쓰이지는 않으나 기사시험에서 난이도로 수험자 판별할 때 쓰는 문제
문제1-14) CUBE 연산자를 이용하여 학과 및 직급별 교수 수, 학과별 교수 수, 전체 교수 수를 출력하여라
SELECT deptno , position , COUNT(*)
FROM professor
GROUP BY CUBE(deptno , position)
;2) DeadLock⭐⭐⭐
: 멀티스레드 프로그래밍에서 발생할 수 있는 상태로, 각 스레드가 서로가 가진 자원을 기다리며 무한히 대기하는 상황을 말함. 이러한 상태에서는 모든 스레드가 더 이상 진행할 수 없기 때문에 프로그램에 정지될 수 있음
DeadLock의 발생 조건
- 상호 배제 (Mutual Exclusion): 자원은 한 번에 하나의 스레드만 사용할 수 있어야한다
- 점유와 대기 (Hold and Wait): 최소한 하나의 자원을 점유한 상태에서 다른 자원을 기다리고 있어야한다
- 비선점 (No Preemption): 다른 스레드가 점유한 자원을 강제로 뺏어올 수 없어야한다
- 순환 대기 (Circular Wait): 각 스레드는 순환적으로 다음 스레드가 점유한 자원을 기다리고 있어야한다.
- 프로세스: 프로그램이 실행된 상태, 프로그램이 메모리를 할당받고 CPU 스케줄링을 대기하는 상태
- 트랜젝션 Transaction
- 데이터베이스의 상태를 변화시키기 위해서 수행하는 작업의 단위
- 데이터를 조작하는 업무의 물리적(시간적)단위
- 1개 이상의 명령어(행동, SQL)로 구성된 작업 단위(기간)
- 특징⭐⭐⭐ (면접 질문!!)
- 원자성(Atomicity): 트랜잭션이 작업의 모든 단계를 완전히 수행하거나 아무 작업도 수행하지 않은 상태로 유지되어야 함을 의미함,
트랜잭션 내의 모든 작업은 전부 성공적으로 완료되거나, 어느 하나라도 실패할 경우 모든 작업이 롤백되어 이전 상태로 복원됨 - 일관성(Consistency): 데이터베이스의 일관성을 유지해야 함
이는 트랜잭션이 데이터베이스에서 정의된 규칙과 제약 조건을 준수해야 함을 의미하며, 트랜잭션의 시작 전과 끝난 후에도 데이터베이스는 일관된 상태여야 함 - 고립성(Isolation): 트랜잭션은 다른 트랜잭션으로부터 독립적으로 실행되어야 함
각 트랜잭션은 서로 영향을 주지 않고, 동시에 실행되는 다른 트랜잭션에 영향을 받지 않아야 함,
이는 한 트랜잭션이 다른 트랜잭션의 실행 결과를 볼 수 없고, 별도의 공간에서 작업을 수행하는 것을 의미함 - 지속성(Durability): 트랜잭션이 성공적으로 완료되면, 해당 트랜잭션에 의한 변경 내용은 영구적으로 저장되어야 함
시스템 장애 또는 전원 손실과 같은 예상치 못한 상황이 발생하더라도, 데이터베이스는 영구적으로 변경된 상태를 유지해야 함
- 원자성(Atomicity): 트랜잭션이 작업의 모든 단계를 완전히 수행하거나 아무 작업도 수행하지 않은 상태로 유지되어야 함을 의미함,
cmd창에 sqlplus scott/tiger 입력
해결방법 : 두 개의 프로세스중 하나를 죽인다===> 도구> 세션모니터> 우클릭> 세션종료
3) Join⭐⭐⭐
1. Join
Join의 정의
- 하나의 SQL 명령문에 의해 여러 테이블에 저장된 데이터를 한번에 조회할 수 있는 기능
- 관계형 데이터 베이스의 분야의 표준
- 두 개 이상의 테이블을 '결합'한다는 의미
- 크게 Inner Join과 Outer Join이 있음
Join의 필요성
- 여러 테이블에 분산되어 있는 데이터를 통합하고 관계를 형성할 수 있어 더 큰 범위의 데이터를 분석하고 원하는 결과를 얻을 수 있음
- 데이터 정규화를 통해 중복을 최소화하고 일관성을 유지하기 위해 테이블을 분리하기도 하는데, 이렇게 분리된 테이블을 다시 연결하여 원하는 정보를 얻을 수 있음
- ⭐데이터 정규화(normalization): 데이터를 특정 범위로 변환하여 범위를 일치시키는 작업
목적=> 1. 이상치(데이터의 범위에서 벗어난 값)처리 2. 변수간 스케일(척도, 수치화) 차이 해결
3. 모델의 수렴 속도 향상 4. 고차원 데이터의 문제 해결
- ⭐데이터 정규화(normalization): 데이터를 특정 범위로 변환하여 범위를 일치시키는 작업
- Join을 사용하면 데이터베이스 스키마
- 스키마(schema): 관계형 데이터베이스 내에서 데이터가 구성되는 방식
여기에는 테이블 이름, 필드, 데이터 형식 빛 이러한 엔티티 간의 관계와 같은 논리적 제약 조건이 포함됨 - 엔티티(Entity): 데이터 모델링에서 사용되는 객체, 업무에 필요하고 유용한 정보를 저장하고 관리하기 위한 추상적인 것
- 스키마(schema): 관계형 데이터베이스 내에서 데이터가 구성되는 방식
Join의 수행 원리 ⭐⭐⭐ (면접 질문)
- Hash Join
- 해싱 기법을 이용하여 조인을 수행함
- 조인될 두 테이블 중 하나를 해시 테이블로 선정하여 조인될 테이블의 조인 키 값을 Hash 알고리즘으로 비교하여 매치되는 결과값을 얻는 방식
- 조인 칼럼의 인덱스를 사용하지 않기 때문에 조인 칼럼의 인덱스가 존재하지 않을 경우데도 사용할 수 있는 기법
- 메모리 사용이 큰 대용량 테이블 조인시 메모리 외에 임시영역(PGA 메모리)까지 사용하여 저장할 수 있어 유리함
- 해쉬 함수를 이용하여 조인을 수행하기 때문에 '='로 수행하는 조인에서만 사용 가능함
- Sort Merge Join
- 조인 컬럼을 기준으로 데이터를 정렬하여 조인을 수행
- 주로 스캔 방식으로 데이터를 읽음
- 인덱스가 없어도 가능한 조인법
- Hash Join 과는 달리 동등 조인뿐만 아니라 비동등 조인에 대해서도 조인 작업이 가능하다는 장점이 있음
- 두 테이블의 사이즈가 비슷한경우에 유리하며, 사이즈 차이가 큰 경우에는 불리하고, 비효율적인 방법임
- NL(Nested Loop) Join
- 프로그래밍에서 사용하는 중첩된 반복문과 유사한 방식으로 조인을 수행함
- 주로 랜덤 액세스 방식으로 데이터를 읽음
- 절차적이며, 프로그래밍에서 FOR, WHILE문 과 같은 구조로 수행됨
- 선행테이블은 풀스캔하므로, 선행테이블의 크기가 작을수록 유리하기 때문에, 두 테이블의 크기 차이가 있는 경우 유리하게 사용될 수 있는 방법임
- 후행테이블에 대해서는 반드시 인덱스가 존재해야 NL 조인이 가능함
- 덱스 구성 전략이 특히 중요하며 조인 컬럼에 대한 인덱스가 있는가 없는가와, 있다면 컬럼이 어떻게 구성됐느냐에 따라 조인 효율이 크게 달라짐
- 랜덤 액세스 방식으로 데이터를 읽기 때문에 처리 범위가 좁은 것이 유리함
두 개의 테이블을 따로 보는 번거로운 방법↓↓↓
문제 3-1) 학번이 10101인 학생의 이름과 소속 학과 이름을 출력하여라
SELECT studno , namd , deptno
FROM student
WHERE studno = 10101
;
문제 3-2) 학과를 가지고 학과이름
SELECT dname
FROM department
WHERE deptno = 101
;
두 개의 테이블의 공통점을 묶어서 한꺼번에 보는 간편한 방법: JOIN
문제 3-3) 3-1과 3-2를 한 번에 조회하는 법===>Join 사용
SELECT studno, name, --가장 기본적인 틀
student.deptno, department.dname
FROM student, department
WHERE student.deptno = department.deptno
;
위를 더 줄여서 쓰는 법: 테이블별명
- 테이블 이름이 너무 긴 경우 사용
- 테이블 이름을 대신하는 별명 사용 가능
- FROM절에서 테이블 이름 다음에 공백을 두고 별명 정의
- 테이블 별명 작성 규칙
- 테이블의 별명은 30자까지 가능, 너무 길지 않게 작성
- FROM절에서 테이블 이름을 명시하고 공백을 둔 다음 테이블 별명지정
- 하나의 SQL 명령문에서 테이블 이름과 별명을 혼용할 수 없음
- 테이블의 별명은 해당 SQL 명령문 내에서만 유효함
SELECT studno , name , s.deptno, dname -->deptno 앞에 구별이 가도록 꼭 별칭 붙이기
FROM student s, department d
WHERE s.deptno = d.deptno
;
칼럼 이름의 애매모호성 (ambiguously) 해결방법
- 서로 다른 테이블에 있는 동일한 칼럼 이름을 연결할 경우
- 칼럼 이름 앞에 테이블 이름을 접두사로 사용(별칭 사용)
- 테이블 이름과 칼럼 이름을 마침표(.)로 구분
- SQL 명령문에 대한 구문분석 시간(parsing time) 줄임 ex) Student.deptno와 department.deptno
-- 애매모호성 (ambiguously) ---> 해결방법: 별칭 붙이기
SELECT studno , name , d.deptno, d.dname --> 구별이 가도록 꼭 별칭 붙이기
FROM student s, department d
WHERE s.deptno = d.deptno
;
문제 3-4) 전인하 학생의 학번, 이름, 학과 이름 그리고 학과 위치를 출력
SELECT s.studno , s.name , d.dname , d.loc
FROM student s, department d
WHERE s.deptno = d.deptno
AND s.name = '전인하'
;
문제 3-5) 몸무게가 80kg이상인 학생의 학번, 이름, 체중, 학과 이름, 학과위치를 출력
SELECT s.studno , s.name , s.weight , d.dname , d.loc
FROM student s , department d
WHERE s.deptno = d.deptno
AND s.weight >= 80
;
2. 카티션 곱(CROSS JOIN)
- 정의: 두 개 이상의 테이블에 대해 연결 가능한 행을 모두 결합
- 카티션을 쓰는 이유: 1.개발자의 실수 2. 개발 초기에 많은 Data를 생성하기 위해
SELECT s.studno , s.name , d.dname , d.loc , s.weight , d.deptno
FROM student s , department d
;↓↓↓아래와 같음
SELECT s.studno , s.name , d.dname , d.loc , s.weight , d.deptno
FROM student s CROSS JOIN department d
;3. Inner Join(= Natural Join )
- 성능은 우수, 다만 누락(NULL)이 없다는 조건에서만 사용해야함
Oracle Join 표기법
SELECT s.studno , s.name , d.deptno , d.dname
FROM student s , department d
WHERE s.deptno = d.deptno
;
3-1. Natural Join
- 자연조인을 이용한 EQUI JOIN (오라클 9i 버전부터 EQUI JOIN을 자연조인이라 명명)
- WHERE 절을 사용하지 않고 NATURAL JOIN 키워드 사용
- 오라클에서 자동적으로 테이블의 모든 칼럼을 대상으로 공통 칼럼을 조사 후, 내부적으로 조인문 생성
>>Natural Join Convert Error 해결
Natural Join Convert Error 해결 방법: NATURAL JOIN시 조인 애트리뷰트에 테이블 별명을 사용하면 오류가 발생
SELECT s.studno , s.name , s.weight , d.dname , d.loc , d.deptno
FROM student s
NATURAL JOIN department d
;
∴ANSI 표기법 사용(별칭 삭제), 시험에서도 이 표기법을 사용
SELECT s.studno , s.name , s.weight , d.dname , d.loc , deptno
FROM student s
NATURAL JOIN department d
;
문제 3-6) NATURAL JOIN을 이용하여 교수 번호, 이름, 학과 번호, 학과 이름을 출력하여라
SELECT p.profno , p.name , deptno , d.dname
FROM professor p
NATURAL JOIN department d
;
문제 3-7) NATURAL JOIN을 이용하여 4학년 학생의 이름, 학과 번호, 학과 이름을 출력하여라
SELECT s.name , s.grade , deptno , d.dname
FROM student s
NATURAL JOIN department d
WHERE s.grade = '4'
;
3-2. Equi Join
- 조인 대상 테이블에서 공통 칼럼을 ‘=‘(equal) 비교를 통해 같은 값을 가지는 행을 연결하여 결과를 생성하는 Join 방법
- SQL 명령문에서 가장 많이 사용하는 조인 방법
- 조인 애트리뷰트(join attribute(속성))
- JOIN ~ USING 절을 이용한 EQUI JOIN
- USING절에 조인 대상 칼럼을 지정
- 칼럼 이름은 조인 대상 테이블에서 동일한 이름으로 정의되어 있어야함
- 조인 애트리뷰트에 테이블 별명을 사용하면 오류가 발생함
문제 3-8) JOIN ~ USING 절을 이용하여 학번, 이름, 학과번호, 학과이름, 학과위치를 출력하여라
SELECT s.studno , s.name , deptno , dname
FROM student s , JOIN department
USING(deptno)
;
문제 3-9) EQUI JOIN의 3가지 방법을 이용하여 성이 ‘김’씨인 학생들의 이름, 학과번호,학과이름을 출력
-- 1) Oracle의 Join--> WHERE절을 사용한 방법, allias(별명)를 써야함
SELECT s.studno , s.name , d.deptno , d.dname
FROM student s , department d
WHERE s.deptno = d.deptno
AND s.name Like'김%'
;
-- 2) NATURAL JOIN절을 사용한 방법
SELECT s.studno , s.name , deptno , dname
FROM student s NATURAL JOIN department d
WHERE s.name Like '김%'
;
-- 3) JOIN~USING절을 사용한 방법
SELECT s.studno , s.name , deptno , d.dname
FROM student s JOIN department d
USING (deptno)
WHERE s.name Like '김%'
;
-- 4) ANSI JOIN 법( INNER JOIN ~ ON), allias(별명)를 쓰면 안됨, ON에서 나오는 JOIN은 명명, WHERE조건으로 필요한거 있으면 넣기
SELECT s.studno , s.name , d.deptno , dname
FROM student s INNER JOIN department d
ON s.deptno = d.deptno
WHERE s.name Like '김%'
;
3-3. NON-EQUI JOIN (범위 JOIN이라고도 함)⭐⭐
: BETWEEN a AND b 와 같이 ‘=‘ 조건이 아닌 연산자 사용
CREATE TABLE "SCOTT"."SALGRADE2"
( "GRADE" NUMBER(2,0),
"LOSAL" NUMBER(5,0),
"HISAL" NUMBER(5,0)
);
문제 3-10) 교수 테이블과 급여 등급 테이블을 NON-EQUI JOIN하여 교수별로 급여 등급을 출력하여라
--새 SALGRADE2 생성 후 아래 문제 작성
SELECT p.profno , p.name , p.sal , s.grade
FROM professor p , salgrade2 s
WHERE p.sal BETWEEN s.losal AND s.hisal
;
4. Outer Join⭐⭐⭐
- LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, SELF JOIN이 있다
- EQUI JOIN에서 양측 칼럼 값중의 하나가 NULL 이지만 조인 결과로 출력할 필요가 있는 경우 OUTER JOIN 사용
- EQUI JOIN의 조인 조건에서 양측 칼럼 값 중, 어느 하나라도 NULL이면 '=' 비교 결과가 거짓이 되어 NULL 값을 가진 행은 조인 결과로 출력 불가=> NULL에 대해서 어떠한 연산을 적용하더라도 연산 결과는 NULL
- 성능은 Inner Join보다는 떨어지지만 데이터를 누락시키지 않음
- LEFT OUTER JOIN이든, RIGHT OUTER JOIN이든 EQUI JOIN은 무조건 나온다
저 둘을 사용하는 이유는 EQUI JOIN으로 나오지 않는 값들을 나오게 하기 위해서임, 나오게 하기 위해 (+)를 쓴다 - (+)기호 (위치에 따른 역할 설명 할 줄 알아야함)
- WHERE 절의 Join조건에서 OUTER JOIN 연산자인 (+)기호 사용
- Join 조건문에서 NULL이 출력되는 테이블의 칼럼에 (+)기호 추가
- Outer Join에서는 IN 연산자를 사용 불가
SELECT s.name , s.grade , p.name , p.position
FROM student s , professor p
WHERE s.profno = p.profno
;
4-1. LEFT OUTER JOIN
== ORACLE LEFT OUTER JOIN: FROM절의 왼쪽에 위치한 테이블이 NULL을 가질 경우에 사용
문제 3-11) 학생 테이블과 교수 테이블을 조인하여 이름, 학년, 지도교수의 이름, 직급을 출력
단, 지도교수가 배정되지 않은 학생이름도 함께 출력하여라.
SELECT s.name , s.grade , p.name , p.position
FROM student s , professor p
WHERE s.profno = p.profno(+)
;
== ANSI LEFT OUTER JOIN
SELECT s.studno , s.name , s.profno , p.name
FROM student s
LEFT OUTER JOIN professor p
ON s.profno = p.profno
;
4-2. RIGHT OUTER JOIN
== ORACLE RIGHT JOIN: FROM절의 오른쪽에 위치한 테이블이 NULL을 가질 경우에 사용
문제 3-12) 학생 테이블과 교수 테이블을 조인하여 이름, 학년, 지도교수 이름, 직급을 출력
단, 지도학생을 배정받지 않은 교수 이름도 함께 출력하여라
SELECT s.name , s.grade , p.name , p.position
FROM student s , professor p
WHERE s.profno(+) = p.profno
ORDER BY p.profno
;
>>>>>0628 이어서 수업
=== ANSI RIGHT OUTER JOIN
SELECT s.studno , s.name , s.profco , p.name
FROM student s
RIGHT OUTER JOIN professor p
ON s.profno = p.profno
;
4-3. FULL JOIN
=== ORACLEFULLOUTER JOIN⭐(ANSI JOIN은 FULL OUTER JOIN은 지원하지 않음)
문제 3-13) 학생 테이블과 교수 테이블을 조인하여 이름, 학년, 지도교수 이름, 직급을 출력
단, 지도학생을 배정받지 않은 교수 이름 및 지도교수가 배정되지 않은 학생이름 함께 출력하여라
-->> Oracle 지원은 사실 안 함ㅎㅎ
SELECT s.name , s.grade , p.name , p.position
FROM student s , professor p
WHERE s.profno(+) = p.profno(+)
ORDER BY p.profno
;
∴이렇게 써야한다 ↓↓↓ ⭐(ANSI JOIN은 FULL OUTER JOIN은 지원하지 않음, 다만 UNION을 사용하면 가능)
SELECT s.name , s.grade , p.name , p.position
FROM student s , professor p
WHERE s.profno = p.profno(+)
ORDER BY p.profno
UNION
SELECT s.name , s.grade , p.name , p.position
FROM student s , professor p
WHERE s.profno(+) = p.profno
ORDER BY p.profno
;
=== ANSI FULL OUTER JOIN
SELECT s.studno , s.name , s.profno , p.name
FROM student s
FULL OUTER JOIN professor p
ON s.profno = p.profno
;
4-4. SELF JOIN⭐⭐⭐
- 하나의 테이블내에 있는 칼럼끼리 연결하는 조인이 필요한 경우 사용
- 조인 대상 테이블이 자신 하나라는 것 외에는 EQUI JOIN과 동일
- 계층형 구조 테이블에 종종 쓰임
SELECT c.deptno , c.dname , c.college , d.dname college_name
-- 학과명 학부
FROM department c , department d
WHERE c.college = d.deptno
;
문제 3-14) 부서 번호가 201 이상인 부서 이름과 상위 부서의 이름을 출력
결과 : xxx소속은 xx
SELECT dept.dname||'의 소속은' || org.dname || '이다'
FROM department dept , department org
WHERE dept.college = org.deptno
AND dept.deptno >= 201
;x학부
4:36에 차이점 설명 적기
| 이름 | Inner Join | Equi Join | Outer Join |
| 차이점 | |||
| 성능 | 성능이 떨어짐에도 쓰는 이유는 무결점 때문 |
현장과제1
>>>0626 오늘의 숙제에 이은 현장과제(sql_0626참고)
6. emp Table 의 이름, 급여/7을 각각 절사하여 출력
SELECT ename , TRUNC(sal/7)
FROM emp
;
7. emp Table 의 이름, 급여/7한 결과를 반올림,절사,ceil,floor
SELECT ename , sal/7 , ROUND(sal/7) , TRUNC(sal/7) , CEIL(sal/7) , FLOOR(sal/7)
FROM emp
;
8. emp Table 의 이름, 급여, 급여/7한 나머지
SELECT ename , sal , MOD(sal/7,comm)
FROM emp
;
9. emp Table 의 이름, 급여, 입사일, 입사기간(각각 날자,월)출력, 소숫점 이하는 반올림
>>>내가한것
SELECT ename , sal , hiredate,
MONTHS_BETWEEN(SYSDATE , hiredate)
FROM emp
;
>>선생님의 답변
SELECT ename , sal , hiredate,
round(sysdate-hiredate),
round(month_between(sysdate,hiredate))
FROM emp
;
10.emp Table 의 job 이 'CLERK' 일때 10% ,'ANALYSY' 일때 20%
-- 'MANAGER' 일때 30% ,'PRESIDENT' 일때 40%
-- 'SALESMAN' 일때 50%
-- 그외일때 60% 인상 하여
-- empno, ename, job, sal, 및 각 인상 급여를 출력하세요(CASE/Decode문 사용)
CASE문
>>>내가한것
SELECT empno, ename, job, sal,
CASE WHEN sal = 0.1*sal Then 'CLERK'
WHEN sal = 0.2*sal Then 'ANALYSY'
WHEN sal = 0.3*sal Then 'MANAGER'
WHEN sal = 0.4*sal Then 'PRESIDENT'
Else
END
FROM
;
>>선생님의 답변
SELECT empno, ename, job, sal,
CASE job WHEN 'CLERK' Then sal*1.1
WHEN 'ANALYSY' Then sal*1.2
WHEN 'MANAGER' Then sal*1.3
WHEN 'PRESIDENT' Then sal*1.4
WHEN 'SALESMAN' Then sal*1.5
Else sal*1.6
END 급여인상
FROM emp
;
DECODE문
>>>내가한것
SELECT empno, ename, job, sal, DECODE(sal , 0.1*sal , 'CLERK'
, 0.2*sal , 'ANALYSY'
, 0.3*sal , 'MANAGER'
, 0.4*sal , 'PRESIDENT'
, 0.6*sal
FROM emp
;
>>선생님의 답변
SELECT empno, ename, job, sal,
DECODE (job , 'CLERK' , sal*1.1
, 'ANALYSY' , sal*1.2
, 'MANAGER' , sal*1.3
, 'PRESIDENT' , sal*1.4
, 'SALESMAN' , sal*1.5
, sal*1.6
) 급여인상
FROM emp
;오늘의 숙제
'DB > Oracle' 카테고리의 다른 글
| 2024_07_01_월 (0) | 2024.07.01 |
|---|---|
| 2024_06_28_금 (0) | 2024.06.28 |
| 2024_06_26_수 (0) | 2024.06.26 |
| 2024_06_26_수 (0) | 2024.06.26 |
| 2024_06_25_화 (0) | 2024.06.25 |
