Three new students

Add rows to a table that already has rules for ids and defaults.

What to build

INSERT statements that add three students, followed by a query that shows the table.

Requirements

• Add Rahim in EEE with CGPA 3.45, Karim with only his name, and Nadia in BBA with CGPA 3.8, in that order.
• Let SQLite choose every id.
• Then show every column of every student, in order of id.

Tables

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

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT DEFAULT 'CSE',
  cgpa REAL
);

Output

Columns id, name, dept and cgpa, 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

The table may already hold students, with any ids.

Example

When the tables start empty,

your SQL should print:

id  name   dept  cgpa
--  -----  ----  ----
1   Rahim  EEE   3.45
2   Karim  CSE   NULL
3   Nadia  BBA   3.8
(3 rows)

Why it works

An INTEGER PRIMARY KEY left out of an INSERT gets one more than the largest id so far. A left-out column with a DEFAULT takes the default, and one without becomes NULL.

Examples

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT DEFAULT 'CSE',
  cgpa REAL
);

id  name   dept  cgpa
--  -----  ----  ----
1   Rahim  EEE   3.45
2   Karim  CSE   NULL
3   Nadia  BBA   3.8
(3 rows)

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT DEFAULT 'CSE',
  cgpa REAL
);
INSERT INTO students VALUES (10, 'Tania', 'CSE', 3.1);

id  name   dept  cgpa
--  -----  ----  ----
10  Tania  CSE   3.1
11  Rahim  EEE   3.45
12  Karim  CSE   NULL
13  Nadia  BBA   3.8
(4 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

INSERT INTO students (name, dept, cgpa) VALUES ('Rahim', 'EEE', 3.45);
INSERT INTO students (name) VALUES ('Karim');
INSERT INTO students (name, dept, cgpa) VALUES ('Nadia', 'BBA', 3.8);

SELECT * FROM students ORDER BY id;

Lesson: Creating tables and adding rows →