SQL Introduction Training

This course provides Application Developers with the foundation critical to any dynamic Web application - database and SQL knowledge. This hands-on course provides students useable knowledge on Structured Query Language - the language of relational databases. It also extends that knowledge to database design basics, choosing the correct database for your Web application, and useful interface design on the Web.

Course Outline
Introducing Fast Track to SQL

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

Introduction to Relational Databases

• Define relational database/terms used in relational database design/ basic concepts of relational technology
• Understand why duplicate data is bad and hard to manage
• Contrast data architectures
• Logical vs. Physical Data Modelling
• 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/ 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 multiiple tables using an inner join
• Using primary and foreign keys in performing joins/filters with joins
• Joining tables using ANSI-92 syntax
• Joining 3 or more tables in a single statement
• Changing database contents with INSERT, UPDATE and DELETE
• Adding data to tables with the INSERT statement
• Introducing the UPDATE statement
• Using the DELETE statement/a filter
• Flagging records deleted as an alternative

Enhancing SELECT statements

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

Connecting to a RDBMS

• Understand the Client/Server model for databases
• Basic concept of database users and permissions
• Discuss how database drivers work
• 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