SQL: Advanced Querying (Fourth Edition)
Course Specifications
Course number: 085072
Course length: 2.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:
•	MySQL 
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