Visualize
Results
SQL
What is SQL?
Imperative
Declarative
explicitly tells a computer to accomplish something
Step 1: Pour a cup of flour in a mixing bowl.
Step 2: Add two cups of milk
Step 3: Stir the mixture once.
Step 4: Go back to Step 3 and repeat seventeen times.
... etc...
describes the end result should be
I want a home-baked chocolate cake...
... with chocolate frosting
... cut into 8 slices
... with no more than 62 calories a slice
Structured Query Language
Developed as "SEQUEL" by IBM in the early-to-mid-1970's
First used in a commercial database by "Relational Software, Inc." in 1979
First standardized by ANSI as "SQL" in 1986
Most recent revision - 2008
"See-quel"
"Ess - Cue - Ell"
"Squeal"
Syntax
SQL is a standardized computer language
for interacting with relational database systems
SQL is Declarative
...we want to describe what our end results should look like
Report types?
Listing
Aggregation
Distribution
Columns?
how many?
data? computed?
what order?
titles?
Rows?
from which table? or tables?
all rows?
a subset? based on what?
sort order?
information about each individual record, one at a time
information about a group of records
individual characteristics
groupings
counts
totals
averages
highest/lowest values
measures
aggregations in custom ranges
computed column for ranges
Relational
Databases
First, there was FLAT...
knowledge of internal representation required
sequential access
Then came E.F. Codd...
natural representation of data, rather than machine
invented the Relational Database in 1970
store and access data based on mathematical set theory
faster searching, better sorting
...and multiple, related tables
data structured around subjects, preventing redundancy
subjects cross-reference eachother through key fields
normalization
relation=table
tuple=row
attribute=column
Primary Key
no two rows in a table can share the same primary key value
a cross-referenced primary key is called a Foreign Key
Keys are typical when joining tables... exceptions?
A field or combination of fields...
... that uniquely identifies each row in a table.
Statements
when writing SQL, we construct
made up of
Keywords
and
Clauses
A keyword begins each statement. This describes the intent of the statment.
SELECT
INSERT
UPDATE
DELETE
CREATE TABLE
ALTER VIEW
DROP PROCEDURE
DML: Data Manipulation Language
DDL: Data Definition Language
Within a statement, additional keywords begin clauses.
SELECT
ID, FirstName, LastName, Age
FROM
WHERE
ORDER BY
Students
Department='EGR' and Age >= 19
LastName, FirstName
Clauses let you describe your intent with precision
... and you control the readability
whitespace and comments
/* ....... */ or two dashes --
keywords, table names, column names are not case-sensitive
(data might be though!)
select id,firstname,lastname,age from
students where department='EGR' and age
>= 19 order by lastname,firstname
SELECT
ID, FirstName, LastName, Age
FROM
WHERE
ORDER BY
Students
Department='EGR' and Age >= 19
LastName, FirstName
/* list all students 19 and older in the department
of Engineering */
-- main table
<html>
Here's some plain text.
Here's some <b>BOLD</b> text.
And a nice bulleted list:
<ul>
<li>Item 1</li>
<li>Item 2</li>
<li>Item 3</li>
</ul>
</html>
Here's some plain text.
Here's some
BOLD
text.
And a nice bulleted list:
Item 1
Item 2
Item 3
Language
HOW
WHATMore presentations by
A Strategy for Capturing Snapshot and Census DataSets
mike ellison on
Presentation for the 2011 RMAIR conference