2008년 4월 18일 금요일 문제 및 답안 ms-sql
2008년 4월 18일 금요일
주안점 : database(기초), table(기초), insert, 도움말(help), update, delete
“자바개발자과정” 제출자 이름 :
문제1) 난이도(평이) (table)
다음의 스키마(schema)를 보고 테이블을 작성하는 SQL을 작성하십시오.
단, 기본적으로 테이블이 소속된, 생성하고 사용할 데이터베이스는 “java_tempdb”라고 전제합니다.
보기) 테이블 이름 : java_temp_table
필드명(컬럼명) 자료형(데이터 타입) 기본값 설 명
User_id Varchar(10) No Null 회원 아이디
User_name Varchar(20) Not Null 회원 이름
User_level Char(1) 9 회원 등급
User_introduction Varchar(200) Null 회원 소개
단, 여기서 primary key(주 키)는 user_id 입니다.
답)
Create database java_tempdb; -- 데이터베이스 생성
Use java_tempdb; -- 데이터베이스 사용
create table java_temp_table (
user_id varchar(20) not null primary key,
user_name varchar(40) not null,
user_level char(1) default '9',
user_introduction varchar(200)
);
create table java_temp_table (
user_id varchar(20) not null primary key,
user_name varchar(40) not null,
user_level char(1) default '9',
user_introduction varchar(200)
);
혹은
create table java_temp_table (
user_id varchar(20) not null,
user_name varchar(40) not null,
user_level char(1) default '9', -- default는 기정값을 말하는 것입니다.
user_introduction varchar(200),
primary key (user_id) -- 제약조건을 사용한 경우
);
주의사항) 두번째의 방법으로 테이블을 작성할 때는 이미 동일한 이름의 테이블이 있기 때문에 테이블을 제거하고 테이블을 작성해야 합니다.
테이블 제거 명령은 다음과 같습니다.
drop table java_temp_table;
문제2) 난이도(중) (table)
다음 작성된 테이블 형성 조건문에서 문법적으로 잘못된 부분을 번호별로 찾아서 출력되는
에러 메시지를 복사하고 해결책을 설명하십시오.
create table java-tmp-tbl ( -- (1) 번
user_id varchar(20) not null, -- (2) 번
user_name varchar(40) not null, -- (3) 번
user_level int default '9', -- (4) 번
user_introduction varchar(8001), -- (5) 번
primary key user_id -- (6) 번
);
답)
각 번호 별 에러 메시지 와 설명
(1) 줄 1: '-' 근처의 구문이 잘못되었습니다.
; 테이블 이름에서는 문자를 연결할 때 띄워쓰기나 ‘-’ 을 이용하면 안됩니다.
문자를 연결할 경우는 ‘_’ 을 사용하십시오.
(5) 열 'user_introduction'에 대해 지정한 크기(8001)가 모든 데이터 형식에 대해 허용된 최대값(8000)을 초과했습니다.
; varchar 자료형은 8000자 이하일 때 조건에 맞습니다. (p.77)
(6) 줄 6: 'user_id' 근처의 구문이 잘못되었습니다.
; primary 제약조건을 사용할 때는 primary key (user_id) 라고 “( )”를 사용하여야 합니다.
주의사항) (4) 번이 문자열 같아서 에러가 날 것 같이 보이지만 의외로 정확하게 실행된다.
그러나, 만약 다음과 같은 경우가 된다면 아래와 같은 문제점이 발생합니다.
일단 테이블을 만들 경우는 에러 없이 처리가 됩니다.
그러나 데이터를 입력하는 insert 문을 사용하는 경우에는 에러가 나게 된다.
user_level int default 'a', -- (4) 번
특히 insert(자료 삽입 명령문) 하지 않은 상태에서 아래와 같이 단순히 검새만 한다면 문제가 발생하지 않습니다.
select * from java_tmp_tbl;
그러면 이 상태에서 데이터를 입력해봅니다.
insert into java_tmp_tbl (user_id, user_name, user_introduction)
values ('java_nut','자바광맨','나는 자바와 결혼했다');
다음과 같은 에러 메시지가 출력됩니다.
varchar 값 'a'을(를) int 데이터 형식의 열로 변환하는 중 구문 오류가 발생했습니다.
## 테이블 생성시와 단순 검색에서는 드러나지 않았지만 막상 레코드를 삽입할 경우에는 문제가 발생합니다. ##
그렇다면 이렇게 입력한다면 어떻게 될까요?
insert into java_tmp_tbl (user_id, user_name, user_level, user_introduction)
values ('java_nut2', '자바_광맨_2', 9, '나는 자바와 결혼했다');
입력이 성공적으로 되고 검색조회 하였을 때 전혀 문제가 발생하지 않습니다.
## 참고로 위의 경우는 다음과 같이 적을 수도 있습니다.
insert into java_tmp_tbl values ('java_nut2', '자바_광맨_2', 9, '나는 자바와 결혼했다'); ##
그러면 이렇게 하면 어떻게 될까요?
insert into java_tmp_tbl values ('java_nut3', '자바_광맨_2', '' , '나는 자바와 결혼했다');
이때 검색시 user_level의 값은 ‘0’으로 기록됩니다. User_level에 대한 자료형이 int(정수형)이기 때문에 정수형 자료형(int)의 초기값인 ‘0’이 기입되는 것입니다.
#### 요약하자면 (4)번의 경우는 초기값이 잘못 입력되었지만 테이블은 생성되며 데이터를 입력할 때 경우에 따라 문제가 발생하는 것을 알 수 있습니다. ######
문제3) 난이도(중) (table, insert)
다음과 같이 테이블을 생성한 이후에 아래와 같이 레코드를 삽입하였습니다.
문법적으로 어떠한 문제점이 발생하며 어떻게 교정하여 올바르게 입력할 수 있습니까?
create table tmp_test (
id varchar(20) not null,
name varchar(40) not null,
level int default 8,
intro varchar(800),
primary key (id)
);
insert into tmp_test
values
(joon_sam,1,'쭌~나','영~young~인~사람인~어질인~맛딛군하~');
답)
위와 같이 입력하면 아래와 같은 에러가 뜹니다.
이름 'joon_sam'은(는) 이 컨텍스트에서 사용할 수 없습니다. 여기서는 상수, 식, 변수만 사용할 수 있고 열 이름은 사용할 수 없습니다.
## 참고로 “컨텍스트(context)”는 text 형식으로 된 content를 두고 하는 말이며 자주 쓰는 말이니 외워둡시다. ####
insert into tmp_test
values
('joon_sam','쭌~나',1,'영~young~인~사람인~어질인~맛딛군하~');
문제4) 난이도(평이) (table, insert)
위 문제의 답과 같이 데이터가 제대로 입력되었을 때, 다음과 같이 입력되면 에러가 출력됩니다. 에러 메시지를 확인하고 답안에 에러 메시지를 복사하십시오.
insert into tmp_test
values
('joon_sam', '효인', 2, '셈~느끼해요~');
답)
서버: 메시지 2627, 수준 14, 상태 1, 줄 1
PRIMARY KEY 제약 조건 'PK__tmp_test__07F6335A'을(를) 위반했습니다. 'tmp_test' 개체에 중복 키를 삽입할 수 없습니다.
문이 종료되었습니다. 참고로 'PK__tmp_test__07F6335A'는 primary key의 이름으로써 이름을 명명하지 않는 경우 DB에서 임의로 명명해주는 것입니다.
설명) 위와 같은 에러는 여러분의 학습을 위해서 꼭 만나 보아야 하는 메시지입니다.
Primary key로 설정된 필드(컬럼)은 중복된 값(redundancy)을 기본적으로 허용하지 않습니다. 말 그대로 unique(독보적인) 조건을 만족시켜야 합니다. 또한 NULL 값이 기본적으로 올 수 없습니다. 또한 위의 메시지의 제약 조건을 보고자 한다면 아래와 같은 저장 프로시저(stores procedure)의 명령을 활용합니다.
sp_helpconstraint tmp_test;
참고로 primary key의 무결성에 대한 정리는 강의 사이트 317번 항목 게시판의 ppt(파워포인트) 교재 p.4 (네번째 페이지)를 보시기 바랍니다.
문제5) 난이도(평이) (도움말 : help)
위 문제에서 발생한 “에러 메시지 2627”를 온라인 도움말(help)에서 찾아서 그 뜻을 복사하여 기입하십시오.
답)
14 %1! 제약 조건 '%2!'을(를) 위반했습니다. '%4!' 개체에 중복 키를 삽입할 수 없습니다.
참고) ‘%1’ 과 같은 것은 무엇인가?
“iID” 라고 볼 수 있습니다. 참고로 “온라인 도움말”에서 “identity” 라는 말을 검색하였을 때 예제에 보면 등장하는 것을 알 수 있다. 일종의 인덱스(index) 같은 역할을 하는 id 로써 “identity id” 라고 볼 수 있다. 심화 학습에 해당되는 것이므로 추후 table 고급 편에서 다루도록 하겠습니다.
문제6) 난이도(평이) (도움말 : help)
오류 메시지 중 “심각도”의 의미가 무엇인지 파악하십시오. 단, 검색시 “검색” 코너를 이용하십시오.
답) 특히, “오류 메시지 심각도”라고 검색하고 위치(location)을 “문제 해결” 부분으로 선택하게 되면 심각도의 정도가 자세하게 나열되어 있다. 반드시 눈으로 확인하고 존재를 확인하도록 한다. “심각도 14”가 어떤 종류인지 살펴보면 다음과 같을 것이다.
심각도 0부터 19
심각도가 10인 오류 메시지는 정보용입니다. 심각도가 11부터 16인 오류 메시지는 사용자가 생성하므로 사용자가 해결할 수 있습니다. 심각도 17 및 18은 리소스 또는 시스템 오류가 발생할 때 생성됩니다. 사용자 세션은 인터럽트되지 않습니다.
심각도가 1부터 25인 사용자 정의 메시지는 sp_addmessage를 사용하여 sysmessages에 추가할 수 있습니다. 19부터 25까지의 심각도를 갖는 메시지는 시스템 관리자만 추가할 수 있습니다.
심각도가 17 이상인 오류 메시지는 시스템 관리자에게 보고해야 합니다.
(여기서 좀더 자세히 본다면…편집자 주)
심각도 11부터 16
이 수준의 메시지는 사용자가 해결할 수 있는 오류를 나타냅니다.
문제7) 난이도(평이) (도움말 : help)
용어집을 “기본 키”에 해당하는 설명을 찾아서 복사하여 기입하십시오. (반드시 2번 이상 정독합니다.)
답)
기본 키(PK) (primary key (PK))
테이블에서 모든 행을 고유하게 식별하는 열 또는 열 집합입니다. 기본 키에는 null 값을 지정할 수 없습니다. 두 행이 같은 기본 키 값을 가질 수 없으므로 기본 키 값은 항상 각 행을 고유하게 식별합니다. 테이블에서 각 행을 고유하게 식별할 수 있는 키는 하나 이상 있을 수 있으며 이 때 각 키를 후보 키라고 합니다. 하나의 후보만 테이블의 기본 키로 선택할 수 있으며 다른 후보 키는 모두 대체 키가 됩니다. 테이블에 반드시 기본 키를 지정할 필요는 없으나 기본 키를 정의하면 편리합니다. 정규화된 테이블에서 각 행의 모든 데이터 값은 기본 키에 종속됩니다. 예를 들어, 정규화된 직원 테이블의 기본 키가 EmployeeID 이고 모든 열에 특정 직원과 관련된 데이터가 있어야 합니다. 부서 이름은 직원 ID가 아닌 부서 ID에 종속되므로 이 테이블에는 DepartmentName 열을 포함할 수 없습니다
문제8) 난이도(중) (table)
다음 기술한 내용을 테이블로 작성하는 SQL문을 기입하십시오.
보기)
홍길동 씨는 지금 “홍길동 프로젝트” 라는 코드명으로 웹 프로젝트 기획을 하면서 회원가입양식에 따른 데이터베이스를 만들려고 합니다. 데이터베이스는 기존 프로젝트를 진행하면서 사용하고 있는 “hkd_project”를 사용하기로 하였고, 테이블의 이름은 “hkd_member_table”로 선정하기로 하였습니다.
문제9) 난이도(평이) (insert) (p.211)
“문제1”에서 작성한 “java_temp_table”에 다음의 주어진 레코드 데이터들을 삽입하십시오.
필드명(컬럼명) 자료형(데이터 타입) 기본값 설 명
User_id Varchar(10) No Null 회원 아이디
User_name Varchar(20) Not Null 회원 이름
User_level Char(1) 9 회원 등급
User_introduction Varchar(200) Null 회원 소개
회원 아이디
회원 이름 회원 등급 회원 소개
JSP 장성필 1 동의대 웹프로그래머
EJB 은지빈 3 평생교육원 프로그래머
SQL 서정렬 4 데이터베이스 관리자
JAVA 정자바 3 자바 프로그래머
답)
insert into java_table values
('JSP', '장성필', 1, '동의대 웹프로그래머');
insert into java_table values
('EJB',' '은지빈', 3, '평생교육원 프로그래머');
insert into java_table values
('SQL', '서정렬', 4, '데이터베이스 관리자');
insert into java_table values
('JAVA', '정자바', 3, '자바 프로그래머');
문제10) 난이도(평이) (Excel)
위의 결과를 ‘엑셀’에서 인식할 수 있는 파일로 저장하여 스크린 샷을 첨부하십시오.
답)
문제11) 난이도(평이) (도움말, 테이블관련 저장 프로시저)
위에서 작성한 테이블명(java_temp_table)을 ‘java_table’로 변경하되, 저장 프로시저(stored procedure)를 이용하여 변경하십시오. 도움말에서 검색하여 해당되는 저장 프로시저를 검색하십시오.
답)
sp_rename java_temp_table, java_table;
문제12) 난이도(평이) (update) (p.221)
위에서 작성한 테이블에서 회원 아이디가 JSP인 회원의 소개를 “동의대 웹개발자”로 변경하고자 합니다. SQL문을 작성하십시오.
답)
Update java_table
set user_introduction = '동의대 웹개발자'
where user_id = 'JSP';
문제13) 난이도(평이) (update)
위의 테이블에서 회원 등급이 3인 회원들의 등급을 2등급으로 조정하려고 합니다.
일괄적으로 조정하는 쿼리문을 작성하십시오.
답)
Update java_table
set user_level = 2
where user_level = 3;
문제14) 난이도(평이) (delete)
위의 테이블에서 “관리자”라는 문자열을 포함하고 있는 모든 레코드를 삭제하려고 합니다.
알맞은 쿼리문을 작성하십시오.
답)
delete from java_table
where user_introduction like '%관리자%';