모델링은 현실세계에 대해서 표현하는 것으로 이해할 수 있다. 다음 중 모델링의 특징으로 가장 부적절한 것은 ?
현실게계를 형식에 맞추어 추상화의 의미를 가질 수 있음
시스템 구현만을 위해 진행하는 사전단계 작업으로, db구축만을 위해 진행하는 사전단계 작업으로 데이터베이스 구축을 위한 사전 작업의미가 있음.
복잡한 현실을 제한된 언어나 표기법을 통해 이해하기 쉽게 하는 단순화의 의미
애매모호함을 배제하고 누구나 이해가능하게 정확하게 현상을 기술하는 정확화
< 해설 >
모델링은 단지 시스템 구현만을 위한것이 아닌 시스템 구현을 포함한 업무 분석 및 업무 형상화 목적이다.
다음 설명 중 데이터 모델링이 필요한 주 이유로 부적절한 것은 ?
업무정보를 구성하는 기초 정보에 대해 일정 표기법에 의해 표현한다.
분석 모델을 갖고 db를 생성해 개발 및 데이터 관리에 사용하기 위한것이다.
db를 구축하기 위한 용도를 위해 데이터모델링을 수행, 업무에 대한 설명은 별도의 표기법을 이용한다.
데이터모델링 자체로 업무 흐름을 설명,분석의 의미를 갖고있다.
< 해설 >
데이터 모델링을 하는 주요 이유는 , 업무정보를 구성하는 기초가 되는 정보들에 대해, 일정한 표기법에 의해 표현함으로써 정보시스템 구축의 대상이 되는 업무내용을 정확하게 분석하는 것이 첫번째 목적이다.
두번째는 분석된 모델을 가지고 실제 DB를 생성, 개발 및 관리해 사용하기 위한 것이다.
데이터모델링 자체로서 업무를 설명하고 분석하는 부분에도 매우 중요한 의미를 갖고있다.
데이터모델링의 유의사항으로 부적절한 것은?
여러 장소의 데이터베이스에 같은 정보를 저장하지 않도록 하여 중복성을 최소화 한다.
데이터 정의를 데이터 사용 프로세스와 분리해 유연성을 높인다.
사용자가 처리하는 프로세스나 장표 등에 따라 매핑이 될 수 있도록 프로그램과 테이블간의 연계성을 높인다.
데이터간 상호 연관관계를 명확하게 정의하여 일관성 있게 데이터가 유지되도록 한다.
< 해설>
데이터 모델링 유의사항 중복성, 비유연성, 비일관성
중복성 : 데이터모델은 같은 데이터를 사용하는 사람, 시간, 장소 파악에 도움을 줌으로써 데이터가 여러장소에 같은정보를 저장하는 잘못을 하지않도록 한다.
비유연성 : 설계에 따라 사소한 업무변화에도 모델이 변경됨으로써 , 유지보수의 어려움을 가중시킬 수 있다. 데이터의 정릐를 데이터의 사용프로세스와 분리함으로써 DB에 변화를 일으킬 가능성을 줄인다.
비일관성 : 데이터 중복이 없더라도 발생가능, 예로 신용상태에 대한 갱신없이 고객의 납부이력 정보를 갱신하는 경우, 데이터모델링을 할때, 데이터와 데이터간의 상호연관 관계에 대해 명확하게 정의한다면 이러한 위험을 사전에 예방하는데 도움을 줄 수 있다.
사용자가 처리하는 프로세스 혹은 이와 관련된 프로그램과 테이블의 연계성을 높이는 것은 데이터모델이 업무변경에 대해 취약하게 만드는 단점에 해당한다.
데이터 모델을 어떻게 설계했느냐에 따라 사소한 업무변화에도 데이터 모델이 수시로 변경됨으로써 유지보수의 어러움을 가중시킬 수 있다. 데이터의 정의를 데이터의 사용 프로세스와 분리함으로써 데이터 모델링은 데이터 혹은 프로세스의 작은 변화가 애플리케이션과 데이터베이스에 중대한 변화를 일으킬 수 있는 가능성을 줄인다. 는 특성은 ?
중복
비유연성
비일관성
일관성
전사적 데이터 모델링을 할때 많이 하며, 추상화 수준이 높고 업무 중심적이고 포괄적인 수준의 모델링을 진행하는 것을 ㄱ 데이터 모델링이라고 한다. 이와 달리 실제 db에 이식할수 있도록 성능,저장 등의 물리적인 성격을 고려한 모델링은 ㄴ 데이터 모델링이다.
ㄱ : 개념적 , ㄴ : 물리적
ㄱ : 논리적 , ㄴ : 개념적
ㄱ : 논리적 , ㄴ : 물리적
ㄱ : 개념적 , ㄴ: 논리적
< 해설 >
개념적, 물리적
다음중 ANSI-SPARC 에서 정의한 3단계 구조에서 아래 내용이 설명하는 스키마 구조로 적절한 것은 ?
📌
- 모든 응용세스템이나 사용자들이 필요로 하는 데이터를 통합한 조직 전체의 DB를 기술한 것으로, DB에 저장되는 데이터와 그들간의 관계를 표현한 스키마 - 모든 사용자 관점을 통합한 조직 전체 관점의 통합적 표현
외부 스키마
개념 스키마
내부 스키마
논리 스키마
< 해설>
개념적 스키마 : 통합관점의 스키마 구조를 표현한 것, 데이터모델링은 통합관점의 뷰를 갖고있는 개념스키마를 만들어가는 과정으로 이해할수 있다.
다음 중 엔터티의 특징으로 가장 부적절한 것은?
속성이 없는 엔터티는 있을 수 없다. 엔터티는 반드시 속성을 가져야 한다.
엔터티는 다른 엔터티와 관계가 있을 수 밖에 없다. 단 통계성 엔터티나 코드성 엔터티의 경우 관계를 생략 할 수 있다.
객체지향의 디자인패턴에는 싱글톤 패턴이 있어, 하나의 인스턴스를 가지는 클래스가 존재한다. 이와 유사하게 엔터티는 한개의 인스턴스를 가지는 것만으로도 충분히 의미를 부여할 수 있다.
데이터로서 존재하지만 업무에서 필요하지 않으면, 해당 업무의 엔터티로 성립될 수 없다.
< 해설 >
엔터티의 특징
반드시 담당업무에서 필요하고, 관리하고자 하는 정보여야한다.
유일한 식별자에 의해 식별이 가능해야 한다.
영속적으로 존재하는 (두개 이상의)인스턴스의 집합이여야 한다.
엔터티는 업무 프로세스에 의해 이용되어야 한다.
엔터티는 반드시 속성이 있어야한다.
엔터티는 다른 엔터티와 최소 한개 이상의 관계가 있어야한다.
다음 중 두개의 엔터티 사이에 정의한 관계를 체크하는 사항으로 가장 부적절한 것은 ?
두개의 엔터티 사이에 잇는 연관규칙이 존재하는가 ?
두개의 엔터티 사이에 정보 조합이 발생 되는가?
업무기술서, 장표에 관계연결을 가능케하는 명사(Noum)가 있는가?
업무기술서, 장표에 관계연결에 대한 규칙이 서술되어 있는가?
< 해설 >
업무 기술서, 장표연결에 가능케하는 동사(Verb)가 있는가? 되어야함.
동사는 관계를 서술하는 업무기술서의 가장 중요한 사항이다.
다음 중 비식별자 관계로 연결하는 것을 고려해야 하는 경우로 가장 부적절한 것은?
부모엔터티에 참조값이 없어도 자식엔터티의 인스턴스가 생성될 수 있는 경우
부모엔터티의 인스턴스가 자식 엔터티와 같이 소멸되는 경우
여러 개의 엔터티를 하나로 통합하면서 각각의 엔터티가 갖고있던 여러개의 개별 관계가 통합되는 경우
자식쪽 엔터티의 주식별자를 부모 엔터티와는 별도로 생성하는 것이 더 유리하다고 판단하는 경우
< 해설 >
엔터티별, 데이터 생명주기를 다르게 관리할 경우... 예를 들어
부모 엔터티의 인스턴스가 자식엔터티와 관계를 가지고 있었지만, 자식만 남겨두고 먼저 소멸될 수 있는 경우, 비식별자 관계로 연결하는것이 적절하다.
부모엔터티의 인스턴스가 자식엔터티와 같이 소멸되는 경우는 비식별자관계보다 식별자 관계로 정의하는 것이 좋다.
다음 중 성능 데이터 모델링에 대한 설명으로 가장 부적절한 것은?
성능이 저하된 결과를 대상으로 데이터 모델보다는, 문제발생 시점의 SQL 중심으로 집중해 튜닝한다.
데이터 증가가 빠를수록 성능저하에 따른 성능개선비용은 증가한다.
데이터모델은 성능 튜닝하면서 변경될 수 있는 특징이 있다.
분석/설계 단계에서 성능을 고려한 데이터 모델링을 수행할 경우, 성능 저하에 따른 Rework 비용을 최소화 할 수 있는 기회를 가지게 된다.
< 해설 >
분석/ 설계 단계에서 DB성능 향상 방법은 주도면밀하게 고려해야 한다.
만약 어떤 트랜잭션이 해당 비즈니스 처리에 핵심적이고, 사용자 업무처리에 있어 중요함을 가지고 있고, 성능이 저하되면 안된다면 !! 프로젝트 초기에 운영환경에 대비한 테스트 환경을 구현하고, 그곳에 트랜잭션을 발생시켜 실제 성능을 테스트해 보아야 한다.
이때, 데이터 성능이 좋은 모습으로 디자인하는 전략이 요구된다. 보기에서 문제 발생 시점의 SQL중심으로 집중 튜닝은 성능 데이터 모델링과 무관한 내용이다.
아래와 같은 보관금원장 엔터티에서 관서에 대한 정보가 반정규화 되어 있기 때문에, 관서정보를 조회할 때, 성능저하가 발생하고 있다. 이 엔터티에 대해 몇차 정규화가 필요한지와 분리된 스키마 구조를 가장 바르게 짝지은것은 ?
1차 정규화가 필요한 엔터티로서 매각기일과 일자별 매각물건으로 1:M 관계가 될 수 있다.
1차 정규화가 필요한 엔터티로서 매각기일과 일자별 매각물건으로 1:1 관계가 될 수 있다.
2차 정규화가 필요한 엔터티로서 매각기일과 일자별 매각물건으로 1:M 관계가 될 수 있다.
2차 정규화가 필요한 엔터티로서 매각기일과 일자별 매각물건으로 1:1 관계가 될 수 있다.
< 해설 >
매각 기일은 일자별로 매각이 시행되는 장소와 시간을 의미하는 것
일자별매각물건 엔터티의 매각시간, 매각장소 속성은 두개의 주식별자 속성 중에서 매각일자에만 종속되기 떄문에, 2차정규화 대상이 된다.
그러므로 매각일자를 주식별자로, 매각시간, 장소 속성을 포함하는 매각기일 엔터티를 독립시킨다. 이때 매각기일 엔터티는 일자별매각물건의 주식별자중 일부로서 독립했기 때문에, 매각기일과 매각일자별물건은 1:M 관계로 연결된다.
2차 정규화를 통해 특정 장소에서 이루어진 매각내역을 조회하고자 할때, 100만건의 일자별 매각내역 데이터를 모두 읽어 원하는 장소에 해당하는 인스턴스들을 찾아 매각일자별로 그룹핑한 후 매각일자별매각내역과 조인할 필요가 없이, 매우 적은 수의 매각기일 엔터티에서 특정 장소에 해당하는 매각일자들을 찾아 매각일자별 매각내역과 1:1로 바로 조인하면 되기 때문에 I/O 를 현저하게 함소시킬수 있어, 성능향상 효과를 얻을 수 있다.
13. 다음 중 데이터 모델에 대한 반정규화를 고려할 때, 판단요소에 대한 설명으로 가장 적절한 것은?
반정규화 정보에 대한 재현의 적시성으로 판단한다. 예를들어 빌링의 잔액은 다수 테이블에 대한 다량의 조인이 불가피하므로 데이터 제공의 적시성 확보를 위한 필수 반정규화 대상 정보이다.
탐색 대상 데이터의 크기로 판단한다. 대량 데이터에 대한 인덱스 활용한 탐색은 RANDOM 처리의 특성으로 성능저하가 불가피하기 때문이다.
RDBMS 는 현재 레코드 기준으로 이전 또는 이후 위치의 레코드에 대한 접근이 원천적으로 불가능함으로 반정규화하지 않으면, 해당 정보에 대한 데이터 접근 자체가 불가능하다.
반정규화 테이블은 집계 테이블에 국한하여 적용한다.
< 해설 >
대량 데이터 탐색의 경우, 인덱스가 아닌 파티션 및 데이터 클러스터링 등의 다양한 물리 저장기법을 활용해, 성능개선을 유도할수 있다. 다만, 하나의 결과셋을 추출하기 위해 다량의 데이터를 탐색하는 처리가 반복ㅂ적으로 빈번하게 발생한다면, 이때는 반정규화를 고려하는 것이 좋다.
이전 또는 이후 위치의 레코드에 대한 탐색은 WINDOW FUNCTION 으로 접근 가능하다.
집계테이블 이외에도 다양한 유형( 다수 테이블의 키 연결 테이블 등 ) 에 대하여, 반정규화 테이블 적용이 필요할 수 있다.
다음 중 아래 주문, 주문목록, 제품에 대한 데이터 모델과 이를 이용해 데이터를 조회하는 SQL 문에서 조회를 빠르게 수행하기 위한 반정규화 방법으로 가장 적절한 것은 ?
📌
SELECT A 주문번호, SUM(C. 단가)
제품 엔터티에 단가를 합한 계산된 칼럼을 추가하도록 한다.
주문목록 엔터티에 단가를 합한 계산된 칼럼을 추가하도록 한다.
주문 엔터티에 단가를 합한 계산된 칼럼을 추가하도록 한다.
제품 엔터티에 최근값 여부에 대한 칼럼을 추가하도록 한다.
< 해설 >
제품 엔터티에 단가를 주문번호별로 합하는 것은, 해당 제품이 여러 주문에 포함될 수 있기 떄문에, 특정 주문번호만의 단가 합계금액을 갖고 있을 수 없다.
주문목록 엔터티에 주문번호별 단가 합계 금액을 추가하게 되면 하나의 주문에 포함된 제품번호마다 동일한 합계 금액을 반복적으로 저장해야해서 일관성 문제가 발생할 수 있다.
제품 엔터티에 최근 값 여부칼럼을 추가하는 것은 단가합계 금액을 빠르게 얻기위한 반정규화와 무관한 조치이다.
그러므로 주문엔터티에 전체를 통합한 계산된 칼럼을 추가하는것이 한번에 데이터를 조회하는 벙법이 된다. → 효과적 반정규화 방법
다음 중 아래 데이터모델에 대한 설명으로 가장 부적절한 것은?
📌
- 공급자의 데이터는 1000만건 이상의 대량데이터를 가진 테이블이다.
공급자별로 최근에 변경된 전화번호, 메일주소, 위치와 공급자 이름을 같이 조회할 때 이 값들을 공급자 테이블에 반정규화로 갖고 있는 경우에 비해 조회성능이 저하되지 않는다.
데이터를 조회할때 과도한 조인으로 인해 조회 성능이 저하될 수 있으므로 공급자 테이블에 가장 빈번하게 조회되는 값인, 최근 변경값에 해당하는 전화번호, 메일주소, 위치를 반정규화해 조회성능을 향상시킬 수 있다.
전화번호, 메일주소, 위치에 대한 가장 최근에 변경된 값을 알 수 있도록 최신여부라는 속성을 추가함으로써 최근값을 찾기위한 조회성능 저하를 예방할 수 있다.
조회성능을 위해서는 하나의 테이블로 통합하여, 전화번호, 메일주소 위치등이 변경될 경우 전체 속성이 계속 발생되는 이력의 형태로 설계 될수있다. 이럴경우 조회에 대한 성능은 향상되나, 과도한 데이터가 한 테이블에 발생하게 되어 용량이 너무 커지는 단점이 있다.
< 해설 >
최근에 변경된 값만을 조회할 경우, 과도한 조인으로 인해 성능이 저하되어 나타내게 된다.
다음중 논리 데이터모델의 슈퍼타입/서브타입 데이터모델을 물리적 테이블 형식으로 변환 시 설명으로 가장 부적절한 것은?
트랜잭션은 항상 전체를 대상으로 처리하는데, 테이블은 서브타입별로 개별 유지하는것으로 변환하면 Union 연산에 의해 성능이 저하될 수 있다.
트랜잭션은 항상 서브타입 개별로 처리하는데 테이블은 하나로 통합하여 변환하면, 불필요하게 많은 양의 데이터가 직접되어 있어 성능 저하 될 수 있다.
트랜잭션은 항상 슈퍼+서브 타입을 함께 처리하는데, 개별로 유지하면 조인에 의해 성능이 저하 될 수 있다.
트랜잭션은 항상 전체를 통합해 분석처리하는데, 하나로 통합되어 있으면 데이터 집적으로 인해 성능이 저하될 수 있다.
< 해설 >
트랜잭션은 항상 전체를 통합하여 분석처리하는데, 슈퍼-서브타입이 하나의 테이블로 통합되어 있으면 하나의 테이블에 직접된 데이터만 읽어 처리할수 있기 때문에, 다른형식에 비해 성능이 우수하다.
다음중 아래와 같은 '현금출급기실적'테이블과 이 테이블에서 데이터를 조회할떄 사용되는 아래의 SQL 패턴에 대한 설명으로 적절한 것은 ?
📌
SELECT 건수, 금액
사무소코드가 '='로 상수값이 들어왔고, 거래일자가 범위 BETWEEN 으로 들어왔기 떄문에, 거래일자 + 사무소코드 + 출급기번호 + 명세표번호 순서로 구성된 PK 인덱스는 최적의 효율을 가진다.
사무소코드가 '='로 상수값이 들어왔고, 거래일자가 범위 BETWEEN 으로 들어왔기 떄문에, 사무소코드 + 출급기번호 + 명세표번호 +거래일자 순서로 구성된 PK 인덱스는 최적의 효율을 가진다.
사무소코드가 '='로 상수값이 들어왔고, 거래일자가 범위 BETWEEN 으로 들어왔기 떄문에, 거래일자 + 출급기번호 + 명세표번호 + 사무소코드 순서로 구성된 PK 인덱스는 최적의 효율을 가진다.
사무소코드가 '='로 상수값이 들어왔고, 거래일자가 범위 BETWEEN 으로 들어왔기 떄문에, 사무소코드 + 거래일자 + 출급기번호 + 명세표번호 순서로 구성된 PK 인덱스는 최적의 효율을 가진다.
< 해설 >
인덱스의 값의 범위에 따라 일정하게 정렬되어 있으므로 상수값으로 EQAUL 조건으로 조회되는 칼럼이 가장 앞으로 나오고, 범위조회하는 유형이 칼럼 그 다음에 오도록 하는것이 인덱스 액세스 범위를 좁힐 수 있는 가장 좋은 방법이다.
다음중 데이터가 여러지역에 분산되어 있지만, 하나의 데이터베이스 처럼 사용하기를 원하는 분산데이터베이스 환경에서 데이터베이스 분산설계를 적용하여 효율성을 증대시킬수 없는 것은?
공통코드, 기준정보 등 마스터 데이터는 분산데이터베이스에 복제분산을 적용한다.
거의 실시간 업무적인 특성을 가지고 있을때, 분산데이터베이스 사용하여 구성할 수 있다.
백업사이트를 구성할때, 간단하게 분산기능을 적용하여 구성할 수 있다.
Global Single Instance 를 구성할때 분산 DB를 활용하여 구성하는것이 효율적이다.
< 해설 >
Global Single Instance는 통합된 한개의 인스턴스, 즉 통합데이터베이스 구조를 의미함= 분산DB와 반대개념
공통코드 , 기준정보 같은 마스터 데이터를 한곳에 두고, 운영하는 경우 원격지에서의 접근이 빈번할 수록 실시간 업무처리에 대해 좋은 성능을 얻기 어려울 수 있기 때문에, 분산 환경에 복제분산을 하는 방법으로 분산 DB구성 가능,
백업 사이트 구성도 적용가능
아래 내용에 해당하는 SQL 명령어의 종류를 작성하시오.
📌
논리적인 작업의 단위를 묶어 DML에 의해 조작된 결과를 작업단위별로 제어하는 명령어인 COMMIT , ROLLBACK, SAVEPOINT 등이 여기 해당되며, 일부에서는 DCL로 분류하기도 한다.
< 해설 >
TCL : COMMIT, ROLLBACK, SAVEPOINT
DML : SELCT , INSERT , UPDATE, DELETE
DDL : CREATE, ALTERT, DROP, RENAME
DCL : GRANT, REVOKE
DB를 정의하고 접근하기위해, DBMS의 통신수단이 필요한데, 이때 데이터 언어라고 하며, 그기능과 사용목적에 따라 DDL, DML, DCL로 구분된다. 데이터 언어와 SQL명령어에 대한 설명으로 가장 부적절한 것은?
비절차적 데이터 조작어(DML) 는 사용자가 무슨 데이터를 원하며, 어떻게 그것을 접근해야 되는지를 명세하는 언어이다.
DML은 DB사용자가 응용프로그램이나, 질의어를 통해 저장된 DB를 실질적으로 접근하는데 사용되며 SELECT, INSERT, DELETE , UPDATE 등이 있다.
DDL은 스키마, 도메인, 테이블 , 뷰 , 인덱스를 정의하거나 변경 또는 제거할때 사용되면 CREATE , ALTER , DROP, RENMAE 등이 있다.
호스트 프로그램속에 삽입되어 사용되는 DML 명렁어들을 데이터부속어라고 한다.
< 해설 >
AS - IS : 비 절차적 데이터 조작어 DML은 사용자가 무슨 데이터를 원하는지 명세함
TO - BE 비절차적 데이터 조작어 DML은 사용자가 무슨 데이터를 원하는지만을 명세하지만 절차적 데이터 조작어는 어떻게 (HOW ) 데이터를 접근해야하는지 명세한다. 절차적 데이터 조작어는 PL/SQL ( ORACLE ) , T-SQL ( SQL - SERVER ) 이 있다.
아래의 데이터 모델과 같은 테이블 및 PK 제약조건을 생성하는 DDL문장으로 올바른것은?
📌
PRODUCT_ID :VARCHAR2(10) NOT NULL
< 해설 >
CREATE TABLE PRODUCT ( PROD_ID VARCHAR2(10) NOT NULL, PROD_NM VARCHAR2(100) NOT NULL, REG_DT DATE NOT NULL, REG_NO NUMBER(10), CONSTRAINT PRODUCT_PK PRIMARY KEY (PROD_ID) );
혹은 바로 PK를 붙여도 된다.
아래와 같이 데이터가 들어있지 않은 왼쪽의 기관분류 테이블을 오른쪽과 같이 변경하고자 할때 올바른 SQL 문장은? ( DBMS = SQL SERVER )
📌
분류 ID : VARCHAR(10) NOT NULL
⏬⏬⏬⏬⏬⏬⏬⏬⏬
📌
분류 ID : VARCHAR(10) NOT NULL
< 해설 >
ALTER TABLE 기관분류 ALTER COLUMN 분류명 VARCHAR(30) NOT NULL;
ALTER TABLE 기관분류 ALTER COLUMN 등록일자 DATE NOT NULL;
아래와 같은 테이블 구조를 정의하려고 한다. 이때 아직 부서가 정의되지 않은 사원은 기본부서 ( 코드 : 0000 ) 로 배치하고, 입사일자 ( JOIN_DATE) 기준으로 많은 조회가 발생하므로 입사일자에 대한 INDEX 를 생성하려고 한다. 다음중 올바른 문장 2개를 고르시오. ( 테이블 생성, 변경 )
📌
EMP_NO : VARCHAR2(10) NOT NULL
< 해설 >
CREATE TABLE EMP (
EMP_NO VARCHAR2(10) PRIMARY KEY,
EMP_NM VARCHAR2(10) NOT NULL,
DEPT_CODE VARCHAR2(4) DEFAULT '0000' NOT NULL,
JOIN_DATE DATE NOT NULL,
REGIST_DATE DATE NULL
); CREATE INDEX IDX_EMP_01 ON EMP (JOIN DATE);
CREATE TABLE EMP (
EMP_NO VARCHAR2(10) NOT NULL,
EMP_NM VARCHAR2(10) NOT NULL,
DEPT_CODE VARCHAR2(4) DEFAULT '0000' NOT NULL,
JOIN_DATE DATE NOT NULL,
REGIST_DATE DATE NULL );
ALTER TABLE EMP ADD CONSTRAINT EMP_PK PRIMARY KEY(EMP_NO);
CREATE INDEX IDX_EMP_01 ON EMP (JOIN_DATE);
다음중 외래키에 대한 설명으로 가장 부적절한 것 2개는?
테이블 생성시 설정할 수 있다.
외래키 값은 널값을 가질 수 없다.
한 테이블에 하나만 존재해야 한다.
외래키 값은 참조 무결성 제약을 받을 수 있다.
< 해설 >
외래키는 널값 가능하며, 한테이블에 여러개 존재 가능하다.
다음 중 데이터베이스 테이블 제약조건에대한 설명으로 부적절한 것은?
CHECK 제약조건은 DB에서 데이터의 무결성을 유지하기 위해 테이블의 특정칼럼에 설정하는 제약이다.
기본키는 반드시 테이블당 하나의 제약만을 정의할 수 있다.
고유키(UNIQUE)로 지정된 모든 칼럼들은 NULL 값을 가질 수 없다.
외래키는 테이블 간 관계를 정의하기 위해, 기본키를 다른 테이블의 외래키가 참조하도록 생성한다.
< 해설 >
고유키 UNIQUE 는 NULL 값을 가질 수 있음.
아래 데이터모델과 같이 주문 데이터가 생성되어, 고객과 주문 테이블에 입력되어있는 데이터는 아래 표와 같다. 이떄 FK_001 이라는 제약조건을 아래 SQL과 같이 생성하였다. 다음중 오류없이 정상적으로 수행되는 SQL 2가지는?
📌
[SQL] ALTER TABLE 주문 ADD CONSTRAINT FK_001 FOREIGN KEY ( 고객 ID ) REFERENCES 고객 ( 고객ID ) ON DELETE SET NULL;
INSET INTO 고객 VALUES ('C003', '강감찬' , '2014-01-01')
INSET INTO 주문 VALUES ('0005', 'C003', '2013-12-28' )
DELETE FROM 주문 WHERE 주문번호 IN ('0001', '0002' )
DELETE FROM 고객 WHERE 고객ID = 'C002'
< 해설 >
2는 고객테이블에 존재하지않는 고객ID 주문을 입력하려고 하여 무결성제약오류가 발생한다.
4번은 고객테이블의 고객 ID C002를 삭제할때 SQL에 의해 추가된 제약조건에따라 주문테이블의 고객 ID를 NULL로 업데이트하려고 DBMS에서 시도하지만, 주문 테이블 고객 ID 칼럼에 NOT NULL제약조건이 먼저 걸려있어서 실패한다.
개발 프로젝트의 표준은 모든테이터를 삭제 데이터에 대한 로그를 남기는것을 원칙으로 테이블 삭제의 경우 허가된 인력만이 정기적으로 수행가능하도록 정하고있다. 개발팀에서 사용용도가 없다고 판단한 A 테이블을 삭제하는 방법은?
DELETE FROM A;
TRUNCATE TABLE A;
DROP TABLE A;
< 해설>
로그를 남기는것은 DELETE ,
구조를 남기는것은 TRUNCATE
구조까지 삭제하는 것은 DROP
고객지열 테이블을 거주지와 근무지가 같은사람이 많아 중복을 제거하고 출력하고자 한다면 SQL 문안에 들어가야할 내용으로 알맞은 것은?
📌
[SQL] SELECT [ ] 거주지, 근무지 FROM 고객지역;
< 해설 >
DISTINCT
다음 중 DELETE 와 TRUNCATE, DROP 명령에 대해서 부적절한 것을 고르시오 2개
특정 테이블에 대해 WHERE 조건절이 없는 DELETE 명령을 수행하면 DROP 테이블과 똑같은 결과를 얻을 수 있다.
DROP 명령어는, 테이블 정의 자체를 삭제하고 TRUNCATE 는 테이블을 초기상태로 만든다.
TRUNCATE 명령어는 UNDO를 위한 데이터를 생성하지 않기 때문에, 동일 데이터량 삭제시 DELETE 보다 빠르다.
DROP 은 AUTO COMMIT 이고, DELETE 와 TRUNCATE는 사용자 COMMIT 이다.
< 해설 >
DROP
DDL
ROLLBACK 불가능
AUTO COMMIT
테이블이 사용했던 스토리지를 모두 릴리즈
테이블 정의 자체를 완전히 삭제한다.
TRUNCATE
DDL ( 일부 DML 성격도 가진다 )
ROLLBACK 불가능
AUTO COMMIT
테이블이 사용했던 스토리지중 최초의 테이블 생성시 할당된 스토리지만 남기고 릴리즈
테이블을 최초생성된 초기상태로 만듬
DELETE
DML
COMMIT 이전ROLLBACK 가능
사용자 COMMIT
데이터를 모두 DELETE 해도, 사용했던 스토리지는 릴리즈 되지 않음
데이터만 삭제,
로그를 남김
아래와 같은 DDL 문장으로 테이블 생성후 SQL 수행시 옳은 것은?
CREATE TABLE 서비스 (
서비스 번호 VARCHAR2(10) PRIMARY KEY,
서비스명 VARCHAR2(100) NULL,
게시일자 DATE NOT NULL );
ㄱ. SELECT * FROM 서비스 WHERE 서비스번호 = 1;
ㄴ. INSERT INTO 서비스 VALUES ('000', '', '2015-11-11');
ㄷ. SELECT * FROM 서비스 WHERE 서비스명 ='';
ㄹ. SELECT * FROM 서비스 WHERE 서비스명 IS NULL;
서비스번호 칼럼에 모든 레코드 중에서 001과 같은 숫자형식으로 하나의 레코드만이라도 입력되어 ㄱ은 오류없이 실행된다.
ORACLE에서 ㄴ과 같이 데이터를 입력했을 때, 서비스명 칼럼에 공백문자 데이터가 입력된다.
ORACLE에서 ㄴ과 같이 데이터를 입력하고, ㄷ과 같이 조회시 데이터 조회된다.
SQL SERVER에서 ㄴ과 같이 데이터를 입력하고 ㄹ과 같이 조회했을때 데이터는 조회되지 않는다.
< 해설 >
서비스번호 칼럼의 모든 레코드가 001과 같이 입력되어야 한다. 1≠001
ㄴ과 같이 입력시, 오라클에서는 NULL로 입력된다.
ㄴ과 같이 입력된것을 조회하려면 IS NULL 로 조회해야 한다.
SQL 서버에서는 ㄴ='' 과 같이 조회가 가능하다.
아래와 같은 내장함수에 대한 설명중에서 옳은것을 모두 묶은 것은?
📌
가) 함수의 입력 행수에 따라 단일행 함수와 다중행 함수로 구분할 수 있다.
가
가, 나
가, 나, 다
가, 나, 다, 라
< 해설 >
다 ) 1:M 조인이라 하더라도 M쪽에 출력된 행이 하나씩 단일행 함수의 입력값으로 사용되므로 사용할 수 있다.
라 ) 다중행 함수도 단일행 함수와 동일하게 단일 값만 반환한다.
아래는 SEARCH_CASE_EXPREESION 문장이다. 이때 사용된 SEARCH_CASE_EXPREESION은 SIMPLE CASE_EXPRESSION을 이용해 똑같은 기능을 표현할 수 있다. 아래 SQL 문장 A에 들어갈 말로 알맞은 말은?
[ SEARCH_CASE_EXPREESION 문장 사례 ]
SELECT LOC,
CASE WHEN LOC ='NEW YORK' THEN 'EAST'
ELSE 'ETC'
END as AREA
FROM DEPT;
[SIMPLE_CASE_EXPREESION 문장 사례 ]
SELECT LOC,
CASE [ A ]
ELSE 'ETC'
END as AREA
FROM DEPT;
< 해설 >
LOC WHEN 'NEW YORK' TEHN 'EAST'
다음중 아래 테이블을 보고 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') =NULL; → 실행결과는 1
< 해설 >
NVL ( 표현식1, 표현식 2) : 표현식 1의 결과가 NULL 이면 표현식2 반환
NVL2 ( 표현식1, 표현식 2, 표현식 3) : 표현식 1의 결과가 NULL 이면 표현식3 반환, 아니면 2 반환
ISNULL ( 표현식1, 표현식 2) : 표현식 1의 결과가 NULL 이면 표현식2 반환
NULLIF ( 표현식1, 표현식 2) : 표현식 1과 표현식2의 결과가 같으면 NULL, 아니면 표현식1 반환
COALESCE ( 표현식1, 표현식 2 ....) : 임의의 개수 표현식에서 NULL이 아닌 최초의 표현식
다음중 아래에 들어갈 함수를 차례로 작성하시오
📌
ㄱ ( 표현식1, 표현식 2 ) : 표현식 1의 결과가 NULL 이면 표현식2 반환
< 해설 >
ㄱ : ISNULL 또는 NVL
ㄴ : NULLIF
ㄷ : COALESCE
다음중 아래 각각 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
< 해설 >
첫번째로 조회되는 데이터는 20+0 → 2건으로 나누면 10이됨
두번쨰는 COL이 NULL 인두번쨰행은 NULL 연산 제외 조건으로 제외된다. WHERE 절에의해 세번쨰행도 연산 대상에서 제외된다. 20 → 한건으로 값은 20
세번쨰 COL1이 두번쨰행은 NOT NULL 조건으로 인해 제외된다. 그다음행 OCL3의 NULL은 AVG 연산 대상에서 제외된다. 1건 조회 20 /1 → 20
어느 기업의 직원 테이블 ( EMP ) 이 직급 별로 사원 500명 ,대리 100명, 과장 30명, 차장 10명, 부장 5명 , 직급이 정해지지 않은 사람 (NULL) 로 구성되었을때, 순차적으로 실행한 결과 건수를 순서대로 나열한 것은 ?
SQL 1) SELECT COUNT(GRADE) FROM EMP;
SQL 2) SELECT GRADE FROM EMP WHERE GRADE IN('차장','부장','널');
SQL 3) SELECT GRADE, COUNT(*) FROM EMP GROUP BY GRADE;
670, 15, 5
645, 40, 5
645, 15, 6
670, 40, 6
< 해설 >
SQL 1 → NULL 제외 , 645명
SQL 2 → '널' 이라는 텍스트는 없음 → 15명
SQL 3 → NULL 포함 6건
다음중 오류가 발생하는 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 SYSEDATE -1 AND SYSDATE GROUP BY 메뉴ID, 사용유형코드;
SELECT 메뉴 ID, 사용유형코드, AVG(COUNT(*)) AS AVGCNT FROM 사용이력 GROUP BY 메뉴ID, 사용유형코드;
< 해설 >
3 GROUP BY로 그룹핑된 칼럼에 대해 HAVING 조건절을 사용할 경우 집계된 칼럼의 FILTER 조건으로 사용할 수 있다. 이런경우 HAVING 절에 집계함수 없이 사용할수가있다.
4 중첩된 그룹함수의 경우 최종 결과값은 1건이 될수 밖에 없기에 GROUP BY 절에 기술된 메뉴 ID와 사용유형코드는 SELECT절에 기술될 수 없다.
다음중 SQL 오류가 발생하는 것은?
SELCT 지역, 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;
< 해설 >
2 SQL 실행순서에 의하면 SELECT 절 이후에 ORDER BY 절이 수행되기 떄문에 SELECT 절에 기술되지 않는 년 칼럼으로 정렬하는것은 논리적으로 맞지 않다. 하지만 오라클은 행기반 DB 이기 때문에 SELECT 절에 기술되지 않은 칼럼으로 정렬이 가능하다. 단 아래와 같은 SQL 일경우에 정렬이 가능하다. SELECT 지역 매출금액 FROM ( SELECT 지역, 매출금액 FROM 지역별매출 ) ORDER BY 년 ASC;
이는 인라인뷰가 먼저 수행됨에 따라 더이상 SELECT 절 외 칼럼을 사용할 수 없기 떄문이다.
3 GROUP BY 를 사용할 경우 GROUP BY 표현식이 아닌 값은 기술될 수 없다.
4 GROYP BY 표현식이기 때문에 가능 .
다음 SQL 실행결과는 ?
SELECT ID, AMT
FROM TBL
ORDER BY (CASE WHEN ID='A' THEN 1 ELSE 2 END), AMT DESC
< 해설 >
CASE 절을 이용해서 원래 정렬순서를 변경했다. A 인것 먼저 정렬 , 그안에서 DESC
아래의 데이터 모델을 참고해, 설명에 맞게 올바르게 작성하시오 .
📌
- 고객에게 컨텐츠를 추천한다.
< 해설 >
SELECT C.컨텐츠 ID, C.컨텐츠명
FROM 고객 A INNER JOIN 추천컨텐츠 B
ON (A.고객ID = B.고객ID) INNER JOIN 컨텐츠 C
ON (B.컨텐츠ID = C.컨텐츠ID) 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 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.컨텐츠ID)
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 );
아래는 어느 회사 생산설비를 위한 데이터 모델의 일부에 대한 설명으로 가장 적절한것 2가지를 고르시오.
제품,생산제품, 생산라인 엔터티를 INNER JOIN 하기 위해선 생산제품 엔터티에는 WHERE 절이 최소 2번 필요하다.
제품과 생산라인 엔터티를 JOIN 시 적절한 JOIN 조건이 없으므로 카타시안 곱이 발생한다.
제품과 생산라인 엔터티에는 생산제품과 대응되지 않는 레코드는 없다.
특정 생산라인번호에서 생산되는 제품의 제품명을 알기위해서는 제품, 생산제품, 생산라인까지 3개의 엔터티의 INNER JOIN 이 필요하다.
< 해설 >
대응되는 레코드는 있을 수 있다.
이너조인은 2개면 된다.
다른 팀(TEAM) 테이블과 구장(STADIUM) 테이블의 관계를 이용해서 소속팀이가지고 있는 전용구장의 정보를 팀의 정보와 함께 출력하는 SQL을 작성할 때, 결과가 다른것은?
SELECT T.REGION_NAME, T.TEAM_NAME, T.STADIUM_ID, S.STADIUM_NAME FROM TEAM T INNER JOIN STADIUM S USING (T.STADIUM_ID = S.STADIUM_ID);
SELECT TEAM.REGION_NAME, TEAM.TEAM_NAME, TEAM.STADIUM_ID, STADIUM.STADIUM_NAME FROM TEAM INNER JOIN STADIUM ON (TEAM.STADIUM_ID = STADIUM.STADIUM_ID);
SELECT T.REGION_NAME, T.TEAM_NAME, T.STADIUM_ID, S.STADIUM_NAME FROM TEAM T INNER JOIN STADIUM S WHERE T.STADIUM_ID = S.STADIUM_ID;
SELECT TEAM.REGION_NAME, TEAM.TEAM_NAME, TEAM.STADIUM_ID, STADIUM.STADIUM_NAME FROM TEAM, STADIUM WHERE TEAM.STADIUM_ID = STADIUM.STADIUM_ID;
< 해설 >
TEAM, STADIUM 두 테이블을 조인해 사용한다.
USING 조건절을 이용한 EQUI JOIN 에서도 NATURAL 조인과 마찬가지로조인칼럼에 대해서는 ALIAS 나 테이블 이름과 같은 접두사를 붙일 수 없다. 지문 1은 SYNTAX 에러 발생.
USING ( STADIUM_ID) 이렇게 고쳐주어야함 T.STADIUM_ID 가 아닌 그냥 STADIUM_ID 로 고쳐야함
아래 테이블에 SQL문을 적용한 결과는 ??
SELECT A.고객번호, A.고객명, B.단말기ID, B.단말기명, C.OSID, C.OS명
FROM 고객 A LEFT OUTER JOIN 단말기 B
ON (A.고객번호 IN (11000, 12000) AND A.단말기ID = B.단말기ID) LEFT OUTER JOIN OS C
ON (B.OSID = C.OSID)
ORDER BY A.고객번호;
< 해설 >
홍길동 강감찬만 적용됨
다음 1,2,3,의 실행결과가 같은 것은 ?
1) SELECT A.ID , B.ID
FROM TBL1 A FULL OUTER JOIN TBL2 B
ON A.ID = B.ID
2) SELECT A.ID , B.ID
FROM TBL1 A LEFT OUTER JOIN TBL2 B
ON A.ID = B.ID
UNION
SELECT A.ID , B.ID
FROM TBL1 A RIGHT OUTER JOIN TBL2 B
ON A.ID = B.ID
3) SELECT A.ID , B.ID
FROM TBL1 A, TBL2 B
WHERE A.ID = B.ID
UNION ALL
SELECT A.ID ,NULL
FROM TBL1 A
WHERE NOT EXISTS (SELECT 1 FROM TBL2 B WHERE A.ID = B.ID)
UNION ALL
SELECT NULL, B.ID
FROM TBL2 B
WHERE NOT EXISTS (SELECT 1 FROM TBL1 A WHERE B.ID = A.ID );
< 해설 >
모두 FULL OUTER JOIN 이다.
신규 부서의 경우 일시적으로 사원이 없는 경우도 있다고 가정하고 DEPT 와 EMP를 조인하되 , 사원이 없는 부서 정보도 같이 출력하도록 할 때 , 아래의 SQL문장의 ㄱ 안에 들어갈 내용은 ?
SELECT E.ENAME, D.DEPTNO, D.DNAME
FROM DEPT D [ ㄱ ] EMP E
ON D.DEPTNO = E.DEPTNO;
< 해설 >
LEFT OUTER JOIN , LEFT JOIN
테이블 A와 B가 있을때 , A가 기준이되고 AB를 비교해서 B의 JOIN 칼럼에서 같은 값이 있을때 B 테이블에서 해당데이터를 가져오고 B의 JOIN 칼럼에서 같은 값이 없는 경우에는 B테이블에서 가져오는 칼럼들은 NULL 값으로 채운다. 그리고 LEFT JOIN으로 OUTER 키워드를 생략해서 사용 가능하다.
아래와 같은 데이터 모델에서 ORACLE을 기준으로 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;
< 해설 >
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;
보기는 게시판별 게시글을 조회하는 SQL이다. 이떄 게시글이 존재하지 않는 게시판도 조회되어야 하는데, 오라클에서 아우터조인 구문을 ( + ) 기호를 사용해 처리할수도 있음, 이를 ANSI 문장으로 변경하기 위해서는 INNER 쪽 테이블에 조건절 ON절에 함꼐 위치시켜야 정상적인 OUTER 조인이 가능하다.
아래와 같은 모델에 대해 SQL을 수행시, 다음 SQL과 동일한 결과를 도출하는 SQL은?
SELECT A.서비스ID, B.서비스명, B.서비스URL
FROM (SELECT 서비스ID
FROM 서비스
INTERSECT
SELECT 서비스ID
FROM 서비스이용) A, 서비스 B
WHERE A.서비스ID = B.서비스ID;
< 해설 >
SELECT X.서비스ID , X.서비스명, X.서비스URL
FROM 서비스X
WHERE NOT EXISTS ( SELECT 1
FROM (SELECT 서비스ID
FROM 서비스
MINUS
SELECT 서비스ID
FROM 서비스이용) Y
WHERE X.서비스ID = Y.서비스ID);
SQL은 이용된적 있었던 서비스를 호출하는 SQL이다.
해답의 SQL은 전체서비스에서 이용된적 있었던 서비스를 MINUS 하였으므로 이용된적 없었던 서비스가 서브쿼리에 추출된다 → NOT EXISTS 에 적용하면 이용된적 있었던 서비스가 출력된다
다음중 아래 테이블에대한 SQL을 수행한 결과는 ?
SELECT COL1, COL2 COUNT(*) AS CNT
FROM ( SELECT COL1, COL2
FROM TBL1
UNION ALL
SELECT COL1, COL2
FROM TBL2
UNION
SELECT COL1, COL2
FROM TBL1)
GROUP BY COL1, COL2;
< 해설 >
SQL에서 위에서 정의된 연산자가 수행된다. UNION이 나중에 나옴으로 중복데이터가 제거되어 아래와같은 결과가 나온다.
다음중 ORACLE 계층형 질의에 대한 설명으로 가장 부저적한 것은 ?
START WITH 절은 계층구조의 시작점을 가정하는 구문이다.
ORDER SIBLINGS BY 절은 형제노드사이에서 정렬을 지정하는 구문이다.
순방향 전개란 부모노드로부터 자식노드방향으로 전개하는 것이다.
루트노드의 LEVEL 값은 0이다.
< 해설 >
루트노드 레벨값은 1이다.
다음중 계층형 질의문에 대한 설명으로 가장 부적절한 것은?
SQL SERVER에서의 계층형 질의문은 CTE를 재귀호출함으로써 계층 구조를 전개한다.
SQL SERVER 에서 계층형 질의문은 앵커멤버를 실행해 기본결과 집합을 만들고, 이후 재귀 멤버를 지속적으로 실행한다.
오라클의 계층형 질의문에서 WHERE 절은 모든 전개를 진행한 이후, 필터조건을 만족하는 데이터만을 추출하는데 활용한다.
오라클의 계층형 질의문에서 PRIOR 키워드 CONNECT BY절에만 사용할수 있으며, PRIOR 자식 = 부모 형태로 사용하면, 순방향전개로 수행된다.
< 해설 >
PRIOR 자식- 부모 전개에서는 SELECT , WHERE 절에서도 사용이 가능하다.
아래의 부서와 매출 테이블에 대해서 SQL 문장을 시행하여, 아래결과와 같이 데이터가 추출되었다. 다음중 동일 결과를 추출하는 SQL문장은 ?
< 해설 >
SELECT A.부서코드, A.부서명, A.상위부서코드, B.매출액, LVL
FROM ( SELECT 부서코드, 부서명, 상위부서코드, LEVEL AS LVL
FROM 부서
START WITH 부서코드 ='120'
CONNECT BY PRIOR 상위부서코드 = 부서코드
UNION
SELECT 부서코드, 부서명, 상위부서코드, LEVEL AS LVL
FROM 부서
START WITH 부서코드 ='120'
CONNECT BY 상위부서코드 = PRIOR 부서코드) A LEFT JOIN 매출 B
120을 기준으로 순방향 , 그리고 역방향 한번을 합쳐 나오는 결과
다음 중 SELF JOIN을 수행해야 할 경우로 가장 적절한 것은?
한 테이블 내 두칼럼이 연관관계가 있다.
두 테이블에 연관칼럼은 없으나, JOIN을 해야한다.
두 테이블에 공통 칼럼이 존재하고, 두 테이블 연관관계가 있다.
한 테이블 내에서 연관된 칼럼은 없으나 JOIN을 해야한다.
< 해설 >
셀프조인은 , 한 테이블에서 두 칼럼의 연관관계를 갖고 조인하는 것이다.
아래와 같은 테이블이 존재할때, 아래 결과처럼 일자별 누적 매출액을 구하려고 한다.. WINDOW FUNCTION을 사용하지않고 일자별 누적매출액을 구하는 SQL로 옳은 것은?
< 해설 >
SELECT A.일자, SUM(B.매출액) AS 누적매출액
FROM 일자별매출 A JOIN 일자별매출액 ON(A.일자 >= B.일자)
GROUP BY A.일자
ORDER BY A.일자;
아래는 서브쿼리에 대한 설명이다. 다음 중 올바른것끼리 묶인 것은?
📌
가) 서브쿼리는 단일행 또는 복수행 비교 연산자와 함께 사용할 수 있다.
나, 라, 마
가, 나, 라
나, 다, 라
가, 나, 마
< 해설 >
다) 서브쿼리 결과가 복수행의 결과를 반환하는 경우 IN, ALL, ANY 등의 복수행 비교연산자와 사용해야한다.
마) 다중 칼럼 서브쿼리는 서브쿼리의 결과로 여러개의 칼럼이 반환되어, 메인 쿼리의 조건과 비교되는데 SQL SERVER 에서는 현재 지원하지 않는다.
아래의 데이터 모델을 기준으로 SQL을 작성했다. 다음중 아래 SQL에 가장 바르게 설명한 것은 ?
< 해설 >
이벤트의 시작일자가 2014.10.01과 같거나, 큰 이벤트를 기준으로 단 한차례라도 이메일 발송이 누락된 회원을 추출하는 SQL문이다.
ㄴ을 제거하고, ㄱ의 EXISTS 연산자를 IN연산자로 변경하게 되면, 회원별로 메일을 발송한 건수를 계산할 수 없음으로 , 원하는 결과를 얻을 수 없다.
GROUP BY 및 집계함수를 사용하지 않고 HAVING절을 사용하였다고 하여 SQL문장이 오류가 발생하지 않는다.
38. 다음중 서브쿼리에 대한 설명으로 가장 적절한 것은 ?
단일행 서브쿼리는 서브쿼리의 실행결과가 항상 한건 이하인 서브쿼리로서 IN ALL 등의 비교 연산자를 사용해야 한다.
다중행 서브쿼리 비교연산자는 단일행 서브쿼리의 비교연산자로도 사용할 수있다.
연관서브쿼리는 주로 메인쿼리에 값을 제공하기 위한 목적으로 사용한다.
서브쿼리는 항상 메인쿼리에서 읽혀진 데이터에 대해 서브쿼리 해당조건이 만족하는지를 확인하는 방식으로 수행한다.
< 해설 >
단일행 서브쿼리의 비교연산자로는 =, <, ≤, ≥, <> 가 되어야한다. IN ALL 등의 비교연산자는 다중행 서브쿼리의 비교연산자이다.
단일행 서브쿼리의 비교연산자는 다중행 서브쿼리의 비교연산자로 사용할 수 없지만, 반대의 경우는 가능하다.
비 연관 서브쿼리가 주로 메인쿼리에 값을 제공하기 위한 목적으로 사용된다.
메인 쿼리의 결과가 서브쿼리로 제공될 수 있고 , 서브쿼리의 결과가 메인쿼리로 제공될 수 도있으므로 실행 순서는 상황에따라 달라진다.
다음중 아래 SQL 설명으로 부적절한 것은?
SELECT B.사원번호, B.사원명, A.부서번호, A.부서명
,(SELECT COUNT(*) FROM 부양가족 Y WHERE
Y.사원번호 = B.사원번호) AS 부양가족수
FROM 부서 A, ( SELECT *
FROM 사원
WHERE 입사년도 = '2014') B
WHERE A.부서번호 = B.부서번호
AND EXISTS ( SELECT 1 FROM 사원 X WHERE X.부서번호= A.부서번호);
위 SQL은 다중행 연관 서브쿼리, 단일 행 연관 서브쿼리, INLINE VIEW 가 적용되었다.
SELECT 절에 사용된 서브쿼리는 스칼라 서브쿼리라고 하며, 이러한 형태의 서브쿼리는 JOIN 으로 동일한 결과 추출이 가능하다.
WHERE 절의 서브쿼리에서 사원테이블 검색조건으로 입사년도 조건을 FROM 절의 서브쿼리와 동일하게 추가해야 원하는 결과를 추출할 수 있다.
FROM 절의 서브쿼리는 동적 뷰 라고 하며, SQL문장중 테이블명이 올수있는곳에서 사용할 수 있다.
< 해설 >
2014년에 입사한 사원들의 사원, 부서 정보와 부양가족수를 추출한다.
SELECT 절에 사용된 서브쿼리는 단일행 연고나 서브쿼리로 JOIN 으로 변경가능하며,
FROM 절에 사용된 서브쿼리는 INLINE VIEW 또는 DYNAMIC VIEW 이고
, WHERE 절에 사용된 서브쿼리는 다중행 연관 서브쿼리이다.
3번 보기의 경우, 이미 FROM 절에 인라인뷰로, 사원테이블의 입사년도 조건을 명시했으므로 WHERE 절의 EXISTS 조건은 부서와 사원ㅌ이블간의 JOIN 조건에 의해 결과에 어떠한 영향도 미치지 못하므로 삭제되어도 무방하다.
아래와 같은 데이터 모델에서 평가상품에대한 SQL문장으로 옳은것은 ?
< 해설 >
1번 인라인뷰 D 에서 평가결과 엔터티의 특정상품 및 평가항목에 대한 최종 평가회차가 아닌 전체 데이터중 평가회차가 가장 큰 값을 가지고 조인하므로 원하는 결과가 아니다.
2번 연관 서브쿼리를 활용해 특정 상품 평가항목별 최종 평가회차의 조인을 수행해 원하는 결괄를 출력한다.
다음 중 뷰에대한 설명으로 부적절한 것은?
뷰는 단지 정의만 가지고 있으며, 실행 시점에 질의를 재작성해 실행한다.
뷰는 복잡한 SQL문장을 단순화 시켜주는 장점이 있는 반면, 테이블 구조가 변경되면 응용프로그램을 변경해야한다.
뷰는 보안강화를 위한 목적으로도 활용할수 있다.
실제 데이터를 저장한 뷰를 생성하는 기능을 지원하는 DBMS도 있다.
< 해설 >
뷰의 장점중 독립성은, 응용프로그램을 변경하지않아도 된다.
아래 데이터에서 SQL을 이용해 표같은 형식의 데이터를 추출하려고 할때, 올바른 SQL문장은 ?
< 해설 >
위의 결과 데이터는 지역에 대해서 월별 이용량 및 소계와 전체 이용량을 출력하였으므로 ROLLUP 함수를 활용할 수 있다.
롤업 집계 그룹함수는 나열된 칼럼에 대해 계층형 구조로 집계를 출력하는 함수로 ROLLUP (A, B)를 수행하면, (A,B) 별 집계, A 별 집계와 전체집계를 출력할 수 있다.
1번 보기는 CASE절의 GROUPING 함수의 사용이 잘못되었음 ( 0이아닌 1이 되어야함 )
다음 설명중 가장 적절한 것은?
일반 그룹 함수를 사용하여 CUBE, GROUPING SETS 와 같은 그룹 함수와 동일한 결과를 추출할 수 있으나, ROLL UP 그룹함수와 동일한 결과를 추출할 수 없다.
GROUPING SETS 함수의 경우에는 함수의 인자로 주어진 칼럼의 순서에 따라 결과가 달라지므로, 칼럼의 순서가 중요하다.
CUBE , ROLLUP, GROUPING SETS 함수의 대상 칼럼중 집계된 칼럼이외의 대상 칼럼 값은, 해당 칼럼의 데이터중 가장 작은 값을 반환한다.
CUBE 그룹 함수는, 인자로 주어진 칼럼의 결합 가능한 모든 조합에 대해서 집계를 수행하므로 다른 그룹함수에 비해 시스템에 대한 부하가 크다.
< 해설 >
1번 CUBE, GROUPING SETS ROLLUP 세가지 그룹함수 모두 일반 그룹함수로 동일한 결과를 추출가능
2번 함수인자로 주어진 칼럼의 순서에 따라 다른 결과를 추출하게 되는 그룹함수는 ROLLUP 이며, 나열된 칼럼에따라 계층구조로 집계를 출력한다.
3번 CUBE, ROLLUP, GROUPING SETS 의 함수들에 의해 집계된 레코드에서 집계 대상 칼럼이외의 그룹대상 칼럼의 값은 NULL을 반환한다.
ㄱ에 들어갈 말로 옳은것은?
1. GROUP BY CUBE (자재번호, (발주처ID, 발주일자))
GROUP BY CUBE (자재번호, 발주처ID, 발주일자)
GROUP BY GROUPING SETS(자재번호, 발주처ID, 발주일자)
GROUP BY GROUPING SETS(자재번호, (발주처ID, 발주일자))
< 해설 >
ROLLUP, CUBE, GROUPING SETS 집계 그룹 함수
문제의 결과 데이터는 ( 자재번호별) sub total 과 (자재번호 발주처별) SUB TOTAL을 출력하고있다. GROUPING
자재번호들을 합쳐서 집계된 행은 자재번호가 null 로 표현되고, GROUPING(자재번호) = 1
일단 CUBE는 아니고 GROUPING SETS인데
정답이 3번인 경우,
발주처ID 각각에 대한 SUM
발주일자 각각엔 대한 SUM이 있어야 합니다.
그런데 발주처ID + 발주일자를 조합한 것을 key로 하여 SUM이 구해졌습니다.
예를 들어,
001+20150102
001+20150103...이런식으로..
그래서 정답은 4번입니다.
다음중 월별 매출 테이블 대상으로 아래 sql을 수행한 결과는 ?
< 해답 >
그룹핑셋츠 함수는 표시된 인수들에 대한 집계를 구하는 기능을 하며, 위 sql은 ( 상품 id, 월) 별 집계 데이터를 출력한다.
< 해설 >
DENSE_RANK는 숫자를 건너뛰지 않음 RANK와 비교
답은 RANK, → 게임상품별로 고객목록 추출은 OVER 절에 PARTITION BY 게임상품 ID 를 적용해야함
< 해설 >
ROW NUMBER 함수는 ORDER BY절에 의해 정렬된 데이터에 동일 값이 존재하더라도 유일한 순위를 부여하는 함수로서, 데이터 그룹내에 유일한 순위를 추출할 때 사용하는 함수이다.
문제의 SQL은 추천경로별 ( PARTIOTION BY 추천경로 ) 로 추천점수가 가장 높은 (ORDER BY 추천점수 DESC) 데이터를 한건씩 출력하지만 1번은 데이터 그댈 ㅗ출력, 2번은 전체에서 추천점수가 가장 높은데이터 1건만 남기고 출력 4번은 추천경로별 추천점수가 가장낮은 데이터를 각 1건씩 출력했기 때문에 , 정답은 3번이다.
< 해설 >
안쪽 인라인 뷰에의해 사원 id와 부서별 최고 연봉이 결과로 생성되며, 이를 다시 사원 테이블과 사원id=사원id and 최고연봉=연봉 으로 조인을해, 부서별 최고연봉의사원이 출력된다.
아래의 sql과 같은 결과이다.
SELECT 사원ID, 사원명, 부서ID, 연봉
FROM ( SELECT 사원ID, 사원명, 부서ID, 연봉
,MAX(연봉) OVER (PARTITION BY 부서ID) AS 최고연봉 FROM 사원 )
WHERE 연봉 = 최고연봉
< 해설 >
LAG 함수 : 현재읽혀진 데이터의 이전값
LEAD 함수 : 이후값을 알아내는 함수
위의 SQL에서 레코드별 FLAG1, FLAG2의 값은
FLAG 1 : 0,1,1,1,0,1
FLAG 2: 1,1,1,0,1,0
메인쿼리의 WHERE 절의 FLAG1 =0 OR FLAG2 =0 이므로 1,4,5,6 Q번째 행이 출력된다
결국엔 인라인 뷰만 보시면 됩니다.
동일하게 ID, START_VAL, END_VAL을 뽑는데 END_VAL에서 NULL값이면 99로 치환해서 뽑습니다.
그럼 저 테이블과 동일한데 END_VAL의 마지막 값만 NULL 대신 99인겁니다.
그러고 나서 FLAG1, FLAG2 칼럼을 뽑습니다.
ID I START_VAL I END_VAL I FLAG1 I FLAG2
FLAG1의 경우, START_VAL 값이 이전 행의 END_VAL과 같은 경우에는 1이 채워지고 다르다면 0이 채워집니다.
첫행의 START_VAL은 10인데 이전 행은 없으니 비교할 수 없습니다. 그러므로 0이 채워집니다.
두번째 행의 START_VAL은 14인데 이전 행의 END_VAL은 14입니다. 동일하니 1이 채워집니다.
세번째 행의 START_VAL은 15이고 이전 행의 END_VAL은 15입니다. 동일하니 1이 채워집니다.
이런식으로 쭉쭉 내려가시면 됩니다.
FLAG2의 경우, END_VAL값이 다음 행의 START_VAL과 같은 경우에는 1이 채워지고 다르다면 0이 채워집니다.
첫행의 END_VAL은 14, 다음 행의 START_VAL은 14입니다. 동일하니 1이 채워집니다.
두번째 행의 END_VAL은 15, 다음 행의 END_VAL은 15입니다. 동일하니 1이 채워집니다.
네번째 행의 END_VAL은 18, 다음 행의 START_VAL은 20입니다. 값이 다르니 0이 채워집니다.
ORDER BY 절에 순서가 START_VAL 오름차순, END_VAL 오름차순인데 NULL 값일 시 99로 채워집니다.
이 인라인 뷰가 끝나고 나서
WHERE 절에서 FLAG1이 0이거나 FLAG2가 0인 것을 뽑으라 했으니
1번 보기 처럼 뽑아집니다.
사용자 LEE가 릴레이션 R생성후, 아래와 같은 권한부여 SQL실행했다. 가능한 2개는 ? A,B는 정수다
📌
LEE : GRANT SELECT, INSERT, DELETE ON R TO KIM WITH GRANT OPTION
PARK : SELECT * FROM R WHERE A =400;
PARK : INSERT INTO R VALUES(400,600);
PARK : DELETE FROM R WHERE B=800;
KIM : INSERT INTO R VALUES (500,600);
< 해설 >
LEE : GRANT SELECT, INSERT, DELETE ON R TO KIM WITH GRANT OPTION
→ 김에게 테이블 R에대한 셀,인,델 권한을 주며 김이 이권한을 다른사람에게 줄수있음
KIM : GRANT SELECT, INSERT, DELETE ON R TO PARK
→ 김이 박에게 셀인델 권한을 줌
LEE : REVOKE DELETE ON R FROM KIM
→ 김에게 삭제권한회수
LEE : REVOKE INSERT IN R FROM KIM CASCADE;
→ 김과 박에게 인서트 권한 취소,
WITH GRANT OPTION으로 김에게 받은 박의 권한은 CASCADE 명령어로 받은권한을 취소할수있다.
다음 중 PL/SQL 에 대한 설명으로 가장 부적절한 것은?
변수와 상수등을 사용해, 일반 SQL 문장을 실행시 WHERE 조건 등으로 대입가능하다.
프로시저, USER DEFINED FUNCTION, 트리거 객체를 PL/SQL로 작성할 수 있다.
PL/SQL로 작성된 프로시저, USER DEFINED FUNCTION 은 전체가 하나의 트랜잭션으로 처리되어야한다.
프로시저 내부에 작성된 절차적 코드는 PL/SQL 엔진이 처리하고, 일반적인 SQL문장은 SQL실행기가 처리한다.
< 해설 >
문제를 잘읽자.
PL/SQL로부터 작성된 프로시저, USER DEFINED FUNCTION 은 작성자 기준으로, 트랜잭션을 분할할수있으며, 또한 프로시저 내에서 다른 프로시저를 호출할 경우에 호출 프로시저의 트랜잭션과는 별도로 PRAGMA AUTONOMOUS_TRANSACTION을 선언해 자율 트랜잭션 처리할수있다.
