Tables, Rows, Columns, and Data Types

Before writing powerful SQL queries, you need to understand how data is actually stored.

This article explains:

• What tables, rows, and columns really are
• Why schemas and data types matter
• How developers and analysts think about data structure differently

This is Part 02 of the SQL from Zero to Pro series.

Table of Contents

• What Is a Database Table?
• Rows: Records in a Table
• Columns: Attributes and Fields
• Schemas and Table Structure
• Common SQL Data Types
• Why Data Types Matter
• SQL for Developers vs Analysts
• Practice Exercises
• Conclusion
• Solutions to the exercises from the article

What Is a Database Table?

A table is where relational databases store data.

Think of a table as a spreadsheet:

• Columns define what kind of data is stored
• Rows store individual records

Example: customers Table

Each table usually represents one real-world concept, such as:

• customers
• orders
• products
• employees

Rows: Records in a Table

A row represents a single record.

In the customers table:

• One row = one customer
• Each row contains values for every column

(1, Alice, alice@email.com, UK)
(2, Bob, bob@email.com, PT)

Primary Keys

Most tables have a primary key:

• Uniquely identifies each row
• Often an id column

id INT PRIMARY KEY

🧠 No two rows can share the same primary key value.

Columns: Attributes and Fields

A column defines a single attribute of the data.

Examples:

• name
• email
• price
• created_at

Each column has:

• A name
• A data type
• Optional constraints

name VARCHAR(100)

Column Rules

• Every value in a column must follow the same data type
• Columns describe what kind of data is allowed

Schemas and Table Structure

A schema is a logical container for tables.

Think of it like:

• A folder inside a database
• A way to organise tables

database
 └── public
     ├── users
     ├── orders
     └── products

Why Schemas Matter

Schemas help with:

• Organisation
• Permissions
• Avoiding name collisions

Common SQL Data Types

Data types define what kind of values a column can store.

Why Data Types Matter

Choosing the right data type affects:

• Storage size
• Performance
• Data accuracy

Example

price VARCHAR(10)   ❌
price DECIMAL(10,2) ✅

Benefits of Correct Data Types

• Prevent invalid data
• Speed up queries
• Enable indexing
• Reduce bugs

SQL for Developers vs Analysts

SQL for Developers

Developers care about:

• Data integrity
• Constraints
• Performance

Common focus areas:

• Primary and foreign keys
• NOT NULL and UNIQUE constraints
• Index-friendly data types
• Schema migrations

SQL for Data Analysts

Analysts care about:

• Querying and aggregations
• Readability
• Business meaning

Common focus areas:

• Column naming clarity
• Date/time consistency
• Numeric precision
• NULL handling

Practice Exercises

Exercise 1 – Identify the Structure

Given this table definition, identify:

• Table name
• Columns
• Data types

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(10,2),
  in_stock BOOLEAN
);

Exercise 2 – Design a Table

Design a table called employees with:

• id
• first name
• last name
• email
• hire date

Write the CREATE TABLE statement.

Exercise 3 – Think Like a Database

Why is DATE a better choice than VARCHAR for a birth date?

Write your answer in plain English.

Bonus Practice

Create a table locally using SQLite or an online SQL editor and try:

• Adding columns
• Inserting rows
• Selecting specific columns

Conclusion

Tables, rows, columns, and data types form the foundation of every relational database.

If you understand:

• How tables are structured
• Why data types exist
• Then writing SQL queries becomes dramatically easier.

In the next article (SQL 03), we’ll dive into SELECT queries and filtering data, where SQL really starts to feel powerful.

Happy querying 🚀

Solutions to the exercises from the article

Exercise 1 – Identify the Structure (Answer)

Given the table definition:

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(10,2),
  in_stock BOOLEAN
);

Table Name

• products

Columns and Data Types

Primary Key

• id is the primary key
• It uniquely identifies each row in the table

Exercise 2 – Design a Table (Answer)

Requirements Recap

Create a table called employees with:

• id
• first name
• last name
• email
• hire date

One Possible Solution

CREATE TABLE employees (
  id INT PRIMARY KEY,
  first_name VARCHAR(100),
  last_name VARCHAR(100),
  email VARCHAR(255),
  hire_date DATE
);

Why These Choices?

• INT for id: efficient and commonly indexed
• VARCHAR for names and email: flexible length
• DATE for hire date: enables date-based queries and sorting

Exercise 3 – Think Like a Database (Answer)

Question

Why is DATE a better choice than VARCHAR for a birth date?

Answer (Plain English)

Using the DATE data type ensures that:

• Only valid dates can be stored
• Dates can be sorted correctly
• Date calculations (age, duration) are possible
• Indexing and filtering are faster

If dates are stored as ‘VARCHAR’, the database:

• Cannot reliably compare or sort them
• Cannot perform date arithmetic
• Cannot enforce date validity

Next up: SQL 03 – SELECT Queries and Filtering Data 🚀