Skip to main content

Database Administration and SQL Language Basics

Course

Intro Video

Photo of Matthew Pearson

Matthew Pearson

Linux Training Architect II in Content

I recently joined Linux Academy as a Linux Training Architect. I have been a linux engineer for many years with a focus in middleware and highly available applications. I posses several linux certifications, including the Red Hat Certified Engineer. I am excited to be a part of this team and to help students grow and succeed in all areas of IT!

Length

14:33:20

Difficulty

Intermediate

Videos

36

Hands-on Labs

10

Course Details

Welcome to the Database Administration and SQL Language Basics course! In this course, we will be using MySQL to learn about administering a database, as well as the basics of the SQL language. The first half of the course will begin with the installation of a MySQL server, then cover common administrative tasks such as creating databases and tables, inserting and viewing data, and running backups for recovery.

We will also cover the different data types that are allowed in MySQL, and wrap up the administrative side by discussing user access and privileges.

The second half of the course will focus on how to use the SQL language in order to view and manipulate data. This will include creating and deleting various database objects, adding and altering data within tables, and viewing and sorting data. After completing this course, you will have hands-on experience in administering a database know how to use the SQL language in order to manage it.

Syllabus

Introduction

Course Introduction

About the Author

00:00:27

Lesson Description:

Learn a little more about Matthew Pearson, the training architect for this course.

Course Overview

00:04:23

Lesson Description:

In this video, we will go over the topics that will be covered during this course, as well as the tools that are available for your study. The first half of this course will focus on database administration, and the second half will focus on using the SQL language in order to manage a database.

Databases

00:03:29

Lesson Description:

Before we begin discussing relational databases (more specifically, MySQL), we need to understand the basic concepts of a database and a Database Management System. In this video, we will learn about databases and database management systems (DBMS), which includes the various types of DBMS.

Relational Database Management Systems (RDBMS)

00:04:24

Lesson Description:

Relational Database Management Systems are some of the most popular database systems that are used today. This is due to the logical way that data is stored, which allows it to be easily accessible. In this video, we will learn about the different aspects of an RDBMS and see why it has become so popular.

Database Administration

General Administration

MySQL Installation on CentOS

00:09:09

Lesson Description:

The MySQL Server is supported on various platforms, from Windows to Linux. In this lesson, we will learn how to perform a MySQL installation on CentOS 7. Note: Use a Cloud Playground server that is larger than the micro instance available to avoid memory issues when following along in the course. Installation: Download the MySQL RPM bundle tar file from the MySQL Community Server downloads page:

 # wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.15-1.el7.x86_64.rpm-bundle.tar
Extract the RPM files from the MySQL bundle tar file:
 # tar -xvf mysql-8.0.15-1.el7.x86_64.rpm-bundle.tar
Install the MySQL server and dependencies using the rpm command:
 # sudo rpm -Uvh mysql-community-{server,client,common,libs}-*
Start up the MySQL server:
 # sudo systemctl start mysqld
Log into the MySQL server as the root user, using the temporary password located in /var/log/mysqld.log:
 # sudo grep 'temporary password' /var/log/mysqld.log
 # mysql -u root -p
Update the password for the root user:
 mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'PASSWORD';
 mysql> exit

MySQL Installation on Ubuntu

00:07:08

Lesson Description:

The MySQL Server is supported on various platforms, from Windows to Linux. In this lesson, we will learn how to perform a MySQL installation on Ubuntu 18.04. Installation:Download the MySQL APT repository configuration file from the MySQL APT Repository download page:

 # wget https://dev.mysql.com/get/mysql-apt-config_0.8.12-1_all.deb
Install the downloaded release package for the MySQL server. In the package configuration window, ensure that MySQL Server and Cluster are set to mysql-8.0, and that the MySQL Tools and Connectors is Enabled then hit OK:
 # sudo dpkg -i mysql-apt-config_0.8.12-1_all.deb
Resynchronize the package index files:
 # sudo apt update
Install the MySQL server and dependencies. When prompted, enter a password, then select Use Strong Password Encryption (RECOMMENDED):
# sudo apt install -y mysql-server
Validate that the MySQL server is running, and then log in using the password created in step 4:
 # sudo systemctl status mysql
 # mysql -u root -p
 mysql> exit

Creating Databases

00:07:53

Lesson Description:

In MySQL Server, a database is used to store tables and all the associated files which correspond to the table. In this lesson, you will learn how to create and drop databases from within the MySQL server and the Linux command line.

Creating Tables

00:10:33

Lesson Description:

Tables consist of columns and rows. The columns allow you to specify what type of data is acceptable for each given entry (row) within the table. In this lesson, you will learn how to create, clone, and drop tables.

Inserting, Viewing, and Deleting Data

00:14:31

Lesson Description:

MySQL provides the ability to store and view data from the database tables on the server. In this lesson, we will learn how to insert data into tables using the INSERT statement, view and sort data using the SELECT statement, and then delete data using the DELETE statement.

Connectors and APIs

00:03:00

Lesson Description:

MySQL offers the ability for client programs to connect to the MySQL server and execute statements. In this lesson, we will learn about the various connectors and APIs that MySQL supports.

Constraints

00:04:11

Lesson Description:

A constraint in MySQL is used to allow or restrict the type of data that can be inserted into a particular column. In this video, we will learn about each of the different constraints in MySQL and how they are used.

Backups and Recovery

00:15:04

Lesson Description:

Every good disaster recovery plan should include regular backups of servers and data. In this lesson, we will learn how to create full and partial backups of the MySQL server using the mysqldump utility, as well as SELECT statements with the INTO OUTFILE clause.

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.

01:00:00

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.

01:00:00

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.

01:00:00

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:45:00

Data Types

Numeric Types

00:04:46

Lesson Description:

MySQL supports all of the standard SQL numeric data types. This includes exact numeric data types such as INTEGER and DECIMAL, as well as approximate numeric data types like FLOAT. In This lesson, we will learn about each of the various numeric data types.

Date and Time Types

00:08:17

Lesson Description:

In this lesson we will learn about the various data types that are used to store date and time values. This includes data types for only storing the date or time, such as the DATE and TIME data types, as well as those for storing both the date and time such as DATETIME and TIMESTAMP.

String Types

00:05:51

Lesson Description:

MySQL supports string data types for characters (i.e., CHAR), binary (i.e., BINARY), as well as sets of values (i.e., SET). In this lesson, we will learn about the various types of string data types and how they differ.

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.

01:15:00

Stored Objects

Views

00:05:34

Lesson Description:

Views allow you to define a set of results based on tables and other views. In this lesson, we will learn about the benefits and restrictions on views, as well as how they are created.

Triggers

00:08:05

Lesson Description:

A trigger is a named database object that is associated with a particular table, and performs an action whenever a defined event occurs on that table. In this lesson, we will learn more about triggers and how to create them within MySQL.

Stored Routines

00:07:20

Lesson Description:

Stored routines consist of procedures and functions. In this lesson, we will learn how to create stored routines and how to invoke them in the MySQL server.

Events

00:04:51

Lesson Description:

An event is a task containing one or more SQL statements that is created to run on a schedule or at certain time. In this lesson, you will learn how to create and modify events and then how to delete them.

User Access

Creating and Deleting Users

00:06:46

Lesson Description:

MySQL users provide login credentials and determine the host from which a connection can originate. In this lesson, we will learn how to create database users and how to allow access from a remote host.

Granting Privileges

00:14:36

Lesson Description:

Privileges are used to grant users access to objects within the MySQL server, such as databases and tables. In this lesson, you will learn how to grant privileges to users in order to provide specific access.

Creating and Assigning Roles

00:10:34

Lesson Description:

A role is a named collection of privileges that can be assigned to users in the MySQL server. In this lesson, you will learn how to create roles, grant privileges to those roles, and then assign those roles to users.

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.

01:00:00

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.

01:00:00

SQL Language Basics

Modifying Data

Creating a Table with Constraints

00:07:08

Lesson Description:

Constraints allow you to specify certain conditions that must be met in order for data to be inserted into columns. In this lesson, you will learn how to create tables and set constraints on the columns within them.

Inserting

00:05:49

Lesson Description:

The INSERT statement allows you to insert data into tables within the database. In this lesson, we will learn how to properly use the INSERT statement to add data.

Updating

00:04:19

Lesson Description:

The UPDATE statement allows you to alter data that's already in a database. In this lesson, we will learn how to properly use the UPDATE statement.

Altering Tables Part 1

00:08:59

Lesson Description:

Columns and their definitions are normally specified during the CREATE TABLE statement. But what happens if the table needs to be modified after it's created? Well, this is where the ALTER TABLE statement is needed. In this lesson, we will be discussing ALTER TABLE and seeing how it can be used for adding and removing columns, as well as modifying the definitions of existing columns.

Altering Tables Part 2

00:08:26

Lesson Description:

Columns and their definitions are normally specified during the CREATE TABLE statement. But what happens if the table needs to be modified after it's created? Well, this is where the ALTER TABLE statement is needed. In this lesson, we will be discussing ALTER TABLE and seeing how it can be used for adding and removing columns, as well as modifying the definitions of existing columns.

Deleting

00:05:56

Lesson Description:

Removing records from a table is a regular task for database administrators. In this lesson, we will learn how to use the DELETE statement to remove individual rows, or all rows, from a table and how to remove all rows from a table using TRUNCATE.

Dropping

00:06:18

Lesson Description:

There are many objects that are created within MySQL, and sometimes it is necessary to remove them. In this lesson, we will learn how to use the DROP statement in order to remove various database objects, including whole databases, tables, and indexes.

Creating an Index

00:07:30

Lesson Description:

Indexes in MySQL allow data to be accessed more quickly, and queries to be executed more efficiently. In this lesson, we will learn how to create indexes on one or more columns, and how to display existing indexes.

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.

01:00:00

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.

01:00:00

Querying and Filtering Data

Selecting

00:05:57

Lesson Description:

The SELECT statement is the primary means of querying and displaying data within a database. In this lesson, we will learn how to perform a SELECT statement to retrieve one or more columns. We'll also use functions to gain some additional information about the data we're seeing in the SELECT results.

Clauses and Operators

00:08:06

Lesson Description:

MySQL allows the use of clauses and operators, in order to query specific records within the database. In this lesson, we will learn how to use the WHERE clause, comparison functions and operators, and logical operators.

GROUP BY and ORDER BY

00:08:46

Lesson Description:

MySQL provides the ability to aggregate result sets by using the GROUP BY clause, and also to order results using the ORDER BY clause. In this video, we will discuss each of the clauses and show examples of how they can be used in order to arrange a particular data result set.

Unions

00:06:56

Lesson Description:

In MySQL, using UNION allows you to link together multiple SELECTs in the same statement, in order to return a single data set that contains each of the SELECT statements. In this video, we will learn about unions and how to use them to produce a desired result set.

Joins

00:09:30

Lesson Description:

Using a JOIN clause allows us to query data from multiple tables and combine it into a single result set. In this video, we will learn about the different JOIN clauses in MySQL: CROSS, INNER, LEFT, and RIGHT.

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.

01:15:00

Final Steps

What's Next

00:03:31

Lesson Description:

Congratulations on completing the Database Administration and SQL Language Basics course! In this video, I will go over some suggestions and recommendations on courses that you might want to take, now that you have completed this course.

Take this course and learn a new skill today.

Transform your learning with our all access plan.

Start 7-Day Free Trial