SQL: Advanced Querying (Second Edition)

Course Specifications

Course number: 085562
Software: Microsoft SQL Server 2000
Course length: 1.0 day(s)

Course Description

In the course SQL: Fundamentals of Querying (Second Edition), you examined the fundamentals of SQL querying. Because of various business conditions and requirements, in addition to querying the database, you may need to restructure and index tables. You may also need to manipulate the data in the tables. In this course, SQL: Advanced Querying (Second Edition), you will create advanced SQL queries, and manipulate and index tables.

Course Objective: You will work with advanced queries and manipulate tables. You will also view and index table data.

Target Student: Students should have basic introductory SQL skills, basic computer 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 (Second Edition) and Windows XP: Introduction or

• Windows XP Professional: Level 1 and Windows XP Professional: Level 2

Delivery Method: Instructor led, group-paced, classroom-delivery learning model with structured hands-on activities.

Performance-Based Objectives

Upon successful completion of this course, students will be able to:

• code subqueries to generate query output.
• manipulate table data by inserting, updating, and deleting records in a table.
• create a simple table, create tables with constraints, modify the table structure, and delete tables.
• create views, manipulate data through views, modify the view structure, and drop views.
• create indexes on table columns and drop inefficient indexes.

Course Content

Lesson 1: Advanced Querying Using Subqueries

Topic 1A: Search Based on an Unknown Value

Topic 1B: Search Based on Multiple Unknown Values

Topic 1C: Compare a Value with Unknown Values

Topic 1D: Search Based on the Existence of Records

Topic 1E: Generate Output Using Correlated Subqueries

Topic 1F: Filter Grouped Data Within Subqueries

Topic 1G: Perform Multiple-level Subqueries

Lesson 2: Manipulating Table Data

Topic 2A: Insert Data

Topic 2B: Modify Data

Topic 2C: Delete Data

Lesson 3: Manipulating the Table Structure

Topic 3A: Create a Simple Table

Topic 3B: Create a Table with Constraints

Topic 3C: Add or Drop Table Columns

Topic 3D: Add or Drop Constraints

Topic 3E: Modify the Column Definition

Topic 3F: Delete Tables

Lesson 4: Working with Views

Topic 4A: Create a View

Topic 4B: Manipulate Data in Views

Topic 4C: Modify and Drop Views

Lesson 5: Indexing Data

Topic 5A: Create Indexes

Topic 5B: Drop Indexes

Appendix A: Structure of the Bookstore Database

Appendix B: Working with Transactions

Supplemental Lesson Working with Transactions

Topic 1A: Rollback Transactions

Topic 1B: Commit Transactions