LOGO For Immediate Assistance Call 855-211-9361

Open Enrollment

Advanced SQL & Enterprise Data

Price: $700.00

Call for availability 855-211-9361

Building upon the skills mastered in PB Tech's popular Introduction to SQL and Database Design course, Advanced SQL and Database Programming teaches the development techniques needed to take full advantage of powerful and flexible data-driven applications. We'll turn from the prior course's concern with data modeling and database design to how to use data in extensive, enterprise-class databases. Again, we will be using MySQL as the basis for the examples and exercises.

Course Overview

By the end of the class students will be able to:

  • Query data simultaneously from multiple, related tables
  • Design subqueries and correlated subqueries
  • Use unions to combine queries into a single result set
  • Performance-tune queries
  • Implement transactions
  • Execute triggers
  • Create and execute stored procedures
  • Handle error conditions

Prerequisites

815 Introduction to SQL and Database Design

Course Outline

  • Entity-Relationship Diagrams
    Text Functions

    • Matching Patterns with LIKE
    • Text manipulation functions
    • Exercise 1: Text Manipulation Functions
    • Dates in SQL Server
    • Exercise 2: Date Handling
    • Conversion Functions
    • Using Union to Combine Queries
    • Exercise 3: Using Union
    • Transact-SQL's CASE statement
  • A Review of Aggregate Functions

    • GROUP BY
    • HAVING
    • WITH CUBE and WITH ROLLUP
    • COMPUTE BY
    • Exercise 4: Fancy Aggregate Functions
  • Querying Data Across Tables

    • Joins the ANSI-92 Way
    • Outer Joins
    • Exercise 5: Using an Outer Join to Show Sales Information
    • Self Joins
    • Exercise 6: Self-join and an organization chart
  • Subqueries

    • Exercise 7: Subqueries
    • Correlated Subqueries
    • Exercise 8: Correlated Subqueries
    • Performance Tuning
    • Connecting to Data in Other Databases
  • Beginning Transact-SQL Programming

    • Introduction to Stored Procedures
    • Exercise 9: A Simple Stored Procedure
    • Transactions in SQL Server
    • Exercise 10: Using a Stored Procedure to Calculate YTD Sales
    • Triggers
    • Exercise 11: Triggers
  • The Books Database

    • Entity-Relationship Diagrams
    • Create Table Statements