Simplified
Simplified

L3.1Rules before the first row

YOU

Rules before the first row

usersidemailnamexpcreated_attypeNOT NULLDEFAULTintegertextvarchar(50)integertimestampprimary key✓✓✓generated0now()no rows yet?
schema
a table's columns, their types, and their rules
usersidemailnamexpcreated_attypeNOT NULLDEFAULTintegertextvarchar(50)integertimestampprimary key✓✓✓generated0now()no rows yet?
new rowsemail: zoe@simplified.orgname: Zoefits: xp 0, created_at now()email: (none)name: Maxrefused: email is NOT NULLemail: max@simplified.orgxp: 'lots'refused: xp is an integer
usersidemailnamexpcreated_attypeNOT NULLDEFAULTintegertextvarchar(50)integertimestampprimary key✓✓✓generated0now()no rows yet

Databases · Lesson 3.1

The Schema

Rules before the first row

Simplified
0:00 / 1:15

Simplified's users table now has a type for every column, and a generated primary key.

It has NOT NULL on email, xp and created at, and a DEFAULT on xp and created at.

And Postgres checks every new row against all of them.

But how does Postgres know these rules before the first learner ever signs up?

Because Simplified's developers wrote them down for it, before the table held a single row.

That written description of a table is called its schema.

A schema lists a table's columns, their types, and their rules.

It holds no rows: just the shape every row must fit.

usersidemailnamexpcreated_attypeNOT NULLDEFAULTintegertextvarchar(50)integertimestampprimary key✓✓✓generated0now()no rows yet

So what happens when learners start signing up?

new rowsemail: zoe@simplified.orgname: Zoefits: xp 0, created_at now()email: (none)name: Maxrefused: email is NOT NULLemail: max@simplified.orgxp: 'lots'refused: xp is an integer

Say Zoe signs up with an email and a name.

Her row leaves out xp and created at, so their defaults fill them in, and the row fits.

Then Max signs up with a name, but no email.

The schema says email is NOT NULL, so Postgres refuses his row.

Then say a row arrives for Max with his email, but with 'lots' in xp.

The schema says xp is an integer, so Postgres refuses that row too.

So the schema is decided before the first row exists.

And every row added after that must fit it.

Example: Rules before the first row

Say Instagram's developers write the schema for a table of saved posts. It lists a post id column, an integer with NOT NULL, and a saved at column, a timestamp with NOT NULL. Then a saved post arrives with no post id.

Does that row fit the schema?

Explanation

First, the schema says post id is NOT NULL, so every row must hold one.

Then, this row has no post id, so it would hold NULL there.

Therefore, it doesn't fit, so Postgres refuses it.

Question 1

Say Spotify's developers write the schema for a songs table. It lists four columns: id, title, artist and length, each with its type. No songs have been added yet.

How many columns does the schema list?

columns

The language Postgres reads

users schemaidintegeremailtextnamevarchar(50)xpintegercreated_attimestampPostgresquery?
your browserruns JavaScriptfetch("/api/lessons")✓Give me the lessons✕Postgresreads SQLa query in SQL ✓Give me the lessons✕
SQL
the language for asking a relational database for data, and telling it what to do
your browserruns JavaScriptfetch("/api/lessons")✓Give me the lessons✕Postgresreads SQLa query in SQL ✓Give me the lessons✕

Databases · Lesson 3.2

The Schema

The language Postgres reads

Simplified
0:00 / 0:49

Simplified's developers wrote the users schema before the first learner signed up.

To hand it to Postgres, they send it as a query, like any instruction to a database.

But what language does Postgres read a query in?

Well, think of the code you've written for the browser.

Your browser runs JavaScript, like a fetch() call.

It won't run a line of plain English, or any other language.

your browserruns JavaScriptfetch("/api/lessons")✓Give me the lessons✕Postgresreads SQLa query in SQL ✓Give me the lessons✕

A relational database works the same way.

It reads queries written in one language, called SQL.

SQL is the language for asking a relational database for data, and telling it what to do.

So Postgres reads SQL, the way your browser reads JavaScript.

So SQL is the language Postgres reads.

Every query to it, a question or an instruction, is written in SQL.

Example: The language Postgres reads

Simplified's server needs Ben's email, so it sends Postgres a query asking for it.

What language is that query written in?

Explanation

First, Postgres is a relational database, and it reads queries in one language.

Then, that language is SQL, for questions and instructions alike.

Therefore, the query is written in SQL.

Question 1

Simplified's developers send Postgres a query that makes a new table.

What language is that query written in?

Question 2

Simplified's server needs a learner's XP from Postgres.

Which of these can Postgres read?

Question 3

Say Simplified's server code is written in JavaScript.

When the server asks Postgres for a learner's XP, what language is that query written in?

Question 4

In one second, Instagram's server sends its relational database 3 questions, like asking for a profile's photos, and 2 instructions, like storing a new like.

How many of those 5 queries are written in SQL?

queries

Question 5

Your browser runs code written in JavaScript.

What plays that same part for Postgres?

Question 6

Say Spotify's server sends its relational database a query telling it to store a new playlist.

Which is true?

Writing your first schema

users schemaidintegeremailtextnamevarchar(50)xpintegercreated_attimestampPostgresquery in SQL?
CREATE TABLE
makes a new table, with the columns, types and rules you list
SQLCREATE TABLE users ( id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, email text NOT NULL, name varchar(50), xp integer NOT NULL DEFAULT 0, created_at timestamp NOT NULL DEFAULT now());?
usersidemailnamexpcreated_attypeNOT NULLDEFAULTintegertextvarchar(50)integertimestampprimary key✓✓✓generated0now()no rows yet
SQLCREATE TABLE songs ( id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, title text NOT NULL, length integer);?
SQLCREATE TABLEusers ( id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, email text NOT NULL, name varchar(50), xp integer NOT NULL DEFAULT 0, created_at timestamp NOT NULL DEFAULT now());

Databases · Lesson 3.3

The Schema

Writing your first schema

Simplified
0:00 / 1:30

Every query to Postgres is written in SQL, and that includes a schema.

Simplified's developers wrote the users schema before the first learner signed up.

So which SQL query hands that schema to Postgres?

It's called CREATE TABLE.

CREATE TABLE makes a new table, with the columns, types and rules you list.

usersidemailnamexpcreated_attypeNOT NULLDEFAULTintegertextvarchar(50)integertimestampprimary key✓✓✓generated0now()no rows yet

So what does the users table's CREATE TABLE say?

SQLCREATE TABLE users ( id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, email text NOT NULL, name varchar(50), xp integer NOT NULL DEFAULT 0, created_at timestamp NOT NULL DEFAULT now());

The query names the table, users, then gives each column a line.

ID is an integer and the primary key, and GENERATED ALWAYS AS IDENTITY makes Postgres generate it.

email is text, with NOT NULL.

name is varchar 50, with no NOT NULL, so it can be NULL.

xp is an integer, with NOT NULL and a DEFAULT of 0.

Created at is a timestamp, with NOT NULL and a DEFAULT of now().

A semicolon ends the query, and Postgres makes the table, with no rows yet.

Now how might Spotify write the schema for its songs?

SQLCREATE TABLE songs ( id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, title text NOT NULL, length integer);

Say its developers write CREATE TABLE songs.

ID is an integer and the primary key, generated by Postgres.

title is text, with NOT NULL, because every song needs a title.

length is an integer, the song's length in seconds, with no rule.

Postgres makes the songs table, ready for its first song.

So a schema is written in SQL, with a CREATE TABLE query.

And each column's line gives its name, its type, then its rules.

Example: Writing your first schema

A line in Simplified's CREATE TABLE users says xp integer NOT NULL DEFAULT 0.

What does that line tell Postgres about xp?

Explanation

First, integer says xp holds whole numbers.

Then, NOT NULL says every row must hold a value in xp.

Then, DEFAULT 0 says a new row that leaves xp out gets 0.

Therefore, xp holds whole numbers, is never NULL, and starts at 0.

Question 1

A line in a CREATE TABLE query reads: title text NOT NULL.

Which part of that line is the column's rule?

Question 2

Say YouTube's developers run a CREATE TABLE query for a new comments table.

What does Postgres do?

Question 3

Say a developer writes this query: CREATE TABLE playlists (id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, name varchar(100) NOT NULL, description text, song_count integer NOT NULL DEFAULT 0, created_at timestamp NOT NULL DEFAULT now());

How many columns will the playlists table have?

columns

Question 4

Say a developer runs: CREATE TABLE tasks (id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, title text NOT NULL, notes text, due timestamp, done boolean NOT NULL DEFAULT false);

How many of its columns can never hold NULL?

columns

Question 5

A line in a CREATE TABLE query says: bio varchar(150).

What does that line tell Postgres about bio?

Question 6

Say Netflix's developers write CREATE TABLE profiles, with the lines name text NOT NULL and language text. Later, a new profile arrives with a language but no name.

What does Postgres do?

Assistant

Highlight lesson text and click Ask AI, or type a question below.