[DB] University

PROFESSOR(pno, pname, pmajor, pdept)

COURSE(cno, cname, credit, session)

STUDENT(sno, pno, sname, year, dept)

ENROLL(sno, cno, grade, exam)

LECTURE(cno, pno, lec_time, room)

 

* 밑줄: 기본키 / 기울임: 외래키 

 

 

 

< Create table > https://github.com/HayeonKang01/SQL/blob/DB(3-2)/hw7_UNIVERSITY(SQL과제).sql 

GitHub - HayeonKang01/SQL

https://github.com/HayeonKang01/SQL/blob/DB(3-2)/DATA_UNIVERSITY.sql 

GitHub - HayeonKang01/SQL

 

* 주의사항 

- 외래키가 걸려있지 않는 테이블 먼저 생성 

- 나는 임의의 데이터를 추가했는데, 

이 경우에 외래키가 걸려있지 않은 테이블에 먼저 데이터를 채워넣고, 외래키가 있는 테이블에 데이터를 맞춰서 입력하면 된다. (참조하는 컬럼에 없는 값을 입력하면 오류) 

- 나중에 데이터를 임포트할 때 값 길이가 적으면 에러가 발생할 수 있다. (그 길이보다 긴 값이 존재할 경우) 때문에 create table에서 미리 주소, 이름 같은 데이터는 크기를 크게 잡기를 권유한다. 

 

< data import >

 

 

 

 

 

 

 

 

 

 

▶ ENROLL에 data import?

왼쪽의 테이블 리스트에서 enroll 테이블을 선택한다. (create table로 스키마만 만들어져있는 상태)

우클릭하면 데이터 임포트 메뉴가 나온다. 선택하면, 아래와 같은 이미지 

 

찾아보기로 파일을 선택한다. 

자동으로 첫번째 행을 헤더로 인지한다, 원치 않으면 헤더의 체크를 취소하면 된다 

. 

다음을 클릭하면, 임포트 방식을 볼 수 있다. 

임포트 방식은 삽입으로 지정해주고 다음으로 넘어가자. 

열을 선택할 수 있다. 제거하고 싶은 컬럼이 있다면 왼쪽 박스로 옮겨주자. 

해당사항이 없으면 바로 다음으로 넘어가자.

이제 불러온 데이터와 우리가 만들어 놓은 컬럼을 연결해준다. 

소스 데이터 열이 엑셀 파일의 컬럼이름이고, 대상 테이블 열이 만들어놓은 스키마의 컬럼 이름이다. 

해당하는 컬럼들을 각각 선택해주거나, 위의 경우엔 자동으로 지정되었다. 

혹시 일일이 선택해야 한다면, 일치 기준을 위치로 지정해주면 한번에 연결한다. (순서가 같을 경우)

 

 

 

다음을 클릭하면 이제 끝이다. 완료 버튼을 누르면 임포트 완료된다. 

 

 

임포트가 정상적으로 되었는지 확인하기 위해 select * from enroll; 을 실행한다. 

위처럼 정상적으로 잘 출력된다.

 

(만약, n 행 ~ m행에 에러가 발생했다는 메세지와 함께 무시하고 데이터를 임포트할 것인지, 임포트 자체를 취소할 것인지 묻는 창이 뜬다면?) 

-> 무시 후 임포트 선택 : 해당 행이 잘리고 들어간다.

-> 취소 선택 : 임포트가 취소되고 이제 해결책을 찾아야 한다.

만약, 외래키가 걸려있는 테이블이라면, 참조하는 컬럼에 존재하지 않는 값이 참조받는 컬럼에 존재하는 경우가 있을 수 있다. 

혹은 키가 제대로 설정되지 않았을 수 있다. 

다른 경우에는 길이가 맞지 않다는 에러가 뜰 수 있는데 그 경우, 아래에서 서술할 '속성'에 들어가서 값을 간편하게 바꾸면 된다.

 

 

< 속성 창 > 

 

 

 

 

 

 

 

 

테이블 선택 후 위 바의 편집 -> 속성을 선택하면, 아래와 같은 창이 뜬다. 

 

 

 

 

이렇게 열, 제약조건 등등 한번에 편집가능하게 해준다. 

여기선 키와 데이터유형, 크기 등 제약조건을 관리할 수 있는데 지금 sno, cno가 기본키로 설정되어 있고 

컬럼들의 데이터 유형과 크기의 정보를 보여준다. 

위에서 말했던 값 크기로 인해 오류가 발생할 경우 여기서 크기를 선택하고 원하는 값을 입력 후 확인을 누르면 간편하게 값의 크기를 수정할 수 있다.

제약조건을 관리하는 창. 

기본키는 sno, cno가 걸려있음을 보여준다. 

 

cno에 설정되어있는 외래키의 정보. 

제약조건의 이름은 ENROLL_CNO_FK 인데 이는 course 테이블의 cno를 가져왔음을 알려준다. 

sno에 설정되어있는 외래키의 정보. 

student의 sno를 가져와서 쓰고있음을 알 수 있다. 

 

 

 

< QUIZ > 

1-a. Retrieve names of all senior student in the 'DT' department = DT 학과의 4학년 학생 이름

select sname from student 
where year = 4 and dept='DT';

 

1-b. Retrieve names of all course taught by Professor 'Jonghoon Chun' = Jonghoon Chun 교수님이 가르치는 모든 강의의 이름

select cname 
from course inner join lecture on (course.cno = lecture.cno)
inner join professor on (professor.pno = lecture.pno)
where pname = 'Jonghoon Chun';

 

1-c. Retrieve names and majors of all straight 'A' students (students who have a grade 'A' in all their courses) = 모든 과목을 A 받은 학생의 이름

SELECT sname, dept
FROM STUDENT
WHERE NOT EXISTS ( SELECT *
	FROM enroll
	WHERE sno= sno AND NOT(Grade='A'));

 

1-d. Retrieve names and majors of all students who do not have a grade 'A' in any of their courses = A를 한번도 받지 못한 학생의 이름

SELECT sname, dept
FROM STUDENT
WHERE NOT EXISTS ( SELECT *
	FROM enroll
	WHERE sno= sno AND Grade='A');

 

1-e. Delete all courses offered by 'CE' department = CE 학과의 모든 강의를 삭제.

* course 테이블의 cno를 enroll, lecture 테이블이 참조한다. 때문에 참조하는 테이블의 관련 정보를 먼저 삭제하고 가장 마지막에 course 테이블의 관련 정보를 삭제해야 한다.

delete from enroll
where cno in (select c.cno from lecture l inner join course c on l.cno=c.cno
inner join professor p on l.pno = p.pno
where pdept='CE');

delete from lecture
where cno in (select c.cno from lecture l inner join course c on l.cno=c.cno
inner join professor p on l.pno = p.pno
where pdept='CE');

delete from course
where cno in (select c.cno from lecture l inner join course c on l.cno=c.cno
inner join professor p on l.pno = p.pno
where pdept='CE');