Practical SQL Skills from Intermediate to Advanced
4.5 (56 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
679 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Practical SQL Skills from Intermediate to Advanced to your Wishlist.

Add to Wishlist

Practical SQL Skills from Intermediate to Advanced

Improve your SQL skills and step up to the next level to learn more and take full command of SQL
4.5 (56 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
679 students enrolled
Created by Gary Kind
Last updated 1/2016
English
Current price: $10 Original price: $30 Discount: 67% off
5 hours left at this price!
30-Day Money-Back Guarantee
Includes:
  • 4 hours on-demand video
  • 1 Supplemental Resource
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Understand more advanced SQL Server features
  • How to apply these techniques to real world problems
  • Enhance your SQL Skills
View Curriculum
Requirements
  • A good understanding of basic SQL
  • Microsoft SQL Server Express (free download and the course explains everything)
Description

This course is designed to further your understanding of structured query language (SQL) for manipulating business data.

Once you have the basics mastered you can take your knowledge to the next level to fully harness the power of Microsoft SQL Server as well as look at how to maximise the performance of any SQL that you write.

The course contains walk through videos, printed documents of the course material as well as links to resources you need to be able to carry out the course.

The course does assume a good working knowledge of SQL to get the most out of this course. If you want an introductory course then I have also authored a course for beginners (https://www.udemy.com/practical-sql-skills-from-beginner-to-intermediate/).

If you need to access any data be it online or within your own company then this course is for you. Financial systems form the hub of any business these days and many job roles require some level of SQL ability. This course will equip you with the knowledge you need to perform these tasks within your business or employment to improve your prospects.

Most business systems utilise some form of SQL database, typically Microsoft SQL Server which is why this course focuses on that particular product. Additionally we can download a free 'express' version of the product to work through the course.

Who is the target audience?
  • Those looking to build upon their basic SQL skills
  • Employees looking to increase their knowledge and add value to a business
  • Business owners looking to full harness the data within their business
Students Who Viewed This Course Also Viewed
Curriculum For This Course
35 Lectures
03:48:40
+
Introduction
7 Lectures 23:33

Introduction to the course.

Preview 04:24

Run OS X on an iMac or Macbook? Can you run MSSQL on it? Well Yes and No, this video explores your options.

Preview 01:07

Where to download Microsoft SQL Server 2008 Express edition and sample data. Be sure to check out the Links document which has all the download links.

Preview 02:36

We'll go through the installation process for getting MSSQL setup on your machine.

Preview 02:06

I have generated a custom dataset called 'Nile Trading' which has millions of rows. We can use this to see the effect of any optimisations.

Installing Our Large Dataset (Nile Trading) Database
03:38

We are using a large dataset which was generated using a tool, here we look at that tool.

Generating a Large Amount of Data
02:23

Here we look at standards in terms of style and coding convention. Not necessarily the 'correct' way but why I do what I do and hopefully makes everything easier and more readable for everyone!

Coding and Style Conventions
07:19
+
Additional JOINs
2 Lectures 09:05

You can join the same table to itself. Useful when a table contains self-referencing keys, here we look at one such scenario.

Self JOINs
03:32

CROSS Joins
05:33
+
Modifying Data
3 Lectures 12:43

How to add multiple records using INSERT with SELECT.

INSERT with SELECT
06:57

When doing an UPDATE that relies on a value in a another table we need to do an UPDATE with a JOIN, this video explains how.

UPDATE with JOIN
04:08

The DELETE with JOIN is similar to the update with join and here is an example.

DELETE with JOIN
01:38
+
Importing Data
2 Lectures 26:48
Import data from CSV
11:41

MERGE is a powerful statement that combines INSERT, UPDATE and DELETE into one command. Here we look at using it to transfer data from our staging table to our live table.

Using MERGE Statement
15:07
+
Aggregation
2 Lectures 11:24

CUBE and ROLLUP allow us to add sub-totalling and totalling to our result sets. Here we examine them.

ROLLUP and CUBE
06:41

Grouping Sets allow us to control what sub-totals appear on the report, its a more flexible approach than cube or rollup.

GROUPING SETS
04:43
+
Indexes, Keys and Constraints
3 Lectures 18:02

Here we take a look at clustered and non-clustered indexes.

Indexing and Index Types
07:47

This video explains what primary and foreign keys are and their purpose.

Primary and Foreign Keys
05:14

Constraints are used to restrict the allowable values within a column. This video explains a couple of different ways of using constraints and using them to provide default values.

Constraints
05:01
+
Views, Common Table Expressions and Windowed Functions
5 Lectures 31:26

Here we look at how to create a VIEW and why you would use one.

VIEWs
05:13

Here we look at common table expressions, or CTEs starting with a simple example.

Common Table Expressions Part 1
05:48

A typical use case for a cte is when navigating hierarchical data. This lecture shows an example of this.

Common Table Expressions Part 2
08:11

We look at row numbering and how to rank data.

RANK and ROWNUMBER
08:13

NTILE is used to segment data, here we look at an example.

Deciles and Quartiles with NTILE
04:01
+
Triggers and Temporary Tables
2 Lectures 25:54

We look at creating 3 triggers to add business logic to our database.

Introducing Triggers
19:37

SQL Server supports the concept of 'temporary' tables. Here we explain what they are.

Temporary Tables
06:17
+
TransactSQL for Stored Procedures and Functions
4 Lectures 39:16

An introduction to TransactSQL.

Writing TransactSQL Part 1
04:59

Here we look at transactions and how do handle errors in TRY..CATCH

Writing TransactSQL Part 2 (transactions and try/catch)
10:09

SQL supports table and scalar value functions, here we look at table based functions.

Table Functions
06:37

Scalar functions return a single value and can be used as a column in a SELECT query. Here we go through an example of this this type of function.

Scalar Functions
17:31
+
Performance Tuning
3 Lectures 25:41

We look at some basic ways to optimise the performance of our queries and dive into execution plans.

Basic Optimisation Tips
10:24

Some more on optimisation including covering indexes and 'dirty reads'

More Optimisation
07:03

How we can create a physical (materialised) view instead of a view which is a virtual table.

Indexed Views
08:14
1 More Section
About the Instructor
Gary Kind
4.2 Average rating
898 Reviews
41,705 Students
12 Courses
Developer, Author, Marketeer

Gary Kind is a highly experienced IT professional and business owner with over 25 years in and around software development and 20+ years running businesses as an individual and in partnerships.

He has a wide spread of business knowledge working in the SME sector as well as delivering KPI and financial reporting for large corporations. He has spent the past few years combining marketing with IT to cover areas such as pay per click, copywriting and conversion optimisation.