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 Subqueries in FROM and SELECT | Megan Taylor

Megan Taylor

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

Intro to Databases: SQL Subqueries in FROM and SELECT

Expressions involving subqueries: nested SELECT statements in FROM and SELECT clauses

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

Return all students whose scaled GPA changes GPA by more than 1
select sID, sName, GPA, GPA*(sizeHS/1000.0) as scaled GPA
from Student
where GPA*(sizeHS/1000.0) – GPA > 1.0 or GPA – GPA*(sizeHS/1000.0) > 1.0;

simplify where clause
select sID, sName, GPA, GPA*(sizeHS/1000.0) as scaled GPA
from Student
where abs(GPA*(sizeHS/1000.0) – GPA) > 1.0;

simplify more with subquery in FROM clause
select *
from (select sID, sName, GPA, GPA*(sizeHS/1000.0) as scaled GPA
from Student) G
where abs(G.scaledGPA – GPA) > 1.0;

Find colleges and pair with highest GPA among applicants
select distinct College.cName, state, GPA
from College, Apply, Student
where College.cNAme = Apply.cName
and Apply.sID = Student.sID
and GPA >= all
(select GPA from Student, Apply
where Student.sID = Apply.sID
and Apply.cName = College.cName);

with subquery in select clause
select cName, state,
(select distinct GPA
from Apply, Student
where College.cName = Apply.cName
and Apply.sID = Student.sID
and GPA >= all
(select GPA from Student, Apply
where Student.sID = Apply.sID
and Apply.cName = College.cName)) as GPA
from College;

Pair colleges with names of applicants
select cName, state,
(select distinct sName
from Apply, Student
where College.cName = Apply.cName
and Apply.sID = Student.sID) as sName
from College;
ERROR subquery in select clause must return exactly one row

November 5, 2011 | Comments Off on Intro to Databases: SQL Subqueries in FROM and SELECT | 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