sql basics ii.pdf
DESCRIPTION
Uploaded from Google DocsTRANSCRIPT
SQL Basics Part II SQL Basics Part IIJonathan Miller Jonathan Miller Learning SQL Series Learning
SQL Series
1
Items Items To To be be Covered Covered ∎
Review Review of of SQL SQL Basics Basics I I ∎
Join Join Types Types ∎Inner Inner & & Outer Outer Joins Joins ∎
SelfSelf-JoinsJoins∎
Working Working With With Multiple Multiple Queries Queries ∎ UNION, UNION, INTERSECT,
INTERSECT, MINUS MINUS ∎Subqueries Subqueries & & Correlated Correlated
Subqueries Subqueries ∎
EXISTS EXISTS & & NOT NOT EXISTS EXISTS ∎
Functions Functions ∎GroupGroup∎
StringString∎
DateDate∎
Number Number ∎
Participants Participants Questions
Questions & & Answers Answers (hopefully!) (hopefully!)2
Review Review of of SQL SQL Basics Basics I I ∎Structure Structure of of a a SQL SQL Statement Statement SELECT
SELECT column(s) column(s FROM FROM table(stable(s) WHEREWHERE condition(s)
condition(s ORDER ORDER BY BY column(s) column(s Optional
Optional Elements Elements3
Cartesian Cartesian / / Simple Simple Join Join SELECT mo_id, poc, parameter_desc FROM monitors, parametersPARAMETERSParameter_Code Parameter_Desc
44201 Ozone
42101 CO
42401 SO2
81102 PM10MONITORS
MO_ID SI_SI_ID PA_PARAMETER_CODE POC
1 1 44201 1
2 1 42101 1
3 1 42101 2
4 2 81102 1
5 2 44201 1
6 3 42401 14
Primary Primary & & Foreign Foreign Keys Keys ∎Primary Primary Key Key (s) (s)
∎Columns Columns on on a a Table Table that that Uniquely Uniquely
Identify Identify a a Record Record on on the the Table Table ∎
Can Can be be composed composed of of 1 1 or or more more columns columns ∎
Foreign Foreign Key Key (s) (s) ∎Column Column on on a a table table
that that references references the the Primary Primary Key Key of of
another another Table Table ∎Can Can be be composed composed
of of one one or or more more columns columns5
Inner Inner Join Join Between Between 2 2 Tables Tables SELECT mo_id, poc, parameter_desc FROM monitors, parameters WHERE pa_parameter_code = parameter_codePARAMETERSParameter_Code Parameter_Desc
44201 Ozone
42101 CO
42401 SO2
81102 PM10MONITORSMO_ID SI_SI_ID PA_PARAMETER_CODE POC
1 1 44201 1
2 1 42101 1
3 1 42101 2
4 2 81102 1
5 2 44201 1
6 3 42401 6
1
Joining Joining Tables Tables Together Together ∎
Joins Joins Between Between
Tables Tables are are Usually Usually Based Based on on Primary Primary / / Foreign
Foreign Keys Keys ∎Make Make Sure Sure Joins Joins
Between Between All All Tables Tables in in the the FROM FROM
Clause Clause Exist Exist ∎List List Joins Joins Between Between Tables Tables Before Before Other Other Selection Selection ElementsElements7
Aliases Aliases∎
““Shorthand Shorthand”” for for Table Table or or Column
Column References References ∎
SELECT SELECT Aliases Aliases Appear Appear as as Column
Column Headers Headers in in the the Output Output ∎
Aliases Aliases Cannot Cannot be be Keywords Keywords (SELECT, (SELECT, FROM, FROM, WHERE, WHERE, etc)etc)8
Join Join Types Types and and Putting Putting
Multiple Multiple SQL SQL Select Select Statements
Statements Together
Together SITESMONITORS
QUALIFIER_ DETAILS
RAW_DATA
MONITOR_ PROTOCOLS9
Join Join Types Types ∎Simple Simple Join: Join: ∎
No No links links made made between between multiple multiple tables tables ∎
RESULT: RESULT: Cartesian Cartesian Product Product ∎
Inner Inner Join Join ∎TABLE1.Foreign TABLE1.Foreign Key Key
= = TABLE2.Primary TABLE2.Primary Key Key ∎RESULT: tablestables RESULT: 1 1 record record for for each each match match between between the the 2 2 ∎
Outer Outer Join Join ∎TABLE1.Foreign TABLE1.Foreign Key
Key = = TABLE2.Primary TABLE2.Primary Key(+) Key(+) ∎RESULT: tables exists RESULT: tables exists in in AND AND TABLE1
TABLE1 1 1 1 1 record record record record for for for for each each each each match
match where where between between the the record record the the only only 2 2
1010