Skip to main content

SQL Deep Dive

Course

Intro Video

Photo of Landon Fowler

Landon Fowler

Database Training Architect II

Length

03:33:28

Difficulty

Intermediate

Videos

28

Hands-on Labs

3

Course Details

SQL is a powerful language for data retrieval and manipulation. Utilized in databases of every size, it is an industry standard for working with data. In this course, we are going to discover what SQL is and how to leverage it. This includes selecting, updating, and deleting information, as well as creating and altering database objects.

Familiarity with the SQL language is an asset to your career, whether you're a programmer, sys admin, DBA, or analyst. And it's actually quite fun! I look forward to diving into SQL with you.

https://interactive.linuxacademy.com/diagrams/SQLDeepDive.html

Syllabus

Course Introduction

Getting Started

Course Introduction

00:01:46

Lesson Description:

Welcome to the SQL Deep Dive course! I'm excited to go on this journey with you. In this video, we discuss the topic at hand and some of what to expect from the course. Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!

About the Training Architect

00:00:47

Lesson Description:

Hi, I'd like to introduce myself! In this video, I give you a very quick synopsis of who I am and what my background is. It's nice to meet you! Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!

Environment Setup

Database Setup

Creating an Azure SQL Database

00:04:18

Lesson Description:

In this video, we set up our sample database environment that will be used throughout the course. We utilize an Azure SQL Database preloaded with the AdventureWorksLT sample database to accomplish this. Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!

Client Setup

Setting Up Visual Studio Code

00:03:07

Lesson Description:

In this video, we set up our local client to connect to the previously deployed Azure SQL Database. We standardize on Visual Studio Code as a cross-platform client, but we discuss other available options as well. Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!

Introduction to SQL

Database Basics

What Is an RDBMS?

00:03:43

Lesson Description:

Before jumping into the details of SQL syntax, let's step back and take a moment to lay a foundation about relational databases. A greater understanding of database structure positions someone to more effectively use the SQL language. Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!

What Is ANSI SQL (and What Is It Not)?

00:02:44

Lesson Description:

The term SQL gets thrown around a lot, and it can be confusing to tell exactly what people mean. In this video, we take a brief moment to explain exactly what ANSI SQL is and differentiate that from the implementations derived from it. Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!

Working with Data

Retrieving Data (Basic)

Selecting

00:03:36

Lesson Description:

We made it! Time to get started working with the SQL language. In this video, we introduce the SELECT statement, our go-to method for retrieving data. Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!

`WHERE` Clauses

00:05:12

Lesson Description:

Now that we know how to pull data using SELECT, we discuss how to filter that data with a WHERE clause. This essential SQL component is invaluable as we begin to build queries. Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!

`HAVING` Clauses

00:02:11

Lesson Description:

In situations where aggregate functions are involved, a WHERE clause cannot be used. In this video, we look at how its cousin, the HAVING clause, resolves this dilemma. Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!

Sorting Results

00:02:49

Lesson Description:

Now that we have the results we want, we talk about how to sort them into the desired order. Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!

`NULL` Values

00:02:18

Lesson Description:

What is the difference between NULL and zero? They are not the same, and we take a brief look at why. Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!

Retrieving Data (Advanced)

Selecting Ranges

00:06:54

Lesson Description:

In this video, we take a look at how to select a particular number of rows. We also use aggregate functions to calculate sets of values. We combine this with the GROUP BY clause to maximize the effectiveness of those functions. Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!

Fuzzy Lookups

00:03:27

Lesson Description:

In this video, we discover how to search for rows where we only know part of the value. We do this by using pattern matching and wildcards. Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!

`WHERE` Operators

00:03:03

Lesson Description:

Let's expand upon our WHERE abilities by adding in operators. In this video, we look at using IN and BETWEEN to improve WHERE conditions. Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!

Aliases

00:07:33

Lesson Description:

In this video, we look at how to make our query results more appealing and the queries themselves easier to write. We do this through the use of aliases. Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!

Joins

00:07:56

Lesson Description:

Data is better when it's together! Let's take an introductory look at JOIN, the mechanism by which we connect different tables together and associate their information. Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!

Joins (Advanced)

00:08:04

Lesson Description:

Now that we've covered the basics of JOIN, let's talk about how to do more with joins and also some similar operations for bringing data together. Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!

Logic Conditions

00:02:46

Lesson Description:

In this video, we look at how to implement logic conditions in our queries using the CASE statement. Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!

Hands-on Labs are real live environments that put you in a real scenario to practice what you have learned without any other extra charge or account to manage.

00:30:00

Changing Data

Inserting

00:06:33

Lesson Description:

In order to query data, we first have to place it in the database! In this video, we look at the INSERT statement as a primary mechanism for entering data into our database. Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!

Updating

00:03:38

Lesson Description:

Now that we have data in our database, let's talk about how to alter it. Using the UPDATE statement, we edit existing rows. Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!

Deleting

00:03:12

Lesson Description:

Sometimes it's necessary to remove rows from our database altogether. In this video, we look at the DELETE and TRUNCATE statements, how they differ, and when it's best to use each. Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!

Hands-on Labs are real live environments that put you in a real scenario to practice what you have learned without any other extra charge or account to manage.

00:30:00

Managing Our Database

Manipulating Objects

Database

00:02:06

Lesson Description:

In this video, we take a look at how to CREATE and DROP databases using SQL syntax, as well as briefly discuss using BACKUP. Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!

Table

00:07:52

Lesson Description:

In this video, we take a look at how to create and manage tables with SQL syntax. We also discuss some table design strategy and the importance of using this statement to structure your data well. Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!

Index

00:04:41

Lesson Description:

Indexes are a powerful way to improve the performance of our queries. In this video, we take a look at the syntax for working with them and also discuss strategies for using them well. Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!

View

00:03:27

Lesson Description:

Database views open up new possibilities to us and our end users for querying data. In this video, we briefly discuss what a view is and how to manage them using SQL syntax. Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!

Hands-on Labs are real live environments that put you in a real scenario to practice what you have learned without any other extra charge or account to manage.

00:30:00

Safeguarding Our Database

SQL Injection

00:03:27

Lesson Description:

A common abuse of SQL comes in the form of SQL Injection. In this video, we take a look at what that is and what we can do to prevent it. Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!

Performance

00:14:28

Lesson Description:

Before we close out our discussion on SQL, let's take a bottom-to-top look at database configuration with an eye for performance. Investing extra effort at each level helps make our overall database strategy efficient and successful. Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!

Conclusion

Final Steps

What's Next

00:01:36

Lesson Description:

It's done! Hail the conquering hero! Congratulations on completing the SQL Deep Dive course. In this video, we look at some paths to take from here to continue the learning journey. Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)! Thanks for joining us, and never stop learning!