데이터베이스 기초2(SELECT/ASC, DESC, WHERE, 관계연산자, LIKE)

데이터베이스

데이터베이스 기초2(SELECT/ASC, DESC, WHERE, 관계연산자, LIKE)

slcry 2022. 10. 27. 09:44

정렬된 데이터 검색

SQL> SELECT [DISTINCT | ALL] 컬럼, 컬럼 ...
2 FROM 테이블
3 ORDER BY 컬럼 [ASC/DESC], 컬럼 [ASC/DESC].......

ASC(오름차순) - 순서가 점점 커지는 정렬

DESC(내림차순) - 순서가 점점 작아지는 정렬

- 회사에서는 내림차순 정렬을 자주 사용한다. (1등 2등 .....)

예제 1. 사원의 이름을 급여 순으로 검색한다.

SQL> SELECT eno 사번, ename 이름, sal 급여
2 FROM emp
3 ORDER BY sal DESC;
사번 이름 급여
---- ------------ ----------
0001 안영희 4800
1001 문시현 4500
2003 정의찬 4350
.............
.............

예제 2. 사원의 사번과 이름을 연봉 순으로 검색한다.

SQL> SELECT eno 사번, ename 이름
2 FROM emp
3 ORDER BY sal*12+NVL(comm,0) DESC;
사번 이름
----- -------
0001 안영희
1001 문시현
2003 정의찬
.............
.............

SQL> SELECT eno 사번, ename 이름, sal*12+NVL(comm,0) 연봉
2 FROM emp
3 ORDER BY 연봉 DESC;
사번 이름 연봉
----- ----------- ----------
0001 안영희 57600
1001 문시현 54520
2003 정의찬 52200
.............

SQL> SELECT eno, ename, sal
2 FROM emp
3 ORDER BY eno;
=

SQL> SELECT eno, ename, sal
2 FROM emp
3 ORDER BY 1;

SQL> SELECT eno, ename, sal
2 FROM emp
3 ORDER BY ename;
=

SQL> SELECT eno, ename, sal
2 FROM emp
3 ORDER BY 2;

SQL> SELECT eno, ename, sal
2 FROM emp
3 ORDER BY sal;
=

SQL> SELECT eno, ename, sal
2 FROM emp
3 ORDER BY 3;

정렬을 이용한 묶음 검색

예제 3. 업무 별로 사원의 급여를 검색한다.

SQL> SELECT job 업무, eno 사번, ename 이름, sal 급여
2 FROM emp
3 ORDER BY 업무;
업무 사번 이름 급여
----- ------- ---------- ------

개발 2002 제갈민 1520
개발 2001 남궁연호 3950
개발 2003 정의찬 4350
.............
.............

예제 4. 각 부서별로 사원의 급여를 검색한다. 단 급여를 많이 받는 사람부터 검색한다.

SQL> SELECT dno 부서번호, eno 사번, ename 이름, sal 급여
2 FROM emp
3 ORDER BY 부서번호, 급여 DESC;
부 사번 이름 급여
-- ------ -------- ------
01 0001 안영희 4800
01 0201 안영숙 3900
01 0202 손하늘 3510
.............
.............

실습

1. 성적순으로 학생의 이름을 검색한다.

A) SELECT SNAME 이름, AVR 성적 FROM STUDENT ORDER BY 성적 DESC;

2. 학과별 성적순으로 학생의 정보를 검색한다.

A) SELECT SNO 학번, SNAME 이름, SEX 성별, SYEAR 학년, MAJOR 학과, AVR 성적

2 FROM STUDENT ORDER BY 학과,성적 DESC;

3. 학년별 성적순으로 학생의 정보를 검색한다.

A) SELECT SNO 생년월일, SNAME 이름, SEX 성별, SYEAR 학년, MAJOR 학과, AVR 성적

2 FROM STUDENT ORDER BY 학년,성적 DESC;

4. 학과별 학년별로 학생의 정보를 성적순으로 검색한다.

A) SELECT SNO 생년월일, SNAME 이름, SEX 성별, SYEAR 학년, MAJOR 학과, AVR 성적

2 FROM STUDENT ORDER BY 학과,학년,성적 DESC;

5. 학점 순으로 과목 이름을 검색한다.

A) SELECT CNAME 과목, ST_NUM 학점, FROM COURSE ORDER BY 과목,학점 DESC;

6. 각 학과별로 교수의 정보를 검색한다.

A) SELECT PNO 담당교수번호, PNAME 교수이름, SECTION 소속학과, ORDERS 직위, HIREDATE 고용날짜

2 FROM PROFESSOR ORDER BY 학과;

7. 지위별로 교수의 정보를 검색한다.

A) SELECT PNO 담당교수번호, PNAME 교수이름, SECTION 소속학과, ORDERS 직위, HIREDATE 고용날짜

2 FROM PROFESSOR ORDER BY 직위;

8. 각 학과별로 교수의 정보를 부임일자 순으로 검색한다.

A) SELECT PNO 담당교수번호, PNAME 교수이름, SECTION 소속학과, ORDERS 직위, HIREDATE 고용날짜

2 FROM PROFESSOR ORDER BY 학과,고용날짜 DESC;

9. 급여가 10% 인상된 경우 부서별로 각 사원의 연봉을 연봉순으로 검색한다.

A) SELECT ENO 사원번호, ENAME 사원이름, JOB 부서, SAL 급여, COMM 보너스, DNO 부서번호, SAL*12*1.1

2 +NVL(COMM,0) 연봉 FROM EMP ORDER BY 연봉 DESC;

10. 보너스가 100% 인상된 경우 업무별로 각 사원의 연봉을 연봉순으로 검색한다.

A) SELECT ENO 사원번호, ENAME 사원이름, JOB 부서, SAL 급여, COMM 보너스, DNO 부서번호, (SAL*12*1.1

2 +NVL(COMM,0)) 연봉, COMM*2 인상보너스 FROM EMP ORDER BY 연봉 DESC;

WHERE 절을 이용한 조건 검색

조건 검색

SQL> SELECT [DISTINCT | ALL] 컬럼, 컬럼 ...
2 FROM 테이블
3 WHERE 조건
4 ORDER BY 컬럼 [ASC/DESC], 컬럼 [ASC/DESC].......

예제 1. 다음 각 조건에 맞는 정보를 검색한다.
. 사원 중에 급여가 4000 이상인 사원의 명단
. 이름이 '문시현'인 사원의 정보
. 10번 부서 이외 부서 사원의 명단

SQL> SELECT eno 사번, ename 이름, sal 급여
2 FROM emp
3 WHERE sal >= 4000;

사번 이름 급여
------- ------- ------
0001 안영희 4800
1001 문시현 4500
1002 김주란 4100
.............
.............

SQL> SELECT * FROM emp
2 WHERE ename = '문시현';

ENO ENAME SEX JOB MGR HDATE SAL COMM DNO
-------- ---------- ---- ------ ----- ---------- ---------- ---------- ----
1001 문시현 남 모델링 0201 1991/02/01 4500 520 10

SQL> SELECT * FROM emp
2 WHERE dno != '10'; --> DNO 10은 문자타입이다. 만약 숫자10을 넣으면 DNO의 숫자와 일일이 대입해주지만 연산속 도가 느려진다.

ENO ENAME SEX JOB MGR HDATE SAL COMM DNO
-------- ---------- ---- ------ ----- ---------- ---------- ---------- ----
0001 안영희 여 경영 1991/01/01 4800 0 01
0201 안영숙 여 지원 0001 1991/02/01 3900 2200 01
0202 손하늘 여 지원 0001 1991/12/01 3510 980 01
.............
.............

예제 2. 잘못된 조건 검색

SQL> SELECT * FROM emp
2 WHERE dno = 10;
ENO ENAME SEX JOB MGR HDATE SAL COMM DNO
-------- ---------- ---- ------ ----- ---------- ---------- ---------- ----
1001 문시현 남 모델링 0201 1991/02/01 4500 520 10
2001 남궁연호 남 개발 0202 1993/12/13 3950 200 10
3001 김선유 남 분석 1001 1998/10/17 3200 300 10
.............
.............

예제 3. 연봉이 30,000 이상인 사원의 이름을 검색한다.

SQL> SELECT eno 사번, ename 이름, sal*12+nvl(comm,0) 년봉
2 FROM emp
3 WHERE sal*12+nvl(comm,0) >= 30000;
사번 이름 년봉
---- --------------- ----------
0001 안영희 57600
0201 안영숙 49000
0202 손하늘 43100
.............
.............

예제 4. 보너스가 200 이하인 사원을 검색한다.

SQL> SELECT eno 사번, ename 이름, comm 보너스
2 FROM emp
3 WHERE comm <= 200; <---- 결과가 나온 데이터가 신뢰할수 있는지 아닌지 확인

사번 이름 보너스
---- --------------- ----------
0001 안영희 0
0301 이승철 0
0302 박선경 0
.............
.............

SQL> SELECT eno 사번, ename 이름, comm 보너스
2 FROM emp
3 WHERE nvl(comm,0) <= 200; 사번 이름 보너스 <----- NULL이 들어가는 순간 이 데이터를 신뢰할수 없게된다.
---- --------------- ----------
0001 안영희 0
0301 이승철 0

0302 박선경 0
1003 양선호
2001 남궁연호 200
2003 정의찬
2008 윤고은
3002 권아현
0309 김선유 90
0702 김민지 60
10 행이 선택되었습니다.

예제 5. 입사일이 1996년 이후인 사원의 정보를 검색한다.

SQL> ALTER SESSION SET nls_date_format='YY/MM/DD';
세션이 변경되었습니다.

SQL> SELECT * FROM emp
2 WHERE hdate >= '1996/01/01';

ENO ENAME SEX JOB MGR HDATE SAL COMM DNO
-------- ---------- ---- ------ ----- -------- ---------- ---------- ----
2002 제갈민 남 개발 0202 96/04/30 1520 2000 20
3002 권아현 여 분석 1002 01/01/29 2900 20
0309 김선유 남 회계 0302 11/01/03 900 90 02
.............
............

SQL> ALTER SESSION SET nls_date_format='YYYY/MM/DD';

세션이 변경되었습니다.

SQL> SELECT * FROM emp
2 WHERE hdate >= '1996/01/01';
ENO ENAME SEX JOB MGR HDATE SAL COMM DNO
-------- ---------- ---- ------ ----- ---------- ---------- ---------- ----
2002 제갈민 남 개발 0202 1996/04/30 1520 2000 20
3002 권아현 여 분석 1002 2001/01/29 2900 20
0309 김선유 남 회계 0302 2011/01/03 900 90 02

예제 6. 보너스 컬럼이 널인 사원를 검색한다.

SQL> SELECT * FROM emp
2 WHERE comm IS NULL; // comm = NULL 절대 안됨!!!

ENO ENAME SEX JOB MGR HDATE SAL COMM DNO
-------- ---------- ---- ------ ----- ---------- ---------- ---------- ----
1003 양선호 남 모델링 0201 1995/02/21 4300 30
2003 정의찬 남 개발 0202 1992/03/03 4350 30
2008 윤고은 여 개발 0001 1992/03/03 2100 40
3002 권아현 여 분석 1002 2001/01/29 2900 20

널과 비교 연산

- 널은 결정된 값이 아님으로 '=', '<' 등의 비교 연산자를 사용할 수 없다.
- 널에 대한 직접적인 검색은 다음과 같이 특별한 연산자를 이용한다.

실습

1. 화학과 학생을 검색한다.

A) SELECT * FROM STUDENT WHERE MAJOR = '학과';

2. 평점이 2.0 미만인 학생을 검색한다.

A) SELECT * FROM STUDENT WHERE AVR > 2.0;

3. 권현 학생의 평점을 검색한다.

A) SELECT AVR 평점, SNAME 이름 FROM STUDENT WHERE SNAME = '권현';

4. 정교수의 명단을 검색한다.

A) SELECT * FROM PROFESSOR WHERE ORDERS = '정교수';

5. 화학과 소속 교수의 명단을 검색한다.

A) SELECT * FROM PROFESSOR WHERE SECTION = '화학';

6. 송강 교수의 정보를 검색한다.

A) SELECT * FROM PROFESSOR WHERE PNAME = '송강';

7. 학년별로 화학과 학생의 성적을 검색한다.

A) SELECT SNO 학번, SNAME 이름, SEX 성별, SYEAR 학년, MAJOR 전공, AVR 성적

2 FROM STUDENT WHERE MAJOR = '화학' ORDER BY 학년,성적;

8. 2000년 이전에 부임한 교수의 정보를 부임일순으로 검색한다.

A) SELECT * FROM PROFESSOR WHERE HIREDATE < '2000/01/01' ORDER BY HIREDATE;

9. 담당 교수가 없는 과목의 정보를 검색한다.

A) SELECT * FROM COURSE WHERE PNO IS NULL;

10. 보너스가 급여의 10% 이상인 사원을 검색한다.

A) SELECT * FROM EMP WHERE COMM >= SAL*1.1;

6. 관계 연산자와 LIKE 연산자

관계 연산자

SQL> SELECT [DISTINCT | ALL] 컬럼, 컬럼 ...
2 FROM 테이블
3 WHERE 조건 [관계 연산자 조건 ...]
4 ORDER BY 컬럼 [ASC/DESC], 컬럼 [ASC/DESC].......

예제 1. 다음 각 조건에 맞는 데이터를 검색한다.
. 20번 부서 사원 중에 급여가 2000 이상인 사원을 검색한다.
. 20번 부서 사원 중에 급여가 2000 이상이고 모델링 업무를 담당하는 사원을 검색한다.

SQL> SELECT * FROM emp
2 WHERE dno = '20'
3 AND sal >= 2000;
ENO ENAME SE JOB MGR HDATE SAL COMM DN
---- ---------- -- -------- ---- -------- ---------- ---------- --
1002 김주란 여 모델링 0201 92/03/03 4100 330 20
3002 권아현 여 분석 1002 01/01/29 2900 20
0120 김경현 남 지원 1002 99/09/05 4000 2500 20

SQL> SELECT * FROM emp
2 WHERE dno = '20' 3 AND sal >= 2000
4 AND job = '모델링';
ENO ENAME SE JOB MGR HDATE SAL COMM DN
---- ---------- -- -------- ---- -------- ---------- ---------- --
1002 김주란 여 모델링 0201 92/03/03 4100 330 20

예제 2. 다음 두 문장을 실행하고 결과를 해석해 보자.

SQL> SELECT * FROM emp
2 WHERE dno = 10 OR sal > 1600 AND comm > 600;
ENO ENAME SE JOB MGR HDATE SAL COMM DN
---- ---------- -- -------- ---- -------- ---------- ---------- --
0201 안영숙 여 지원 0001 91/02/01 3900 2200 01
0202 손하늘 여 지원 0001 91/12/01 3510 980 01
1001 문시현 남 모델링 0201 91/02/01 4500 520 10
.............

............

SQL> SELECT * FROM emp
2 WHERE (dno = 10 OR sal > 1600) AND comm > 600;
ENO ENAME SE JOB MGR HDATE SAL COMM DN
---- ---------- -- -------- ---- -------- ---------- ---------- --
0201 안영숙 여 지원 0001 91/02/01 3900 2200 01
0202 손하늘 여 지원 0001 91/12/01 3510 980 01
2007 이초록 남 개발 0001 92/09/05 1989 2300 30
.............
............

LIKE 연산자

SQL> SELECT [DISTINCT | ALL] 컬럼, 컬럼 ...
2 FROM 테이블
3 WHERE 컬럼 LIKE '비교 문자열'
4 ORDER BY 컬럼 [ASC/DESC], 컬럼 [ASC/DESC].......;

예 설명 검색 대상
'경%' '경'으로 시작하는 모든 문자열 경, 경제, 경범죄, 경영학과 ...
'%과' '과'로 끝나는 모든 문자열 과, 다과, 화학과, 물리학과 ...
'%김%' '김'이란 글자가 들어간 모든 문자열 김, 김씨, 돌김, 되새김질 ...
'화_' '화'로 시작하는 두글자 단어 화학, 화약, 화상 ...
'__화' '화'로 끝나는 세 글자 단어 무궁화, 해당화, 운동화 ...
'_동_' '동'이 가운데 들어간 세글자 단어 원동기, 전동차 ...
'%이#%%' ESCAPE '#' 으로 지정한 경우, '이%' 가 포함된 문자열
'_#%_' ESCAPE '#' 으로 지정한 경우, '%'가 포함된 세 글자 단어

예제 3. 다음 조건에 맞는 사원을 검색한다.

- 김씨 성을 가진 사원
- 이름이 '하늘'인 사원
- 성과 이름이 각각 한 글자인 사원을 검색한다.

SQL> SELECT * FROM emp
2 WHERE ename LIKE '김%';
ENO ENAME SE JOB MGR HDATE SAL COMM DN
---- ---------- -- -------- ---- -------- ---------- ---------- --
1002 김주란 여 모델링 0201 92/03/03 4100 330 20
0309 김선유 남 회계 0302 11/01/03 900 90 02

3001 김선유 남 분석 1001 98/10/17 3200 300 10
.............
............

SQL> SELECT * FROM emp
2 WHERE ename LIKE '%하늘';
ENO ENAME SE JOB MGR HDATE SAL COMM DN
---- ---------- -- -------- ---- -------- ---------- ---------- --
0202 손하늘 여 지원 0001 91/02/01 3510 980 01

SQL> SELECT * FROM emp
2 WHERE ename LIKE '__';
선택된 레코드가 없습니다. 예제 4. 이름에 '%' 문자가 포함된 사원을 검색한다. SQL> SELECT eno, ename
2 FROM emp
3 WHERE ename LIKE '%#%%' ESCAPE '#'; 선택된 레코드가 없습니다.

예제 4. 이름에 '%' 문자가 포함된 사원을 검색한다.

SQL> SELECT eno, ename
2 FROM emp
3 WHERE ename LIKE '%#%%' ESCAPE '#';

선택된 레코드가 없습니다.

실습

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

A) SELECT SNAME 이름, MAJOR 학과, FROM STUDENT WHERE MAJOR = '화학' OR MAJOR = '물리';

2. 화학과가 아닌 학생 중에 1학년 학생을 검색한다.

A) SELECT SNAME 이름, SYEAR 학년, MAJOR 학과 FROM STUDENT WHERE MAJOR != '화학' AND SYEAR = 1;

3. 화학과 3학년 학생을 검색한다.

A) SELECT SNAME 이름, SYEAR 학년, MAJOR 학과 FROM STUDENT WHERE MAJOR = '화학' AND SYEAR = 3;

4. 평점이 2.0에서 3.0 사이인 학생 검색한다.

A) SELECT SNAME 이름, SYEAR 학년, MAJOR 학과, AVR 평점 FROM STUDENT WHERE AVR > 2.0 AND AVR < 3.0;

5. 교수가 지정되지 않은 과목 중에 학점이 3학점인 과목을 검색한다.

A) SELECT * FROM COURSE WHERE CNAME != 'PNO' AND ST_NUM = '3';

6. 화학 관련된 과목 중 2학점 이하인 과목을 검색한다.(화학 관련 과목은 과목명에 화학이 들어간 과목을 의미한다.)

A) SELECT * FROM COURSE

2 WHERE CNAME LIKE '화학%' OR CNAME LIKE '%화학%' OR CNAME LIKE '%화학' AND ST_NUM <= '2';

7. 화학과 정교수를 검색한다.

A) SELECT * FROM PROFESSOR WHERE SECTION = '화학' AND ORDERS = '정교수';

8. 화학과 학생 중에 성이 권씨인 학생을 검색한다.

A) SELECT * FROM STUDENT WHERE MAJOR = '화학' AND SNAME LIKE '권%';

9. 부임일이 1995년 이전인 정교수를 검색한다.

A) SELECT * FROM PROFESSOR WHERRE HIREDATE <= '1999/12/31' AND ORDERS = '정교수';

10. 성과 이름이 각각 한글자인 교수를 검색한다.

A) SELECT * FROM PROFESSOR WHERE PNAME LIKE '__';