Data Modification Statements (edX StandfordOnline: Databases: relational databases and sql)

출처

https://www.edx.org/learn/relational-databases/stanford-university-databases-relational-databases-and-sql

StanfordOnline: Databases: Relational Databases and SQL | edX

 

insert, delete, update

 

insert into College values ('Carnegie Mellon', 'PA', 11500);

insert into Apply
select sID, 'Carnegie Mellon', 'CS', null
from Student
where sID not in (select sID from Apply);

insert into Apply
select sID, 'carnegie Mellon', 'EE', 'Y'
from Student
where sID in (Select sID from Apply where major = 'EE' and decision='N');

 

Delete

* Delete all students who applied to more than two different majors

delete from Student
where sID in (select sID
from Apply
group by sID
having count(distinct major) > 2);

 

* Delete colleges with no CS applicants

delete from College
where cName not in (select cName from Apply where major = 'CS');

 

 

Update

* Accept applicants to Carnegie Mellon with GPA < 3.6 but turn them into economics major

update Apply
set deicision = 'Y', major = 'economics'
where cName = 'Carnegie Mellon' and sID in (select sID from Student where GPA < 3.6);

 

* Turn the highest-GPA EE applicant into a CSE applicant

update Apply
set major = 'CSE'
where major = 'EE'
and sID in 
(select sID from Student where GPA >= all (select GPA from Student where sID in (select sID from Apply where major = 'EE')));

 

* 전체 학생의 GPA를 최대값으로 바꾸고, sizeHS는 최소값으로 바꾸기

update Student
set GPA = (select max(GPA) from Student), sizeHS = (select min(sizeHS) from Student);

 

* 모든 applicant에게 입학 허가하기

update Apply
set decision = 'Y';