Fast Track to SQL is a 2-day course that provides application developers with the foundation critical to any dynamic web application — database and SQL knowledge. This hands-on course gives students knowledge on Structured Query Language

Topics:

Understanding Database design basics
Painting complex queries using the query builder
Comparing and contrasting connection strategies
Using the SQL Data Manipulation Language
Implementing interfaces to data on the web
Introducing stored procedures

Prerequisites:

Basic understanding of programming concepts
Experience using Windows operating system

Objectives:

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 Allaire's built-in query builder to quickly "paint" complex queries
Choose a database platform based on your web site's requirements
Understand, compare and contrast connection strategies
Describe performance-enhancing data interfaces for use on the web

Outline:
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
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
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
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
Using a filter
Flagging records deleted as an alternative to DELETE
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
Choosing a RDBMS platform

Understanding scalability issues (concurrent users, locking granularity, types of indexing)
Comparing functionality (Transaction support, SP support)
Pricing Issues (platform requirements, licensing, training requirements)
DBMS Comparison Matrix
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
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
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 selectivity using search criteria
APPENDIX A: SQL Data Definition Language
CREATE TABLE
ALTER TABLE
DROP TABLE
APPENDIX B: Oracle Issues
Oracle Tablespaces
Oracle extensions to SQL
Connecting to Oracle (SQL*Net, Native vs ODBC Drivers)

-top

American Media Training