Banner and the SQL Select Statement: Part Two Single Table Selects

About This Presentation

Title:

Banner and the SQL Select Statement: Part Two Single Table Selects

Description:

We might want to keep on specific rows from a table based on a condition ... would retrieve 'Ernie Jones' and 'Ernestine Smith' but NOT 'ERNIE Jones' or ' ... – PowerPoint PPT presentation

Number of Views:136

Avg rating:3.0/5.0

Slides: 92

Provided by: paws3

Learn more at: https://paws.wcu.edu

Category:

more less

Transcript and Presenter's Notes

Title: Banner and the SQL Select Statement: Part Two Single Table Selects

1
Banner and the SQL Select Statement Part Two
(Single Table Selects)

Mark Holliday
Department of Mathematics and
Computer Science
Western Carolina University
14 October, 28 October, and 4 November
(updated 4 November 2005)

2
Outline

The Goal
The Concepts
A First Example
Single Table Selects
Joins
Multiple Connected Select Statements

3
A First Example

Outline
The Relational Model Single Table
Lab 1 TOAD, Schema Browser
Some Structured Query Language (SQL) Basics
Lab 2 TOAD, SQL Editor

4
Single Table Selects

Outline
WHERE clause single condition, multiple
conditions
Lab 3
Aliases Order By Aggregate Functions
Lab 4
Group By Having
Lab 5

5
WHERE Clause

We might want to keep on specific rows from a
table based on a condition gt where clause
SELECT "column_name" FROM "table_name" WHERE
"condition"
We then project onto the columns of interest gt
select clause

6
WHERE Clause
(franz)

This condition can be a simple condition, or it
can be a compound condition.
Compound conditions are made up of multiple
simple conditions connected by AND or OR.
There is no limit to the number of simple
conditions that can be present in a single SQL
statement.
SELECT "column_name" FROM "table_name" WHERE
"simple condition" ANDOR "simple
condition"

7
WHERE Clause

Each simple condition must evaluate to the value
True or the value False.
Operators within a simple condition?
arithmetic comparison operators
is, is not
in, between
like

8
Arithmetic Comparison Operators (franz)

WHERE age gt 18

9
A Single Condition Example

English Query
Suppose we were going to offer a new scholarship
from Ben Jerrys Ice Cream Company.
The eligible students are listed in the spriden
table.
Eligible student need to have a first name of
Arnold.
We want to find the first and last names of the
eligible students.
Solution?

10
A Single Condition Example

SELECT spriden_last_name, spriden_first_name
FROM spriden
WHERE spriden_first name Arnold

11
A Single Condition Example

How do we remember the column names in the
spriden table?
They are in the Columns tab of the Schema Browser
open Schema Brower,
make Saturn the owner,
select the SPRIDEN table,
select the Columns tab (it is the default)

12
A Single Condition Example

How do we remember the column names in the
spriden table?
Shortcut
in the sql statement in the SQL editor select the
table name, spriden,
right-click to open a menu
select the Describe menu item (near the bottom)
the Columns tab from the Schema Browser for that
table appears

13
SPRIDEN_CHANGE_IND is NULL

Problem
In spriden the spriden_pidm column is not a key
gt
need not determine a unique row
Why?
PIDM refers to a particular person, but a person
may have several spriden rows.

14
SPRIDEN_CHANGE_IND is NULL

Why?
The information about a person changes (e.g.
address) but we want to keep the old information
gt the person will have multiple spriden rows.

15
SPRIDEN_CHANGE_IND is NULL

Question Of all the spriden rows for a
particular person (all have the same PIDM) how do
we find the row with the persons current
information?
Answer That row has the null value in the change
indicator field, spriden_change_ind

16
SPRIDEN_CHANGE_IND is NULL

Question What is the NULL value?
Answer
Every column has a data type (e.g. INTEGER,
VARCHAR)
What if the column entry is currently empty?
We cant use any value in the data type.
Solution Use a special value called NULL.

17
SPRIDEN_CHANGE_IND is NULL

Question How do you check for the NULL value?
Answer
Use the IS operator or the IS NOT operator.
where spriden_change_ind is null
Can not use the or ltgt operators

18
A Two Condition Example

Problem
For a Single Condition Example above we really
need two conditions
spriden_change_ind is null
spriden_first_name Arnold
and both conditions must be true

19
Truth Table for AND logical operator

20
Truth Table for OR logical operator

21
A Two Condition Example

SELECT spriden_last_name, spriden_first_name
FROM spriden
WHERE
(spriden_change_ind is null)
and
(spriden_first name Arnold)

22
A Second Equivalent Two Condition Example

SELECT spriden_last_name, spriden_first_name
FROM spriden
WHERE spriden_change_ind is null
and spriden_first name Arnold

23
A Three Condition Example
(franz)

Suppose we were going to offer a new scholarship
from Ben Jerrys Ice Cream Company.
In order to apply eligible students need to have
a first name of Arnold or
or a first name of Arturo and
have active records (that is change indicator
must be null)
Solution?

24
A Three Condition Example
(franz)

SELECT spriden_last_name, spriden_first_name
FROM spriden
WHERE spriden_change_ind is null and
(spriden_first name Arnold or
spriden_first_name Arturo)
Note that the first name can be Arnold OR Arturo.
But, in either case, the change indicator must
be null.

25
The Importance of Parentheses

SELECT spriden_last_name, spriden_first_name
FROM spriden
WHERE (spriden_change_ind is null and
spriden_first name Arnold) or
spriden_first_name Arturo
How is this query different?

26
The Importance of Parentheses

SELECT spriden_last_name, spriden_first_name
FROM spriden
WHERE (spriden_change_ind is null and
spriden_first name Arnold) or
spriden_first_name Arturo
Like the previous query this query includes
students who have a first name of Arnold and a
spriden_change_ind value of null.
However this query includes all students who have
a first name of Arturo even those who have
spriden_change_ind not equal to null.

27
The Importance of Parentheses

SELECT spriden_last_name, spriden_first_name
FROM spriden
WHERE spriden_change_ind is null and
spriden_first name Arnold or
spriden_first_name Arturo
To which of the statements before is this one
equivalent?

28
The Importance of Parentheses

AND is of higher precedence than OR
the query is equivalent to
SELECT spriden_last_name, spriden_first_name
FROM spriden
WHERE (spriden_change_ind is null and
spriden_first name Arnold) or
spriden_first_name Arturo
Morale
Use parentheses for readability and to avoid
surprises

29
The Importance of Parentheses

SELECT spriden_last_name, spriden_first_name
FROM spriden
WHERE spriden_first name Arnold
or spriden_first_name Arturo
and spriden_change_ind is null
To which of the statements before is this one
equivalent?

30
The Importance of Parentheses

Answer None! Because AND is higher precedence
than OR, the query is equivalent to
SELECT spriden_last_name, spriden_first_name
FROM spriden
WHERE spriden_first name Arnold
or (spriden_first_name Arturo
and spriden_change_ind is null)

31
A Proposed Query

SELECT spriden_last_name, spriden_first_name
FROM spriden
WHERE spriden_change_ind is null and
(spriden_first name Arnold
or Arturo)
What will this query do?

32
A Proposed Query

Query will generate an error since
one operand to the OR operator is not a boolean
value (that is, True or False)
which one? Arturo
Morale SQL is pickier than English sometimes.

33
IN Operator
(franz)

SELECT "column_name" FROM "table_name" WHERE
"column_name" IN ('value1', 'value2', ...)
In SQL, there are two uses of the IN keyword, and
this section introduces the one that is related
to the WHERE clause.
When used in this context, we know exactly the
value of the returned values we want to see for
at least one of the columns.

34
IN Operator
(franz)

The number of values in the parenthesis can be
one or more, with each value separated by comma.
Values can be numerical or characters.
If there is only one value inside the
parenthesis, this command is equivalent to
WHERE "column_name" 'value1'
Character values should be enclosed in single
quotes.
Numeric values would not be enclosed in quotes.

35
An Example of the IN Operator
(franz)

The example below would select valid states
codes and descriptions from the valid state table
that are either of the values NC or SC.

36
An Example of the IN Operator (cont.)
(franz)

SELECT stvstat_code, stvstat_desc
FROM stvstat
WHERE stvstat_code IN (NC, SC)

37
BETWEEN Operator
(franz)

SELECT "column_name" FROM "table_name" WHERE
"column_name" BETWEEN 'value1 AND 'value2'
Whereas the IN keyword helps people limit the
selection criteria to one or more discrete
values, the BETWEEN keyword allows for selecting
a range.

38
An Example of the Between Operator

(franz)

Perhaps you wanted to know the names of the
first 100 names loaded onto the spriden table.
(As they were loaded, the PIDM was assigned as a
sequentially incremented integer.)
This example pulls all pidms, last and first
names, and change indicators, from spriden for
PIDMs between 0 and 100.

39
An Example of the Between Operator

(cont.)(franz)

SELECT spriden_pidm, spriden_last_name,
spriden_first_name, spriden_change_ind
FROM spriden
WHERE spriden_pidm BETWEEN 0 and 100

40
LIKE

SELECT "column_name" FROM "table_name" WHERE
"column_name" LIKE PATTERN
LIKE is another keyword that is used in the
WHERE clause. We previously saw an example where
we pulled name information like Er in the
first name field.
Basically, LIKE allows you to do a search based
on a pattern, rather than specifying exactly what
is desired (as when using the IN syntax) or spell
out a range (as in BETWEEN).

41
The syntax for LIKE is
(franz)

LIKE PATTERN ? where PATTERN often consists
of wildcards
Here are some examples

42
Examples of the LIKE Operator
(franz)

'A_Z' All strings that starts with 'A', contains
one other character, and ends with 'Z'.
For example, 'ABZ' and 'A2Z' would both satisfy
the condition, while 'AKKZ' would not (because
there are two characters between A and Z instead
of one).
'ABC' All strings that start with 'ABC'.
For example, 'ABCD' and 'ABCABC' would both
satisfy the condition.
'XYZ' All strings that end with 'XYZ'.
For example, 'WXYZ' and 'ZZXYZ' would both
satisfy the condition.
'AN' All string that contain the pattern 'AN'
anywhere.
For example, 'LOS ANGELES' and 'SAN FRANCISCO'
would both satisfy the condition.

43
A first LIKE Example (franz)

SELECT FROM spridenWHERE spriden_first_name
like Er
Strings must be in single quotes. Also, please
note Banner name fields are CASE sensitive. (Last
and first names are mixed case.)
This example would retrieve Ernie Jones and
Ernestine Smith but NOT ERNIE Jones or
ERNESTINE Smith (which are capitalized).

44
A Second LIKE Example (franz)

SELECT
FROM spriden
WHERE
spriden_change_ind is null
and spriden_last_name like Smith

45
Laboratory Three

Objectives
Develop competence with the WHERE clause in
single table select statements
Steps
First query
Second query
Third query

46
Laboratory Three

First Query
Find the pidms and area codes of all the people
who
have area codes that are 608 or 414.
Hint use the sprtele table.
Do in two different ways.

47
Laboratory Three

First Query Solution (First Way)
select sprtele_pidm, sprtele_area_code
from sprtele
where sprtele_area_code in (608, 414)

48
Laboratory Three

First Query Solution (Second Way)
select sprtele_pidm, sprtele_area_code
from sprtele
where (sprtele_area_code 608
or (sprtele_area_code 414)

49
Laboratory Three

Second Query
Find the pidms and area codes of all the people
who
have area codes that are 608 or 414
and the local phone number ends with a 2.
Do in two different ways.

50
Laboratory Three

Second Query Solution (First Way)
select sprtele_pidm, sprtele_area_code,
sprtele_phone_number
from sprtele
where sprtele_area_code in (608, 414)
and sprtele_phone_number like 2

51
Laboratory Three

Second Query Solution (Second Way First
Equivalent)
select sprtele_pidm, sprtele_area_code,
sprtele_phone_number
from sprtele
where ((sprtele_area_code 608)
or (sprtele_area_code 414))
and (sprtele_phone_number like 2)

52
Laboratory Three

Second Query Solution (Second Way Second
Equivalent)
select sprtele_pidm, sprtele_area_code,
sprtele_phone_number
from sprtele
where (sprtele_area_code 608
or sprtele_area_code 414)
and sprtele_phone_number like 2

53
Laboratory Three

Second Query Solution (Second Way Third
Equivalent)
select sprtele_pidm, sprtele_area_code,
sprtele_phone_number
from sprtele
where sprtele_area_code 608
or sprtele_area_code 414
and sprtele_phone_number like 2

54
Laboratory Three

Is this statement equivalent to the second way?
select sprtele_pidm, sprtele_area_code,
sprtele_phone_number
from sprtele
where sprtele_area_code 608
and sprtele_phone_number like 2
or sprtele_area_code 414

55
Laboratory Three

No. It is equivalent to
select sprtele_pidm, sprtele_area_code,
sprtele_phone_number
from sprtele
where (sprtele_area_code 608
and sprtele_phone_number like 2)
or sprtele_area_code 414

56
Laboratory Three

Third Query
Find the pidms and area codes of all the people
who
have area codes that greater than or equal to 600
and less than or equal to 700.

57
Laboratory Three

Third Query Solution
select sprtele_pidm, sprtele_area_code,
from sprtele
where sprtele_area_code between 600 and 700

58
Aliases
(franz)

There are two types of aliases used most
frequently
Column alias
Table alias

59
COLUMN Alias
(franz)

SUM(SALES)/12 monthly_sales
In this example, the total sales were divided by
12 to derive the monthly sales amount. The alias
monthly_sales is understandable and could be
easily referenced.

60
TABLE Alias
(franz)

The table alias is placed directly after the
table name in the FROM clause.
This is convenient when you want to obtain
information from two separate tables (the
technical term is 'perform joins').
The advantage of using a table alias when
performing joins is readily apparent when we talk
about joins later.

61
TABLE Alias
(franz)

Before we get into joins, though, let's look at
the syntax for both the column and table aliases
SELECT "table_alias"."column_name1"
"column_alias" FROM "table_name" "table_alias

62
An Alias Example (franz)

Using an example from spriden, if we were
creating a quick ad-hoc query that would only run
this one time, to select last names starting with
S, J, or F, we might save ourselves some
typing time by using the alias syntax as follows
SELECT s.spriden_last_name ln
FROM spriden s
WHERE ln like S or
ln like J or
ln like F

63
ORDER BY Clause
(franz)

SELECT "column_name" FROM "table_name" WHERE
"condition"ORDER BY "column_name" ASC, DESC
The means that the WHERE statement is
optional. However, if a WHERE clause exists, it
comes before the ORDER BY clause.
ASC means that the results will be shown in
ascending order, and DESC means that the results
will be shown in descending order. If neither is
specified, the default is ASC.

64
ORDER BY Clause
(franz)

SELECT "column_name" FROM "table_name" WHERE
"condition"ORDER BY "column_name" ASC, DESC
It is possible to order by more than one column.
In this case, the ORDER BY clause above becomes
ORDER BY "column_name1" ASC, DESC,
"column_name2" ASC, DESC

65
AGGREGATE FUNCTIONS
(franz)

Aggregate functions allow you to create a single
value from the rows in a result set.
Arithmetic functions
AVG
COUNT
MAX
MIN
SUM

66
AGGREGATE FUNCTIONS (franz)

An example aggregate function syntax using the
COUNT function is
SELECT COUNT("column_name") FROM "table_name
COUNT and DISTINCT can be used together in a
statement to fetch the number of distinct entries
in a table.

67
AGGREGATE FUNCTIONS (franz)

For example, if we want to find out the number
of distinct address types for Banner, we'd type
SELECT COUNT(DISTINCT stvatyp_code) FROM
stvatyp
The result would look like this

68
AGGREGATE FUNCTIONS

Warning!
if the SELECT clause uses an aggregate function,
then
it must use only aggregate functions
unless the query has a GROUP BY clause
Why?
Aggregate function gt single value
other operands in a SELECT clause (e.g. columns)
may have multiple values
gt conflict

69
Laboratory Four

Objectives
Develop competence with aggregate functions
Steps
First Query
Second Query
Third Query

70
Laboratory Four

First Query
Find the largest pidm in spriden for someone
whose currently active and whose first name is
Mark.
Label that column Largest.

71
Laboratory Four

First Query Solution
select max(spriden_pidm) as Largest
from spriden
where spriden_change_ind is null
and spriden_first_name Mark

72
Laboratory Four

Second Query
Find how many telephone numbers have a 919 area
code.
Label that column 919 Numbers.
Use the sprtele table.

73
Laboratory Four

Second Query Solution
select count() as 919 Numbers
from sprtele
where sprtele_area_code 919

74
Laboratory Four

Third Query
Find how many telephone number have a 919 area
code.
Label that column 919 numbers.
In the same query, find the smallest pidm of a
person with a 919 area code.
Label that column Smallest.
Hint use the sprtele table.

75
Laboratory Four

Third Query Solution
select
count() as 919 Numbers,
min(sprtele_pidm) Smallest
from sprtele
where sprtele_area_code 919

76
GROUP BY Clause
(franz)

SELECT "column_name1", SUM("column_name2")
FROM "table_name" GROUP BY "column_name1"
The GROUP BY keyword is used when we are
selecting multiple columns from a table (or
tables) and at least one arithmetic operator
appears in the SELECT statement.
When that happens, we need to GROUP BY all the
other selected columns -- i.e., all columns
except the one(s) operated on by the arithmetic
operator.

77
Task calculate the number of people in our
records by gender and hair code (franz)

SELECT spbpers_sex, spbpers_hair_code, COUNT()
FROM spbpers
GROUP BY spbpers_sex, spbpers_hair_code

78
The HAVING Clause
(franz)

SELECT "column_name1", AGGREGATE_FNC("column_nam
e2") FROM "table_name" WHERE conditions
GROUP BY "column_name1" HAVING (arithmetic
function condition)

79
The HAVING Clause

This is complicated!
How do we keep it all straight?
Answer SQL is said to be non-procedural, but
the order of execution of the clauses is
procedural
the order of execution of the expressions within
a clause (e.g. WHERE and HAVING) is non-procedural

80
The HAVING Clause

5) SELECT "column_name1", AGGREGATE_FNC("column_
name2") 1) FROM "table_name" 2) WHERE
conditions
3) GROUP BY "column_name1" 4) HAVING
(arithmetic function condition)
Order of execution of the clauses

81
The HAVING Clause

Step 2)The WHERE clause elimination of rows is
done on the individual rows BEFORE the GROUP BY
and HAVING clauses.
Step 3) The grouping of the remaining rows into
groups by the GROUP BY clause is done before the
HAVING clause.

82
The HAVING Clause

Step 4) The HAVING clause eliminates groups that
do not meet the HAVING conditions.
Step 5) The projection onto columns and
application of aggregate functions in the SELECT
clause is done last.

83
Task For each state count the records in the
address table where the address type is MA
(home/permanent mailing). Display the counts
only for the states that have ten or more such
addresses.