sql basics ii.pdf

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