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 Basic SELECT Statement | Megan Taylor

Megan Taylor

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

Intro to Databases: SQL Basic SELECT Statement

Select, From, Where

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

Find all students with GPA > 3.6
select sID, sName, GPA
from Student
where GPA > 3.6;

Find names of students and majors for which they applied
select distinct sName, major
from Student, Apply
where Student.sID = Apply.sID;

(warning: duplicates! use distinct)

Find names and GPAs of students who applied for CS at Stanford with HS size les than 1000
select sname, GPA, decision
from Student, Apply
where Student.sID = Apply.sID
and sizeHS < 1000 and major = 'CS' and cname = 'Stanford'; Find all large campuses that have someone applying for CS select cName from College, Apply where College.cName = Apply.cName and enrollment > 20000 and major = ‘CS’;

ERROR ambiguous column name
use relation preface

select College.cName
from College, Apply
where College.cName = Apply.cName
and enrollment > 20000 and major = ‘CS’;

Join all three relations to find student applications
select Student.sID, sName, GPA, Apply.cName, enrollment
from Student, College, Apply
where Apply.sID = Student.sID and Apply.cName = College.cName
order by GPA desc, enrollment;

SQL is unordered, can request results to be sorted by an attribute (ascending is the default)

Like predicate in SQL allows simple string matching on attribute values

Find all students applying for major having to do with bio
select sID, major
from Apply
where major like ‘%bio%’;

select * returns all attributes in the result

Scale student GPA based on HS size
select sID, sName, GPA, sizeHS, GPA*(sizeHS/1000.0) as scaledGPA
from Student;

as modifies name of new column

November 5, 2011 | Comments Off on Intro to Databases: SQL Basic SELECT Statement | 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