SQL Intermediate to Advanced Developer Course

SQL Intermediate to Advanced Developer

You’ll be able to design and implement database models and database storage objects; deal with user-defined functions, triggers, stored procedures, advanced SQL query writing and reporting.

Course Modules And Objectives

Working with Query Fundamentals

  • Querying Data with the SELECT Statement
  • Sorting and Filtering Data with the WHERE clause
  • Use the DISTINCT statement
  • Work with NULL values
  • Use the LIKE clause
  • Work with Wildcard characters
  • Use the BETWEEN clause
  • Use the ORDER BY clause

Querying Multiple Tables

  • Understand Joins
  • Work with INNER JOINS
  • Work with OUTER JOINS
  • Understand types of OUTER JOINS
  • Work with CROSS JOINS
  • Retrieve data from multiple tables
  • Work with Self Joins

Implementing Aggregate Queries

  • Understand Aggregate functions
  • Use the GROUP BY clause
  • Modify data in tables
  • Work with the WITH ROLLUP Operator
  • Work with the WITH CUBE Operator
  • Work with Grouping Sets
  • Understand the HAVING clause

Combine and Compare Datasets

  • Understand the Fundamentals of Combining Datasets
  • Use the UNION operator
  • Use the EXCEPT operator
  • Use the INTERSECT operator
  • Use the APPLY operator
  • Understand Built-in Scalar Functions
  • Work with the DATE and TIME functions
  • Use the SYSTEM functions
  • Use the STRING functions

Working with Transactions and Locking

  • Understand the fundamentals of Transactions
  • List the properties and types of Transactions
  • Work with Implicit Transactions
  • Use the COMMIT and ROLLBACK statements
  • Work with Explicit Transactions
  • Understand different database locking approaches
  • Understand the Deadlock scenario
  • Set Transaction Isolation Levels

Modifying Data

  • Use the INSERT statement
  • Use the UPDATE statement
  • Use the DELETE statement
  • Use the TRUNCATE TABLE statement
  • Modify Data using the MERGE Statement
  • Use the OUTPUT clause

Working with Data Types and Creating Tables

  • Understand Data types
  • Understand Schemas
  • Work with Identifiers
  • Choose the right Data Types
  • Work with NULL or NOT NULL values
  • Understand the IDENTITY property
  • Create a Table
  • Compress Rows and Pages to reduce storage space

Implementing Data Integrity

  • Understand the fundamentals of Data Integrity
  • Define Declarative Data Integrity
  • Implement Data Integrity
  • Use the PRIMARY KEY constraint
  • Use the UNIQUE constraint
  • Use the FOREIGN KEY constraint
  • Use the CHECK constraint
  • Use the DEFAULT constraint

Using Advanced Query Techniques

  • Define Common Table Expressions
  • Work with Common Table Expressions
  • Understand how Sub-queries work
  • Work with Sub-queries
  • Understand how to rank rows with functions.
  • Use the ROW_NUMBER function
  • Use the RANK function
  • Use the DENSE RANK function
  • Use the NTILE function

Using Programming Objects

  • Understand how Variables are used
  • Work with Variables
  • Understand how to use Stored Procedures
  • Work with Stored Procedures
  • Work with the following Control Flow statements
    • BEGIN…END
    • IF…ELSE
    • WHILE…BREAK/CONTINUE
    • RETURN
    • WAITFOR
    • GOTO
  • Understand the use of Cursors
  • Work with Cursors
  • Understand and work with User-Defined Functions

Working with Triggers and Views

  • Understand Triggers
  • Work with DML Triggers
  • Work with DDL Triggers
  • Work with Logon Triggers
  • Understand Views
  • Create Regular Views
  • Create Indexed Views
  • Create Partitioned Views
  • Modify Views
  • Rename Views
  • Modify Data in Views

Improving Query Performance

  • Understand the principles of Query Performance
  • Measure Query Performance
  • Understand what is the Query Cost
  • Understand what is the Query Execution Time
  • Optimize Query Performance
  • Tune Query Performance
  • Understand Effects of Query Components on Query Cost

Working with Indexes and Partitions

  • Understand the Index Structure
  • Work with Indexes
  • Use the DISTINCT statement
  • Work with NULL values
  • Use the LIKE clause
  • Work with Wildcard characters
  • Use the BETWEEN clause
  • Use the ORDER BY clause

Working with XML in the database

  • Understand XML
  • Understand the use of XML in SQL Server
  • Retrieve Table Data as XML
  • Work with XML Data
  • Use the XML Data Type
  • Work with XML Stored in an XML Variable or Column
  • Retrieve data stored in an XML column by using:
    • Exist
    • Value
    • Nodes
    • Query

Working with SQLCLR and FILESTREAM

  • Understand SQLCLR
  • Create Objects using SQLCLR
  • Compile a Common Language Runtime (CLR) Object
  • Load and Use the compiled assembly in SQL Server
  • Describe the Permission Sets in SQLCLR
  • Understand the fundamentals of FILESTREAM
  • Use the FILESTREAM feature

Using Additional Functionality

  • Understand what are Spatial Data Types
  • Work with Geometry Data Types
  • Work with Geography Data Types
  • Understand what are Full Text Indexes
  • Work with the Full Text Index Thesaurus File and Stop List
  • Work with CONTAINS and FREETEXT
  • Use Full-Text Search
  • Troubleshoot Full-Text Search

Implementing the Service Broker

  • Understand the Service Broker
  • Understand the Service Broker Components
  • Work with the Service Broker
  • Understand the Service Broker Applications
  • Enable the Service Broker
  • Configure the Service Broker Components
  • Send and Receive Messages

Using SQL Server Manageability Features

  • Understand Database Mail
  • Configure Database Mail
  • Send Database Mail messages
  • Manage Database Mail
  • Understand Windows PowerShell
  • Execute SQL Server PowerShell cmdlets

Tracking Data Changes

  • Understand how to Manage Changes to Data
  • Work with Change Tracking
  • Enable Change Tracking at the Database Level
  • Enable Change Tracking at the Table Level
  • Use Change Tracking Functions
  • Understand the fundamentals of Change Data Capture
  • Work with Change Data Capture
  • Microsoft Test Preparation

Post a Comment

Your email is kept private. Required fields are marked *

Enquire Now

And receive our free ebook on SQL Joins

  • This field is for validation purposes and should be left unchanged.
×