1113 PL SQL Banner
1113 PL SQL Banner
1113 PL SQL Banner
com
Introduction to SQL
Presented by: Jennifer Flagel
George Mason University
March 20, 2007
Course ID 1113
A Community of Learning
Introduction
SQL = Structured Query Language
Communicate the benefits of learning SQL
Provide a basic understanding of relational database
structures
Translate Banner application data to its underlying
Oracle source
Introduce basic SQL commands for querying Banner
data
Use the Microsoft Access SQL editor to perform
simple queries against sample General Person data
Course ID 1113
Benefits of SQL
A Community of Learning
Course ID 1113
Relational Database
Structure
A Community of Learning
Course ID 1113
Course ID 1113
SPRADDR
SPRADDR_PIDM
SPRADDR_STREET_LINE1
SPRADDR_CITY
SPRADDR_STAT_CODE
SPRADDR_ZIP
Course ID 1113
Course ID 1113
Translating Between
Banner and Oracle
A Community of Learning
10
Banner Schemas
Banner data is grouped by process area, similar to the
Banner menu structure
Tables are grouped by owner, or schema
Course ID 1113
11
Course ID 1113
12
SPAIDEN ID = SPRIDEN
SPAIDEN Address = SPRADDR
SPAIDEN Telephone = SPRTELE
SPAIDEN Biographical = SPBPERS
Course ID 1113
13
Course ID 1113
14
A Community of Learning
15
SQL Syntax
ANSI approved standard
Proprietary variations
Must support standard keywords
Oracle uses SQL*Plus
Course ID 1113
16
Common Statements
DESC table
Produces list of columns and properties
SELECT *
Selects all columns from specified table
Course ID 1113
17
= equal to
<> not equal to
< less than
> greater than
Course ID 1113
18
19
Joins
Joins are used to select data from multiple tables.
Each join connects two related tables.
Inner join (also called natural join) selects only those
rows where the shared attribute exists in both tables.
Outer join selects all rows from one table regardless of
whether the shared attribute exists in the other.
Left outer join selects all rows from first table regardless of
match in second table.
Right outer join selects all rows from second table
regardless of match in first table.
20
Course ID 1113
21
Course ID 1113
22
Course ID 1113
23
Course ID 1113
24
Subqueries
Produce subset of data for reference in another query
Can be used as table in FROM
SELECT column
FROM
(SELECT column
FROM table)
WHERE condition;
Course ID 1113
25
Course ID 1113
26
Course ID 1113
27
Aggregate Functions
Perform mathematical summaries over a group of rows
MIN
MAX
COUNT
AVG
SUM
Course ID 1113
28
Practice 4: Aggregation
Select count of persons by ethnicity and gender
SELECT spbpers_ethn_code, spbpers_sex,
COUNT(spriden_pidm)
FROM spriden
LEFT JOIN spbpers
ON spriden_pidm = spbpers_pidm
WHERE spriden_entity_ind = P
AND spriden_change_ind IS NULL
GROUP BY spbpers_ethn_code, spbpers_sex;
Course ID 1113
29
Advanced Options
Aliases
Concatenation
Literals
Substitution Variables
Calculations
Set Operators
UNION
INTERSECT
MINUS
Character Strings
Substring
Instring
Course ID 1113
Formats
UPPER
LOWER
NVL
CASE
DECODE
ROUND
TRUNC
30
31
Course ID 1113
32
Course ID 1113
33
Course ID 1113
34
Exercises
Use Microsoft Access SQL editor to practice writing
queries against the sample tables
Course ID 1113
35
Learning More
Training
SQL*Plus user manuals
Online resources
View generated SQL
Course ID 1113
36
Course ID 1113
37
Thank You!
Jennifer Flagel
jflagel@gmu.edu
SunGard, the SunGard logo, Banner, Campus Pipeline, Luminis, PowerCAMPUS, Matrix, and Plus are trademarks or registered
trademarks of SunGard Data Systems Inc. or its subsidiaries in the U.S. and other countries. Third-party names and marks
referenced herein are trademarks or registered trademarks of their respective owners.
2006 SunGard. All rights reserved.
Course ID 1113
38