Sql basics joi ns and common commands (1)

SQL Basics
Common Commands and JOINs

Common Command Example Tables
SharkTbl
ID Name Street

1 Alf 111 Street

2 Barry 222 Street

3 Charlie 333 Street

4 Dirk 444 Street

WhaleTblfe
ID Name Street Greeting

1 Antenna Assessor 5 Ave howdy

2 Bear Barber 6 Ave hello

3 Chorizo Chef 7 Ave hi

4 Database Diver 8 Ave salve citizen

Multi Table SELECTs
They look like this:

SELECT *
FROM SharkTbl, WhaleTbl;

But you can run into trouble if they share
common column names.

Table Defined Columns
This is going to break...

SELECT Name, Street FROM SharkTbl, WhaleTbl;

...because both SharkTbl and WhaleTbl have columns
named Name and Street.

Result:

For more than one table, use this format:

SELECT <tbl 1>.<col 1>, <tbl 2>.<col 2>
FROM <tbl 1>, <tbl 2>

Here's how it looks in action:

SELECT SharkTbl.Name, WhaleTbl.Street

If some columns are unique and some are
ambiguous, you can only include table
definitions where you must, like so:

SELECT Greeting, SharkTbl.Name

The "AS" Column Alias Command
You use this to clean up table names.

Structure:

SELECT <tbl 1>.<col 1> AS <alias 1>
FROM <tbl 1>, <tbl 2>

The "AS" Column Alias Command
Example:

SELECT SharkTbl.Name AS Pudding,
WhaleTbl.Street AS Brownies

Changes The Column Names:

"AND" vs "OR"
AND Concept:

If A and B, then true

You've got to have both A and B before true

"AND" vs "OR"
OR Concept:

If A or B, then true

Having either A or B will make it true

You don't need both, just one or the other.

"AND" vs "OR"
This makes OR a more wide search

This makes AND a narrow and exact search

"AND" vs "OR"
Lets run some examples in phpMyAdmin
starting with this query:

SELECT Name, Street
FROM SharkTbl
WHERE Street = '222 Street'
OR Name = 'Barry';

The "AND" Command
What It Looks Like:

SELECT Name, Street
FROM SharkTbl
AND Name = 'Barry';

The "AND" Command
Rules:

ANDs are an extension of the WHERE
command.

You can use as many as you please, one per
condition.

The "OR" Command
What It Looks Like:

SELECT Name, Street
FROM SharkTbl
OR Name = 'Barry';

ORs are also an extension of the WHERE
command.

COUNT Command
Say you want a count of the number of rows
returned instead of the raw data.

Structure:

SELECT COUNT(<col 1>)
FROM <tbl 1>;

COUNT Command
Example:

SELECT COUNT(Name) AS NameCount
FROM SharkTbl;

Returns:

COUNT Command
Lets try it without the count to double check:

SELECT Name
FROM SharkTbl;

Yup, it works as 4 rows are returned:

JOINs
The Greatest SQL Command

JOIN Example Tables
PersonTbl
ID Name JobID

1 Alf 3

2 Barry 3

3 Charlie 1

4 Dirk 2

JobTbl
ID Name

1 Antenna Assessor

2 Bear Barber

3 Chorizo Chef

4 Database Diver

The Shape Of Things In Our World
● Oceans
○ Swimming Creatures
■ Orcas
● Transient
● Resident
● Offshore
■ Sharks
● Hammer Heads
● Great White Sharks
■ Other Less Interesting Fish
○ Reefs
■ Coral
■ Cement

● Orders
○ Products
■ Product Attributes
■ Manufacturers
■ Billing Codes
■ Part Numbers
○ Addresses
○ Tracking Numbers
○ Faxes
○ Notes

So, just about anything you look at can be
broken down into "A has Bs which have Cs":

● As
○ have Bs
■ which in turn have Cs
○ have Fs
○ have Gs
○ have Hs

To turn this pattern into databases:

1. Make A, B and C into tables

2. Relate the tables to one another

3. Describe any object or relation of objects

These three things allow table relations:

1. Primary Keys

2. Foreign Keys

3. JOINs in your SQL statements

Primary Keys
Remember that tables have IDs?

Like so:
ID Name

1 Alf

2 Barry

3 Charlie

4 Dirk

All the IDs we have seen so far are formally
known as Primary Keys.

Primary Keys
Primary keys are the IDs used to identify a row.

PersonTbl
ID Name

1 Alf

2 Barry

3 Charlie

4 Dirk

^ Primary key of the PersonTbl

Foreign Keys

Primary Keys are going to reference rows in
this table

Foreign Keys are going to reference rows in
another table

Sometimes called Secondary Keys

Foreign Keys
They look like this:

JobID is a Foreign Key...
ID Name JobID

1 Alf 3

2 Barry 3

3 Charlie 1

4 Dirk 2

...and ID is the Primary Key

Foreign Keys
Why? Because:

JobID talks about another table
ID Name JobID

1 Alf 3

2 Barry 3

3 Charlie 1

4 Dirk 2

...but ID talks about this table

Foreign Keys
Formal:

Foreign Keys are keys that relate back to the
Primary Keys of other tables.

Boiled Down:

You can use the JobID in PersonTbl to "look
up" values in JobTbl

Use A Foreign Key, Get A Value

So, what just happened?

● PersonTbl's 4th row had a JobID of 2

● We looked the JobTbl row with ID of 2

● The name of JobTbl ID = 2 is Bear Barber

● So JobID 2 is the same as Bear Barber!

Big Concept:

The arrow is the JOIN action.

The arrow drawn between PersonTbl and
JobTbl represents a JOIN.

A JOIN needs to know which Foreign Key in
PersonTbl relates to which Primary Key in
JobTbl.

Breaking Down A JOIN
Structure:

SELECT * FROM <table 1>
JOIN <table 2>
ON <table 1>.<col 1> = <table 2>.<col 2>

SELECT * FROM <table 1>
JOIN <table 2>
ON <table 1>.<col 1> = <table 2>.<col 2>

What each part does:

SELECT...FROM - Read all cols from table 1
JOIN - Read all cols from table 2 as well
ON - Use these keys to link the two tables

In English:

Select all columns from the one or more tables
named where the primary key of one table is
equal to the secondary key of the other.

Further boiled down:

Replace the JobID in PersonTbl with its name
in JobTbl

Live SQL Example:

SELECT PersonTbl.ID,
PersonTbl.Name AS PersonName, JobTbl.
Name AS JobName
FROM PersonTbl
JOIN JobTbl
ON PersonTbl.JobID = JobTbl.ID;

Result:

Boiled Down Concept:

We replaced JobIDs with Job Names using
JOIN

Implicit vs Explicit JOIN Statements
Implicit:

SELECT * FROM PersonTbl, JobTbl
WHERE PersonTbl.JobID = JobTbl.ID

Explicit:

SELECT * FROM PersonTbl
INNER JOIN JobTbl on JobTbl.ID = PersonTbl.
JobID

Implicit vs Explicit JOIN Statements
My examples have all be explicit.

Explicit is better, because the reader does not
have to guess if or how you are joining.

You write it out on this line:

INNER JOIN JobTbl on JobTbl.ID = PersonTbl.
JobID

JOINs Put Another Way
When getting your head around JOINs, it is
good to hear the same thing in many different
ways.

Here's a lady on youtube explaining it:

http://www.youtube.com/watch?
v=oWHO4lJlX54

Real World Example From MadCow
Goal:

Query all the available sizes of the ComfortGel
Original

PartsTbl
PNum PName

1802 ComfortGel Original Nasal CPAP Mask with Headgear

PartsAttributeTbl
PAID PNum Description

44 4266 Petite

45 4266 Small

46 4266 Medium

47 4266 Large

The SQL:

SELECT PartsTbl.PNum, PartAttributeTbl.
PAID, PartsTbl.PName, PartAttributeTbl.
Description FROM PartsTbl
JOIN PartAttributeTbl
ON PartsTbl.PNum = PartAttributeTbl.PNum
WHERE PartsTbl.PNum = 1802

The Result:

Break Down This SQL:

SELECT PartsTbl.PNum, PartAttributeTbl.
PAID, PartsTbl.PName, PartAttributeTbl.
Description FROM PartsTbl

It Means:

Select columns from PartsTbl


JOIN PartAttributeTbl

It Means:

We are also wanting columns from the
PartAttributeTbl


ON PartsTbl.PNum = PartAttributeTbl.PNum

It Means:

Match up the PNums in both tables


WHERE PartsTbl.PNum = 1802

It Means:

Look only for PNum 1802, which is the
ComfortGel Original's PartTbl PNum ID.

For The Next SQL Class...

● Advanced JOINs
○ INNER
○ LEFT
○ RIGHT
● UNIONS
● More Helpful Commands
● Advanced Real World Examples

Classes Next Thursday
Thurs Feb 21st @ Noon

John Nelson of IT presenting Virtualization 101.

Someone from Marketing presenting...something.

Sql basics joi ns and common commands (1)

More Related Content

Viewers also liked

Similar to Sql basics joi ns and common commands (1)

More from johnnygoodman

Sql basics joi ns and common commands (1)