SQL: Advanced Querying (Fourth Edition)
Course Specifications
Course number: 085072
Course length: 1.0 day(s)
Course Description
Course Objective: You will work with advanced queries to manipulate and index tables.
You will also create transactions so that you can choose to save or cancel
data entry process.
Target Student: Students should have basic computer skills, SQL skills, and be familiar
with concepts related to database structure and terminology.
Prerequisites: To ensure your success, we recommend you first take the
following Element K courses, or have equivalent skills and knowledge:
- SQL: Fundamentals
of Querying (Fourth Edition)
- Windows XP Professional: Level
1 and Windows XP
Professional: Level 2 or Windows XP: Introduction
Hardware Requirements
- Minimum of 1 GB of RAM for each computer.
- 40 GB or larger hard disk drive for each student and instructor
computer with 1 GB of free space.
- Pentium IV 1 GHz CPU or higher.
- VGA or higher resolution video card and monitor with screen
resolution of 1024x768.
- Mouse or compatible tracking device.
- 12x (or faster) CD-ROM drive.
- Network adapter and network cable.
- Display system to project the instructor’s computer
screen.
Software Requirements
Each computer requires the following software:
- SQL Server™ 2008 Standard Edition.
- Microsoft SQL Server Management Studio 2008 for the instructor
and each student.
Course Objectives
Upon successful completion of this course, students will be able to:
- use subqueries to generate query output.
- manipulate table data by inserting and updating records
in a table and deleting records from a table.
- manipulate the table structure.
- create views, manipulate data through views, modify the
view structure, and drop views.
- create indices on table columns and drop inefficient indices.
- mark the beginning of a transaction, create a savepoint
within a transaction, rollback a transaction, and commit a transaction.
Course Content
- Lesson 1: Using Subqueries to Perform Advanced Querying
- Topic 1A: Search Based on Unknown Values
- Topic 1B: Compare a Value with Unknown Values
- Topic 1C: Search Based on the Existence of Records
- Topic 1D: Generate Output Using Correlated Subqueries
- Topic 1E: Filter Grouped Data Within Subqueries
- Topic 1F: Perform Multiple-Level Subqueries
- Lesson 2: Manipulating Table Data
- Topic 2A: Insert Data
- Topic 2B: Modify and Delete Data
- Lesson 3: Manipulating the Table Structure
- Topic 3A: Create a Table
- Topic 3B: Create a Table with Constraints
- Topic 3C: Add and Drop Table Columns
- Topic 3D: Add and Drop Constraints
- Topic 3E: Modify the Column Definition
- Topic 3F: Back Up Tables
- Topic 3G: Delete Tables
- Lesson 4: Working with Views
- Topic 4A: Create a View
- Topic 4B: Manipulate Data in Views
- Topic 4C: Create Aliases
- Topic 4D: Modify and Drop Views
- Lesson 5: Indexing Data
- Topic 5A: Create Indices
- Topic 5B: Drop Indices
- Lesson 6: Managing Transactions
- Topic 6A: Create Transactions
- Topic 6B: Commit Transactions
Appendix A: Structure
of the Bookstore Database