[2과목] 1장. SQL 기본

01 관계형 데이터베이스 개요

1. DB : 데이터를 일정한 형태로 저장해 놓은 것, DBMS를 이용해 효율적인 데이터 관리와 데이터 손상 복구 가능

▷ 종류

  - 계층형 DB : 트리 형태의 자료구조에 데이터 저장, 1:N 관계 표현

  - 네트워크형 DB : 오너와 멤버 형태로 데이터 저장, M:N 관계 표현

  - 관계형 DB : 릴레이션에 데이터 저장, 집합 연산과 관계 연산 가능

*현재 대부분의 데이터베이스는 관계형 데이터베이스이다.

 

2. 관계형 DB(RDB; Relationship DAtabase)

① 정규화를 통해 이상현상 및 중복 데이터 제거

② 동시성 관리와 병행 제어를 통해 데이터 동시 조작 가능

▷ 집합 연산

  - 합집합(Union)

  - 차집합(Difference)

  - 교집합(Intersection)

  - 곱집합(Cartesian Product) : 각 릴레이션에 존재하는 모든 데이터를 조합

 

▷ 관계 연산

  - 선택 연산(Selection) : 조건에 맞는 행(튜플) 조회

  - 투명 연산(Projection) : 조건에 맞는 칼럼(속성) 조회

  - 결합 연산(Join) : 공통 속성을 사용해 새로운 릴레이션 생성

  - 나누기 연산(Division) : 공통요소를 추출하고 분모 릴레이션의 속성을 삭제한 후 중복된 행 제거

 

3. SQL(Structured Query Language)

관계형 데이터베이스에서 데이터를 정의, 조작, 제어할 수 있는 언어

데이터 정의 DDL	데이터 구조(테이블)를 정의한다	creat, alter, drop, rename, truncate
데이터 조작 DML	데이터 자체를 다룬다.	select(조회) / insert, update, delete(변형)
데이터 제어 DCL	데이터의 접근권한을 다룬다	grant, revoke
트랜젝션 제어어 TCL	트랜잭션을 제어한다.	commit, rollback

 

4. 테이블(Table)

: 데이터를 저장하는 객체(Object)로서 관계형 데이터베이스의 기본단위이다

: 테이블은 특정 주제와 목적으로 만들어지는 집합(표)이다.

: 테이블은 반드시 하나 이상의 칼럼(속성)을 가져야 한다. 

가로 = 행 = ROW(로우) = 속성
세로 = 열 = Column(칼럼) = 데이터 1줄
식별자	기본키 Primary Key	각 행을 특정할 수 있는 칼럼(속성)
	외부키 Foreign Key	다른 테이블의 기본키와 내 테이블을 연결하는 칼럼(속성)

---

02 데이터 타입 및 제약 조건

1. 데이터 타입

데이터는 특정 칼럼(속성)이 받아들일 수 있는 자료의 유형을 저장해야한다.

	Oracle	SQL Server
고정 길이 문자열	CHAR(길이)
가변 길이 문자열	VARCHAR2(길이)	VARCHAR(길이)
정수, 실수 등의 숫자	NUMBER	int, float
날짜, 시간	DATE	DATETIME

CHAR은 공백 체크 X : 'AA' = 'AA '

VARCHAR2은 공백 체크 : 'AA' ≠ 'AA '

 

2. 제약조건 CONSTRAINT

: 특정 칼럼에 사용자가 원하는 조건의 데이터만 저장하기 위한 제약

- PRIMARY KEY(기본키) : UNIQUE + NOT NULL

- FOREIGN KEY(외래키) : 테이블 간의 관계 표현

- UNIQUE : 중복된 값이 저장되면 안됨 + NULL 기능

- CHECK(조건식) : 입력할 수 있는 값의 범위 등을 제한

- DEFAULT값 : 값이 입력되지 않은 경우, 기본값을 사전에 설정

- NOT NULL : NULL 금지

CREATE TABLE 테이블명(
    칼럼명 데이터유형 PRIMARY KEY,
    칼럼명 데이터유형 REFERENCES 다른테이블명(칼럼명),
    칼럼명 데이터유형 UNIQUE,
    칼럼명 데이터유형 CHECK(칼럼명>=100 AND 칼럼명<300),
    칼럼명 데이터유형 DEFAULT 'Y',
    칼럼명 데이터유형 NOT NULL
    
    칼럼명 데이터유형 CHECK(칼럼명>200) PRIMARY KEY -- 제약조건 혼합가능
    
    [CONSTRAINT 제약조건명] PRIMARY KEY(칼럼명, 칼럼명..),
    [CONSTRAINT 제약조건명] FOREIGN KEY(칼럼명), REFERENCES 다른테이블명(칼럼명)

 

3. 생성된 테이블 구조 확인

- ORACLE : DESCRIBE 테이블명 or DESC 테이블명

- SQL Server : exec sp_help 'dbo.테이블명'

---

*자주 쓰이는 데이터 유형

---

03 DDL

: 테이블(데이터베이스의 기본단위) 구조 생성, 변경, 삭제할 수 있는 명령어

DDL			Oracle	SQL Server
테이블 생성			CREATE TABLE 테이블 이름(     칼럼명1 데이터타입 [제약조건],     칼럼명2 데이터타입 [제약조건],     칼럼명3 데이터타입 [제약조건] ); ex) CREATE TABLE PLAYER(           ID CHAR(7) NOT NULL,           NAME VARCHAR2(20)           BIRTH DATE, AGE NUMBER(3)        );
테이블 변경	칼럼	추가	ALTER TABLE 테이블명 ADD 칼럼명 데이터타입; ex) ALTER TABLE PLAYER       ADD EMAIL VARCHAR(20);
		변경	ALTER TABLE 테이블명 MODIFY (칼럼명 데이터타입 [제약조건], ..); ex) ALTER TABLE       MODIFY EMAIL VARCHAR(30);	ALTER TABLE 테이블명 ALTER COLUMN(칼럼명 데이터타입 [제약조건],..);
		삭제	ALTER TABLE 테이블명 DROP COLUMN 칼럼명; ex) ALTER TABLE PLAYER       DROP COLUMN EMAIL;
	칼럼명 변경		ALTER TABLE 테이블명 RENAME COLUMN 기존 TO 변경; ex) ALTER TABLE PLAYER     RENAME COLUMN EMAIL TO EMAILS;	exec sp_rename 'dbo.기존', '변경', 'COLUMN';
	제약 조건	추가	ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건(칼럼명); ex) ALTER TABLE PLAYER       ADD CONSTRAINT       PLAYER_PK PRIMARY KEY(ID);
		삭제	ATLER TABLE 테이블명 DROP CONSTRAINT 제약조건명; ex) ALTER TABLE PLAYER       DROP CONSTRAINT PLAYER_PK;
테이블명 변경			RENAME 기존 TO 변경; ex) RENAME PLAYER TO PLA;	exec sp_rename 'dbo.기존' , '변경';
테이블 삭제	구조 삭제		DROP TABLE 테이블명 [cascade 제약조건]; ex) DROP TABLE PLAYER;
	구조 유지		TRUNCATE TABLE 테이블명; ex) TRUNCATE TABLE PLAYER;

 

*유의사항

▷ 테이블 생성

  - 테이블명은 객체를 의미할 수 있고, 단수형으로 작성되어야 한다.

  - 테이블명과 칼럼명은 문자로 시작, 길이에 대한 한계가 있다

  - _ , $, #은 허용되지만 - 는 안됨

  - 대/소문자 구별X

 

▷ 테이블 변경 - 칼럼 변경

  - 칼럼의 크기는 늘일 수 있지만 줄일 수 없다

  - DEFAULT값을 변경하면 변경 이후에만 영향을 미친다

  - NULL값이 없을 경우에만 NOT NULL제약조건 추가 가능

 

▷ 테이블 변경 - 칼럼 삭제 & 칼럼명 변경

  - 한번에 하나의 칼럼만 삭제 가능

  - 삭제된 칼럼은 복구 불가능

  - 일부 DBMS에서만 지원하는 기능

  - 해당 칼럼과 관계된 제약조건에도 자동으로 변경되는 장점이 있다.

 

▷ 테이블 삭제 - 구조 삭제

  - SQL Server에는 cascade옵션이 존재하지 않고, 참조테이블을 먼저 삭제해야 함

 

▷ 테이블 삭제 - 구조 유지

  - 모든 행 제거, 저장공간 재사용 가능해짐

 

*SQL Server에서는 여러 칼럼 동시 수정하는 구문은 지원하지 않음

 

Q. 다음 중 아래설명과 같은 테이블 및 PK제약조건을 생성하는 DDL문장으로 올바른 것은? (ORACLE 기준)
PRODUCT테이블
PROD_ID : VARCHAR2(10) NOT NULL
PROD_NM : VARCHAR2(100) NOT NULL
REG_DT : DATE NOT NULL
REGR_NO : NUMBER(10) NULL

① CREATE TABLE PRODUCT (    PROD_ID VARCHAR2(10) NOT NULL,    PROD_NM VARCHAR2(100) NOT NULL,    REG_DT DATE NOT NULL,    REGR_NO NUMBER(10) NULL ); ALTER TABLE PRODUCT ADD PRIMARY KEY PRODUCT_PK ON(PROD_ID);	② CREATE TABLE PRODUCT(    PROD_ID VARCHAR2(10),    PROD_NM VARCHAR2(100),    REG_DT DATE,    REGR_NO NUMBER(10) ); ALTER TABLE PRODUCT ADD CONSTRAINT PRODUCT_PK PRIMARY KEY(PROD_ID);
③ CREATE TABLE PRODUCT(    PROD_ID VARCHAR2(10) NOT NULL,    PROD_NM VARCHAR2(100) NOT NULL,    REG_DT DATE NOT NULL,    REGR_NO NUMBER(10) NULL,    ADD CONSTRAINT PRIMARY KEY(PROD_ID) );	④ CREATE TABLE PRODUCT(    PROD_ID VARCHAR2(10) NOT NULL,    PROD_NM VARCHAR2(100) NOT NULL,    REG_DT DATE NOT NULL,    REGR_NO NUMBER(10), CONSTRAINT PRODUCT_PK PRIMARY KEY (PRO_ID));

 

Q. 테이블

분류ID : VARCHAR(10) PRIMARY KEY

분류명 : VARCHAR(10) NOT NULL → VARCHAR(30) NOT NULL

등록일자 : VARCHAR(10) NULL → DATE NOT NULL

위와 같이 테이블 구조를 변경하고자 할 때, 올바른 SQL문장은? (SQL Server기준)

①  ALTER TABLE 기관분류      ALTER COLUMN(분류명 VARCHAR(30)      , 등록일자 DATE NOT NULL);	② ALTER TABLE 기관분류     ALTER COLUMN(분류명 VARCHAR(30) NOT NULL     , 등록일자 DATE NOT NULL);
③ ALTER TABLE 기관분류     ALTER COLUMN 분류명 VARCHAR(30);     ALTER TABLE 기관분류     ALTER COLUMN 등록일자 DATE NOT NULL;	④ ALTER TABLE 기관분류     ALTER COLUMN 분류명 VARCHAR(30) NOT NULL;     ALTER TABLE 기관분류     ALTER COLUMN 등록일자 DATE NOT NULL;

---

04 DML

: 테이블 구조에 맞게 데이터를 조회, 삽입, 수정, 삭제할 수 있는 명령어이다.

DML		Oracle	SQL Server
데이터 입력	모든 칼럼에 데이터 입력	INSERT INTO 테이블명 VALUES(값, 값,..); ex) INSERT INTO PLAYER VALUES('07', '박지성', 23);
	지정 칼럼에 데이터 입력	INSERT INTO 테이블명(칼럼명, 칼럼명,..) VALUES(값, 값,..); ex) INSERT INTO PLAYER(ID, NAME, AGE)       VALUES('07', '박지성', 23);
데이터 수정		UPDATE 테이블명 SET 칼럼명 = 수정값 [WHERE절]; ex) UPDATE PLAYER SET NAME = '박지후'       WHERE NAME = '박지성'
데이터 삭제		DELETE FROM 테이블명 [WHERE절]; ex) DELETE FROM PLAYER       WHERE ID = '07';
데이터 조회	전체 조회	SELECT * FROM 테이블명;
	특정칼럼 조회	SELECT [DISTINCT] 칼럼명, 칼럼명.. FROM 테이블명;
	별명 지정	SELEXT 칼럼명 [AS] 별명 FROM 테이블명;

 

*유의사항

▷데이터 입력

  - 한번에 한건만 입력 가능

  - 칼럼명과 1:1로 매핑해서 값이 입력

  - 문자는 ' '

  - 값이 저장되지 않으면 NULL 입력됨

 

▷ 데이터 조회 - 별명 지정

  - AS " " : 쌍따옴표 사용 시, 공백, 특수문자, 대소문자 구분함

 

*INSERT INTO 테스트테이블 VALUES("")

: 오라클은 ""을 NULL로 처리하지만 SQL Server는 "" 그대로 처리

---

05 DCL

: 데이터베이스에 접근할 수 있는 유저 생성하고 권한을 제어하는 명령어다

DCL			Oracle
권한 부여	권한 부여		GRANT CREATE USER / CREATE SESSION / CREATE TABLE... TO 사용자명;
	유저 생성		CREATE USER 유저명 IDENTIFIED BY 비밀번호;
	DML 권한부여		GRANT SELECT / INSERT / UPDATE / DELETE ON 테이블명 TO 사용자명;
	타인에게 권한부여 가능	연쇄 철회	GRANT 권한 ON 테이블명 TO 사용자명 WITH GRANT OPTION;
		권한부여자만 철회	GRANT 권한 ON 테이블명 TO 사용자명 WITH ADMIN OPTION;
권한 취소			REVOKE 권한 FROM 사용자명;
ROLE	ROLE 생성		CREATE ROLE ROLE명;
	ROLE에 권한부여		GRANT 권한 TO ROLE명;
	유저에게 ROLE 부여		GRANT ROLE명 TO 유저명;

 

*유의사항

▷권한부여 - 타인에게 권한부여가능 - 연쇄철회

  - 다른 사람에게 권한을 부여할 수 있는 권한 부여

  - 객체에 사용

  - REVOKE하면 다른 사람에게 부여했던 권한도 같이 회수

 

▷권한부여 - 타인에게 권한부여가능 - 권한부여자만 철회

  - 다른 사람에게 권한 부여할 수 있는 권한을 부여

  - 시스템에 사용

  - REVOKE하면 권한 부여자의 권한만 회수되고 부여 받은 자의 권한은 회수하지 않는다.

 

1. ROLE : 권한들의 집합

유저를 생성하면 기본적으로 SESSION, TABLE, PROCEDUE 등의 많은 권한을 부여해야 한다.

유저와 권한 사이에 ROLE을 생성하고, 이 ROLE을 부여함으로써 많은 권한을 빠르고 정확하게 부여할 수 있게 된다.

 

2. Oracle 기본제공 ROLE

- CONNECT : 로그인 권한(ex. CREATE SESSION...)

- RESOURCE : 오브젝트 생성 권한 (ex. CREATE TABLE...)

---

06 TCL

: 트랜잭션은 분리될 수 없는 한 개 이상의 데이터베이스 조작을 가리킨다.

분할할 수 없는 최소 단위이기 때문에 전부 적용되거나 전부 취소되어야 한다. 

TCL		Oracle	SQL Server
트랜잭션 정의		SET TRANSACTION DML문장	BEGIN TRAN[SACTION] DML문장 ex) BEGIN TRANSACTION UPDATE PLAYER SET HEIGHT = 100;
트랜잭션 반영		COMMIT;	COMMIT TRAN[SACTION]
트랜잭션 되돌리기	전체 되돌리기	ROLLBACK;	ROLLBACK TRAN[SACTION]
	저장점까지 되돌리기	ROLLBACK TO 저장점명; ex) ROLLBACK TO FIRST;	ROLLBACK TRAN[SACTION] 저장점명;
저장점 생성		SAVEPOINT 저장점명; ex) SAVEPOINT FIRST;	SAVE TRAN[SACTION] 저장점명;

 

*유의사항

▷ 트랜잭션 정의

  - DCL(create, alter, drop...) 문장은 AUTO COMMIT된다.

 

▷ 트랜잭션 반영

  - DML문장 이후 커밋없이 DCL문장 실행되면 AUTO COMMIT된다.

 

▷ 트랜잭션 되돌리기 - 저장점까지 되돌리기

  - 저장점이 A 이후에 B가 존재할 때, ROLLBACK TO A 수행하면 저장점 B는 존재하지 않는다.

 

▷ 저장점 생성

  - ROLLBACK 사용 시, 전체 작업을 되돌리는 것이 아닌, 트랜잭션의 일부만 롤백할 수 있다.

  - 동일 이름으로 저장점을 정의했을 때는 나중에 정의한 저장점이 유효하다.

 

1. 트랜잭션의 특성 ACID

원자성 Atomicity	트랜잭션은 분리되지 않고 전부 적용 or 전부 취소
일관성 Consistency	트랜잭션 실행 전에 오류가 없었다면 실행 후에도 오류 없어야 한다.
고립성 Isolation	트랜잭션 실행 도중에 다른 트랜잭션의 영향을 받지 않는다 *잠금(LOCKING) : 트랜잭션 실행 도중 데이터에 다른 트랜잭션이 접근하지 못하도록 제한하는 것
지속성 Durability	트랜잭션에 성공한 내용은 DB에 영구적으로 저장된다.

 

2. 트랜잭션 이전/이후 데이터 상태

이전	이후
- 메모리 버퍼에만 영향을 받았기 때문에 이전 상태로 복구 가능 - 현재 사용자는 select문장으로 결과 확인 가능 - 다른 사용자는 기존의 결과(변경이 반영되지 않은) 확인 가능 - 변경된 행은 잠금(LOCKING)이 설정되어 다른 사용자 접근 불가	- DB에 변경사항 반영 - 변경 전의 데이터는 잃어버리게 된다 - 모든 사용자가 변경이 반영된 결과를 확인할 수 있다. - 변경된 행의 잠금(LOCKING)이 풀리고 다른 사용자가 접근할 수 있다.

 

3. Oracle과 SQL Server의 DDL, DML 트랜잭션 처리방식

	Oracle	SQL Server
DDL (create, alter, rename, drop, truncate)	직접 데이터베이스에 영향을 미치기 때문에 즉시 완료 된다. (AUTO COMMIT) AUTO COMMIT = false로 설정 불가	직접 데이터베이스에 영향을 미치기 때문에 즉시 완료된다. (AUTO COMMIT) AUTO COMMIT = false로 설정 가능
DML (insert, update, delete)	메모리 버퍼에 올려놓고 작업하기 때문에 사용자가 COMMIT, ROLLBACK 명령어를 직접 입력해야만 반영된다.	DML도 즉시 완료된다. (AUTO COMMIT) 오류가 발생하면 바동 ROLLBACK 처리 된다. AUTO COMMIT = false로 설정 가능

 

AUTO COMMIT	암시적 트랜잭션	명시적 트랜잭션
SQL Server의 기본방식 DDL, DML 수행 시 DBMS가 자동으로 COMMIT하고, 오류 발생 시 자동으로 ROLLBACK 수행	Oracle의 기본방식 DDL은 AUTO COMMIT이지만 DML은 사용자가 COMMIT, ROLLBACK 명령어를 입력해야 한다.	트랜잭션의 시작과 끝을 모두 명시 BEGIN TRANSACTION 명령문; COMMIT or ROLLBACK;

---

07 WHERE절

1. WHERE

: 조회하려는 데이터에 특정 조건 부여

SELECT ~
FROM ~
WHERE 조건식;

 

2. 연산자

연산자	Oracle	SQL Server
산술 연산자	( ) 괄호, * 곱하기,  / 나누기, + 더하기, - 빼기 *NULL과 수치연산은 NULL 리턴
합성연산자	'문자' || '문자' ex) SELECT AGE || '나이' FROM PLAYER;	'문자' + '문자'
	CONCAT(문자, 문자) ex) SELECT CONCAT(AGE, '나이') FROM PLAYER;
비교 연산자 SQL 연산자	=, >, <, >=, <= *NULL과 비교연산은 FALSE 리턴
	!= 같지 않다, ^= 같지 않다, <> 같지 않ㄴ다(ANSI/ISO 표준)
	칼럼명 [NOT] BETWEEN 작은값 AND 큰값 ex) 키 BETWEEN 170 AND 180
	칼럼명 [NOT] IN (값, 값, ...) *값 중 하나라도 일치하면 된다. ex) 소속 IN (인사팀, 회계팀)
	칼럼명 LIKE '와일드카드 이용한 비교문자열' *% : 0개 이상의 어떤 문자 / _ : 1개의 단일문자 *와일드카드 앞에 #붙이면 와일드카드가 아닌 문자열로 인식한다. ex) 제품코드에서 'A_A'가 포함된 문자열 제품코드 LIKE '%A#_A%'
	칼럼명 IS [NOT] NULL ex) 포지션 IS NULL
논리 연산자	AND(둘다 참이어야 함), OR (하나만 참) NOT(결과를 반전한다) *우선순위 : NOT > AND > OR ex) A이거나 B이고, C이다. (A or B) AND C

 

3. 부분 범위 처리 ~ 가짜칼럼 ROWNUM, TOP(N)

Oracle	SQL Server
SELECT ~ FROM ~ WHERE ROWNUM <= N;	SELECT TOP(N) [WITH TIES] FROM ~;
SELECT SAL FROM ~ (SELECT SAL FROM EMP ORDER BY SAL) WHERE ROWNUM < 4;	SELECT TOP(2) WITH TIES SAL FROM EMP ORDER BY SAL; //500, 300, 300
Oracle에서는 ORDER BY절이 같이 사용되면 정렬 전에 ROWNUM을 처리하게 되어 무작위 결과가 추출된다.	SQL Server에서는 ORDER BY와 WITH TIES를 같이 사용하면 공동순위도 같이 출력해준다.

 

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

SELECT TOP(2) WITH TIES ename, sal
FROM emp
ORDER BY sal DESC;

>> KING 5000 SCOTT 3000 FORD 3000

---

DUAL 테이블(Oracle)

사용자 테이블이 필요 없는 SQL문장을 실행할 때 사용한다.

SELECT FROM 형식을 갖추기 위한 일종의 DUMMY테이블이다.

SELECT LENGth('SQL Expert') FROM DUAL;

//결과
//LENGTH('SQL Expert')
//------------------
//                10

 

*SQL Server의 경우엔 SELECT로도 SQL문장이 수행 가능하기 때문에 DUAL테이블이 없다

SELECT len('SQL Expert');

//결과
//(열 이름 없음)
//------------------
//                10

---

08 함수

입력값이 아무리 많아도 출력은 1가지만 나온다.

 

1. 단일 행 함수 : 입력값 단일행

단일행 함수		Oracle	SQL Server
문자형 함수	대/소문자	LOWER(문자열), UPPER(문자열)
	아스키	ASCII(문자 or 숫자)
		CHR(ASCII번호)	CHAR(ASCII번호)
	문자연결	CONCAT(문자, 문자) *합성연산자 '||'(Oracle)나 '+'(SQL Server)와 동일하다.
	특정 문자반환	SUBSTR(문자, M[, N]) *N 생략되면 마지막까지	SUBSTRING(문자, M, N) *N 생략되면 마지막까지
	문자열 개수	LENGTH	LEN
	문자제거	LTRIM(문자, [, 지정문자]) *문자 왼쪽부터 지정문자까지 삭제(지정문자 생략 시, 공백값 처리) ex) LTRIM('xxYxzYzz', 'x') = 'YxzYzz'
		RTRIM(문자, [, 지정문자]) *문자 오른쪽부터 지정문자까지 삭제(지정문자 생략 시, 공백값 처리) *CHAR와 VARCHAR 데이터 유형을 비교할 때 용이하게 사용된다. ex) RTRIM('xxYxzYzz', 'z') = 'xxYxzY'
		TRIM([leading, trailing, both] 지정문자 FROM 문자) *leading 입력 시 LTRIM과, tailing 입력 시 RTRIM과 같다 *leading, tailing 생략 시 both로 지정됨 *문자 양쪽에서 지정문자 삭제 ex) TRIM('x' FROM 'xxYxzYxx') = 'YxzY'	TRIM(문자) //공백만 제거가능

단일형 함수		Oracle	SQL Server
숫자형 함수	절대값 반환	ABS()
	양/음/0 반환	SIGN() *음수 -1 /  0 0 / 양수 1
	나머지 값 반환	MOD(값, 나누는 수) *%연산자로도 대체 가능 (ex. 7%3)
	올림	CEIL() *올림수 반환, 음수면 소수점 없앰 ex) CEIL(38.12) = 39 CEIL(-38.12) = -38	CEILING() *올림수 반환, 음수면 소수점 없앰
	내림	FLOOR() *내림수 반환, 음수면 정수-1 반환 ex) FLOOR(38.12) = 38 FLOOR(-38.12) = -39
	반올림	ROUND(숫자[, M]) *M = 반올림 후 표시할 소수점 개수(생략되면 디폴트는 0) ex) ROUND(38.52, 1) = 38.5 ROUND(38.52, -1) = 40
	버림	TRUNC(숫자[, M]) *M = 버림 후 표시할 소수점 개수(생략 시, 0) ex) TRUNC(38.52, 1) = 38.5 TRUNC(38.52, -1) = 30	제공되지 않음

단일행 함수		Oracle	SQL Server
날짜형 함수	현재 날짜 시각	SYSDATE	GETDATE()
	날짜 출력	EXTRACT('YEAR'|'MONTH'|'DAY' FROM d)	DATEPART('YEAR'|'MONTH'|'DAY', d)
반환형 함수	숫자로 반환	TO_NUMBER(문자열)
	문자로 반환	TO_CHAR(숫자|날짜 [, format]) *숫자나 날짜를 format에 맞추어 반환 *format은 'YYYY/MM/DD HH[24]:MU:SS'와 같이 입력할 수 있다. ex) TO_CHAR(SYSDATE, 'YYYY/MM/DD HH:MI:SS') = 2021/11/05 02:22:06
	날짜로 반환	TO_DATE(문자열 [, format])
NULL 함수	대체값 출력	NVL(대상, NULL일 때 대체값) *대상이 NULL이면 대체값 출력 *공집합에 사용해도 공집합이 출력	ISNULL(대상, NULL일 때 대체값)
		NULLIF(표현식1, 표현식2) *표현식1이 표현식2와 같으면 NULL 출력 *표현식1이 표현식 2와 같지 않으면 표현식1 출력
	최초로 NULL아닌 것 출력	COALESCE(표현식1, 표현식2,...) *표현식에서 NULL이 아닌 최초의 표현식 출력 *모든 표현식이 NULL이라면 NULL출력

*'201501' = TO_CHAR(서비스종료일시, 'YYYYMM') : 서비스종료일시의 년과 월이 2015년 1월달인 것들이 해당

*TO_DATE('201501', 'YYYYMM') = 서비스종료일시 : 지정되지 않은 DD는 01로 지정되어 2015년 1월 1일이 서비스종료일시와 같은 것들을 찾아낸다.

 

Q. 사원(EMP)테이블의 입사일자에서 년, 월, 일 데이터를 각각 출력한다.

Oracle SQL >>

EXTRACT(MONTH FROM hiredate) 입사원,
EXTRACT(DAY FROM hiredate) 입사일
FROM EMP;

 

SQL >>

SELECT ename, hiredate, DATEPART(YEAR, hiredate) 입사년도,
DATEPART(MONTH, hiredate) 입사월, 
DATEPART(DAY, hiredate) 입사일
FROM EMP;

SELECT ename
    , hiredate
    , YEAR(hiredate) 입사년도
    , MONTH(hiredate) 입사월
    , DAY(hiredate) 입사일
FROM EMP;

 

Q. 사원 테이블에서 MGR와 7698이 같으면 NULL을 표시하고, 같지 않으면 MGR를 표시한다

SELECT ename, empno, mgr, NULLIF(mgr, 7698)
FROM emp;

 

Q. 사원 테이블에서 커미션(COMM)을 1차 선택값으로, 급여(SAL)를 2차 선택값으로 선택하되 두 칼럼 모두 NULL인 경우는 NULL로 표시한다. 

SELECT ename, comm, sal, COALESCE(comm, sal) coal
FROM emp;

 

2. 데이터 변환

▷ 데이터 유형 변환의 종류

- 명시적 형 변환 : 변환형 함수 이용해 데이터 타입 변환

- 암시적 형 변환 : DBMS가 자동으로 데이터 타입 변환

 

▷ 단일행 변환형 함수의 종류

Oracle	SQL Server
TO_NUMBER(문자열)	CAST(expression AS data_type [(length)]) CONVERT(data_type[(length)], expression [, style]) : expression을 목표 데이터 유형으로 변환
TO_CHAR(숫자|날짜 [, FORMAT])
TO_DATE(문자열 [, FORMAT])

 

 

3. 조건문

CASE 표현		Oracle	SQL Server
다중조건 함수	CASE 표현식	SIMPLE CASE EXPRESSION CASE 칼럼 or 표현식     WHEN 칼럼 or 표현식의 비교값 THEN 참일 경우 값     ELSE 거짓일 경우 값 END *CASE 다음 바로 칼럼이나 표현식 사용 *DECODE 함수와 동일 ex) CASE LOCAL           WHEN 'A' THEN 'EAST'           WHEN 'B' THEN 'CENTER'           ELSE 'ETC'       END
		SEARCHED CASE EXPRESSION CASE     WHEN 조건식 THEN 참일 경우 값     ELSE 거짓일 경우 값 END *CASE 다음에 표시하지 않고, WHEN절에서 조건 적용 ex) CASE           WHEN SAL > 2000 THEN 'HIGH'           WHEN SAL > 1000 THEN 'MID'        ELSE 'LOW'        END
	디코드 함수	DECODE(대상, 조건1, 값1, 조건2, 값2,..., 디폴트 값) *대상이 조건1에 해당하면 값1 출력 *맞는 조건 없으면 디폴트값 출력 ex) DECODE(gender, 'M', 남자, 'F', '여자', '기타')	지원X

 

Q. 사원정보에서 급여가 3000 이상이면 상등급, 1000 이상이면 중등급으로, 1000 미만이면 하등급으로 분류하라

SELECT ename,
CASE WHEN sal >= 3000 THEN 'HIGH' WHEN sal >= 1000 THEN 'MID ELSE 'LOW' END
AS salary_grade FROM EMP;

---

09 GROUP BY, HAVING 절

1. 집계 함수

: 여러 생들의 그룹이 모여서 그룹 당 하나의 결과를 돌려주는 함수

(COUNT, SUM, AVG, MAX, MIN, STDDEV, VARIAN....등)

- 행의 그룹을 만들어주는 GROUP BY와 많이 사용됨

- GROUP BY 이후에 실행되는 HAVING, SELECT, ORDER BY절에서만 사용 가능(WHERE절 사용 불가)

- GROUP BY절에 정의하지 않은 칼럼도 집계함수를 사용하면 HAVING, SELECT, ORDER BY절에서 사용할 수 있다.

SELECT job
FROM emp
GROUP BY job
ORDER BY MAX(empno), SUM(sal);
-- 성공
-- 이미 job으로 그룹화되어 있어 GROUP BY보다 수행이 늦은
-- HAVING, SELECT, ORDER BY에서 집계함수로 다른 칼럼 그룹화할 수 있다.

SELECT job, MAX(empno)
FROM emp
GROUP BY job
ORDER BY MAX(empno), SUM(sal);
-- 성공
-- job도 그룹화되었고, empno도 집계함수로 그룹화되어 있다.

SELECT job, empno
FROM emp
GROUP BY job
ORDER BY MAX(empno), SUM(sal);
-- 실패
-- empno가 그룹화되지 않은 칼럼이기 때문에 실패

 

*집계함수 사용 시, 전체가 하나의 그룹이 되는 경우에는 GROUP BY절 없이 단독 사용 가능

SELECT COUNT(*) "전체 행 수", COUNT(HEIGHT) "키 건수"

 

2. GROUP BY

: 행들을 소그룹화

: SELECT ~ FROM ~ WHERE ~ GROUP BY 칼럼 HAVING 조건

*ALIAS 별명 사용 불가

 

Q. K-리그 선수들의 포지션 별 평균 키는 어떻게 되는가?

SELECT position 포지션
	, COUNT(*) 인원수
    , COUNT(height) 키대상
    , MAX(height) 최대키
    , MIN(height) 최소키,
    , ROUND(AVG(height), 2) 평균키
FROM player
GROUP BY position;

 

3. HAVING

: GROUP BY로 묶인 그룹들에 조건을 적용한다.

(WHERE절에는 개체 수준의 조건 적용)

 

Q. 평균키가 180센티미터 이상인 정보만 표시

SELECT position 포지션, ROUND(AVG(height), 2) 평균키
FROM player
GROUP BY position
HAVING AVG(height) >= 180;

 

*NULL이 있을 경우 연산에서 제외 → 굳이 NVL함수로 NULL을 0으로 만들어줄 필요X

---

10 ORDER BY 절

1. ORDER BY

: 데이터들을 특정칼럼 기준으로 정렬해 출력하게 해준다.

SELECT ~ FROM ~ WHERE ~ GROUP BY ~ HAVING ~ ORDER BY 칼럼명 ASC/DESC;

*ASC : 기본값

 

- ORDER BY절에 별명 사용가능

SELECT age 나이
FROM player
ORDER BY 나이;

 

- 조회하는 칼럼의 순서번호를 사용해도 가능하다.

SELECT age 나이, name 이름
FROM player
ORDER BY 2;

 

- 조회하지 않는 칼럼으로 정렬할 수 있다.

SELECT age, name
FROM player
ORDER BY id;

 

Q. player테이블에서 선수명과 팀명은 오름차순, 연봉은 내림차순으로 조회하는 SQL로 바른 것은?

① SELECT 선수명, 팀명, 연봉 FROM ORDER BY 선수명 DESC, 팀명 DESC, 연봉 ASC
② SELECT 선수명, 팀명, 연봉 FROM ORDER BY 선수명 ASC, 팀명 ASC , 연봉
③ SELECT 선수명, 팀명, 연봉 FROM ORDER BY 선수명 ASC, 팀명, 3 DESC     //별명, 순서번호 혼합해서 사용 가능
④ SELECT 선수명, 팀명, 연봉 FROM ORDER BY 선수명, 팀명, DESC 연봉

 

Q. 선수 테이블에서 선수들의 이름, 포지션, 백넘버 출력하는데 사람 이름을 내림차순으로 정렬하여 출력, 키가 NULL인 데이터는 제외

SELECT player_name 선수명, position 포지션, back_no 백넘버 FROM player
WHERE back_no IS NOT NULL
ORDER BY 1 DESC;

 

2. Oracle과 SQL Server의 NULL 취급

Oracle	SQL Server
NULL을 가장 큰 값으로  취급한다.	NULL을 가장 작은 값으로 취급한다.

---

11 조인

1. 조인

: 여러 테이블을 연결 또는 결합해 데이터 출력

: 일반적으로 PK나 FK의 연관성에 의해 성립

 

2. 등가 조인

: 두 테이블의 칼럼 값이 정확히 일치하는 경우, 대부분 PK와 FK 관계를 기반으로 함

SELECT 칼럼s
FROM 테이블1 A, 테이블2 B
WHERE A.칼럼명 = B.칼럼명;

SELECT 칼럼s
FROM 테이블1 A 
INNER JOIN 테이블2 B
WHERE A.칼럼명 = B.칼럼명;

 

3. 비등가 조인

: 두 테이블의 칼럼 값이 정확하게 일치하지 않는 경우, 부등호나 BETWEEN 연산자를 통해 조인

 

Q. 선수들 별로 홈그라운드 경기장이 어디인지 출력하고 싶다고 했을 때, 선수 테이블과 운동장 테이블이 서로 관계가 없으므로 중간에 팀 테이블이라는 서로 연관관계가 있는 테이블을 추가해 세 개의 테이블을 JOIN해야 원하는 데이터를 얻을 수 있다. 

SELECT p.player_name 선수명
    , p.position 포지션
    , t.region_name 연고지
    , t.team_name 팀명
    , s.stadium_name 구장명
FROM player p, team t, stadium s
WHERE p.team_id = t.team_id AND t.stadium_id = s.stadium_id
ORDER BY 선수명;

SELECT p.player_name 선수명
    , p.position 포지션
    , t.region_name 연고지
    , t.team_name 팀명
    , s.stadium_name 구장명
FROM player p 
INNER JOIN team t
ON p.team_id = t.team_id
INNER JOIN stadium s
ON t.stadium_id = s.stadium_id
ORDER BY 선수명;