[SQL] ORDER BY, DISTINCT, SQL 연산자

💡피드백 Tip

1. SQL Developer 클라이언트 프로그램에서의 NULL 표기

Putty 터미널을 이용하여 오라클 SQL 쿼리 해보기

2. 되도록 SQL에서 R-Value가 타입이 변하도록 해야 한다.

3. 숫자/날짜, 문자 암시적 타입 캐스팅

📘 ORDER BY

📘 DISTINCT

📘 SQL 연산자

BETWEEN

LIKE

IN

ANY(or), ALL(and)

📝일일 과제

1. EMP 테이블에서 사번,이름,직무,급여,부서번호 데이터 사이에 구분자 ,를 삽입하는 예제 SQL 작성

a) SPOOL, SQL Script file 사용해서 emp_list.csv 파일생성 >> excel

b) set 명령어(SQL*PLUS) 사용 >> excel

b_1)

b_2) create_csv_emp.sql

cf) export로 csv 파일 만들기

2. ③ SQL 실행 결과가 Oracle 9i 에서는 정렬되어 나타나지만 Oracle 10g이후 버전부터는 정렬된 결과가 나타나지 않는다. 이유를 찾아 설명

3. ⑥⑦ SQL이 왜 에러가 나는지 Select Syntax Diagram을 보고 설명

4. Interactive SQL 과 Embedded SQL를 설명 하고 각각의 사용예시를 찾아서 발표

5. SELECT * FROM SALGRADE WHERE 3000 BETWEEN LOSAL AND HISAL; 결과 데이터를 설명

6. 아래의 SQL 실행시 데이터 검색이 안되는 이유를 날짜 포맷 YY 와 RR의 차이점을 조사한 후 설명

7. LIKE 연산자를 사용하여 ENAME에 '_' 가 들어가 있는 사원들을 찾는 SQL 작성 ENAME 컬럼의 데이터를 수정(UPDATE)하여 검색 결과 예시 출력 -힌트 : escape option

EMP 테이블 ENAME 컬럼 데이터 수정 (UPDATE)

‘_’가 들어가 있는 사원 찾는 SQL 작성

---

💡피드백 Tip

1. SQL Developer 클라이언트 프로그램에서의 NULL 표기

Putty 터미널을 이용하여 오라클 SQL 쿼리 해보기

1. putty를 이용하여 VM 접속

2. Oracle DBMS 접속

$ sqlplus scott/

SCOTT> SELECT DISTINCT COMM FROM EMP;

💡

SQL Developer에서는 (null) 값을 따로 처리해 시각적으로 보여주지만, 터미널을 이용하여 SQL 쿼리시에는 null을 표기해주지 않아 null을 구별하지 못한다.

2. 되도록 SQL에서 R-Value가 타입이 변하도록 해야 한다.

💡

L-Value = R-Value에서 되도록 오른쪽 값을 타입 변경하도록 SQL문을 작성해야 한다. → 데이터 타입 캐스팅의 횟수를 되도록 적게 하기 위함이고, 성능 개선의 이유가 된다.

💡

sal like ‘2%’ sal = ‘2000’

3. 숫자/날짜, 문자 암시적 타입 캐스팅

💡

내부적으로 숫자 타입이 메모리, 계산의 용이한 이점이 있기 때문에, 암시적 타입 캐스팅 발생 시에 대부분 숫자나 날짜 우선으로 캐스팅 된다.

📘 ORDER BY

💡

[역할] 지정한 컬럼을 기준으로 데이터 정렬(Sorting) - ORDER BY는 데이터가 많으면 부하가 있을 수 있다. - SQL 문법적으로 가장 마지막에 위치. (실행도 가장 마지막에 실행된다.)

💡

[기준] 정렬시 값 비교기준 숫자 : ~ 작은 수/큰 수 비교 EX) 123 < 456 문자 : 알파벳 순서(ASCII) EX) ‘SCOTT' < ‘T' → 문자는 첫 글자를 대상으로 비교된다. 날짜 : 숫자와 동일 EX) ‘2003/11/16' > ‘19990916' → DBMS 내부에서는 숫자로 저장되고, 출력 시에는 문자열로 표기 → 따라서 숫자 비교와 동일하다. NULL : Oracle DBMS 가장 큰 값으로 간주 , MS SQL-Server: 가장 작은 값으로 간주 [정렬 방향(방법)] ASC : 오름차순(ASCENDING ORDER) , DEFAULT DESC : 내림차순(DESCENDING ORDER)

💡

// 정렬방향? Default(ASC) ① SELECT ENAME, HIREDATE, SAL, COMM FROM EMP ORDER BY ENAME ;

// SQL 가독성을 위해 ASC 명시하는 것이 좋다. ② SELECT ENAME, HIREDATE, SAL, COMM FROM EMP ORDER BY ENAME asc;

// 날짜 기준 정렬 ③ SELECT ENAME, HIREDATE, SAL, COMM FROM EMP ORDER BY HIREDATE desc;

→ 날짜 기준으로 내림차순 정렬되었다. // column name 기준 정렬 ④ SELECT ENAME, HIREDATE, SAL, COMM FROM EMP ORDER BY ENAME;

// column position 기준 정렬 ⑤ SELECT ENAME, HIREDATE, SAL, COMM FROM EMP ORDER BY 2;

→ 두번째 컬럼 HIREDATE를 기준으로 오름차순 정렬되었다.

// ORDER BY 에 Column Alias올 수 있다. ⑥ SELECT ENAME, SAL*12 as 연봉 FROM EMP ORDER BY 연봉;

→ SAL*12의 Alias인 연봉 컬럼을 기준 오름차순 정렬되었다. // expression, NULL 위치 ⑦ 1) SELECT ENAME, HIREDATE, SAL, COMM FROM EMP ORDER BY COMM * 12;

→ ORDER BY 다음에 계산 식이 올 수 있다.

→ COMM * 12의 값에 오름차순으로 정렬되었다. 2) SELECT ENAME, HIREDATE, SAL, COMM FROM EMP ORDER BY COMM * 12 NULLS FIRST;

// ⑧ SELECT DEPTNO,JOB,ENAME FROM EMP ORDER BY DEPTNO;

// ORDER BY에 복수 개의 컬럼 // 첫번째 컬럼 기준 정렬 후, 두번째 컬럼 기준 정렬 (조합의 순서쌍) ⑨ SELECT DEPTNO,JOB,ENAME FROM EMP ORDER BY DEPTNO,JOB;

// DEPTNO는 오름차순, JOB은 내림차순 ⑩ SELECT DEPTNO,JOB,ENAME FROM EMP ORDER BY DEPTNO,JOB desc; // 차이점은?

📘 DISTINCT

💡

중복된 데이터를 필터링하여 조회(SELECT) - 조회 시 사용하며, 실제 데이터가 삭제되지는 않는다. - HASH 알고리즘이 적용되어 중복을 처리한다.

💡

// 전체 Row(레코드) 개수 만큼, ① SELECT JOB FROM EMP;

// 직군 종류, Oracle ② SELECT UNIQUE JOB FROM EMP;

// DISTINCT - ANSI ③ SELECT DISTINCT JOB FROM EMP;

→ DISTINCT와 UNIQUE는 동일한 결과를 출력한다. → DISTINCT 키워드가 ANSI 표준이므로 주로 사용된다.

④ SELECT DISTINCT DEPTNO, JOB FROM EMP; // 중복 데이터(?)

• 컬럼(들)의 조합의 중복 필터링

⑤ SELECT JOB FROM EMP ORDER BY JOB;

• distinct 연산 내부 알고리즘: Oracle 9i: Sort , 10g: Hash

⑥ SELECT DISTINCT JOB, DISTINCT DEPTNO FROM EMP; // 범위?

• DISTINCT 는 맨 앞 하나만 가능 (각 컬럼 조합에 대한 중복 제거)

  

⑦ SELECT JOB, DISTINCT DEPTNO FROM EMP; // 위치?

• DISTINCT는 맨 앞에 위치해야 한다.

• 마찬가지로 조합에 대한 중복을 제거하는 것 이므로, JOB을 먼저 select 하고 이후 중복제거는 불가능하다.

⑧ SELECT COMM FROM EMP WHERE COMM IS NOT NULL; // 4 Rows

→NULL이 아닌, 값을 가진 컬럼들이 출력되었다.

⑨ SELECT DISTINCT COMM FROM EMP; // 5 Rows ??

• * NULL과 DISTINCT

→ NULL 값도 DISTINCT 처리되어 각 중복이 제거된 항목이 나온다.

📘 SQL 연산자

BETWEEN

• 범위 연산자 (하한 값과 상한 값 사이의 범위 검색 연산자)

💡

// 숫자 타입 // BETWEEN 연산자 사용 ① SELECT ENAME,SAL,HIREDATE FROM EMP WHERE SAL between 1000 and 2000;

// 비교 연산자 사용 ② SELECT ENAME,SAL,HIREDATE FROM EMP WHERE SAL >= 1000 and SAL <= 2000;

→ ①,②는 같은 결과를 리턴한다. BETWEEN 연산자를 몰라도, 비교 연산을 통해 같은 결과를 낼 수 있다. → BETWEEN 연산자는 DBMS에서 내부적으로 비교 연산자로 변환하여 처리한다.

// 이유는? ③ SELECT ENAME,SAL,HIREDATE FROM EMP WHERE SAL between 2000 and 1000;

→ between은 하한 값 and 상한 값 으로 명시해야 한다. // 문자 타입 ④ SELECT ENAME,SAL,HIREDATE FROM EMP WHERE ENAME BETWEEN 'C' AND 'K’;

// 문자 → 날짜 타입, 암시적 데이터형변환 ⑤ SELECT ENAME,SAL,HIREDATE FROM EMP WHERE HIREDATE BETWEEN '81/02/20' AND '82/12/09’;

// 날짜 타입, 명시적 형변환, 검색이 안되는 이유는 ? ⑥ SELECT ENAME,SAL,HIREDATE FROM EMP WHERE HIREDATE BETWEEN to_date('81/02/20','yy/mm/dd') AND to_date('82/12/09','yy/mm/dd');

→ ‘rr/mm/dd’ 로 포멧을 변경하면 된다.

// 날짜 표기 포멧 이해 암기 ⑦ SELECT ENAME,HIREDATE, TO_CHAR(HIREDATE,'YYYY/MM/DD'), TO_CHAR(HIREDATE,'YYYY/MM/DD HH24:MI:SS'), // Date에 시간정보확인 TO_CHAR(HIREDATE,'RRRR/MM/DD HH24:MI:SS'), TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') // sysdate에 시간정보확인 FROM EMP;

SELECT ENAME,SAL,HIREDATE FROM EMP WHERE HIREDATE BETWEEN to_date('81/02/20’,’rr/mm/dd') AND to_date('82/12/09','yy/mm/dd');

⑧ SELECT ENAME,SAL,HIREDATE FROM EMP WHERE HIREDATE BETWEEN to_date('1981/02/20','yyyy/mm/dd') AND to_date('1982/12/09','yyyy/mm/dd');

⑨ SELECT ENAME,SAL,HIREDATE FROM EMP WHERE HIREDATE BETWEEN to_date('81/02/20','rr/mm/dd') AND to_date('82/12/09','rr/mm/dd'); ⑩ SELECT ENAME,SAL,HIREDATE FROM EMP WHERE HIREDATE BETWEEN to_date('2081/02/20','yyyy/mm/dd') AND to_date('2082/12/09','yyyy/mm/dd');

LIKE

💡

문자열 데이터 타입에 대한 패턴 매칭 연산자 - 정확한 값을 몰라도 찾을 수 있다. * Wildcard 문자 ① % : 0개 이상의 모든 문자 ② _ : 1개의 모든 문자, 위치가 의미를 가짐. * 데이터 양이 많아질 경우 like 사용을 자제하자.

💡

* 타입 캐스팅시, like 연산자는 문자열에 대한 패턴 매칭 연산자 이므로, 기존 컬럼의 데이터가 문자열로 캐스팅 된 후 패턴 매칭되어 결과를 리턴해준다.

💡

// pattern matching ① SELECT ENAME FROM EMP WHERE ENAME like 'A%';

→ ENAME이 A로 시작하는 것

② SELECT ENAME FROM EMP WHERE ENAME like '_A%';

→ ENAME이 두번째에 A가 들어간 것

③ SELECT ENAME FROM EMP WHERE ENAME like '%L%E%';

→ ENAME이 중간에 L과 E가 들어간 것 ④ SELECT ENAME FROM EMP WHERE ENAME like '%LE%';

→ ENAME이 LE가 포함된 데이터 ⑤ SELECT ENAME FROM EMP WHERE ENAME like '%A%';

→ ENAME에 A가 포함된 데이터 ⑥ SELECT ENAME FROM EMP WHERE ENAME NOT like '%A%';

→ A가 포함되지 않은 데이터 // 날짜 , 암시적 형변환 ⑦ SELECT ENAME,HIREDATE FROM EMP WHERE HIREDATE like '81%';

→ HIREDATE의 ‘81’년도의 데이터 출력 → HIREDATE는 날짜 타입이다. → 원래 대로라면, 날짜 타입이 문자열 타입보다 우선이지만, like 특성 상 문자열의 패턴 매칭 연산자이므로 HIREDATE 컬럼 값이 문자열로 변환된다.

// 숫자 , ⑧ SELECT ENAME,SAL FROM EMP WHERE SAL like 2%; → (에러) like 다음엔 항상 문자가 와야 한다!! // 암시적 형변환 ⑨ SELECT ENAME,SAL FROM EMP WHERE SAL like '2%';

→ L-Value인 SAL이 문자 형으로 암시적 타입 변환되어 패턴 매칭된 값이 출력 된다.

// 명시적 형변환 ⑩ SELECT ENAME,SAL FROM EMP WHERE TO_CHAR(SAL) like '2%';

→ 9번과 마찬가지로, 명시적으로 타입 변환을 적어주는 것이 좋다.

💡

타입 변환 유의 // 날짜 HIREDATE like ‘81%’ // 숫자 SAL like 2% // 숫자 SAL like ‘2%’ // 문자 to_char(SAL) like ‘2%’

💡

customer 데이터에서 여의도에 거주하는 김씨 성을 가진 사람 like로 검색해보기 select * from customer where ADDRESS1 like '%여의도%' and NAME like '김%';

IN

💡

리스트 연산자

💡

// 숫자 ① SELECT EMPNO, JOB FROM EMP WHERE EMPNO IN (7369,7521,7654);

// 차이점 ? ② SELECT EMPNO, JOB FROM EMP WHERE EMPNO = 7369 or EMPNO = 7521 or EMPNO=7654;

→ ①,②의 결과 값은 같다. IN을 몰라도 OR 연산자를 이용해 동일한 로직을 구현할 수 있다. → IN의 처리는 DBMS 내부적으로 값이 OR연산으로 변환되어 처리해준다. // 문자 ③ SELECT EMPNO,ENAME,JOB FROM EMP WHERE JOB IN ('clerk','manager');

→ 조회가 안된다. 데이터는 대소문자 구별이 되므로, ‘CLERK’과 ‘MANAGER’로 수정해야 한다. SELECT EMPNO,ENAME,JOB FROM EMP WHERE JOB IN ('CLERK','MANAGER');

// 날짜? ④ SELECT EMPNO,ENAME,HIREDATE FROM EMP WHERE HIREDATE IN ('81/05/01','81/02/20');

→ 암시적 형변환 발생 → IN 뒤 문자열 타입이 날짜 타입으로 캐스팅 되어 처리된다. // 다중컬럼리스트 , in (1..1000) ⑤ SELECT EMPNO,ENAME,JOB,DEPTNO FROM EMP WHERE (JOB,DEPTNO) in (('MANAGER',20),('CLERK',20));

→ 내부적인 괄호 내 콤마는 AND의 역할을 하고, 바깥 콤마는 OR 역할을 한다.

ANY(or), ALL(and)

💡

ANY(SOME) :리스트내에 조건을 만족하는 값이 1개 이상이면 결과 리턴 (OR)

ALL :리스트내에 모든값이 조건을 만족해야 결과 리턴 (AND) ① SELECT ENAME, SAL FROM EMP WHERE SAL > (1500,2450,3000); // Error ?? 단일값 비교 연산자 ② SELECT ENAME, SAL FROM EMP WHERE SAL > ANY(1500,2450,3000); // OR , SAL > 15000 ③ SELECT ENAME, SAL FROM EMP WHERE SAL >= ALL(1500,2450,3000); // AND , SAL >= 3000 ④ SELECT ENAME, SAL FROM EMP WHERE SAL = ANY (1500,2450,3000); // ANY = SOME ⑤ SELECT ENAME, SAL FROM EMP WHERE SAL = SOME (1500,2450,3000); SELECT ENAME, SAL FROM EMP WHERE SAL IN (1500,2450,3000); // 0 Rows가 검색되는 이유? ⑥ SELECT ENAME, SAL FROM EMP WHERE SAL = ALL (1300,2450,3000);

---

📝일일 과제

1. EMP 테이블에서 사번,이름,직무,급여,부서번호 데이터 사이에 구분자 ,를 삽입하는 예제 SQL 작성

💡

SQL Script file - text file - N 줄의 SQL - 반복 실행

💡

SET 명령어 - 환경 변수 설정하는 명령어

💡

CSV(Comma-Separated Values) 파일에서 사용할수 있는 데이터 포맷 출력 a) SPOOL, SQL Script file 사용해서 emp_list.csv 파일생성 >> excel b) set 명령어(SQL*PLUS) 사용 >> excel

$ sqlplus scott/tiger

a) SPOOL, SQL Script file 사용해서 emp_list.csv 파일생성 >> excel

SPOOL emp_list.csv
SELECT empno || ',' || ename || ',' || job || ',' || sal || ',' || deptno FROM EMP
SPOOL off;

b) set 명령어(SQL*PLUS) 사용 >> excel

b_1)

-- 페이지 크기를 0으로 설정하여 페이지 구분을 없앱니다.
set pagesize 0

-- 출력된 데이터의 좌우 여백을 제거합니다.
set trimspool on

-- 컬럼명과 데이터 사이에 구분자를 출력하지 않습니다.
set headsep off

-- 쿼리 실행 결과 메시지를 출력하지 않습니다.
set feedback off

-- 출력할 한 줄의 최대 길이를 2000으로 설정합니다.
set linesize 2000

-- NUMBER 타입 데이터를 출력할 때 최대 자릿수를 2000으로 설정합니다.
set numw 2000

-- 실행 시간 정보를 출력하지 않습니다.
set timing off

spool emp_list.csv;
SELECT EMPNO || ',' || ENAME || ',' || JOB || ',' || SAL || ',' || DEPTNO FROM EMP;
spool off;

@good.sql

b_2) create_csv_emp.sql

-- ','로 구분된 컬럼 데이터를 출력하기 위해 구분자를 ','로 설정합니다.
SET COLSEP ','

-- 쿼리 결과에서 헤더를 제거합니다.
SET HEADING OFF

-- 출력된 데이터의 좌우 여백을 제거합니다.
set trimspool on

-- 컬럼명과 데이터 사이에 구분자를 출력하지 않습니다.
set headsep off

-- 쿼리 실행 결과의 메시지를 제거합니다.
SET FEEDBACK OFF

-- 쿼리 실행 결과를 여러 페이지로 나누지 않고, 한 페이지에 출력합니다.
SET PAGESIZE 0

-- 쿼리 결과를 CSV 형식으로 마크업합니다.
SET MARKUP CSV ON

SPOOL emp_list.csv
SELECT empno, ename, job, sal, deptno FROM EMP;
SPOOL OFF

-- 마크업을 해제합니다.
SET MARKUP CSV OFF

@create_csv_emp.sql

cf) export로 csv 파일 만들기

2. ③ SQL 실행 결과가 Oracle 9i 에서는 정렬되어 나타나지만 Oracle 10g이후 버전부터는 정렬된 결과가 나타나지 않는다. 이유를 찾아 설명

💡

Oracle 9i 에서 DISTINCT의 내부적인 알고리즘은 정렬 알고리즘을 통해 구현되어 있었다. Oracle 10g 이후 버전부터는 DISTINCT를 Hash 알고리즘으로 구현되어 있기 때문에 정렬된 결과가 나타나지 않는다.

3. ⑥⑦ SQL이 왜 에러가 나는지 Select Syntax Diagram을 보고 설명

💡

⑥ SELECT DISTINCT JOB, DISTINCT DEPTNO FROM EMP; // 범위?

• DISTINCT 는 맨 앞 하나만 가능 (각 컬럼 조합에 대한 중복 제거)

  

⑦ SELECT JOB, DISTINCT DEPTNO FROM EMP; // 위치?

• DISTINCT는 맨 앞에 위치해야 한다.

• 마찬가지로 조합에 대한 중복을 제거하는 것 이므로, JOB을 먼저 select 하고 이후 중복제거는 불가능하다.

4. Interactive SQL 과 Embedded SQL를 설명 하고 각각의 사용예시를 찾아서 발표

💡

Interactive SQL과 Embedded SQL은 둘 다 SQL(Structured Query Language)을 사용하여 데이터베이스와 상호 작용하는 방법입니다.

Interactive SQL은 사용자가 SQL 명령을 DBMS로 직접 전송하는 방식으로 상호 작용합니다. 사용자는 SQL 쿼리를 입력하고 실행하여 결과를 즉시 볼 수 있습니다.

Embedded SQL은 프로그래밍 언어(예: C, C++, Java) 내에서 SQL 코드를 내장하는 방식입니다. 프로그래밍 언어 코드 내에 SQL 문을 삽입하여 데이터베이스와 상호 작용할 수 있습니다.

• Java - Host Language

• SQL -

각각의 사용 예시로, 우리가 실습으로 SQL Developer를 활용하여 DBMS 서버에 직접 SQL 쿼리를 전송하는 것이 Interactive SQL 방법이고, 추후 JAVA에서 JDBC를 활용하여 SQL 코드를 내장하여 쿼리하는 것이 Embedded SQL의 예시 입니다.

💡

추후 Interactive SQL을 통해 SQL을 개발 및 기능, 성능을 검증하고, embeded SQL로 해당 SQL을 넣어 개발한다.

5. SELECT * FROM SALGRADE WHERE 3000 BETWEEN LOSAL AND HISAL; 결과 데이터를 설명

💡

결과

"salgrade" 테이블에서 "losal"과 "hisal" 사이에 3000이 포함되는 행을 선택하는 것입니다.

"salgrade" 테이블은 급여 범위와 해당 범위에 대한 등급을 포함하는 테이블이다. 따라서 이 쿼리는 3000달러의 급여가 어떤 등급에 속하는지 확인하는 데 사용될 수 있다.

6. 아래의 SQL 실행시 데이터 검색이 안되는 이유를 날짜 포맷 YY 와 RR의 차이점을 조사한 후 설명

SELECT ENAME,SAL,HIREDATE FROM EMP
WHERE HIREDATE BETWEEN to_date('81/02/20','yy/mm/dd')
AND to_date('82/12/09','yy/mm/dd');

💡

yy : 0을 기준으로 100년 주기로 연도를 표기함. 따라서, 2000년대만 표기할 수 있고, 1900년대는 표기할 수 없다. rr : 50을 기준으로 100년대를 나눈다. 따라서, 00에서 49까지는 2000년에서 2049년까지의 연도로, 50에서 99까지는 1950년에서 1999년까지의 연도로 해석된다.

→ 따라서 위 SQL 코드를 yy로는 2081년, 2082년대를 가리키고 있으므로 해당 연도에 속하는 데이터가 없다고 출력되었다.

→ rr 을 사용할 경우 1981년, 1982년으로 정상 인식되어 정상적으로 해당 연도에 속한 데이터가 출력되었다.

7. LIKE 연산자를 사용하여 ENAME에 '_' 가 들어가 있는 사원들을 찾는 SQL 작성 ENAME 컬럼의 데이터를 수정(UPDATE)하여 검색 결과 예시 출력 -힌트 : escape option

EMP 테이블 ENAME 컬럼 데이터 수정 (UPDATE)

update emp
set ENAME = '_'||ENAME
WHERE (JOB = 'SALESMAN');

💡

위 코드를 통해 JOB이 SALESMAN인 사람의 이름에 ‘_’가 들어가도록 수정했다.

‘_’가 들어가 있는 사원 찾는 SQL 작성

select * from emp
where ENAME LIKE '%\_%' escape '\';

💡

정상적으로 ‘_’가 포함된 이름을 가진 행이 출력되었다.

---

Uploaded by N2T