Sql - Beyond The Basics

Go beyond simple database creation and queries into subqueries, variables, functions and more.

What you'll learn
create and interact with SQL databases at an intermediate to advanced level
utilize subqueries, variables, custom functions and more
Requirements
a working knowledge of SQL fundamentals
Description
In this SQL - Beyond the Basics training course, expert author Guy Vaccaro will dive deeper into SQL and covers advanced techniques. This course is designed for users that already have experience with SQL. You will start by learning a variety of useful skills, including how to convert datatypes, create a temporary table, and declare and use variables. From there, Guy will teach you about sub queries, the CASE statement, and numbering and ranking. This video tutorial also covers using built in functions, creating and using your own functions, and creating views. Finally, you will learn about stored procedures, such as how to create and run a stored procedure, loop with WHILE and IF, and dynamically build SQL statements. Once you have completed this computer based training course, you will have expanded your skills and be able to make more efficient use of SQL.

Overview

Section 1: The Warm Up

Lecture 1 Important - Download These First - Working Files

Lecture 2 About This SQL Advanced Title

Lecture 3 Relational Databases And Key Terms Explained

Lecture 4 Recap Of SQL SELECT Command

Lecture 5 Overview Of The Other DML Statements

Lecture 6 How To Access Your Working Files

Lecture 7 Installing The Sample Data And Using The Working Files

Section 2: Useful Skills

Lecture 8 Converting Datatypes

Lecture 9 Creating A Temporary Table

Lecture 10 The DISTINCT And TOP Keywords In Action

Lecture 11 Indexing To Speed Up Your Life

Lecture 12 Declaring And Using Variables

Section 3: The Subquery

Lecture 13 What Is A Subquery?

Lecture 14 Subquery Vs Joins

Lecture 15 Using Correlated Subqueries

Lecture 16 Using The EXISTS Keyword

Lecture 17 Finding Duplicate Records

Section 4: The CASE Statement

Lecture 18 Getting Started With The CASE Statement

Lecture 19 Using The CASE Statement With Criteria On Multiple Columns

Lecture 20 Multiple Criteria Within The CASE Statement

Lecture 21 CASE In WHERE, ORDER BY, And GROUP BY Clauses

Lecture 22 CASE In UPDATE And INSERT Statements

Lecture 23 CASE Within Subqueries

Section 5: Numbering And Ranking

Lecture 24 Numbering Or Ranking Records In A SELECT Statement

Lecture 25 Filtering Results Based On Numbering Ranking

Lecture 26 Using Numbering Within Groups

Lecture 27 Filtering A Ranked Group To Determine Top N

Section 6: Functions

Lecture 28 Using Built In Functions

Lecture 29 Creating And Using Your Own Functions (UDF)

Section 7: Views

Lecture 30 Creating A View

Lecture 31 Example Views And Views In Views

Lecture 32 Using The UNION Clause

Section 8: Stored Procedures

Lecture 33 What Is A Stored Procedure?

Lecture 34 Creating A Stored Procedure

Lecture 35 Running A Stored Procedure

Lecture 36 Stored Procedures Within Stored Procedures

Lecture 37 The Global Variables @@Identity And @@Rowcount

Lecture 38 Looping With WHILE And IF

Lecture 39 Dynamically Build SQL Statements

Lecture 40 TRY And CATCH For Error Trapping

Lecture 41 Cursors In Use - Part 1

Lecture 42 Cursors In Use - Part 2

Lecture 43 Use Of Transactions

Section 9: Conclusions

Lecture 44 Wrap Up

students who have taken the introductory SQL course or who have a basic understanding of SQL