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.

%d bloggers like this: