Introduction to SQL

Duration: 2 Days

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

This course is available as a private or customised course. Contact us for further information and to customise this course to your exact requirements:

Phone us: 0208 269 4260Book Now


"Concepts were explained clearly - very much so. What a great instructor! "

Jason Mather

"The course was perfect. Great course, great trainer!"

Northgate

"Our trainer was very approachable and knowledgeable."

Bond Pearce



 

 

© Transmedia . 277 Greenwich High Road London SE10 8NB  UK . 0208 269 4260
Designed by Transmedia's Web Team