과제물 풀이
A형 과제. 다음 요구조건에 맞는 쿼리을 생성하시오. (대소문자는 구별하지 않음)
1) 학생(Student)-성적(Score)-과목(Subject) 테이블에 대한 정의를 DDL로 표현하시오.
- 모든 테이블들은 자동증가(AUTO_INCREMENT), 숫자 타입(INT) 'ID'를 기본키(PK)로 해야한다.
- 학생테이블은 이름(Name), 주소(Address), 전화번호(Telephone), 이메일(Email), 학생번호(StudentNo) 속성을 가진다.
- 성적테이블은 과목ID(Subject_id), 학생ID(Student_id), 점수(Grade) 속성을 가진다.
- 과목테이블은 과목명(Name)을 가진다.
- 숫자 컬럼은 INT, 문자 컬럼은 VARCHAR(255) 타입을 가진다.
- 성적테이블은 학생/과목테이블에 대한 외래키를 가진다. (제약조건명은 fk_Score_Student, fk_Score_Subject을 사용할 것)
- 성적테이블은 학생/과목테이블에 대한 외래키(각각 Student_id, Subject_id)를 가진다.
CREATE TABLE Student (
ID INT NOT NULL AUTO_INCREMENT,
Name VARCHAR(255),
Address VARCHAR(255),
Telephone VARCHAR(255),
Email VARCHAR(255),
StudentNo VARCHAR(255),
PRIMARY KEY (ID)
);
CREATE TABLE Subject (
ID INT NOT NULL AUTO_INCREMENT,
Name VARCHAR(255),
PRIMARY KEY (ID)
);
CREATE TABLE Score (
ID INT NOT NULL AUTO_INCREMENT,
Grade INT,
Student_id INT,
Subject_id INT,
PRIMARY KEY (ID),
CONSTRAINT `fk_Score_Student` FOREIGN KEY (`Student_id`) REFERENCES `Student` (`ID`),
CONSTRAINT `fk_Score_Subject` FOREIGN KEY (`Subject_id`) REFERENCES `Subject` (`ID`)
);
2) 다음과 같이 레코드를 추가하는 DML을 적성하시오(참조무결성 고려할 것). (대소문자는 구별하지 않음)
- Student(학생)
| ID | Name | Address | Telephone | Email | StudentNo |
+----+------+---------+-----------+---------+-----------+
| 1 | Sam | Seoul | 123-4567 | a@b.com | 1000 |
| 2 | Tom | Busan | 111-4567 | b@b.com | 2000 |
| 3 | Kim | Inchon | 222-4567 | c@b.com | 3000 |
+----+------+---------+-----------+---------+-----------+
- Score(성적)
| ID | Grade | Student_id | Subject_id |
+----+-------+------------+------------+
| 1 | 100 | 1 | 1 |
| 2 | 95 | 1 | 2 |
| 3 | 90 | 1 | 3 |
| 4 | 90 | 2 | 1 |
| 5 | 85 | 2 | 2 |
| 6 | 95 | 2 | 3 |
| 7 | 75 | 3 | 1 |
| 8 | 85 | 3 | 2 |
| 9 | 90 | 3 | 3 |
+----+-------+------------+------------+
- Subject(과목)
| ID | Name |
+----+------+
| 1 | Lang |
| 2 | Math |
| 3 | Eng |
+----+------+
insert into Student (Name, Address, Telephone, Email, StudentNo)
select 'Sam', 'Seoul', '123-4567', 'a@b.com', '1000' from dual union all
select 'Tom', 'Busan', '111-4567', 'b@b.com', '2000' from dual union all
select 'Kim', 'Inchon', '222-4567', 'c@b.com', '3000' from dual;
insert into Subject (Name)
select 'Lang' from dual union all
select 'Math' from dual union all
select 'Eng' from dual;
insert into Score (Grade, Student_id, Subject_id)
select 100, 1, 1 from dual union all
select 95, 1, 2 from dual union all
select 90, 1, 3 from dual union all
select 90, 2, 1 from dual union all
select 85, 2, 2 from dual union all
select 95, 2, 3 from dual union all
select 75, 3, 1 from dual union all
select 85, 3, 2 from dual union all
select 90, 3, 3 from dual;
3) Student, Score, Subject 세 테이블을 조인해서 학생이름-과목명-성적을 출력하도록 하시오.
select Student.Name, Subject.Name, Score.Grade
from Student join Score on Student.id=Score.student_id
join Subject on Score.subject_id=Subject.id;