085972 - Advanced Querying

Duration

1 Day - Instructor Led

Introduction

In the course SQL: Fundamentals of Querying (Third Edition), you performed basic 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 using advanced queries. In this course, SQL: Advanced Querying (Third Edition), you will create advanced SQL queries to manipulate and index tables.

Audience

Students should have basic introductory SQL skills, basic computer skills, and be familiar with concepts related to database structure and terminology.

Course Objectives   

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.

At Course Completion

• Upon successful completion of this course, students will be able to:
• Connect to the database and execute a simple query.
• Include a search condition in a simple query.
• Use various functions based on the data types to perform calculations on data.
• Organize the data obtained from the query before it is displayed on the screen.
• Retrieve data by combining information from multiple tables.
• Format the output, save the result, and generate a report.

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
• Windows XP Professional : Level 1 and Windows XP Professional : Level 2 or Windows XP : Introduction

Performance-Based Objectives

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

• use subqueries to generate query output.
• manipulate table data by inserting, updating, and deleting records in 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 Outline

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 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

top of page