SQL from zero · Chapter 21 of 37

A new feature, Circles

By the end of this chapter you can create a table of your own: its columns, their types, which ones are required, and what fills itself in. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Building the schema
  • 3 tasks
  • SQL
  • In the all labs plan

The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.

Why you need this

Meera wants Circles: small groups inside MingleSphere, like Chai Lovers, Cricket Club and Design Crit. A circle has a name, an owner, can be private, and has a member limit. Next she wants meetups for each circle. The app needs a place to keep all of that, today.

Every query you have written so far reads a table that already exists. There is no circles table, and no SELECT can make one. You could hide circles inside a table that is already there, say as posts of a made-up type, but then every count of posts would have to step around them, and nothing would stop a circle with no name or a member limit of "lots".

What you build: 3 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    circles.sql: the circles table, its columns and types

    Create the table circles, one row per circle, with exactly these eight columns and no others: id (integer, the primary key), name (up to 50 characters: varchar(50)), description (text of any length), owner_id (integer, the id of the member who made it), is_private (boolean), member_limit (integer), created_at and archived_at (both timestamptz).

    description and archived_at are optional. Every other column is required (NOT NULL).

  2. Task 2

    circles.sql: what fills itself in

    In the same circles.sql, make these columns fill themselves in when an insert leaves them out: id is numbered by the database, and nobody can set it by hand (an identity that is generated always); is_private is false; member_limit is 50; created_at is the moment of the insert.

    Afterwards, inserting a circle with only a name and an owner_id works, and every new circle gets its own id. A circle with no name, no owner_id, a name longer than 50 characters, or an id given by hand is refused.

  3. Task 3

    circle-events.sql: the circle_events table for meetups

    Create the table circle_events, one row per meetup a circle plans, with exactly these seven columns: id (bigint, the primary key, numbered by the database and never set by hand), circle_id (integer, required), title (up to 100 characters, required), starts_at (timestamptz, required), duration (interval, required, 2 hours unless given), fee (rupees with 2 decimals, up to 999999.99: numeric(8,2), required, 0 unless given) and capacity (smallint, optional: NULL means no limit).

    Afterwards, a fee of 149.999 is stored as 150.00, a fee of 1000000 is refused, and a capacity of 40000 is refused.

What you will learn

  • CREATE TABLE: a new table, one column at a time, each with a name and a type.
  • Types: varchar(n), text, integer, smallint, bigint, boolean, timestamptz, numeric(p, s) and interval, and how to pick.
  • NOT NULL and DEFAULT: which columns are required, and what a column gets when an INSERT leaves it out.
  • Identity columns: ids that the database numbers for you.

More in Building the schema

Build "A new feature, Circles" yourself

MingleSphereQL, a social app, needs answers from its data every day. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.