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