10.04.28 DB 실습

select s.sname, c.cname, e.final

from student s, course c, enrol e

where s.sno=e.sno and c.cno=e.cno and e.final>=90

order by c.cname , sname desc

select cno, avg(final) as 기말평균

from enrol

group by cno

select sno , count(cno)

from enrol

group by sno

select cno, avg(final) as 평균

from enrol

group by cno

having count(*)>=3

select cno, avg(final) as 기말평균

from enrol

group by cno

having avg(final)>=90

select cno, avg(final) as 평균

from enrol

group by cno

having count(*)>=3 and avg(final)>=90

select sname

from student

where sno in

 (select sno

 from enrol

 where cno='c413')

select sname

from student

where sno not in

 (select sno

 from enrol

 where cno='c413')

select sname, dept

from student

where dept = 

 (select dept

 from student

 where sname='정기태')

select sno,cno

from enrol

where final>=all

 (select final

 from enrol

 where sno=500)

select cno,cname

from course

where cno like 'c%'

select dept

from course

where dept like '전%'

select prname

from course

where prname like '이%'

select cno

from course

where cno like 'c3________'

select sno,sname

from student

where dept is null

select sname, dept

from student

where sno not in

 (select sno

 from enrol

 where cno='c324')