Description

This practical course provides attendees with advanced querying techniques, including subqueries and window functions.

You will also learn how to create and manipulate tables, create views, index data and manage transactions.

Prerequisites

SQL Training: Fundamentals of Querying or equivalent experience.

Key Topics

• Using Subqueries to Perform Advanced Querying
• Manipulating Table Data
• Manipulating the Table Structure
• Working with Views
• Indexing Data
• Managing Transactions

Learning Objectives

When the participant completes this course they 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.

Topic Outline

Using Subqueries to Perform Advanced Querying

• Search Based on Unknown Values
• Compare a Value with Unknown Values
• Search Based on the Existence of Records
• Generate Output Using Correlated Subqueries
• Filter Grouped Data Within Subqueries
• Perform Multiple-Level Subqueries

Manipulating Table Data

• Insert Data
• Modify and Delete Data

Manipulating the Table Structure

• Create a Table
• Create a Table with Constraints
• Add and Drop Table Columns
• Add and Drop Constraints
• Modify the Column Definition
• Back Up Tables
• Delete Tables

Working with Views

• Create a View
• Manipulate Data in Views
• Create Aliases
• Modify and Drop Views

Indexing Data

• Create Indices
• Drop Indices

Managing Transactions

• Create Transactions
• Commit Transactions

Target Audience

This course is designed for data analysts and any others in the organisation who require advanced SQL skills. This course is suitable for people with the following job title or similar role:

• Data analyst
• Reporting specialist
• Business intelligence analyst