10.05.26 DB 실습

/*

insert

into student(sno, sname, year, dept)

values (20063703,'서정회',3,'멀티');

insert

into enrol(sno,cno,grade,midterm,final)

values(20063703,'c413','A',99,99);

*/

select sname, s.dept

from student s, enrol e, course c

where s.sno=e.sno and e.cno=c.cno and c.prname='우영운'

select sname, s.dept

from student s, enrol e, course c

where s.sno=e.sno and e.cno=c.cno and c.cname='자료구조';

select cno, count(*) as 수강인원

from enrol

group by cno;

select cno, count(*) as 수강인원

from enrol

group by cno

having count(*) >= 3

update student

set year=2

where sno=20063703;

update enrol

set grade='F',  midterm = midterm - 20, final = final - 20

where sno=20063703 and cno='c413';

select lnr

from loan

where bnm = 'perryridge' and amount > 1200;