SQLD 문제 풀이 12

1. 아래와 같은 데이터 모델에서 oracle 기준으로 sql을 작성하였다. 그러나 sql server에서도 동일한 결과를 보장할 수 있도록 ansi 구문으로 sql을 변경하려고 한다. 다음 중 아래의 sql을 ANSI표준 구문으로 변경한 것으로 가장 적절한 것은?

[SQL]

SELECT A.게시판ID, A.게시판명, COUNT(B.게시글ID) AS CNT
FROM 게시판 A, 게시판 B
WHERE A.게시판ID = B.게시판ID(+)
AND B.삭제여부(+) = 'N'
AND A.사용여부 = 'Y'
GROUP BY A.게시판ID , A.게시판명
ORDER BY A.게시판ID

 

ANSI SQL (American National Standards Institute)
DBMS들에서 각기 다른 SQL을 사용하므로, 미국 표준 협회에서 이를 표준화하여 표준 SQL문을 정립 시켜 놓은 것이다.

특징
1. 표준 SQL문이기 때문에 DBMS의 종류에 제약을 받지 않는다.
2. 테이블간의 JOIN관계가 FROM 에서 명시되기 때문에 WHERE문에서 조건만 확인하면 된다.

ORACLE SQL JOIN

TABLE1.COL1 = TABLE2.COL1(+) --왼쪽 외부조인
TABLE1.COL1(+) = TABLE2.COL1 --오른쪽 외부조인

 

--ANSI

SELECT A.게시판ID, A.게시판명, COUNT(B.게시글ID) AS CNT
FROM 게시판 A
LEFT OUTER JOIN B ON A.게시판ID = B.게시판ID AND B.삭제여부 = 'N'
WHERE A.사용여부 = 'Y'
GROUP BY A.게시판ID , A.게시판명
ORDER BY A.게시판ID

 

2. 다음과 같은 2개의 릴레이션이 있다고 가정하자. student의 기본키는 st_num이고, department의 기본키는 dept_num이다. 또한 student의 d_num은 department의 dept_num을 참조하는 외래키이다. 아래 sql문의 실행 결과 건수는?

SELECT COUNT(ST_NAME)
FROM STUDENT S
WHERE NOT EXISTS
	(SELECT *
    FROM DEPARTMENT D
    WHERE S.D_NUM = D.DEPT_NUM
    AND DEPT_NAME = '전자계산학과');

STUDENT
ST_NUM	ST_NAME	D_NUM
1001	YOO	10
1002	KIM	30
1003	LEE	20
1004	PARK	10
1005	CHOI	20
1006	JEONG	10

DEPARTMENT
DEPT_NUM	DEPT_NAME
10	컴퓨터공학과
20	원자력공학과
30	전자계산학과

답 : 5건

 

3. (SQL server) 다음중 아래의 sql과 동일한 결과를 추출하는 SQL은? (단, 테이블 TAB1, TAB2의 PK 컬럼은 A,B이다.)

SELECT A, B
FROM TAB1
EXCEPT
SELECT A, B
FROM TAB2;

집합 연산자 - EXCEPT 및 INTERSECT

EXCEPT는 오른쪽 입력 쿼리의 출력이 아닌 고유한 행을 왼쪽 입력 쿼리에서 반환합니다.
INTERSECT는 왼쪽 및 오른쪽 입력 쿼리 연산자 모두에서 출력되는 고유 행을 반환합니다.

SELECT TAB1.A, TAN1.B
FROM TAB1
WHERE TAB1.A NOT IN (SELECT TAB2.A FROM TAB2)
AND TAB1.B NOT IN(SELECT TAB2.B FROM TAB2);
--동시에 비교해야 함. 각각 비교로 틀린 문장

--정답
SELECT TAB1.A, TAB1.B
FROM TAB1
WHERE NOT EXISTS ( SELECT 'X'
				FROM TAB2
                WHERE TAB1.A = TAB2.A
                AND TAB1.B = TAB2.B);

 

4. 아래와 같은 데이터 모델에 대해 SQL을 수행하였다. 다음 중 수행된 SQL과 동일한 결과를 도출하는 SQL은?

SELECT A.서비스ID, B.서비스명, B.서비스URL
FROM (SELECT 서비스ID
	FROM 서비스
    INTERSECT
    SELECT 서비스ID
    FROM 서비스이용)A, 서비스 B
 WHERE A.서비스ID = B.서비스ID;

이용된 적이 있었던 서비스를 추출하는 쿼리이다.

 

--1
SELECT B.서비스ID, A.서비스명, A.서비스URL
FROM 서비스 A, 서비스이용 B
WHERE A.서비스ID = B.서비스ID
--서비스 이용 테이블에 있는 서비스ID 개수만큼 출력됨. 

--2
SELECT X.서비스ID
	,X.서비스명
    ,X.서비스URL
FROM 서비스 X
WHERE NOT EXISTS ( SELECT 1
	FROM ( SELECT 서비스ID
    	FROM 서비스
        EXCEPT
        SELECT 서비스ID
        FROM 서비스이용 ) Y
	WHERE X.서비스ID = Y.서비스ID);

답 2