Megan Taylor

front-end dev, volunteacher, news & data junkie, bibliophile, Flyers fan, sci-fi geek and kitteh servant

Intro to Databases: SQL Data Modification Statements

2 methods for inserting new data
insert into table values (…)
insert into table select-statement

deleting data
delete from table where condition

updating existing data
update table set attr = expression where condition
can update multiple attributes

Demo: simple college admissions database
College (cName, state, enrollment)
Student (sID, sName, GPA, sizeHS)
Apply (sID, cName, major, decision)

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

have students who didn’t apply anywhere apply to CS at Carnegie Mellon

find students who haven’t applied anywhere
select *
from Student
where sID not in (select sID from Apply);

have those students apply to Carnegie Mellon
insert into Apply
select sID, ‘Carnegie Mellon’, ‘CS’, null
from Student
where sID not in (select sID from Apply);

admit to Carnegie Mellon EE all students who were turned down in EE elsewhere
insert into Apply
select sID, ‘Carnegie Mellon’, ‘EE’, ‘Y’
from Student
where sID in (select sID from Apply where major = ‘EE’ and decision = ‘N’);

find all students who have applied for more than 2 different majors and delete them from the database
delete from Student
wheresID in
(select sID
from Apply
group by sID
having count(distinct major) > 2);

run it again on Apply
not all database systems allow this type of deletion

delete college with no CS applicants
delete from College
where cName not in (select cName from Apply where major = ‘CS’);

find students with GPA less than 3.6 and applied to Carnegie Mellon and accept them, but turn them into economics majors
update Apply
set decision = ‘Y’, major =’economics’
where cName = ‘Carnegie Mellon’
and sID in (select sID from Student where GPA < 3.6); turn 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’)));

give every student the highest GPA and the smallest size high school
update Student
set GPA = (select max(GPA) from Student),
sizeHS = (select min(sizeHS) from Student;

accept every student
update Apply
set decision = ‘Y’;

November 5, 2011 | Comments Off on Intro to Databases: SQL Data Modification Statements | Categories: Posts | Permalink

Comments are closed.

%d bloggers like this: