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.