Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

Introduction to SQL - RMAIR 2012

Introduction for the RMAIR Conference SQL Workshop, 2012
by

mike ellison

on 26 April 2014

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Introduction to SQL - RMAIR 2012

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
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
WHAT
Mike Ellison
UNLV Office of Institutional Analysis & Planning
mike.ellison@unlv.edu

RMAIR 2012
Full transcript