[SQLD] 2과목 SQL 기본 및 활용 - SQL 기본

🚀 관계형 데이터베이스(Relation Database)

1️⃣ 관계형 데이터베이스(Relation Databse)

❶  관계형 데이터베이스의 등장

- 1960년대 초반에는 파일 시스템 기반의 데이터 관리 방식이 사용되었지만, 데이터 중복과 일관성 문제로 인해 비효율적이었다.

- 1970년대 E.F.Codd가 제안한 관계형 데이터 모델이 등장하면서, 데이터를 표 형식(테이블)으로 관리하는 방식이 널리 사용되기 시작했다.

- 관계형 데이터베이스는 릴레이션(Relation)과 릴레이션의 조인 연산을 통해서 합집합, 교집합, 차집합 등을 만들 수 있다.

 

❷ 데이터베이스와 데이터베이스 관리 시스템의 차이점

- 데이터베이스(DB, Database): 데이터를 체계적으로 저장하고 관리하는 공간

- 데이터베이스 관리 시스템(DBMS, Databse Management System): 데이터베이스를 효율적으로 운영하고 관리하기 위한 소프트웨어

 

비교	계층형 DB	네트워크형 DB	관계형 DB
구조	트리(Tree)	그래프(Graph)	테이블(Table)
관계	1:N (부모-자식)	N:M (다대다 가능)	N:M (JOIN 활용)
유연성	낮음	중간	높음
사용 예	조직도, 파일 시스템	N:M (다대다 가능)	금융, 웹, ERP

 

❸ 관계형 데이터베이스 집합 연산과 관계 연산

 

👉 집합 연산

집합 연산	설명
합집합(Union)	두 개의 테이블에서 중복을 제거하고 모든 데이터를 반환
차집합(Difference)	첫 번째 테이블에만 존재하는 데이터를 반
교집합(Intersection)	두 개의 테이블에 공통으로 존재하는 데이터를 반환
곱집합(Cartesian Product)	두 개의 테이블에서 모든 가능한 조합을 생성

 

👉 관계 연산

관계 연산	설명
선택 연산(Selection)	특정 조건을 만족하는 행(Row) 선택 / 릴레이션에서 조건에 맞는 행(튜플)만을 조회
투영 연산(Projection)	특정 칼럼(Column)만 선택 / 릴레이션에서 조건에 맞는 속성만을 조회
결합 연산(Join)	두 개 이상의 테이블을 연결하여 데이터 조회
나누기 연산(Division)	특정 조건을 만족하는 데이터를 필터링하여 변환

 

 

2️⃣ 테이블(Table)의 구조

테이블의 구조	설명
테이블(Table)	관계형 데이터베이스에서 데이터를 저장하는 기본 단위
속성(Attribute)	테이블의 열(Column), 데이터의 특징을 정의, 데이터를 저장하기 위한 필드(Field)
튜플(Tuple)	테이블의 행(Row), 한 개의 데이터 레코드를 의미
기본 키(Primary Key)	각 행을 유일하게 식별할 수 있는 칼럼 - 유일성(Unique), 최소성, Not Null, 대표성
외래 키(Foreign Key)	다른 테이블의 기본 키를 참조하는 칼럼, 결합 연산(Join)을 하기 위해서 사용

 

 

 

---

🚀 SQL 종류

1️⃣ SQL(Structured Query Language)

- SQL은 관계형 데이터베이스에서 데이터 구조를 정의, 데이터를 조작 · 제어 등을 할 수 있는 정차형+비절차형 언어

- ANSI/ISO 표준을 준수하기 때문에 데이터베이스 관리 시스템이 변경되어도 그대로 사용할 수 있다.

 

👉 SQL 표준

SQL 표준	설명
SQL-86	최초의 SQL 표준
SQL-89	소폭 수정된 버전
SQL-92	대부분의 RDBMS에서 지원하는 버전
SQL:1999	트랜잭션 제어 및 트리거 기능 추가
SQL:2003	XML 데이터 처리 기능 추가

 

 

2️⃣ SQL 종류

-  SQL은 데이터 정의, 데이터 조작, 데이터 제어 등의 기능을 지원

 

❶ SQL의 종류

종류	설명
DDL(Data Definition Language)	- 데이터 구조를 정의하는 명령어 - CREATE, ALTER, DROP, RENAME, TRUNCATE
DML(Data Manipulation Language)	- 데이터를 입력, 수정, 삭제, 조회하는 조작 명령어 - INSERT, UPDATE, DELETE, SELECT
DCL(Data Control Language)	- 사용자에게 권한을 부여하거나 회수하는 접근 제어 명령어 - GRANT, REVOKE,
TCL(Transaction Control Language)	- 트랜잭션을 제어하는 명령어 - COMMIT, ROLLBACK, SAVEPOINT

 

 

❷ 트랜잭션(Transaction)

- 트랜잭션은 데이터베이스에서 하나의 논리적 작업 단위이며, 트랜잭션 단위로 데이터가 처리됨.

트랜잭션 특성	설명
원자성(Atomicity)	- 트랜잭션은 연산의 전부가 실행되거나 전혀 실행되지 않아야 한다(ALL OR NOTHING) - 즉, 트랜잭션의 처리가 완전히 끝나지 않았을 경우는 실행되지 않은 상태와 같아야 한다.
일관성(Consistency)	- 트랜잭션 실행 결과로 데이터베이스의 상태가 모순도디지 않아야 한다. - 트랜잭션 실행 후에도 일관성이 유지되어야 한다.
고립성(Isolation)	- 트랜잭션 실행 중에 생성하는 연산의 중간결과는 다른 트랜잭션이 접근할 수 없다. - 즉, 부분적인 실행 결과를 다른 트랜잭션이 볼 수 없으며 서로 간섭할 수 없다.
영속성(Durability)	- 트랜잭션이 완료되면 데이터가 영구적으로 저장되어야 한다.

 

 

 

3️⃣ SQL문의 실행 순서

- SQL문(DDL, DML, DCL 등)은 3단계를 걸쳐서 실행된다.

 

👉 SQL 실행 순서

SQL 실행 순서	설명
파싱(Parsing)	- SQL문의 문법을 확인하고 구문 분석 - 구문 분석한 SQL문은  Library Cache에 저장
실행(Execution)	- 옵티마이저(Optimizer)가 수립한 실행 계획에 따라 SQL을 실행
인출(Fetch)	-  데이터를 읽어서 전송; 실행 결과를 사용자에게 반환

 

 

 

4️⃣ Select문

❶ SELECT문 사용

- 테이블에 입력된 데이터를 조회하는 기본적인 SQL문장

- SELECT문은 특정 칼럼이나 특정 행만을 조회

 

👉 SELECT 문법

SELECT 컬럼명
FROM 테이블명 
WHERE 조건;

SELECT문 문법	설명
SELECT *	모든 칼럼을 출력한다.
FROM EMP	FROM절에는 테이블명을 쓴다; EMP 테이블을 지정한다.
WHERE 사원번호 = 1000	EMP 테이블에서 사원번호가 1000번인 행을 조회한다; 조건문을 지정한다.

 

👉 SELECT 칼럼 지정

사용 예제
SELECT EMPNO, ENAME FROM EMP;	EMP 테이블의 모든 행에서 EMPNO와 ENAME 칼럼만 출력한다.
SELECT * FROM EMP;	EMP 테이블의 모든 칼럼과 모든 행을  조회한다.
SELECT ENAME || '님' FROM EMP;	- EMP 테이블의 모든 행에서 ENAME 칼럼을 조회한다. - 단, ENAME 칼럼 뒤에 '님'이라는 문제를 결합한다.

 

 

❷  ORDER BY를 사용한 정렬

- 데이터를 오름차순(Ascending) 혹은 내림차순(Descending)으로 출력한다.

- ORDER BY가 정렬을 하는 시점은 모든 실행이 끝난 후에 데이터를 출력하기 바로 전이다.

- 정렬을 하기 때문에 데이터베이스 메모리를 많이 사용, 대량의 데이터 정렬시 성능 저하 발생

- 정렬을 회피하기 위해 인덱스(Index)를 생성할 때 사용자가 원하는 형태로 오름차순 혹은 내림차순으로 생성

- 디폴드 값은 오름차순

SELECT * 
FROM 테이블명 
ORDER BY 컬럼명 [ASC|DESC];

 

 

❸ Index를 사용한 정렬 회피

- 정렬은 데이터베이스에 부하를 주므로, 인덱스를 사용해서 ORDER BY를 회피할 수 있다.

 

 

❹ DISTINCT와 Alias

• DISTINCT: 중복 제거

SELECT DISTINCT 컬럼명 
FROM 테이블명;

• Alias: 별칭 사용

SELECT 컬럼명 AS 별칭 
FROM 테이블명;

 

 

---

🚀 WHERE문 사용

1️⃣ WHERE문이 사용하는 연산자

- 비교 연산자, 부정 비교 연산자, 논리 연산자, SQL 연산자, 부정 SQL 연산자

✅ 비교 연산자

연산자	설명
=	같다
>	크다
<	작다
>=	크거나 같다
<=	작거나 같다

 

✅ 부정 비교 연산자

부정 비교 연산자	설명
!=	같지 않다
^=	같지 않다
<>	같지 않다
NOT 칼럼명 =	같지 않다
NOT 칼럼명 >	크지 않다

 

✅ 논리 연산자

연산자	설명
AND	조건을 모두 만족해야 참(True)이 된다.
OR	조건 중 하나만 만족해도 참(True)이 된다.
NOT	참이면 거짓(False)으로 바꾸고 거짓이면 참(True)으로 바꾼다.

 

✅ SQL 연산자

SQL 연산자	설명
LIKE '%비교 문자열%'	비교 문자열을 조회한다. '%'는 모든 값을 의미한다.
BETWEEN A AND B	A와 B 사이의 값을 조회한다.
IN (list)	OR을 의미하며 list 값 중에 하나만 일치해도 조회된다.
IS NULL	NULL 값을 조회한다.

 

✅ 부정 SQL 연산자

부정 SQL 연산자	설명
NOT BETWEEN A AND B	A와 B 사이에 해당하지 않는 값을 조한다.
NOT IN (list)	list와 불일치한 것을 조회한다.
IS NOT NULL	NULL 값이 아닌 것을 조회한다.

 

2️⃣ LIKE문 사용

 

- LIKE문은 와일드카드를 사용해서 데이터를 조회할 수 있다.

와일드카드	설명
%	- 어떤 문자를 포함한 모든 것을 조회한다. - 예를 들어 '조%'는 '조'로 시작하는 모든 문자를 조회한다.
_(underscore)	한 개의 단일 문자를 의미한다.

 

- A%: A로 시작하는 모든 데이터, A_: A로 시작하고 하나의 글자만 더 있는 것(예:  AB, AC, AA)

SELECT * FROM 테이블명 WHERE 컬럼명 LIKE 'A%';

 

3️⃣ BETWEEN문 사용

- 지정된 범위에 있는 값을 조회

- BETWEEN 10 AND 50은 10과 20을 포함한 10과 20 사이의 값을 조회

SELECT * FROM 테이블명 WHERE 컬럼명 BETWEEN 10 AND 50;

 

4️⃣ IN문 사용

 

- IN문은 'OR'의 의미를 가지고 있어서 하나의 조건만 만족해도 조회가 된다.

SELECT * FROM 테이블명 WHERE 컬럼명 IN (컴퓨터공학, 통계학, 경영학);

 

5️⃣ NULL 값 조회

 

- NULL은 모르는 값, 값의 부재를 의미

- 숫자 혹은 날짜를 더하면 NULL

- 어떤 값과 비교를 할 때, '알 수 없음'을 반환

 

👉 NULL 관련 함수

NULL함수	설명
NVL(칼럼, 대체값) ISNULL(칼럼, 대체값)	NULL이면 대체값 반환
NULLIF(A, B)	A와 B가 같으면 NULL, 값지 않으면 A 반환
COALESCE(A, B, C, ...)	NULL이 아닌 최초의 인자 값을 반환

 

 

---

🚀 GROUP 연산

1️⃣ GROUP BY 문

- 특정 컬럼을 기준으로 데이터를 그룹화하여 집계 연산을 수행할 때 사

- GROUP BY를 사용하면 SUM(), COUNT(), AVG() 등의 집계 함수를 함께 활용

SELECT DEPTNO, AVG(SAL) AS AVG_SAL
FROM EMP
GROUP BY DEPTNO;

 

 

2️⃣ HAVING문 사용

- WHERE는 개별 행을 필터링하고, HAVING은 그룹화된 데이터를 필터링

- HAVING은 집계 함수와 함께 사용

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 5000;

 

 

3️⃣ 집계 함수 종류

집계 함수	설명
COUNT()	개수 반환
SUM()	합계 반환
AVG()	평균값 반환
MAX(), MIN()	최댓값, 최솟값 반환
STDDEV()	표준 편차 계산
VARIANCE()	분산 계산

 

4️⃣ COUNT 함수

 

- COUNT(*)는 NULL 포함하여 개수를 반환함.

- COUNT(컬럼명)은 NULL을 제외한 개수를 반환함.

SELECT COUNT(*) FROM EMP;
SELECT COUNT(DEPTNO) FROM EMP;

 

 

5️⃣ GROUP BY  사용 예제

❶ 부서별(DEPNO), 관리자별(MGR) 급여평균 계산

- 부서별, 관리자별 급여평균이므로 GROUP BY에 부서와 관리자를 추가한다.

- 평균을 계산하기 위해서 SELECT문에 AVG함수를 사용해야 한다.

SELECT DEPTNO, MGR, AVG(SAL)
FROM EMP
GROUP BY DEPTNO, MGR;

 

❷ 직업별(JOB) 급여합계 중에 급여(SAL)합계가 1000 이상인 직업

- 직업별 급여합계이므로 GROUP BY에 JOB을 포함시키고 급여합계가 1000 이상만 조회해야하므로 HAVING구에 조건을 넣어야 한다.

SELECT JOB, AVG(SAL)
FROM EMP
GROUP BY JOB
HAVING AVG(SAL) >= 1000;

 

❸ 사원번호 1000~1003번의 부서별 급여합계

- 사원번호 1000번에서 1003번까지 조회를 해야 하므로 WHERE문에 조건을 넣어야 한다.

- 그리고 부서별 합계이므로 GROUP BY에 DEPTNO를 사용하고 SELECT문에 SUM 함수를 사용한다.

SELECT DEPTNO, SUM(SAL)
FROM EMP
WHERE EMPNO BETWEEN 1000 AND 1003
GROUP BY DEPTNO;

 

 

---

🚀 SELECT문 실행 순서

5️⃣ SELECT column_name
1️⃣ FROM table_name
2️⃣ WHERE condition
3️⃣ GROUP BY column_name
4️⃣ HAVING condition
6️⃣ ORDER BY column_name;

1️⃣ FROM: 테이블을 선택함.
2️⃣ WHERE: 조건을 만족하는 행을 필터링함.
3️⃣ GROUP BY: 데이터를 그룹화함.
4️⃣ HAVING: 그룹화된 결과에서 특정 조건을 적용함.
5️⃣ SELECT: 최종 결과에 필요한 컬럼을 선택함.
6️⃣ ORDER BY: 결과를 정렬함.

 

 

 

---

🚀 명시적(Explicit) 형변환과 암시적(Implicit) 형변환

- 형변환: 두 개의 데이터의 데이터 타입(형)이 일치하도록 변환하는 것

- 예를 들어 숫자와 문자열의 비교, 문자열과 날짜형의 비교와 같이 데이터 타입이 불일치할 때 발생

 

1️⃣ 형변환 함수

형변환 함수	설명
TO_NUMBER(문자열)	문자열을 숫자로 변환한다.
TO_CHAR(숫자 혹은 날짜, [FORMAT])	숫자 혹은 날짜르르 지정된 FORMAT의 문자로 변환한다.
TO_DATE(문자열, FORMAT)	문자열을 지정된 FORMAT의 날짜형으로 변환한다.

 

2️⃣ 암시적 형변환

 

- SQL이 자동으로 데이터 타입을 변환하는 경우 발생함.

- 문자열과 숫자를 연산할 때 자동 변환됨.

SELECT '123' + 1;  -- 124 반환 (문자열이 숫자로 변환됨)

 

 

---

🚀내장형 함수(BUILT-IN Function)

1️⃣ 내장형 함수

- 데이터베이스에서 제공하는 기본적인 함수들을 의미

- 문자열, 숫자, 날짜, 형변환 함수 등

 

2️⃣ DUAL 테이블

- Oracle 데이터베이스에 의해서 자동으로 생성되는, 단일 행을 반환하는 테이블

- 사용자가 임시로 사용할 수 있는 테이블로 내장형 함수를 실행할 때도 사용할 수 있다.

- SELECT 1 FROM DUAL; 같은 형태로 사용함

 

3️⃣ 내장형 함수의 종류

- 문자열, 날짜형, 숫자형 함수

- ASCII 함수: 대문자 A를 기준으로 A(65), B(66), C(67) 등의 값

- SUBSTR 함수는 지정된 위치의 문자열을 자르는 함수, LENGTH 함수, LEN 함수는 문자열 길이 계산

- LTRIM 함수를 사용하면 문자열의 왼쪽 공백 제거

- 함수 중첩 가능, 예) LENGTH(LTRIM(' ABC'))

 

 

[Oracle 데이터베이스에서 제공하는 함수들]

❶ 문자열 함수

문자열 함수	설명
ASCII(문자)	문자 혹은 숫자를 ASCII 코드값으로 변환한다.
CHR/CHAR(ASCII 코드값)	- ASCII 코드 값을 문자로 변환하ㅏㄴ다. - 오라클은 CHR, MSSQL, MYSQL은 CHAR 사용
SUBSTR(문자열, m, n)	문자열에서 m번째 위치부터 n개를 자른다.
CONCAT(문자열1, 문자열2)	- 문자열1번과 문자열2번을 결합한다.
LOWER(문자열)	영문자를 소문자로 변환한다.
UPPER(문자열)	영문자를 대문자로 변환한다.
LENGTH(), LEN()	공백을 포함해서 문자열의 길이를 알려준다.
LTRIM(문자열, 지정문자)	- 왼쪽에서 지정된 문자를 삭제한다. - 지정된 문자를 생략하면 공백을 삭제한다.
RTRIM(문자열, 지정문자)	- 오른쪽에서 지정된 문자를 삭제한다. - 지정된 문자를 생략하면 공백을 삭제한다.
TRIM(문자열, 지정문자)	- 왼쪽 및 오른쪽에서 지정된 문자를 삭제한다. - 지정된 문자를 생략하면 공백을 삭제한다.

 

❷ 날짜형 함수

날짜형 함수	설명
SYSDATE	오늘의 날짜를 날짜 타입으로 알려준다.
EXTRACT(YEAR FROM SYSDATE)	날짜에서 년, 월, 일을 조회한다.

 

❸ 숫자형 함수

숫자형 함수	설명
ABS(숫자)	절대값을 돌려준다.
SIGN(숫자)	양수, 음수, 0을 구별한다.
MOD(숫자1, 숫자2)	- 숫자1을 숫자2로 나누어 나머지를 계산한다. - %를 사용해도 된다.
CEIL/CEILING(숫자)	숫자보다 크거나 같은 최소의 정수를 돌려준다.
FLOOR(숫자)	숫자보다 작거나 같은 최대의 정수를 돌려준다.
ROUND(숫자, m)	- 소수점 m 자리에서 반올림한다. - m의 기본값(Default Value)은 0이다.
TRUNC(숫자, m)	- 소수점 m 자리에서 절삭한다. - m의 기본값(Default Value)은 0이다.

 

 

 

---

🚀DECODE와 CASE문

1️⃣DECODE

- DECODE문으로 IF문을 구현할 수 있다. 특정 조건이 참이면 A, 거짓이면 B로 응답한다.

 

SELECT DECODE(STAT, 'Y', '1', 'N', '0', 'Unknown') AS STAT_TXT
FROM EMP;

- STAT이 'Y'면 '0' 반환, STAT이 'N'이면 '1' 반환, 그 외의 값이면 'Unknown'

- 간단 예) DECODE(EMPNO, 1000, 'TRUE', 'FALSE') 

 

2️⃣ CASE문

 

- IF-ELSE와 같은 조건문을 SQL에서 사용할 수 있도록 지원

SELECT EMPNO,
       CASE 
           WHEN SAL > 5000 THEN 'HIGH SAL'
           WHEN SAL BETWEEN 3000 AND 5000 THEN 'MED SAL'
           ELSE 'LOW SAL'
       END AS SAL_INFO
FROM EMP;

 

 

---

🚀WITH구문

- WITH구문은 서브쿼리(Subquert)를 사용해서 입시 테이블이나 뷰처럼 사용할 수 있는 구문

- 서브쿼리 블록에 별칭(Alias)을 지정할 수 있다.

- 옵티마이저는 SQL을 인라인 뷰나 임시 테이블로 판단한다.

- CTE(Common Table Expression)

WITH EMPSAL AS (
    SELECT EMPNO, SAL
    FROM EMP
    WHERE SAL > 5000
)
SELECT * FROM EMPSAL;