The where clause stipulates a test predicate that each row must pass to be returned in the select statement SELECT FROM students WHERE spellperformance gt 8 SELECT FROM students WHERE spellperformance gt 8 ID: 928095
Download Presentation The PPT/PDF document "SQL – Where Where SELECT ... FROM ....." is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
SQL – Where
Slide2Where
SELECT ... FROM ... WHERE ...;
The where clause stipulates a test (predicate) that each row must pass to be returned in the select statement.
SELECT * FROM students WHERE spell_performance > 8;
Slide3SELECT * FROM students WHERE spell_performance > 8;
first_name
spell_performance
type_cat
Michael
8
No
Austin
8
cat
Adi
10
Dog
Daniel
9
cat
Wesley
8
cat
Slide4SELECT * FROM students WHERE spell_performance > 8;
first_name
spell_performance
type_cat
Michael
8
No
Austin
8
cat
Adi
10
Dog
Daniel
9
cat
Wesley
8
cat
Slide5SELECT * FROM students WHERE spell_performance > 8;
first_name
spell_performance
type_cat
Adi
10
Dog
Daniel
9
cat
Slide6What should happen for the query:
It should return 0 rows
It should raise a syntax error
It should return 1 row
It should raise a "no rows" error
SELECT * FROM students WHERE
spell_performance
>
10;
first_name
spell_
performance
type_cat
Michael
8
No
Austin
8
cat
Adi
10
Dog
Daniel
9
cat
Josh
NULL
cat
Slide7SQL operators
Arithmetic Operators:
+ - * / %
Comparison Operators:
= != <> > < >= <=
Logical Operators
ALL AND ANY BETWEEN EXISTS IN LIKE NOT OR IS
Slide8AND AND OR
SELECT * FROM students WHERE spell_performance > 8 AND
type_cat
= 'cat';
1 row
SELECT * FROM students WHERE spell_performance > 8
OR
type_cat
= 'cat
';
4 rows (poor Michael)
first_name
spell_performance
type_cat
Michael
8
No
Austin
8
cat
Adi
10
Dog
Daniel
9
cat
Wesley
8
cat
Slide9Between
SELECT * FROM students WHERE spell_performance BETWEEN
9
AND 10;
2
rows
Between is inclusive of boundaries
SELECT * FROM students WHERE spell_performance
NOT BETWEEN
9 AND 10
;
3 rows
first_name
spell_performance
type_cat
Michael
8
No
Austin
8
cat
Adi
10
Dog
Daniel
9
cat
Wesley
8
cat
Slide10IS
SELECT * FROM students WHERE
fav_color
IS NULL;
2
rows
SELECT * FROM students WHERE
fav_color
IS
NOT NULL
;
3 rows
The "IS" operator is only to be used to test for NULL values.
first_name
spell_performance
fav_color
Michael
8
NULL
Austin
8
'red'
Adi
10
NULL
Daniel
9
'Green'
Wesley
8
'dark green'
Slide11Like
SELECT * FROM students WHERE
fav_color
LIKE '%
reen
';
2
rows
%
is a wildcard that can match 0 or more characters.
Should have been
*
, but it was already taken_ (underscore) is a wildcard representing a single characterShould have been
.
grrrrr
first_name
spell_performance
fav_color
Michael
8
NULL
Austin
8
'red'
Adi
10
NULL
Daniel
9
'Green'
Wesley
8
'dark green'
Slide12IN
SELECT * FROM students WHERE spell_performance IN (5, 8, 10);
4 rows
IN tests for inclusion in a static, parenthesized list.
first_name
spell_performance
fav_color
Michael
8
NULL
Austin
8
'red'
Adi
10
NULL
Daniel
9
'Green'
Wesley
8
'dark green'
Slide13EXISTS, ANY, ALL
These operators use sub-queries, which we'll get to later in the course.
Slide14Comparing NULLs to values
The logic of conditions in SQL is really 3-valued logic: TRUE, FALSE, UNKNOWN.
Comparing any value (including NULL itself) with NULL yields UNKNOWN.
A tuple is in a query answer if and only if the WHERE clause is TRUE (not FALSE or UNKNOWN).
Slide15What should happen for the query:
It should return 0 rows
It should return 1 row
It should return 2 rows
It should return 3 rows
SELECT * FROM students WHERE
NOT
spell_performance
>
8;
first_name
spell_
performance
type_cat
Michael
8
No
Austin
8
cat
Adi
10
Dog
Daniel
9
cat
Wesley
8
cat
Josh
NULL
cat
Slide1616
Three-Valued Logic
To understand how AND, OR, and NOT work in 3-valued logic, think of TRUE = 1, FALSE = 0, and UNKNOWN = ½.
AND = MIN; OR = MAX, NOT(
x
) = 1-
x
.
Example:
TRUE
AND (FALSE OR
NOT (
UNKNOWN)) = MIN(1, MAX(0, (1 - ½ ))) =MIN(1, MAX(0, ½ )) = MIN(1, ½ ) = ½.
Slide1717
Example with null
UNKNOWN
first_name
spell_performance
Yi
NULL
SELECT * FROM students WHERE
spell_performance > 5 OR spell_performance <= 5;
UNKNOWN
UNKNOWN
Slide1818
Reason: 2-Valued Laws != 3-Valued Laws
Some common laws, like commutativity of AND, hold in 3-valued logic.
But not others, e.g., the
law of the excluded middle
:
p
OR NOT
p
= TRUE.
When
p
= UNKNOWN, the left side is MAX( ½, (1 – ½ )) = ½ != 1.