Banner and the SQL Select Statement: Part One (A First Example)

1
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)

2
Acknowledgements

• Thanks to Lynn Franz and Stan Hammer for their
  suggestions, slides, handouts, and time.
• Thanks to Larry Hammer for suggesting this
  project.

3
Presentation Slides

• http//cs.wcu.edu/holliday/LectureNotes/
• Banner/BannerSqlSelectPart1.ppt

4
Outline

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

5
The Goal

• Generate a report that answers a question about
  the data in Banner.
• Banner gt Query gt Report

6
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

7
Single Table Selects

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

8
Joins

• Outline
• Why multiple tables?
• Inner Joins
• Lab 6
• Outer Joins
• Lab 7

9
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

10
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.

11
RDBMS Flavors??? (Franz/Hammer/Holliday)

• Ingres,
• Postgres

DB2, Informix
MS Access
Microsoft SQL Server
Oracle
MySQL
12
Relational Database Model (Hammer)

• Relational comes from set theorys relation

EMPLOYEE TABLE
Sets are called tables (or relations) and table
elements are rows rows consists of columns (or
attributes)
i.e. all rows in Employee table
13
A sample table structure
(Franz)
14
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.

15
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

16
Some Banner Tables (Franz)
People (students, staff, faculty) SPRIDEN Unique
identifier - PIDM
Telephone Info SPRTELE
Personal Info SPBPERS
Addresses SPRADDR
17
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 databases physical
  implementation do NOT adversely affect
  applications built upon it

18
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

19
Laboratory One

• Objectives
• See TOAD
• (Tool for Oracle Application Development)
• Schema Browser
• Steps
• Starting Toad
• Overall Appearance
• Using the Schema Browser

20
Laboratory One Starting Toad

• Desktop Icon or
• Start, Programs, Quest Software, TOAD
• Useful Documents
• Toad Users Guide
• Toad Getting Started Guide
• shortcuts not currently working
• Connect to the Banner database
• TRNG data set
• TRAINXX username

21
Laboratory One Starting Toad

• Connect to Banner RDBMS
• TRNG database
• TRAINXX username
• Once in Banner do
• File/End Connection
• File/Start Connection

22
Laboratory One Appearance

• Your new home!
• Lots of stuff!
• Three key tools are opened by default
• SQL Editor (top window in middle)
• discussed in the later laboratories
• SQL Modeler (underneath window in middle)
• used to graphically create a SQL statement
• not discussed further
• Schema Browser (right side)

23
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

24
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
• gt all tables listed

25
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)

26
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

27
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

28
WHAT IS SQL? (Hammer)

• Structured Query Language to create and
  manipulate data

• Query language of relational databases
• Pronounced es queue el
• Commonly sequel
• Ubiquitous, de facto standard

29
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
30
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)

31
SQL Statements Sublanguages (Franz)

• DDL ( Data Definition Language )
• create and destroy databases
• create and destroy database objects (tables)
• alter tables
• DML ( Data Manipulation Language )
• ?--- US!

32
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) not null,
• spriden_pidm number(8, 0) not null)

33
Data Types (Franz/Hammer)
34
DDL continued.. (Franz/Holliday)

• DROP TABLE spriden
• DROP DATABASE banner
• 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)

35
Some SQL Basics

• DDL ( Data Definition Language )
• DML ( Data Manipulation Language )
• types of updates
• insert
• delete
• modify
• retrieve (read-only select statement)
• ?-- US!

36
DML (Data Manipulation Language)

• INSERT
• INTO spriden(spriden_pidm, spriden_first_name,
  sprident_last_name, spriden_mi)
• values (12345678, john, smith, null)

37
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

38
DML SELECT Statement (Franz)

• SELECT ALL DISTINCT column1,column2FROM
  table1,table2WHERE "conditions"GROUP BY
  "column-list"HAVING "conditionsORDER BY
  "column-list" ASC DESC

39
SELECT ALL DISTINCT (franz)

• SELECT spriden_last_name FROM spriden
• This statement would retrieve all the last names
  in spriden.
• If distinct was omitted, (i.e., ALL was the
  default) these records might appear
• SELECT DISTINCT spriden_last_name FROM spriden
• Conversely, if distinct was specifically used,
  duplicate records would disappear

40
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

41
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"

42
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

43
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

44
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

45
Laboratory Two SQL Editor

• SQL Query (first version)
• select saturn.spriden.spriden_last_name
• from saturn.spriden

46
Laboratory Two SQL Editor

• Second version
• select spriden.spriden_last_name
• from spriden
• Third version
• select s.spriden_last_name
• from spriden s

47
Laboratory Two SQL Editor

• fourth version
• select spriden_last_name
• from spriden
• a related, but different, query
• select distinct spriden_last_name
• from spriden

48
Laboratory Two SQL Editor

• Another example sql statement
• select from stvethn

49
Single Table Selects

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

50
Joins

• Outline
• Why multiple tables?
• Inner Joins
• Lab 6
• Outer Joins
• Lab 7

51
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