SQL Scripting: Level 2 - Querying & Manipulating Data

COURSE OUTLINE:

Description In 'SQL Scripting: Level 1 - Fundamentals of Querying', 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 instructor-led course, you will create advanced SQL queries, and manipulate and index tables.

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

Prerequisites
Completion of SQL Scripting: Level 1 or equivalent knowledge recommended.

Learning 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