Entegra Consultancy

Home / Training / Networking & Security / D101

D101

D101: SQL Programming - 3 Days

Who Should Attend:

Web designers and programmers, software engineers, systems analysts, applications programmers, computer science students, and anyone who is interested in acquiring the technical skills required to write basic SQL queries.

Prerequisites:

None.

Objectives:
SQL programming is a foundation module that provides Application Developers with the knowledge critical to the development of dynamic Web applications. This hands-on module 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 interface design issues on the Web.

Upon completion of this module, the participant should be able to Understand basic database concepts, retrieve complex data sets from a database, insert, delete and update data in tables. Operations such as group order and calculate computed values on data in tables as well as the use of the built-in query builder to quickly "paint" complex queries will be mastered. In addition the participant will be able to understand, compare and contrast connection strategies and appreciate performance-enhancing data interfaces for use on the Web.

Contents:

Introduction to Relational Databases: Relational database concepts. Data architectures. Logical vs. Physical Data Modeling. Entity/Relationship diagrams. Normalization.

Selecting Data: SQL Viewer. SELECT statement. Owner and table prefixes. Specifying textual vs numeric data types. Filtering rows with the WHERE clause. Using comparison operators: =, <, >, and <>. Understanding 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: Creating recordsets from multiple tables using an inner join. Using primary and foreign keys in performing joins. Joining tables using ANSI-92 JOIN syntax. Joining 3 or more tables in a single statement. Using filters with joins.

Changing database contents with INSERT, UPDATE and DELETE: Adding data to tables with the INSERT statement. UPDATE statement. Using a filter. The DELETE statement. Flagging records deleted as an alternative to DELETE.

Enhancing SELECT statements: Ordering data. Expressions in SELECT. Renaming tables and columns with aliases. Selecting computed columns (expressions). Character strings in queries (hard coding string output in query). Aggregating values. Getting row counts. Minimum, Maximum, Average and Sum (column). Grouping data. Grouping query results with the GROUP BY clause. Aggregates and groups (count, min, max, avg, sum). Using the HAVING clause. Using positional notation in GROUP BY.

Connecting to a RDBMS : Understand the Client/Server model for databases. Basic concept of database users and permissions. Discuss how database drivers work (queries and result sets). Creating an ODBC Data source. Comparing performance (Access &SQL Server 2000 & MySQL & Oracle). Query Painters.

Introduction to Stored Procedures: 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. Next-n interfaces. Limiting to n rows returned. Performing dynamic searches. Increasing selectivity using search criteria.

--------