SQL Basic

Overview

SQL (Structured Query Language) is the universal language used to interact with all commercial relational database management systems (RDBMS’s). This introduction to ANSI SQL course incorporates lecture, labs and practical exercises to teach students relational database fundamentals and SQL programming skills. Topics covered include RDBMS client-server operations, data tables, referential integrity, and the SQL language to include simple and complex data definition and data manipulation language.

The course shows the general syntax of each statement according to ANSI SQL and follows with numerous examples of various uses of each statement. Each example is shown in the standard ANSI SQL format and most are given for both Oracle and Microsoft SQL Server to show that most of the language is completely portable but that there are some proprietary differences. The course can be adapted for use with any of the major commercial Relational Database Management Systems.

Benefits

Upon successful completion of this course, students should be able to understand the SQL standard and use SQL to:

• create new tables
• insert new data into the tables
• modify or delete existing data
• retrieve data according to specified selection criteria
• retrieve related data from several tables in a single query.

Intended Audience

This course is recommended for database application programmers and system administrators, database designers and system architects, programmers, data analysts, power end users, and managers interested in gaining an ability to create SQL queries on a database.

Prerequisites

Students should have general computer programming background.

On-site System Requirements

In order for students to to do the labs, they must have a computer with client software able to connect to a test server for the RDBMS they will be using.

Although a number of operating Systems can be used, the course is usually taught in a Windows or UNIX/Linux environment with access to at least one machine with the target RDBMS installed. It is preferable to have the database server installed on each student machine.

Suggested Follow-On Courses

Advanced SQL (any RDBMS), Database Design (any RDBMS), Database Administration (Microsoft SQL Server).

Training Approach

This is an intensive, interactive course, which is approximately 70% lecture and 30% lab. Questions are highly encouraged.

Recommended Texts (if desired, as additional resource):

SQL Fundamentals, 2nd edition , John Patrick, Prentice-Hall.
Transact-SQL Desk Reference: For Microsoft SQL Server, Deac Lancaster, Prentice-Hall.

Outline

Day 1

Chapter 1—Introduction to RDBMS and SQL

• Using a commercial RDBMS to model business data.
• SQL and the ANSI SQL standard.
• Client-Server Architecture
• Organizing and accessing collections of tables.
• The SQL Language
• SQL Data Types - 1st look
• SQL Comments
• Data and Metadata
• Identifiers
• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• DDL Syntax & Examples - CREATE TABLE, ALTER TABLE, DROP TABLE

• Commercial RDBMS Utilities to Show Table Structure
• DML Syntax & Examples - INSERT, UPDATE, DELETE, SELECT
• Capitalization In Sql Statements
• Complete Simple Example of DDL and DML Operations

Lab Exercises - Chapter 1

Day 2

Chapter 2 — SQL Data Types and Basic Queries

• Uses of an RDBMS: OLTP and DSS
• OLTP – Online Transaction Processing - focus of this class
• DSS – Decision Support Systems, Data Warehousing
• Expressions in SQL
• Column Aliases
• dual - Oracle
• STRING CONCATENATION
• ANSI SQL Data Types
• ANSI SQL Types compared with Oracle and MS SQL Server
• Automatically Generated Primary Key Values
• MSS IDENTITY Property to generate Integer values
• Oracle SEQUENCE to generate Integer values
• Domain Values and NULL
• Literals (Constants)
• CHARACTER and VARCHAR Literals:
• Number Literals: INT, DEC, NUMERIC, FLOAT,
• DATE and TIME Literals:
• Oracle DATE literals
• MSS DATETIME literals
• ANSI SQL Symbolic Constants
• CAST
• Oracle functions to convert among data types
• Queries: SELECT Statement - 2d look
• SQL is Free Form
• DISTINCT
• ORDER BY
• Aggregate Functions - Count, SUM, AVG, MIN, MAX
• Relational Operators, NULL Values and Logical Operators
• Comparing VARCHAR and CHAR Values for Equality
• NULL and Three Way Boolean Logic in SQL
• Comparing a column to a domain value vs being NULL
• Using IS NULL in a query
• Updating a Value to NULL
• Compound WHERE clauses
• Truth Tables with NULL
• LIKE Pattern Matching Operator
• ANSI SQL Functions - overview
• Other Functions in Oracle and MSS

Lab Exercises - Chapter 2

Day 3

Chapter 3 — Schemas, Views, Joins

• Schema and Catalog
• Database Diagram
• Views
• Table Aliases (correlation names)
• Joins
• Inner Joins - The essence of RDBMSs
• Inner Join Examples:
• Self-Join
• Outer Joins
• Left Outer Join
• Right Outer Join
• Full Outer Join
• Relationships
• Keys, Primary Keys and Foreign Keys
• Database Diagram or Schema Diagram
• M:1 and 1:1 Relationships
• Many to Many Relationships
• ANSI SQL Transaction Control
• Starting and Ending a Transaction

Lab Exercises - Chapter 3