Introduction to SQL

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.

• 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

• Logical Data Modelling
• Relational Database Design

1. Introduction to Databases

• Database
• Files or Tables
• Database - Further Properties
• Metadata

• Relational Database
• Example of a Relation
• Relational Operations

• 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

• 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

• 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

• 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

• 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

• Drop Table
• Alter
• Create Index
• Index Speedup

• 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

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

• SQL Server
• Enterprise Manager
• Query Analyzer