SQL SELECT Queries: Understanding DISTINCT, WHERE Clauses, and JOINs, Slides of Database Management Systems (DBMS)

Download SQL SELECT Queries: Understanding DISTINCT, WHERE Clauses, and JOINs and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

SQL SELECT

SQL SELECT Overview

SELECT

[DISTINCT | ALL] <column-list>

FROM <table-names>

[WHERE ]

[ORDER BY <column-list>]

[GROUP BY <column-list>]

[HAVING ]

• ([] - optional, | - or)

DISTINCT and ALL

• Sometimes you end

up with duplicate

entries

• Using DISTINCT

removes duplicates

• Using ALL retains

them - this is the

default

SELECT ALL Last

FROM Student

SELECT DISTINCT Last

FROM Student

Last Smith Jones Brown Jones Brown

Last Smith Jones Brown

WHERE Clauses

• Usually you don’t

want all the rows

• A WHERE clause

restricts the rows that

are returned

• It takes the form of a

condition - only those

rows that satisfy the

condition are returned

• Example conditions:

• Mark < 40
• First = ‘John’
• First <> ‘John’
• First = Last
• (First = ‘John’)

AND

(Last = ‘Smith’)

• (Mark < 40) OR

(Mark > 70)

WHERE Example

• Given the table •^ Write an SQL query to

find a list of the ID

numbers and marks in

IAI of students who have

passed (scored 40 or

higher) IAI

Grade ID Code Mark S103 DBS 72 S103 IAI 58 S104 PR1 68 S104 IAI 65 S106 PR2 43 S107 PR1 76 S107 PR2 60 S107 IAI 35

ID Mark S103 58 S104 65

One Solution

SELECT ID, Mark FROM Grade

WHERE (Code = ‘IAI’) AND

(Mark >= 40)

We only want the ID and Mark, not the Code

We’re only interested in IAI

Single quotes around the string

We’re looking for entries with pass marks

Student ID First Last S103 John Smith S104 Mary Jones S105 Jane Brown S106 Mark Jones S107 John Brown

SELECT from Multiple Tables

SELECT

First, Last, Mark

FROM Student, Grade

WHERE

(Student.ID =

Grade.ID) AND

(Mark >= 40)

Grade ID Code Mark S103 DBS 72 S103 IAI 58 S104 PR1 68 S104 IAI 65 S106 PR2 43 S107 PR1 76 S107 PR2 60 S107 IAI 35

And then with the second…

and so on

Are matched with the first entry from the Student table...

All of the entries from the Grade table

SELECT from Multiple Tables

ID First Last ID Code Mark S103 John Smith S103 DBS 72 S103 John Smith S103 IAI 58 S103 John Smith S104 PR1 68 S103 John Smith S104 IAI 65 S103 John Smith S106 PR2 43 S103 John Smith S107 PR1 76 S103 John Smith S107 PR2 60 S103 John Smith S107 IAI 35 S104 Mary Jones S103 DBS 72 S104 Mary Jones S103 IAI 58 S104 Mary Jones S104 PR1 68 S104 Mary Jones S104 IAI 65 S104 Mary Jones S106 PR2 43

SELECT ... FROM Student, Grade WHERE...

SELECT from Multiple Tables

ID First Last ID Code Mark S103 John Smith S103 DBS 72 S103 John Smith S103 IAI 58 S104 Mary Jones S104 PR1 68 S104 Mary Jones S104 IAI 65 S106 Mark Jones S106 PR2 43 S107 John Brown S107 PR1 76 S107 John Brown S107 PR2 60

SELECT ... FROM Student, Grade WHERE (Student.ID = Grade.ID) AND (Mark >= 40)

SELECT from Multiple Tables

First Last Mark John Smith 72 John Smith 58 Mary Jones 68 Mary Jones 65 Mark Jones 43 John Brown 76 John Brown 60

SELECT First, Last, Mark FROM Student, Grade WHERE (Student.ID = Grade.ID) AND (Mark >= 40)

SELECT from Multiple Tables

ID First Last ID Code Mark Code Title

S103 John Smith S103 DBS 72 DBS Database Systems S103 John Smith S103 IAI 58 IAI Intro to AI S104 Mary Jones S104 PR1 68 PR1 Programming 1 S104 Mary Jones S104 IAI 65 IAI Intro to AI S106 Mark Jones S106 PR2 43 PR2 Programming 2 S107 John Brown S107 PR1 76 PR1 Programming 1 S107 John Brown S107 PR2 60 PR2 Programming 2 S107 John Brown S107 IAI 35 IAI Intro to AI

Student Grade^ Course

Student.ID = Grade.ID Course.Code = Grade.Code

JOINs

• JOINs can be used to

combine tables

• There are many types

of JOIN

• CROSS JOIN

• INNER JOIN

• NATURAL JOIN

• OUTER JOIN

• OUTER JOIN s are

linked with NULL s -

more later

A CROSS JOIN B

• returns all pairs of rows

from A and B

A NATURAL JOIN B

• returns pairs of rows

with common values

for identically named

columns and without

duplicating columns

A INNER JOIN B

• returns pairs of rows

satisfying a condition

NATURAL JOIN

SELECT * FROM

Student NATURAL JOIN

Enrolment

Enrolment

ID Code

123 DBS 124 PRG 124 DBS 126 PRG

Student

ID Name

123 John 124 Mary 125 Mark 126 Jane ID^ Name

123 John 124 Mary 124 Mary 126 Jane

Code DBS PRG DBS PRG

CROSS and NATURAL JOIN

SELECT * FROM

A CROSS JOIN B

• is the same as

SELECT * FROM A, B

SELECT * FROM

A NATURAL JOIN B

• is the same as

SELECT A.col1,… A.coln,

[and all other columns

apart from B.col1,…B.coln]

FROM A, B

WHERE A.col1 = B.col

AND A.col2 = B.col

...AND A.coln = B.col.n

(this assumes that col1…

coln in A and B have

common names)