SQL 정리_2

SQL 사용법에 대해 알아 보자

3. 연결 연산자와 중복 제거

> 연결 연산자의 사용

 SQL> SELECT 컬럼 || '리터럴' || .......

    2     FROM 테이블;

 * 연결 연산자(||)는 리터럴이나 컬럼을 하나의 문자열로 검색

 * 리터럴이란 SQL문에 쓰인 문자, 숫자, 날짜를 의미

  - 리터럴은 숫자를 제외하고 반드시 단일 인용부호를 사용

 * 연결 연산자는 SELECT 문을 이용 다양한 문장을 만들 때 사용

  연결 연산자는 사칙 연산자와 같이 일반 연산자로 우선순위에 영향을 받음. 

-예제1 다양한 방법으로 사원의 이름을 급여 또는 업무와 함께 검색

SQL> SELECT ename || sal 이름_급여 ( 별명에 공백이 있을 경우 _(밑줄)을 이용하면 인용부호 생략 가능)

  2      FROM emp;

SQL> SELECT ename||' '||sal 이름_급여 

   2      FROM emp;

띄어 쓰기가 되어진 것을 확인 가능

SQL> SELECT ename||'의 업무는 '||job 

  2      FROM emp;

연결 연산자를 통해 여러 컬럼을 하나의 컬럼으로 출력 가능 하며, 중간에 문자열 삽입하여 문장 만들기 가능, 문자열을 삽입시 단일 인용부호(')를 이용하며, 연결 연산자는 문자열을 대상으로하기 때문에 문자형이 아닌 형을 모두 문자로 자동 변환

 ▲패치(fatch)

  - SELECT 문을 이용한 검색 결과를 응용 프로그램의 변수로 가져가는 일련의 과정을 의미, 보통은 컬럼명을 킷값으로 하는 배열의 요소에 각 컬럼의 값을 패치하거나 컬럼명과 동일하거나 컬럼명을 포함한 변수명을 갖는 변수에 컬럼 단위로 패치

-예제2 주의해야 할 연결 연산자 이용

SQL> SELECT ename||' '||sal+100

  2      FROM emp;

 연결 연산자의 우선순위가 사칙연산연산자('+')보다 높아서 연결 연산자를 통해 문자형으로 변환 된 값에 100을 더하려고 하기에 오류가 나는 것, 그래서 괄호를 해주어 해결

 ▲스풀링

  - 본문에서 사용하는 터미널 기반의 sqlplus는 과거 도스키와 같은 히스토리 기능이 제공, 그러나 대부분 오라클 툴은 이런 기능을 제공하지 않음, 이것 때문에 매우 긴 SQL문을 반복적으로 사용하려면 계속해서 동일한 SQL문장을 입력해야 하는 불편함이 따름, 이런 불편한 환경을 조금이나마 해결해주는 기능이 이전에 수행한 문장을 저장했다가 재사용하는 스풀링 기능임. 저장하고자 하는 SQL 명령을 수행한 다음 'save 파일명.sql'을 입력하면 방금 실행한 문장이 파일명.sql로 저장됨 나중에 저장된 SQL문을 실행하려면 '@파일명.sql'으로 실행함, @는 SQL 스크립트 파일을 호출하는 예약어

> 중복제거

SQL> SELECT [DISTINCT | ALL] 컬럼, 컬럼, ...

  2      FROM 테이블;

 * DISTINCT : 중복된 출력 값은 한번만 출력함

 * ALL : 중복된 값을 모두 출력함(기본 값)

SELECT 절에는 DISTINCT나 ALL을 기술하지 않기 때문에 이들 옵션이 부가적인 걸로 생각하기 쉽지만 사실은 SELECT문에 기본적으로 포함된 구문, 아무것도 정의 없을 땐 ALL

검색 대상이 되는 행을 기준으로 중복된 내용을 모두 검색하는 경우는 ALL, 중복된 내용을 한번만 출력하는 경우 DISTINCT를 씀

-예제3 직원들의 업무는 어떤 것이 있는지 검색(업무의 종류를 검색)

SQL>SELECT job 업무 FROM emp;

SQL>SELECT DISTINCT job 업무 FROM emp;

DISTINCT는 중복을 제거하여 출력을 해주지만 사용시 주의가 필요한 명령어

오라클과 같은 RDBMS가 중복된 결과를 걸러내기 위한 방법 중에 가장 널리 알려진 방법은 결과를 정렬 후 동일한 값들은 하나로 합치는 것 그러나 이러한 정렬 작업은 RDBMS에서 성능을 저해시키는 요인으로 알려진 것 중 하나

아예 사용을 하지 않을 수 없지만, 사용에 주의가 필요한 명령어임

<문제>

1. '____학생의 4.5 환산 평점은 ____' 형태로 학생의 환산 평점 출력

SQL> SELECT sname|| '학생의 4.5 환산 평점은 ' || avr*(1.125) FROM student;

2. '___과목의 담당 교수 번호는 ___' 형태로 과목의 정보 출력

SQL>SELECT section|| '과목의 담당 교수 번호는 ' || pno FROM professor;

3. 학교에 개설된 과목들은 몇 학점짜리 인지 검색

SQL>SELECT cname || '과목은 ' || st_num || '학점' FROM course;

4. 학생들이 수강 중인 과목의 과목번호 검색

SQL>SELECT cno || '과목 번호는 '|| cname || '과목임' FROM course;

> 정렬된 데이터 검색

SQL> SELECT [DISTINCT | ALL] 컬럼, 컬럼...

  2      FROM 테이블

  3      ORDER BY 컬럼 [ASC/DESC], 컬럼 [ASC/DESC] ...

 * ORDER BY : 지정된 컬럼을 대상으로 정렬

 * ASC는 오름차순 정렬, DESC는 내림차순 정렬 (지정하지 않으면 ASC가 기본 값)

 * 정렬 대상인 컬럼이 여러 개인 경우 앞에 지정한 컬럼이 동일한 값에 대해서 뒤에 지정된 컬럼으로 정렬 즉, 앞쪽 컬럼의 정렬이 깨지지 않는 범위 내에서 다음 컬럼으로 정렬 수행

 * 정렬하지 않는 SELECT 문의 출력 순서는 의미 없음

 * 정렬 대상으로는 컬럼 이외에 다음의 것들이 사용 가능

  - 별명, 수식, 검색 항목의 순서 ( 별로 좋은 방법이 아님 )

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

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

입력 순서와 검색 결과

 - 오라클과 같은 RDBMS(Relational Database Management System)는 데이터 독립성이 무엇보다 중요 데이터는 작업이나 응용 프로그램 심지어 해당 업무 프로세스에 대해서도 독립적으로 구성되고 운영 그러므로 데이터베이스의 저장 구조는 데이터의 입력이나 검색 순서와는 무관하게 설계. 일반적인 운영 환경에서 데이터의 검색은 정렬을 수행하지 않고서는 절대 순서를 보장하지 않음. 그러나 경험이 많은 DBA들을 SQL의 몇 가지 트릭을 이용, 데이터를 정렬한 다음 저장해서 검색의 효율성을 높이는 방법을 사용, 이들 대부분은 DDL 문장을 이용하는 것으로 테이블을 생성하고 관리하는 부분에서 자세히 설명함, SQL 구문을 배우는 현 단계에서 '명시적으로 정렬하지 않은 검색 결과는 순서를 보장하지 않음' 여기서 명시적인 정렬이란 'DISTINCT', 'ORDER BY' 이외에 뒤에서 배울 'GROUP BY', 'UNION'이나 인덱스를 이용한 검색 등 여러 방법이 있음. 비록 DISTINCT나 GROUP BY 등이 최신의 RDBMS에서 정렬보다는 해시를 이용하지만 일반적으로 정렬이라고 널리 알려져 있음으로 명시적 정렬에 포함됨

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

SQL> SELECT eno, ename FROM emp ORDER BY sal*12+NVL(comm,0) DESC;

SQL> SELECT eno, ename, sal*12+NVL(comm,0) FROM emp ORDER BY sal*12+NVL(comm,0) DESC;

 정렬 대상에는 컬럼 뿐만 아니라 컬럼을 이용한 수식이나 별명을 지정하는 것도 가능

> 정렬을 이용한 묶음 검색

- 정렬의 용도는 결과를 순서화 하는 것 뿐 아니라 동일한 값을 묶어서 검색하는 경우에도 사용, 검색 결과 중에 특정한 컬럼의 값이 같은 값끼리 모아서 검색하고 싶다면 어떻게 할까? 이때도 정렬을 이용

-예제3 업무 별로 사원의 급여를 검색

SQL> SELECT job, eno, ename, sal

  2      FROM emp

  3      ORDER BY job;

 job 컬럼은 중복된 값이 많은 컬럼인데, 중복된 값이 많은 컬럼으로 정렬하면 중복된 값들은 정렬할 수 없음으로 묶여서 출력, 결국 검색 결과는 업무별로 같은 값인 동일 업무를 수행하는 사람끼리 검색 됨

단순히 동일한 값을 갖는 컬럼을 묶어서 검색한다면 ORDER BY 절을 이용하면 쉬움

검색의 목표가 ~별로 검색하는 것 일 때 SQL 문은 크게 2가지로 나뉘는 데 그 중 하나가 ORDER BY 절을 이용하는 것이고, 다른 하나는 그룹 함수를 이용하는 것, 단순히 검색 결과를 정렬 하는 것이라면 ORDER BY 절, 검색결과가 통계 정보로 평균, 종합, 표준편차, 전체 개수 등이라면 그룹 함수를 이용함

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

SQL>SELECT dno, eno, ename, sal FROM emp ORDER BY dno, sal DESC;

 두 개의 항목으로 정렬을 수행, 먼저 dno 컬럼으로 정렬 (ASC) 수행, sal에 대해서 내림차순으로(DESC) 정렬

<문제>
1. 각 부서 별로 사원의 연봉을 검색(단, 연봉이 높은 순으로 검색)
SELECT * FROM emp ORDER BY sal DESC;

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

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

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

5. 4.5점으로 환산된 평점을 이용 학과별로 학생의 정보를 성적순으로 검색
SELECT sname, major, syear, (avr*(1.125)) FROM student ORDER BY (avr*(1.125));