Fast Track to SQL

Objectives

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.


Pre-requisites:

Basic understanding of programming concepts, and experience using Windows operating system.


Course Includes:

A comprehensive set of reference notes covering course topics, follow-up telephone support and, for training on Transmedia premises, lunch and refreshments.


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