SQL – Basic Retrievals
standard form “select-from-where”
A,B,…,CF (rs … t)
becomes
select [distinct] A, B, …, C
from r, s, …, t
where F;
* stands for all columns
tuple variables (aliases) may be declared:
… from r [as] v1, s, …, t v2
(<sfw)>)
except, [minus,] intersect, union; contains
(<sfw>)
create {table, view} <name> as
<sfw>;
nesting:
… where (a,b,c) [not] in
(<sfw>)
… where [not] exists
(<sfw>)
any (<sfw>)// at least one row
all (<sfw>)// all rows
Relational Queries: Examples 1 in SQL
students(sname, gpa, fname)
faculty(fname, office)
1. advisors
select distinct fname
from students;
2. student records with gpa > 3.0
select *
from students
where gpa > 3.0;
3. advisors of students with gpa > 3.0
select distinct fname
from students
where gpa > 3.0;
4. advisors’ offices of students with gpa > 3.0
select distinct office
from students s, faculty f
where s.fname = f.fname
and gpa > 3.0;
Relational Queries: Examples 2 in SQL
students(sname, gpa, fname)
took(sname, cno, sem, grade)
offerings(cno, sem, fname)
1. students who took a course with their advisor
create view students_who_did as// looking ahead to #2
select distinct s.sname
from students s, took t, offerings o
where s.sname = t.sname
and s.fname = o.fname
and t.cno = o.cno
and t.sem = o.sem;
2. students who never took a course with their advisor
(select sname
from students)
except
(select sname
from students_who_did);
3. students who took a course their advisor never taught
select distinct sname
from took
where <sname, cno> not in
(select sname, cno
from students s, offerings o
where s.fname = o.fname);
Relational Queries: Examples 2 in SQL (continued)
students(sname, gpa, fname)
took(sname, cno, sem, grade)
offerings(cno, sem, fname)
4. students who only took courses with their advisor
select sname from students s
where
((select cno, sem// courses taught by the advisor
from offerings o
where s.fname = o.fname)
contains
(select cno, sem// courses taken by the student
from took t
where s.sname = t.sname));
SQL - Aggregates and Grouping
took(sname, cno, sem, grade)
1. the student name, maximum grade obtained, and the number of courses taken in spring, 2004, for each student who took at least three courses then
select sname, count(*), max(grade)
from took
where sem = ‘S04’
group by sname
having count(*) > 2;
2. students’ names who took the most courses in spring, 2004
select sname
from took
where sem = ‘S04’
group by sname
having count(*) >= all
(selectcount(*)
from took
where sem = ‘S04'
group by sname);