Deprecated: add_custom_background is deprecated since version 3.4.0! Use add_theme_support( 'custom-background', $args ) instead. in /home/pqmz7qzy9yt5/public_html/wp-includes/functions.php on line 5084

Deprecated: add_custom_image_header is deprecated since version 3.4.0! Use add_theme_support( 'custom-header', $args ) instead. in /home/pqmz7qzy9yt5/public_html/wp-includes/functions.php on line 5084

Notice: wp_enqueue_script was called incorrectly. Scripts and styles should not be registered or enqueued until the wp_enqueue_scripts, admin_enqueue_scripts, or login_enqueue_scripts hooks. This notice was triggered by the jquery handle. Please see Debugging in WordPress for more information. (This message was added in version 3.3.0.) in /home/pqmz7qzy9yt5/public_html/wp-includes/functions.php on line 5536

Notice: wp_enqueue_script was called incorrectly. Scripts and styles should not be registered or enqueued until the wp_enqueue_scripts, admin_enqueue_scripts, or login_enqueue_scripts hooks. This notice was triggered by the smoothscroll handle. Please see Debugging in WordPress for more information. (This message was added in version 3.3.0.) in /home/pqmz7qzy9yt5/public_html/wp-includes/functions.php on line 5536

Deprecated: The called constructor method for WP_Widget in Yoko_SocialLinks_Widget is deprecated since version 4.3.0! Use __construct() instead. in /home/pqmz7qzy9yt5/public_html/wp-includes/functions.php on line 5177
Intro to Databases: SQL Data Modification Statements | Megan Taylor

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.


Notice: Undefined index: host in /home/pqmz7qzy9yt5/public_html/wp-content/plugins/jetpack/modules/stats.php on line 209