SQL-PL/SQL

COURSE DESCRIPTION:

Query language (SQL) and procedural language-SQL (PL/SQL). Database structures and storing, retrieving, and manipulating data in relational databases. Covers PL/SQL blocks of application code.

COURSE OBJECTIVES: 

• Create/drop tables (including constraints and table population with data) and indexes.
• Write single table queries using all common clauses (DISTINCT, WHERE, ORDER BY) that include the selection of data based on logical operators, lists, data ranges, character matching, unknown values, and with expressions.
• Write queries with aggregate functions.
• Write JOIN queries and associated subqueries.
• Write queries with string, mathematical, conversion, and date/time functions.
• Create/drop views and materialized views, and query these database objects.
• Code PL/SQL subprograms that include the use of variables and constants.
• Code PL/SQL subprograms with conditional and iterative coding.
• Code PL/SQL programs with cursors and exception handling.
• Code PL/SQL functions and triggers.
• Code PL/SQL packages. 

COURSE SCHEDULE AND COMPUTER LABORATORY ASSIGNMENTS

Examinations are indicated in bold print.  Class notes are provided through hyperlinks.  You may wish to print a copy of the notes prior to class for reference during class.

Week 1 – Creating Tables and Indexes.  Covers relational database characteristics; data types; creating tables with constraints; relating tables; indexing tables; use of SQL Developer; executing a database script; simple SELECT statement.

• Read:  Bordoloi/Bock Chapter 1 and Chapter 2.  Power point slides:  Chapter 1, Chapter 2.
• Online reference:  SQL Reference – Chapter 2 Basic Elements of Oracle SQL.  Sections to read:  Datatypes, Database Objects, andSchema Object Names and Qualifiers.
• Creating a Virtual Private Network Tunnel to SIUE.
• Using SQL Developer at SIUE (helps you get started with SQL Developer).
• Connecting to an SIUE Oracle Server Using PuTTY and WinSCP.
• Work on your own time – Oracle SQL Developer (software) Tutorial:  http://st-curriculum.oracle.com/tutorial/SQLDeveloper/index.htm
• In-Class Lab #1 & #2:  Create the Madison Hospital database.

Week 2 – Single Table Queries.  Covers simple SELECT statement; formatting default output; common query errors; use of DISTINCT, WHERE, and ORDER BY clauses.

• Read: Bordoloi/Bock Chapter 3.  Power point slides:  Chapter 3.
• Online reference:  SQL Reference – Chapter 2 Basic Elements of Oracle SQL.  Sections to read:  Datatype Comparison and Literals.
• Online reference:  SQL Reference – Chapter 4 Operators. Sections to read:  About SQL Operators, Arithmetic Operators, andConcatenation Operator.
• Online reference:  SQL Reference – Chapter 9 Queries and Subqueries.  Sections to read:  About Queries and Subqueries, Creating Simple Queries, and Sorting Query Results.
• In-Class Lab #3:  Writing Simple Queries.

Week 3 – Adding Power to Queries.  Covers logical operators; lists; ranges; character matching; unknown values; order of precedence; and using expressions in queries.  Aggregate Functions and Grouping Data.  Covers the AVG, SUM, MIN, MAX, and COUNT aggregate functions; GROUP BY clause; HAVING clause; and the use of GROUPBY with expressions, WHERE clause, and ORDER BY clause.

• Read: Bordoloi/Bock Chapter 4.  Power point slides:  Chapter 4.
• Online reference:  SQL Reference – Chapter 7 Conditions.  Sections to read:  About SQL Conditions, Comparison Conditions, Floating-Point Conditions, Logical Conditions, Pattern-matching Conditions, Range Conditions, Null Conditions, and In Condition.
• In-Class Lab #4:  Writing Queries with Complex WHERE Clauses.
• Read: Bordoloi/Bock Chapter 5.  Power point slides:  Chapter 5.

·         Online reference:  SQL Reference – Chapter 5 Functions.  Sections to read:  Aggregate Functions.

• In-Class Lab #5:  Writing Queries with Aggregate Functions.

Week 4 – Joins.  Covers join operations; join operation rules; Cartesian products; inner joins; complex joins with three or more tables; outer joins; self-joins (also called hierarchical joins).

• Read: Bordoloi/Bock Chapter 6.  Power point slides:  Chapter 6.

• Online reference:  SQL Reference – Chapter 9 Queries and Subqueries.  Section to read:  Joins (except for AntiJoins and SemiJoins).
• In-Class Lab #6:  Writing Join Queries.

Week 5 – Exam #1

Week 6 – Subqueries.  Covers defining a subquery; subquery restrictions; subqueries with the IN and NOT IN operators; multiple levels of nesting; subqueries and comparison operators; correlated subqueries.

• Read: Bordoloi/Bock Chapter 7.  Power point slides:  Chapter 7.
• Online reference:  SQL Reference – Chapter 9 Queries and Subqueries.  Section to read: Using Subqueries; Unnesting NestedSubqueries, and Joins (read the section on AntiJoins and SemiJoins).
• In-Class Lab #7:  Writing Subqueries.

Week 7 – Additional Functions (NOT aggregate functions).  Covers string and text functions; mathematical functions; conversion functions; date/time functions.

• Read: Bordoloi/Bock Chapter 10.  Power point slides:  Chapter 10.

·         Online reference:  SQL Reference – Chapter 5 Functions.  Section to read:  Single-Row Functions.

• In-Class Lab #10:  Writing Queries with Single-Line Functions.

Week 8 – Views, Synonyms, and Sequences.  Covers creating views, join views, dropping views, view stability, data updates through views, and materialized views; creating, renaming, and dropping public and private synonyms; creating, accessing, altering, and dropping sequences. 

• Read: Bordoloi/Bock Chapter 8.  Power point slides:  Chapter 8.

·         Online reference:  SQL Reference – Chapter 11 SQL Statements:  ALTER MATERIALIZED VIEW to ALTER SYSTEM.  Section to read: ALTER SEQUENCE.

·         Online reference:  SQL Reference – Chapter 13 SQL Statements:  ALTER TRIGGER to COMMIT.  Section to read:  ALTER VIEW.

·         Online reference:  SQL Reference – Chapter 15 SQL Statements: CREATE LIBRARY to CREATE SPFILE.  Sections to read:  CREATE MATERIALIZED VIEW and CREATE SEQUENCE.

·         Online reference:  SQL Reference – Chapter 16 SQL Statements: CREATE SYNONYM to CREATE TRIGGER.  Section to read: CREATE SYNONYM.

·         Online reference:  SQL Reference – Chapter 17 SQL Statements: CREATE TYPE to DROP ROLLBACK SEGMENT.  Section to read: CREATE VIEW.

·         Online reference:  SQL Reference – Chapter 18 SQL Statements: DROP SEQUENCE to ROLLBACK.  Sections to read:  DROP SEQUENCYE, DROP SYNONYM, and DROP VIEW.

·         In-Class Lab #8:  Views, Synonyms, and Sequences.

Week 9 – SQL Plus Reports.  Covers creating basic reports through the use of command files; control break reports; master-detail reports; using variables for interactive reporting.

• Read: Bordoloi/Bock Chapter 9.  Power point slides:  Chapter 9.

·         In-Class Lab #9:  SQL*Plus Reports.

Week 10 – Exam #2

Week 11 – Break.

Week 12 – Introduction to PL/SQL.  Covers an overview of PL/SQL as a procedural language; PL/SQL architecture; using PL/SQL variables and constants; declaring PL/SQL subprograms;

• Read: Bordoloi/Bock Chapter 11.  Power point slides:  Chapter 11.

·         Online reference:  PL/SQL Reference – Chapter 1 Overview of PL/SQL.  Sections to read:  all sections.

·         Online reference:  PL/SQL Reference – Chapter 2 Fundamentals of PL/SQL.  Sections to read:  all sections.

·         No Lab for this week.  You will be tested on this material on the final exam.

Week 13 – PL/SQL Conditional and Iterative Coding.  Covers IF and CASE statements; LOOP and EXIT statements; WHILE-LOOP statement; FOR-LOOP statement; GOTO statement; and NULL statement; also covers PL/SQL cursors and exceptions; querying data; using Subqueries; using cursor variables and expressions; and transaction processing.

• Read: Bordoloi/Bock Chapter 12.  Power point slides:  Chapter 12.
• Online reference:  PL/SQL Reference – Chapter 4 Using PL/SQL Control Structures.  Sections to read:  all sections.
• Online reference:  PL/SQL Reference – Chapter 6 Performing SQL Operations from PL/SQL.  Sections to read:  all sections.
• Online reference:  PL/SQL Reference – Chapter 10 Handling PL/SQL Errors.  Sections to read:  all sections.
• No Lab for this week.  You will be tested on this material on the final exam.

Week 14 – PL/SQL Procedures, Functions, Packages, and Triggers.  Covers an introduction to subprograms; creating PL/SQL functions and the RETURN statement; passing parameters; PL/SQL Packages including package specification and coding a package body; coding DML Triggers. 

• Read: Bordoloi/Bock Chapter 13.  Power point slides:  Chapter 13.
• Online reference:  PL/SQL Reference – Chapter 8 Using PL/SQL Subprograms.  Sections to read:  What Are Subprograms through How Subprogram Calls are Resolved. 
• Online reference:  PL/SQL Reference – Chapter 9 Using PL/SQL Packages.
• Online reference: Oracle Database Concepts – Chapter 22 Triggers.

·         No Lab for this week.  You will be tested on this material on the final exam.

Week 15 – Work on the Take Home Final Exam Project.  This is a take home exam project.  It must be submitted for grading as indicated within the examination document.