MySQL Introduction

Hours: 32 / Access Length: 180 Days / Delivery: Online, Mentor Supported

Course Overview:

This course will give students the skills they need to set up and manage a MySQL database. Students will learn the basics of designing a relational database and learn to use SQL statements to create databases, tables and manage data in the data tables. Students will create a MySQL database and will identify the types of data that can be stored in data tables. Students will use the PHPMyAdmin tool to view and manage their MySQL databases. Students will learn to write applications which use MySQL databases.

Students will:

• Install MySQL and MySQL Workbench.
• Write queries to retrieve data from one table.
• Write queries to retrieve data from more than one table.
• Write queries that summarize data.
• Write queries that contain sub queries.
• Write queries to insert, delete and update data
• Write queries that convert from one data type to another and use functions to work with data.
• Design a database
• Will write SQL statements to create a database.
• Create a final project using what they learned in the course.

Course Outline:

Lesson 1: An Introduction to Relational Databases

This chapter presents the concepts and terms that you should understand before you begin learning how to work with a SQL database such as MySQL. Although this chapter doesn’t present the coding details, it does present an overview of the most important types of SQL statements that are presented in this book.

Lesson 2: How to Retrieve Data from a Single Table

In this chapter, you’ll learn how to code SELECT statements that retrieve data from a single table. The skills covered here are the essential ones that apply to any SELECT statement you code...no matter how many tables it operates on, no matter how complex the retrieval. So you’ll want to be sure you have a good understanding of the material in this chapter before you go on to the chapters that follow.

Lesson 3: How to Retrieve Data from Two or More Tables

In the last chapter, you learned how to create result sets that contain data from a single table. Now, this chapter shows you how to create result sets that contain data from two or more tables. To do that, you can use an inner join, an outer join, or a union.

Lesson 4: How to Insert, Update, and Delete Data

In the last two chapters, you learned how to code the SELECT statement to retrieve and summarize data. Now, you’ll learn how to code the INSERT, UPDATE, and DELETE statements to modify the data in a table. When you’re done with this chapter, you’ll know how to code the four statements that are used every day by professional application developers.

Lesson 5: How to Code Summary Queries

In this chapter, you’ll learn how to code queries that summarize data. For example, you can use summary queries to report sales totals by vendor or state. Similarly, you can use summary queries to get a count of the number of invoices that were processed each day of the month. But first, you’ll learn how to use a special type of function called an aggregate function. Aggregate functions allow you to do jobs like calculate averages, summarize totals, or find the highest value for a given column, and you’ll use them in summary queries.

Lesson 6: How to Code Subqueries

Subqueries allow you to build queries that would be difficult or impossible to build otherwise. In chapter 5, you learned how to use them in INSERT, UPDATE, and DELETE statements. In this chapter, you’ll learn how to use subqueries in SELECT statements.

Lesson 7: How to Work with Data Types

So far, you have been using SQL statements to work with the three most common types of data: strings, numbers, and dates. Now, this chapter takes a more in-depth look at the data types that are available with MySQL and shows some basic skills for working with them. When you complete this chapter, you’ll have a thorough understanding of the data types, and you’ll know how to use some functions to convert one data type to another.

Lesson 8: How to Use Functions

In chapter 3, you were introduced to some of the scalar functions that you can use in a SELECT statement. Now, this chapter expands on that coverage by presenting many more of the scalar functions, as well as some specialized window functions. When you complete this chapter, you’ll have a thorough understanding of the functions that you can use with MySQL.

Lesson 9: How to Design a Database

In this chapter, you’ll learn how to design a new database. This is useful information whether or not you ever design a database on your own. To illustrate this process, you’ll use the accounts payable (AP) database that you’ve seen throughout this book.

Lesson 10: How to Create Databases, Tables, and Indexes

Now that you’ve learned how to design a database, you’re ready to learn how to implement your design. To do that, you use the set of SQL statements that are known as the data definition language (DDL). In this chapter, you’ll learn how to use DDL statements or MySQL Workbench to work with databases, tables, and indexes. In addition, you’ll learn how to change the character set, collation, and storage engine if you need to do that.

Lesson 11: How to Create Views

As you’ve seen throughout this book, SELECT queries can be complicated, particularly if they use multiple joins, subqueries, or complex functions. Because of that, you may want to save the queries you use regularly. One way to do that is to store the statement in a script. Another way is to create a view. Unlike scripts, which are stored in files, views are stored as part of the database. As a result, they can be used by SQL programmers and by custom applications that have access to the database. This provides some advantages over using tables directly.

All necessary materials are included.

System Requirements:

Internet Connectivity Requirements:

• Cable, Fiber, DSL, or LEO Satellite (i.e. Starlink) internet with speeds of at least 10mb/sec download and 5mb/sec upload are recommended for the best experience.

NOTE: While cellular hotspots may allow access to our courses, users may experience connectivity issues by trying to access our learning management system.  This is due to the potential high download and upload latency of cellular connections.   Therefore, it is not recommended that students use a cellular hotspot as their primary way of accessing their courses.

Hardware Requirements:

• CPU: 1 GHz or higher
• RAM: 4 GB or higher
• Resolution: 1280 x 720 or higher.  1920x1080 resolution is recommended for the best experience.
• Speakers / Headphones
• Microphone for Webinar or Live Online sessions.

Operating System Requirements:

• Windows 7 or higher.
• Mac OSX 10 or higher.
• Latest Chrome OS
• Latest Linux Distributions
  

NOTE: While we understand that our courses can be viewed on Android and iPhone devices, we do not recommend the use of these devices for our courses. The size of these devices do not provide a good learning environment for students taking online or live online based courses.

Web Browser Requirements:

• Latest Google Chrome is recommended for the best experience.
• Latest Mozilla FireFox
• Latest Microsoft Edge
• Latest Apple Safari

Basic Software Requirements (These are recommendations of software to use):

• Office suite software (Microsoft Office, OpenOffice, or LibreOffice)
• PDF reader program (Adobe Reader, FoxIt)
• Courses may require other software that is described in the above course outline.