Using SQL and Relational Databases

Overview

This course teaches skills necessary to query, update, and develop a relational database. The relational model has become the dominant paradigm in the world of Client/Server databases. Structured Query Language (SQL) is the programming language used to develop, maintain, and run these databases.

All vendors of relational database products support SQL. Besides an industry-approved standard, there are several dialects of SQL. Fortunately, the differences among these dialects are minor. You can easily carry over skill in one dialect to another.

The course describes both the standard version of SQL and the language as implemented by Oracle. The average amount of time to complete the on-line materials is 12 to 14 hours. In addition, the materials may be reviewed at any time to provide reinforcement and greater retention.

Objectives

Upon successful conclusion of the course, the student should be able to:

• Understand the basics concepts underlying a relational database model.
• Design tables to hold data.
• Populate the tables with business data.
• Develop queries to extract information in an appropriate form from the database.
• Modify existing data in tables.

Course Outline

Lesson 1: Introducing Relational Databases and SQL
Introduction to Relationa lDatabases * Tables, Columns, andRows * Primary Keys ForeignKeys * Integrity Constraints A Sample Database * Data in theSample Database * Printing theSample Database Introduction to SQL * Declarative Language * Set-Oriented Language * Dialects of SQL SQL Sub-Languages * DataDefinition Language - DDL * DataManipulationLanguage-DML * Data Control Language - DCL

Lesson 2: Defining and Populating Tables
The CREATE TABLE Command Columns * Constraints * An Example DataTypes * Character Types * NumericData Types * Temporal Data Type * Binary Data * Null Value * DevelopingTables for theCompanyDatabase * Defining Column Clauses * Defining Constraint Clauses * Indexes

Lesson 3: Using Select: The Basic Query Statement
The SELECT Command The SELECTClause The FROM Clause Reordering of Columns Removing Duplicate Rows The WHERE Clause * RelationalPredicates * NULLs inComparison Using Boolean Operators in WHERE Clauses

Lesson 4: Using Operators and Functions in the Select Statements
Special Operators * The IN operator *TheBETWEENoperator * TheLIKE operator Aggregate Functions * The Count Function * The SUM and the AVG functions * The MAX and the MIN Functions The Group By Clause * The Having Clause

Lesson 5: Queries on Multiple Tables: Joins
A Two-Table Query Two Table Joins * Ambiguous columnnames * Matching Columns * Cartesian Product table * Equijoins * Selfjoin and Alias * Table Aliases * Queryiagram for Joins * Three TableJoins

Lesson 6: Relating Multiple Queries
Subqueries * Uncorrelated Queries with Relational Operators * Uncorrelated Query with INOperator * Uncorrelatde Queries with the ANY Operator * Uncorrelated Queries with the ALL Operator Correlated Queries * CorrelatedQueries with Relational Operators * Correlated Queries and the EXISTS Operator Subqueries and Joins

Lesson 7: Keeping a Database Current
The INSERT INTO Command The DELETE FROM Command The UPDATE Statement Altering Tables * Adding Columns andConstraints * Dropping Constraints * Dropping Columns * Modifying Columns * Modifying Constraints Dropping A Table

Lesson 8: Creating and Using Views
The CREATE VIEW Command * Advantages of Views * Views and Joins * Views based on Subqueries and other Views Changing Data Through Views * Viewswith the CHECK OPTION Usingthe DROP VIEW Command All IBT Courses are Y2K (Year 2000 Compliant). * Windows NT is a trademark of Microsoft Corporation. Click here for an online order form that is submitted automatically to ESI.

Note: All web based training courses are Y2K (Year 2000) compliant.