자바기반 앱개발 & 웹서비스 구축개발 :: SQL활용 문제와 답

이번 SQL 활용 테스트에서는 SQL 문 작성능력을 테스트하는 것으로 50분간 13문항의 문제를 풀면 됩니다.

---

자바기반 앱개발 & 웹서비스 구축개발 :: 애플리케이션테스트 문제와 답

자바기반 앱개발 & 웹서비스 구축개발 :: 프로그래밍언어활용 문제와 답

자바기반 앱개발 & 웹서비스 구축개발 :: 애플리케이션 배포 문제와 답

자바기반 앱개발 & 웹서비스 구축개발 :: 데이터베이스 구현 문제와 답

자바기반 앱개발 & 웹서비스 구축개발 :: 응용소프트웨어기초기술활용 문제와 답

---

 

1.  DML에 해당하는 3가지 명령어를 적으시오.

>> 단일행 함수와 WHERE 절을 이용한 DML 명령문 작성 테스트.

답 : INSERT, DELETE, UPDATE
DML : data manipulation language ( 조작어 ) :  select, insert, update, delete
DDL : data definition language ( 정의어 )  : create, alter, drop, rename, truncate - 데이터구조와 관련
DCL : data control language ( 제어어 ) : grant, revoke - db접근사용권한
TCL : transaction control language : commit, rollback, savepoint -  논리적 작업의 단위를 묶어서 DML에 의해 조작된 결과를작업단위(트랜젝션) 별로 제어하는 명령어

 

 

 

 

다음 그림과 같이 사원 (emp) 테이블과 부서 (dept) 테이블, 데이터를 참고하여 제시된 문제에 답하시오.

학과 (dept)

deptno	dname
101	경영학과
102	컴퓨터공학과
103	영문학과

 

학생 (student)

studno	name	grade	deptno	profno
101	김기현	1	101	1001
102	김민영	2	101	1003
103	김정환	3	101	1001
104	김준태	4	101	1003
105	김지용	1	102	1002
106	김진	2	102	1002
107	김찬권	3	102	1002
108	김옥규	1	103	1004
109	박원영	2	103	1006
110	박의종	3	103	1004

교수 (professor)

profno	name	deptno	position	pay
1001	홍길동	101	정교수	450
1002	김연아	102	정교수	400
1003	박지성	101	부교수	350
1004	김태근	103	정교수	410
1005	서찬수	101	전임강사	250
1006	김수현	103	부교수	350
1007	정동진	102	전임강사	320
1008	임진영	103	전임강사	200

 

 

2. dept (학과) 테이블을 다음과 같이 생성하기 위한 쿼리를 작성하라.
deptno 고정길이 문자 3으로 지정하고 기본 키로 지정, dname 가변길이 문자 20으로 지정하고 not null 제약조건을 설정한다.

DDL 명령문을 작성 가능

create table dept (
	deptno char(3),
	dname varchar(20) not null,
	constraint "PK_DEPT" primary key (deptno)
);

 

 

 

 

3. dept (학과) 테이블에 데이터를 입력하는 쿼리를 작성하시오. (단 1개의 레코드 입력만 서술)

DML 명령문을 작성 가능

insert into dept(deptno, dname)
values ('123', 'hong');

 

 

 

 

4. student (학생) 테이블과 dept(학과) 테이블을 deptno를 참조하여 EQUI JOIN(등가 조인) 하는 쿼리를 작성하라.
(단 select 절의 칼럼은 편의상 * 으로 표시한다.)

조인을 이용하여 데이터를 조회하는 DML 작성 여부

select * 
from student s, dept d
where s.deptno = d.deptno;

 

 

 

5.  student (학생) 테이블의 deptno는 현재 dept (학과) 테이블의 deptno 를 참조하고 있다. student(학생) 테이블의 studno가 110인 사람의 deptno를 104로 수정을 해야한다면 수정을 할 수 없다. 그 이유는 무엇이며 꼭 수정해야한다면 어떻게 해야할 지 서술하시오. (쿼리 작성이 아닌 해결방법만 서술하시오)

조인을 이용하여 데이터를 조회하는 DML 작성 여부

>> 학생테이블에는 deptno 중 104가 없기 때문에 참조할 수 없다. 꼭 104로 수정하려면 학과테이블에 deptno 가 104인 데이터가 추가되어야 한다.

 

 

 

 

6. professor(교수) 테이블에서 그룹함수를 사용하여 deptno별로 pay의 총합을 출력하는 쿼리를 작성하시오 (출력되는 칼럼은 deptno와 pay만 있으면 됨)

행을 조회하는 DML명령문 작성 여부

select deptno, sum(pay)
from professor
group by deptno;

 

 

 

 

7. student 테이블에서 grade별로 학생수에 대한 소계값을 구해야 한다면 어떤 함수를 사용해야 하는가?

행을 조회하는 DML명령문 작성 여부

>> rollup 이나 cube
- rollup :  group by 절에 의해 그룹지어진 집합결과에 대해서 상세한 정보를 반환하는 기능을 수행
select절에서 선택된 데이터와 그 데이터의 총계를 구할 수 있다
- cube :  결합 가능한 모든 값에 대하여 다차원 집계를 생성. 내부적으로는 Grouping Columns의 순서를 바꿔어서 또 한 번의 Query를 추가 수행해야 한다. 뿐만 아니라 Grand Total은 양쪽의 Query 에서 모두 생성이 되므로 한 번의 Query에서는 제거 되어야만 하므로 ROLLUP에 비해 시스템의 연산 대상이 많다.

 

 

 

8. 그룹함수는 where 절에서 비교할 수 없다. 그렇다면 그룹함수는 어떻게 조건을 비교해야 하는가?

행을 조회하는 DML명령문 작성 여부
>> having 사용

 

 

 

9. 괄호 안에 들어갈 명령문을 완성하시오.

DML이 일어나면 트랜잭션의 완료와 취소를 위한 DCL명령문이 필요하다. 데이터의 변경사항을 데이터베이스에 영구히 기록시키는 것을 (           )이라고 하며, 원래 상태로 돌리는 것을 (          )이라고 한다.

 

업무단위인 트랜젝션의 완료와 취소를 위한 DCL명령문의 작성 여부
>> commit, rollback

 

 

 

10. 테이블 조회시에 하드디스크나 데이터버퍼캐시에 접근하기 이전에 제약조건이나, 해당테이블의 존재 유무 등을 파악하기 위해 참고하는 것을 데이터 딕셔너리라고 한다. dept(학생) 테이블의 데이터 딕셔너리를 조회하는 쿼리를 작성하시오. (단 select절의 칼럼은 편의상 * 으로 표시한다.)

데이터 사전을 조회하는 명령문을 작성할 수 있어야 한다.

select *
from user_tables
where table_name = 'dept';

 

 

 

11. student (학생) 테이블에서 grade별로 학생수에 대한 소계값을 구하기 위해서는 복수행함수를 사용하면 쉽게 구할 수 있다. 복수행 함수를 사용하지 않고 소계값을 구하기 위해서는 여러개의 select문을 합치는 UNION을 사용하면 된다. UNION과 UNION ALL에 대한 차이는 무엇인가?

집합연산자에 대한 이해가 있어야 한다.

>> select절을 합치는 키워드이며 UNION은 중복된 값을 배제하고 UNION ALL은 모든 결과를 합친다.
(UNION과 UNION ALL은 두 개의 select절을 합친다는 면에서는 동일하나, UNION은 중복된 값을 배제하고 Order by를 해준다는 면에서  UNION ALL 과 차이가 있다. 두 개의 집합을 합치는 것이 목적이라면 UNION을 사용하는 것보다 UNION ALL을 사용하는 것이 바람직하다. 그 이유는 중복된 값을 배제한다던지 정렬을 해주는 추가 연산이 들어가지 않기 때문이다.)

 

 

 

12. 학생 전화번호의 지역번호가 '02'이면 '서울', '051'이면 '부산', '052'이면 '울산', '053'이면 '대구', 나머지는 '기타'로 출력하여라.

name	userid	tel
전인하	jun123	051) 781-2158
이동운	Dals	055) 426-1752
박미경	anse1414	055) 261-8947
김영균	mandu	051) 824-9637
박동진	Ping2	051) 742-6384
김진영	simply	055) 419-6328
지은경	Gomo00	055) 418-9627
오유석	yousuk	051) 724-9618
하나리	hanal	055) 296-3784

 

CASE WHEN THEN 또는 DECODE를 이용한 DML을 작성할 수 있어야 한다

select name, tel,
	case(substr(tel,1,instr(tel,')')-1)) when '02' then '서울'
		when '031' then '경기'
		when '051' then '부산'
		when '052' then '울산'
		when '055' then '경남'
		else '기타'
		from student
		where deptno=101;
--decode
select name, tel,
	decode((substr(tel,1,instr(tel,')'-1)), '02', '서울',
		'031','경기',
		'051','부산'
		'052','울산'
		'055','경남','기타') "지역명"
from student
where deptno=101;

 

 

 

 

13. 사용자 아이디에서 문자열의 길이가 7  이상인 학생의 이름과 사용자 아이디를 출력하여라.

where절과 단일행 함수를 활용한 DML명령문을 작성할 수 있어야 한다.

select name, userid
from student
where length(userid)>=7;