Tables, rows and columns
30 min · Beginner
Need help? Ask a tutor on WhatsApp (opens in a new tab)A relational database keeps all its data in tables: named grids where every row is one record and every column is one kind of fact. Once you can read a table, you can read any query result.
A relational database is a set of tables
A relational database stores data in tables that are related to each other through keys. Each table holds one kind of thing, and a column in one table can point to a row in another.
A table is a named grid of data, a little like one sheet in a spreadsheet. The ShopLite practice store has five tables: categories, customers, products, orders and order_items. Each table holds one kind of thing.
Example 1 · One table: the store's categories
SELECT * FROM categories ORDER BY category_id;+-------------+-------------+-----------+ | category_id | name | parent_id | +-------------+-------------+-----------+ | 1 | Electronics | NULL | | 2 | Mobiles | 1 | | 3 | Laptops | 1 | | 4 | Accessories | 1 | | 5 | Books | NULL | | 6 | Fashion | NULL | | 7 | Toys | NULL | +-------------+-------------+-----------+
What this code does
SELECT *means "all columns". The table has three columns and seven rows, one per category.parent_idsays which category a row belongs under: Mobiles, Laptops and Accessories (parent 1) sit under Electronics. Top-level categories haveNULLthere.
A database that stores data this way is called relational, because its tables are related: an order row says which customer placed it, an order item says which product was bought. MySQL, PostgreSQL, Oracle and SQL Server are all relational databases.
Unlike a spreadsheet, a table has strict rules. Every column has a fixed type, and the database refuses values that break the rules.
Rows are records, columns are fields
A row is one record and a column is one field, the same kind of fact for every row. NULL means a cell has no value: it is not zero and not an empty text.
A row is one record: one customer, one product, one order. A column is one field, the same kind of fact for every row: name, city, phone. The single value where a row meets a column is a cell, or simply a value.
Example 2 · Rows, columns and NULL in customers
SELECT customer_id, name, phone, cityFROM customersORDER BY customer_id;+-------------+--------------+------------+-----------+ | customer_id | name | phone | city | +-------------+--------------+------------+-----------+ | 1 | Asha Rao | 9876500001 | Bengaluru | | 2 | Ravi Kumar | 9876500002 | Chennai | | 3 | Meena Iyer | NULL | Chennai | | 4 | Kabir Singh | 9876500004 | Delhi | | 5 | Priya Sharma | 9876500005 | Pune | | 6 | Arjun Mehta | NULL | Mumbai | | 7 | Divya Nair | 9876500007 | Kochi | | 8 | Rahul Verma | 9876500008 | Delhi | | 9 | Sneha Patil | 9876500009 | Pune | | 10 | Imran Khan | NULL | Hyderabad | +-------------+--------------+------------+-----------+
What this code does
- Each of the ten rows is one customer; each of the four columns is one fact about every customer.
- Meena Iyer, Arjun Mehta and Imran Khan did not give a phone number, so their
phonecell isNULL, not an empty text.
Rows have no built-in order. The database may return them in any order it likes, so a query that needs a fixed order says ORDER BY. Every query with more than one row in this course sorts its rows, so your output matches the lesson.
When a cell has no value, the database stores NULL, which means "unknown" or "not given".
Predict the output
Predict the rows
Use the customers table from the lesson. How many rows come back?
Reading a table printed by the mysql client
The mysql client is the terminal program you use to talk to MySQL (you install it in this module). It prints every result as a text table with +---+ borders. The first line inside the border is the header: the column names.
Example 3 · A small IRCTC-style table
DROP TABLE IF EXISTS trains;CREATE TABLE trains ( train_no INT, train_name VARCHAR(40), from_city VARCHAR(30), to_city VARCHAR(30), fare DECIMAL(8,2));INSERT INTO trains VALUES (12007, 'Shatabdi Express', 'Chennai', 'Mysuru', 1255.00), (12657, 'Bengaluru Mail', 'Chennai', 'Bengaluru', 455.50), (22691, 'Rajdhani Express', 'Bengaluru', 'Delhi', NULL);SELECT * FROM trains ORDER BY train_no;+----------+------------------+-----------+-----------+---------+ | train_no | train_name | from_city | to_city | fare | +----------+------------------+-----------+-----------+---------+ | 12007 | Shatabdi Express | Chennai | Mysuru | 1255.00 | | 12657 | Bengaluru Mail | Chennai | Bengaluru | 455.50 | | 22691 | Rajdhani Express | Bengaluru | Delhi | NULL | +----------+------------------+-----------+-----------+---------+
What this code does
- The first statements build a tiny table and add three rows; the module on creating tables explains them. Only the last statement prints a table.
- Read the result:
train_noandfareare numbers, so they line up on the right; names and cities are text, so they line up on the left. The Rajdhani fare is not set yet, so it printsNULL.
Numbers are lined up on the right and text on the left, so you can tell them apart at a glance. A missing value prints as NULL. After the table, the client also prints a line such as 3 rows in set (0.00 sec); the lessons leave that line out because the timing changes on every run.
Every column has a name and a type
A data type says what kind of value a column may hold, such as INT for whole numbers or DATE for dates. The database refuses any value that does not fit the type of its column.
Each column has a name and a data type: the kind of value it may hold.
Example 4 · DESCRIBE a table, then break its types
DESCRIBE products;INSERT INTO products (name, category_id, price, stock)VALUES ('Spiral Notebook', 5, 60.00, 'many');+-------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------+------+-----+---------+----------------+ | product_id | int | NO | PRI | NULL | auto_increment | | name | varchar(80) | NO | | NULL | | | category_id | int | YES | MUL | NULL | | | price | decimal(10,2) | NO | | NULL | | | stock | int | NO | | 0 | | +-------------+---------------+------+-----+---------+----------------+ ERROR 1366 (HY000): Incorrect integer value: 'many' for column 'stock' at row 1
What this code does
DESCRIBEprints one row per column: its name (Field), its type, whether it may beNULL, whether it is a key (PRI= primary key,MUL= a key that may repeat, such as a foreign key), its default value and extras such asauto_increment.stockis anint, so the textmanyis refused with error 1366 and no row is added. In real data a stock count is a number, and the database makes sure it stays one.
INT holds whole numbers, VARCHAR(60) holds text of up to 60 characters, DECIMAL(10,2) holds exact amounts such as prices with two digits after the point, and DATE holds dates such as 2025-06-01.
DESCRIBE table_name; lists the columns of a table with their types. The next module, Creating Tables & Data Types, covers each type in detail. The type is a promise the database keeps. If you try to put the text many into an INT column, MySQL refuses the whole row with an error.
Fix the error
Fix the type mistake
The column stock is an INT. Pick the line that fixes the error.
Add a notebook to the store.
Error
ERROR 1366 (HY000): Incorrect integer value: 'many' for column 'stock' at row 1
Keys that link one table to another, first look
A primary key is a column whose value is different in every row, so it names exactly one row. A foreign key is a column that holds another table's primary key, which links a row to a row in that table.
A primary key is a column whose value is different in every row, so it names exactly one row. customer_id is the primary key of customers: customer 1 is always Asha Rao.
Example 5 · Follow a key from an order to its customer
SELECT order_id, customer_id, order_date, statusFROM ordersWHERE customer_id = 1ORDER BY order_id;
SELECT customer_id, name, cityFROM customersWHERE customer_id = 1;+----------+-------------+------------+-----------+ | order_id | customer_id | order_date | status | +----------+-------------+------------+-----------+ | 1 | 1 | 2025-01-05 | DELIVERED | | 3 | 1 | 2025-02-02 | DELIVERED | | 10 | 1 | 2025-04-12 | SHIPPED | +----------+-------------+------------+-----------+ +-------------+----------+-----------+ | customer_id | name | city | +-------------+----------+-----------+ | 1 | Asha Rao | Bengaluru | +-------------+----------+-----------+
What this code does
- Orders 1, 3 and 10 all hold
customer_id1, the foreign key. The second query looks up that key incustomersand finds Asha Rao from Bengaluru. - The customer's name and city are stored once, in
customers. Every order points to them by id instead of copying them. The Joins module shows how to combine both tables in one query.
Example 6 · An order for a customer who does not exist
INSERT INTO orders (customer_id, order_date)VALUES (99, '2025-06-10');ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`practice`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`))
What this code does
- There is no customer 99, so the foreign key check fails and the order is refused. The error names the database (
practicehere, after the practice-database lesson), the table, the ruleorders_ibfk_1and the column it links. - This is a sample because the first part of the message holds your database's name, which may differ from
practice.
orders.customer_id is a foreign key: order 3 has customer_id 1, so it was placed by Asha Rao. This is how tables "relate".
The database checks every foreign key, as the second example shows. The Keys & Constraints module covers both keys in depth.
Next step
Check your understanding
10 quick questions