In MySQL, tables consist of columns and rows. Columns define the type of data that can be stored in a table, while rows hold the actual data (records) for each column. Each row in a table represents a complete data entry, and each column represents a specific field within the entry. Understanding the relationship between rows and columns is key to effectively organizing data in MySQL.

Below is a breakdown of how rows and columns work, using tables for company, employees, departments, and branches.

1. Defining Columns for the company Table

When defining columns in a table, each column is given a name, a data type, and optional constraints (such asNOT NULL or UNIQUE).

CREATE TABLE company (
company_id INT AUTO_INCREMENT PRIMARY KEY,
company_name VARCHAR(100) NOT NULL,
founded_year YEAR NOT NULL,
company_address VARCHAR(255),
company_phone VARCHAR(20)
);

1. company_id: A column that stores the company’s unique ID.
2. company_name: A column that stores the company’s name. It’s required (NOT NULL).
3. founded_year: A column that stores the year the company was founded, using the YEAR data type.
4. company_address:A column that stores the company’s address (optional).
5. company_phone: A column that stores the company’s phone number.

2. Understanding Rows in the company Table

After creating the table, data can be inserted into rows. Each row is a complete record that holds data for every column in the table.

INSERT INTO company (company_name, founded_year, company_address,
company_phone)
VALUES ('Tech Innovations', 2010, '123 Tech Street, Silicon Valley',
'1234567890');
    

1. The first row for the company table stores:
   • company_name: 'Tech Innovations'
   • founded_year: 2010
   • company_address: '123 Tech Street, Silicon Valley'
   • company_phone: '+1234567890'

3.Defining Columns for the employees Table

The employees table stores details of individual employees. Each column defines a piece of information about an employee:

CREATE TABLE employees(
employee_id INT AUTO_INCREMENT PRIMARY KEY,-- Column 1
first_name VARCHAR(50) NOT NULL,            -- Column 2
last_name VARCHAR(50) NOT NULL,             -- Column 3
email VARCHAR(100),                         -- Column 4
phone_number VARCHAR(20),                   -- Column 5
hire_date DATE NOT NULL,                    -- Column 6
department_id INT                           -- Column 7
);

1. employee_id: Stores the unique ID for each employee.
2. first_name: Stores the employee’s first name.
3. last_name: Stores the employee’s last name.
4. email: Stores the employee’s email (optional).
5. phone_number: Stores the employee’s phone number (optional).
6. hire_date:Stores the date the employee was hired.
7. department_id:A column to reference the department the employee belongs to.

4. Understanding Rows in the employees Table

Data inserted into rows of the employees table would look like this:

INSERT INTO employees (first_name, last_name, email, phone_number, hire_date, department_id)
VALUES ('John', 'Doe' 'johndoe@example.com', '+987654321',
'2023-01-01', 1);

1. The first row for the employees table stores:

• first_name: 'John'
• last_name: 'Doe'
• email: 'johndoe@example.com'
• phone_number: '+987654321'
• hire_date: '2023-01-01'
• department_id: 1

5. Defining Columns for the departments Table

Thedepartmentstable stores the different departments within the company. Each column represents a piece of information about a department:

CREATE TABLE departments (
department_id INT AUTO_INCREMENT PRIMARY KEY, -- Column 1
department_name VARCHAR(100) NOT NULL         -- Column 2
);

1. department_id: Stores the unique ID for each department.
2. department_name: Stores the name of the department

6. Defining Columns for the branchesTable

Thebranchestable stores information about the different company branches. Columns define details about each branch:

CREATE TABLE branches (
branch_id INT AUTO_INCREMENT PRIMARY KEY, -- Column 1
branch_name VARCHAR(100 NOT NULL,        -- Column 2
location VARCHAR(255) NOT NULL,           -- Column 3
branch_manager_id INT                    -- Column 4
);

1. branch_id: Stores the unique ID for each branch.
2. branch_name: Stores the name of the branch.
3. location: Stores the location of the branch.
4. branch_manager_id: References the employee managing the branch.

This explanation of rows and columns should help beginners understand the basic structure of tables in MySQL and how they are used to store data in a relational format. The example tables for company, employees, departments, and branches demonstrate how to organize real-world data into rows and columns.

Understanding Rows and Columns in SQL Tables

Columns

Columns represent the attributes or fields of the data table, each designed to hold a specific type of information.

• DataType: Dictates the kind of data a column can store (e.g., integers, text, dates).
• Column Name: A unique name within the table that identifies the column.

Rows

Rows represent individual records or data entries, containing a unique instance of data for the columns.

• PrimaryKey: Uniquely identifies each row in the table.
• Uniqueness: Each row should have a unique combination of values.

Example Table: Employees

SQL Operations on Rows and Columns

Inserting Data

Adds new rows to the table.

INSERT INTO Employees (EmployeeID, FirstName, LastName, Email, HireDate) VALUES (3, 'Alice', 'Johnson', 'alice.johnson@example.com', '2020-03-20');

Querying Data

Retrieves data from the table, potentially filtering both rows and columns.

SELECT FirstName, LastName FROM Employees WHERE HireDate > '2020-01-01';

Updating Data

Modifies existing rows in the table.

UPDATE Employees SET Email = 'new.email@example.com' WHERE EmployeeID = 1;

Deleting Data

Removes rows from the table.

DELETE FROM Employees WHERE EmployeeID = 2;

SQL Column Components

SQL column components define the structure, constraints, and behavior of data within a database table. Below are the key components and attributes for SQL columns:

1. Data Type

Specifies the kind of data a column can store. Common types include:

• INTEGER: For whole numbers.
• VARCHAR(n): For variable-length strings, where n defines the maximum length.
• CHAR(n): For fixed-length strings, with n defining the string length.
• DATE: For dates.
• FLOAT, DOUBLE: For floating-point numbers.

2. Default Value

Automatically assigns a specific value if no value is provided during row insertion.

Age INT DEFAULT 18

3. Not Null Constraint

Ensures a column cannot store a NULL value, requiring every row to have a value for this column.

Name VARCHAR(100) NOT NULL

4. Unique Constraint

Ensures all values in the column are unique across the table, important for non-primary key uniqueness.

Email VARCHAR(100) UNIQUE

5. Primary Key Constraint

A unique identifier for each row, cannot be NULL and must be unique. Can be a single column or a combination of columns.

CustomerID INT PRIMARY KEY

6. Foreign Key Constraint

Establishes a link between the data in two tables, referencing the primary key of another table to enforce data integrity.

OrderID INT FOREIGN KEY REFERENCES Orders(OrderID)

7. Check Constraint

Specifies a condition on a column that must be true for all rows, used to enforce domain integrity.

Age INT CHECK (Age >= 18)

8. Auto Increment

Automatically assigns a unique value to the column for each new row, commonly used for ID columns.

CustomerID INT AUTO_INCREMENT

Example

Combining these components, here's an example of a table creation statement in SQL:

CREATE TABLE Customers (
    CustomerID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    Age INT DEFAULT 18 CHECK (Age >= 18),
    Address VARCHAR(255)
);

Each column component plays a specific role in defining how data is stored, validated, and related to other tables' data.

EMPLOYEE TABLE WITH DATA

Employee Table with Data

To gain complete access, login with gmail or outlook, no need of signup, click here