Review

Core SQL Statements

In the last few chapters we learned the fundamental SQL statements to build the core foundation of the SQL language, which included the following.

  1. How to create a database with the CREATE DATABASE command
  2. How to create a table in a database with the CREATE TABLE command
  3. How to view a table structure with the meta command \d table_name
  4. How to insert data in a table with the INSERT command
  5. How to select data from the table with the SELECT command
  6. How to modify data in the table with the UPDATE and DELETE command
  7. How to alter the table structure with the ALTER command

SQL categorizes these statements into Data Definition Language and Data Manipulation Language. To understand this categorization, we must first understand the distinction between the database's schema and the database's data.

Data vs. schema

Data vs. Schema

A database's schema is like the construction plan for a table. For example, a column's name, type and any options are all related to the database schema. If you ask PostgreSQL for a description of the table, the schema, you'll receive the output below with the table column names as rows.

library=# \d users
                                     Table "public.users"
   Column   |            Type             |                     Modifiers
------------+-----------------------------+----------------------------------------------------
 id         | integer                     | not null default nextval('users_id_seq'::regclass)
 full_name  | character varying(15)       | not null
 enabled    | boolean                     | default true
 last_login | timestamp without time zone | not null default now()
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

A database's data, on the other hand, is the actual content of the tables. When we think of a database as a spreadsheet, we are naturally thinking about the database's data. For example, after adding some users to the users table, we can query the table for its data, like this:

library=# SELECT * FROM users;
 id |  full_name  | enabled |         last_login
----+-------------+---------+----------------------------
  1 | John Smith  | t       | 2016-03-20 22:06:13.745301
  2 | Jack Wilson | t       | 2016-03-20 22:06:13.745301
  3 | Jack Smith  | f       | 2016-03-20 22:06:13.745301
(3 rows)

It's important to recognize that even though the output looks different, both the data and schema perspectives are views into the same table. In this book, we'll be showing the database from the data perspective, but other books or resources may show the schema perspective. In either case, make sure to understand that they're both showing the same table, just in different ways.

Data Manipulation Statements

Data Manipulation Statements are used for the accessing and manipulating the data of the database. We executed all the data manipulation statements listed below in the earlier sections. These are also referred to as CRUD operations.

  • INSERT
  • SELECT
  • UPDATE
  • DELETE

For a full list of data manipulation statements, check PostgreSQL Documentation:

A bit about CRUD

The term CRUD is a commonly used term in the database world that is used to denote CREATE data, READ data, UPDATE data and DELETE data. These are the four operations that we perform with INSERT, SELECT, UPDATE and DELETE statements.

Data Definition Statements

Data Definition Statements are commands that relate to the structure of the database. We executed the data definition commands and Postgres commands below to create our first database and table.

  • createdb
  • CREATE TABLE
  • \d table_name
  • psql database_name
  • DROP DATABASE
  • ALTER TABLE
  • DROP TABLE

For a full list of data definition statements, check PostgreSQL Documentation.

This chapter concludes the fundamentals of the SQL query language. In the next chapter, we'll look into creating multiple tables to store the user's data as well as the books in the library, reviews, and fines associated with the user.