Introduction

A database becomes useful only when we can actually create it, fill it with data, and ask it questions. The previous chapter built the theory — what a Relational Database Management System (RDBMS) is and why we need one. This chapter turns that theory into practice: it teaches the language through which we command an RDBMS.

Recall that an RDBMS is software that manages a relational database. Several such systems are widely used today:

• MySQL
• Microsoft SQL Server
• PostgreSQL
• Oracle

Different as they are in origin and licensing, all of them do the same essential job: they allow us to create a database consisting of relations (tables), to link one or more relations together, and to query the linked data efficiently — so that data on the database can be stored, retrieved and manipulated in an organised way.

Throughout this chapter, MySQL is the RDBMS used for hands-on work. The goal is to learn three practical skills with it:

• how to create a database and the tables inside it,
• how to populate those tables with data, and
• how to query the database to pull out exactly the information we want.

The road map for the chapter runs in this order: an introduction, the Structured Query Language (SQL) itself, data types and constraints in MySQL, SQL for data definition, SQL for data manipulation, SQL for data query, and finally data updation and deletion. Notice the logic of that sequence — you first define the structure of the data, then put data in, then learn to question it, and finally learn to change or remove it.

The chapter opens with a reflection from E. F. Codd, the originator of the relational model. He said that the most important motivation for the research that produced the relational model was the objective of providing a sharp and clear boundary between the logical and the physical aspects of database management. The idea is worth pausing on: as users, we should think only about the logical side — tables, rows, columns, and what we want from them — while the messy physical side, such as how bytes are arranged on a disk, remains entirely the system's concern. The language you are about to learn, SQL, lives wholly on the logical side of that boundary: you describe what you want, never where or how it is physically stored.