SQL Queries and Subqueries: Understanding Selection, Comparison, and Aggregation, Study notes of Computer Science

Download SQL Queries and Subqueries: Understanding Selection, Comparison, and Aggregation and more Study notes Computer Science in PDF only on Docsity!

SQL Queries and SubqueriesSQL^ Queries^ and

Subqueries Zaki MalikSeptember 04, 2008September 04,^2008

Basic SQL QueryBasic^ SQL^ Query SELECT [DISTINCT]^ target‐listFROM relation‐listWHERE qualification;q f ; • Relation‐list: A^ list^ of^ relation^ names^ (possibly

with^ range‐

variable^ after each name).variable^ after^ each^ name). • Target‐list:^ A^ list^ of^ attributes

of^ relations^ in^ relation‐list

-^ Qualification:^ conditions

on^ attributes

-^ DISTINCT: optional keyword for duplicate removal•^ DISTINCT:^ optional^ keyword

for^ duplicate^ removal.

-^ Default^ =^ no^ duplicate^ removal!

How to evaluate a query?How^ to^ evaluate

a^ query?

SELECT^ [DISTINCT]^ target

‐list

FROM^ relation‐listWHERE qualification;WHERE qualification; •^ Conceptual^ query^ evaluation

using^ relational^ operators:

1)^ Compute^ the^ cross‐product

of^ relation‐list. 2)^ Discard^ resulting^ tuples^ if

they^ fail^ qualifications. 3)^ Delete^ attributes^ that^ are

not^ in^ target‐list.^ (called^ column

‐list) 4)^ If DISTINCT is specified, eliminate duplicate rows.4)^ If^ DISTINCT^ is^ specified,^ eliminate

duplicate^ rows. SELECT S.snameFROM^ Sailors S Reserves R FROM^ Sailors^ S,^ Reserves^ R WHERE S.sid=R.sid^ AND^ R.bid=103;

Example of Conceptual Evaluation (1)Example^ of^ Conceptual

Evaluation^ (1) (1) Compute^ the^ cross‐product^ of^ relation

‐ list. SELECT^ S.snameFROM^ Sailors^ S,^ Reserves^ R WHERE^ S.sid=R.sid^ AND^ R.bid=103;^ Sailors^

Reserves

sid^ sname^ rating^ age 22 dustin^7 45.

sid^ bid^ day 22 101 10/10/96X

31 lubber^8 55.5 58 rusty^10 35.

X^58 103 11/12/

Example of Conceptual Evaluation (3)Example^ of^ Conceptual

Evaluation^ (3) (3) D l t^ tt ib t^ l^ th t SELECT^ S sname^

(3) Delete attribute columns that arenot in target-list. SELECT^ S.snameFROM Sailors S, Reserves RWHERE S.sid=R.sid AND R.bid=103;

sname (sid)^ sname^ rating^ Sailors X Reservesage^ (sid)^ bid^ day

sname^ rusty (sid)^ sname^ rating^

age^ (sid)^ bid^ day 22 dustin^7

45.0^22 101 10/10/

22 dustin^7

45.0^58 103 11/12/

31 lubber^8

55.5^22 101 10/10/

31 lubber^8

55.5^58 103 11/12/

58 rusty^10

35.0^22 101 10/10/

58 rusty^10

35.0^58 103 11/12/

Renaming / AliasingRenaming^ /^ Aliasing Consider the following SALESREPS relation E l R^

ffi

Empl_num^ name^ age

Rep_office^ manager

105 Bill^37

104 Bob^33

106 Sam^52

11 NULL

How do we determine the name of Bob’s manager?

Relational^ Design

Example

-^ Students^ (PID:^ string ,^ Name:^ string

,^ Address:^ string )

-^ Professors (PID^ t i^ Name

t i^ Office^ t i^ Age^ i t^

DepartmentName

-^ Professors^ (PID:^ string ,^ Name

:^ string ,^ Office:^ string ,^ Age:^ integer

,^ DepartmentName: string ) • Courses (Number: integer DeptName:

string^ CourseName:^ string^ Classroom:

-^ Courses^ (Number:^ integer,^ DeptName:

string ,^ CourseName:^ string ,^ Classroom: string ,^ Enrollment:^ integer ) • Teach (ProfessorPID:^ string , Number:

integer , DeptName:^ string ) Teach^ (ProfessorPID:^ string ,^ Number:

integer ,^ DeptName:^ string )

-^ Take^ (StudentPID:^ string ,^ Number:

integer ,^ DeptName:^ string ,^ Grade:

string , ProfessorEvaluation:^ integer)g^ ) • Departments^ (Name:^ string ,^ ChairmanPID:

string )

-^ PreReq^ (Number:^ integer ,^ DeptName:

string ,^ PreReqNumber:^ integer, PreReqDeptName:^ string )

Motivation^ for

Subqueriesq

-^ Find the name of the professor who teaches

“CS 4604 ”

Find^ the^ name^ of^ the^ professor

who^ teaches^ CS^4604.

SELECT^ NameFROM^ Professors,^ TeachWHERE^ (PID^ =^ ProfessorPID)

AND^ (Number^ =^ ‘4604’) AND(DeptName = ‘CS’)^ ;

-^ Do^ we^ need^ to^ take^ the

natural^ join^ of^ two^ big^ relations just to get a relation with^ one^ tuple?

-^ Can^ we^ rewrite^ the^ query

without^ using^ a^ join?

Subquery ExampleSubquery^ Example • Find the name^ of^ the^ professor^ who

teaches^ “CS^ 4604.”

SELECT^ NameFROM ProfessorsFROM^ Professors WHERE^ PID^ = (SELECT^ ProfessorPIDFROM^ TeachWHERE^ (Number

=^ 4604)^ AND^ (DeptName^ =^ ’CS’)););

-^ When^ using^ =,^ the^ subquery

must^ return^ a^ single^ tuple

Conditions^ Involving

Relations

SQL i^ l d^ b^

f^ h^ l

-^ SQL^ includes^ a^ number

of^ operators^ that^ apply^ to

a

relation^ and^ produce^ a^ boolean

result.

-^ These operators are very useful to apply on results of sub

These^ operators^ are^ very

useful^ to^ apply^ on^ results

of^ sub

queries. • Let^ R^ be^ a^ relation^ and^ t^

be^ a^ tuple^ with^ the^ same

set^ of

battributes. – EXISTS^ R^ is^ true^ if^ and^ only^ if^ R

contains^ at^ least^ one^ tuple.

-^ t IN R is true if and only if t equals a tuple in R.t^ IN^ R^ is^ true^ if^ and^ only^ if

t^ equals^ a^ tuple^ in^ R.

-^ t^ >^ ALL R^ is^ true^ if^ and^ only

if^ R^ is^ unary^ (has^ one^ attribute)

and

t^ is^ greater^ than^ every^ value

in^ R. • Can use any of the other five comparison operatorsCan use any of the other five comparison^ operators. • If we use <>, R need not be unary.

-^ t^ >^ ANY^ R^ (which^ is^ unary)^ is

true^ if^ and^ only^ if^ t^ is^ greater^

than^ at

least one value in R.least^ one^ value^ in^ R. • We^ can^ use^ NOT to^ negate^ EXISTS,

ALL,^ and^ ANY.

Correlated vs UncorrelatedCorrelated^ vs^

Uncorrelated

-^ The^ previous^ subqueries

did^ not^ depend^ on^ anything

outside

the^ subquery^ –^ and thus need to be executed just once…and^ thus^ need^ to^ be^ executed

just^ once.

-^ These^ are^ called^ uncorrelated. • A correlated subquery depends on data from the outer query• A^ correlated^ subquery^ depends

on^ data^ from^ the^ outer^ query

-^ …^ and^ thus^ has^ to^ be^ executed

for^ each^ row^ of^ the^ outer^ table(s)

Correlated SubqueriesCorrelated^ Subqueries • Find course^ names^ that^ have^ been^

used^ for^ two^ or^ more^ courses. SELECT CourseNameSELECT^ CourseName FROM^ Courses^ AS^ FirstWHERE^ CourseName^ IN (SELECT

CourseNameFROM CoursesWHERE (N mber <> First N mber)WHERE (Number^ <>^ First.Number)AND (DeptName^ <>^ First.DeptName));)

Subqueries in FROM clausesSubqueries^ in

FROM^ clauses

-^ Can^ use^ a^ subquery^ as^ a

relation^ in^ a^ FROM^ clause.

-^ We^ must^ give^ such^ a^ relation

an^ alias^ using^ the^ AS^ keyword.

-^ Let^ us^ find^ different^ ways

of^ writing^ the^ query^ “Find^

the^ names^ of Professors who have taught the student whose first name isProfessors^ who^ have^ taught

the^ student^ whose^ first^ name

is ’Suri’.” • The^ old^ way:SELECT P^ f^ NSELECT^ Professors.NameFROM^ Professors,^ Take,^ Teach,^ StudentsWHERE^ (Professors.PID^ =^ Teach.ProfessorPID)AND^ (Teach.CourseNumber

=^ Take.CourseNumber)AND (Teach.DeptName = Take.DeptName)AND (Take.StudentPID = Student.PID)AND (Take.StudentPID Student.PID) AND (Student.Name = ’Suri %’);

-^ “Find^ the^ names^ of^ (Professors

who^ have^ taught^ (courses

taken

b^ ( t d^ t^ ith fi^ t^

’S^ i’))) ”

by^ (student^ with^ first^ name

’Suri’))).”

SELECT NameSELECT^ Name FROM^ ProfessorsWHERE^ PID^ IN (SELECT P^ f^ PID(SELECT^ ProfessorPIDFROM^ TeachWHERE^ (Number,^ DeptName)

IN ( SELECT^ Number,^ DeptNameFROM^ TakeWHERE^ StudentPID^ IN (SELECT PID(SELECT

PID FROM StudentsWHERE Name = ’Suri^ %’) )) );