DBMS(오라클) : ORDER BY문, SQL-PLUS

* ORDER BY 문

=> 오름차순(ASC)가 기본값

=> SELECT LIST에 없는 열로도 정렬은 가능

SELECT 문에 없는 comm 열로 정렬 가능

 

# 문제 1: 부서 10과 30에 속하는 모든 사원의 이름과 부서번호를 이름의 알파벳 순으로 정렬되도록 질의문을 형성하라.

 

문제 1_답안

 

# 문제 2 : 1982년에 입사한 모든 사원의 이름과 입사일을 구하는 질의문은?

 

문제 2_답안

 

# 문제 3 : 보너스를 받는 모든 사원에 대해서 이름, 급여 그리고 보너스를 출력하는 질의문을 형성하라. 단, 급여와 보너스에 대해서 내림차순 정렬할 것

문제 3_답안

# 문제 4 : 보너스가 급여의 20% 이상이고 부서번호가 30인 모든 사원에 대해서 이름, 급여 그리고 보너스를 출력하는 질의문을 형성하라.

문제 4_답안

 

---

* SQL-PLUS

=> DB 서버는 죽으면 절대 안됨. (모든 업무가 중지되기 때문) (은행은 야간에 서버를 백업해둠)

=> DB 서버에 DBMS를 설치하면 좋지 않을 확률이 높기 때문에 cmd로 SQL을 다룰 수 있는 SQL-PLUS를 사용

=> 개발자들은 'orange', 'torr', 'sql developer'를 사용하지만 엔지니어들은 'sql-plus'를 사용

 

* SQL-PLUS 사용 환경 명령어

SET HEADING OFF : 열이름 출력 X

 

ed로 editor 열기 (메모장 형식, 닫으면 cmd 명령 종료됨)

 

/ : 조회

 

* AUTO-COMMIT

기존 테이블

 

=> SET  AUTO-COMMIT ON 후 INSERT

COMMIT한 결과 확인 (7행 -> 8행)

=> SET  AUTO-COMMIT OFF 후 INSERT, COMMIT

COMMIT한 결과 확인 (8행 -> 9행)

* SET FEEDBACK (숫자)

=> SELECT 명령문의 실행 결과를 표시하기 위하여 출력 행의 수를 지정하는 변수

=> 기본값은 6

* SET LINESIZE (숫자)

=> 한 화면에서 표시되는 SQL 명령문의 출력 결과에 대한 행의 크기를 설정하는 변수

=> 기본값은 80

 

* SET PAGESIZE (숫자)

=> 한 화면에서 표시되는 SQL 명령문의 실행 결과에 대한 페이지의 크기를 설정하는 변수

=> 기본값은 14

 

* SET PAUSE ON/OFF

=> SQL 명령문의 실행 결과를 한 화면에서 보기 힘든 경우, 한 페이지씩 나누어 출력하기 위한 변수

Enter 입력 후, 테이블 결과 출력

* TIME 변수 

=> SQL 프롬프트 앞에 시스템의 현재 시간을 함께 표시하도록 설정하는 변수

 

* TIMING

=> SQL 명령문을 실행하는데 소요된 시간을 출력하기 위한 변수

=> ‘시:분:초.밀리초’ 형식

 

실행 후, 명령문을 실행하는데 소요된 시간이  출력됨.

 

 

 

* COLUMN 명령어

=> SQL 명령문의 실행 결과로 출력되는 칼럼 제목이나 칼럼 데이터에 대한 출력 형식을 다양하게 지정하기 위한 명령어

 

=> An : 문자형식 칼럼의 출력 크기를 n폭으로 설정 

기존의 테이블 출력 형태

'format a20' 지정 후 테이블 출력 형태

 

# 예제 : 교수 테이블의 sal칼럼 데이터를 숫자 세자리마다 ‘,’로 구분하여 출력하여라. 단, sal칼럼의 크기는 7로 하고 출력크기보다 데이터가 작은 경우에는 0을 추가한다.

출력 결과

기존 출력 결과

 

 

* 특정 칼럼에 대한 설정 값 출력 및 해제

* col 명령어 : 모든 칼럼에 대한 설정 값 출력

---

* SQL-PLUS 편집 명령어

 

* LIST 명령어

=> 버퍼에 저장된 SQL 명령문을 출력하기 위해 사용하는 명령어

=> 특정 라인 또는 특정 범위, 전체 범위의 라인에 포함된 명령문을 확인 또는 편집 시 특정 라인으로 이동하기 위해 사용

 

* ‘RUN’ 명령어 :  SQL 명령문과 실행결과 출력

*  ‘/ ‘ : 실행 결과만 출력

 

=> 세 줄로 쿼리 입력 후 'L (번호)' 로 줄 조회

 

 

* n : 현재 행으로 만들 행을 지정

* n text : n 행을 text로 바꿈

1행인 ' * SELECT *'을 'SELECT NAME, STUDNO'으로 교체

* DEL :  SQL 버퍼에 저장된 SQL 명령문 중에서 일부 내용을 삭제하기 위한 명령어

=> DEL : 현재 행을 삭제합니다

=> DEL n : n번째 행을 삭제

=> DEL m n : m ~ n 번째까지 삭제

 

L과 DEL로 쿼리문 조회, 삭제

 

* INPUT : SQL 버퍼에 저장된 SQL 명령문의 현재 라인 다음에 새로운 라인을 추가하는 경우에 사용

=> I[NPUT] : 행 수에 대한 제한 없이 삽입

=> I[NPUT] text : text를 구성하는 한 행을 삽입

=> 0 text : 1행 앞에 한 행을 삽입

 

'ed'로 에디터(메모장) 연 다음 쿼리 추가 후, 'I'와 'DEL'로 쿼리 변경

 

* A(PPEND) TEXT : Text를 현재 행의 끝에 추가합니다.

3번 째 행에 쿼리 추가

 

* CHANGE 명령어 : 현재 활성화된 라인에서 특정 텍스트를 다른 텍스트로 변경하기 위해 사용하는 명령

=> C(HANGE) /old/new : old(변경하기 전의 텍스트), new(변경할 텍스트)

행 선택 후 해당 행의 쿼리 변경, 삭제

 

* CL[EAR] BUFF[ER] : buffer 소거

 

---

* SQL-PLUS 파일조작 명령어

=> SQL 버퍼에 저장된 SQL 명령문을 파일에 저장하거나 파일에 저장된 SQL 명령문을 읽어올 수 있도록 하는 명령어

 

* SAV[E] filename [REP[LACE]] APP[END]]

=> SQL 버퍼의 현재 내용을 파일에 저장

=> APPEND : 기존 파일에 추가

=> REPLACE : 기존 파일에 겹쳐씀

=> 파일 이름의 기본 확장자는 .sql

sql 파일 생성 후 저장

생성된 test 파일에 저장된 쿼리문 (repl로 인해 같은 쿼리문이 추가 저장됨)

 

* GET filename : 이전에 저장한 파일 내용을 SQL버퍼에 씀.

 

버퍼 초기화

get으로 sql 파일 불러오기

 

* SPO[OL] [filename | OFF | OUT] : 질의 결과를 파일에 저장

=> OFF : 스풀 파일을 닫음

=> OUT : 스풀 파일을 닫은 후 파일 결과를 시스템 프린터로 전송

=> 확장명은 .LST

 

SPOOL 결과 확인

* STA[RT] filename (= @filename) : 스크립트 실행 (이전에 저장한 명령 파일을 실행)

 

 

* host : 운영체제로 이동

* exit : sql로 이동

---

* SQL 함수

=> 단일행 함수 : 데이터 값 조작에 주로 사용. 행별로 함수를 적용하여 하나의 결과를 반환하는 함수.

=> 복수행 함수 : 조건에 따라 여러 행을 그룹화하여 그룹별로 결과를 하나씩 반환하는 함수.

 

* 문자 함수 : 문자 데이터를 입력하여 문자나 숫자를 반환하는 함수

INITCAP : 문자열의 첫 번째 문자만 대문자로 변환

LOWER : 문자열 전체를 소문자로 변환

UPPER : 문자열 전체를 대문자로 변환

 

INITCAP을 USERID 열에 적용

 

LOWER와 UPPER을 USERID 열에 적용

 

# 문제 : adams라는 사원이 있으면 이름, 직업, 부서번호를 출력하세요.

문제_답안

 

 

* LENGTH 함수 : 인수로 입력되는 칼럼이나 표현식의 문자열의 길이를 반환하는 함수

* LENGTHB 함수 : 문자열의 바이트 수를 반환하는 함수

 

현재 database의 캐릭터셋 확인

* 캐릭터셋 종류

=> KO16KSC5601 : 한글 완성형의 경우, 글자당 2 Byte

=> AL32UTF8 : 글자당 3 Byte

( 캐릭터셋을 임의로 바꾸면 글자가 깨지기도 할 수도 있음.)

 

 

 

# 문제 : 이름이 J,A, M 으로 시작하는 모든 사원에 대해 첫 번째 문자는 대문자로 나머지는 모두 소문자로 사원의 이름과 길이를 출력하고 이름순으로 정렬하세요.

문제_답안_1

 

문제_답안_2

 

문제_답안_3

 

 

 

* CONCAT 함수 : 첫 번째 문자와 두 번째 문자를 연결

 

* SUBSTR 함수 : 문자열의 일부를 추출하는 함수

 

* INSTR 함수 : 문자열 중에서 사용자가 지정한 특정 문자가 포함된 시작위치를 반환하는 함수

=> N 번째 위치부터 M번째 char의 시작위치를 찾음.

=> N이 음수이면 char의 뒤부터 찾음.

 

문장 안에 'OR'이 총 3번 등장

 

* LPAD, RPAD 함수 : 문자열이 일정한 크기가 되도록 왼쪽 또는 오른쪽에 지정한 문자를 삽입하는 함수

 

 

* LTRIM, RTRIM 함수 : 문자열에서 특정 문자를 삭제하기 위해 사용 

LTRIM 함수 사용 : 왼쪽부터 해당 문자열 탐색 후 제거

 

RTRIM 함수 사용 : 오른쪽부터 해당 문자열 탐색 후 제거

 

# 예제 : 부서 테이블에서 부서 이름의 마지막 글자인 '과'를 삭제하여 출력하여라.

문제_답안

 

---

# 예제 1 : 사원테이블에서 사원명 칼럼의 별명은 Name, 급여*12는 Annual Salary로 부여하여 출력해 보세요.

문제_답안

# 예제 2 : 사원테이블의 사원명과 급여를 아래와 같은 포맷으로 출력해 보세요.

MONTHLY
--------------------------------------
SMITH: 1 Month salary = 800
ALLEN: 1 Month salary = 1600
WARD: 1 Month salary = 1250

 

문제_답안

# 예제 3 : 사원테이블에서 급여가 $1,500 ~ $5,000이고 직무가 PRESIDENT나 ANALYST가 아닌 모든 사원에 대해 사번, 이름, 직무, 급여를 직무,급여의 오름차순으로 정렬 하세요.

문제_답안

 

# 예제 4 : 사원 테이블에서 2월에 입사한 사원을 출력해 보세요.

문제_답안

 

# 예제 5 : 사원 테이블에서 20이나 30번 부서에 속하고 이름의 마지막 글자에 'S'자를 포함한 사원들 중에서 마지막 'S'를 제거해 보세요.

문제_답안