by | Released January 2015 | ISBN: 9781771373449

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. Working files are included, allowing you to follow along with the author throughout the lessons. Show and hide more Publisher resources Download Example Code

1. The Warm Up
   • About This SQL Advanced Title 00:05:00
   • About The Author 00:01:48
   • Relational Databases And Key Terms Explained 00:07:46
   • Recap Of SQL SELECT Command 00:07:39
   • Overview Of The Other DML Statements 00:05:30
   • Installing The Sample Data And Using The Working Files 00:03:05
2. Useful Skills
   • Converting Datatypes 00:07:59
   • Creating A Temporary Table 00:06:03
   • The DISTINCT And TOP Keywords In Action 00:05:21
   • Indexing To Speed Up Your Life 00:08:00
   • Declaring And Using Variables 00:06:50
3. The Subquery
   • What Is A Subquery? 00:06:01
   • Subquery Vs Joins 00:04:59
   • Using Correlated Subqueries 00:07:05
   • Using The EXISTS Keyword 00:04:47
   • Finding Duplicate Records 00:05:45
4. The CASE Statement
   • Getting Started With The CASE Statement 00:06:35
   • Using The CASE Statement With Criteria On Multiple Columns 00:05:34
   • Multiple Criteria Within The CASE Statement 00:06:11
   • CASE In WHERE, ORDER BY, And GROUP BY Clauses 00:04:50
   • CASE In UPDATE And INSERT Statements 00:06:33
   • CASE Within Subqueries 00:06:05
5. Numbering And Ranking
   • Numbering Or Ranking Records In A SELECT Statement 00:06:21
   • Filtering Results Based On Numbering Ranking 00:05:54
   • Using Numbering Within Groups 00:04:45
   • Filtering A Ranked Group To Determine Top N 00:05:18
6. Functions
   • Using Built In Functions 00:07:35
   • Creating And Using Your Own Functions (UDF) 00:05:08
7. Views
   • Creating A View 00:05:20
   • Example Views And Views In Views 00:05:06
   • Using The UNION Clause 00:06:24
8. Stored Procedures
   • What Is A Stored Procedure? 00:08:04
   • Creating A Stored Procedure 00:05:34
   • Running A Stored Procedure 00:04:56
   • Stored Procedures Within Stored Procedures 00:05:28
   • The Global Variables @@Identity And @@Rowcount 00:08:06
   • Looping With WHILE And IF 00:06:59
   • Dynamically Build SQL Statements 00:07:44
   • TRY And CATCH For Error Trapping 00:04:30
   • Cursors In Use - Part 1 00:06:33
   • Cursors In Use - Part 2 00:05:31
   • Use Of Transactions 00:06:42
9. Conclusions
   • Wrap Up 00:06:37
Show and hide more