Download Database Systems: SQL SELECT Overview and Queries on Multiple Tables and more Slides Introduction to Database Management Systems in PDF only on Docsity!
G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
SQL SELECT Overview
SELECT
[DISTINCT | ALL] <column-list>
FROM <table-names>
[WHERE ]
[ORDER BY <column-list>]
[GROUP BY <column-list>]
[HAVING ]
• ([] - optional, | - or)
G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
Example Tables
Student ID First Last S103 John Smith S104 Mary Jones S105 Jane Brown S106 Mark Jones S107 John Brown Course Code Title DBS Database Systems PR1 Programming 1 PR2 Programming 2 IAI Intro to AI
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
G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
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
G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
WHERE Clauses
• Usually you don’t
want all the rows
restricts the rows that
are returned
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 > 70)
G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
WHERE Examples
SELECT * FROM Grade
WHERE Mark >= 60
SELECT DISTINCT ID
FROM Grade
WHERE Mark >= 60
ID Code Mark
S103 DBS 72 S104 PR1 68 S104 IAI 65 S107 PR1 76 S107 PR2 60
ID
S
S
S
G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
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
G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
One Solution
SELECT ID, Mark FROM Grade
WHERE (Code = ‘IAI’) AND
(Mark >= 40)
G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
SELECT from Multiple Tables
• Often you need to
combine information
from two or more
tables
• You can get the
effect of a product
by using
SELECT * FROM Table1,
Table2...
• If the tables have
columns with the
same name
ambiguity results
• You resolve this by
referencing columns
with the table name
TableName.Column
G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
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 G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
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...
G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
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 S107 John Brown S107 IAI 35
SELECT ... FROM Student, Grade WHERE (Student.ID = Grade.ID) AND ...
G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
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)
G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
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 * FROM
A, B
WHERE A.col1 = B.col
AND A.col2 = B.col
AND ...
G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
INNER JOIN
• INNER JOIN s specify
a condition which the
pairs of rows satisfy
SELECT * FROM
A INNER JOIN B
ON
• Can also use
SELECT * FROM
A INNER JOIN B
USING
(col1, col2,…)
• Chooses rows where
the given columns
are equal
G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
INNER JOIN
SELECT * FROM
Student INNER JOIN
Enrolment USING (ID)
Enrolment ID Code 123 DBS 124 PRG 124 DBS 126 PRG
Student ID Name
123 John 124 Mary 125 Mark 126 Jane
ID Name ID Code
123 John 123 DBS 124 Mary 124 PRG 124 Mary 124 DBS 126 Jane 126 PRG
G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
INNER JOIN
SELECT * FROM
Buyer INNER JOIN
Property ON
Price <= Budget
Property Address Price
15 High St 85, 12 Queen St 125, 87 Oak Row 175,
Buyer Name Budget
Smith 100, Jones 150, Green 80,
Name Budget Address Price Smith 100,000 15 High St 85, Jones 150,000 15 High St 85, Jones 150,000 12 Queen St 125,
G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
INNER JOIN
SELECT * FROM
A INNER JOIN B
ON
• is the same as
SELECT * FROM A, B
WHERE
SELECT * FROM
A INNER JOIN B
USING(col1, col2,...)
•is the same as
SELECT * FROM A, B
WHERE A.col1 = B.col
AND A.col2 = B.col
AND ...
G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
JOINs vs WHERE Clauses
• JOINs (so far) are
not needed
same effect by
selecting from
multiple tables with
an appropriate
WHERE clause
JOINs or not?
• Yes, because
concise queries
very common
• No, because
varies a fair bit
among SQL dialects
G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
Writing Queries
• When writing queries
ways to write the
query
about being correct,
clear, and concise in
that order
being clever or
efficient
• Most DBMSs have
query optimisers
query and figure out
how to efficiently
execute it
easier to optimise
ways to improve
efficiency later
G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
This Lecture in Exams
Track cID Num Title Time aID 1 1 Violent 239 1 1 2 Every Girl 410 1 1 3 Breather 217 1 1 4 Part of Me 279 1 2 1 Star 362 1 2 2 Teaboy 417 2
CD
cID Title Price 1 Mix 9. 2 Compilation 12.
Artist aID Name 1 Stellar 2 Cloudboy
G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
This Lecture in Exams
Find a list of all the CD titles.
(1 mark)
Find a list of the titles of tracks that are more than 300 seconds
long.
(2 marks)
Find a list of the names of those artists who have a track on the
CD with the title “Compilation”.
(4 marks)
