ABS, MOD, ROUND, TRUNC, SIGN, CHR/CHAR, CEIL/CEILING, FLOOR, EXP, LOG, LN, POWER, SIN, COS, TAN
날짜형 함수
DATE 타입의 값을 연산한다.
SYSDATE/GETDATE, EXTRACT/DATEPART, TO_NUMBER(TO_CHAR(d, 'YYYY' | 'MM' | 'DD')) / YEAR | MONTH | DAY
변환형 함수
문자, 숫자, 날짜형 값의 데이터 타입을 변환한다.
TO_NUMBER, TO_CHAR, TO_DATE / CAST, CONVERT
NULL 관련 함수
NULL을 처리하기 위한 함수
NVL/ISNULL, NULLIF, COALESCE
문제 43
Q. 아래는 SEARCHED_CASE_EXPRESSION SQL 문장이다. 이때 사용된 SEARCHED_CASE_EXPRESSION은 SIMPLE_CASE_EXPRESSION을 이용해 똑같은 기능을 표현할 수 있다. 아래 SQL 문장의 ( ㄱ ) 안에 들어갈 표현을 작성하시오. (스칼라 서브쿼리는 제외함.)
[SEARCHED_CASE_EXPRESSION 문장 사례]
SELECT LOC,
CASE WHEN LOC = 'NEW YORK' THEN 'EAST'
ELSE 'ETC'
END as AREA
FROM DEPT;
[SIMPLE_CASE_EXPRESSION 문장 사례]
SELECT LOC,
CASE ( ㄱ )
ELSE 'ETC'
END as AREA
FROM DEPT;
해설 :SEARCHED_CASE_EXPRESSION을 SIMPLE_CASE_EXPRESSION으로 변환하는 문제이다.
문제 44
Q. 팀별 포지션별 FW, MF, DF, GK 포지션의 인원수와 팀별 전체 인원수를 구하는 SQL을 작성할 때 결과가 다른 것은? (보기 1은 SQL Server 환경이고, 보기 2, 3, 4는 ORACLE 환경이다.)
①
SELECT TEAM_ID,
ISNULL(SUM(CASE WHEN POSITION = 'FW' THEN 1 END), 0) FW,
ISNULL(SUM(CASE WHEN POSITION = 'MF' THEN 1 END), 0) MF,
ISNULL(SUM(CASE WHEN POSITION = 'DF' THEN 1 END), 0) DF,
ISNULL(SUM(CASE WHEN POSITION = 'GK' THEN 1 END), 0) GK,
COUNT(*) SUM
FROM PLAYER
GROUP BY TEAM_ID;
②
SELECT TEAM_ID,
NVL(SUM(CASE POSITION WHEN = 'FW' THEN 1 END), 0) FW,
NVL(SUM(CASE POSITION WHEN = 'MF' THEN 1 END), 0) MF,
NVL(SUM(CASE POSITION WHEN = 'DF' THEN 1 END), 0) DF,
NVL(SUM(CASE POSITION WHEN = 'GK' THEN 1 END), 0) GK,
COUNT(*) SUM
FROM PLAYER
GROUP BY TEAM_ID;
③
SELECT TEAM_ID,
NVL(SUM(CASE WHEN POSITION = 'FW' THEN 1 END), 0) FW,
NVL(SUM(CASE WHEN POSITION = 'MF' THEN 1 END), 0) MF,
NVL(SUM(CASE WHEN POSITION = 'DF' THEN 1 END), 0) DF,
NVL(SUM(CASE WHEN POSITION = 'GK' THEN 1 END), 0) GK,
COUNT(*) SUM
FROM PLAYER
GROUP BY TEAM_ID;
④
SELECT TEAM_ID,
NVL(SUM(CASE POSITION WHEN = 'FW' THEN 1 ELSE 1 END), 0) FW,
NVL(SUM(CASE POSITION WHEN = 'MF' THEN 1 ELSE 1 END), 0) MF,
NVL(SUM(CASE POSITION WHEN = 'DF' THEN 1 ELSE 1 END), 0) DF,
NVL(SUM(CASE POSITION WHEN = 'GK' THEN 1 ELSE 1 END), 0) GK,
COUNT(*) SUM
FROM PLAYER
GROUP BY TEAM_ID;
해설 :지문4는 CASE 문장에서 데이터가 없는 경우를 0으로 표시해야(ELSE 0), 다른 3개의 지문과 같은 결과가 나온다.
문제 45
Q. 다음 중 아래 TAB1을 보고 각 SQL 실행 결과를 가장 올바르게 설명한 것을 고르시오.
① SELECT COL2 FROM TAB1 WHERE COL1 = 'b'; → 실행 결과가 없다. (공집합) ② SELECT ISNULL(COL2, 'X') FROM TAB1 WHERE COL1 = 'a'; → 실행 결과로 ’X'를 반환한다. ③ SELECT COUNT(COL1) FROM TAB1 WHERE COL2 = NULL; → 실행 결과는 1이다. ④ SELECT COUNT(COL2) FROM TAB1 WHERE COL1 IN ('b', 'c'); → 실행 결과는 1이다.
해설 :ISNULL 함수는 결과값이 NULL일 경우 지정된 값을 반환한다. 칼럼의 NULL 값을 확인할 때는 ISNULL을 사용해야 한다.
※ NULL의 특성
- 널 값은 아직 정의되지 않은 값으로 0 또는 공백과 다르다. 0은 숫자이고. 공백은 하나의 문자이다. - 테이볼을 생성할 때, NOT NULL 또는 PRIMARY KEY로 정의되지 않은 모든 데이터 유형은 널 값을 포함할 수 있다. - 널 값올 포함하는 연산의 경우 결과 값도 널 값이다. 모르는 데이터에 숫자를 더하거나 빼도 결과는 마찬가지로 모르는 데이터인 것과 같다. - 결과값을 NULL이 아닌 다른 값을 얻고자 할 때NVL/ISNULL 함수를 사용한다. NULL 값의 대상이 숫자 유형 데이터인 경우는 주로 0(Zero)으로, 문자 유형 데이터인 경우는 블랭크보다는 'x' 같이 해당 시스템에서 의미 없는 문자로 바꾸는 경우가 많다.
문제 46
Q. 사원 테이블에서 MGR의 값이 7698와 같으면 NULL을 표시하고, 같지 않으면 MGR을 표시하려고 한다. 아래 SQL 문장의 ( ㄱ ) 안에 들어갈 함수명을 작성하시오.
SELECT ENAME, EMPNO, MGR ( ㄱ )(MGR, 7698) as NM
FROM EMP;
해설 :ISNULL, NVL 함수는 표현식1의 결과값이 NULL이면 표현식2의 값을 출력하며, NULLIF 함수는 표현식1이 표현식2와 같으면 NULL을, 같지 않으면 표현식1을 리턴한다. COALESCE 함수는 임의의 개수 표현식에서 NULL이 아닌 최초의 표현식을 나타낸다.
※ NULL 관련 함수
- NVL(표현식1, 표현식2)
- ISNULL(표현식1, 표현식2)
- NULLIF(표현식1, 표현식2)
- COALESCE(표현식1, 표현식2, ...)
문제 50
Q. 다음 중 아래 각각 3개의 SQL 수행 결과로 가장 적절한 것은?
SELECT AVG(COL3) FROM TAB_A;
SELECT AVG(COL3) FROM TAB_A WHERE COL1 > 0;
SELECT AVG(COL3) FROM TAB_A WHERE COL1 IS NOT NULL;
① 20, 20, 20 ② 20, 10, 10 ③ 10, 20, 20 ④ 10, 10, 10
Q. 어느 기업의 직원 테이블(EMP)이 직급(GRADE) 별로 사원 500명, 대리 100명, 과장 30명, 차장 10명, 부장 5명, 직급이 정해지지 않은(NULL) 사람 25명으로 구성되어 있을 때, 다음 중 SQL문을 SQL1)부터 SQL3)까지 순차적으로 실행한 결과 건수를 순서대로 나열한 것으로 가장 적절한 것은?
SQL1) SELECT COUNT(GRADE) FROM EMP;
SQL2) SELECT GRADE FROM EMP WHERE GRADE IN ('차장', '부장', '널');
SQL3) SELECT GRADE, COUNT(*) FROM EMP GROUP BY GRADE;
① 670, 15, 6 ② 645, 40, 5 ③ 645, 15, 6 ④ 670, 40, 6
SELECT [DISTINCT] 칼럼명 [ALIAS명]
FROM 테이블명
[WHERE 조건식]
[GROUP BY 칼럼(Column)이나 표현식]
[HAVING 그룹조건식];
문제 52
Q. 아래는 어느 회사의 광고에 대한 데이터 모델이다. 다음 중 광고매체ID별 최초로 게시한 광고명과 광고시작일자를 출력하기 위하여 아래 ( ㄱ )에 들어갈 SQL로 옳은 것은?
[SQL]
SELECT C.광고매체명, B.광고명, A.광고시작일자
FROM 광고게시 A, 광고 B, 광고매체 C,
( ㄱ ) D
WHERE A.광고시작일자 = D.광고시작일자
AND A.광고매체ID = D.광고매체ID
AND A.광고ID = B.광고ID
AND A.광고매체ID = C.광고매체ID
ORDER BY C.광고매체명;
①
SELECT D.광고매체ID, MIN(D.광고시작일자) AS 광고시작일자
FROM 광고게시 D
WHERE D.광고매체ID = C.광고매체ID
GROUP BY D.광고매체ID;
②
SELECT 광고매체ID, MIN(광고시작일자) AS 광고시작일자
FROM 광고게시
GROUP BY 광고매체ID;
③
SELECT MIN(광고매체ID) AS 광고매체ID, MIN(광고시작일자) AS 광고시작일자
FROM 광고게시
GROUP BY 광고ID;
④
SELECT MIN(광고매체ID) AS 광고매체ID, MIN(광고시작일자) AS 광고시작일자
FROM 광고게시;
해설 : 광고게시 테이블에서 광고매체ID별로 광고시작일자가 가장 빠른 데이터를 추출하는 SQL을 작성해야 한다.
※ GROUP BY 절과 HAVING 절의 특성
- GROUP BY 절을 통해 소그룹별 기준을 정한 후, SELECT 절에 집계 함수를 사용한다. - 집계 함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행한다. - GROUP BY 절에서는 SELECT 절과는 달리 ALIAS 명을 사용할 수 없다. - 집계 함수는 WHERE 절에는 올 수 없다. (집계 함수를 사용할 수 있는 GROUP BY 절보다 WHERE 절이 먼저 수행된다.) - WHERE 절은 전체 데이터를 GROUP으로 나누기 전에 행들을 미리 제거시킨다. - HAVING 절은 GROUP BY 절의 기준 항목이나 소그룹의 집계 함수를 이용한 조건을 표시할 수 있다. - GROUP BY 절에 의한 소그룹별로 만들어진 집계 데이터 중, HAVING 절에서 제한 조건을 두어 조건을 만족하는 내용만 출력한다. - HAVING 절은 일반적으로 GROUP BY 절 뒤에 위치한다.
문제 53
Q. 다음 중 오류가 발생하는 SQL 문장인 것은?
①
SELECT 회원ID, SUM(주문금액) AS 합계
FROM 주문
GROUP BY 회원ID
HAVING COUNT(*) > 1;
②
SELECT SUM(주문금액) AS 합계
FROM 주문
HAVING AVG(주문금액) > 100;
③
SELECT 메뉴ID, 사용유형코드, COUNT(*) AS CNT
FROM 시스템사용이력
WHERE 사용일시 BETWEEN SYSDATE - 1 AND SYSDATE
GROUP BY 메뉴ID, 사용유형코드
HAVING 메뉴ID = 3 AND 사용유형코드 = 100;
④
SELECT 메뉴ID, 사용유형코드, AVG(COUNT(*)) AS AVGCNT
FROM 시스템사용이력
GROUP BY 메뉴ID, 사용유형코드;
해설 : SQL 실행 순서에 의해 HAVING 절은 SELECT 절보다 선행 처리 되기에, SELECT 절 COUNT 함수 사용 여부는 관게 없다. 위의 SQL은 나 칼럼으로 GROUP BY를 수행하였을 때 건수가 2건 이상인 데이터를 추출하여 SUM(다)DML 값이 큰 순으로 정렬하는 SQL이므로 CNT가 2이상인 것만 출력된다.
문제 55
Q. 다음 중 아래 SQL의 실행 겱과로 가장 적절한 것은?
SELECT ID FROM TBL
GROUP BY ID
HAVING COUNT(*) = 2
ORDER BY (CASE WHEN ID = 999 THEN 0 ELSE ID END)
해설 : GROUP BY를 사용할 경우 GROUP BY 표현식이 아닌 값은 기술될 수 없다.
문제 57
Q. 다음 중 ORDER BY 절에 대한 설명으로 가장 부적절한 것은?
① SQL 문장으로 조회된 데이터들을 다양한 목적에 맞게 특정 칼럼을 기준으로 정렬하는데 사용한다. ② DBMS 마다 NULL 값에 대한 정렬 순서가 다를 수 있으므로 주의해야 한다. ③ ORDER BY 절에서 칼럼명 대신 Alias 명이나 칼럼 순서를 나타내는 정수도 사용이 가능하나, 이들을 혼용하여 사용할 수 없다. ④ GROUP BY 절을 사용하는 경우 ORDER BY 절에 집계 함수를 사용할 수도 있다.
해설 :CASE 절을 이용해서 원래의 정렬 순서를 변경하였다. 그래서 ID가 'A'인 것이 가장 먼저 표시되도록 하였다.
문제 59
Q. 다음 중 SELECT 문장의 실행 순서를 올바르게 나열한 것은?
① SELECT - FROM - WHERE - GROUP BY - HAVING - ORDER BY ② FROM - SELECT - WHERE - GROUP BY - HAVING - ORDER BY ③ FROM - WHERE - GROUP BY - HAVING - ORDER BY - SELECT ④ FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY
해설 : SELECT 문장의 실행 순서는 FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY 이다.
※ SELECT 문장 실행 순서
① 발췌 대상 테이블을 참조한다. (FROM) ② 발췌 대상 데이터가 아닌 것은 제거한다. (WHERE) ③ 행들을 소그룹화 한다. (GROUP BY) ④ 그룹핑된 값의 조건에 맞는 것만을 출력한다. (HAVING) ⑤ 데이터 값을 출력/계산한다. (SELECT) ⑥ 데이터를 정렬한다. (ORDER BY)
문제 60
Q. 아래의 팀별성적 테이블에서 승리건수가 높은 순으로 3위까지 출력하되 3위의 승리건수가 동일한 팀이 있다면 함께 출력하기 위한 SQL 문장으로 올바른 것은?
①
SELECT TOP(3) 팀명, 승리건수
FROM 팀별성적
ORDER BY 승리건수 DESC;
②
SELECT TOP(3) 팀명, 숭리건수
FROM 팀별성적;
③
SELECT 팀명, 승리건수
FROM 팀별성적
WHERE ROWNUM <= 3
ORDER BY 승리건수 DESC;
④
SELECT TOP(3) WITH TIES 팀명, 승리건수
FROM 팀별성적
ORDER BY 승리건수 DESC;
해설 : 영화명과 배우명은 출연 테이블이 아니라 영화와 배우 테이블에서 가지고 와야 하는 속성이므로 출연 테이블의 영화번호와 영화 테이블의 영화번호 및 출연 테이블의 배우번호와 배우 테이블의 배우번호를 조인하는 SQL문을 작성해야 한다.
※ EQUI JOIN 문장
SELECT 테이블1.칼럼명, 테이블2.칼럼명, ...
FROM 테이블1, 테이블2
WHERE 테이블1.칼럼명1 = 테이블2.칼럼명; /* WHERE 절에 JOIN 조건을 넣는다. */
※ ANSI/ISO SQL 표준 EQUI JOIN 문장
SELECT 테이블1.칼럼명, 테이블2.칼럼명, ...
FROM 테이블1 INNER JOIN 테이블2
ON 테이블1.칼럼명1 = 테이블2.칼럼명2; /* ON 절에 JOIN 조건을 넣는다. */
문제 63
Q. 다음 중 아래에서 Join에 대한 설명으로 가장 적절한 것은?
가) 일반적으로 Join은 PK와 FK 값의 연관성에 의해 성립된다. 나) DBMS 옵티마이저는 From 절에 나열된 테이블들올 임의로 3개 정도씩 묶어서 Join을 처리한다. 다) EQUI Join은 Join에 관여하는 테이블 간의 걸럼 값들이 정확하게 일치하는 경우에 사용되는 방법이다. 라) EQUI Join은 '=' 연산자에 의해서만 수행되며, 그 이외의 비교 연산자를 사용하는 경우에는 모두 Non EQUI Join이다. 마) 대부분 Non EQUI Join을 수행할 수 있지만. 때로는 설계상의 이유로 수행이 불가능한 경우도 있다.
해설 : DBMS 옵티마이저는 From 절에 나열된 테이블이 아무리 많아도 항상 2개의 테이블씩 짝을 지어 Join을 수행한다.
※ 두 개 이상의 테이블 둘을 연결 또는 결합하여 데이터를 출력하는 것을 JOIN이라고 하며, 일반적인 경우 행들은 PRIMARY KEY(PK)나 FOREIGN KEY(FK) 값의 연관에 의해 JOIN이 성립된다. 하지만 어떤 경우에는 이러한 PK, FK의 관계가 없어도 논리적인 값들의 연관만으로 JOIN이 성립 가능하다.
문제 64
Q. 다음 SQL의 실행 결과로 맞는 것은?
[SQL]
SELECT COUNT(*) CNT
FROM EMP_TBL A, RULE_TBL R
WHERE A.ENAME LIKE B.RULE;
