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 Table Variable and Set Operators | Megan Taylor

Megan Taylor

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

Intro to Databases: SQL Table Variable and Set Operators

Table variables – From clause of SELECT statement

set operators: union, intersect, except

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

Table variable example
select S.sID, sNAme, GPA, A.cName, enrollment
from Student S, College C, Apply A
where A.sID = S.sID and A.cName = C.cName;

Find all pairs of students with the same GPA
select S1.sID, S1.sName, S1.GPA, S2.sID, S2.sName, S2.GPA
from Student S1, Student S2
where S1.GPA = S2.GPA and S1.sID < S2.sID; Watch out for duplicates! Union Set Operator select cName as name from College union all select sName as name from Student order by name; union operator by default eliminates duplicates to include duplicates add all Intersect Set Operator select sID from Apply where major = 'CS' intersect select sID from Apply where major = 'EE'; some database systems don't support the intersect operator, just write queries differently, watch out for duplicates! select distinct A1.sID from Apply A1, Apply A2 where A1.sID = A2.sID and A1.major = 'CS' and A2.major = 'EE'; Except Set Operator select sID from Apply where major = 'CS' except select sID from Apply where major = 'EE'; some database systems don't support the except operator, need to write queries different, not addressed in this lecture.

November 5, 2011 | Comments Off on Intro to Databases: SQL Table Variable and Set Operators | 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