[과목 II] 자격검정 실전문제(21~40)

게시 2024/05/21

By tonyleekorea 54 분읽는 시간

Q. 실행 결과가 다른 하나는?

④

  
①
SELECT DNAME, LOC, DEPTNO 
FROM DEPT
ORDER BY DNAME, LOC, 3 DESC;

②
SELECT DNAME, LOC AREA, DEPTNO 
FROM DEPT
ORDER BY DNAME, AREA, DEPTNO DESC;

③
SELECT DNAME, LOC AREA, DEPTNO 
FROM DEPT
ORDER BY 1, AREA, 3 DESC;

④
SELECT DNAME DEPT, LOC AREA, DEPTNO 
FROM DEPT
ORDER BY DEPT DESC, LOC, 3 DESC;

④ 첫 번째 결과 컬럼인 DEPT를 역순 정렬하여 나머지와 결과가 다르다.

Q. 아래는 이름이 4문자 이상이고 2번째 문자가 S인 학번을 출력하는 SQL이다. 빈칸 ㉠에 들어갈 수 있는 내용으로 가장 적절하지 않은 것은?

① ‘%S_ _ _’

  
SELECT 학번
FROM 학생
WHERE 학생.이름 LIKE ㉠

① ‘% S _ _ _’

② ‘_ S % _ _’

③ ‘_ S _ % _’

④ ‘_ S _ _ %’

①은 % 위치에 아무런 문자가 들어가지 않을 수도 있다. 따라서 S가 첫 문자가 될 수도 있으므로 옳지 않다.
컬럼 LIKE 패턴
- LIKE 연산자 컬럼이 패턴에 포함된 경우의 데이터 조회
% 0개 이상의 문자열과 일치
[] 1개의 문자와 일치
[^] 1개의 문자와 불일치
_ 특정 위치의 1개의 문자와 일치

Q. 전공이름별로 묶어 전공이름과 학점 평균을 전공이름의 내림차순으로 정렬하여 출력하고자 할 때, 빈칸 ㉠, ㉡에 들어갈 명령어로 가장 적절한 것은?

GROUP BY, DESC

  
SELECT 전공이름, AVG(학점)
FROM 전공, 학생
WHERE 전공.전공번호 = 학생.전공번호
㉠ 전공이름
ORDER BY 전공이름 ㉡;

GROUP BY절 - 데이터들을 작은 그룹으로 분류해 소그룹에 대한 항목별 통계 정보를 얻을 때 사용한다.
DESC - 조회한 테이블을 내림차순으로 정렬한다.
집계 함수의 종류

집계 함수	사용 목적
COUNT(*)	NULL 값을 포함한 행의 수를 출력한다.
COUNT(표현식)	표현식의 값이 NULL 값인 것을 제외한 행의 수를 출력한다.
SUM([DISTINCT/ALL] 표현식)	표현식의 NULL 값을 제외한 합계를 출력한다.
AVG([DISTINCT/ALL] 표현식)	표현식의 NULL 값을 제외한 평균을 출력한다.
MAX([DISTINCT/ALL] 표현식)	표현식의 최댓값을 출력한다.(문자, 날짜 데이터 타입도 사용가능)
MIN([DISTINCT/ALL] 표현식)	표현식의 최솟값을 출력한다.(문자, 날짜 데이터 타입도 사용가능)
STDDEV([DISTINCT/ALL] 표현식)	표현식의 표준 편차를 출력한다.
VARIANCE([DISTINCT/ALL] 표현식)	표현식의 분산을 출력한다.
기타 통계 함수	벤더별로 다양한 통계식을 제공한다.

ORDER BY 문장

  
  SELECT 칼럼명 [ALIAS명]
  FROM 테이블명
  [WHERE 조건식]
  [GROUP BY 칼럼(Column)이나 표현식]
  [HAVING 그룹조건식]
  [ORDER BY 칼럼(Column)이나 표현식 [ASC 또는 DESC]];

ASC(Ascending): 조회한 데이터를 오름차순으로 정렬한다.(기본 값이므로 생략 가능)
DESC(Descending): 조회한 데이터를 내림차순으로 정렬한다.

Q. SQL의 실행 결과로 가장 적절하지 않은 것은?

④ SUBSTR(’Gangneung Wonju’, 8, 4) = ‘g Wo’

① ROUND(4.875, 2) = 4.88

② LENGTH(’KOREAN’) = 6

③ DATE_FORMAT(’2022-11-02’, ‘%Y-%m-%d’) = 2022-11-02

④ SUBSTR(’Gangneung Wonju’, 8, 4) = ‘g Wo’

Gangneung Wonju 문자열에서 8번째 문자인 n에서부터 4개 문자를 출력함 ‘ng W’
단일행 문자형 함수의 종류
- LOWER(문자열)
  - 문자열의 알파벳 문자를 소문자로 바꾸어 준다.
- UPPER(문자열)
  - 문자열의 알파벳 문자를 대문자로 바꾸어 준다.
- ASCII(문자)
  - 문자나 숫자를 ASCII 코드 번호로 바꾸어 준다.
- CHR/CHAR(ASCII번호)
  - ASCII 코드 번호를 문자나 숫자로 바꾸어준다.
- CONCAT(문자열1, 문자열2)
  - 오라클, My SQL에서 유효한 함수이며 문자열1과 문자열2를 연결한다.
  - 합성 연산자 ||(오라클)나 +(SQL Server)와 동일하다.
- SUBSTR/SUBSTRING(문자열, m[, n])
  - 문자열 중 m 위치에서 n개의 문자 길이에 해당하는 문자를 돌려준다.
  - n이 생략되면 마지막 문자까지이다.
- LENGTH/LEN(문자열)
  - 문자열의 개수를 숫자값으로 돌려준다.
- LTRIM(문자열 [, 지정문자])
  - 문자열의 첫 문자부터 확인해서 지정 문자가 나타나면 해당 문자를 제거한다.(지정 문자가 생략되면 공백 값이 디폴트)
  - SQL Server에서는 LTRIM 함수에 지정 문자를 사용할 수 없다. 즉, 공백만 제거할 수 있다.
- RTRIM(문자열 [, 지정문자])
  - 문자열의 마지막 문자부터 확인해서 지정 문자가 나타나는 동안 해당문자를 제거한다.(지정 문자가 생략되면 공백 값이 디폴트)
  - SQL Server에서는 RTRIM 함수에 지정 문자를 사용할 수 없다. 즉, 공백만 제거할 수 있다.
- TRIM([leading/trailing/both] 지정문자 FROM 문자열)
  - 문자열에서 머리말, 꼬리말, 또는 양쪽에 있는 지정 문자를 제거한다.(leading/trailing/both 가 생략되면 both가 디폴트)
  - SQL Server에서는 TRIM 함수에 지정 문자를 사용할 수 없다. 즉, 공백만 제거할 수 있다.
※ 주: 오라클함수/SQL Server 표시, ‘/’ 없는 것은 공통 함수

Q1. 다음 SQL의 결과로 가장 적절한 것은 무엇인가?

③ QL DEVELOPER

  
SELECT RTRIM(LTRIM('SQL DEVELOPER', 'S'), 'SQL') FROM DUAL;

① DEVELOPER

② DEVELOPER

③ QL DEVELOPER

④ QL DEVELOPER

LTRIM으로 ‘S’만 지워진다. RTRIM으로 오른쪽부터 ‘S’, ‘Q’, ‘L’ 문자를 찾지만 해당 문자가 없기 때문에 진행
세트가 아닌 따로따로 S, Q, L 따로이다. 다만, RTRIM(오른쪽)이므로, 왼쪽 QL이 없어지는 것이 아니다. 오른쪽 맨 끝이 R이므로 (S, Q, L)에 해당 안 됨

Q2. 다음 SQL의 결과를 고르시오.

④ DEVELOPER

  
SELECT LTRIM('SQLDEVELOPER', 'LQS') FROM DUAL;

① SQLDEVELOPER

② QLDEVELOPER

③ LDEVELOPER

④ DEVELOPER

왼쪽부터 (L, Q, S) 가 아닌 문자를 만나기 전까지 (L, Q, S) 를 지움
Q. L이 두 개여도 첫 번째 한 개만 지우는 것인가? L이 제일 오른쪽에 한 개만 있어도 지워지는가?
- L 의 개수는 상관 없으며, 오른쪽은 해당 없다.
  - Ex1, LTRIM(‘SQLLLQQQSSSaaaSQLLQS’, ‘LQS’) -> aaaSQLLQS
  - Ex2, LTRIM(‘ABCD’,’BCD’)의 결과 값은 ‘ABCD’이며, RTRIM(‘ABCD’,’BCD’)의 결과 값은 ‘A’이다.
- 연속된 것은 갯수와 상관없이 지우되, 연속되지 않으면 지우지 않는다.

Q3. 아래의 결과가 다른 것은?

④

① RTRIM(‘SQLXX’, ‘X’)

② LTRIM(‘XXSQL’, ‘X’)

③ REPLACE(‘XXSQLXX’, ‘X’)

④ TRIM(‘XXSQLXX’, ‘X’)

LTRIM은 왼쪽부터, RTRIM은 오른쪽부터 지정한 문자 제거

  
-- Oracle, MySQL
SELECT LTRIM('!!!Hello World!!!', '!') AS LeftTrimmedString;
-- 결과: 'Hello World!!!'

SELECT LTRIM('   Hello World   ') AS LeftTrimmedString;
-- 결과: 'Hello World   '

-- SQL Server에서는 지정 문자를 사용할 수 없기 때문에 기본 공백 제거만 가능하다.
SELECT LTRIM('   Hello World   ') AS LeftTrimmedString;
-- 결과: 'Hello World   '

-- Oracle, MySQL
SELECT RTRIM('!!!Hello World!!!', '!') AS RightTrimmedString;
-- 결과: '!!!Hello World'

SELECT RTRIM('   Hello World   ') AS RightTrimmedString;
-- 결과: '   Hello World'

-- SQL Server에서는 지정 문자를 사용할 수 없기 때문에 기본 공백 제거만 가능하다.
SELECT RTRIM('   Hello World   ') AS RightTrimmedString;
-- 결과: '   Hello World'

REPLACE 함수는 특정 문자열을 다른 문자열로 대체한다.

세 번째 인수를 지정하지 않으면, 두 번째 인수를 기존 문자열에서 제거한다.

  
  -- 1. 문자열 변경
  -- MySQL, Oracle
  SELECT REPLACE('Hello World', 'World', 'Everyone') AS Result;
  -- 결과: 'Hello Everyone'
    
  -- 2. 문자 제거
  -- MySQL, Oracle
  SELECT REPLACE('Hello World', 'World') AS Result;
  -- 결과: 'Hello '

TRIM의 경우, 양쪽에서 해당 문자열 삭제
- LTRIM, RTRIM에서는 FROM이 빠져도 정상 출력하지만 TRIM은 예외이다.
  - TRIM(‘xax’) 은 가능 - 공백 제거
  - TRIM(‘xax’, ‘x’) 는 불가 - 문법 오류
- 올바른 문법
  - TRIM(BOTH ‘x’ FROM ‘xax’)
  - TRIM(‘x’ FROM ‘xax’)

  
-- Oracle, MySQL
SELECT TRIM(LEADING '!' FROM '!!!Hello World!!!') AS LeadingTrimmedString;
-- 결과: 'Hello World!!!'

SELECT TRIM(TRAILING '!' FROM '!!!Hello World!!!') AS TrailingTrimmedString;
-- 결과: '!!!Hello World'

SELECT TRIM(BOTH '!' FROM '!!!Hello World!!!') AS BothTrimmedString;
-- 결과: 'Hello World'

-- SQL Server에서는 지정 문자를 사용할 수 없기 때문에 기본 공백 제거만 가능하다.
SELECT TRIM('   Hello World   ') AS BothTrimmedString;
-- 결과: 'Hello World'

Oracle과 MySQL에서는 TRIM, LTRIM, RTRIM 함수 모두 지정 문자를 사용할 수 있지만, SQL Server에서는 공백만 제거할 수 있다.
각 함수의 동작을 설명하면 다음과 같다.

① RTRIM('SQLXX', 'X')

RTRIM 함수는 오른쪽부터 지정한 문자를 제거한다.

② LTRIM('XXSQL', 'X')

LTRIM 함수는 왼쪽부터 지정한 문자를 제거한다.

③ REPLACE('XXSQLXX', 'X')

REPLACE 함수는 문자열 내의 모든 지정된 문자를 대체한다. 대체 문자열을 지정하지 않으면 해당 문자를 제거한다.

④ TRIM('XXSQLXX', 'X')

TRIM 함수는 문자열의 양쪽에서 지정한 문자를 제거한다.
주어진 TRIM('XXSQLXX', 'X') 표현은 문법적으로 올바르지 않으며 오류를 발생시킨다. 올바른 문법으로 작성하면 TRIM('X' FROM 'XXSQLXX')이 되어야 한다. 이 경우, 결과는 'SQL'이 된다.

Q. 아래를 참고할 때 광고매체 ID별 최초로 게시한 광고명과 광고시작일자를 출력하기 위하여 빈칸 ㉠에 들어갈 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을 작성해야 한다.
①의 경우 연관 서브쿼리를 활용하는 방법이지만, 이를 활용하기 위해서는 WHERE 절에서 사용되어야 한다.(Inline View에서는 사용할 수 없다) 메인 쿼리에서 이미 조인을 걸고 있는 WHERE 절이 존재하기 때문에, 서브쿼리에서 조인이 될 필요가 없는 조건이다.
③은 광고 ID별로 광고매체ID와 광고시작일자의 최솟값을 출력하므로 틀린 결과이다. 광고매체ID별로 정리를 해야하는데, 여기에 또 MIN 값을 씌우게 되면 하나로 요약된다.
④은 광고게시의 전체데이터에서 광고매체ID의 최솟값과 광고시작일자의 최솟값을 가져오므로 틀린 결과이다.

GROUP BY 문장

  
  SELECT [DISTINCT] 칼럼명 [ALIAS명]
  FROM 테이블명
  [WHERE 조건식]
  [GROUP BY 칼럼(Column)이나 표현식]
  [HAVING 그룹조건식]

광고 매체별로 최초 광고 시작일을 찾고자 하는 문제이다. 광고 개시 엔터티에는 광고 게시 현황이 있으며, 이는 광고 매체에 대한 1대 다의 관계를 가지고 있어서 하나의 광고 매체에 여러 개의 광고게시가 달릴 것으로 예상된다.
우리가 원하는 것은 각 광고 매체별로 최초 광고 시작일을 정리하는 것이다. 각 광고 매체별로 최초 광고 시작일을 찾기 위해서는 광고게시를 먼저 광고매체ID별로 그룹화해야 한다.
그러나 이 결과에는 광고명과 같은 추가 정보가 없다. 따라서 이 정보를 얻기 위해서는 광고게시 엔터티와 다시 한 번 조인을 해야 한다. 그러면 광고명과 같은 정보를 최초 시작일에 함께 출력할 수 있다.
정리하면, 광고 매체 아이디로 그룹화하여 각 매체별로 광고 시작일의 최초 시작일을 찾았다. 이 결과에 해당되는 광고명과 같은 정보를 얻기 위해서는 다시 한 번 광고 계시 테이블과 조인해야 한다.

Q. 아래 SQL을 순서대로 실행했을 때 최종적으로 반영되는 SQL을 모두 고른 것은?

(가), (라), (마)

  
(가)
INSERT INTO emp(empno, ename, deptno) VALUES(999, 'Smith', 10);
SAVEPOINT a;

(나) 
DELETE emp WHERE empno = 202; ②
SAVEPOINT b;

(다) 
UPDATE emp SET ename = 'Clark'; ③
ROLLBACK TO SAVEPOINT a;

(라) 
INSERT INTO emp(empno, ename, deptno) VALUES(300, 'Thomas', 30); ④
SAVEPOINT c;

(마) 
DELETE emp WHERE deptno = 20;
COMMIT;

③과 ④사이에서 SAVEPOINT A 이후의 모든 트랜잭션을 롤백했기 때문에 ②, ③번 트랜잭션은 모두 취소된다.

Q. 아래 SQL의 실행 결과로 가장 적절한 것은?

COLB	COLA1	COLA2	SUMAC

가	1	1	6
나	3	3	3
다	5	5	5

[TABLE_A]

TABKEY	COLA	COLB	COLC
1		가
2	1	가	5
3		나	2
4	3	나	0
5			3
6	5	다	0
7		다

  
SELECT COLB
	, MAX(COLA) AS COLA1
	, MIN(COLA) AS COLA2 
	, SUM(COLA + COLC) AS SUMAC
FROM TABLE_A
GROUP BY COLB;

GROUP BY 절은 NULL 데이터도 집계에 포함하므로 COLB칼럼의 값에 NULL이 있는 행도 결과로 출력된다. COLB를 기준으로 집계하는 것이니 COLB의 값 4개가 전부 나와야 한다.
MIN, MAX 함수는 NULL 칼럼의 값이 NULL이 아닌 행 중에서의 최소, 최대값을 추출한다.
NULL과의 사칙연산은 결과가 NULL이므로 COLA 또는 COLB 둘 중 하나에 칼럼의 값이 NULL이라면 NULL을 반환하므로 SUM의 결과에는 포함되지 않는다.
NULL 포함 연산의 결과
- NULL + 2, 2 + NULL
- NULL - 2, 2 - NULL
- NULL * 2, 2 * NULL
- NULL / 2, 2 / NULL의 결과는 모두 NULL이다.

Q. 아래 SQL의 실행 결과로 가장 적절한 것은?

ID
999
100

  
SELECT ID 
FROM TBL
GROUP BY ID
HAVING COUNT(*) = 2
ORDER BY (CASE 
	   WHEN ID = 999 THEN 0 
	   ELSE ID 
	  END)

[TBL]

ID
100
100
200
200
200
999
999

GROUP BY HAVING한 결과에 대해 정렬 연산을 하는 것이다.
ID 건수가 2개이며, ORDER BY절 CASE문에 의해 999는 0으로 치환되고 그 외는 ID 값으로 정렬된다.
OREDER BY 절 특징
- 기본적인 정렬 순서는 오름차순(ASC)이다.
- 숫자형 데이터 타입은 오름차순으로 정렬했을 경우에 가장 작은 값부터 출력된다.
- 날짜형 데이터 타입은 오름차순으로 정렬했을 경우에 날짜 값이 가장 이른 값이 먼저 출력된다.
  - 예를 들어 ‘01-JAN-2012’는 ‘01-SEP-2012’보다 먼저 출력된다.
- 오라클에서는 NULL 값을 가장 큰 값으로 간주하여 오름차순으로 정렬했을 경우에 가장 마지막에, 내림차순으로 정렬했을 경우에는 가장 먼저 위치한다.
- 반면, SQL Server에서는 NULL 값을 가장 작은 값으로 간주하여 오름차순으로 정렬했을 경우에는 가장 먼저, 내림차순으로 정렬했을 경우에는 가장 마지막에 위치한다.

Q. 오류가 발생하는 SQL은?

③

  
①
SELECT 지역, SUM(매출금액) AS 매출금액
FROM 지역별매출
GROUP BY 지역
ORDER BY 매출금액 DESC;

② 
SELECT 지역, 매출금액
FROM 지역별매출
ORDER BY 년 ASC;

③ 
SELECT 지역, SUM(매출금액) AS 매출금액
FROM 지역별매출
GROUP BY 지역
ORDER BY 년 DESC;

④ 
SELECT 지역, SUM(매출금액) AS 매출금액
FROM 지역별매출
GROUP BY 지역
HAVING SUM(매출금액) > 1000
ORDER BY COUNT(*) ASC;

② SQL 실행 순서에 의하면 SELECT 절 이후에 ORDER BY 절이 수행되기 때문에 SELECT 절에 기술되지 않은 ‘년’ 칼럼으로 정렬하는 것은 논리적으로 맞지 않다. 하지만 오라클은 행기반 DATABASE이므로 데이터를 액세스할 때 행 전체 칼럼을 메모리에 로드한다. 이와 같은 특성으로 인해 SELECT 절에 기술되지 않은 칼럼으로도 정렬을 할 수 있다.
단, 아래와 같은 SQL일 경우에는 정렬을 할 수 없다.
1 2 3 4 5 6 SELECT 지역, 매출금액 FROM( SELECT 지역, 매출금액 FROM 지역별매출 ) ORDER BY 년 ASC;
- 이는 IN-LINE VIEW가 먼저 수행됨에 따라 더 이상 SELECT절 외 칼럼을 사용할 수 없기 때문이다.
③ GROUP BY를 사용할 경우 GROUP BY 표현식이 아닌 값은 기술될 수 없다.
④ GROUP BY 표현식이기에 가능하다.

Q. 아래 SQL의 실행 결과로 가장 적절한 것은?

③ 2019.02.25 01:00:00

  
SELECT TO_CHAR(TO_DATE('2019.02.25', 'YYYY.MM.DD') + 1/12/(60/30), 'YYYY.MM.DD HH24:MI:SS')
FROM DUAL 

① 2019.02.25 02:00:00

② 2019.02.25 01:30:00

③ 2019.02.25 01:00:00

④ 2019.02.25 00:30:00

오라클에서 날짜의 연산은 숫자의 연산과 같다. 특정 날짜에 1을 더하면 하루를 더한 결과와 같으므로 1/24/60 = 1분을 의미한다. 1/12/(60/30) = 1시간과 같으므로 2019년 02월 25일 00시 00분 00초에 1시간을 더한 결과와 같다.
1일을 12로 나누면 2시간이 된다. 2시간을 (60/30)으로 나누면, 2시간을 2로 나누는 것과 같아서 결국 1시간이 된다.
DATE 타입에서의 날짜 연산
- TO_DATE(’2024.05.21’, ‘YYYY.MM.DD’) + 1 → 하루 증가
- TO_DATE(’2024.05.21’, ‘YYYY.MM.DD’) + 1/24 → 1시간 증가
- TO_DATE(’2024.05.21’, ‘YYYY.MM.DD’) + 1/1440 → 1분 증가
- TO_DATE(’2024.05.21’, ‘YYYY.MM.DD’) + 1/86400 → 1초 증가
  - DATE 타입에서 1은 하루를 의미한다.
    - 즉, 1은 24시간을 의미한다.
  - 1시간은 하루(1)를 24로 나눈 값이다.
    - 1day/24=1hour
  - 1분은 하루(1)를 24로 나누고, 이를 다시 60으로 나눈 값이다.
    - 1day/24/60=1/1440=1minute
  - 1초는 하루(1)를 24로 나누고, 이를 다시 60으로 나누고, 이를 다시 60으로 나눈 값이다.
    - 1day/24/60/60=1/86400=1second

Q. 아래 SQL의 실행 결과로 가장 적절한 것은?(단, DBMS는 오라클로 가정)

③ 2023.01.10 10:10:00

  
SELECT TO_CHAR(TO_DATE('2023.01.10 10', 'YYYY.MM.DD HH24') + 1/24(60/10), 'YYYY.MM.DD HH24:MI:SS') FROM DUAL;

① 2023.01.10 11:01:00

② 2023.01.10 10:05:00

③ 2023.01.10 10:10:00

④ 2023.01.10 10:30:00

오라클에서 날짜의 연산은 숫자의 연산과 같다. 특정 날짜에 1을 더하면 하루를 더한 결과와 같으므로 1/24/60 = 1분을 의미한다. 1/24(60/10) = 10분과 같으므로 2015년 1월 10일 10시에 10분을 더한 결과와 같다.
- 주어진 식은 아직 날짜로 파싱되지 않았기 때문에 날짜로 변환한 후 날짜 연산을 수행한다.
- 1은 1일을 의미하며, 1일을 24로 나누면 1시간이 된다.
- 1시간을 6으로 나누면 10분이 된다.
- 따라서, 2023년 1월 10일 10시에서 10분을 더하는 연산을 수행한다.
- 연산한 시간은 TO_CHAR 함수를 사용하여 지정된 형식으로 표현된다.
DUAL 테이블의 특성
- 사용자 SYS가 소유하며 모든 사용자가 액세스 가능한 테이블이다.
- SELECT ~ FROM ~의 형식을 갖추기 위한 일종의 DUMMY 테이블이다.
- DUMMY라는 문자열 유형의 칼럼에 ‘X’라는 값이 들어 있는 행을 1건 포함하고 있다.

Q. 실행 결과가 NULL인 SQL은? (단, DBMS는 오라클로 가정)

② SELECT NULLIF(‘A’, ‘A’) FROM DUAL;

① SELECT COALESCE(NULL, ‘A’) FROM DUAL;

② SELECT NULLIF(‘A’, ‘A’) FROM DUAL;

③ SELECT NVL(’A’, NULL) FROM DUAL;

④ SELECT NVL(NULL, 0) + 10 FROM DUAL;

NULL(표현식1, 표현식2) 함수는 표현식1과 표현식2가 같은면 NULL, 아니면 표현식1을 리턴한다.
① ‘A’ - COALESCE 함수는 인자 중 첫 번째로 NULL이 아닌 값을 반환한다.
② NULL
③ ‘A’ - 첫 번째 인자는 'A'이므로 결과는 'A'가 된다.
④ 10 - NVL(NULL, 0)은 0을 반환하고, 이에 10을 더한 결과는 10이 된다.

Q. SELECT 문장의 실행 순서를 올바르게 나열한 것은?

FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY

SELECT 문장의 실행 순서는 FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY이다.
- FROM - 발췌 대상 테이블을 참조한다.
- WHERE - 발췌 대상 데이터가 아닌 것은 제거한다.
- GROUP BY - 행들을 소그룹화한다.
- HAVING - 그루핑된 값의 조건에 맞는 것만을 출력한다.
- SELECT - 데이터 값을 출력/계산한다.
- ORDER BY - 데이터를 정렬한다.

Q. 5개의 테이블로부터 필요한 칼럼을 조회하려고 할 대, 최소 몇 개의 JOIN 조건이 필요한가?

4개

여러 테이블로부터 원하는 데이터를 조회하기 위해서는 전체 테이블 개수에서 최소 N-1개 만큼의 JOIN 조건이 필요하다.

EQUI JOIN 문장

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

WHERE 절에 JOIN 조건을 넣는다.

ANS/ISO SQL 표준 EQUI JOIN 문장

  
  SELECT 테이블1.칼럼명, 테이블2.칼럼명, ...
  FROM 테이블1 
  [INNER] JOIN 테이블2
  ON 테이블1.칼럼명1 = 테이블2.칼럼명2;

ON 절에 JOIN 조건을 넣는다.

Q. 출연료가 8888 이상인 영화명, 배우명, 출연료를 구하는 SQL로 가장 적절한 것은? (단, 밑줄 친 속성들은 테이블의 기본키이다.)

④

  
①
SELECT 출연.영화명, 영화.배우명, 출연.출연료
FROM 배우, 영화, 출연
WHERE 출연료 >= 8888
AND 출연.영화번호 = 영화.영화번호
AND 출연.배우번호 = 배우.배우번호;

②
SELECT 영화.영화명, 배우.배우명, 출연료 
FROM 영화, 배우, 출연
WHERE 출연.출연료 > 8888
AND 출연.영화번호 = 영화.영화번호
AND 영화.영화번호 = 배우.배우번호;

③ 
SELECT 영화명, 배우명, 출연료 
FROM 배우, 영화, 출연
WHERE 출연료 >= 8888
AND 영화번호 = 영화.영화번호
AND 배우번호 = 배우.배우번호;

④ 
SELECT 영화.영화명, 배우.배우명, 출연료 
FROM 배우, 영화, 출연
WHERE 출연료 >= 8888
AND 출연.영화번호 = 영화.영화번호
AND 출연.배우번호 = 배우.배우번호;

영화명과 배우명은 출연 테이블이 아니라 영화와 배우 테이블에서 가지고 와야 하는 속성이므로 출연 테이블의 영화번호와 영화 테이블의 영화번호 및 출연 테이블의 배우번호와 배우 테이블의 배우번호를 조인하는 SQL을 작성해야 한다.
출연료에 대한 조건을 만족하는 영화명, 배우명, 출연료를 조인해서 구하는 문제이다. 문제의 의도는 조인할 때 컬럼명이 중복될 경우, 테이블 출처를 명확히 밝혀야 한다는 것이다.
예를 들어, 영화와 출연 테이블 모두에 같은 이름의 컬럼이 있을 때 이를 구분하기 위해 테이블명을 함께 써야 한다. 반면, 출연 테이블에만 존재하는 출연료 같은 컬럼은 단독으로 사용해도 된다. 가능하면 테이블명을 명확히 표시하는 것이 좋다.

Q. 아래에서 JOIN에 대한 설명으로 가장 적절한 것은?

(가), (다), (라), (마)

(가) 일반적으로 조인은 PK와 FK값의 연관성에 의해 성립된다.

(나) DBMS 옵티마이저는 FROM 절에 나열된 테이블들을 임의로 3개씩 묶어서 조인을 처리한다.

(다) EQUI JOIN은 조인에 관여하는 테이블 간의 컬럼 값들이 정확하게 일치하는 경우에 사용되는 방법이다.

(라) EQUI JOIN은 ‘=’ 연산자에 의해서만 수행되며, 그 이외의 비교연산자를 사용하는 경우에는 모두 NON EQUI JOIN이다.

(마) 대부분 NON EQUI JOIN을 수행할 수 있지만, 때로는 설계상의 이유로 수행이 불가능한 경우도 있다.

DBMS 옵티마이저는 FROM 절에 나열된 테이블이 아무리 많아도 항상 2개의 테이블씩 짝을 지어 JOIN을 수행한다.
조인(JOIN)
- 두 개 이상의 테이블들을 연결 또는 결합하여 데이터를 출력하는 것을 조인(JOIN)이라고 하며, 일반적인 경우 행들은 PRIMARY KEY(PK)나 FOREIGN KEY(FK) 값의 연관에 의해 조인이 성립된다.
- 하지만 어떤 경우에는 이런 PK, FK의 관계가 없어도 논리적인 값들의 연관만으로 조인이 성립된다.

Q. 아래 SQL의 실행 결과로 가장 적절한 것은?

4

  
SELECT COUNT(*) CNT
FROM EMP_TBL A, RULE_TBL B
WHERE A.ENAME LIKE B.RULE;

[EMP_TBL]

EMPNO	ENAME
1000	SMITH
1050	ALLEN
1100	SCOTT

[RULE_TBL]

RULE_NO	RULE
1	S%
2	%T%

LIKE 연산자를 이용한 조인의 이해가 필요하다. SQL의 실행 결과는 다음과 같다.
EMPNO ENAME RULE
1000 SMITH S%
1100 SCOTT S%
1000 SMITH %T%
1100 SCOTT %T%
카티션 프로덕트(모든 발생 가능한 조합)를 만든 후, 모든 조합 중에서 조건에 맞는 결과를 골라내는 방식이다.
LIKE 조건을 사용하여 조합을 필터링하면, 참인 것들만 선택된다.
총 네 번 출력되는 것은, 조인이 모든 발생 가능한 조합을 만든 후, WHERE 절을 통해 해당 조건을 만족하는 애들만 선택하기 때문이다.

EMPNO	ENAME	RULE
1000	SMITH	S%
1100	SCOTT	S%
1000	SMITH	%T%
1100	SCOTT	%T%

Q. 순수 관계 연산자로 가장 적절하지 않은 것은?

UPDATE

순수 관계 연산자에는 SELECT, PROJECT, JOIN, DIVIDE가 있다.
순수 관계 연산자와 SQL 문장 비교
- SELECT 연산은 WHERE 절로 구현
- PROJECT 연산은 SELECT 절로 구현
- (NATURAL) JOIN 연산은 다양한 JOIN 기능으로 구현
- DIVIDE 연산은 현재 사용되지 않음

Q. 아래를 참고할 때 가장 적절한 SQL은?

④

[설명] 우리는 매일 배치작업을 통하여 고객에게 추천할 컨텐츠를 생성하고 고객에게 추천서비스를 제공한다. 추천 컨텐츠 엔터티에서 언제 추천을 해야 하는지를 정의하는 추천 대상일자가 있어 해당일자에만 컨텐츠를 추천해야 한다. 또한 고객이 컨텐츠를 추천 받았을 때 선호하는 컨텐츠가 아닌 경우에는 고객이 비선호 컨텐츠로 분류하여 더 이상 추천 받기를 원하지 않는다. 그러므로 우리는 비선호 컨텐츠 엔터티에 등록된 데이터에 대해서는 추천을 수행하지 않아야 한다. ※ 배치작업이란? 어떤 처리를 연속적으로 하는 것이 아니고 일정량씩 나누어 처리하는 경우 그 일정량을 배치(batch)라고 한다. 배치의 원뜻은 한 묶음이라는 의미다. [기계공학용어사전] 예) 상품을 주문하는 로직은 그 당시에 발생하는 트랜잭션에 대한 처리이므로 배치작업이라 표현하지는 않는다. 하지만 상품별 주문량을 집계하는 로직의 경우 특정조건(기간 등)으로 일괄처리를 해야하므로 배치작업이라 표현할 수 있다.

  
①
SELECT C.컨텐츠ID, C.컨텐츠명 
FROM 고객 A 
INNER JOIN 추천컨텐츠 B
ON (A.고객ID = B.고객ID) 
INNER JOIN 컨텐츠 C
ON (B.컨텐츠ID = C.컨텐츠ID)
WHERE A.고객ID = #custId#
	AND B.추천대상일자 = TO_CHAR(SYSDATE, 'YYYY.MM.DD')
	AND NOT EXISTS (SELECT X.컨텐츠ID
			FROM 비선호컨텐츠 X 
			WHERE X.고객ID = B.고객ID);

② 
SELECT C.컨텐츠ID, C.컨텐츠명
FROM 고객 A 
INNER JOIN 추천컨텐츠 B
ON (A.고객ID = #custId# AND A.고객ID = B.고객ID) 
INNER JOIN 컨텐츠 C
ON (B.컨텐츠ID = C.컨텐츠ID) 
RIGHT OUTER JOIN 비선호컨텐츠 D
ON (B.고객ID = D.고객ID AND B.컨텐츠ID = D.컨텐츠ID)
WHERE B.추천대상일자 = TO_CHAR(SYSDATE, 'YYYY.MM.DD')
AND B.컨텐츠ID IS NOT NULL;

③ 
SELECT C.컨텐츠ID, C.컨텐츠명
FROM 고객 A 
INNER JOIN 추천컨텐츠 B
ON (A.고객ID = B.고객ID) 
INNER JOIN 컨텐츠 C 
ON (B.컨텐츠D = C.컨텐츠D) 
LEFT OUTER JOIN 비선호컨텐츠 D
ON (B.고객ID = D.고객ID AND B.컨텐츠ID = D.컨텐츠ID)
WHERE A.고객ID = #custId#
AND B.추천대상일자 = TO_CHAR(SYSDATE, 'YYYY.MM.DD')
AND D.컨텐츠ID IS NOT NULL;

④ 
SELECT C.컨텐츠ID, C.컨텐츠명
FROM 고객 A 
INNER JOIN 추천컨텐츠 B 
ON (A.고객ID = #custId# AND A.고객ID = B.고객ID) 
INNER JOIN 컨텐츠 C
ON (B.컨텐츠ID = C.컨텐츠D)
WHERE B.추천대상일자 = TO_CHAR(SYSDATE, 'YYYY.MM.DD')
AND NOT EXISTS (SELECT X.컨텐츠ID
		FROM 비선호컨텐츠 X 
		WHERE X.고객ID = B.고객ID 
		AND X.컨텐츠ID = B.컨텐츠ID);

① NOT EXIST 절의 연관서브쿼리에 X.컨텐츠ID = B.컨텐츠ID가 존재하지 않아 단 하나의 컨텐츠라도 비선호로 등록한 고객에 대해서는 모든 컨텐츠가 추천에서 배제된다. 비선호컨텐츠는 고객별 컨텐츠별로 관리된다. 고객ID 조건만 주면 컨텐츠별 체크가 안 된다. 문제의 요구사항에 부합하려면 두 가지 모두를 비교해야 한다.
② 추천컨텐츠를 기준으로 비선호컨텐츠와 LEFT OUTER JOIN이 수행되고 비선호컨텐츠의 컨텐츠ID에 대해서 IS NULL 조건이 있다면 정확히 비선호컨텐츠만 필터링할 수 있다.(고객이 비선호로 등록하지 않은 컨텐츠는 추천컨텐츠에만 등록 되어있으므로)
③ 추천컨텐츠를 기준으로 비선호컨텐츠와 LEFT OUTER JOIN이 수행되고 D.컨텐츠ID에 대해 IS NOT NULL 수행 시, 비선호컨텐츠가 추출된다.
#custId#는 바인드 변수로 특정하게 정해진 값(상수)이 아니라 파라미터로 받아온 값으로 조회하겠다는 뜻이다.
(고객)이 (컨텐츠)에 대해 (비선호)한 것을 (제외)해야 한다. ①은 서브쿼리 안에 컨텐츠ID 조건 누락, ②와 ③은 LEFT JOIN 후 마지막 WHERE 조건이 D.컨텐츠ID IS NULL이 되어야 한다.
ANSI/ISO SQL에서 표시하는 FROM 절의 JOIN 형태
- INNER JOIN
- NATURAL JOIN
- USING 조건절
- ON 조건절
- CROSS JOIN
- OUTER JOIN(LEFT, RIGHT, FULL)
고객이 입력되면 해당 고객에 대한 추천 콘텐츠를 찾아 추천하고, 비선호 콘텐츠는 제외하여 추천 목록을 제공한다.
ERD 관계
- 고객과 추천 콘텐츠는 1대 다 관계이다. 한 명의 고객에게 여러 개의 추천 콘텐츠가 있을 수 있다. 하지만, 모든 고객에게 반드시 추천 콘텐츠가 있어야 하는 것은 아니므로 선택적 관계이다.
- 고객에게 콘텐츠가 없더라도 출력을 해야 한다면 레프트 아우터 조인을 사용하지만, 여기서는 추천 콘텐츠가 있는 고객만 출력하면 되므로 이너 조인을 사용한다.
- 추천 콘텐츠는 콘텐츠가 먼저 있어야 하므로, 콘텐츠는 필수 요소이다. 따라서 고객이 추천 콘텐츠를 필요로 하면, 추천 콘텐츠는 콘텐츠를 필요로 하므로 이너 조인(INNER JOIN)이 필요하다.
비선호 콘텐츠 처리
- 고객별로 콘텐츠 ID별로 비선호 콘텐츠가 기록된다. 추천 콘텐츠 중 비선호 콘텐츠를 제외해야 한다. 이를 위해서 NOT IN 또는 NOT EXISTS를 사용하여 제외할 수 있다. NOT EXISTS 조건은 해당 조건이 참이면 해당 레코드를 생략(제외)하는 역할을 한다.
- 추천 콘텐츠에 있는 고객 ID와 콘텐츠 ID를 확보하여 추천 콘텐츠를 필터링해야 한다. 고객별로 그리고 콘텐츠 ID별로 모두 조인 조건을 걸어야 한다.
- 조인 시, 비선호 콘텐츠 테이블과의 조건을 추가하여, 추천 콘텐츠 중에서 비선호 콘텐츠를 제외한다.

Q. 아래에 대한 설명으로 가장 적절한 것은?

②

① 제품, 생산제품, 생산라인 엔터티를 INNER JOIN하기 위해서 생산제품 엔터티는 WHERE 절에 최소 3번 나타나야 한다.

② 제품과 생산라인 엔터티를 JOIN 시 적절한 JOIN 조건이 없으므로 카티시안 곱(Cartesian Product)이 발생한다.

③ 제품과 생산라인 엔터티에는 생산제품과 대응되지 않는 레코드는 없다.

④ 특정 생산라인번호에서 생산되는 제품의 제품명을 알기 위해서는 제품, 생산제품, 생산라인까지 3개의 엔터티의 INNER JOIN이 필요하다.

① 생산제품 엔터티는 WHERE 절에 최소 2번 나타나야 한다.
③ 데이터 모델을 보면 제품과 생산라인 엔터티에는 생산제품과 대응되지 않는 레코드가 있을 수 있다.(선택 관계라 적절치 않음)
④ 특정 생산라인에서 생산되는 제품의 제품명을 알기위해서는 제품과 생산제품까지 2개의 엔터티만을 Inner Join하면 된다.(라인번호는 생산제품에도 있으므로, 제품과 생산제품만을 조인)
INNER JOIN
- INNER JOIN은 OUTER(외부) 조인과 대비하여 내부 JOIN이라고 하며 JOIN 조건에서 동일한 값이 있는 행만 반환한다.
조인을 위한 조건은 ON 절에, 검색을 위한 조건은 WHERE 절에 작성한다.
OUTER JOIN의 경우 조건의 위치에 따라 결과가 달라질 수 있으니 신중해야 한다. INNER JOIN의 경우 조건의 위치가 결과에 영향을 미치지 않는다.

Q. 아래 SQL의 빈칸 ㉠, ㉡에 들어갈 내용으로 가장 적절한 것은?

INNER JOIN 구매정보 B ON A.고객번호 = B. 고객번호, HAVING COUNT(B.구매번호) >= 3

구매정보 테이블의 고객번호는 고객 테이블의 고객번호를 참조하는 외래키(Foreign Key)이다.
[조건] 구매 이력이 있는 고객 중 구매 횟수가 3회 이상인 고객의 이름과 등급을 출력

  
SELECT A.이름, A.등급
FROM 고객 A
㉠
GROUP BY A.이름, A.등급
㉡ ;

구매이력이 있어야 하므로 INNER JOIN이 필요하며, 구매 횟수이므로 COUNT 함수를 사용한다.
문제에서, 구매횟수가 3회 이상인 고객의 ‘이름’과 ‘등급’을 출력하려면 이름과 등급을 먼저 묶어주고(GROUP BY 절), 그 뒤에 3회 이상인 고객을 필터링(HAVING 절)해야 한다.
GROUP BY는 ‘이름’과 ‘등급’을 그룹핑하는 역할을 한다.

Certificate, [SQLD] 개념 및 문제풀이

%	0개 이상의 문자열과 일치
[]	1개의 문자와 일치
[^]	1개의 문자와 불일치
_	특정 위치의 1개의 문자와 일치

인기 태그