Tables, Rows, and Columns

Introduction

In a relational database, data is commonly organized into tables. A table is the most familiar structure in SQL because almost every beginner starts by creating a table, inserting rows, selecting columns, filtering records, and updating values. Even advanced SQL topics such as joins, indexes, constraints, normalization, transactions, reporting, and query optimization depend on a clear understanding of tables, rows, and columns.

A table is made up of rows and columns. Columns define what kind of information the table stores. Rows contain the actual records. If you imagine a spreadsheet-like structure, the column headings describe the attributes, and each horizontal line contains one record. SQL uses this structure in a more formal and controlled way than a spreadsheet because each column normally has a data type, constraints, keys, and rules enforced by the database engine.

Table
|-- Columns -> Define what data is stored
|-- Rows    -> Contain the actual records

For example, an employees table may have columns such as employee_id, name, department, and salary. Each row represents one employee. The row 101 | John | IT | 75000 means employee 101 is John, he belongs to the IT department, and his salary is 75000. The column department contains department values for all employees, while a row combines one employee's values across all columns.

Here the table is employees. The columns are employee_id, name, department, and salary. The rows are the individual employee records. This simple structure is the foundation of relational databases.

What Is a Table?

A table is a database object used to store related data in a structured format. It groups information about one subject, entity, or business concept. A table can store employees, customers, products, orders, payments, departments, accounts, tickets, test cases, users, invoices, audit logs, or any other structured data that belongs together.

In SQL, a table is created using the CREATE TABLE statement. The statement defines the table name and the columns inside it. Each column has a name and usually a data type. Additional rules such as primary keys, unique constraints, and not-null constraints can also be added.

CREATE TABLE employees (
    employee_id INT,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2)
);

This creates a table named employees. At this point, the table structure exists, but the table may not contain any rows yet. This is an important distinction: a table definition and table data are related but different. The definition describes what the table can store. The rows are the actual stored records.

A well-designed table usually has a clear purpose. An employees table should store employee data, not product data. An orders table should store order-level information, not random customer comments unless that is part of the order design. Clear table design improves SQL readability, reporting, application development, testing, maintenance, and performance tuning.

Table Structure

The structure of a table defines its columns, data types, constraints, keys, and sometimes default values. This structure is often called part of the schema. The schema tells the database what kind of values are valid and how rows should be organized. Without a table structure, the database would not know how to validate, store, compare, or process data consistently.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    city VARCHAR(100)
);

This customer table has four columns. The customer_id column is an integer and acts as the primary key. The name column is required because it uses NOT NULL. The email column must be unique, so two customers cannot have the same email value. The city column stores text values up to the allowed length.

customers
|
|-- customer_id -> INT, PRIMARY KEY
|-- name        -> VARCHAR(100), NOT NULL
|-- email       -> VARCHAR(150), UNIQUE
|-- city        -> VARCHAR(100)

When new rows are inserted, the database engine uses this structure to validate the values. If a row tries to insert a duplicate primary key, the database rejects it. If a row tries to insert a null value into name, the database rejects it. This is one reason table structure is more powerful than a simple spreadsheet layout.

Table Example

Consider a products table used by an e-commerce application. The table may contain the product ID, product name, category, price, and stock quantity. Each row represents one product available in the store. Each column represents one property of the product.

This table stores product-related information only. The row for product 101 describes a laptop. The row for product 102 describes a mouse. The row for product 103 describes a chair. The price column stores price values for all products, while the stock column stores available inventory counts.

This table can now support many business operations. A product listing page can read product names and prices. A warehouse system can update stock. A reporting query can group products by category. A search feature can filter products by price. An order system can reference a product by its product ID. These operations all depend on the table's rows and columns.

What Is a Row?

A row represents one complete record in a table. It contains values for the columns defined in the table structure. In an employees table, one row represents one employee. In a products table, one row represents one product. In an orders table, one row represents one order. In a students table, one row represents one student.

For example, this row represents one product:

101 | Laptop | Electronics | 999.99 | 20

The row has values across multiple columns. The product ID is 101, the product name is Laptop, the category is Electronics, the price is 999.99, and the stock is 20. Another row may represent a mouse:

102 | Mouse | Electronics | 29.99 | 100

Rows are sometimes called records in everyday database language. In formal relational theory, a row may be called a tuple. In interviews and real projects, row and record are both commonly understood. The important point is that a row is one complete occurrence of the entity represented by the table.

One Row Represents One Entity Instance

A table usually represents an entity type, and each row represents one instance of that entity. If the table is employees, each row is one employee. If the table is customers, each row is one customer. If the table is orders, each row is one order. This mental model helps you design tables correctly.

In this table, row 1 represents John and row 2 represents Alice. The table describes employees in general, while each row describes one specific employee. This relationship is one of the first concepts to understand before learning normalization and relationships between tables.

Rows normally change more frequently than columns. New customers are added as new rows. Existing employees are updated by changing values in their rows. Old products may be deleted or archived by removing or marking rows. The table's column structure changes less frequently because changing structure is a schema change that can affect application code, queries, reports, and integrations.

What Is a Column?

A column defines one attribute or property of the data stored in a table. It describes what kind of value can appear in that position for every row. In an employees table, common columns are employee_id, first_name, last_name, department, salary, and hire_date. Each column has a consistent meaning across all rows.

A column may also be called a field or attribute. In formal relational theory, column maps closely to attribute. In application development and business discussions, people often say field. For SQL learning, column is the most direct term.

Consider this customer data:

The city column contains Chicago and Dallas. Both values represent the same type of attribute: customer city. A column should not mix unrelated meanings. For example, a column should not store a city in one row, a phone number in another row, and a salary in another row. Consistent meaning is essential for relational design.

Rows vs Columns

The simplest distinction is that a row is one complete record, while a column is one specific attribute. A row moves horizontally across the table. A column moves vertically down the table. This is an easy memory trick for beginners.

Row    = One complete record
Column = One specific attribute

In the following small table, the first row contains employee 101's complete record. The columns are employee_id, name, and salary.

The row 101 | John | 75000 describes one employee. The salary column contains salary values for all employees. Row and column meet at a single value. For employee 101 and the salary column, the value is 75000.

Table, Row, and Column Relationship

A table combines rows and columns into a structure that represents a business concept. Columns describe the allowed attributes, and rows store individual records. The intersection of a row and column is a cell value, though SQL usually talks in terms of column values rather than spreadsheet cells.

              COLUMNS
        id      name     city
ROW     101     John     Chicago
ROW     102     Alice    Dallas
ROW     103     David    Boston

More conceptually, the table is the container, the columns are the attribute definitions, and the rows are the records. A database contains many tables, each table contains columns and rows, and SQL operations work with these structures.

DATABASE
   |
TABLE
   |
ROWS -> Individual records
   |
COLUMNS -> Attributes / properties

Understanding this relationship helps prevent common beginner mistakes. A table is not the same as a database. A row is not the same as a column. A column is not always unique. A row is not identified by its physical position. Relational databases rely on logical structure and keys, not visual position.

Number of Rows vs Number of Columns

A table can contain thousands, millions, or billions of rows. The number of columns is usually much smaller. For example, a customers table may have 8 columns and 5,000,000 rows. This means the table tracks 8 attributes for 5 million customer records.

customers table
Columns = 8
Rows    = 5,000,000

The number of rows usually grows as the business grows. More customers, orders, payments, transactions, log entries, or messages produce more rows. The number of columns grows only when the table needs to store a new attribute. Adding rows is normal application activity. Adding columns is a database design change.

Large row counts affect query performance, indexing, storage, backups, and maintenance. Wide tables with many columns can also affect performance and design quality. A table with 150 columns may be valid in some domains, but it should be reviewed carefully because it may indicate that multiple concepts are being stored in one table.

Table Schema and Empty Tables

The table schema is the structure of the table. It includes the column names, data types, constraints, and keys. A table can exist even when it contains no rows. This is common immediately after creation, before the first insert happens.

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10,2)
);

Initially, this table has three columns and zero rows. The structure exists, so SQL tools can show the table definition. Applications can prepare insert statements for it. Constraints can be enforced when data arrives. But until rows are inserted, the table contains no product records.

This distinction is important in interviews. A table definition is not the same as table data. A table can be empty and still be a valid database object. Similarly, a database can contain many empty tables during early development, before test data or production data has been loaded.

Adding Rows with INSERT

Rows are normally added using the INSERT statement. An insert provides values for the columns in a new row. It is best practice to explicitly list the columns being inserted because this makes the SQL clearer and safer if the table structure changes later.

INSERT INTO products (
    product_id,
    product_name,
    price
)
VALUES (
    101,
    'Laptop',
    999.99
);

After this insert, the products table contains one row. The product ID is 101, the product name is Laptop, and the price is 999.99. If the table has constraints, the database checks them during the insert. If product_id is a primary key, the value 101 must be unique and not null.

You can also insert multiple rows in one statement:

INSERT INTO products (
    product_id,
    product_name,
    price
)
VALUES
    (102, 'Mouse', 29.99),
    (103, 'Keyboard', 49.99),
    (104, 'Monitor', 249.99);

Each value tuple creates a separate row. Multi-row inserts can be more efficient than many individual inserts, depending on the database and workload. The concept remains the same: each row contains values that match the table's columns.

Reading Rows and Columns with SELECT

The SELECT statement reads data from tables. When you write SELECT *, you ask for all columns. When you omit a WHERE clause, you usually ask for all rows. This can be useful for small examples, but in real applications it is better to select only the columns and rows needed.

SELECT *
FROM products;

This retrieves all columns and all rows from the products table. A more focused query selects only specific columns:

SELECT product_name, price
FROM products;

This query returns only product names and prices. It does not return product IDs, categories, stock values, or any other columns. Selecting fewer columns can improve readability and reduce unnecessary data transfer, especially when tables contain large text, binary data, or many columns.

Rows can be filtered with WHERE:

SELECT *
FROM products
WHERE price > 100;

This returns only products whose price is greater than 100. The SELECT list controls columns, and the WHERE clause controls which rows qualify. Together, they allow SQL to retrieve exactly the needed part of a table.

SELECT product_name, price
FROM products
WHERE price > 100;

Updating Rows

The UPDATE statement changes existing row values. A row can contain many columns, but an update may change only one column or a few columns. The WHERE clause identifies which rows should be modified. Without a proper WHERE clause, an update may affect more rows than intended.

UPDATE products
SET price = 899.99
WHERE product_id = 101;

Before the update, product 101 may have price 999.99. After the update, product 101 has price 899.99. The row remains the same product record, but one column value changes.

Before: 101 | Laptop | 999.99
After:  101 | Laptop | 899.99

Another example updates an employee salary:

UPDATE employees
SET salary = 80000
WHERE employee_id = 101;

The employee ID, name, and department remain unchanged. Only the salary column changes for the matching employee row. This illustrates that SQL can update one attribute of one record without replacing the entire table.

Deleting Rows vs Dropping Tables

The DELETE statement removes rows from a table. It does not remove the table structure. If you delete a product row, the products table still exists with the same columns and constraints. Only the matching row is removed.

DELETE FROM products
WHERE product_id = 104;

This removes the row for product 104. The table definition remains available, and more products can still be inserted later. By contrast, DROP TABLE removes the table object itself, including its definition and rows, subject to database-specific dependency rules.

DROP TABLE products;

This distinction is critical. DELETE works with row data. DROP TABLE works with the table object. A beginner who confuses these commands can accidentally remove an entire table when the intention was only to remove selected records.

Column Data Types

Every column normally has a defined data type. A data type tells the database what kind of values are expected and how those values should be stored, compared, sorted, calculated, and validated. Common data types include integers, decimals, variable-length text, dates, timestamps, booleans, and binary values.

CREATE TABLE employees (
    employee_id INT,
    name VARCHAR(100),
    salary DECIMAL(10,2),
    hire_date DATE
);

Data types matter because they protect meaning and improve consistency. An employee ID should be numeric if the system treats it as a number. A salary should use a precise numeric type because money-like values should not be stored as free text. A hire date should use a date type so the database can compare and sort it correctly.

Good data types also improve storage efficiency and query behavior. A column that stores only small integer status codes does not need a large text type. A column that stores descriptions needs enough space. Choosing correct data types is one of the first steps in good table design.

Column Constraints

Constraints are rules applied to columns or tables. They help protect data quality and business correctness. Common constraints include PRIMARY KEY, NOT NULL, UNIQUE, CHECK, and FOREIGN KEY. Constraints are enforced by the database engine, not just by application code.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    age INT CHECK (age >= 18)
);

This table requires each customer to have a unique primary key. The name cannot be null. The email must be unique when provided. The age must be at least 18. These constraints make every row more reliable because invalid data is rejected before it becomes part of the table.

Constraints are especially useful in shared systems. A database may be updated by web applications, backend services, import jobs, admin scripts, and reporting tools. If validation exists only in one application, another process may bypass it. Database constraints provide centralized protection for the table data.

Primary Key Columns

A primary key identifies each row uniquely. It is one of the most important concepts in relational databases. Without a reliable key, it can be difficult to update, delete, join, or reference a specific row. A primary key value should not be duplicated, and it should not be null.

Here customer_id can be the primary key. Customer 101 and customer 102 are clearly different rows. Even if two customers have the same name, their customer IDs can distinguish them.

customer_id INT PRIMARY KEY

Duplicate-looking rows are a common problem in poor design. If a table stores only name and city, two identical rows may appear:

Which John is which? A better design includes a unique identifier:

Foreign Key Columns

A foreign key creates a relationship between tables. It allows one table to reference rows in another table. This is one of the core ideas behind relational databases. Instead of storing all information in one huge table, related data is separated into tables and connected through key columns.

For example, customers and orders can be stored in separate tables. The customers table has a primary key called customer_id. The orders table also has a customer_id column that references the customer who placed the order.

CUSTOMERS.customer_id
        |
ORDERS.customer_id

This relationship says orders belong to customers. Customer 101 has orders 5001 and 5002. Customer 102 has order 5003. Foreign keys help preserve referential integrity so an order does not point to a customer that does not exist.

NULL Values and NOT NULL Columns

A column can sometimes contain NULL. NULL means the value is missing, unknown, or not applicable depending on the business meaning. It does not automatically mean zero, and it does not necessarily mean an empty string. NULL is a special marker used by SQL to represent absence of a known value.

Alice's phone value is NULL. That may mean the phone number is unknown, not collected, or not applicable. The business definition matters. SQL handles NULL differently from normal values, so learners should treat it carefully when filtering, comparing, and aggregating data.

If a column must always have a value, define it as NOT NULL:

name VARCHAR(100) NOT NULL

Then an insert that tries to store a null name can fail:

INSERT INTO customers (
    customer_id,
    name
)
VALUES (
    101,
    NULL
);

Required columns should be marked clearly because they express business rules. A customer may require a name. An order may require an order date. A payment may require an amount. Constraints make those rules enforceable.

Column Order and Row Order

Tables have a defined column structure, but SQL queries should preferably specify the columns they actually need. Relying on SELECT * can make application code fragile because changes to table structure may change the result shape. Selecting named columns improves clarity.

SELECT name, salary
FROM employees;

This is clearer than selecting every column when only two columns are required. It also makes the result easier to understand and can reduce unnecessary data transfer.

Row order is a separate concept. An important SQL rule is that rows in a relational table do not have a guaranteed query-result order unless you explicitly request one using ORDER BY. A query such as this does not guarantee a stable order:

SELECT *
FROM employees;

To guarantee ordering, use ORDER BY:

SELECT *
FROM employees
ORDER BY employee_id;

This matters in applications and tests. Do not assume the "first row" means anything unless you define the ordering. The database engine is free to return rows in an order based on execution plan, indexes, storage, or other internal factors unless you specify the order.

Column Aliases and Calculated Columns

Columns can be given temporary display names in query results using aliases. An alias changes the output label for the query result. It does not rename the actual table column.

SELECT
    employee_id AS id,
    name AS employee_name,
    salary AS annual_salary
FROM employees;

In this result, employee_id may appear as id, and name may appear as employee_name. The table definition remains unchanged. Aliases are useful for reports, readable output, calculations, joins, and expressions.

A query can also return calculated values that are not permanently stored columns. For example:

SELECT
    name,
    salary,
    salary * 12 AS annual_salary
FROM employees;

Here annual_salary is calculated for the query result. It does not automatically become a stored column in the table. The database evaluates the expression and returns it as part of the result set. This distinction helps beginners understand the difference between table structure and query output.

Table Relationships Through Columns

Columns are what allow relational tables to connect. A customer table, order table, and payment table may be separate, but key columns create a relationship chain. This avoids storing all data repeatedly in one large table and supports cleaner design.

customers.customer_id
        |
orders.customer_id
        |
payments.order_id

An e-commerce database may contain tables such as customers, products, orders, order_items, and payments. The customers table stores customer details. The products table stores product details. The orders table stores order headers. The order_items table connects orders to products and quantities. The payments table stores payment information.

Rows and columns together allow the system to represent a complete business transaction. One customer row can be related to many order rows. One order row can be related to many order item rows. Each order item row can reference a product row. This is the relational model in practice.

Table Definition vs Table Data

A table has structure and data. The structure includes column names, data types, constraints, and keys. The data is the actual rows. These two concepts work together but should not be confused.

TABLE STRUCTURE
employee_id INT
name VARCHAR(100)
salary DECIMAL(10,2)

TABLE DATA
101 | John  | 75000
102 | Alice | 65000

Structure changes are schema changes. Adding a column, dropping a column, changing a data type, or adding a constraint changes the table definition. Data changes happen through inserts, updates, and deletes. Applications perform data changes frequently. Schema changes are less frequent and require more planning.

For example, adding a new customer creates a row. Changing a customer's city updates a row. Adding a new business requirement such as date of birth may require a new column. Dropping a column can remove stored values and break queries, so it requires care.

Adding and Removing Columns

A table's structure can be modified using ALTER TABLE. Adding a column extends the table structure. Existing rows may receive NULL or a default value depending on the database and column definition.

ALTER TABLE employees
ADD email VARCHAR(150);

Before this change, the table may have employee_id, name, and salary. After the change, it also has email. This can be necessary when the business starts tracking a new attribute.

Before:
employee_id
name
salary

After:
employee_id
name
salary
email

Removing a column changes the schema and can remove the data stored in that column. It may also affect views, stored procedures, reports, application code, test automation, exports, and integrations.

ALTER TABLE employees
DROP COLUMN email;

Because schema changes can have wide impact, real projects usually review them carefully, test migrations, and coordinate deployments.

Table and Column Naming

Good table names clearly represent the data they contain. Examples include customers, employees, orders, products, and payments. Poor names such as data1, table2, info, or tempstuff make databases harder to understand and maintain.

Good column names describe their meaning. Examples include customer_id, order_date, total_amount, and email_address. Poor names such as c1, data2, value, or x create confusion because the reader cannot understand the business meaning without external explanation.

Consistent naming standards are important in real projects. Some teams use singular table names, while others use plural names. Some use snake_case, while others use camelCase or PascalCase depending on the platform. The specific style matters less than consistency, clarity, and avoiding ambiguous names.

Wide Tables and Large Tables

A wide table contains many columns. A table with 150 columns may be considered wide. Wide tables are not automatically wrong, but they should be reviewed carefully. Sometimes they indicate that different concepts have been combined into one table. Sometimes the domain genuinely requires many attributes.

Table
|-- column_1
|-- column_2
|-- column_3
...
|-- column_150

A large table contains many rows. A transactions table may contain millions or billions of rows. Large tables often require careful attention to indexing, partitioning, query design, storage, archiving, backups, and maintenance.

transactions
Rows    = 2,000,000,000
Columns = 12

Table size is more than row count. It depends on the number of rows multiplied by the data stored per row, plus indexes and storage overhead. One million tiny rows may use less storage than one hundred thousand rows containing large text or binary values. Understanding rows and columns helps you estimate table growth and design better storage strategies.

Tables and Indexes

Tables store the primary row data. Indexes provide additional access structures that help locate rows faster. An index is not the table itself. It is a separate structure maintained by the database engine.

Table
  |
Actual Rows

Index
  |
Search Structure
  |
Helps Locate Rows

For example, an index on employee name can help queries that search by name:

CREATE INDEX idx_employee_name
ON employees(name);

Indexes improve many read operations, but they also add maintenance cost. When rows are inserted, updated, or deleted, related indexes may also need to change. Good SQL design uses indexes where they help real query patterns rather than indexing every column blindly.

Tables and Views

A table normally stores data. A view generally stores a query definition rather than independently storing result data, except for materialized or indexed view variants in some database systems. Views present selected rows and columns from underlying tables in a reusable way.

CREATE VIEW high_salary_employees AS
SELECT employee_id, name, salary
FROM employees
WHERE salary > 100000;

This view presents employees whose salary is greater than 100000. The view can be queried like a table in many situations, but conceptually it is based on a stored query definition. The underlying employees table still stores the actual employee rows.

Views are useful for simplifying complex queries, exposing limited columns, improving readability, supporting reporting, and controlling access. However, beginners should remember that a view and a table are not always the same thing. A table is a storage object for rows. A view is usually a saved query over tables.

Common Mistakes

A common beginner mistake is reversing rows and columns. The correct memory rule is simple: rows are horizontal records, and columns are vertical attributes. A row moves across many columns. A column contains one type of value across many rows.

Row    -> Horizontal record
Column -> Vertical attribute

Another mistake is thinking a table is the same as a database. A database can contain many tables. For example, an ecommerce database may contain customers, products, orders, payments, employees, shipments, reviews, discounts, and audit tables. Each table is one object inside the database.

Database
|-- customers
|-- products
|-- orders
|-- payments
|-- employees

Another mistake is assuming every column must have unique values. That is not true. A department column may contain IT for many employees. A city column may contain Chicago for many customers. Only columns with uniqueness rules, such as primary keys or unique constraints, must have unique values.

A final important mistake is identifying a row by position. Do not depend on first row, second row, or third row to identify data. Use a key such as employee_id = 101. A database does not guarantee that the same row will always appear first unless an explicit ORDER BY is used.

Real-World Employee Table

A practical employee table may include an employee ID, first name, last name, department, salary, and hire date. The employee ID acts as the primary key. Names are required. Department, salary, and hire date describe attributes of each employee.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10,2),
    hire_date DATE
);

In this example, employees is the table. Individual employees are rows. Properties describing employees are columns. The value 75000 is the salary column value for employee 101. The value Finance is the department column value for employee 103.

This same pattern appears in almost every business database. A hospital may have patient rows. A college may have student rows. A bank may have account rows. A testing tool may have test case rows. A stock trading application may have transaction rows. Tables organize the records; columns give those records meaning.

Interview-Ready Explanation

A short interview answer is: a table is a database object that stores related data in rows and columns. A row represents one complete record, and a column represents one attribute or property of that record.

A stronger answer is: in a relational database, tables organize data for one entity or business concept. Columns define the structure, including names, data types, constraints, and keys. Rows contain the actual records. SQL statements such as SELECT, INSERT, UPDATE, and DELETE operate on these rows and columns. Primary keys uniquely identify rows, and foreign keys use columns to create relationships between tables.

You can also mention common traps. Rows are horizontal records, while columns are vertical attributes. A table is not the same as a database. Query result order is not guaranteed unless ORDER BY is used. A table can exist with columns but no rows. Not every column must be unique; uniqueness applies only when a key or unique constraint is defined.

Key Takeaway

The foundation of a relational database is simple but powerful. A table is an organized collection of related records. A row is one individual record. A column is one attribute or property of those records.

TABLE
   |
Organized collection of related records

ROW
   |
One individual record

COLUMN
   |
One attribute or property of those records

For example, employees is a table. The record 101 | John | IT | 75000 is a row. The salary field is a column. The row and column intersect at the value 75000. This basic relationship powers almost every SQL operation.

Understanding tables, rows, and columns is fundamental because almost every SQL operation works with these structures. SELECT reads rows and columns. INSERT adds rows. UPDATE changes column values in rows. DELETE removes rows. JOIN connects tables through columns. WHERE filters rows. ORDER BY sorts result rows. Indexes help find rows through column values. Constraints protect row and column correctness. Once you understand these basics clearly, the rest of SQL becomes much easier to learn.