📌 [정보처리기사]데이터베이스 구축 - SQL 응용/DML SELECT

 

데이터베이스 구축

---

 

 

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절 : 특정 속성을 기준으로 정렬하여 검색할 때 사용한다.
  • 속성명 : 정렬의 기준이 되는 속성명을 기술한다.
  • [ASC DESC] : 정렬 방식으로서 'ASC'는 오름차순, 'DESC'는 내림차순이다. 생략하면 오름차순으로 지정된다.
• 조건 연산자/연산자 우선순위
  • 조건 연산자
    • 비교 연산자

    연산자	=	<>	>	<	>=	<=
    의미	같다	같지 않다	크다	작다	크거나 같다	작거나 같다

    • 논리 연산자 : NOT, AND, OR
    • LIKE 연산자 : 대표 문자를 이용해 지정된 속성의 값이 문자 패턴과 일치하는 튜플을 검색하기 위해 사용된다.

    대표 문자	%	_	#
    의미	모든 문자를 대표함	문자 하나를 대표함	숫자 하나를 대표함

  • 연산자 우선순위

  종류	연산자	우선순위
  산술 연산자	X./.+/-	왼쪽에서 오른쪽으로 갈수록 낮아짐.
  관계 연산자	=.<>.>.>=.<.<=	모두 같다.
  논리 연산자	NOT, AND, OR	왼쪽에서 오른쪽으로 갈수록 낮아짐

💡 산술, 관계, 논리 연산자가 함께 사용되었을 때는 산술 > 관계 > 논리 연산자 순서로 연산자 우선순위가 정해진다.

 

• 다음과 같은 기본 테이블에 대해 다음 예제의 결과를 확인하시오.

 

<사원>

이름	부서	생일	주소	기본급
나연	기획	04/05/61	망원동	120
정연	인터넷	01/09/69	서교동	80
모모	편집	07/21/75	합정동	100
사나	편집	10/22/73	망원동	90
지효	기획	02/20/64	대흥동	100
미나	편집	03/11/67	상암동	120
다현	기획	04/29/78	연남동	110
채영	인터넷	12/11/80		90

 

<여가활동>

이름	취미	경력
사나	당구	10
지효	나이트댄스	5
다현	태껸	15
정연	씨름	8

 

 

 

기본 검색

📌 SELECT 절에 원하는 속성을 지정하여 검색한다.

 

🔔 예제1) <사원> 테이블의 모든 튜플을 검색하시오.

• SELECT * FROM 사원;
• SELECT 사원.* FROM 사원;
• SELECT 이름, 부서, 생일, 주소, 기본급 FROM 사원;
• SELECT 사원.이름, 사원.부서, 사원.생일, 사원.주소, 사원.기본급 FROM 사원

 

💡 위의 SQL은 모두 보기에 주어진 <사원> 테이블 전체를 그대로 출력한다.

 

<결과>

이름	부서	생일	주소	기본급
나연	기획	04/05/61	망원동	120
정연	인터넷	01/09/69	서교동	80
모모	편집	07/21/75	합정동	100
사나	편집	10/22/73	망원동	90
지효	기획	02/20/64	대흥동	100
미나	편집	03/11/67	상암동	120
다현	기획	04/29/78	연남동	110
채영	인터넷	12/11/80		90

 

 

🔔 예제2) <사원> 에서 '주소'만 검색하되 같은 '주소'는 한 번만 출력하시오.

• SELECT DISTINCT 주소 FROM 사원;

 

<결과>

주소

대흥동

망원동

상암동

서교동

연남동

합정동

 

🔔 예제3) <사원> 테이블에서 '기본급'에 특별수당 10을 더한 월급을 "XX부서의 XXX의 월급 XXX" 형태로 출력하시오.

• SELECT 부서 + '부서의' AS 부서2, 이름 + '의 월급' AS 이름2, 기본급 + 10 AS 기본급2 FROM 사원;

부서2	이름2	기본급2
기획부서의	나연의월급	120
인터넷부서의	정연의월급	80
편집부서의	모모의월급	100
편집부서의	사나의월급	90
기획부서의	지효의월급	100
편집부서의	미나의월급	120
기획부서의	다현의월급	110
인터넷부서의	채영의월급	90

 

💡 부서 + "부서의" AS 부서2 '부서'에 '부서의'를 연결하여 표시하되, '부서'라는 속성 으로 표시한다.

 

 

 

조건 지정 검색

📌 WHERE 절에 조건을 지정하여 조건에 만족하는 튜플만 검색한다.

 

🔔 예제1) <사원> 테이블에서 '기획'부의 모든 튜플을 검색하시오.

• SELECT * FROM 사원 WHERE 부서 = '기획';

 

<결과>

이름	부서	생일	주소	기본급
나연	기획	04/05/61	망원동	120
지효	기획	02/20/64	대흥동	100
다현	기획	04/29/78	연남동	110

 

🔔 예제2) <사원> 테이블에서 "기획" 부서의 근무하면서 "대흥동"에 사는 사람의 튜플을 검색하시오.

• SELECT * FROM 사원 WHERE 부서 = '기획' AND 주소 = '대흥동';

 

<결과>

이름	부서	생일	주소	기본급
지효	기획	02/20/64	대흥동	100

 

🔔 예제3) <사원> 테이블에서 '부서'가 "기획" 이거나 "인터넷"인 튜플을 검색하시오.

• SELECT * FROM 사원 WHERE 부서 = '기획' OR 부서 = '인터넷';
• SELECT * FROM 사원 WHERE 부서 IN('기획', '인터넷'); 여기서 IN은 포함된 튜플을 의미한다.

 

<결과>

이름	부서	생일	주소	기본급
나연	기획	04/05/61	망원동	120
정연	인터넷	01/09/69	서교동	80
지효	기획	02/20/64	대흥동	100
다현	기획	04/29/78	연남동	100
채영	인터넷	12/11/80		90

 

🔔 예제4) <사원> 테이블에서 성이 '김'인 사람의 튜플을 검색하시오.

• SELECT *FROM 사원 WHERE 이름 LIKE "김%";

 

<결과>

이름	부서	생일	주소	기본급
채영	인터넷	12/11/80		90

 

💡 NULL이 아닌 값을 검색할 때는 IS NOT NULL을 사용한다. <사원> 테이블에서 주소가 NULL이 아닌 튜플 검색
SELECT * FROM 사원 WHERE 주소 IS NOT NULL;

 

 

 

정렬 검색

📌 ORDER BY 절에 특정 속성을 지정하여 지정된 속성으로 자료를 정렬하여 검색한다.

 

🔔 예제1) <사원> 테이블에서 '주소'를 기준으로 내림차순 정렬시켜 상위 2개 튜플만 검색하시오.

• SELECT TOP 2 * FROM 사원 ORDER BY 주소 DESC;

 

<결과>

이름	부서	생일	주소	기본급
모모	편집	07/21/75	합정동	100
다현	기획	04/29/78	연남동	110

 

🔔 예제2) <사원> 테이블에서 '부서'를 기준으로 오름차순 정렬하고, 같은 '부서'에 대해서는 '이름'을 기준으로 내림차순 정렬시켜서 검색하시오.

• SELECT * FROM 사원 ORDER BY 부서 ASC, 이름 DESC;

 

<결과>

이름	부서	생일	주소	기본급
지효	기획	02/20/64	대흥동	100
다현	기획	04/29/78	연남동	110
나연	기획	04/05/61	망원동	120
채영	인터넷	12/11/80		90
정연	인터넷	01/09/69	서교동	80
사나	편집	10/22/73	망원동	90
모모	편집	07/21/75	합정동	100
미나	편집	03/11/67	상암동	120

 

 

 

하위 질의

📌 하위 질의는 조건절에 주어진 질의를 먼저 수행하여 그 검색 결과를 조건절의 피연산자로 사용한다.

 

🔔 예제1) '취미'가 "나이트댄스"인 사원의 '이름'과 '주소'를 검색하시오.

• SELECT 이름, 주소 FROM 사원 WHERE 이름 = (SELECT 이름 FROM 여가활동 WHERE 취미 = '나이트댄스');

 

<결과>

이름	주소
지효	대흥동

 

🔔 예제2) 취미활동을 하지 않는 사원들을 검색하시오.

• SELECT * FROM 사원 WHERE 이름 NOT IN(SELECT 이름 FROM 여가활동);

 

<결과>

이름	부서	생일	주소	기본급
나연	기획	04/05/61	망원동	120
모모	편집	07/21/75	합정동	100
미나	편집	03/11/67	상암동	120
채영	인터넷	12/11/80		90

 

💡 NOT IN() 은 포함되지 않는 데이터를 의미한다. 즉 <사원> 테이블에서 모든 자료를 검색하는데, <여가활동> 테이블에 '이름'이 있는 자료는 제외하고 검색한다.

 

🔔 예제3) 취미활동을 하는 사원들의 부서를 검색하시오.

• SELECT 부서 FROM 사원 WHERE EXISTS(SELECT 이름 FROM 여가활동 WHERE 여가활동.이름 = 사원.이름);

 

<결과>

부서

인터넷

편집

기획

기획

 

💡 EXISTS() 는 하위 질의로 검색된 결과가 존재하는지 확인핼 때 사용한다. 즉 <사원> 테이블의 '이름'이 <여가활동> 테이블의 '이름'에도 있는지 확인하는 것이다.

 

 

 

복수 테이블 검색 질의

📌 여러 테이블을 대상으로 검색을 수행한다.

 

🔔 예제) '경력'이 10년 이상인 사원의 '이름', '부서', '취미', '경력'을 검색 하시오.

• SELECT 사원.이름, 사원.부서, 여가활동.취미, 여가활동.경력 FROM 사원, 여가활동 WHERE 여가활동.경력 >= 10 AND 사원.이름 = 여가활동.이름;

 

<결과>

이름	부서	취미	경력
사나	편집	당구	10
다현	기획	태껸	15

 

 

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절 : 특정 속성을 기준으로 그훔화하여 검색할 때 사용한다. 일반적으로 GROUP BY절은 그룹 함수와 함께 사용된다.
• HAVING절 : GROUP BY와 함께 사용되며, 그룹에 대한 조건을 지정한다.
• 그룹 함수 / WINDOW 함수
  • 그룹 함수 : GROUP BY절에 지정된 그룹별로 속성의 값을 집계할 때 사용된다.
    • COUNT(속성명) : 그룹별 튜플 수를 구하는 함수
    • SUM(속성명) : 그룹별 합계를 구하는 함수
    • AVG(속성명) : 그룹별 평균을 구하는 함수
    • MAX(속성명) : 그룹별 최대값을 구하는 함수
    • MIN(속성명) : 그룹별 최소값을 구하는 함수
    • STDDEV(속성명) : 그룹별 표준편차를 구하는 함수
    • VARIANCE(속성명) : 그룹별 분산을 구하는 함수
    • ROLLUP(속성명, 속성명, ...)
      • 인수로 주어진 속성을 대상으로 그룹별 소계, 총 합계를 구하는 함수이다.
      • 속성의 개수가 n개이면, n + 1 레벨까지, 하위 레벨에서 상위 레벨 순으로 데이터가 집계된다.
    • CUBE(속성명, 속성명, ...)
      • ROLLUP 과 유사한 형태이나 CUBE는 인수로 주어진 속성을 대상으로 모든 조합의 그룹별 소계, 총 합계를 구한다.
      • 속성의 개수가 n개이면, 2n승 레벨까지, 상위 레벨에서 하위 레벨 순으로 데이터가 집계된다.
  • WINDOW 함수
    • GROUP BY절을 이용하지 않고 함수의 인수로 지정한 속성을 범위로 하여 속성의 값을 집계한다.
    • 함수의 인수로 지정한 속성이 대상 레코드의 범위가 되는데, 이를 윈도우(WINDOW)라고 부른다.
    • WINDOW 함수 종류
      • ROW_NUMBER() : 윈도우 별로 각 레코드에 대한 일련 번호를 반환한다.
      • RANK() : 윈도우 별로 순위를 반환하며, 공동 순위를 반영한다.
      • DENSE_RANK() : 윈도우 별로 순위를 반환하며, 공동 순위를 무시하고 순위를 부여한다.

 

 

WINDOW 함수 이용 검색

📌 GROUP BY절을 이용하지 않고 함수의 인수로 지정한 속성을 범위로 하여 속성의 값을 집계한다.

 

<상여금>

부서	이름	상여내역	상여금
기획	홍길동	연장근무	100
기획	일지매	연장근무	100
기획	최준호	야간근무	120
기획	장길산	특별근무	90
인터넷	강건달	특별근무	90
인터넷	서국현	특별근무	90
인터넷	박인식	연장근무	30
편집	김선달	특별근무	80
편집	황종근	연장근무	40
편집	성춘향	야간근무	80
편집	임꺽정	야간근무	80
편집	황진이	야간근무	50

 

<결과>

상여내역	상여금	NO
야간근무	120	1
야간근무	80	2
야간근무	80	3
야간근무	50	4
연장근무	100	1
연장근무	100	2
연장근무	40	3
연장근무	30	4
특별근무	90	1
특별근무	90	2
특별근무	90	3
특별근무	80	4

 

🔔 예제1) <상여금> 테이블에서 '상여내역' 별로 '상여금'에 대한 일련 번호를 구하시오. (단 순서는 내림차순 이며 속성명은 'NO'로 할 것)

 

SELECT 상여내역, 상여금, ROW_NUMBER() OVER(PARTITION BY 상여내역 ORDER BY 상여금 DESC) AS NO FROM 상여금;

 

🔔 예제2) <상여금> 테이블에서 '상여내역' 별로 '상여금'에 대한 순위를 구하시오.(단, 순서는 내림차순이며, 속성명은 '상여금 순위'로 하고 RANK() 함수를 이용할 것

 

SELECT 상여내역, 상여금, RANK() OVER(PARTITION BY 상여내역 ORDER BY 상여금 DESC) AS 상여금순위 FROM 상여금;

 

<결과>

상여내역	상여금	상여금순위
야간근무	120	1
야간근무	80	2
야간근무	80	2
야간근무	50	4
연장근무	100	1
연장근무	100	1
연장근무	40	3
연장근무	30	4
특별근무	90	1
특별근무	90	1
특별근무	90	1
특별근무	80	4

 

 

 

그룹 지정 검색

📌 GROUP BY절에 지정한 속성을 기준으로 자료를 그룹화하여 검색한다.

 

🔔 예제1) <상여금> 테이블에서 '부서별 상여금'의 평균을 구하시오.

 

SELECT 부서, AVG(상여금) AS 평균 FROM 상여금 GROUP BY 부서;

 

<결과>

부서	평균
기획	102.5
인터넷	70
편집	66

 

🔔 예제2) <상여금> 테이블에서 부서별 튜플 수를 검색하시오.

 

SELECT 부서, COUNT(*) AS 사원수 FROM 상여금 GROUP BY 부서;

 

<결과>

부서	사원수
기획	4
인터넷	3
편집	5

 

🔔 예제3) <상여금> 테이블에서 '상여금'이 100이상인 사원이 2명 이상인 '부서'의 튜플 수를 구하시오.

 

SELECT 부서, COUNT(*) AS 사원수 FROM 상여금 WHERE 상여금 >= 100 GROUP BY 부서 HAVING COUNT(*) >= 2;

 

<결과>

부서	사원수
기획	3

 

1. 'WHERE 상여금 >= 100' 절에 의해서 '상여금'이 100 이상인 자료만 검색 대상이 된다.
2. GROUP BY부서 절에 의해서 '상여금'이 100 이상인 자료에 대해서만 부서별로 그룹을 지정한다.
3. HAVING COUNT(*) >= 2 절에 의해서 부서의 인원이 2 이상인 부서의 인원만 검색한다.

 

🔔 예제4) <상여금> 테이블에서 '부서', '상여내역', 그리고 '상여금'에 대해 부서별 상여 내역별 소계와 전체 합계를 검색하시오. (단, 속성명은 '상여금합계'로 하고, ROLLUP 함수를 사용할 것.

 

SELECT 부서, 상여내역, SUM(상여금) AS 상여금합계 FROM 상여금 GROUP BY ROLLUP(부서, 상여내역);

 

<결과>

부서	상여내역	상여금합계	설명(테이블 아님)
기획	야간근무	120	3레벨(부서별, 상여내역별 '상여금'의 합계)
기획	연장근무	200
기획	특별근무	90
기획		410	2레벨(부서별 '상여금'의 합계)
편집	야간근무	210	3레벨
편집	연장근무	40
편집	특별근무	80
편집		330	2레벨
인터넷	연장근무	30	3레벨
인터넷	특별근무	180
인터넷		120	2레벨
		950	1레벨(전체 '상여금'의 합계)

 

💡 ROLLUP 함수가 적용되는 속성이 2개이브로 집계되는 레벨 수는 2+1로 총 3레벨이다. 가장 하위 레벨인 3레벨부터 표시된다. 3레벨은 부서별 상여내역별 '상여금'의 합계, 2레벨은 부서별 '상여금'의 합계, 1레벨은 전체 '상여금'의 합계가 표시된다. ROLLUP 함수는 표기된 속성의 순서에 따라 표시되는 집계 항목이 달라지므로 속성의 순서에 주의해야 한다. ROLLUP(상여내역, 부서로 지정하면 3레벨은 상여내역별 부서별 '상여금'의 합계, 2레벨은 상여내역별 상여금의 합계, 1레벨은 전체 '상여금'의 합계가 표시되므로 부서별 '상여금'의 합계는 확인할 수 없다.

 

🔔 예제5) <상여금> 테이블에서 '부서', '상여내역', 그리고 '상여금'에 대해 부서별 상여내역별 소계와 전체 합계를 검색하시오. (단, 속성명은 '상여금합계'로 하고, CUBE 함수를 사용할 것)

 

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개이므로 집계되는 레벨 수는 2n승으로 총 4레벨이다. CUBE 함수는 가장 상위 레벨인 1레벨부터 표시된다. 1레벨은 전체 '상여금'의 합계, 2레벨은 상여내역별 '상여금'의 합계, 3레벨은 부서별 '상여금'의 합계, 4레벨은 부서별 상여내역별 '상여금'의 합계가 표시된다.
CUBE 함수는 ROLLUP 함수와 달리 인수로 주어진 속성을 대상으로 결합 가능한 모든 집계를 표시하므로 인수로 주어진 속성의 순서가 바뀌어도 표시 순서만 달라질 뿐 표시되는 집계 항목은 동일하다.

 

 

집합 연산자를 이용한 통합 질의

📌 집합 연산자를 사용하여 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문의 조회 결과를 제외한 행을 출력한다.	차집합

 

<사원>

사원	직급
김형석	대리
홍영선	과장
류기선	부장
김현천	이사

 

<직원>

사원	직급
신원섭	이사
이성호	대리
홍영선	과장
류기선	부장

 

🔔 예제1) <사원> 테이블과 <직원> 테이블을 통합하는 질의문을 작성하시오.  (단, 같은 레코드가 중복되어 나오지 않게 하시오.)

 

SELECT * FROM 사원 UNION SELECT * FROM 직원;

 

<결과>

사원	직급
김현천	이사
김형석	대리
류기선	부장
신원섭	이사
이성호	대리
홍영선	과장

 

🔔 예제2) <사원> 테이블과 <직원> 테이블에 공통으로 존재하는 레코드만 통합하는 질의문을 작성하시오.

 

SELECT * FROM 사원 INTERSECT SELECT * FROM 직원;

 

<결과>

사원	직급
류기선	부장
홍영선	과장