Introduction to SQL

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