SQLIntroduction to SQLbeginner35 min read

Relational Databases, Tables, Rows, and Columns

Understand how relational databases organize information into tables, rows, and columns, how schemas structure database objects, and how relationships and integrity constraints help maintain reliable data.

On This Page (Table of Contents)

In this lesson, you will learn

✓Explain the basic idea behind the relational database model.
✓Identify tables, rows, columns, schemas, and databases.
✓Distinguish a row from a column using practical examples.
✓Explain primary keys, foreign keys, and basic data integrity.
✓Recognize why relational databases store related information in separate tables.
✓Inspect table-column metadata using information_schema in PostgreSQL.

1. What Is a Relational Database?

A relational database is a database that organizes data according to the relational model. In everyday SQL work, you will usually see this data represented as tables made up of rows and columns.

Imagine a college needs to store information about students. It could maintain a table containing student IDs, names, and email addresses. Each row describes one student, and each column describes one kind of information about students.

The relational model also provides ways to connect related data, such as linking students to courses. Instead of placing every possible detail into one enormous table, a database can store different kinds of information separately and connect them using keys.

The mathematical relational model is more precise than the everyday idea of a table. In particular, a mathematical relation is a set of tuples, whereas SQL tables can have behaviors such as duplicate rows unless constraints prevent them.

Note

Remember the central idea: tables organize records, columns describe attributes, and keys help identify and connect records.

2. Understand the Basic Terminology

Consider this student table:

Each part has a specific meaning. Understanding these terms makes SQL queries, database design, and interview questions much easier.

2. Understand the Basic Terminology

Term	Meaning	Example
Database	An organized collection of data and database objects	A college database
Schema	A namespace that organizes database objects	public in PostgreSQL
Table	A database object that stores data in rows and columns	students
Row	One record in a table	101, Asha, 20, Lucknow
Column	One attribute represented for every row	name
Data type	The declared kind of value a column can store	INTEGER or VARCHAR
Primary key	A constraint that uniquely identifies each row	student_id
Foreign key	A constraint that references a key in another or the same table	student_id in enrollments

3. Rows vs Columns

A row and a column describe data from two different directions.

A row goes across the table. It groups the values belonging to one record. In the example, the row beginning with 101 describes one student.

A column goes down the table. It groups the values for one attribute across multiple records. The name column contains Asha, Rahul, and Neha.

A simple way to remember this is:

Row = one record.
Column = one attribute.

When writing a query, you commonly select columns and filter rows.

3. Rows vs Columns

sql

1
2
SELECT name, city
FROM students;

This query requests the name and city columns from the students table. It returns those values for the rows selected by the query.

Note

In SELECT name, city, the names after SELECT are columns. The table after FROM is the source of the rows.

4. Creating a Table

Before inserting records, you normally define the table's structure. This structure includes column names and declared data types. You can also define constraints that specify which values are allowed.

The following example uses PostgreSQL syntax.

4. Creating a Table

sql

1
2
3
4
5
6
CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INTEGER,
    city VARCHAR(100)
);

CREATE TABLE creates a table named students. student_id is an integer primary key. name must have a value because it is declared NOT NULL. age is an integer column, and city stores character data. The definition does not impose a particular age range; that would require an additional constraint.

Note

Declaring a column's data type does not enforce every business rule. For example, INTEGER alone does not prevent a negative age. Add suitable constraints when such rules must be enforced by the database.

5. Inserting and Reading Rows

Once a table exists, INSERT can add records and SELECT can read them.

Each value in the INSERT column list corresponds to a value in the VALUES list. Writing the column names explicitly makes the statement easier to understand and less dependent on the table's column order.

5. Inserting and Reading Rows

sql

1
2
3
4
5
6
7
8
9
INSERT INTO students (student_id, name, age, city)
VALUES
    (101, 'Asha', 20, 'Lucknow'),
    (102, 'Rahul', 21, 'Delhi'),
    (103, 'Neha', 19, 'Kanpur');
 
SELECT student_id, name
FROM students
ORDER BY student_id;

The INSERT statement adds three rows. The SELECT statement returns the student_id and name columns. ORDER BY makes the requested output order explicit.

Note

The displayed records are an example of what these statements produce when run successfully against a newly created, empty table.

6. Why Do Tables Need Keys?

Suppose two students have the same name. If you use only the name to identify a student, you may not know which record you mean.

A primary key solves this problem by uniquely identifying each row. A primary key must be unique and cannot be NULL.

A foreign key serves a different purpose: it checks that a referenced value matches an eligible key in the referenced table, subject to the foreign-key definition. This helps represent relationships between tables.

6. Why Do Tables Need Keys?

sql

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE courses (
    course_id INTEGER PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL
);
 
CREATE TABLE enrollments (
    student_id INTEGER,
    course_id INTEGER,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

courses stores course records. enrollments connects students and courses. Its composite primary key prevents the same student-course pair from being inserted twice. The foreign keys require referenced student and course records to exist.

Note

A primary key identifies a row. A foreign key helps maintain a valid reference between records. They are related concepts, but they are not interchangeable.

7. Why Separate Related Data into Tables?

Imagine storing a student's name, email, course name, instructor, and instructor contact details in one row for every course enrollment. If a student joins several courses, the student's details may be repeated many times. Instructor details may also be repeated.

This repetition can lead to update problems. If an instructor changes their email address, several rows may need updating. If one row is missed, the database may contain conflicting information.

A relational design can instead use separate students, courses, instructors, and enrollments tables, connected by keys. This often reduces duplication and helps keep updates consistent.

Not every repeated value is automatically a design error. Good database design depends on the meaning of the data, the relationships, and the rules the application needs to enforce.

One large table vs Related tables

Example: One large table

script.py

python

Related details may be repeated across many rows, increasing the risk of inconsistent updates.

Example: Related tables

script.py

python

Each kind of record can be stored in a suitable table, with keys connecting related records.

8. Inspecting Table Columns in PostgreSQL

You do not always need to guess a table's structure. PostgreSQL exposes metadata through information_schema.columns.

The following query lists the columns visible to the current user for a table named students in the public schema.

8. Inspecting Table Columns in PostgreSQL

sql

1
2
3
4
5
6
7
8
SELECT
    column_name,
    data_type,
    is_nullable
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'students'
ORDER BY ordinal_position;

column_name identifies each column, data_type reports its declared type in the information schema, and is_nullable indicates whether the column allows NULL according to the metadata. ordinal_position orders columns as defined.

Note

The result depends on the table that actually exists and the metadata visible to your database user. The query does not create or modify any table.

9. Common Mistakes

New learners often confuse records with attributes, assume that column types enforce all business rules, or store multiple independent values in one column. These mistakes can make queries harder to write and data harder to maintain.

10. Knowledge Check: MCQs

Test your understanding before moving on. Try answering each question first, then reveal the answer and explanation.

11. Guided Practice

Use the students table created earlier.

Task 1: Write a query that returns every column for every student.

Task 2: Write a query that returns only name and city.

Task 3: Write a query that returns students older than 19, showing their names and ages.

Task 4: Explain why student_id is a better primary-key choice than name.

Task 5: Create a departments table with department_id as its primary key and department_name as a required column.

11. Guided Practice

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Task 1
SELECT *
FROM students;
 
-- Task 2
SELECT name, city
FROM students;
 
-- Task 3
SELECT name, age
FROM students
WHERE age > 19;
 
-- Task 5
CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

Task 4 is conceptual: names may be duplicated or changed, while a properly assigned student_id can uniquely identify each student. Tasks 1–3 query the existing table; Task 5 creates a new table.

12. Interview Questions

Q1. What is a relational database?

It is a database organized around the relational model. In SQL systems, data is commonly represented using tables and relationships between them.

Q2. What is the difference between a row and a column?

A row represents one record; a column represents an attribute shared across records.

Q3. What is a schema?

A schema is a namespace that groups database objects. In PostgreSQL, the public schema is a commonly used example.

Q4. What is the difference between a primary key and a foreign key?

A primary key uniquely identifies a row in its table. A foreign key enforces a reference to an eligible key in another or the same table.

Q5. Do data types guarantee that all stored data is valid?

No. Data types restrict the kinds of values a column can store, but additional constraints may be needed for rules such as non-negative ages or valid date ranges.

Q6. Why normalize related data into separate tables?

Normalization can reduce unnecessary duplication and prevent update, insertion, and deletion anomalies while making relationships explicit. The appropriate design depends on the application's requirements.

13. Lesson Summary

A relational database organizes information using relations, commonly represented as SQL tables.
A row is a record; a column is an attribute.
A schema organizes database objects.
Data types define the kinds of values a column can store.
Constraints such as PRIMARY KEY, FOREIGN KEY, and NOT NULL help enforce data integrity.
Related tables can represent relationships without repeatedly copying every detail.
PostgreSQL information_schema.columns exposes metadata about table columns.
SELECT reads data, INSERT adds records, and CREATE TABLE defines a table’s structure.

You now have the vocabulary needed to understand table creation, filtering, and relationships in upcoming SQL lessons.

Common pitfalls to avoid

Real debugging lessons and exact syntax contrasts to avoid runtime bugs

#1 Confusing a row with a column

Why it happens: Both are parts of a table, but one represents a record and the other represents an attribute.

Incorrect Approach

sql

Calling the name column one student record.

Correct Solution

sql

Treating one row as a student record and name as a column.

Key takeaway: A row groups values for one record. A column groups values for one attribute across records.

#2 Using a person's name as the only identifier

Why it happens: Names look meaningful, so beginners may assume they are unique.

Incorrect Approach

sql

Using name as the only identifier when two students can be named Rahul.

Correct Solution

sql

Assigning each student a unique student_id and defining it as the primary key.

Key takeaway: A primary key must uniquely identify each row and cannot be NULL.

#3 Assuming a data type enforces every business rule

Why it happens: Beginners may assume INTEGER means an integer must also be logically valid.

Incorrect Approach

sql

Using age INTEGER and assuming negative ages are rejected.

Correct Solution

sql

Using age INTEGER CHECK (age >= 0) when non-negative ages are a required rule.

Key takeaway: A data type restricts value representation; a CHECK constraint can enforce an additional condition.

#4 Storing multiple independent values in one column

Why it happens: Putting comma-separated values in a single field can initially seem easier.

Incorrect Approach

sql

Storing course names as 'SQL, Python, C++' in one student column when each course must be queried independently.

Correct Solution

sql

Representing courses in a courses table and student-course relationships in an enrollments table.

Key takeaway: Separate related records make individual values easier to query, validate, and maintain.

#5 Assuming information_schema shows data rows

Why it happens: The word schema can be confused with the data stored in a table.

Incorrect Approach

sql

Using information_schema.columns to retrieve student names.

Correct Solution

sql

Using SELECT name FROM students to retrieve names, and information_schema.columns to inspect column metadata.

Key takeaway: Table data and metadata about table structure serve different purposes.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →

Key takeaways

A relational database organizes data into relations, commonly represented in SQL as tables.
A row represents one record, while a column represents one attribute of that record.
A table's schema describes its structure, including its columns and their declared data types.
Primary keys identify rows, and foreign keys can connect records across tables.
Constraints help protect data integrity, but the rules enforced depend on the constraints actually defined.
Related information is often stored in separate tables to reduce unnecessary duplication and improve consistency.

Was this lesson helpful?

Found an error? Report an issue