[정처기 필기] 「3」 | SQL 응용 - (3.3) DML - SELECT 1, 2
[정처기 필기] 「3」 | SQL 응용 - (3.3) DML - SELECT 1, 2
「1」 소프트웨어 설계
「2」 소프트웨어 개발
> 「3」 데이터베이스 구축
- 논리 데이터베이스 설계, 물리 데이터베이스 설계, > SQL 응용, SQL 활용, 데이터 전환
「4」 프로그래밍 언어 활용
「5」 정보시스템 구축 관리
1 SQL 개념
2 DDL
3 DCL
4 DML
> 5 DML - SELECT - 1
> 6 DML - SELECT - 2
7 DML - JOIN
5. DML - SELECT - 1
일반 형식
SELECT [PREDICATE] [테이블명.]속성명 [AS 별칭][, [테이블명.]속성명, ...]
--[, 그룹함수(속성명) [AS 별칭]]
--[, Window함수 OVER (PARTITION BY 속성명1, 속성명2, ...
--ORDER BY 속성명3, 속성명4, ...)]
FROM 테이블명[, 테이블명, ...]
[WHERE 조건]
--[GROUP BY 속성명, 속성명, ...]
--[HAVING 조건]
[ORDER BY 속성명 [ASC | DESC]];
- SELECT절
: PREDICATE - 불러올 튜플 수를 제한할 명령어
> ALL : 모든 튜플을 검색할 때 지정, 주로 생략
> DISTINCT : 중복된 튜플이 있으면 첫 번째 한 개만 검색
> DISTINCTROW : 중복된 튜플 제거 한 개만 검색, 선택된 속성 값이 아닌 튜플 전체를 대상으로
: 속성명 - 불러올 속성 또는 속성을 이용한 수식을 지정
> 두 개 이상의 테이블을 대상으로 검색할 때 '테이블명.속성명'으로 표현
: AS - 속성 / 연산의 이름을 다른 제목으로 표시
- FROM절 : 검색될 데이터들을 포함하는 테이블명 기술
- WHERE절 : 특정 조건을 기술
- ORDER BY절 : 특정 속성을 기준으로 정렬하여 검색
: 속성명 - 정렬의 기준이 되는 속성명
>조건 연산자 / 연산자 우선순위<
조건 연산자
- 비교 연산자
| = | <> | > | < | >= | <= |
| 같다 | 같지 않다 | 크다 | 작다 | 크거나 같다 | 작거나 같다 |
-논리 연산자 : NOT, AND, OR
- LIKE 연산자 : 대표 문자를 이용하여 지정된 속성의 값이 문자 패턴과 일치하는 튜플 검색을 위해 사용
| % | _ | # |
| 모든 문자를 대표 | 문자 하나를 대표 | 숫자 하나를 대표 |
연산자 우선순위
| ×, /, +, - | 왼쪽에서 오른쪽으로 낮아짐 |
| =, <>, >, >=, <, <= | 같음 |
| NOT, AND, OR | 왼쪽에서 오른쪽으로 낮아짐 |
산술 > 관계 > 논리
기본 검색
SELECT 절에 원하는 속성 지정하여 검색
ex)
SELECT 부서 + '부서의' AS 부서2, 이름 + '의 월급' AS 이름2, 기본급 + 10 AS 기본급2
FROM 사원;
// <사원> 테이블에서 '기본급'에 특별수당 10을 더한 월급을 "XX부서의 XXX의 월급 XXX" 형태로 출력
SELECT DISTINCT 주소
FROM 사원;
// <사원> 테이블에서 '주소'만 검색하되, 중복 제거하여 출력
조건 지정 검색
WHERE 절에 조건을 지정하여 조건에 만족하는 튜플만 검색
ex)
SELECT *
FROM 사원
WHERE 이름 LIKE "김%";
// <사원> 테이블에서 성이 '김'인 사람의 튜플 검색
정렬 검색
ORDER BY 절에 특정 속성을 지정하여 지정 속성으로 정렬하여 검색
ex)
SELECT *
FROM 사원
ORDER BY 부서 ASC, 이름 DESC;
// <사원> 테이블에서 '부서'를 기준으로 오름차순 정렬, 같은 '부서'에 대해 '이름'을 기준으로 내림차순 정렬시켜 검색
하위 질의
조건절에 주어진 질의를 먼저 수행하여 그 검색 결과를 조건절의 피연산자로 사용
ex)
SELECT 이름, 주소
FROM 사원
WHERE 이름 = (SELECT 이름 FROM 여가활동 WHERE 취미 = '나이트댄스');
// <사원> 테이블에서 '취미'가 '나이트댄스'인 사원의 '이름'과 '주소'를 검색
SELECT *
FROM 사원
WHERE 이름 NOT IN (SELECT 이름 FROM 여가활동);
// <사원> 테이블에서 취미활동을 하지 않는 사원들을 검색
복수 테이블 검색
여러 테이블을 대상으로 검색 수행
ex)
SELECT 사원.이름, 사원.부서, 여가활동.취미, 여가활동.경력
FROM 사원, 여가활동
WHERE 여가활동.경력 >= 10 AND 사원.이름 = 여가활동.이름;
// '경력'이 10년 이상인 사원의 '이름', '부서', '취미', '경력'을 검색
6. DML - SELECT - 2
일반 형식
SELECT [PREDICATE] [테이블명.]속성명 [AS 별칭][, [테이블명.]속성명, ...]
[, 그룹함수(속성명) [AS 별칭]]
[, Window함수 OVER (PARTITION BY 속성명1, 속성명2, ...
ORDER BY 속성명3, 속성명4, ...)]
--FROM 테이블명[, 테이블명, ...]
--[WHERE 조건]
[GROUP BY 속성명, 속성명, ...]
[HAVING 조건]
--[ORDER BY 속성명 [ASC | DESC]];
- 그룹함수 : GROUP BY 절에 지정된 그룹별로 속성 값을 집계할 함수 기술
- WINDOW 함수 : GROUP BY 절을 이용하지 않고 속성 값을 집계할 함수 기술
: PARTITION BY - WINDOW 함수가 적용될 범위로 사용할 속성 지정
: ORDER BY - PARTITION 안에서 정렬 기준으로 사용할 속성 지정
- GROUP BY절 : 특정 속성을 기준으로 그룹화하여 검색, 그룹 함수와 함께 사용
- HAVING절 : GROUP BY 절과 함께 사용되며, 그룹에 대한 조건 지정
>그룹 함수 / WINDOW 함수<
그룹 함수
- CONT(속성명) : 그룹별 튜플 수 구하는 함수
- SUM(속성명) : 그룹별 합계를 구하는 함수
- AVG(속성명) : 그룹별 평균을 구하는 함수
- MAX(속성명) : 그룹별 최대값을 구하는 함수
- MIN(속성명) : 그룹별 최소값을 구하는 함수
- STDDEV(속성명) : 그룹별 표준편차를 구하는 함수
- VARIANCE(속성명) : 그룹별 분산을 구하는 함수
- ROLLUP(속성명, 속성명, ...) : 인수로 주어진 속성을 대상으로 그룹별 소계를 구하는 함수
- CUBE(속성명, 속성명, ...) : 인수로 주어진 속성을 대상으로 모든 조합의 그룹별 소계를 구하는 함수
WINDOW 함수
- GROUP BY 절을 이용하지 않고 함수의 인수로 지정한 속성을 범위로 속성값을 집계
- 함수의 인수로 지정한 속성이 대상 레코드의 범위가 되는데, 이를 윈도우(WINDOW)라 함
- ROW_NUMBER( ) : 윈도우별 각 레코드에 대한 일련번호 반환
- RANK( ) : 윈도우별 순위를 반환, 공동 순위를 반영
- DENSE_RANK( ) : 윈도우별 순위를 반환, 공동 순위를 무시하고 순위 부여
WINDOW 함수 이용 검색
GROUP BY 절을 이용하지 않고 함수의 인수로 지정한 속성을 범위로 하여 속성의 값을 집계
ex)
SELECT 상여내역, 상여금,
ROW_NUMBER( ) OVER (PARTITION BY 상여내역 ORDER BY 상여금 DESC) AS NO
FROM 상여금;
// <상여금> 테이블에서 '상여내역'별로 '상여금'에 대한 일련번호 내림차순, 속성명을 'NO'로 구하여 검색
그룹 지정 검색
GROUP BY 절에 지정한 속성을 기준으로 자료를 그룹화하여 검색
ex)
SELECT 부서, COUNT(*) AS 사원수
FROM 상여금
WHERE 상여금 >= 100
GROUP BY 부서
HAVING COUNT(*) >= 2;
// <상여금> 테이블에서 '상여금'이 100 이상인 사원 2명 이상인 '부서'의 튜플 수를 구하여 검색
SELECT 부서, 상여내역, SUM(상여금) AS 상여금합계
FROM 상여금
GROUP BY ROLLUP(부서, 상여내역)
// <상여금> 테이블의 '부서', '상여내역', 그리고 '상여금'에 대해 부서별 상여내역별 소계와 전체 합계를 검색
| 부서 | 상여내역 | 상여금합계 | |
| 기획 | 야간근무 | 120 | → 3레벨 (부서별, 상여내역별 '상여금' 합계) → |
| 기획 | 연장근무 | 200 | |
| 기획 | 특별근무 | 90 | |
| 기획 | 410 | → 2레벨 (부서별 '상여금' 합계) | |
| 편집 | 야간근무 | 210 | → 3레벨 → |
| 편집 | 연장근무 | 40 | |
| 편집 | 특별근무 | 80 | |
| 편집 | 330 | → 2레벨 | |
| 인터넷 | 연장근무 | 30 | → 3레벨 → |
| 인터넷 | 특별근무 | 180 | |
| 인터넷 | 210 | → 2레벨 | |
| 950 | → 1레벨 (전체 '상여금' 합계) |
- ROLLUP 함수가 적용되는 속성이 2개이므로 2 + 1로 총 3레벨, 가장 하위 레벨인 3레벨부터 표시
- 표기된 속성의 순서에 따라 표시되는 집계 항목이 달라지므로 속성의 순서에 주의
SELECT 부서, 상여내역, SUM(상여금) AS 상여금합계
FROM 상여금
GROUP BY CUBE(부서, 상여내역)
// <상여금> 테이블의 '부서', '상여내역', 그리고 '상여금'에 대해 부서별 상여내역별 소계와 전체 합계를 검색
| 부서 | 상여내역 | 상여금합계 | |
| 950 | → 1레벨 (전체 '상여금' 합계) | ||
| 야간근무 | 330 | → 2레벨 (상여내역별 '상여금' 합계) → |
|
| 연장근무 | 270 | ||
| 특별근무 | 350 | ||
| 기획 | 410 | → 3레벨 (부서별 '상여금' 합계) | |
| 기획 | 야간근무 | 120 | → 4레벨 (부서별, 상여내역별 '상여금' 합계) → |
| 기획 | 연장근무 | 200 | |
| 기획 | 특별근무 | 90 | |
| 편집 | 330 | → 3레벨 | |
| 편집 | 야간근무 | 210 | → 4레벨 → |
| 편집 | 연장근무 | 40 | |
| 편집 | 특별근무 | 80 | |
| 인터넷 | 210 | → 3레벨 | |
| 인터넷 | 연장근무 | 30 | → 4레벨 → |
| 인터넷 | 특별근무 | 180 |
- CUBE 함수가 적용되는 속성이 2개이므로 2의 제곱으로 총 4레벨, 가장 상위 레벨인 1레벨부터 표시
- 표기된 속성의 순서가 바뀌어도 표시 순서만 달라질 뿐 집계 항목은 동일
집합 연산자를 이용한 통합 질의
집합 연산자를 사용하여 2개 이상의 테이블 데이터를 하나로 통합
SELECT 속성명1, 속성명2, ...
FROM 테이블명
UNION | UNION ALL | INTERSECT | EXCEPT
SELECT 속성명1, 속성명2, ...
FROM 테이블명
[ORDER BY 속성명 [ASC | DESC]];
- 두 SELECT 문에 기술한 속성들은 개수와 데이터 유형이 서로 동일해야 함
- UNION : 두 SELECT 문의 조회 결과를 통합하여, 중복된 행은 한 번, 나머지는 모두 출력, 합집합
- UNION ALL : 두 SELECT 문의 조회 결과를 통합하여, 중복된 행도 그대로, 나머지도 모두 출력, 합집합
- INTERSECT : 두 SELECT 문의 조회 결과 중 공통된 행만 출력, 교집합
- EXCEPT : 첫 번째 SELECT 문의 조회 결과에서 두 번째 SELECT 문의 조회 결과를 제외한 행 출력, 차집합
ex)
SELECT *
FROM 사원
INTERSECT
SELECT*
FROM 직원;
// <사원> 테이블과 <직원> 테이블에 공통으로 존재하는 레코드만 통합하여 검색
출처 | <시나공> 정보처리기사 필기 2024 기본서 (길벗알앤디)