Structured Query Language, or SQL (pronounced see-quel), is the language that is used for querying and manipulating data and defining structures in databases. Initially developed at IBM in the early 1970s, SQL became an ANSI and ISO standard in 1986.

SQL is a powerful, yet simple language, and can do many things, such as execute queries, retrieve, insert, update, and delete data, create databases and tables, and much more.

These types of activities can be grouped into different subdivisions of SQL: Data Definition Language (DDL), Data Manipulation Language (DML), and Data Control Language (DCL):

• Use DDL commands to specify database schema:
• CREATE: This is used to create a new database or objects in a database.
• ALTER: This is used to alter a database or objects in a database.
• DROP: This is used to delete a database or objects in a database.
• TRUNCATE: This is used to remove all data from a table instantaneously.
• Use DML commands to query and modify data:
• SELECT: This is used to retrieve data from a database.
• INSERT: This is used to insert data into a database.
• UPDATE: This is used to update data in a database.
• DELETE: This is used to remove data from a database.
• Use DCL commands to control permissions and translations:
• GRANT: This is used to give access to a user.
• REVOKE: This is used to take access away from a user.
• COMMIT: This is used to save changes in a transaction.
• ROLLBACK: This is used to remove the saved changes in a transaction.

The SQL language comprises several elements that will be explained in more depth in subsequent chapters. These elements include the following:

• Queries that retrieve data based on specific criteria.
• Clauses that are components of statements or queries.
• Predicates that are logical conditions that evaluate to true or false. These help you to narrow down the results of your queries.
• Expressions that produce either scalar values or tables of columns and rows. Expressions are a part of predicates.
• Statements that are queries run against a database, comprised of clauses and, optionally, expressions and predicates.
• White space that is generally ignored in SQL statements and queries, making it easier to format for readability because you don't have to worry so much about particular spacing for the SQL to run correctly.

The following diagram shows you the components of a SQL statement, which is also called a SQL query:

In the preceding diagram, you can see the different elements of a SQL statement. Each line in the preceding statement is considered a clause. Clauses use SQL keywords. Keywords are reserved words that have special significance in the SQL language—SELECT, FROM, and WHERE are just some of the keywords that are used. More information on keywords is provided in Chapter 4, Designing and Creating a Database. The preceding diagram also shows an expression and predicate. A predicate helps you to narrow down your query results. The expression is a piece of a predicate that sets the value. The diagram also helps to illustrate the use of white space. You could write out your entire query on one line, but it's much easier to read when you add carriage returns and spaces. The details of the different elements of queries will be covered more in future chapters of this book.

A database is a collection of data. You store databases in a relational database management system (RDMS). The RDMS is the basis for modern database systems like MySQL, SQL Server, Oracle, PostgreSQL, and others. These will be covered in more detail later in this chapter.

In an RDMS, objects called tables store data. Tables are a collection of related data stored in columns and rows. The following screenshot is a cross-section of a table that contains data about baseball players' appearances in all-star games:

A NULL value in a table is a value that appears to be blank. It doesn't represent a string of blank spaces, zero, or a zero-length character string: it's a missing or unknown value.

A field is an intersection of a row and a column. This field could be any type of data, including a yearID, teamID, or a playerID field (using our example). Each red arrow in the following screenshot points to a value in a column that is considered a field:

A row contains values in a horizontal division of data. In this example case, it's a row or record from a table:

A column contains values in a vertical division of data. In this example case, it's the gameID column from a table:

To ensure that the data in your tables is consistent and accurate, you will need to understand data integrity. You will learn about data integrity in the next section.

Data integrity refers to the consistency and accuracy of the data. It is typically enforced by the procedures and guidelines in the database design phase. In RDMS, keys enforce data integrity. A key is user-defined and forces values in a table to conform to a specified standard. This standard will allow only certain kinds of values to be in the database.

Data integrity refers to the consistency and accuracy of data and table relationships. The following table lists the types of integrity you can use:

Each type of integrity and how each relates to one another is discussed in the following sections.

To ensure that each row in a table is identifiably unique, you use entity integrity. This is done with a few different types of keys or constraints, including unique, not null, and primary key constraints.

To ensure that all values in a column or columns are different from each other, you use a unique constraint. This type of key can be applied to any data type and is used to avoid duplicate data. You can apply a unique constraint to multiple columns so that it creates a unique value across those multiple columns. It can contain null values.

If you create a unique constraint on one column, it will force the table to have unique values in that specific column. If they are not unique, then the row will not be able to be inserted or updated.

In the following screenshot, the parkkey constraint is unique. All the other fields can have duplicate information as long as the parkkey unique constraint isn't violated:

If you create a unique constraint on a combination of columns in a table, it will force the table to have unique values in the combination of those columns in the unique constraint. If they are not unique, the row will not be able to be inserted or updated.

The following screenshot shows an example of a composite, unique constraint. In this case, playerID, yearID, and teamID would need to be unique for the row to be acceptable:

To ensure that all values in a column are not null, you use a not null constraint. This type of key can be applied to any data type and is used to avoid missing data. If you create a not null constraint on a column, it will force the table to have values in that specific column. If the values are null, then the row will not be inserted or updated.

In the following screenshot, you can see that the birthYear constraint is set to not null. The deathYear constraint would allow nulls since not all people have a year of death:

The primary key is used to ensure that all values in a column are not null and unique. This key combines the unique and not null constraint properties into one key. This type of key can be applied to any data type and is used to avoid missing and duplicate data. You can only have one primary key per table.

If you create a primary key on a table, it will force the table to have unique, not null values in that specific column. If the values don't comply, then the row will not be able to be inserted or updated. You can also create a primary key on multiple columns. This is considered a composite key. In this case, the composite key would have to be unique for each row, otherwise the row could not be inserted or updated.

In the following screenshot, the playerID constraint would be the primary key because it's unique and not null for every row in the table:

In the following screenshot, the playerID, yearID, and teamID constraints could be the composite primary key because the combination of those three columns is unique and not null for every row in the table:

Referential integrity refers to the consistency and accuracy between tables that can be linked together. By having a primary key on the parent table and a foreign key on the child table, you achieve referential integrity. A foreign key on the child table creates a link between one or more columns in the child table and the primary key on the parent table. When a foreign key is present, it must reference a valid, existing primary key in the parent table. This way, the data in both tables can maintain a proper relationship. You will learn more about this in the following example.

If you don't set up referential integrity, you wind up with orphaned records. For example, let's say that you delete a player from the first table here:

Now let's say that you didn't delete the corresponding record in the second table here. In this case, the second table's records would be orphaned:

If there was a foreign key constraint on the salary column, then the player could not be deleted from the parent table without first deleting the corresponding salary rows in the salary table. By having a foreign key constraint, we will also prevent users from adding rows to the child table without a corresponding parent row or changing values in a parent table that would result in orphaned child table records.

You won't get an error if there is incomplete data when you lack referential integrity constraints. It's basically like your records are lost in the database since they may never show up in reports or query results. This can cause all kinds of problems, such as strange results, lost orders, and potentially life-and-death situations where (for example) patients don't receive proper treatments.

When creating a foreign key constraint, the foreign key must reference a column in another table that is the primary key. It can be any data type and accept duplicate and null values by default. The foreign key constraint can maintain three types of table relationships (covered in more detail in Chapter 7, Querying Multiple Tables):

• One-to-one: This type of relationship is when one table has just one corresponding row in another table. An example of this could be a table with employees and computers. Each employee has one computer.
• One-to-many: This type of relationship is when one table has none, one, or many corresponding rows in another table. An example of this could be a table with adults and children. An adult table row may have none, one, or many rows in the child table.
• Many-to-many: This type of relationship is when many rows in one table correspond to many rows in another table. An example of this could be the customers and products tables. Customers can purchase many products.

In the following screenshots, the primary key would be on the first table as playerID. The second table would have a foreign key reference to playerID on the first table. In this case, there would be a one-to-many relationship between the first and second tables because there is one player in the first table and none, one, or many rows corresponding to that player in the second table.

If you had a foreign key setup on playerID in the second table, then you would not be able to delete the playerID value from the first table unless you deleted it in the second table beforehand. This key setup maintains the referential integrity and ensures that you won't have orphaned records in the second table:

To ensure that data values follow defined rules for formatting, range, and value using check and default constraints, you use domain integrity.

The check constraint is used to ensure that all values in a column are within a range of values. This type of key can be applied to any data type and is used to ensure that values aren't invalid. A check constraint is enforced with user-defined conditions and evaluates as either true or false. You can define a check constraint on a single column or a combination of columns in a table.

The following screenshot shows an example of a table where a check constraint would make sense on the inducted column. A player can either be inducted into the hall of fame or not. In this case, you could create a check constraint that only allows Y or N in that field. If the value isn't Y or N, then the row can't be updated or inserted:

The following screenshot shows an example of a table where a check constraint can be applied to multiple columns. For instance, you wouldn't want deathYear to be a year before the birthYear, so you can set a check constraint that will only allow you to add or update a birthYear or deathYear that follows a check constraint like birthYear < deathYear:

To ensure that all rows in a column have a value, you use a default constraint. This type of key can be applied to any data type. A default constraint assigns a default value to a field. This is used to avoid having a null value for a field if a user doesn't specify a value.

The following screenshot shows an example of a table where a default constraint could make sense on the ab column:

A player can be in a game without having any at-bats. In this case, you could create a default constraint that sets the ab column to 0 if the user provides no value.

Database normalization is the process of putting your raw data into tables using rules to avoid redundant data, optimize database performance, and ensure data integrity.

Without proper normalization, not only can you have data redundancy, which uses additional storage space, but it can be more difficult to update and maintain the database without data loss.

Normalization requires forms. Forms are sets of rules to follow to normalize your data into database tables. There are three forms that we will discuss: the first normal form, the second normal form, and the third normal form. Each of these forms has a set of rules to ensure that your database complies with the form. Each of the forms builds on the previous forms.

The first normal form (1NF) is the first level of database normalization. You will need to complete this step before proceeding to other database normalization forms. The primary reason to implement 1NF is to eliminate repeating groups. This ensures that you can use simple SQL statements to query the data. It also ensures that you aren't duplicating data, which uses additional storage and computing time. This step will ensure that you are doing the following:

• Defining data, columns, and data types and putting related data into columns
• Eliminating repeating groups of data:
• This means that you will not have repeating columns, such as Year1, Year2, Year3, but instead will have a column that is named Year, and each row in the table will be a different year.
• Another example of this is not having multiple values in the same field, such as 1985, 1987, 1989, but instead placing each year in a row.
• This means that there are no exact duplicate rows. The example following this bullet list will explain this concept in more depth.
• Creating a primary key for each table

In the following example, you could make the first column the primary key in the people table and the foreign key in the salaries table. In the salaries table, you could create a new primary key or create a composite key that is an amalgamation of multiple fields.

Here is a denormalized sample table:

There is a right way and wrong way to normalize this table. Let's go over the wrong way first:

The preceding design has introduced new problems. Even though it doesn't have groups of repeating data in one column, the salary is limited to two values. What if a player has more than two salaries? You don't have anywhere to put another salary without adding a third column. This also wastes space for those players that only have one salary, and searching through this table for a player with a specific salary becomes difficult. The same goes for the SalaryYear columns.

The right way to normalize the denormalized table to the first normal form is to ensure that there aren't repeating groups, as shown in the following table. The people table with player information would look like the following:

The Salary value has been removed and placed in another table with the playerID field linking them to each other; therefore, the salaries table will look like the following:

Let's go through a denormalization example by looking at the following table:

To meet the requirements of 1NF, you would need to split this table into multiple tables. Depending on the table you are trying to normalize, you might not need to split it if it's already following the rules of 1NF.

This table only contains the information about the player and has a primary key of playerID:

The other table would contain the rest of the fields from the denormalized table. The following table has a foreign key relationship to the preceding table regarding playerID:

The second normal form (2NF) is the second level of database normalization. You will need to complete 1NF before beginning this step. The primary reason to implement 2NF is to narrow tables down to a single purpose, which makes it easier to use and design tables. This step will ensure that you do the following:

• Meet the requirements of 1NF: You will need to implement 1NF before you can use 2NF.
• Remove partial dependencies: This will entail narrowing tables down to a single purpose where possible.

Starting with the tables from our 1NF example, you can break these down further into additional tables. You will still have the same player table from 1NF since it serves a single purpose of giving us player information. The franchise table has multiple purposes with RBI and rank, so since RBI isn't related to the franchise, you will split the franchise table into two.

The franchise table has all the franchise and team information in it now, and the RBI columns and related columns can be split out into a batting table. The franchise table still has a primary key of franchID and playerID with a foreign key referring back to the player table on playerID:

The batting table has a primary key of playerID and teamID and has a foreign key of playerID to the player table and a foreign key of teamID to the franchise table:

Since you split the franchise table into two tables—one table with franchise information and one table with batting information—these tables now comply with 2NF because each table is serving a single purpose.

The third normal form (3NF) is the second level of database normalization. You will need to complete 2NF before beginning this step. The primary reason to implement 3NF is to ensure that your tables aren't created so that dependencies between columns may cause inconsistency. Generally, if a database is described as normalized, then it's normalized according to the third normal form. This step will ensure that you are doing the following:

• Meeting the requirements of 2NF: You will need to make sure that your tables comply with 2NF before proceeding with 3NF.
• No attributes depend on other non-key attributes: This means that you will need to look at your tables and see whether more fields can be split into other tables since they aren't dependent on a key.

Working from our 2NF example, you can further split the franchise table into a franchise table and a team table. The rank of the team isn't dependent on the primary key of the franchise table.

In 3NF, our franchise table becomes the following two tables.

franchID becomes the primary key in the franchise table:

The team table has a primary key of teamID and a foreign key of franchID referring to the franchise table:

To summarize the process of taking data from denormalized to the third normal, here's a diagram of the changes that are made:

The preceding diagram shows how you went from denormalized to 3NF. Following the rules of the normal forms, you took a single table and turned it into four tables. To begin with, you split up one denormalized table into two tables as part of 1NF. 1NF ensured that you didn't have duplicate data and repeating groups. This resulted in a player and franchise table. Next, you split the tables out into three tables as part of 2NF. 2NF resolved the issue of not giving each table a specific purpose, resulting in a player, franchise, and batting table. For the final step, you split the tables into four tables as part of 3NF. 3NF ensured that you didn't have any fields in a table that weren't dependent on the primary key, resulting in a player, franchise, batting, and team table.

By going from a denormalized table to 3NF, you accomplished several things, including ensuring that you don't have duplicate data, that you have keys linking data to each other in the tables, that you have a single purpose for each table, and that you have minimized the storage and compute costs for your queries.

Even adhering to the third normal form can be taken to extremes, so while the third normal form is desirable, it's not always required. For instance, with zip codes, you could create a table with just zip codes since they may be duplicated in a table with users' addresses, but this may degrade performance instead of helping performance.

An RDMS is a database that stores data in tables using rows and columns. The values in the tables are related to one other, and the tables may also be related to one another, hence the term relational. This relationship makes it possible to access data across multiple tables with a single query.

In this section, we will review the top four relational database management systems. The top four are Oracle, MySQL, SQL Server, and PostgreSQL.

According to the DB-Engines Ranking, here are the scores for the top RDMSes at the time of writing this book:

The preceding screenshot can be found at https://db-engines.com/en/ranking.

Oracle was first released in 1979. Oracle was the first commercially available SQL-based RDMS. It does have one free version, Oracle Database XE, which has some limitations compared to its licensed versions. Oracle runs best on Linux, but can be installed on Windows. Oracle is a great choice for organizations that need an RDMS and can handle very large databases and a variety of features.

The advantages of Oracle are that it offers a lot of functionality for system and database administrators, it's fast and stable, and it has lots of support and documentation.

The disadvantages of Oracle are that licensing is expensive and it may require significant database administrator resources to maintain it after installation.

MySQL is a free, open source SQL database that started in 1995. It also has proprietary licensing available, which includes support and maintenance. Sun Microsystems bought MySQL in 2008, which was then acquired by Oracle in 2010. MySQL is commonly used in conjunction with PHP web applications. MySQL is a great choice for organizations that need a good RDMS but have a tight budget.

The advantages of MySQL are that it's available for free, it offers a lot of functionality for system and database administrators, it's easy to use and implement, and it's fast and stable.

The disadvantages of MySQL are that while support is available, it's not free. Also, since it's under Oracle, not all features are free, including paid-for options, such as enterprise monitoring, backup, high availability, scalability, and security.

Initially released in 1989, SQL Server is available with a commercial license. It does have one free version, SQL Server Express, with a limitation of 10 GB per database, along with other resource limitations. SQL Server is usually installed on Windows, but can also be installed on Linux. SQL Server is a great choice for organizations that need a good RDMS, and use a lot of other Microsoft products.

The advantages of SQL Server are that it offers a lot of functionality, including replication, and high availability and partitioning works very well with other Microsoft products, such as .NET Framework and Visual Studio. It is also fast and stable.

The disadvantages of SQL Server are that licensing is expensive, especially for the Enterprise edition, and not all features are included in all editions, such as some high-availability options and partitioning.

The first release of PostgreSQL was in 1989. This indefinitely and doesn't enforce any limits at all. PostgreSQL is usually installed on Linux machines and can be used to store structured and unstructured data. PostgreSQL is a great choice for organizations that need a good RDMS, already use Linux, and don't want to spend a lot of money on licensing.

The advantages of PostgreSQL are that it offers a lot of functionality, such as high availability and partitioning, it's scalable and can handle terabytes of data, and it's fast and stable.

The disadvantages of PostgreSQL are that documentation may be harder to come by and configuration can be confusing. It also runs on Linux, and you need to know how to run commands from Command Prompt.

Even though there is an ANSI/ISO standard, there are different versions of SQL. Still, to be compliant, they all similarly support the major commands, so SELECT, WHERE, INSERT, UPDATE, and DELETE would all have a syntax that matches.

Each subsequent chapter of this book will also note differences in the language or functionality of SQL where there are differences between MySQL and SQL Server, PostgresSQL, and Oracle.

This chapter introduced the concepts required to understand the basics of relational database management systems. It introduced you to foundational topics such as understanding SQL, what SQL can do, and its basic components. You learned that there are three subdivisions of SQL called DML, DDL, and DCL, and that the SQL language is comprised of several elements that make up a statement. We walked through a description of the relational model, what a database is, and what is in a database, including what a table, row, column, and field are.

We followed with an explanation of data integrity, including the different types of data integrity, such as entity, referential, and domain integrity, and looked at how to use keys and constraints. This understanding of data integrity helped you understand database normalization, including the different forms of normalization, 1NF, 2NF, and 3NF.

Lastly, you learned about the types of relational database management systems, including Oracle, MySQL, SQL Server, and PostgreSQL, and their advantages and disadvantages. You also learned what makes these RDMS different from each other in terms of SQL syntax.

In the next chapter, we will look at how to install MySQL Workbench on Windows and Mac, and go through step-by-step instructions to help you walk through each part of the installation process. The instructions will also include the configuration of MySQL Workbench on both Windows and Mac. We will walk through some examples of how to connect to your local MySQL and set up connections to other MySQL servers. We'll conclude with a step-by-step explanation of how to restore a database to MySQL.

1. What is SQL?
2. What are the different subdivisions of SQL?
3. What are the elements of a SQL statement?
4. What are the reasons to normalize a database?
5. What are the levels of database normalization?
6. What is data integrity?
7. What are the different ways you can enforce data integrity?
8. What types of RDMS exist?
9. What is the main advantage of MySQL?
10. What is the main disadvantage of Oracle and SQL Server?