Only adults may join

Write the rules into the table, and let the database turn bad rows away.

What to build

A members table whose constraints keep out anyone who may not join.

Requirements

• Create members with id INTEGER PRIMARY KEY, a name that must be given, and an age that must be given and be at least 18.
• Copy the applicants in with the INSERT OR IGNORE given in the starter.
• Show every column of members, in order of id.

Tables

Each test fills these tables with its own rows, then runs your SQL:

CREATE TABLE applicants (
  id INTEGER PRIMARY KEY,
  name TEXT,
  age INTEGER
);

Output

Columns id, name and age, in order of id. Your result is compared with the expected one, column names included, so name each column exactly as asked (use AS).

Constraints

An applicant's name or age may be NULL.

Example

When a test starts with:

INSERT INTO applicants VALUES (1, 'Rahim', 20), (2, 'Tuhin', 9), (3, 'Nadia', 18);

your SQL should print:

id  name   age
--  -----  ---
1   Rahim  20
3   Nadia  18
(2 rows)

Why it works

CHECK (age >= 18) on its own lets a NULL age through, because the check is unknown rather than false. NOT NULL closes that gap, and OR IGNORE skips the rows the rules refuse.

Examples

CREATE TABLE applicants (
  id INTEGER PRIMARY KEY,
  name TEXT,
  age INTEGER
);
INSERT INTO applicants VALUES (1, 'Rahim', 20), (2, 'Tuhin', 9), (3, 'Nadia', 18);

id  name   age
--  -----  ---
1   Rahim  20
3   Nadia  18
(2 rows)

CREATE TABLE applicants (
  id INTEGER PRIMARY KEY,
  name TEXT,
  age INTEGER
);
INSERT INTO applicants VALUES (1, NULL, 30), (2, 'Karim', NULL);

id  name  age
--  ----  ---
(0 rows)

Each test makes its own tables, runs your SQL on them, and compares what your queries return. Changes never carry over from one test to the next.

Hints

Solution

CREATE TABLE members (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  age INTEGER NOT NULL CHECK (age >= 18)
);

INSERT OR IGNORE INTO members (id, name, age)
SELECT id, name, age FROM applicants ORDER BY id;

SELECT * FROM members ORDER BY id;

Lesson: Keys and constraints →