Introduction to SQL

Summary

This course is an introduction to the SQL database language. After this course the student will be able to use SQL to define, manipulate and control access to their data. The course thoroughly covers SQL syntax and facilities.

The course begins with a general introduction to databases, including the principles of relational databases. Data retrieval is discussed in depth, covering all of the pertinent features of SQL, including joins. Data Manipulation Language is covered, including inserting, updating and deleting data. Transaction control and locking is discussed. Data Definition Language is covered, including creating, altering and dropping tables, and the use of integrity constraints. Views and indexes are covered. The course includes detailed coverage of Data Control Language. The course concludes with an introduction to the principles of relational database design.

The material is generic to SQL. An appendix provides a survival guide to working with Microsoft SQL Server. The student will receive a Student Guide containing complete course notes with a lab manual containing very complete write-ups of laboratory exercises and solutions. A file provides a setup script for the class database. All courseware software is available for download.

Prerequisites

General familiarity with computers and data processing.

Course Objectives

Introduction to Databases - their purposes and terminology
Gain an in-depth understanding of SQL - concepts, syntax, uses
Use SQL hands-on to access a database

Intended Audience

This course may prove useful to people such as application developers, business managers, and users who need to develop SQL statements.

Next Steps

Students completing this course may wish to further their knowledge with

or some of the vendor-specific courses listed in the catalogue.

Length

3 days

Format

Instructor-led course, with many practical computer-based exercises.

Course Outline

1. Introduction to Databases

Database
Files or Tables
Database - Further Properties
Metadata

2. Relational Databases

Relational Database
Example of a Relation
Relational Operations

3. Introduction To SQL

History of SQL
Data Definition Language
Tables
Schemas
Views
Data Manipulation Language
Find Data
Insert, Update and Delete Data
Data Control Language
Grant and Revoke
Commit and Rollback
Non-Procedural Language
SQL Datatypes

4. Table Creation

Create Table
Column Constraints
NULL and NOT NULL
Keys
Default Values
References and Check Clauses
Additional Constraints
Foreign Keys
Populating A Table
Insert Statement
Inserting Into a Table through a Query Specification

5. Finding Data In Tables - Part I

Select Statement
Where Clause
Predicates
Boolean combinations
SQL "Truth" Tables
Boolean Precedence
Two Value Rules Retained
Between Predicate
In Predicate
Outer Reference
Exists Predicate
Quantified Predicate

6. Finding Data in Tables - Part II

Joins
Cross Product Joins
Equ-Join
Outer-Joins
Left Outer Join
Union, Intersection, and Difference
All Keyword
Order By
Multiple Column Order Bys
NULL and ORDER BY

7. Finding and Updating Data in Tables

Aggregate Functions
Table for Aggregates
Aggregate functions, examples
Aggregate Function Rules
Group By
Having Cause
Views
Normalized Tables and Views
Combined View
Views and Security
Drop View
Update
Assignments
Delete
Updateble Views

8. More Data Definition Language

Drop Table
Alter
Create Index
Index Speedup

9. Data Control Language

Data Control Language
Grant
Reference Privileges
Revoke
Abandoned Privileges
Transaction
Commit and Rollback
All material following in chapter is specific to Microsoft
Transactions and Microsoft
SQL Server Locks
Locking Granularity
Shared, Exclusive and Update Lock
Intent, Extent and Demand Locks
Demand locks
sp_lock
Deadlock
ISQLW and Transactions
ISQLW and set statements
Read Commited
Read Uncommited
Repeatable Read
HOLDLOCK
Other Table Qualifiers

10. Introduction to Relational Database Design

One-To-One Relationships
One-To-Many
Many-To-Many
Normal Forms
First Normal Form
Second Normal Form
Third Normal Form

Appendix A. Lab Manual Appendix B. SQL Server Survival Guide

SQL Server
Enterprise Manager
Query Analyzer

Appendix C. Reading List

Hardware and Software Requirements

A computer with any SQL-92 compliant database should be sufficient for most of the material in this course, although a small amount of the course material is specific to Microsoft.

Notice: Undefined index: HTTP_REFERER in /var/www/datadeliverance/inc/trademarks.inc on line 3

Notice: Undefined index: HTTP_REFERER in /var/www/datadeliverance/inc/trademarks.inc on line 4

Notice: Undefined variable: REMOTE_USER in /var/www/datadeliverance/inc/trademarks.inc on line 7

Notice: Undefined index: HTTP_REFERER in /var/www/datadeliverance/inc/trademarks.inc on line 7

Oracle and Java are registered trademarks of Oracle and/or its affiliates. PL/SQL and SQL*Plus are trademarks or registered trademarks of Oracle Corporation. Microsoft is either a registered trademark or trademark of Microsoft Corporation in the United States and/or other countries. MVS is a registered trademark of the International Business Machines Corp. in the U.S. or other countries or both. Motif, OSF/1, UNIX, and the "X Device" are registered trademarks and IT DialTone and The Open Group are trademarks of The Open Group in the U.S. and other countries. All other trademarks are the property of their respective owners.