Basic elements of an SQL query:

Basic elements of an SQL query:

The Select clause

The From clause

The Where clause

Wild Card Characters: * (ie select * from ….)
Ambiguity – (two tables have fields with the same names…)

..must be cleared up by using qualified names

Table A Table B

Fname Fname

Select A.Fname

From A,B

Using aliases

Select cust.customer_name as name

From customer_table cust

Where name = ‘Bob’;

Ordering of columns in the select clause:

Select A,B,C vs Select B,A,C

Using expressions:

Stored functions: Sum, Avg, Mean, Min, etc

Creating expressions: Select qty, price, qty*price as total

Aggregated Data vs. row level data: Count, Min, Max, Sum, Avg

Count vs. Count(*) (only valid values vs. total including NULLS)

Using wild cards in pattern matching:

Orcale and DB2 (% _ ) Access (* _ )

Select customer_ID

From customer

Where fname = ‘B%’ NO,NO,NO!!!!!!!!!!

Like ‘B%’

Like ‘%z%’

Like ‘____b%’

Comparison operators: = > < >= <= <> !=

Boolean Operators:

And, Or, Not

Ranges

A>3 and A < 20

A>=3 and A<=20 ~ A between 3 and 20

Using Distinct (unique values)

Using IN and NOT IN with Lists

Sub Queries (Nested sub queries)

Correlated Sub Queries

Unions

Views

Sorting with the Order By clause

Categorizing results: Group by clause

>Filtering with Group bys - Having
Multi Table Joins
Syntax for Joins

Two methods:

(We have two tables, table A and table B.
Both tables have a columns named column_a.
The data in column_a in both tables is simalar data.)

Select *
From A,B
Where A.column_a = B.column_a

or

Select *
From A inner join B on A.column_a = B.column_a

Equi Join vs. Natural Join

Cross Products or "My Mommy told me to join more..."
(The evil Cartesian Product!!!)

One where clause to join tables for each pair of tables

(Or if N is the number or tables in our From clause, we should have N-1 join coditions...)

2 tables – one where

3 tables – two where

4 tables – three where!!!

Inner Join

Outer Join (Left and Right)
Syntax for Outer Joins:

(We have two tables, table A and table B.
Both tables have a columns named column_a.
The data in column_a in both tables is simalar data.)

For a Left Join:

Select *
From A,B
Where A.column_a *= B.column_a

Or

Select *
From A left outer join B on A.column_a = B.column_a