Transcript Here - Eden.Rutgers.edu

SQL Intermediate Workshop
Authored by Jay Mussan-Levy
Introduction to Querying
The power of SQL lies not in the creation of
tables, but the ability to attain information
from them.
Querying is asking the database if it has
certain things, then the database gives you the
results.
Select Statement
The way to query in SQL is through the select
statement
 The select statement has five main clauses but
“from” is the only required ones
 Here is the syntax for a select statement:
select all|distinct column1, column2
from table1, table2
where "some condition"
group by "some condition"
having "some condition"
order by "some condition"
Select Statement cont’d
Here is an example of a simple query:
select quantity, max(price)
from items_ordered
group by quantity;
This gets displays the quantity for the most
costly item for every quantity number, and
its corresponding price. Groups by quantity
(staring with 1)
Select Statement cont’d
Here is what the results would look like:
1
2
3
4
1250.00
88.70
14.75
125.00
Select Statement cont’d
Comparison Operators
=
>
<
>=
<=
<>
LIKE
equals
greater than
less than
greater than or equal to
less than or equal to
or != not equal to
string comparison test
Select Statement cont’d
Example:
select first, last, title
from employee
where title LIKE 'Pro%';
 Now the previous statement gets the first
name, last name, and title columns for all
employees whose title begins with Pro
Select Statement cont’d
Before
first last
title
Jonie Weber Secretary
Potsy Weber Programmer
After
age salary
28 19,500.00
32 45,300.00
Dirk Smith Programmer II 45 75,020.00
John Doe Programmer III 58 100,000.00
Joe Shmoe Manager
Jane Doe Analyst
36 62,000.00
22 45,000.00
first last
title
Potsy Weber Programmer
Dirk Smith Programmer II
John Doe
Programmer III
Select Statement All and
Distinct
ALL and DISTINCT will select either all or
unique records
ex)
Select DISTINCT age
from employee_info;
This will return all the unique ages you have
in the table
Select Statement All and
Distinct cont’d
Before
After
first
John
last
Jones
id
age city
99980 45 Payson
state
Arizona
age
Mary
Jones
99982 25 Payson
Arizona
Eric
Edwards 88232 32 San Diego
25
42
Mary Ann Edwards 88233 32 Phoenix
Ginger
Howell
California
Arizona
98002 42 Cottonwood Arizona
Sebastian Smith
92001 23 Gila Bend
Arizona
Gus
Gray
22322 35 Bagdad
Arizona
Mary Ann May
32326 52 Tucson
Arizona
Erica
Williams 32327 60 Show Low
Arizona
Leroy
Brown
32380 22 Pinetop
Arizona
Elroy
Cleaver
32382 22 Globe
Arizona
45
23
35
52
60
Select Statement Review
Please go to http://sqlcourse2.com/select2.html
Scroll to the bottom of the page and do
Problems 2 and 3
Select Statement Aggregate
Functions
Aggregate Functions are:
 Min - Returns the smallest value in a given column
 Max - Returns the largest value in a given column
 Sum - Returns the sum of the numeric values in a given
column
 Avg - Returns the average value of a given column
 Count - Returns the total number of values in a given
column
 Count(*) - returns the number of rows in a table
Select Statement Aggregate
Functions cont’d
Aggregate values are used to manipulate numeric
values in a column.
Here is an example:
Select avg(salary)
from employee;
Select Statement Aggregate
Functions cont’d
Before
first last
title
Jonie Weber Secretary
Potsy Weber Programmer
After
age salary
28 19,500.00
32 45,300.00
Dirk Smith Programmer II 45 75,020.00
John Doe Programmer III 58 100,000.00
Joe Shmoe Manager
Jane Doe Analyst
36 62,000.00
22 45,000.00
57,803.33
Select Statement Group By
The GROUP BY clause will gather
all of the rows together that contain data in the
specified column(s) and will allow aggregate
functions to be performed on the one or more
columns.
Select Statement Group By
cont’d
Here is the syntax:
Select "columnname", SUM("columnname2")
from "tablename"
Group By “some column name";
Select Statement Group By
cont’d
Select max(salary), title
from employee
group by title;
This statement will select the maximum salary for
each unique position.
Basically, the salary for the person who makes the
most in each position will be displayed. Their,
name, salary, and their position will be returned.
Select Statement Group By
cont’d

Before
first last title
Jonie Weber Secretary
Potsy Weber Programmer
After
age salary
28 19,500.00
32 45,300.00
Dirk Smith Programmer II 45 75,020.00
John Doe Programmer III 58 100,000.00
Joe Shmoe Manager
Jane Doe Analyst
36 62,000.00
22 45,000.00
first last
title
Salary
Jonie Weber Secretary
19,500.00
John Doe Programmer III 100,000.00
Joe Shmoe Manager
Jane Doe Analyst
62,000.00
45,000.00
Select Statement Group By
cont’d
How do you group everything of quantity 1 together,
quantity 2 together, quantity 3 together, etc. ?
What if you wanted to determine the largest cost
item for each grouped quantity?
Here is how:
Select quantity, max(price)
from items_ordered
group by quantity
Select Statement Group By
cont’d
Results
quantity price
1
2
1250.00
88.70
3
4
14.75
125.00
Select Statement Having Clause



Must follow the Group By clause
Allows you to specify conditions on the
rows for each group
Which rows should be selected will be
based on the conditions you specify
Select Statement Having Clause
cont’d
The syntax for Having is:
Select "columnname", sum("columnname2")
from "tablename"
group by "columnlist"
having "condition";
Select Statement Having Clause
cont’d
Let's say you have an employee table containing the
employee's name, title, salary, and age. If
you would like to select the average salary for
each position,you could enter:
Select title, avg(salary)
from employee
group by title;
Select Statement Having Clause
cont’d
Before
first last
title
Jonie Weber Secretary
Potsy Weber Programmer
After
age salary
28 19,500.00
32 45,300.00
Dirk Smith Programmer II 45 75,020.00
John Doe Programmer III 58 100,000.00
Joe Shmoe Manager
Jane Doe Analyst
36 62,000.00
22 45,000.00
title
salary
Secretary
19,500.00
Programmer 73,440.00
Manager
Analyst
62,000.00
45,000.00
Select Statement Having Clause
cont’d
But what if you only wanted to display the
average if their salary was over 20000?
select quantity, avg(price)
from items_ordered
group by quantity
having avg(price) > 20;
Select Statement Having Clause
cont’d
Results
1 120.132500
2 45.799999
4 61.666667
Select Statement Order By

Order by is sorting



through a rule you create
Ascending
descending
Syntax:
Select "columnname", Sum("columnname2")
from "tablename"
order by "rule";
Select Statement Order By
Example:
Select first, last, id, age, state
from employee_info
where state = ‘Arizona’
order by last;
This statement will select 5 columns, where the
State is Arizona. It will sort them by last name in
ascending order.
If you wanted it to be descending, change the last
line to: order by last desc;
Select Statement Order By cont’d
Before
After
first
John
last
Jones
id
age city
99980 45 Payson
state
Arizona
first
Leroy
last
Brown
Mary
Jones
99982 25 Payson
Arizona
Elroy
Cleaver 32382 22 Globe
Eric
Edwards 88232 32 San Diego
California
Mary Ann Edwards 88233 32 Phoenix
Arizona
Arizona
Gus
Gray
22322 35 Bagdad
Arizona
Ginger
Howell
98002 42 Cottonwood
Arizona
John
Jones
99980 45 Payson
Arizona
Mary
Jones
99982 25 Payson
Arizona
Mary Ann May
32326 52 Tucson
Arizona
Arizona
Sebastian Smith
92001 23 Gila Bend
Arizona
Arizona
Erica
Mary Ann Edwards 88233 32 Phoenix
Ginger
Howell
98002 42 Cottonwood Arizona
Sebastian Smith
92001 23 Gila Bend
Arizona
Gus
Gray
22322 35 Bagdad
Arizona
Mary Ann May
32326 52 Tucson
Arizona
Erica
Williams 32327 60 Show Low Arizona
Leroy
Brown
Elroy
Cleaver 32382 22 Globe
32380 22 Pinetop
id
age city
32380 22 Pinetop
Williams 32327 60 Show Low
state
Arizona
Arizona
Arizona
Select Statements Combining
Conditions cont’d

The AND operator –



Used to join 2 conditions in the WHERE clause.
Both sides of the AND condition must be true for the
condition to be met and for those rows to be displayed
The OR operator –


Used to join two conditions in the WHERE clause.
Either side of the OR operator can be true and the
condition will be met or both sides can be true
Select Statements Combining
Conditions and Boolean Operations
Syntax:
Select "columnname", sum("columnname2")
from "tablename"
where "condition1" and "condition2";
Select Statements Combining
Conditions and Boolean Operations
example:
Select first, last, id, age, city, state
from employee_info
where (age >= 30) and (state = ‘Arizona');
Select Statements Combining
Conditions and Boolean Operations
Before
After
first
John
last
Jones
id
age city
99980 45 Payson
state
Arizona
first
last
id
John
Jones
99980 45 Payson
Mary
Jones
99982 25 Payson
Arizona
Eric
Edwards 88232 32 San Diego
Eric
Edwards 88232 32 San Diego
Mary Ann Edwards 88233 32 Phoenix
Mary Ann Edwards 88233 32 Phoenix
Ginger
Howell
California
Arizona
98002 42 Cottonwood Arizona
Sebastian Smith
92001 23 Gila Bend
Arizona
Gus
Gray
22322 35 Bagdad
Arizona
Mary Ann May
32326 52 Tucson
Arizona
Erica
Williams 32327 60 Show Low
Arizona
Leroy
Brown
32380 22 Pinetop
Arizona
Elroy
Cleaver
32382 22 Globe
Arizona
Ginger
Gus
Howell
Gray
Mary Ann May
Erica
age city
state
Arizona
California
Arizona
98002 42 Cottonwood Arizona
22322 35 Bagdad
Arizona
32326 52 Tucson
Williams 32327 60 Show Low
Arizona
Arizona
Select Statements Combining
Conditions and Boolean Operations
Example:
Select first, last, id, age, city, state
from employee_info
where (age >= 30) or (state = ‘Arizona');
Select Statements Combining
Conditions and Boolean Operations
Before
After
first
John
last
Jones
id
age city
99980 45 Payson
state
Arizona
first
John
last
Jones
id
age city
99980 45 Payson
state
Arizona
Mary
Jones
99982 25 Payson
Arizona
Mary
Jones
99982 25 Payson
Arizona
Eric
Edwards 88232 32 San Diego
California
Mary Ann Edwards 88233 32 Phoenix
Arizona
Arizona
Ginger
Mary Ann Edwards 88233 32 Phoenix
Ginger
Howell
Howell
98002 42 Cottonwood Arizona
98002 42 Cottonwood Arizona
Sebastian Smith
92001 23 Gila Bend
Arizona
Sebastian Smith
92001 23 Gila Bend
Arizona
Gus
Gray
22322 35 Bagdad
Arizona
Gus
Gray
22322 35 Bagdad
Arizona
Mary Ann May
32326 52 Tucson
Arizona
Mary Ann May
32326 52 Tucson
Arizona
Erica
Williams 32327 60 Show Low Arizona
Leroy
Brown
Elroy
Cleaver 32382 22 Globe
32380 22 Pinetop
Arizona
Arizona
Erica
Williams 32327 60 Show Low Arizona
Leroy
Brown
Elroy
Cleaver 32382 22 Globe
32380 22 Pinetop
Arizona
Arizona
Select Statement In and Between
Conditional Operators
The In Operator can be thought of as an or
Statement. This is why:
where lastname IN('Hernandez', 'Jones',
'Roberts', 'Ruiz');
is the same as
where lastname = (‘Hernandez’) or lastname =
(‘Jones’) or lastname = (‘Roberts’) or lastname =
‘Ruiz’);
You can also use not in to exclude people
Select Statement In and Between
Conditional Operators cont’d
The between operator is used to find something
within a range of numbers Here is an example:
Select employeeid, age, lastname, salary
from employee_info
where age Between 30 and 40;
This is the same as
where age >= 30 and age <= 40;
You can also use not between
Select Statements Mathematical
Functions
The Mathematical Operators are:
+
addition
-
subtraction
*
multiplication
/
division
%
modulos (remainder)
Select Statements Mathematical
Functions cont’d

ABS(x)
SIGN(x)

MOD(x,y)

FLOOR(x)

CEILING(x) or CIEL(x)

absolute value of x
-1, 0, 1, returns positive,
negative, zero
returns the remainder of
x/y (same as x%y)
returns the largest integer
less than or equal to x
returns the smallest
Integer that is >= x
Select Statements Mathematical
Functions cont’d

POWER(x,y)
ROUND(x)

ROUND(x,d)

SQRT(x)

raise x to the y power
returns x rounded to the
nearest integer
returns the value of x
rounded to the number
of decimal places specified
by d
returns the square-root
value of x
Select Statements Mathematical
Functions cont’d
Here is an example:
select round(salary), first, last
from employee;
This means that everyone’s salary will be
rounded to the nearest integer
Select Statements Mathematical
Functions cont’d
Before
first last
title
Jonie Weber Secretary
Potsy Weber Programmer
After
age salary
28 19,500.00
32 45,300.00
first last
salary
Jonie Weber 19,500.00
Potsy Weber 45,300.00
Dirk Smith Programmer II 45 75,020.00
John Doe Programmer III 58 100,000.00
Dirk Smith 75,020.00
John Doe 100,000.00
Joe Shmoe Manager
Jane Doe Analyst
Joe Shmoe 62,000.00
Jane Doe 45,000.00
36 62,000.00
22 45,000.00
Select Statement Table Joins
All the querying you have seen so far has
been for one table, Joins will break the
barriers holding you back
Joining allows you to relate multiple tables to
each other which is the reason the databases
you are using are called relational.
Select Statements Table Joins
cont’d
Here is the BASIC syntax for a join:
Select "columnname"
from "tablename", "tablename2"
where "condition"
Select Statements Table Joins
cont’d
Joins can be explained easier by demonstrating what
would happen if you worked with one table only,
and didn't have the ability to use "joins". This single
table database is also sometimes referred to as a "flat
table". Let's say you have a one-table database that is
used to keep track of all of your customers and what
they purchase from your store
Select Statements Table Joins
cont’d
Everytime a new row is inserted into the table, all
columns will be be updated, thus resulting in
unnecessary "redundant data". For example, every
time Wolfgang Schultz purchases something, the
following rows will be inserted into the table:
Select Statements Table Joins
cont’d
An ideal database would have two tables:
1) One for keeping track of your customers
2) And the other to keep track of what they
purchase:
"Customer_info" table:
customer_number|firstname|lastname|address|city|state|zip|
"Purchases" table:
customer_number|date|item|price|
Select Statements Table Joins
cont’d
Now, whenever a purchase is made from a
repeating customer, the 2nd table, "Purchases"
only needs to be updated! We've just
eliminated useless redundant data, that is,
we've just normalized this database!
Select Statements Table Joins
cont’d
Normalization - Data Normalization is a
database design technique that is used to
get the tables in your database into at least
the third normal form (3NF). Basically, this
means that you want to eliminate the
redundancy of non-key data when
constructing your tables. Each table should
only have columns that depend on the
primary key.
Select Statements Table Joins
cont’d
Notice how each of the tables have a common
"cusomer_number" column. This column, which contains the
unique customer number will be used to JOIN the two tables.
Using the two new tables, let's say you would like to select
the customer's name, and items they've purchased. Here is an
example of a join statement to accomplish this:
SELECT customer_info.firstname,
customer_info.lastname, purchases.item
FROM customer_info, purchases
WHERE customer_info.customer_number =
purchases.customer_number;
Select Statements Table Joins
cont’d
This particular "Join" is known as an "Inner Join" or
"Equijoin". This is the most common type of "Join" that
you will see or use.Notice that each of the colums are
always preceeded with the table name and a period. This
isn't always required, however, it IS good practice so
that you wont confuse which colums go with what
tables. It is required if the name column names are the
same between the two tables. I recommend preceeding
all of your columns with the table names when using
joins.
Select Statements Table Joins
cont’d
example:
SELECT employee_info.employeeid,
employee_info.lastname, employee_sales.comission
FROM employee_info, employee_sales
WHERE employee_info.employeeid =
employee_sales.employeeid;
This statement will select the employeeid, lastname (from
the employee_info table), and the comission value (from
the employee_sales table) for all of the rows where the
employeeid in the employee_info table matches the
employeeid in the employee_sales table.