[Oracle] SELECT 활용 - 1

DB 내의 더비 테이블 - DUAL

현재 날짜 출력 - SELECT sysdate FROM dual;

컬럼을 이용한 수식을 검색

SELECT [수식] FROM 테이블명; //SELECT문에는 FROM 절을 생략 할 수 없다.
ex) 

SELECT (2+3)*6 FROM dual;

SELECT eno (AS) 사번, ename 이름, sal*12 (AS) 년간_급여 FROM emp;
수식을 검색하는 경우 헤더에 수식이 출력됨으로 이런 경우 가급적 별명을 출력하면 가
독성이 높아진다.
헤더는 응용 프로그램 개발시에 변수명이나 배열의 키로 이용됨으로 프로그램 개발시
에는 이를 고려한다

별명을 이용해서 검색 - AS

SELECT 컬럼명 AS "별명"..... FROM 테이블명;

AS는 생략가능
별명은 헤더의 출력을 조정
" "가 필요한 경우 - 공백 포함 / 특수문자 포함 / 대소문자 구분

ex)

SELECT eno AS "사원 번호", ename AS "이름" FROM emp;
SELECT dno (AS) 부서_번호, dname (AS) 부서명 FROM dept;
SELECT eno (AS) 사번, sal*1.1 (AS) "10%인상급여" FROM emp;

NVL() 함수

SELECT eno, ename, sal, comm, sal*12+comm FROM emp; - NULL 값이 나올수 있는 연산

컬럼의 값이 NULL이면 치환값으로 바꾼다.
‐>  SELECT eno, ename, sal, comm, sal*12+ NVL(comm,0) FROM emp;

※ 데이터베이스에 NULL값이 존재한다는건 해당 DB는 무결성이 깨진 DB이므로 NULL값이 나오지 않게 제약조건을 추가하는것이 중요하다.

예제

1. 각 학생의 평균 평점을 검색한다.(별명을 이용)
SELECT sno 학생_번호, sname 학생_이름, avr 평균_평점 FROM student;

2. 각 과목의 학점수는 검색한다.(별명을 이용)
SELECT cno 과목_점수, cname 과목명, st_num 학점수 FROM course;

3. 각 교수의 지위를 검색한다.(별명을 이용)
SELECT pno 교수_번호, pname 교수_이름, orders 직위 FROM professor;

4. 급여를 10% 인상했을 때 연간 지급되는 급여를 검색한다.
SELECT eno 사번, ename 이름, sal*1.1*12 연상_연급여 FROM emp;

5. 현재 학생의 평균 평점은 4.0 만점이다. 이를 4.5 만점으로 환산해서 검색한다.
SELECT sno 학생_번호, sname 학생_이름, avr 기존_평점,avr*1.125 바뀐_평점 FROM student;

6. 급여가 10% 인상될 경우 각 사원의 연봉을 검색한다. 
SELECT eno 사번, ename 이름, sal*1.1*12+comm 연상_연봉 FROM emp;
SELECT eno 사번, ename 이름, sal*1.1*12+NVL(comm,0) 연상_연봉 FROM emp;

7. 1년 동안 지급되는 급여와 10% 인상되어 1년 동안 지급되는 급여 간에 차액을 검색한다.
SELECT eno 사번, ename 이름, sal*12+NVL(comm,0) 기존_연봉,sal*1.1*12+NVL(comm,0) 연상_연봉,
(sal*1.1*12+NVL(comm,0))-(sal*12+NVL(comm,0)) 차액
FROM emp;

8. 각 학생들의 평균평점을 4.5 만점으로 환산한 경우 각각 평점의 상승폭은 얼마인지 검색한다
SELECT sno 학생_번호, sname 학생_이름, avr 기존_평점,avr*1.125 바뀐_평점,
(avr*1.125) - avr 상승폭
FROM student;

SET

• SET LINE [ESIZE] ##
  - 한 라인에 출력할 문자의 수
• SET PAGES[IZE] ##
  - 한 페이지에 출력할 라인의 수 -ROW의 개수가 아님
  - 페이지마다 헤더가 출력된다.

컬럼 형식 지정 (COLUMN|COL)

• COL[UMN] 컬럼 FORMAT [A## | 숫자형식]
  - 컬럼의 출력 형식을 지정한다
  - 문자 컬럼 : A##로 출력 너비를 지정한다.
  - 숫자 컬럼 : 0,9를 사용해서 출력 형식을 지정한다.
  - 문자 컬럼은 출력 너비를 지정하는 거지만, 숫자 컬럼은 형식을 지정
• COL [UMN] 컬럼 CLEAR
  - 컬럼의 출력 형식을 삭제한다.

스크립트 파일 설정

ORACLE_HOME_DIR\sqlplus\admin\glogin.sql

연결 연산자 ( || )

SELECT [컬럼 | '리터럴' ] ||, ... FROM 테이블;

• 연결 연산자는 리터럴이나 컬럼을 하나의 문자열로 검색
• 리터럴이란? SQL문에 쓰인 문자, 숫자, 날짜를 의미
  - 리터럴은 숫자를 제외하고 반드시 단일 인용부호( ' ' )를 사용

ex)

SELECT ename||sal 이름_급여 FROM emp;

SELECT ename||' '||sal 이름_급여 FROM emp;

SELECT ename||'의 업무는 '||job||'입니다.' FROM emp;


주의 해야할 연결 연산자 
SELECT ename||' '||sal+100 이름_급여 FROM emp;

SELECT eno||' '||ename ||' 님의 년봉은 '||((sal*12)+nvl(comm,0))||'입니다.'
FROM emp;

주의 : 연산 우선순위
*, / > || > +,- > ......

1. '____학과인 ____학생의 현재 평점은 __입니다.' 형태로 학생의 정보를 출력한다.
SELECT major ||'학과인 '||sname||'학생의 현재 평점은 '||avr||'입니다.' FROM student;

2. '____과목은 ____학점 과목입니다.' 형태로 과목의 정보를 출력한다. 
SELECT major ||'학과인 '||sname||'학생의 현재 평점은 '||avr||'입니다.' FROM student;

3. '____교수는 ____학과 소속입니다.' 형태로 교수의 정보를 출력한다.
SELECT pname||'교수는 '||section||'학과 소속입니다.' FROM professor;

4. 학교에는 어떤 학과가 있는지 검색한다.(학생 테이블 기반으로 검색한다.)
SELECT DISTINCT major FROM student;

5. 학교에는 어떤 학과가 있는지 검색한다.(교수 테이블 기반으로 검색한다.)
SELECT DISTINCT major FROM section;

6. 교수의 지위는 어떤 것들이 있는지 검색한다.
SELECT DISTINCT orders FROM section;

7. '____학생의 4.5 환산 평점은 ____입니다.' 형태로 학생의 환산 평점을 출력한다.
SELECT sname||'학생의 4.5 환산 평점은'||avr*1.125||'입니다.' FROM student;

8. '____과목의 담당 교수 번호는 ____입니다.' 형태로 과목의 정보를 출력한다.
SELECT cname||'과목의 담당 교수 번호는 '||pno||'입니다.' FROM course;

9. 학교에 개설된 과목들은 몇 학점인지 검색한다.
SELECT DISTINCT st_num FROM course;

10. 학생들이 수강중인 과목의 과목번호를 검색한다.
SELECT DISTINCT cno FROM score;

 

정렬 검색

SELECT * | [DISTINCT] 컬럼|수식 [(AS) 별명], ...
FROM 테이블
ORDER BY 컬럼 [ASC | DESC], 컬럼[ASC | DESC], .... ;
ORDER BY : 지정된 컬럼을 대상으로 정렬 수행
ASC : 오름차순 (default) DESC : 내림차순
정렬대상인 컬럼이 여러 개인 경우 앞에 지정한 컬럼이 동일 한 값에 대해서 뒤에 지정된 컬럼으로 정렬된다
‐ 정렬하지 않는 SELECT문의 출력 순서는 의미가 없다.
‐ 정렬 대상으로는 컬럼 이외에 다음의 것들이 사용 가능하다.
．별명, 수식, 검색 항목 순서

SELECT eno 사번, ename 이름, sal 급여
FROM emp
ORDER BY sal DESC;

SELECT eno 사번, ename 이름, sal 급여
FROM emp
ORDER BY 급여 DESC;

SELECT eno 사번, ename 이름, sal 급여
FROM emp
ORDER BY 3 DESC; --index를 통한 정렬 방법 (가급적 지향)

SELECT eno 사번, ename 이름
FROM emp
ORDER BY sal*12+nvl(comm,0) DESC --SQL문은 의미가 있지만 검색 결과는 의미를 알기 어렵다.
-- sal*12+nvl(comm,0)를 검색하거나 적당한 별명을 추가한다.

SELECT eno 사번, ename 이름, sal 급여, comm 보너스
FROM emp
ORDER BY sal DESC, comm DESC; -- 여러 컬럼으로 정렬하는 것은 순서가 중요하다

SELECT job 업무, eno 사번, ename 이름, sal 급여
FROM emp
ORDER BY 업무; -- 업무별로 사원의 급여를 검색한다.

SELECT dno 부서번호, eno 사번, ename 이름, sal 급여
FROM emp
ORDER BY 부서번호, sal DESC; --부서별로 사원의 급여를 검색한다

SELECT dno 부서번호, eno 사번, ename 이름, sal 급여
FROM emp
ORDER BY sal, dno; --결과에 ORDER BY절의 의미가 없음.

카디널리티(Cardinality) - 중복 정도
성별(남|여) : 카디널리티가 낮다.
이름(OOOO) : 카디널리티가 높다.

1. 성적순으로 학생의 이름을 검색한다.
SELECT sno, sname, avr
FROM student
ORDER BY avr DESC;

2. 학과별 성적순으로 학생의 정보를 검색한다.
SELECT *
FROM student
ORDER BY major, avr DESC;

3. 학년별 성적순으로 학생의 정보를 검색한다.
SELECT *
FROM student
ORDER BY syear DESC, avr DESC;

4. 학과별 학년별로 학생의 정보를 성적순으로 검색한다.
SELECT *
FROM student
ORDER BY major, syear DESC, avr DESC;

5. 학점 순으로 과목 이름을 검색한다.
SELECT cno, cname, st_num
FROM course
ORDER BY st_num DESC;

6. 각 학과별로 교수의 정보를 검색한다.
SELECT *
FROM professor
ORDER BY section;

7. 지위별로 교수의 정보를 검색한다.
SELECT *
FROM professor
ORDER BY orders DESC;

8. 각 학과별로 교수의 정보를 부임일자 순으로 검색한다.
SELECT *
FROM professor
ORDER BY section, hiredate;

9. 급여가 10% 인상된 경우 부서별로 각 사원의 연봉을 연봉순으로 검색한다.
SELECT eno, ename, sal*1.1, NVL(comm,0), ((sal*1.1)+NVL(comm,0)) 연봉, dno
FROM emp 
ORDER BY dno, 연봉 DESC;


10. 보너스가 100% 인상된 경우 업무별로 각 사원의 연봉을 연봉순으로 검색한다.
SELECT eno, ename, sal, NVL(comm*2,0), (sal+NVL(comm*2,0)) 연봉, dno
FROM emp
ORDER BY dno, 연봉 DESC;