생각과 개발

수업중 학생들에게 내 준 SQL 문제 (기말고사에서 발췌), 도전해보시라.

시험조건 : open book

1. 다음 릴레이션을 보고 다음 답변을 해주세요.

STUDENT (SID, SNAME, STARTYEAR, DID)

DEPARTMENT (DID, DNAME)

PROFESSOR(PID, PNAME, DID)

COURSE(CID, CNAME, DID, PID)

ENROLL(SID, CID)

ROOM(RID, RNAME)

COURSEROOM(CID,RID, CLASSNUM, WEEK, HOUR, DURATION)

예 : 어떤 수업이 일주일 두 번 있는 경우

( classnum, week, hour, duration )

1, 1, 14, 2 일주일내 첫번째 수업, 월요일(=1) 오후2시(=14)부터 2시간

2, 3, 19, 2 일주일내 두번째 수업, 수요일(=3) 오후7시(=19)부터 2시간

1) 각 테이블에 Primary Key 와 Foreign Key 를 찾아주세요

2) ER Diagram 를 그리세요. 적절한 cardinality constraint를 표현해주세요.

 

3) 학번 51 학생의 수업명, 수업장소(장소명)와 시간(요일, 교시, 수업길이) 정보를 추출하세요.

select         c.cname, cr.classnum, r.rname, cr.week, cr.hour, cr.duration

from

where         e.sid = '51' and

4) 학번 51 학생과 한번이라도 수업이 겹치는 학생들의 정보(학번, 이름)을 추출하세요.

select         s.sid, s.sname

from

where

5) 다음의 두 질의가 어떤 의미인지 명확하게 설명해주세요. 공백제외 20자 이내로 써주세요.

select distinct     s.sid, s.sname

from

(select       sid, classnum, rid, week, hour, duration

from         enroll e, course c, courseroom cr

where       e.cid = c.cid and c.cid = cr.cid ) T1,

(select       sid, classnum, rid, week, hour, duration

from         enroll e, course c, courseroom cr

where       e.cid = c.cid and c.cid = cr.cid ) T2,

student s

where         T1.sid = s.sid and
T1.sid = T2.sid and T1.cid <> T2.cid and
T1.rid = T2.rid and T1.week = T2.week and
( T1.hour <= T2.hour and T1.hour + T1.duration > T2.hour)

 

 

select distinct     s.sid, s.sname

from           enroll e, course c, courseroom cr, student s

where         e.sid = s.sid and e.cid = c.cid and c.cid = cr.cid and
exists

(select    cr2.classnum

from      enroll e2, course c2, courseroom cr2

where    e2.cid = c2.cid and c2.cid = cr2.cid and
e.sid = e2.sid and cr.cid <> cr2.cid and
cr.rid = cr2.rid and cr.week = cr2.week and
( cr.hour <= cr2.hour and cr.hour + cr.duration > cr2.hour)

)

5 번은 SQL을 만드는 문제를 내려다가 답을 만들다 보니 너무 길어서 의미를 설명하는 것으로 바꾼 것...
SQL을 만드는 문제를 냈으면 채점은 쉬웠겠지만, 욕먹었을 듯.

수업중 학생들에게 내 준 SQL quiz문제, 도전해보시라.

시험조건 : open book, 30 분

당연히 틀릴 줄 알았던, 5번을 거의 정확하게 푼 학생까지 있었다.

한편 다섯문제 다 완전히 틀려서 0점 받은 학생도 있었지만.. ^^

다음 릴레이션을 보고 다음 답변을 해주세요.

STUDENT (SID, SNAME, STARTYEAR, DID)

DEPARTMENT (DID, DNAME)

PROFESSOR(PID, PNAME, DID)

COURSE(CID, CNAME, DID, ROOM, PID)

ENROLL(SID, CID)

1)     개설된 과목 중 수강인원이 3명 이하인 과목을 찾는 SQL을 작성하세요. 과목명이 노출되도록 해주세요. (수강생이 0 인 과목은 없다고 가정합니다)

select   c .cname, count (*) as '수강생수'
from

where

group by

having

2)      “동물학과” 소속의 교수가 개설한 과목을 듣는 학생들을 검색하는 SQL을 작성하세요. 학번과 학생이름이 출력되도록 해주세요.

select   distinct s.sid, s.sname

from

where

3)     각 과목마다, 입학년도 별 수강인원을 알아내는 SQL 을 작성하세요. 과목명이 노출되도록 해주세요.

select   c.cname, s.startyear, count(*) as '학년별수강생수 '

from

where

group by

4)     각 과목마다, 신입생(=2008년 입학생)과 재학생 수강생을 출력하세요. (다음 형태를 활용하세요.  case when condition then value-for-true else value-for-false end )

select    c.cname,
case when startyear = 2008 then ' 신입생'               as '학생구분' ,

             as '수강생수'

from

where

group by

5)     신입생 수강 비율이 가장 높은 과목을 찾는 SQL을 작성하세요. 과목명과 신입생 비율이 노출되도록 해주세요.

select

from

where

group by

order by