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 => Query => 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. ANSI SQL/92 RDBMSFlavors??? (Franz/Hammer/Holliday) Ingres, Postgres DB2, Informix MS Access Microsoft SQL Server Oracle MySQL

12. ID First Name Last Name Phone 1 Stan Hammer 2929 2 Ernie Jamison 2127 3 Brian Chamberlin 3210 Set of Employees Relational Database Model (Hammer) Relational comes from set theory’s 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 Addresses SPRADDR Personal Info SPBPERS Telephone Info SPRTELE

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 database’s 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 User’s 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 • => 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. char(size) Fixed-length character string. Size is specified in parenthesis. Max 255 bytes. varchar(size) Variable-length character string. Max size is specified in parenthesis. number(size) Number value with a max number of column digits specified in parenthesis. date Date value number(size,d) Number value with a maximum number of digits of "size" total, with a maximum number of "d" digits to the right of the decimal. 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[,column2]FROMtable1[,table2][WHERE "conditions"][GROUP BY "column-list"][HAVING "conditions”][ORDER BY "column-list" [ASC | DESC] ]

39. spriden_last_name Jones Smith Smith Smith Thompson Wilson Wilson spriden_last_name Jones Smith Thompson Wilson 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. STVETHN_CODE STVETHN_DESC … W White, Not of Hispanic Origin … I Amer Indian/Alaskan Native … … O Asian or Pacific Islander … 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: