Transcript An Introduction to the Relational Model and to the

Banner and the SQL Select
Statement:
Part One (A First Example)
Mark Holliday
Department of Mathematics and
Computer Science
Western Carolina University
30 September 2005 and 7 October 2005
(updated: 2 November 2005)
Acknowledgements
Thanks to Lynn Franz and Stan
Hammer for their suggestions,
slides, handouts, and time.
Thanks to Larry Hammer for
suggesting this project.
Presentation Slides
http://cs.wcu.edu/~holliday/LectureNotes/
Banner/BannerSqlSelectPart1.ppt
Outline


The Goal
The Concepts




A First Example
Single Table Selects
Joins
Multiple Connected Select Statements
The Goal


Generate a report that answers a
question about the data in Banner.
Banner => Query => Report
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
Single Table Selects

Outline






WHERE clause: single condition, multiple
conditions
Lab 3:
Order By; Aggregate Functions
Lab 4:
Group By; Having
Lab 5:
Joins

Outline





Why multiple tables?
Inner Joins
Lab 6:
Outer Joins
Lab 7:
Multiple Connected Select Statements

Outline



Set Operators
Lab 8:
Subqueries





Use directly: FROM clause
Use as a set: new operators
Use as a single value: aggregate functions
Lab 9:
A Query Development Methodology
The Relational Model: Single Table



Banner is a database application
running on Oracle.
Oracle is a Relational Database
Management System (RDBMS)
A RDBMS implements the relational
model.
RDBMS Flavors???
Ingres,
Postgres
(Franz/Hammer/Holliday)
DB2,
Informix
MS Access
ANSI SQL/92
Microsoft
SQL Server
MySQL
Oracle
Relational Database Model
(Hammer)
Relational comes from set theory’s relation
EMPLOYEE TABLE
ID
First
Name
Last
Name
Phone
1
Stan
Hammer
2929
2
Ernie
Jamison
2127
3
Brian
Chamberlin
3210
Sets are called tables (or
relations) and table elements
are rows; rows consists of
columns (or attributes)
Set
of
Employees
i.e. all rows in
Employee
table
A sample table structure:
(Franz)
Constraints
(Franz)
What are (column) constraints?
A constraint is basically a rule associated
with a column that the data entered into
that column must follow.
When tables are created, it is common for
one or more columns to have constraints
associated with them.
Various constraints can be placed upon the
individual columns.
Constraints
(Franz)
The three most common constraints are:
•
•
•
"unique" -- specifies that no two records
can have the same value in a particular
column; they must all be unique
"not null" -- specifies that a column can't
be left blank
"primary key" -- defines a unique
identification of each record (or row) in a
table
Some Banner Tables
(Franz)
People
(students, staff, faculty)
SPRIDEN
Unique identifier - PIDM
Addresses
Personal Info
Telephone Info
SPRADDR
SPBPERS
SPRTELE
Advantages of relational databases are:
(Franz/Hammer)

Built-in multilevel integrity




Duplicates are removed
Primary keys are enforced
Valid relationships between tables
Logical and Physical data independence from
the database applications
Changes to logical database design and/or
changes by vendor to the database’s physical
implementation do NOT adversely affect
applications built upon it
Advantages of relational databases (cont.)
(Franz/Hammer)

Data consistency and accuracy
You can impose various levels of
integrity within the database

Easy data retrieval
Information can be viewed in an almost
unlimited number of ways [either from
one table or multiple related tables]
Laboratory One


Objectives:
 See TOAD

(Tool for Oracle Application
Development)
 Schema Browser
Steps
 Starting Toad
 Overall Appearance
 Using the Schema Browser
Laboratory One: Starting Toad


Desktop Icon or
Start, Programs, Quest Software, TOAD

Useful Documents:




Toad User’s Guide
Toad Getting Started Guide
shortcuts not currently working
Connect to the Banner database


TRNG data set
TRAINXX username
Laboratory One: Starting Toad


Connect to Banner RDBMS

TRNG database

TRAINXX username
Once in Banner do


File/End Connection
File/Start Connection
Laboratory One: Appearance



Your new home!
Lots of stuff!
Three key tools are opened by default

SQL Editor (top window in middle)


SQL Modeler (underneath window in middle)



discussed in the later laboratories
used to graphically create a SQL statement
not discussed further
Schema Browser (right side)
Laboratory One: Appearance


Minimize or close the SQL Editor and SQL
Modeler
Maximize the Schema Browser



two panes appear
two text fields above the left pane
Top left text field specifies the table owner


defaults to TRAINxx
no tables shown
Laboratory One: Schema Browser

Change table owner to SATURN




main table owner in Banner
many tables are now listed in the left pane in
alphabetical order
Second text field is used to narrow the list of
tables shown in the left pane
* is the wildcard that matches any string

=> all tables listed
Laboratory One: Schema Browser

Change the second text field to S*


the left pane now only lists the tables owned by
SATURN that start with a S character
In the left pane select the SPRIDEN table


the right pane now shows information about this
table
many tabs (focus on just a few)
Laboratory One: Schema Browser

Default right pane tab: Columns





shows the table structure
row for each column in the table
column name, data type, whether can be null, …
Data tab: the data (row values) in the current
instance of the SPRIDEN table
Minimize or close the schema browser
Laboratory One: SQL Editor




Maximize the SQL Editor window
Alternatively, open from the Database tab
along the top of the Toad window
top pane is where you enter a SQL statement
press the Execute Current Stmt icon


leftmost icon in toolbar (looks like a page)
The result set (table) appears in the bottom
pane
WHAT IS SQL?
(Hammer)
Structured Query Language to
create and manipulate data
databases
Query language of relational
 Pronounced: “es queue el”
 Commonly: “sequel”
 Ubiquitous, de facto standard
SQL




(Hammer)
Set-based language
Can operate on an entire table, or multiple
tables all at once
Declarative Language (non-procedural);
express what you want without details about
where or how data is located
SQL is Not a procedural language

Use PL-SQL or Visual Basic w/SQL
A Procedural Language is a computer language where the
programmer specifies an explicit sequences of steps to follow to
produce a result
Using SQL

Stand-alone SQL statements



issue explicitly
report generation ---- US!
Embedded within a Host Language



needed sometimes (cursors)
Oracle: PL/SQL
Java/JDBC (Java DataBase Connectivity)
SQL Statements: Sublanguages
(Franz)


DDL ( Data Definition Language )
1. create and destroy databases
2. create and destroy database objects
(tables)
3. alter tables
DML ( Data Manipulation Language )

--- US!
DDL (Data Definition Language)
(Franz/Holliday)
CREATE DATABASE Banner
This statement creates an empty database named
"employees" on your DBMS (DataBase Management
System).
CREATE TABLE spriden
(spriden_first_name varchar2(15),
spriden_last_name varchar2(60)
spriden_pidm
number(8, 0)
not null,
not null)
Data Types
char(size)
varchar(size)
number(size)
date
number(size,d)
(Franz/Hammer)
Fixed-length character string.
Size is specified in
parenthesis. Max 255 bytes.
Variable-length character
string. Max size is specified in
parenthesis.
Number value with a max
number of column digits
specified in parenthesis.
Date value
Number value with a
maximum number of digits of
"size" total, with a maximum
number of "d" digits to the
right of the decimal.
DDL
continued…..

DROP TABLE spriden

DROP DATABASE banner
(Franz/Holliday)
These last two commands are
dangerous!
(You will not be using any of these
commands in Banner except possibly
with views which will be discussed
much later)
Some SQL Basics

1.
DDL ( Data Definition Language )
DML ( Data Manipulation Language )
 types of updates




insert
delete
modify
retrieve (read-only; select statement)

-- US!
DML (Data Manipulation Language)
INSERT
INTO spriden
(spriden_pidm, spriden_first_name,
sprident_last_name, spriden_mi)
values (12345678, ‘john’, ‘smith’, null)
DML (Data Manipulation Language)
DELETE
FROM spriden S
WHERE S.spriden_last_name = ‘Jones’
UPDATE spriden S
SET S.spriden_last_name = ‘Thompson’
WHERE S.spriden_pidm = 12345678
DML: SELECT Statement
(Franz)
SELECT [ALL | DISTINCT]
column1[,column2]
FROM table1[,table2]
[WHERE "conditions"]
[GROUP BY "column-list"]
[HAVING "conditions”]
[ORDER BY "column-list" [ASC | DESC] ]
SELECT [ALL | DISTINCT]
SELECT spriden_last_name
FROM spriden
This statement would
retrieve all the last names in
spriden.
(franz)
spriden_last_name
Jones
Smith
Smith
Smith
Thompson
If ‘distinct’ was omitted,
(i.e., ALL was the default)
these records might appear:
Wilson
Wilson
spriden_last_name
SELECT DISTINCT
spriden_last_name
FROM spriden
Jones
Smith
Conversely, if ‘distinct’ was
specifically used, duplicate
records would disappear:
Thompson
Wilson
SELECT *
(franz)
Asterisk is used as a wildcard.
The statement below will select all columns and rows in our table.
SELECT *
FROM stvethn
Below is a subset of the data returned from this query:
STVETHN_CODE
STVETHN_DESC
…
W
White, Not of Hispanic Origin
…
I
Amer Indian/Alaskan Native
…
…
O
Asian or Pacific Islander
…
SELECT Statement
(franz)
SELECT "column_name" FROM "table_name"
As we saw previously, the SELECT keyword allows us to
grab all information from a column (or columns) on a
table. Depending upon how the data is structured, there
may be redundancies. To select each DISTINCT
element, we add DISTINCT after SELECT.
SELECT DISTINCT "column_name"
FROM "table_name"
Laboratory Two: Simple Select

Objectives:


Lean to use the TOAD SQL Editor on a simple
select statement
Steps
 Starting the SQL Editor
 Six example select statements
Laboratory Two: SQL Editor




Maximize the SQL Editor window
Alternatively, open from the Database tab
along the top of the Toad window
top pane is where you enter a SQL statement
press the Execute Current Stmt icon


leftmost icon in toolbar (looks like a page)
The result set (table) appears in the bottom
pane
Laboratory Two: SQL Editor




English Query:
 “Find the last names of all people.”
Enter in top pane
Press the Execute One Stmt icon
Observe result in bottom pane
Laboratory Two: SQL Editor
SQL Query (first version):
select saturn.spriden.spriden_last_name
from saturn.spriden;

Laboratory Two: SQL Editor

Second version:
select spriden.spriden_last_name
from spriden;

Third version:
select s.spriden_last_name
from spriden s;
Laboratory Two: SQL Editor

fourth version:
select spriden_last_name
from spriden;

a related, but different, query:
select distinct spriden_last_name
from spriden;
Laboratory Two: SQL Editor

Another example sql statement:
select * from stvethn;
Single Table Selects

Outline






WHERE clause: single condition, multiple
conditions
Lab 3:
Aliases; Order By; Aggregate Functions
Lab 4:
Group By; Having
Lab 5:
Joins

Outline





Why multiple tables?
Inner Joins
Lab 6:
Outer Joins
Lab 7:
Multiple Connected Select Statements

Outline



Set Operators
Lab 7:
Subqueries




Use directly: FROM clause
Use as a set: new operators
Use as a single value: aggregate functions
Lab 8: