SQL Pocket Guide, 4th Edition

Chapter 5. Creating, Updating, and Deleting

The majority of this book covers how to read data from a database with SQL queries. Reading is one of the four basic database operations out of create, read, update, and delete (CRUD).

This chapter focuses on the remaining three operations for Databases, Tables, Indexes, and Views. In addition, the Transaction Management section covers how to execute multiple commands as a single unit.

Databases

A database is a place to store data in an organized way.

Within a database, you can create database objects, which are things that store or reference data. Common database objects include tables, constraints, indexes, and views.

A data model or a schema describes how database objects are organized within a database.

Figure 5-1 shows a database that contains many tables. The specifics around how the tables are defined (i.e., the Sales table contains five columns) and how they connect with one another (i.e., the customer_id column in the Sales table matches the customer_id column in the Customer table) are all a part of the schema of the database.

Figure 5-1. A database containing a star schema

The tables in Figure 5-1 are arranged in a star schema, which is a basic way of organizing tables in a database. The star schema includes a fact table in the center and is surrounded by dimension tables (also known as lookup tables). The fact table records ...