SQL Primer

Course

Intro Video

Photo of Anthony James

Anthony James

Founder/CEO

Anthony is the Founder/CEO of Linux Academy, and instructs courses on AWS, DevOps, and Linux. When not creating and maintaining a variety of courses, he is leading the Linux Academy content & code team.

Length

04:14:06

Difficulty

Intermediate

Videos

33

Course Details

The Structured Query Language (or SQL) is how we both define and manipulate data within a traditional Relational Database Management System (or RDBMS). In this course, we are going to learn how to structure SQL statements that do just that. Learn how to create databases, manage access, create tables, insert and update data, create indexes and more. We will demonstrate SQL functions and talk about how they differ from the more traditional SQL statements. All of this will be accomplished at a SQL command line within MySQL, but will apply equally to SQL compliant RDBMS systems in general.

Syllabus

Creation and Setup

In this section we will create our database instance and set it up as well as introduce SQL as a language.

Introduction

00:06:09

Lesson Description:

This is a brief introduction to SQL and the course itself

Create Database

00:14:24

Lesson Description:

Learn how to install a MySQL database and then create one we can use. NOTE: Since the download page referenced in the video has changed, use this command to get the RPM file:wget https://dev.mysql.com/get/mysql80-community-release-el7-2.noarch.rpm

Create Table

00:07:53

Lesson Description:

Now that we have a database, let's create a table we can use to learn with.

Create Table With Constraints

00:07:23

Lesson Description:

Now that we have created a table, let's learn about how we can apply constraints to control what is contained within each column.

Oracle MySQL Installation: Ubuntu

00:06:26

Lesson Description:

Learn how to install Oracle's MySQL Server in Ubuntu

Oracle MySQL Secure Setup: Ubuntu

00:08:01

Lesson Description:

Learn how to secure the MySQL instance in Ubuntu

Oracle MySQL Installation: CentOS

00:07:02

Lesson Description:

Learn how to install Oracle MySQL in CentOS

Oracle MySQL Secure Setup: CentOS

00:12:08

Lesson Description:

Learn how to secure Oracle MySQL instance in CentOS

MariaDB Installation: Ubuntu

00:07:16

Lesson Description:

Learn how to install the MySQL drop in replacement MariaDB in Ubuntu

MariaDB Secure Setup: Ubuntu

00:07:38

Lesson Description:

Learn how to secure the MariaDB instance in Ubuntu

MariaDB Installation: CentOS

00:07:15

Lesson Description:

Learn how to install the MySQL drop in replacement MariaDB server in CentOS

MariaDB Secure Setup: CentOS

00:07:15

Lesson Description:

Learn how to secure MariaDB instance in CentOS

Exercise: Install and Configure MySQL Database Server

00:30:00

SQL Functions and Statements

This is the main section. We will walk through examples covering the major SQL statemetns and functions, pointing out those that are standard SQL compliant as well as mentioning those that are known to be different across different RDBMS.

Insert Into

00:08:55

Lesson Description:

In this video we will learn how to insert records into our tables using SQL.

Select From

00:07:10

Lesson Description:

We have values in our tables now, how do we view their values? Watch this video and find out!

Alter Table

00:08:15

Lesson Description:

Whoops! We made some mistakes, lets learn how to alter our tables and add columns after its been created.

Delete From

00:05:50

Lesson Description:

Now we need to remove some stuff without destroying all of our work.

Create Index

00:06:57

Lesson Description:

Our database may start to suffer from slow downs. Before we go any further, lets talk about indexes.

Drop Table

00:07:07

Lesson Description:

Well, sometimes the only way to get rid of your mistakes is to start over. Let's drop our table or index and start again.

Truncate Table

00:04:40

Lesson Description:

Hang on! Maybe we don't need to start completely over. Let's just dump the data and start with a clean slate.

Auto Increment

00:08:36

Lesson Description:

When developing IDs that you can use for indexing or keys, let's be sure we don't have to keep track!

Count Function

00:07:10

Lesson Description:

Let's start learning about functions in SQL - how to view and grab our data without making any direct changes.

Average and Sum Functions

00:08:20

Lesson Description:

Again, a function that tells us stuff about our data without storing or making any changes

Like Operator

00:09:22

Lesson Description:

Sometimes you don't know what your don't know, so let's figure out how to find information when we only know part of the story.

Create View

00:07:31

Lesson Description:

As much as we might like to live with functions, sometimes you access information enough that you just want to store it for later. Let's create a view into our data that we can treat like a table.

Inner Join

00:08:34

Lesson Description:

Learn about joins in general and the most common one, the Join (sometimes called Inner Join).

Left Join

00:07:59

Lesson Description:

Left Outer Joins show records from the left table even if they do not have a matching relational record in the right.

Right Join

00:07:15

Lesson Description:

Right Joins will show all records from the Right table even if there is no matching relational record in the left table.

Full Join

00:05:02

Lesson Description:

A Full Join will show all records on both the left and right tables regardless of matching relational records in either.

Unions

00:10:02

Lesson Description:

A Union is the concatenation/combination of multiple select statements from one or more tables. Learn also how to use it to generate a full join in those DBs that do not support it directly.

Sorting Records

00:06:31

Lesson Description:

Learn how to sort records in ascending or descending order depending on what and how you want to see the results. Use this to mimc certain SQL functions that are not supported universally.

Minimum and Maximum Values

00:08:15

Lesson Description:

Use built in SQL Functions to determine the maximum and minimum values from within our records sets.

Upper and Lower Case Conversions

00:05:31

Lesson Description:

Again using SQL functions to provide different views of our data without changing any of the underlying values or structures.

Now()

00:06:02

Lesson Description:

See how the Now() function works and how we can use it when creating data views to maintain historical change records for our products table.

Exercise: Create Database, Database User and Tables

00:30:00

Exercise: Creating Tables with Constraints

00:30:00

Exercise: Altering Table Structures

00:30:00

Exercise: Insert and Select Records in a Table

00:30:00

Exercise: Count Records and Execute Functions in a Table

00:30:00

Exercise: Use Joins to View Multi-Table Data

00:30:00

Exercise: Sort Records and Determine Min/Max Values

00:30:00

Exercise: SQL Functions (Average and Sum)

00:30:00