Tables, Rows & Columns

A table is a database object used to store related information in a structured format.

A table is made up of rows and columns.

For example, a students table may look like this:

students

id | name  | age | course
---|-------|-----|--------
1  | Rahul | 21  | Python
2  | Priya | 22  | SQL
3  | Amit  | 20  | Java

Here, students is the name of the table.

A row represents one complete record in a table.

For example:

1 | Rahul | 21 | Python

The above row contains information about one student.

Rows are also commonly called records.

A column represents a particular type or attribute of data in a table.

For example, the students table has the following columns:

• id
• name
• age
• course

Columns are also commonly called fields.

A table can be visualized as a grid containing columns and rows.

The four headings are columns, while each horizontal entry below the headings represents a row.

Every table has a name that identifies it within a database.

Examples:

• students
• employees
• products
• orders
• customers
• payments

A good table name should clearly describe the type of information stored in the table.

Each column should have a meaningful name that describes the data stored in that column.

For example:

students

student_id
student_name
mobile
email
course

Meaningful names make database structures easier to understand and maintain.

Every column normally has a defined data type that determines what kind of values it can store.

Common SQL data types include:

• INT – Whole numbers
• VARCHAR – Variable-length text
• DATE – Date values
• DECIMAL – Exact numeric values
• BOOLEAN – Boolean values where supported

Example:

student_id INT
name VARCHAR(100)
fee DECIMAL(10,2)
admission_date DATE

The CREATE TABLE statement is used to create a new table.

CREATE TABLE students (
    id INT,
    name VARCHAR(100),
    age INT,
    course VARCHAR(100)
);

This creates a table named students with four columns.

The INSERT INTO statement is used to add rows to a table.

INSERT INTO students
(id, name, age, course)
VALUES
(1, 'Rahul', 21, 'Python');

This statement adds one student record to the table.

Multiple rows can be inserted using one INSERT statement.

INSERT INTO students
(id, name, age, course)
VALUES
(1, 'Rahul', 21, 'Python'),
(2, 'Priya', 22, 'SQL'),
(3, 'Amit', 20, 'Java');

This adds three records to the students table.

The SELECT statement is used to retrieve information from a table.

SELECT * FROM students;

The asterisk * means all columns.

The query returns all rows and columns from the students table.

Instead of selecting every column, we can specify only the columns we need.

SELECT name, course
FROM students;

This query returns only the name and course columns.

The ALTER TABLE statement can be used to modify the structure of an existing table.

For example, to add an email column:

ALTER TABLE students
ADD email VARCHAR(150);

The students table now contains an additional email column.

The UPDATE statement is used to modify existing data.

UPDATE students
SET course = 'Django'
WHERE id = 1;

This changes the course of the student whose ID is 1.

The DELETE statement removes rows from a table.

DELETE FROM students
WHERE id = 3;

This removes the student whose ID is 3.

A primary key uniquely identifies each row in a table.

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    course VARCHAR(100)
);

Here, the id column is the primary key.

Two rows should not have the same primary key value.

A NULL value represents missing, unknown, or inapplicable data. It is different from zero and different from an empty string.

For example:

id | name  | email
---|-------|----------------
1  | Rahul | rahul@mail.com
2  | Priya | NULL

In this example, Priya's email value is NULL.

Constraints are rules applied to columns to help maintain valid and consistent data.

Common constraints include:

• PRIMARY KEY
• FOREIGN KEY
• NOT NULL
• UNIQUE
• DEFAULT
• CHECK

A database normally contains multiple tables for different types of information.

For example, a school database may contain:

• students
• teachers
• courses
• attendance
• payments
• exams

Related tables can be connected using keys.

Consider a student management system.

students
----------------
id
name
course_id

courses
----------------
id
course_name
fee

The course_id in the students table can refer to the id in the courses table.

This allows the application to store student and course information separately while maintaining a relationship between them.

Suppose we want to create an employee table.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(100),
    salary DECIMAL(10,2)
);

This table contains four columns:

• employee_id
• employee_name
• department
• salary

A well-designed table makes a database easier to maintain and use.

• Use meaningful table names.
• Use meaningful column names.
• Choose suitable data types.
• Use primary keys where appropriate.
• Use relationships between related tables.
• Avoid unnecessary duplicate data.
• Use appropriate constraints.
• Keep table structure simple and organized.

Consider an online shopping website. It may contain tables such as:

• customers
• products
• orders
• order_items
• payments

Each table stores a specific type of information. Relationships between these tables allow the application to manage customers, products, orders, and payments together.

Here is a simple example showing table creation, inserting data, and retrieving data.

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    course VARCHAR(100)
);

INSERT INTO students
(id, name, age, course)
VALUES
(1, 'Rahul', 21, 'Python'),
(2, 'Priya', 22, 'SQL');

SELECT * FROM students;

This example demonstrates the basic relationship between tables, rows, columns, and SQL statements.