Transcript Here - Lee Turner

SQL 101 – Class 1
Lee Turner
Agenda
1.
This is your life – SQL
•
•
•
2.
Some Super Simple Selection Strategies
•
•
3.
Create a simple table
Create a simple select query
Just a Rebel without a Where Clause
•
•
4.
A brief history of SQL
What SQL is and what it is not
Normalization
Using simple where clauses to filter results
Examining more complex where clauses
A SQL Query walks up to two tables in a
restaurant and says: “Mind if I join you?”
•
•
Inner Joins
Outer Joins
This is your life - SQL
SQL (Pronounced “si kwel”) stands for Structured Query Language. It is a specialpurpose programming language designed for managing data held in a relational
database management system (RDBMS).
Initially developed in the early 1970s by IBM, it was picked up by Relational Software,
Inc. (now Oracle Corporation) and offered commercially in 1979 as Oracle V2.
SQL was one of the first commercial languages to use the relational model and has
become the most widely used database language.
SQL became a standard of the American National Standards
Institute (ANSI) in 1986, and of the International
Organization for Standardization (ISO) in 1987.[12] Since
then, the standard has been enhanced several times with
added features. Despite these standards, code is not
completely portable among different database systems,
which can lead to vendor lock-in. The different makers do
not perfectly adhere to the standard, for instance by adding
extensions, and the standard itself is sometimes
ambiguous.
Source: http://en.wikipedia.org/wiki/SQL
What SQL Is and Is Not
SQL Is A
•
•
Computer Language
•
Considered a 4GL
•
Data Definition
•
Data Manipulation
Standard
•
•
Vendors typically deviate
from the standard slightly.
Language that is pretty
simple to grasp the basics,
yet capable of performing
very complex tasks.
SQL Is Not
•
•
•
A program owned by any
one company.
The only way to retrieve
data.
Synonymous with
“Relational”
Normalization
A relational database stores data in tables that “relate” to other tables. For instance
you may have a customer table with information about your customer and an orders
table with information about your orders. Within the orders table there is a field that
contains the “Customer ID” relating the orders table back to the customer table.
The purpose of normalization is to reduce redundant data within
the database. Normalized databases typically have fewer data
integrity issues, faster index creation and updates as well improved
database locking.
Some Super Simple Select Strategies
CREATE A SIMPLE TABLE
•
•
•
•
Go to Database, right-click on
“Tables” and select “New Table”
Click “New Query”
• Type Table DDL statement
For now, lets stick with int and
varchar data types. We will pick up
more in future classes.
Identity – Automagically created
sequential number that ensures
every record has a unique value
(kinda:)
CREATE A SIMPLE QUERY
•
Click on “New Query”
• Type in Select statement
•
Select “Query”, “Design Query
In Editor…”
Right-Click on Table
• “Select Top 1000 Rows”
• “Edit Top 200 Rows”
•
•
“Select * from Foo;”
Just a Rebel without a Where Clause
The Where clause is used to limit the returned results to only
those that meet a specific criteria.
Predicates
• =, <>, >, >=, <, <=, IN, BETWEEN, LIKE, IS NULL or IS NOT NULL
Simple
• “Select * from Foo where customer_name = ‘Google’;”
• “Select * from Foo where customer_name LIKE ‘Goo%’;”
•
•
•
Complex - The keywords AND and OR can be used
to combine two predicates into a new one. If
multiple combinations are applied, parentheses can
be used to group combinations to indicate the
order of evaluation. Without parentheses,
the AND operator has a stronger binding than OR.
Table Joins
•
•
•
•
The SQL JOIN clause is used to join two or more tables using
a common field between them.
A JOIN can either be an “INNER” or “OUTER” (Just like belly
buttons! :)
INNER – Only rows that match up. This may bite you when
you are doing analysis.
OUTER –
• Left – Returns all rows from the left table,
and matched rows from the right.
• Right – Returns all rows from the right table,
and matched rows from the left.
• Full – Return all rows when there is a match
in one of the tables.