Dev Learning Lab

The labs on this site need JavaScript to run. The same content is written out below.

Fundamentals › Database

Tables, rows, and columns

What does a table look like, and what are rows and columns?

A relational database keeps data in tables. CREATE TABLE is the statement that makes a table and decides its columns (the vertical slots). Each column has a name and a type, which decide what it holds, as in "the id column holds an integer and the name column holds text". INSERT is the statement that puts in one row (a horizontal line, the information of one person), and a row must match the number and the types of the columns. SELECT is the statement that reads the values of chosen columns from the rows. This lab runs the same SQL while you remove CREATE TABLE or change the shape of the second row and the column that is read, and shows which statements the database accepts and which it rejects.

Checked against real execution · Ran all 16 combinations on H2 2.4.240 and checked against them (the rules were checked against the MySQL documentation)

Concepts in this lab

Table
A table is the grid in which a relational database holds data. A table has a name, such as member, and the columns it has (the kinds of slots) are decided with CREATE TABLE. Data is stored as rows (lines) that match those columns, and you put in rows with INSERT and read them with SELECT. Before you make a table, you cannot INSERT into it or SELECT from it by that name. One database can have many tables.
Rows and columns
A table in a relational database is made of columns and rows, like a spreadsheet. A column is a kind of cell whose name and type are fixed when the table is created. For example, id and name are the columns of the member table. A row is one line filled with values for those columns, and the information about one member is one row. INSERT adds one row, UPDATE changes every row that matches the WHERE condition, and SELECT reads rows. A column with no value is shown as NULL.
Column data type
The data type of a column is the kind of value that the column can hold. You write it with the name of each column in CREATE TABLE. For example, id INT is an integer column, and name VARCHAR(20) is a column that holds text of variable length up to 20 characters. The MySQL 8.4 documentation divides types into five kinds: numeric, date and time, string, spatial, and JSON. If you try to put in a value that does not match the type, the database usually rejects that INSERT, but how strict it is depends on the database and its settings.
CREATE TABLE, INSERT, SELECT
These three are SQL statements. SQL is the language that tells a relational database what to do, and each sentence of it is called an SQL statement. CREATE TABLE is the statement that makes a table and decides the names and types of its columns (for example, CREATE TABLE member (id INT, name VARCHAR(20))). INSERT is the statement that puts in one row, and if you do not write the column names separately, the values after VALUES go into the columns in the order in which the columns were written in CREATE TABLE. SELECT is the statement that reads rows, and if you write a column name, as in SELECT name FROM member, it reads only the values of that column, and if you write *, it reads the values of all columns. A statement usually ends with a semicolon (;).

Try these

Running with the default settings

  1. CREATE TABLE: the database makes an empty table named member and remembers the names and types of the columns. There are two columns, id (INT, a column that holds integers) and name (VARCHAR(20), a column that holds text), and there are no rows yet.
  2. INSERT INTO member VALUES (1, 'Alice'): the two values go into the two columns in order. The first value, 1, goes into the id column, and the second value, 'Alice', goes into the name column, and the line filled in this way is one row. The table has 1 row now.
  3. INSERT INTO member VALUES (2, 'Bob'): it tries to put in the second row. Before it puts in a row, the database checks whether the number of values is the same as the number of columns, and whether each value matches the type of its column (an integer for the id column, text for the name column). We did not write the code that does this check. The database does it by itself each time an INSERT arrives. There are two values, and 2 is an integer and 'Bob' is text, so the count and the types both match the two columns.
  4. Result of the DB check: it passes. The Bob row went in, and the table now has 2 rows.
  5. SELECT name FROM member: the database reads only the values of the name column from all the rows of the table and returns them. It does not read the id column. It read 2 rows, and the result is Alice, Bob.

Result of this run

Code

-- Table: make a table named member and decide two columns (id, name)
CREATE TABLE member (
    id   INT,
    name VARCHAR(20)
);

-- Row: put in one line with the information of one person
INSERT INTO member VALUES (1, 'Alice');
INSERT INTO member VALUES (2, 'Bob');

SELECT name FROM member;

Assumptions of this model

This was checked against real execution on H2 2.4.240 (in-memory database, JDBC). All 16 combinations were checked: the whole SQL on the screen (including the comment lines) was run exactly as it is, one statement at a time, and even if one statement failed, the next statement kept running. What was checked against the run is whether the second INSERT went in or was rejected and the kind of error (H2 SQLSTATE 22018 the value cannot be converted to the type, 21S02 the number of values differs from the number of columns, 42S04 no table, 42S22 no column), the number of rows after the two INSERT statements, and the values the last SELECT read. That the number of rows right after CREATE TABLE is 0 and that the database remembers the names and types of the columns (id integer, name text of 20 characters) was checked by reading the metadata. H2 rejected an INSERT that has two columns but only one value, and this is behavior observed on H2, not something defined by the documentation. In the tutorial of the MySQL 8.4 documentation, Creating a Table (CREATE TABLE decides the names and types of the columns, and VARCHAR is text of variable length), Loading Data into a Table (the values of INSERT go in in the order in which the columns were written in CREATE TABLE, and NULL can be put in directly), Selecting All Data and Selecting Particular Columns (SELECT * gives all rows and columns, and writing column names gives only those columns), and the opening of Chapter 13 Data Types (the types are of five kinds: numeric, date and time, string, spatial, and JSON) were opened directly and checked. But the rule that an INSERT whose types or count do not match is rejected is not something these documents define, and it was checked by running H2. A check against execution on MySQL was not done. The text of the error messages, and how strictly a value that does not match is rejected, may differ by database and settings. The screen shows only the kind of error, and the error numbers and the original text of the messages exist only in the output of the checking program. This lab covers only the two types INT and VARCHAR, and it does not cover constraints such as a primary key, multiple tables, or WHERE conditions.

Previous lab: JPQL · Next lab: Primary keys

Concepts in this lab

What this lab shows

Settings

Code


      
    


    

      
Diagram

      

      

    


    

      

      

      

    


  


  

    

      
Run comparison

      

      

    

    

      
Try these