데이터 베이스 기초3(SELECT/BETWEEN, IN, 등가조인, 비등가조인)

데이터베이스

데이터 베이스 기초3(SELECT/BETWEEN, IN, 등가조인, 비등가조인)

slcry 2022. 10. 31. 09:52

BETWEEN..AND 연산자

SQL> SELECT [DISTINCT | ALL] 컬럼, 컬럼 ...
2 FROM 테이블
3 WHERE 컬럼 BETWEEN 값1 AND 값2 (컬럼 >= 값1 AND 컬럼 <= 값2)
4 ORDER BY 컬럼 [ASC/DESC], 컬럼 [ASC/DESC].......

예제 1. 다음 조건에 맞는 사원을 검색한다.
. 급여가 1000에서 2000 이내인 사원
. 10, 20번 부서 사원

SQL> SELECT * FROM emp
2 WHERE sal BETWEEN 1000 AND 2000;

ENO ENAME SE JOB MGR HDATE SAL COMM DN
---- ---------- -- -------- ---- -------- ---------- ---------- --
2002 제갈민 남 개발 0202 96/04/30 1520 2000 20
2007 이초록 남 개발 0001 92/09/05 1989 2300 30
0702 김민지 남 회계 0301 17/01/09 1100 60 02

SQL> SELECT * FROM emp
2 WHERE dno BETWEEN '10' AND '20';

ENO ENAME SE JOB MGR HDATE SAL COMM DN
---- ---------- -- -------- ---- -------- ---------- ---------- --
1001 문시현 남 모델링 0201 91/02/01 4500 520 10
1002 김주란 여 모델링 0201 92/03/03 4100 330 20
2001 남궁연호 남 개발 0202 93/12/13 3950 200 10
.............
............

예제 2. 1992년에서 1996년 사이에 입사한 사원을 검색한다.

SQL> SELECT * FROM emp
2 WHERE hdate BETWEEN '1992/01/01' AND '1996/12/31' 3 ORDER BY hdate;

ENO ENAME SE JOB MGR HDATE SAL COMM DN
---- ---------- -- -------- ---- -------- ---------- ---------- --
0301 이승철 남 회계 0001 92/02/01 3400 0 02
1002 김주란 여 모델링 0201 92/03/03 4100 330 20

2008 윤고은 여 개발 0001 92/03/03 2100 40
.............
............

ALTER SESSION SET nls_date_format='YYYY/MM/DD:HH24:MI:SS'

예제 3. 급여가 2000에서 1000 사이인 사원을 검색한다.

SQL> SELECT * FROM emp
2 WHERE sal BETWEEN 2000 AND 1000; 선택된 레코드가 없습니다.

IN 연산자

SQL> SELECT [DISTINCT | ALL] 컬럼, 컬럼 ...
2 FROM 테이블
3 WHERE 컬럼 IN (값1, 값2 ...) (컬럼=값1 OR 컬럼=값2 OR ....)
4 ORDER BY 컬럼 [ASC/DESC], 컬럼 [ASC/DESC].......

예제 4. 다음 조건에 맞는 사원을 검색한다.
. 개발이나 관리 업무를 담당하는 사원.
. 10, 20번 부서 사원

SQL> SELECT * FROM emp
2 WHERE job IN ('개발', '관리') 3 ORDER BY job;

ENO ENAME SE JOB MGR HDATE SAL COMM DN
---- ---------- -- -------- ---- ---------- ---------- ---------- --
2001 남궁연호 남 개발 0202 1993/12/13 3950 200 10
2002 제갈민 남 개발 0202 1996/04/30 1520 2000 20
2008 윤고은 여 개발 0001 1992/03/03 2100 40
2007 이초록 남 개발 0001 1992/09/05 1989 2300 30
2003 정의찬 남 개발 0202 1992/03/03 4350 30

SQL> SELECT * FROM emp
2 WHERE dno IN ('10', '20') 3 ORDER BY dno;

ENO ENAME SE JOB MGR HDATE SAL COMM DN
---- ---------- -- -------- ---- -------- ---------- ---------- --
0401 김진성 남 회계 1001 08/03/13 3200 1000 10
0269 권나현 여 분석 0301 15/05/21 2600 1900 10
3001 김선유 남 분석 1001 98/10/17 3200 300 10
.............
............

예제 3. 결과가 동일한 두 개의 SQL문을 비교해 보자
SQL> SELECT * FROM emp
2 WHERE dno BETWEEN '10' AND '20' 3 AND job = '개발';

ENO ENAME SE JOB MGR HDATE SAL COMM DN
---- ---------- -- -------- ---- -------- ---------- ---------- --
2001 남궁연호 남 개발 0202 93/12/13 3950 200 10
2002 제갈민 남 개발 0202 96/04/30 1520 2000 20

SQL> SELECT * FROM emp
2 WHERE dno IN ('10', '20')
3 AND job = '개발';

실습

1. 평점이 3.0에서 4.0 사이의 학생을 검색한다.

A) SELECT * FROM STUDENT WHERE AVR BETWEEN 3.0 AND 4.0;

2. 1999년에서 2001년까지 부임한 교수의 명단을 검색한다.

A) SELECT * FROM PROFESSOR WHERE HIREDATE BETWEEN '1999/01/01'

2 AND '2001/12/31';

3. 화학과와 물리학과, 생물학과 학생을 검색한다.

A) SELECT * FROM STUDENT WHERE MAJOR IN('화학', '물리', '생물');

4. 정교수와 조교수를 검색한다.

A) SELECT * FROM PROFESSOR WHERE ORDERS IN('정교수', '부교수');

5. 학점수가 1학점, 2학점인 과목을 검색한다.

A) SELECT * FROM COURSE WHERE ST_NUM IN('1', '2');

6. 1,2 학년 학생 중에 평점이 2.0에서 3.0사이인 학생을 검색한다.

A) SELECT * FROM STUDENT WHERE SYEAR IN('1', '2') AND AVR BETWEEN 2.0 AND 3.0;

7. 화학, 물리학과 학생 중 1,2 학년 학생을 성적순으로 검색한다.

A) SELECT * FROM STUDENT WHERE MAJOR IN('화학', '물리') AND

2 SYEAR IN('1', '2') ORDER BY AVR DESC;

8. 물리, 화학과 학생 중 4.5 환산 평점이 3.5에서 4.0 사이인 학생을 검색한다.

A) SELECT * FROM STUDENT WHERE MAJOR IN('물리', '화학') AND

2 AVR*1.125 BETWEEN 3.5 AND 4.0;

9. 물리, 화학과 학생의 정보를 학년별 성적순으로 검색한다.

A) SELECT * FROM STUDENT WHERE MAJOR IN('물리', '화학')

2 ORDER BY SYEAR, AVR DESC;

10. 물리, 화학과 교수 중에 1999년에서 2000년 사이에 부임한 교수의 정보를 직위별로 검색한다.

A) SELECT * FROM PROFESSOR WHERE SECTION IN('물리', '화학') AND

2 HIREDATE BETWEEN '1999/01/01' AND '2000/12/31' ORDER BY ORDERS;

등가 조인과 비 등가 조인(Equi join, Nequi join : INNER JOIN)

SQL> SELECT 테이블1.컬럼, ...테이블2.컬럼, ...
2 FROM 테이블1, 테이블2, ...
3 WHERE 조인_조건
4 AND 일반_조건

- 조인 조건은 두 테이블간의 관계를 수식으로 표현한 것이다.
- 조인 조건은 SELECT 문에 사용된 테이블의 개수에 따라 달라지지만 테이블 n개 라면

조인 조건의 개수는 항상 (n-1)개 이상이다.
- 조인 조건에 '='를 이용하는 조인을 등가 조인(Equi-join)이라고 하고 부등호가 포함된 조인
조건을 이용하는 경우 비 등가 조인(Non equi-join)이라고 한다. 예제 1. 각 사원의 근무 부서를 검색한다.

SQL> SELECT eno 사번, ename 이름, emp.dno 부서번호,
2 dept.dno 부서번호, dname 부서명
3 FROM dept, emp
4 WHERE dept.dno = emp.dno;

사번 이름 부서번호 부서번호 부서명
---- ---------- -------- -------- -------
0001 안영희 01 01 총무
0201 안영숙 01 01 총무
0202 손하늘 01 01 총무
.............
............

예제 2. 광주에서 근무하는 직원의 명단을 검색한다.(부서번호와 부서명도 검색한다.)
SQL> SELECT loc 근무처, d.dno 부서번호, dname 부서명,
2 eno 사번, ename 이름
3 FROM dept d, emp e
4 WHERE d.dno=e.dno
5 AND loc = '광주';

근무처 부서번호 부서명 사번 이름
------ -------- ------ ---- ------ 광주 30 ITEA 1003 양선호
광주 30 ITEA 2003 정의찬
광주 30 ITEA 2007 이초록
예제 3. 각 직원의 급여를 10% 인상한 경우 급여 등급을 검색한다.

SQL> SELECT eno 사번, ename 이름,
2 sal*1.1 인상된급여, grade 등급
3 FROM emp, salgrade
4 WHERE sal*1.1 BETWEEN losal AND hisal;

사번 이름 인상된급여 등급
---- ---------- ---------- ----------
0309 김선유 990 5
0702 김민지 1210 4
2002 제갈민 1672 4
.............
.............

예제 4. 조인 조건이 없는 잘못된 조인문장의 결과를 확인해 보자.

SQL> SELECT d.dno, dname, e.dno, ename
2 FROM dept d, emp e;
DNO DNAME DNO ENAME
---- ---------- ---- ------
01 총무 01 안영희
01 총무 01 안영숙
.....
.....
50 POS 02 김민지

예제 4)와 같은 조인을 Cross join(교차 조인)이라고 하는데 이런 조인은 특별한 경우 사용된다.

cross join은 잘못된 결과값(Cartesian product)을 생성한다.

테이블과 관계

* 부모 테이블과 자식 테이블

실습

1. 송강 교수가 강의하는 과목을 검색한다.

A) SELECT P.PNO 교수번호, P.PNAME 교수이름, P.SECTION 교수담당과목, C.CNO 과목번호, C.CNAME 과목이름

2 FROM PROFESSOR P, COURSE C WHERE P.PNO = C.PNO AND P.PNAME = '송강';

2. 과목명에 화학이 포함된 과목을 강의하는 교수의 명단을 검색한다.

A) SELECT P.PNO 교수번호, P.PNAME 교수이름, P.SECTION 교수담당과목, C.CNO 과목번호, C.CNAME 과목이름

2 FROM PROFESSOR P, COURSE C WHERE P.PNO = C.PNO AND P.SECTION = '화학';

3. 학점이 2학점인 과목과 이를 강의하는 교수를 검색한다.

A) SELECT C.CNO 과목번호, C.CNAME 과목이름, C.ST_NUM 학점, C.PNO 교수번호,

2 P.PNAME 교수이름, P.SECTION 교수담당과목 FROM PROFESSOR P, COURSE C

3 WHERE P.PNO = C.PNO AND C.ST_NUM = 2;

4. 화학과 교수가 강의하는 과목을 검색한다.

A) SELECT P.PNO 교수번호, P.PNAME 교수이름, P.SECTION 교수담당과목, C.CNO 과목번호, C.CNAME 과목이름

2 FROM PROFESSOR P, COURSE C WHERE P.PNO = C.PNO AND P.SECTION = '화학';

5. 화학과 1학년 학생의 기말고사 성적을 검색한다.

A) SELECT S.SNO 학번, S.SNAME 학생이름, S.MAJOR 학과, S.SYEAR 학년, SC.RESULT 기말점수

2 FROM STUDENT S, SCORE SC WHERE S.SNO = SC.SNO AND S.SYEAR = 1 AND S.MAJOR = '화학';

6. 일반화학 과목의 기말고사 점수를 검색한다.

A) SELECT C.CNO 과목번호, C.CNAME 과목이름, SC.RESULT 기말점수

2 FROM COURSE C, SCORE SC WHERE C.CNO = SC.CNO AND C.CNAME = '일반화학';

7. 화학과 1학년 학생의 일반화학 기말 고사 점수를 검색한다.

A) SELECT S.SNO 학번, S.SNAME 이름, S.SYEAR 학년, S.MAJOR 학과, C.CNAME 과목이름, SC.RESULT 기말점수

2 FROM STUDENT S, SCORE SC, COURSE C

3 WHERE S.SNO = SC.SNO AND C.CNO = SC.CNO AND S.MAJOR = '화학' AND C.CNAME = '일반화학'

AND S.SYEAR =1;

8. 화학과 1학년 학생이 수강하는 과목을 검색한다.

A) SELECT DISTINCT S.SNO 학번, S.SNAME 이름, S.SYEAR 학년, S.MAJOR 학과, C.CNAME 과목이름

2 FROM STUDENT S, COURSE C SCORE SC

3 WHERE S.SNO = SC.SNO AND C.CNO = SC.CNO AND S.MAJOR = '화학', S.SYEAR = 1;

9. 일반화학 과목에서 평가 점수가 A인 학생의 명단을 검색한다.

A) SELECT S.SNO 학번, S.SNAME 이름, C.CNO 과목번호, C.CNAME 과목이름, g.grade 등급
2 FROM COURSE C, SCGRADE G, STUDENT S, SCORE SC

3 WHERE S.SNO = SC.SNO AND SC.CNO = C.CNO AND C.CNAME = '일반화학' AND SC.RESULT

4 BETWEEN LOSCORE AND HISCORE AND G.GRADE = 'A';

10. 송강 교수의 과목을 수강하는 학생의 기말고사 점수를 성적 순서로 검색한다.

A) SELECT S.SNO 학번, S.SNAME 이름, P.PNAME 교수이름, C.CNAME 과목이름, SC.RESULT 기말점수

2 FROM STUDENT S, COURSE C, PROFESSOR P, SCORE SC

3 WHERE S.SNO = SC.SNO AND P.PNO = C.PNO AND C.CNO = SC.CNO AND

P.PNAME = '송강' ORDER BY SC.RESULT DESC;

11. 화학과 1학년 학생의 기말고사 성적을 학점(A,B,C,D,F)으로 검색한다.

A) SELECT S.SNO 학번, S.SNAME 이름, S.SYEAR 학년, S.MAJOR 학과, C.CNAME 과목이름, g.grade 등급

2 FROM STUDENT S, COURSE C, SCORE SC, SCGRADE G WHERE S.SNO = SC.SNO AND C.CNO = SC.CNO

3 AND S.SYEAR = 1 AND S.MAJOR = '화학' AND SC.RESULT BETWEEN LOSCORE AND HISCORE;

12. 송강 교수가 강의하는 과목에서 평가 점수가 A인 학생의 명단을 과목명과 함께 검색한다.

A) SELECT S.SNO 학번, S.SNAME 이름, P.PNAME 교수이름, C.CNAME 과목이름, g.grade 등급

2 FROM STUDENT S, COURSE C, PROFESSOR P, SCORE SC, SCGRADE G

3 WHERE S.SNO = SC.SNO AND C.CNO = SC.CNO AND P.PNO = C.PNO AND P.PNAME = '송강' AND

4 SC.RESULT BETWEEN LOSCORE AND HISCORE AND G.GRADE = 'A';

13. 화학과 1학년 학생에게 강의하는 교수의 명단을 검색한다.

A) SELECT DISTINCT S.SNO 학번, S.SNAME 이름, S.SYEAR 학년, S.MAJOR 학과, P.PNAME 교수이름

2 FROM STUDENT S, COURSE C, PROFESSOR P, SCORE SC

3 WHERE S.SNO = SC.SNO AND C.CNO = SC.CNO AND P.PNO = C.PNO

4 AND S.SYEAR = 1 AND S.MAJOR = '화학';