Transcript Chapter 3
The WHERE clause, also called the predicate, provides the
power to narrow down the scope of the data retrieved.
Comparison Operators
Comparison Operator Definition
=
Equal
!=,<>
Not equal
>,>=
Greater than, greater than or equal to
<,<=
Less than, less than or equal to
BETWEEN…And..
Inclusive of two values
LIKE
Pattern matching with wildcard
characters % and _
IN(…)
List of values
IS NULL
Test for null values
Equality,
denoted by =
Select first_name, last_name, phone
from instructor
where last_name = ‘Schorin’;
Inequality, denoted by !=
Data types must be the same.
>,<, >=,<=
Select description, cost
from course
where cost >= 1195;
Tests
for a range of values.
Select description, cost
from course
where cost between 1000 and 1100;
Works
with a list of value, separated by
commas, contained within a set of
parentheses.
Select description, cost
from course
where cost in (1095, 1595);
Performs
pattern matching using wild cards %
and _
Select first_name, last_name, phone
from instructor
where last_name like ‘%S%’;
Select first_name, last_name, phone
from instructor
where last_name like ‘_o%’;
All
previously mentioned operators can be
negated with the NOT comparison operator.
Select phone
from instructor
where last_name not like ‘%S%’;
Is
Null and Is Not Null determines whether
there is an unknown value in the data.
Select description, prerequisite
from course
where prerequisite Is Null;
Comparison operators can be combined with the help of the
logical operators AND and OR
Select description, cost
from course
where cost = 1095 or cost = 1195 and description LIKE ‘I%’;
AND always takes precedence over OR, but precedence can be
changed with parentheses.
‘I%’;
Select description, cost
from course
where (cost = 1095 or cost = 1195) and description like
SQL
uses tri-value logic; this means a
condition can evaluate to true, false, or
unknown(null);
Null values cannot be compared so they
cannot be returned for queries using non-null
values with comparison operators.
TRUE
FALSE
UNKNOWN
TRUE
TRUE
FALSE
UNKNOWN
FALSE
FALSE
FALSE
FALSE
UNKNOWN
UNKNOWN
FALSE
UNKNOWN
OR TRUTH TABLE
TRUE
FALSE
UNKNOWN
TRUE
TRUE
TRUE
TRUE
FALSE
TRUE
FALSE
UNKNOWN
UNKNOWN
TRUE
UNKNOWN
UNKNOWN
NOT
TRUE
FALSE
UNKNOWN
FALSE
TRUE
UNKNOWN
Go
to page 111 in book
Answer and discuss answers in class.
Data is not stored in any particular order
Result sets are displayed in whatever order they
are returned from the database
The ORDER BY clause to is used to order data any
way you wish.
You can use ASC or DESC and the sequence
number of the column instead of the name.
Select course_no, description
from course
where prerequisite is NULL
ORDER BY DESCRIPTION.
If
the SELECT list contains DISTINCT, the
column(s)the keyword pertains to must also
be listed in the ORDER BY clause.
SELECT distinct first_name, last_name
from student
where zip =‘10025’
order by first_name, last_name;
The
default sort order is to have nulls listed
last.
This can be changed by using NULLS FIRST or
NULLS LAST in the ORDER BY clause.
Select DISTINCT cost
from course
order by cost nulls first;
Double-click
one of the columns in the
column header to sort.
You can also use the SQL Developer Data tab,
which allows you to retrieve data from a
table without writing a SQL statement.
Sort and filters can be done using this tab.
A
column alias can be used in the SELECT list
to give a column or an expression an alias.
You can order by an alias name.
Select first_name first,
first_name “First Name”,
first_name as “First”
From student
Where zip = ‘10025’;
Placing
comments or remarks in a SQL
statement is very useful for documenting
purpose, thought, and ideas.
Very handy for when you have developed
multiple statements saved in a script.
You must identify a comment with either a –
or /* */
--This is a single line comment
/*This is a multi-line
comment */
Click
CTRL+S on the keyboard, the save icon,
or go to FileSave to save the sql script .
To open the file, use CTRL+O, the open icon,
or go to FileOpen.
You will inevitably make mistakes. Oracle
returns an error number and an error message to
inform you of any mistake.
Errors can be one or many and the error message
is not always indicative of the problem.
The parser works from the front of the query and
works backward; therefore, the first error
message will be for the first error the parser
encounters.
You can look up the Oracle error message in the
Oracle Database Error Messages manual at
Oracle.com or refer to Appendix G and Appendix
H.
Go
to page 127 in book
Answer and discuss answers in class.
Quiz
will be given at the beginning of our
next class