Transcript CSS122_FALL08_LECTURE2

Using Relational Databases and SQL
Lecture 2:
Single-Table Selections
Steven Emory
Department of Computer Science
California State University, Los Angeles
Miscellany
CSNS
Please sign up
Please specify a valid email. No email = no grades.
Emails might come in as spam (Yahoo!)
Wiki Page
New office hours
New tutorials
Fun stuff (well... not really...)
Lyric database (for Labs)
Books database (for Homework)
Review: Part I
Database
Database Model
Relational Model
Tables
Attributes
Types
Tuples
Relationships
Schema
Review: Part II
Database Management System
Query Language
Structured Query Language
Candidate Keys
A unique identifier (may contain multiple
attributes).
Examples:
Artists table  ArtistID, ArtistName, ...
Students table  CIN and SSN
Primary Keys
A candidate key that has been singled out to
uniquely identify each record.
Difference between candidate key and primary
key? A table may have more than one candidate
key, while only one primary key.
Out of all candidate keys, which key do you
choose to be the primary key? Just pick one!
Students table  CIN or SSN? Probably best to
choose CIN.
Foreign Keys
Most table relationships almost always involve a
primary key.
A foreign key is just a key at the other end of the
relationship.
We usually say a foreign key ‘references’ a
primary key.
Example:
In Titles table, Genre is a foreign key that references
the primary key Genre in the Genre table.
SQL Data Types
Strings (C-syntax)
‘Peterson’, ‘Joe\’s’, “Peterson”, “Joe’s”, “A\\B”
Integers
1, 2, 3, etc.
Decimals
1.45, 2.83, 3.14159
NULL
NULL, null
Basic Queries
Basic format is:
SELECT field_name1, field_name2, ...
FROM table_name
[WHERE conditions]
To automatically select ALL fields:
SELECT *
FROM table_name
[WHERE conditions]
Code in [] is optional
Basic Examples
List all attributes for all artists.
SELECT *
FROM Artists;
List the first name of all members.
SELECT FirstName
FROM Members
List all artists from the United States.
SELECT ArtistName FROM Artists
WHERE Country='USA'
Ordering By Attributes
Syntax:
SELECT A1, A2, ...
FROM tablename
[WHERE conditions]
ORDER BY F1 [ASC | DESC], F2 [ASC | DESC], ...
Example:
List all artist names in decreasing order.
SELECT ArtistName
FROM Artists
ORDER BY ArtistName DESC;
Ordering By Attributes
Note that the ORDER BY clause can take
multiple field names.
Example:
List all artists and their respective countries, sorted
by country first, then by artist name.
SELECT ArtistName, Country
FROM Artists
ORDER BY Country, ArtistName;
Ordering By Attributes
Note that you can also mix ASC and DESC.
Example:
List all member’s first and last names along with their
respective countries, sorted by country first in
ascending order, followed by first name in descending
order.
SELECT FirstName, LastName, Country
FROM Members
ORDER BY Country ASC, FirstName DESC;
Operators
Arithmetic
+, -, *, /, %
Conditional operators:
<, >, <=, >=, =, <>, BETWEEN
Logical operators: AND, OR, NOT
Other operators: IS NULL
Field Arithmetic
You can use simple mathematical expressions in
the field parameters of the SELECT clause.
Examples:
What is ((2 + 2)*4 – 7)/3?
SELECT ((2 + 2)*4 – 7)/3;
All sales people desire a 2 times pay raise. Display all
sales people and their desired salaries.
SELECT FirstName, LastName, 2*Base
FROM SalesPeople;
The WHERE Clause
Use the WHERE clause to filter results.
Examples:
SELECT * FROM Artists;
SELECT * FROM Artists WHERE
Country=‘Canada’;
The statement in the WHERE clause gets
executed once per row.
AND, OR, and NOT
Examples:
Display all member names from California or Texas.
SELECT FirstName, LastName FROM Members
WHERE Region=‘CA’ OR Region=‘TX’;
Display all titles whose genre is not alternative.
SELECT * FROM Titles
WHERE NOT (Genre=‘alternative’);
Display all member names from California or Texas
who have a sales ID of 2.
SELECT FirstName, LastName FROM Members
WHERE (Region=‘CA’ OR Region=‘TX’) AND
SalesID=2;
AND, OR, and NOT
Be careful of parentheses:
SELECT FirstName, LastName FROM Members
WHERE (Region=‘CA’ OR Region=‘TX’) AND
SalesID=2;
SELECT FirstName, LastName FROM Members
WHERE Region=‘CA’ OR Region=‘TX’ AND
SalesID=2;
OUCH! 2nd query does not return the right result.
BETWEEN
Like saying fieldname >= x AND fieldname <= y
List the titles and tracks of all tracks with lengths
between 240 and 300 seconds.
SELECT TrackTitle, LengthSeconds
FROM Tracks
WHERE LengthSeconds BETWEEN 240 AND 300;
IS NULL
Do not use conditional operators to compare NULL
NULL is a special keyword, not a value (as in say Java)
Examples:
Select all artist names who do not have a web page.
SELECT ArtistName
FROM Artists
WHERE WebAddress IS NULL;
Select all artist names who do have a web page.
SELECT ArtistName
FROM Artists
WHERE NOT (WebAddress IS NULL);
LIKE
Used for pattern matching
%  any zero or more characters
_  any single character
Example:
List all member names and their emails whose emails
have a .org domain.
SELECT FirstName, LastName, Email
FROM Members
WHERE Email LIKE '%.org';
NOT LIKE
Negation of LIKE
Ignores comparing NULL strings
Example:
List all member names and their emails whose emails
do not come from a .org domain.
SELECT FirstName, LastName, Email
FROM Members
WHERE Email NOT LIKE '%.org';
CASE Statements
Use in the SELECT clause when you want to
check and possibly modify/replace a column
value before it is displayed
Let’s go over the syntax first, and then go over
some tips and examples
CASE: Switch Style
Syntax:
CASE case_value
WHEN when_value THEN statement_list
WHEN when_value THEN statement_list
…
ELSE statement_list
END
It’s like saying:
if(case_value=when_value) then statement_list;
else if(case_value=when_value) then statement_list;
…
else statement_list;
CASE: IF-ELSE Style
Syntax:
CASE
WHEN condition THEN statement_list
WHEN condition THEN statement_list
…
ELSE statement_list
END
It’s like saying:
if(condition) then statement_list;
else if(condition) then statement_list;
…
else statement_list;
CASE Examples
List all track titles and their length in minutes. If
the length of the track title is less than 3 minutes,
display ‘Short Track’; otherwise, display ‘Long
Track.’
SELECT TrackTitle, LengthSeconds/60,
CASE WHEN LengthSeconds/60 < 3
THEN 'Short Track‘
ELSE 'Long Track’
END
FROM Tracks;
CASE Examples
List all track titles and their length in minutes. If
the length of the track title is less than 3 minutes,
display ‘Short Track’; less than 4 minutes, display
‘Medium Track’; otherwise, display ‘Long Track.’
SELECT TrackTitle, LengthSeconds/60,
CASE
WHEN LengthSeconds/60 < 3 THEN ‘Short Track’
WHEN LengthSeconds/60 < 4 THEN ‘Medium Track’
ELSE ‘Long Track’
END
FROM Tracks;
Column Aliases
Last CASE example had a really long column name.
To get rid of that we can use column aliases
SELECT expression AS alias FROM …
Example:
SELECT TrackTitle, LengthSeconds/60,
CASE
WHEN LengthSeconds/60 < 3 THEN ‘Short Track’
WHEN LengthSeconds/60 < 4 THEN ‘Medium Track’
ELSE ‘Long Track’
END AS ‘Track Description’
FROM Tracks;
DISTINCT
Use when you want to remove duplicate results
Example:
Display a list of unique countries that all members
come from.
SELECT Country FROM Members; // WRONG!!!
SELECT DISTINCT Country FROM Members;
// CORRECT!!!