Job Ready SQL

Introduction

Modern computer applications rely heavily on databases, even when the program in question isn't designed to help users manage data. Computer games rely on databases to keep track of characters, character attributes, items that each character can use during gameplay, and even locations within the game. A learning management system (LMS) uses databases to keep track of learners, instructors, content, grades, attendance, and communication between users.

A database can contain data that is structured or unstructured. Modern database software programs hosting databases can usually handle both structured and unstructured data, but it is still good to understand the difference.

In a database with structured data, which we will call a structured database, the data is organized in a specific pattern. This makes it easy to control what data is available and where to find specific pieces of data. In a structured database, the developer can limit what kinds of data are stored in the database to improve data integrity and reduce the amount of redundant data. This comes as a trade‐off in that creating new data and accessing stored data are relatively slow compared to creating and accessing data in an unstructured database. Structured databases are best for datasets that contain predictable types of data, such as bank accounts, personnel records, and inventories.

Relational databases are highly structured in that they organize data into one or more tables or relations, where each table represents a logical group of data. In day‐to‐day professional work, we usually say table. Relation is the formal, academic term, which you may run into if you read about databases in other contexts. Relation is also the basis for the term relational database. At a more abstract level, the term entity is also used to refer to a table, especially during the design phase of a database and before the database is built on a server.

Job Ready SQL provides readers with an understanding of relational databases and Structured Query Language (SQL). SQL is a domain‐specific language designed for managing data held in a relational database management system.

A SQL Course Within a Book

This book contains a full‐fledged SQL course that is used by the Wiley Edge Global Academy and the Software Guild to train our alumni in SQL and other topics, such as data analysis and data science.

Features to Make You Job Ready

As you read through this book, enter the code listings and play with the code. If you also take a hands‐on approach to doing the exercises, you will be better able to take what you learned to the next level.

Most important, this book (as well the Job Ready series) goes beyond what many books provide by including lessons that help you pull together everything you are learning in a way that is more like what you would find in the professional world. This includes building a more comprehensive example than what you get in the standard short listings provided in most books. If you work through the Pulling It All Together lessons, then you will be better prepared for many of those jobs that require SQL.

WHAT DOES THIS BOOK COVER?

As mentioned, this book is a complete SQL programming course. It is broken into several parts, each containing a number of lessons.

Part 1: Introduction to Database Concepts The first part of this book focuses on introducing database concepts including structured and unstructured data, as well as relational database concepts.

Part 2: Applying SQL The second part focuses on getting you set up to use MySQL. This includes help for installing MySQL and setting up the tools you will need to work through this book. Additionally, this section dives into the basics of MySQL including query design and development and the basics of database management.

Part 3: Data Management and Manipulation The third part focuses on going beyond the basics of MySQL and focuses on learning about concepts you'll need to design and develop complex databases and advanced querying of the data stored on MySQL. This includes CRUD operations, joins, select queries, sorting, and aggregation. Finally, this section also includes lessons on how to leverage Python to query SQL data.

READER SUPPORT FOR THIS BOOK

There are several ways to get the help you need for this book.

Companion Download Files

As you work through the examples in this book, you should type in all the code manually. This will help you learn and better understand what the code does.

However, in some lessons, download files are referenced. You can download the files from www.wiley.com/go/jobreadysql.

How to Contact the Publisher

If you believe you have found a mistake in this book, please bring it to our attention. At John Wiley & Sons, we understand how important it is to provide our customers with accurate content, but even with our best efforts an error may occur.

To submit your possible errata, please email it to our Customer Service Team at wileysupport@wiley.com with the subject line Possible Book Errata Submission.

PART I

Introduction to Database Concepts

Lesson 1: Exploring Relational Databases and SQL

Lesson 2: Applying Normalization

Lesson 3: Creating Entity‐Relationship Diagrams

Lesson 4: Pulling It All Together: Normalizing a Vinyl Record Shop Database

Lesson 1

Exploring Relational Databases and SQL

SQL is used to access data. Before jumping into SQL and how it is used, it is important to step back and consider how information that you will access has been stored. In this chapter, you'll dive into the topic of data and databases to set the foundation for then accessing the information. You will get a high‐level look at databases in general and at relational databases specifically.

Learning Objectives

By the end of this lesson, you will be able to:

Describe what a relational database is, how it works, and how it differs from a database management system (DBMS)

Define database tables, relations, columns, attributes, rows, records, tuples, and data types

Identify the ACID properties

Know about entity integrity and uniqueness using keys

Discuss database backup strategies

SAVING DATA

To be useful, software systems must remember. If your character started at the beginning (level 0) every time you fired up a video game, or your online banking app reset your balance to $0 when you logged off, or your phone forgot your contacts when it rebooted, you wouldn't use them. To remember, applications must save data in a way that allows ready access to that data when needed.

There are a few options for saving data.

Write text or bytes directly into a file

Store data in a relational database

Store data in a nonrelational database

The first option of writing directly into a file can be cumbersome. In this case, the file typically is expected to be local (on the same computer as the program that is accessing the file). This means there is a high risk of losing data if something happens to that computer.

In a software environment, databases (relational or not) are preferable to files because they can store data separately from the application itself, often on a completely separate server. While this might slow down access to the data to a small degree, the fact that they are separate means that multiple applications can access the same database and that changes to the data in the database are immediately accessible to any application that uses that data.

Nonrelational databases are becoming more common today, but relational databases are standard across many industries as a way of storing data in a predictable and reliable way that allows applications to easily retrieve that data as needed.

WHAT IS A DATABASE?

In real life, most people work with databases every day, often without realizing it. Most computer applications depend on some kind of data access to work correctly. Any advanced system with a goal of identifying objects and performing specific actions on those objects (such as an employee list, an inventory, or a course roster) depends on a database. A database is a structured representation of data that can be read from and written to, and a database is often stored separately from any application that uses the data.

Database Uses

Modern computer applications rely heavily on databases, even when the program in question isn't designed to help users manage data. Computer games rely on databases to keep track of characters, character attributes, items that each character can use during gameplay, and even locations within the game. A learning management system (LMS) uses databases to keep track of learners, instructors, content, grades, attendance, and communication between users.

As a concrete example, consider a modern smartphone. The phone itself has a database that stores connection information, OS version, model number, serial number, and similar data about the device itself.

A smartphone also has a variety of applications on it, many of which have their own databases. Common examples include a contact list, a calendar, email apps, photo galleries, social networking apps, and shopping apps. While these apps store data for internal use, the user can grant permission for some apps to access data stored in other apps. For example, a calendar app may be connected to the contact app's database so that the user can easily add appointments with specific people to their calendar, while Facebook can access photos stored on the phone so that the user can share the photos with friends.

In none of these cases, though, does the user have direct access to the database itself. Instead, the application's front end (the part the user interacts with) includes tools that allow the user to create and retrieve data, update existing data, and even delete data that the user no longer needs. The software developer must incorporate the database into the application in a way that allows the application to access and manage the data.

Data vs. Information

When talking about databases, data and information are invariably mentioned. The terms data and information are often used interchangeably in casual speech, but from a software perspective, there is a very clear difference between the two. Specifically:

The term data refers to individual, raw facts. In many cases, individual pieces of data are meaningless on their own.

When we process data, the result is information. Unlike the raw data, information is useful and normally corresponds to the end user's specific needs.

As an example, consider a piece of data like smith. On its own, this is meaningless. While you might first think it is someone's last name, there isn't enough information here to tell you exactly whose name it is. It could also be an occupation rather than a name.

In a specific context, however, this piece of data can be combined with other data to give you useful information. As part of a course roster, for example, it could be combined with a first name to reference a specific student. In a job application or online profile, it could reference the person's work experience, with a completely different name.

Structured vs. Unstructured

A database can contain data that is structured or unstructured. Modern database software programs hosting databases can usually handle both structured and unstructured data, but it is still good to understand the difference.

In a database with structured data, which we will call a structured database, the data is organized in a specific pattern. This makes it easy to control what data is available and where to find specific pieces of data. In a structured database, the developer can limit what kinds of data are stored in the database to improve data integrity and reduce the amount of redundant data. This comes as a trade‐off in that creating new data and accessing stored data are relatively slow compared to creating and accessing data in an unstructured database. Structured databases are best for datasets that contain predictable types of data, such as bank accounts, personnel records, and inventories.

NOTE Data integrity refers to the reliability and accuracy of the data. A dataset is a collection of related information that is composed of separate elements but can be manipulated as a group.

A database with unstructured data typically does contain some amount of structure, but without the strict controls inherent to a structured database. Unstructured databases are typically a little faster than structured databases, but they are also prone to duplicate or redundant data. Unstructured databases are often found in applications that have unpredictable or irregular kinds of data, such as social media posts, online product reviews, and similar user‐generated content.

NOTE In this lesson, we will look only at structured databases, specifically relational databases.

Database vs. DBMS

As mentioned earlier, a database is a representation of data that can be read from and written to and is often stored separately from any application that uses the data. The fact that the database is separate from an application means that it can be made available to multiple applications, such as allowing access to a contact database from a calendar app or posting photos from an image database using a social media app. While any application that uses a specific database must know how to access the data, the data itself is simply a pool that any authorized app can pull from.

A database management system (DBMS) is a software system that manages databases. The DBMS executes commands, provides security, enables network access, and provides admin tools for database administrators (DBAs) to work with database files.

A subset of DBMSs includes relational database management systems (RDBMSs) that are designed specifically to work with relational databases. There are many options for RDBMSs, including MySQL, PostgreSQL, Microsoft SQL Server, Oracle Database, and DB2. The choice of DBMS determines some factors of how the data itself is organized, but in large part, all RDBMSs do the same thing.

While a specific RDBMS will be used in this book, keep in mind that all RDBMSs do essentially the same things in the same way. If you understand how one RDMBS works, you can easily transfer that knowledge to a different RDBMS.

RELATIONAL DATABASE CONCEPTS

Relational databases are highly structured in that they organize data into one or more tables or relations, where each table represents a logical group of data. In day‐to‐day professional work, we usually say table. Relation is the formal, academic term, which you may run into if you read about databases in other contexts. Relation is also the basis for the term relational database. At a more abstract level, the term entity is also used to refer to a table, especially during the design phase of a database and before the database is built on a server.

NOTE The relational database model was first proposed by Edgar F. Codd in 1970, with the main goal of reducing duplicate data in a database and thereby making it easier to retrieve and manage specific data.

A table can be imagined as a two‐dimensional grid of cells.

A row in a table is a horizontal group of cells, one cell high. It holds facts about one discrete thing represented by the table. That thing could be anything such as a person, a credit card transaction, or a professional sports mascot.

A column in the table is a vertical strip of cells, one cell wide. Every cell in the column holds the same type of data, but each cell is a fact about a different thing. For example, if the table includes data about people, then the table could include separate columns for name, phone number, and address.

A cell represents the intersection of a row and a column. Each cell contains a single piece of data.

The following is a concrete example:

In the data in this table, each row represents one state. Rows are also known as records or tuples. The term record is common, while tuple is an academic term. A database record is a single row in a table in the database, and each row in a table is considered a separate object from the other rows in the same table.

Each column represents a fact about a state: its name, abbreviation, capital, date established, and current population. There's a subtle nuance here. The term column refers to a strip of vertical cells, but it also refers to a definition: an overall name (Abbr, Capital, Population) and restrictions on the size, shape, and type of data allowed as values in the column. In fact, when a developer says column, they're usually talking about the definition, rather than the cells themselves. To reduce confusion, we may call the value of a record's column a field. Academically, column definitions are also called attributes.

NOTE The size, shape, and type of data allowed in a column will be discussed in more detail in Part 2, Applying SQL. As an example, a Population field might be defined as one that can hold a whole number (the type) that is between one and four billion (the size and shape).

ACID COMPLIANCE

Relational databases provide rich and powerful ways to model our data, and it doesn't stop there. A relational database's data structures and algorithms also provide behavior guarantees. They can't guarantee an action will always work, but they can guarantee the state of a database after an action succeeds or fails. They also guarantee predictable behavior when multiple users are interacting with a database. There are many types of guarantees.

The ACID properties are four of the most important guarantees. ACID is an acronym for the following:

Atomicity

Consistency

Isolation

Durability

Before jumping into ACID, you need to understand database transactions. A relational database allows the following actions:

Read existing data

Insert new data

Update existing data

Delete existing data

Add or alter schema (tables and relationships)

A transaction is a set of one or more actions that represents a single, logical unit of work. Say you want to reserve three rooms at a hotel, and those room reservations are stored in at least three rows in a database. In most circumstances, you don't want to book any rooms if one or more room reservations fail—it's all or nothing. That makes your three‐room reservation a transaction. It's a single unit of work that should succeed or fail as a unit.

If you purchase a concert ticket for an in‐demand concert, the software system must first find an available ticket and then put it on hold until you can provide payment. That's a transaction. If it wasn't, the system might find an available ticket only to have another person purchase it before you. Without a transaction, the ticket could be purchased twice, or the system might waste time presenting tickets that are no longer for sale. Imagine selling tickets to a show that's predicted to sell out in 10 minutes without software that can handle transactions.

ACID Properties

As mentioned, ACID is an acronym for atomicity, consistency, isolation, and durability. The ACID properties are not required. You can run a database without them; however, for some situations, running without ACID is risky. In situations involving things, such as banking, medical records, and real‐time decision‐making, bad things can and will happen to your application if you ignore ACID.

Additionally, you never know what will happen when using a software. The network can fail, the operating system can crash, or another user can alter data that you're using. Given enough time, something will fail.

ACID‐compliant databases are designed to withstand unexpected failures without corrupting your data. Let's look at each of the elements of ACID.

Atomicity

A transaction is atomic if it follows the all‐or‐nothing rule. If one action in the transaction fails, then the entire transaction fails. An atomic transaction never partially succeeds.

Imagine a scenario where you write a new row of data to a table with 10 columns. On the eighth column write, a power failure occurs, and your server immediately shuts down. If the database supports atomicity, it will notice the unfinished transaction and restore the data to its pre‐transaction state when it comes back online.

Consistency

A transaction is consistent if it can move the database from only one valid state to another valid state. This means that the data processed during the operation is in a consistent state when the transaction starts and when the transaction ends as well. For example, when we transfer money from one account to another, consistency means that the sum of both accounts before the transfer and after it will be the same. If Account A has $200 and Account B has $100, the sum of both accounts would be $300. If $100 is transferred from Account A to Account B, then Account A would have $100, and Account B would have $200. The sum of both accounts is still $300. Consistency has been maintained.

A consistent database also enforces constraints on the types and sizes of data that are allowed. For example, the balance of an account is expected to be a numeric value. A birthdate is expected to be a date value. The database will maintain consistency by ensuring that the type of the data and size of the data are maintained.

Consistency also enforces primary and foreign key relationships. A primary key is a unique value assigned to each row of a table. For example, in a table containing bank account information, the account number would likely be unique and thus could be a primary key. In regard to consistency, the system will never allow a duplicate primary key in a table to occur, and it will require that each record have a primary key value.

A foreign key is a value within the row of a table that is used to connect or is related to another table. For example, a store can have a table of customers, and each customer can have multiple orders. An order ID can be stored in the customer row that can then connect to a table of orders, as shown in Figure 1.1.

Figure 1.1 Customer orders

For foreign keys, most DBMS systems by default will not allow you to orphan a row, where the value used as a foreign key does not correspond to a value in the primary key of the related table. Using our example of customers and orders, there could be a Customer and Order relationship where a Customer can have one or more Orders. If you were to try to delete only a Customer row without first deleting its Orders, then the Orders associated with that Customer would have a foreign key pointing to a record that no longer existed.

NOTE A properly configured relational schema will prevent this from happening by either rejecting the delete transaction outright or automatically deleting all the orders associated with the customer being deleted first. (This automation is called a cascade delete, and because it can lead to the deletion of millions of records without warning, it is usually not the preferred solution to resolving orphan keys.)

Isolation

A transaction is isolated if its effects are not visible to other transactions until it is complete. This is often referred to as concurrency control. A large database application may have hundreds or thousands of users making changes to it at the same time, so if transactions are not isolated, this could cause inconsistent data.

Imagine two users, John and Sally, accessing the database at once. John is updating data in the orders table. At the same time, Sally is reading data from the orders table, including records being edited by John. A DBMS has various levels of isolation it could apply. As a beginner, you need to know only two levels.

Serializable: Sally will not receive her data until John's changes are committed. When John begins a transaction to change data, the data is locked until his transaction is complete.

Read Uncommitted: Sally will get her data right away, including whatever changes John has made that haven't been committed yet. This is called a dirty read because it is possible that John's transaction could fail and be rolled back.

The default isolation in most DBMS systems is serializable because it does a better job of avoiding errors or corrupting data.

Durability

A transaction is durable if once it is committed (saved to the database), it will remain so, even in the event of catastrophic failures. Even if you kick the server's power cord out of the wall after a transaction, it will stay committed.

This means a transaction is not fully committed until it is written to permanent storage, such as a storage drive.

Databases and Log Files

In most ACID databases, a transaction log (sometimes referred to as a journal or audit trail) is a history of executed actions. The upshot of this is that even if there are crashes or hardware fails, the log file has a durable list of each change made to the database.

The log file is physically separate from the actual database data. This is important to ensure a database remains consistent. For example, when you insert a new row into a table, a few things happen.

The DBMS validates the incoming command.

A record is added to the log file specifying what changes are about to be made.

The DBMS attempts to make the changes to the actual data in the table or tables.

If successful, the log record is marked as committed.

If a failure occurs between steps 2 and 4, like a server reboot, the DBMS will scan the log file for uncommitted transactions when it comes back online. If it finds them, it will examine the actions performed and undo them, effectively restoring the database to its former, consistent state.

ENTITY INTEGRITY

One of the keystones of relational database design is entity integrity, which guarantees that each record in a table is unique within that table. All RDBMSs enforce entity integrity automatically, but the database creator has to appropriately define a primary key within each table for this to work. As data is added to a table, the RDBMS will check two properties to ensure that the new record is unique.

That no other existing record in the table has the same primary key value as the new entry

That there is a value entered for each field of the primary key

If a new record fails to meet both criteria, then the RDBMS will reject the record and prevent it from being added to the table.

Remember from our definitions for a relational database that a record is the collection of values for a single item in a table and that each record is independent of other records in a table. In this case, the term unique has its original definition of one of a kind, so under the guidelines of referential integrity, the set of values in each row must be different from the set of values in all other rows of the table. This uniqueness serves two specific purposes.

Reducing (but not necessarily eliminating) duplicate data

Allowing the database to easily find specific records within a table

Ensuring Uniqueness

The relational design approach to meeting the requirements of entity integrity is to include one or more fields in each table whose sole purpose is to identify each individual record. In some cases, we can use an existing field to be the primary key. For example, we could use the entry date as a primary key in a table that tracks newspaper issues, on the grounds that each newspaper in the database issues only one paper per day. This is a called a natural key because it happens to be a piece of data we want to track anyway, so we don't need to create a separate field just to ensure uniqueness. Other examples of potential natural keys include a phone number or email address to identify people in a Contacts table. We typically want both of those values in such a dataset, and if each person has their own unique phone number or email address, either