:: 작업장

[SQLD] 2과목 SQL 기본및 활용 - 1장 8절 ORDER BY 절

1. ORDER BY 절

- SQL 문장으로 조회된 테이블을 다양한 목적에 맞게 특정 칼럼을 기준으로 정렬하여 출력하는데 사용한다.

- ORDER BY 절에 칼럼명 대신에 SELECT 절에서 사용한 ALIAS명이나 칼럼 순서를 나타내는 정수도 사용한다.

* ASC : 오름차순정렬( DEFAULT)

* DESC : 내림차순 정렬

* ORDER BY 절 사용 특징.

- 기본적인 정렬 순서는 오름차순(ASC) 이다.

- 숫자형 데이터 타입은 오르차순으로 정렬했을 때 가장 작은 값부터 출력된다.

- 날짜형 데이터 타입은 오름차순으로 정렬했을 때 날짜 값이 가장 빠른 값이 먼저 출력된다.

(2015/01/01 -> 2015/01/02 ->2015/01/03 ....)

- 오라클은 NULL값을 사용할 경우 가장 큰 값으로 간주하며, SQL server는 가장 작은값으로 간주한다.

- ORDER BY 절에 칼럼의 순서로 명시할 수 있다

(EX. ORDER BY 3 DESC, 2,1)

EX1.) 컬럼기준 정렬

SELECT DEPTNO, AVG(SAL)

FROM EMP

GROUP BY DEPTNO

HAVING MAX(SAL) >=3000

ORDER BY DEPTNO ;

결과
DEPTNO   AVG(SAL)

---------- ----------

10         2916.66667

20         2175

* 정렬안한 결과

DEPTNO   AVG(SAL)

---------- ----------

20          2175

10          2916.66667

EX2.) ALIAS 기준 정렬

SELECT DEPTNO DEP_AS, AVG(SAL)

FROM EMP

GROUP BY DEPTNO

HAVING MAX(SAL) >=3000

ORDER BY DEP_AS;

* 오라클에서는 NULL 값을 가장 큰 값으로 취급한다.

* SQL Server에서는 NULL 값을 가장 작은 값으로 취급.

ENAME COMM

---------- ----------

TURNER     0

ALLEN   300

WARD   500

MARTIN 1400

SCOTT

KING

ADAMS

JAMES

FORD

MILLER

BLAKE

JONES

GOODBOY

CLARK

EX4)여러가지 컬럼을 기준으로 정렬

연봉이 높은 순서대로, 연봉이 같은경우 커미션이 높은 순서대로 정렬

SELECT ENAME, SAL, NVL(COMM,0) NVL_COMM

FROM EMP

ORDER BY SAL, NVL_COMM;

EX5) 칼럼 순서로 정렬.

SELECT ENAME, SAL, NVL(COMM,0) NVL_COMM

FROM EMP

ORDER BY 2,3,1;

예제. DEPT 테이블 정보를 부서명, 지역별, 부서번호 내림차순으로 정렬하여 출력. EX1) 칼럼명사용. SELECT DNAME, LOC, DEPTNO FROM DEPT ORDER BY DNAME, LOC, DEPTNO; EX2) 칼럼명 + ALIAS 사용  SELECT DNAME, LOC LOC_AS, DEPTNO FROM DEPT ORDER BY DNAME, LOC_AS, DEPTNO; EX3) 칼럼순서번호 + ALIAS 사용 SELECT DNAME, LOC LOC_AS, DEPTNO FROM DEPT ORDER BY 1, LOC_AS, 3; -결과는 모두 같다.

 

2. SELECT 문장 실행 순서.

- GROUP BY 절과 ORDER BY 절이 같이 사용될 때 SELECT 문장은 6개의 절로 구성된다.

1. SELECT 2. FROM 3. WHERE 4. GROUP BY 5. HAVING 6. ORDER BY

=> 문장 실행 순서.(옵티마이저가 SYNTAX 및 SEMANTIC 에러를 점검하는 순서) 

1. 발췌대상 테이블 참조.( FROM )

2. 발췌대상 데이터가 아닌것은 제거 ( WHERE)

3. 행들을 소그룹화 (GROUP BY)

4. 그룹핑된 값의 조건에 맞는 것만을 출력 (HAVING)

5. 데이터값을 출력 및 계산 (SELECT)

6. 데이터를 정렬(ORDER BY)

* ORDER BY절에는 SELECT 목록에 없는 문자형 항목이 포함될 수 있다.

단, SELET DISTINCT를 지정하거나 SQL 문장에 GROUP BY 절이 있거나 또는 SELECT문에 UNION 연산자가 있으면 열정의가 SELECT 목록에 표시되어야 한다.

(관계형 DB에서 데이터를 메모리로 올릴 때 행단위로 모든 컬럼을 가져오게되므로 SELECT절에서는 일부만 선택하더라도 ORDER BY 절에서 메모리에 올라온 다른 컬럼데이터를 사용할 수 있다.)

ex) SELECT 절에 없는 EMP 칼럼을 ORDER BY 절에 사용.

SELECT EMPNO, ENAME

FROM EMP

ORDER BY MGR ;

     EMPNO ENAME

---------- ----------

      7902 FORD

      7788 SCOTT

      7900 JAMES

      7499 ALLEN

      7521 WARD

      7844 TURNER

      7654 MARTIN

      7934 MILLER

      7876 ADAMS

      7698 BLAKE

      7566 JONES

      7782 CLARK

      7369 GOODBOY

      7839 KING

ex) 인라인 뷰에 정의된 SELECT 칼럼을 메인쿼리에서 사용.

SELECT EMPNO

FROM (SELECT EMPNO, ENAME FROM EMP ORDER BY MGR);

     EMPNO

----------

      7902

      7788

      7900

      7499

      7521

      7844

      7654

      7934

      7876

      7698

      7566

      7782

      7369

      7839

ex) 인라인 뷰에 미정의된 SELECT 칼럼을 메인쿼리에서 사용.

SELECT MGR

FROM (SELECT EMPNO, ENAME FROM EMP ORDER BY MGR;)

결과

ERROR at line 1:

ORA-00904: "MGR": invalid identifier

=> MGR이 인라인뷰를 통해 조회되지않음.

- 서브쿼리절에서 선택되지 않은 컬럼들은 계속 유지가 되는것이 아니라 서브쿼리 범위를 벗어나면 더이상 하용할 수 없다.

-GROUP BY 절에서 그룹핑 기준을 정의하게 되면 데이터베이스는 일반적인 SELECT 문장처럼 FROM 절에 정의된 테이블 구조를 그대로 가지고 가는 것이 아니라 GROUP BY 절의 그룹핑 기준에 사용된 칼럼과 집계 함수에

사용될 수 있는 숫자형 데이터 칼럼들의 집합을 새로 만든다.

- GROUP BY 절을 사용하게 되면 그룹핑 기준에 사용된 칼럼과 집계함수에 사용될 수 있는 숫자형 데이터 컬럼들의 집합을 새로 마드는데 개별 데이터는 필요 없으므로 저장되지 않는다. GROUP BY 이후 수행 절인 SELECT 절이나 ORDER BY 절에서 개별 데이터를 사용할 경우 에러가 발생한다.

* SELECT 절에서는 그룹핑 기준과 숫자 형식 칼럼의 집계함수를 사용할 수 있지만 그룹핑 기준 외의 문자 형식 칼럼은 정할 수 없다.

- GROUP BY 절 사용 시 SELECT 절에 일반 칼럼을 사용

SELECT JOB, SAL

FROM EMP

GROUP BY JOB

HAVING COUNT(*) > 0

ORDER BY SAL;

 

ERROR at line 1:

ORA-00979: not a GROUP BY expression

 

=> 에러발생.

- GROUP BY 절 사용시 ORDER BY 절에 일반 칼럼을 사용해본다.

SELECT JOB

FROM EMP

GROUP BY JOB

HAVING COUNT(*) > 0

ORDER BY SAL;

ORA-00979: not a GROUP BY expression

 

=> 에러발생

- GROUP BY 절 사용시 ORDER BY 절에 집계 칼럼 사용.

SELECT JOB

FROM EMP

GROUP BY JOB

HAVING COUNT(*) > 0

ORDER BY MAX(EMPNO), MAX(MGR), SUM(SAL), COUNT(DEPTNO), MAX(HIREDATE);

JOB

---------

singer

people

actor

MANAGER

PRESIDENT

SALESMAN

ANALYST

CLERK

 

3. TOP N 쿼리

- ROWNUM

오라클에서 우선순위가 높은 N개의 로우를 추출하기 위해 ORDER BY 절과 WHERE 절의 ROWNUM조건을 같이 사용하는 경우가 있는데 이 두조건으로는 원하는 값을 얻을 수 없다.

오라클의 경우 정렬이 완료된 후 데이터의 일부가 출력되는 것이 아니라 데이터의 일부가 추출된 후 데이터에 대한 정렬작업을 수행한다.

예제. 사원테이블에서 급여가 높은 3명만 내림차순으로 출력

SELECT ENAME, SAL

FROM EMP

WHERE ROWNUM < = 3

ORDER BY SAL DESC;

ENAME   SAL

---------- ----------

ALLEN 1600

WARD 1250

 

GOODBOY   800

=> 해당 결과는 급여에 대한 오름차순 정렬이 아니라 무작위로 추출된 3명에 대해 급여를 내림차순으로 정렬한 결과이므로 원하는 결과가 출력된것이아니다.

( WHERE 절이 ORDER BY 절보다 먼저 수행 되기 때문이다.)

A. 정답. 인라인 뷰를 통해 데이터 정렬을 먼저 수행 후 메인쿼리에서 ROWNUM 사용.

SELECT  ENAME, SAL

FROM (SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC)

WHERE ROWNUM < = 3 ;

ENAME   SAL

---------- ----------

KING 5000

SCOTT 3000

 

FORD 3000

* 인라인뷰를 사용하여 추출하고자 하는 집합을 정렬한 후 ROWNUM을 적용시킴으로써 결과에 참여하는 순서와 추출된는 로우 순서를 일치 시킴으로써 TOP N 쿼리의 결과를 만들었다.

- TOP

반면에, SQL server는 조건을 사용하게 되면 별도의 처리 없이 관련 ORDER BY 절의 데이터 정렬 후 원하는 일부 데이터만 쉽게 출력할 수 있다.

TOP (EXPRESSION) [PERCENT] [WITH TIES]

 

* TOP 절을 사용해서 결과 집합으로 반환되는 행의 수를 제한할 수 있다. 

- WITH TIES 옵션은 ORDER BY 절의 조건 기준으로 TOP N 의 마지막 행으로 표시되는 추가 행의 데이터가 같을경우 N+ 동일정렬 순서 데이터를 추가 반환하도록 지정하는 옵션이다.

EX) 사원테이블에서 급여가 높은 2명을 내림차순으로 출력.

SELECT TOP(2) ENAME, SAL

FROM EMP

ORDER BY SAL DESC;

ENAME   SAL

---------- ----------

KING 5000

SCOTT 3000

 

EX2) 사원테이블에서 급여가 높은 2명을 내림차순으로 출력하는데, 같은 급여를 받는 사원이 있으면 같이 출력

SELECT TOP(2) WITH TIES ENAME, SAL

FROM EMP

ORDER BY SAL DESC;

ENAME   SAL

---------- ----------

KING 5000

SCOTT 3000

 

FORD 3000

 

[SQLD] 2과목 SQL 기본및 활용 - 1장 4절 TCL

1. 트랜잭션 개요.

- 트랜잭션은 데이터베이스의 논리적인 연산단위이다.

- 트랜잭션이란 밀접히 관련되어 분리될 수 없는 한 개 이상의 데이터베이스 조작을 가리킨다.

(분할할 수 없는 최소단위)

- 전부 적용하거나 전부 취소한다. ( ALL OR NOTHING)

- 올바르게 반영된 데이터를 데이터베이스에 반영시키는 것을 COMMIT, 트랜잭션 시작 이전 상태로 되돌리는 것을 ROLLBACK 이라고한다. SAVEPOINT 기능과 함께 TCL로 분류한다.

- 트랜잭션의 대상이 되는 SQL 문은 UPDATE, INSERT, DELETE 등 데이터를 수정하는 DML문이다.

- SELECT 문은 트랜잭션 대상은 아니지만 SELECT FOR UPDATE 등 배타적 LOCK을 요구하는 SELECT 문장은 트랜잭션의 대상이 될 수 있다.

* 트랜잭션의 특성 ACID

2. COMMIT

- 입력한 자료나 수정한 자료에 대해서 또는 삭제한 자료에 대해서 문제가 없다고 판단되었을 경우 COMMIT 명령어를 통해 트랜잭션을 완료할 수 있다.

* COMMIT이나 ROLLBACK 이전의 데이터 상태

- 단지 메모리 BUFFER에만 영향을 받았기 때문에 데이터의 변경 이전 상태로 복구 가능하다.

- 현재 사용자는 SELECT문으로 결과를 확인할 수 있다.

- 다른 사용자는 현재 사용자가 수행한 명령의 결과를 볼 수 없다.

- 변경된 행은 잠금이 설정되어 다른사용자가 변경할 수 없다.

*COMMIT 이후의 데이터 상태

- 데이터에 대한 변경사항이 데이터베이스에 반영된다.

- 이전 데이터는 영원히 잃어버리게된다.

- 모든 사용자는 결과를 볼 수 있다.

- 관련된 행에 대한 잠금이 풀리고 다른 사용자들이 행을 조작할 수 있다.

* SQL server의 COMMIT

- 오라클은 DML을 수행할 경우 사용자가 트랜잭션을 종료하기 위해 COMMIT 혹은 ROLLBACK을 수행해야 종료된다.

하지만 SQL server경우에는 기본적으로 AUTO COMMIT 모드이기 때문에 DML 수행이후 사용자가 COMMIT이나 ROLLBACK 을 처리할 필요가 없다.

DML 구문이 성공적이면 자동으로 COMMIT되고 오류가 발생할 경우 자동으로 ROLLBACK  처리된다.

* SQL server의 트랜잭션 방식

1) AUTO COMMIT

- SQL server의 기본 방식이며 DML, DDL 수행할 때마다 DBMS 가 트랜잭션을 컨트롤 하는방식이다.

명령어가 성공적으로 수행되면 COMMIT을 수행하고 오류가 발생하면 자동으로 ROLLBACK을 수행한다.

2) 암시적 트랜잭션

- 오라클과 같은방식으로 처리된다. 트랜잭션의 시작은 DBMS가 처리하고 트랜잭션의 끝은 사용자가 명시적으로 COMMIT 또는 ROLLBACK으로 처리한다. 인스턴스 단위 또는 세션 단위로 설정할 수 있다.

3) 명시적 트랜잭션

- 트랜잭션의 시작과 끝을 모두 사용자가 명시적으로 지정하는 방식이다.

BEGIN TRANSACTION (BEGIN TRAN 구문도 가능)으로 시작

COMMIT TRANSACTION (TRANSACTION 생략가능)

ROLLBACK TRANSACTION (TRANSACTION 생략가능) 으로 종료한다.

ROLLBACK 구문을 만나면 최초 BEGIN TRANSACTION 까지 ROLLBACK이 수행된다.

3. ROLLBACK

- 테이블에 입력한 데이터나 수정한데이터 삭제한 데이터에 대하여 COMMIT 이전에는 변경사항을 취소할 수있다.

- 변경사항이 취소되어 데이터의 이전상태로 복구하며 관련된 행에대해 LOCK이 풀리고 다른 사용자들이 데이터를 변경할 수 있다.

* SQL server의 ROLLBACK

- AUTOCOMMIT이 기본방식이므로 임의적으로 ROLLBACK을 수행하기 위해서는 명시적으로 트랜잭션을 선언해야한다.

*COMMIT과 ROLLBACK을 수행하며 얻는 효과

- 데이터 무결성 보장

- 영구적인 변경을 하기 전 데이터의 변경사항 확인 가능

- 논리적으로 연관된 작업을 그루핑하여 처리가능.

4. SAVEPOINT

- ROLLBACK할 때 트랜잭션에 포함된 전체 작업을 롤백 하는것이 아니라 현 시점에서 SAVEPOINT까지 트랜잭션의 일부만 롤백할 수 있다.

- 복수의 저장점을 지원할 수 있으며 동일한 이름으로 저장할 경우 나중에 정의한 저장점이 유효하다.

- 오라클

- SQL server

- ROLLBACK 원리 (오라클)

 - A  저장점으로 돌린 후 B 저장점으로 되돌릴 수 없다. 특정 지점까지 롤백하면 그 저장점 이후에 설정한 저장점이 무효화되기 때문

(ROLLBACK TO A를 실행한 시점에서 A 이후에 저장한 B는 존재하지않는다.)

* ROLLBACK TO B 후 ROLLBACK TO A는 가능하다.

***트랜잭션 정리

- 해당테이블에 데이터의 변경을 발생시키는 입력, 수정, 삭제 수행시 그 변경되는 데이터의 무결성을 보장하는것이 COMMIT과 ROLLBACK의 목적이다.

- COMMIT은 변경된 데이터를 테이블이 영구적으로 반영해라 라는 의미를 갖음.

- ROLLBACK 변경된 데이터가 문제가있으니 변경 전 데이터로 복귀하라는 의미.

- 저장점은 데이터 변경을 저장점까지만 롤백하라는 의미.

- 오라클의 트랜잭션은 SQL문이 시작되면 자동으로 시작되고 COMMIT 또는 ROLLBACK을 수행할 때 종료된다.

- 다음의 경우에는 COMMIT과 ROLLBACK을 수행하지 않아도 자동으로 트랜잭션이 종료됨.

* CREATE, INSERT, DROP, RENAME, TRUNCATE TABLE 등 DDL이 수행되면 그 전후 시점에 자동으로 커밋된다.

* DML문장 이후 커밋없이 DDL을 수행하면 DDL 수행 전에 자동 커밋된다.

* 데이터메이스를 정상종료하면 자동으로 커밋된다.

* 애플리캐이션의 이상 종료로 데이터베이스와 접속이 단절되었을 때는 트랜잭션이 자동 롤백된다.

* SQL Server의 트랜잭션은 AUTO COMMIT이 기본방식이다.

(애플리케이션의 이상종료로 데이터베이스와 접속이 단절되었을 경우에는 오라클처럼 자동 ROLLBACK된다.)

[SQLD] 2과목 SQL 기본및 활용 - 1장 1절 관계형 데이터베이스 개요

1. 데이터베이스

- 특정 기업이나 조직 또는 개인이 필요에 의해 데이터를 일정한 형태로 저장해 놓은 것.

* 데이터베이스의 발전

- 1960년대: 플로우 차트 중심의 개발 방법을 사용하였으며 파일 구조를 통해 데이터를 저장하고 관리하였다.

- 1970년대: 데이터베이스 관리기법이 처음 태동되던 시기였으며 계층형 데이터베이스, 망형 데이터베이스 같은 제품들이 상용화 되었다.

- 1980년대: 현재 대부분의 기업에서 사용되고있는 관계형 데이터베이스가 상용화 되었으며 Oracle, Sybase, DB2와 같은 제품이 있다.

- 1990년대: Oracle, Sybase, Informix, DB2, Teradata, SQL Server외 많은 제품들이 보다 향상된 기능으로 정보시스템의 확실한 핵심 솔루션으로 자리잡게 되었으며 인터넷 환경의 급속한 발전과 객체지향 정보를 지원하기 위해 객체 관계형 데이터베이스로 발전하게되었다.

* 관계형 데이터베이스

- 관계형 데이터베이스는 정규화를 통한 합리적인 테이블 모델링을 통해 이상 현상을 제거하고 데이터중복을 피할 수 있으며 동시성관리, 병행제어를 통해 많은 사용자들이 동시에 데이터를 공유 및 조작할 수 있는 기능을 제공한다.

- 관계형 데이터베이스는 메타 데이터를 총괄 관리할 수 있기 때문에 데이터의 성격, 속성 또는 표현 방법 등을 체계화할 수 있고, 데이터 표준화를 통한 데이터 품질을 확보할 수 있는 장점을 가지고 있다.

- 테이블 생성시에 사용할 수 있는 다양한 제약조건을 이용하여 사용자가 실수로 조건에 위배되는 데이터를 입력한다든지 관계를 연결하는 중요한 데이터를 삭제하는 것을 방지하여 무결성을 보장할 수 있다.

2. SQL

- 관계형 데이터베이스에서 데이터 정의 데이터 조작, 데이터 제어를 하기위해 사용하는 언어.

3. TABLE

- 데이터는 관계형 데이터베이스에서 테이블 형태로 저장된다.

- 모든 자료는 테이블에 등록되고 우리는 테이블로부터 원하는 자료를 꺼내올 수 있다.

- 테이블은 어느 특정한 주제와 목적으로 만들어지는 일종의 집합이다.

- 테이블은 데이터를 저장하는 객체로서 관계형 데이터베이스의 기본단위이다.

- 세로방향은 컬럼, 가로방향은 로우 라고하며 칼럼과 행이 겹치는 부분을 필드라고한다.

- 선수와 관련된 데이터는 선수테이블과 구단 테이블 이라는 복수의 테이블로 분할하여 저장한다.

분할된 테이블은 그 칼럼의 값에 의해 결정된다.  이렇게 테이블을 분할하여 데이터의 불필요하는 중복을 줄이는 것을 정규화라고 한다.

- 각 행을 한가지 의미로 특정할 수 있는 한 개 이상의 칼럼을 기본키(Primary Key)라고 하며 다른 테이블의 기본 키로 사용되면서 테이블과의 관계를 연결하는 역할을 하는 칼럼을 외부키라고 한다.

4.  ERD

- ERD의 구성요소는 엔터티, 관계, 속성 3가지 이며 이 3가지 구성요소로 모두 표현이 가능하다.