SQL Querying: Advanced

(SQL-Advance.AD1)/ISBN:9781644593615

This course includes
Lessons
TestPrep
LiveLab
Mentoring (Add-on)

Lessons

13+ Lessons | 46+ Exercises | 46+ Quizzes | 113+ Flashcards | 113+ Glossary of terms

TestPrep

62+ Pre Assessment Questions | 4+ Full Length Tests | 65+ Post Assessment Questions | 120+ Practice Test Questions

Hand on lab

42+ LiveLab | 42+ Video tutorials | 01:15+ Hours

Video Lessons

5+ Videos | 15+ Minutes

Here's what you will learn

Download Course Outline

Lessons 1: Executing a Simple Query

  • Lesson Objectives
  • Lesson Introduction
  • TOPIC A: Connect to the SQL Database
  • TOPIC B: Query a Database
  • TOPIC C: Save a Query
  • TOPIC D: Modify and Execute a Saved Query
  • Summary

Lessons 2: Performing a Conditional Search

  • Lesson Objectives
  • Lesson Introduction
  • TOPIC A: Search Using One or More Conditions
  • TOPIC B: Search for a Range of Values and NULL Values
  • TOPIC C: Search Data Based on Patterns
  • Summary

Lessons 3: Working with Functions

  • Lesson Objectives
  • Lesson Introduction
  • TOPIC A: Perform Date Calculations
  • TOPIC B: Calculate Data Using Aggregate Functions
  • TOPIC C: Manipulate String Values
  • Summary

Lessons 4: Organizing Data

  • Lesson Objectives
  • Lesson Introduction
  • TOPIC A: Sort Data
  • TOPIC B: Rank Data
  • TOPIC C: Group Data
  • TOPIC D: Filter Grouped Data
  • TOPIC E: Summarize Grouped Data
  • TOPIC F: Use PIVOT and UNPIVOT Operators
  • Summary

Lessons 5: Retrieving Data from Multiple Tables

  • Lesson Objectives
  • Lesson Introduction
  • TOPIC A: Combine the Results of Two Queries
  • TOPIC B: Compare the Results of Two Queries
  • TOPIC C: Retrieve Data by Joining Tables
  • Summary

Lessons 6: Exporting Query Results

  • Lesson Objectives
  • Lesson Introduction
  • TOPIC A: Generate a Text File
  • TOPIC B: Generate an XML File
  • Summary

Lessons 7: Using Subqueries to Perform Advanced Querying

  • Lesson Objectives
  • Introduction
  • TOPIC A: Search Based on Unknown Values
  • TOPIC B: Compare a Value with Unknown Values
  • TOPIC C: Search Based on the Existence of Records
  • TOPIC D: Generate Output Using Correlated Subqueries
  • TOPIC E: Filter Grouped Data Within Subqueries
  • TOPIC F: Perform Multiple-Level Subqueries
  • Summary

Lessons 8: Manipulating Table Data

  • Lesson Objectives
  • Introduction
  • TOPIC A: Insert Data
  • TOPIC B: Modify and Delete Data
  • Summary

Lessons 9: Manipulating the Table Structure

  • Lesson Objectives
  • Introduction
  • TOPIC A: Create a Table
  • TOPIC B: Create a Table with Constraints
  • TOPIC C: Modify a Table's Structure
  • TOPIC D: Back Up Tables
  • TOPIC E: Delete Tables
  • Summary

Lessons 10: Working with Views

  • Lesson Objectives
  • Introduction
  • TOPIC A: Create a View
  • TOPIC B: Manipulate Data in Views
  • TOPIC C: Create Aliases
  • TOPIC D: Modify and Delete Views
  • Summary

Lessons 11: Indexing Data

  • Lesson Objectives
  • Introduction
  • TOPIC A: Create Indexes
  • TOPIC B: Drop Indexes
  • Summary

Lessons 12: Managing Transactions

  • Lesson Objectives
  • Introduction
  • TOPIC A: Create Transactions
  • TOPIC B: Commit Transactions
  • Summary

Appendix A

  • The FullerAckerman Database
  • Schema

Hands-on LAB Activities

Executing a Simple Query

  • Connecting to a Database
  • Saving a Query and Modifying and Executing the Saved Query

Performing a Conditional Search

  • Comparing Column Values in a Table
  • Searching Using Simple and Multiple Conditions
  • Searching for a Range of Values and NULL Values
  • Retrieving Data Based on Patterns

Working with Functions

  • Performing Date Calculations
  • Using Aggregate Functions
  • Manipulating String Values in a Table

Organizing Data

  • Sorting Data in a Table
  • Ranking Data in a Table
  • Grouping Data in a Table
  • Filtering Grouped Data in a Table
  • Summarizing Grouped Data
  • Using the PIVOT and UNPIVOT Operators

Retrieving Data from Multiple Tables

  • Combining the Results of Two Queries
  • Comparing the Results of Two Queries
  • Retrieving Data by Joining Tables

Exporting Query Results

  • Saving the Query Results in CSV Format
  • Generating an XML File

Using Subqueries to Perform Advanced Querying

  • Searching for Records Based on Unknown Values
  • Searching for Records by Comparing Them with Unknown Values
  • Searching for Records Based on Their Existence in Another Table
  • Generating Output Using Correlated Subqueries
  • Filtering Grouped Data Within a Subquery
  • Generating Output Using Nested Subqueries

Manipulating Table Data

  • Inserting, Modifying, and Deleting Data

Manipulating the Table Structure

  • Creating a Table
  • Creating a Table with Constraints
  • Adding and Dropping Columns in a Table
  • Adding and Dropping Constraints in a Table
  • Modifying the Column Definition in a Table
  • Backing Up a Table
  • Deleting a Table

Working with Views

  • Creating Views
  • Creating Views with Schema Binding
  • Manipulating Data in Views
  • Creating Aliases
  • Modifying and Deleting Views

Indexing Data

  • Creating Indexes
  • Dropping Indexes

Managing Transactions

  • Creating and Comitting Transactions