Course Prerequisites

Course Objectives

Upon completion of this course, you should be able to:

• Understand database terminology and articulate your site's database design
• Retrieve complex data sets from a database
• Insert, delete and update data in tables
• Group, order and calculate computed values on data in tables
• Use the built-in query builder to quickly "paint" complex queries
• Understand, compare and contrast connection strategies
• Describe performance-enhancing data interfaces for use on the Web

Course Outline

Unit 1: Introducing Fast Track to SQL

• Understanding the course prerequisites
• Using the course format
• Browsing the course outline

Unit 2: Introduction to Relational Databases

• Define the terms used in relational database design
• Understand why duplicate data is bad and hard to manage
• Define a relational database
• Contrast data architectures
• Describe the basic concepts of relational technology
• Logical vs. Physical Data Modeling
• Understand Entity/Relationship diagrams
• Describe the basics of normalization

Unit 3: Selecting Data

• Overview of SQL Viewer
• Using the basic SELECT statement
• Using a column wildcard
• Using owner and table prefixes
• Specifying textual vs numeric data types
• Filtering rows with the WHERE clause
• Using comparison operators: =, <, >, and <>
• Using Null
• Understanding Nulls and inequality
• Using compound WHERE clauses with AND and OR
• Using IN and NOT IN to shorten SQL queries
• Using LIKE for partial pattern matching

Unit 4: Creating JOIN statements

• Describe join types
• Creating recordsets from multiple tables using an inner join
• Using primary and foreign keys in performing joins
• Joining tables using ANSI-92 JOIN syntax
• Joining 3 or more tables in a single statement
• Using filters with joins

Unit 5: Changing database contents with INSERT, UPDATE and DELETE

• Adding data to tables with the INSERT statement
• Introducing the UPDATE statement
• Using a filter
• Using the DELETE statement
• Flagging records deleted as an alternative to DELETE

Unit 6: Enhancing SELECT statements

• Ordering data
• Expressions in SELECT
• Renaming tables and columns with aliases
• Selecting computed columns (expressions)
• Character strings in queries (hardcoding string output in query)
• Aggregating values
• Getting row counts
• Minimum in a column: min()
• Maximum in a column: max()
• Average of a column: avg()
• Sum a column: sum()
• Grouping data
• Grouping query results with the GROUP BY clause
• Aggregates and groups (count, min, max, avg, sum)
• Using the HAVING clause
• Using positional notation in GROUP BY

Unit 7: Connecting to a RDBMS

• Understand the Client/Server model for databases
• Basic concept of database users and permissions
• Discuss how database drivers work (queries and result sets)
• Creating an ODBC Data source
• Comparing performance (Access & MSDE)
• Query Painters

Unit 8: Introduction to Stored Procedures

• Description of stored procedures
• Benefits of stored procedures
• Incorporating procedural logic into stored procedures
• Examples of stored procedures for SQL Server and Oracle

Unit 9: Strategies for Web Database Access

• Understand limitations of the Web environment
• Discuss strategies for limiting data
• Next-n interfaces
• Limiting to n rows returned
• Performing dynamic searches
• Increasing