SQL 테이블생성, 자료입력, 자료수정 문제 풀이

문제풀이

 

#1 문제

 

=================

“School”이라는 새 데이터베이스를 생성합니다. 이 데이터베이스에는 두 테이블, teachers (교사)와 students (학생)가 있어야 합니다.

students 테이블에는 student_id, first_name (이름), last_name (성), homeroom_number (교실 번호), phone (전화번호), email (이메일), graduation year (졸업 연도) 열이 있어야 합니다.

teachers 테이블에는 teacher_id, first_name (이름), last_name (성), homeroom_number (교실 번호), department (부서), email (이메일), phone (전화번호) 열이 있습니다.

 

제약 조건은 대부분 여러분 마음대로 하면 되지만, 테이블 제약 조건으로는 다음을 고려해야 합니다:

 

1. 비상시 학생에게 연락할 수 있는 전화번호가 있어야 합니다.

2. 테이블의 기본키로 id가 있어야 합니다.

3. 전화번호와 이메일은 개인별로 모두 달라야 합니다.

 

표를 만들었으면, 전화번호가 777-555-1234이고 이메일이 없는 Mark Watney (student_id=1)라는 학생을 삽입하세요. 그는 2035년에 졸업하고 교실 번호는 5번입니다.

 

그다음 생물학 부서에 있고 교실 번호가 5인 Jonas Salk (teacher_id = 1)라는 교사를 삽입합니다. 연락처 정보는 jsalk@school.org이고 전화번호는 777-555-4321입니다.

 

=================

---

 

#2 기본 환경 만들기

 

# 학생영역

CREATE TABLE students(

student_id SERIAL PRIMARY KEY,

first_name VARCHAR(30) NOT NULL,

last_name VARCHAR(30) NOT NULL,

homeroom_number VARCHAR(100) UNIQUE NOT NULL,

phone VARCHAR(12),

email VARCHAR(50),

graduation_year TIMESTAMP NOT NULL

)

 

# 선생님 영역

CREATE TABLE teachers(

student_id SERIAL PRIMARY KEY,

first_name VARCHAR(30) NOT NULL,

last_name VARCHAR(30) NOT NULL,

homeroom_number VARCHAR(100) UNIQUE NOT NULL,

department VARCHAR(100) NOT NULL,

email VARCHAR(50),

phone VARCHAR(12)

 

)

---

#2 에러 발생 및 문제 찾아가기

 

1)

SELECT 로 두 테이블 모두 확인해봤더니 teachers 테이블의 id값이 ‘student_id’로 되어있다.

이를 변경해주기 위해서 컬럼명 변경을 해준다.

 

 

ALTER TABLE teachers

RENAME COLUMN student_id TO teacher_id

 

2)

해결됨

---

#3 자료입력

 

1)

 

자료입력을 하려고 하는데

 

INSERT INTO students 

(phone, first_name, last_name, 

 student_id, graduation_year, homeroom_number )

VALUES ('777-555-1234', 'Mark', 'Watney',1,'2035', '5')

 

라고 입력했더니

 

ERROR:  invalid input syntax for type timestamp: "2035"

LINE 4: VALUES ('777-555-1234', 'Mark', 'Watney',1,'2035', '5')

                                                   ^

SQL state: 22007

Character: 146

 

에러가 뜬다.

 

원인은 한가지 인데,

졸업을 하는 시점을 상정해서 TIMESTAMP로 처리하려고 했다.

결과적으로 에러가 났으니 이에 대한 데이터 타입을 고쳐주자.

 

해결중

 

컬렴 속성 변경을 하기 위해서

ALTER TABLE students

ALTER COLUMN graduation_year VARCHAR(4) NOT NULL

 

코드로 입력했더니

 

ERROR:  syntax error at or near "VARCHAR"

LINE 2: ALTER COLUMN graduation_year VARCHAR(4) NOT NULL

                            ^

SQL state: 42601

Character: 51

 

라는 에러가 뜬다

 

타임스탬프를 varchar로 변경해 주는게 안되서 그런걸까?

에러 정보를 토대로 원인을 찾아보자.

 

ALTER TABLE students

ALTER COLUMN graduation_year TYPE VARCHAR(4)

 

stack over flow를 통해서 type을 붙여주는 것으로 해결하는 것을 알았다.

NOT NULL의 추가, 삭제는 추후 필요해졌을때 확인해보자.

 

이제 당초에 시도하려고 했던 INSERT 쿼리문이 정상 삽입 되었다.

 

2)

 

INSERT INTO teachers 

(department, homeroom_number, first_name, last_name,

teacher_id, email, phone)

 

VALUES('biology', 5, 'JONAS', 'Salk', 1, 'jsalk@school.org', '777-555-4321')

 

정상적으로 마무리 되었다.

 

===========================

---

강사 답안과 흐름

 

테이블 생성만 보자.

 

# 학생테이블

 

• CREATE TABLE students(
• student_id serial PRIMARY KEY,
• first_name VARCHAR(45) NOT NULL,
• last_name VARCHAR(45) NOT NULL,
• homeroom_number integer,
• phone VARCHAR(20) UNIQUE NOT NULL,
• email VARCHAR(115) UNIQUE,
• grad_year integer);

 

# 교사테이블

 

• CREATE TABLE teachers(
• teacher_id serial PRIMARY KEY,
• first_name VARCHAR(45) NOT NULL,
• last_name VARCHAR(45) NOT NULL, 
• homeroom_number integer,
• department VARCHAR(45),
• email VARCHAR(20) UNIQUE,
• phone VARCHAR(20) UNIQUE);