In PostgreSQL, a database is composed of tables that store structured data in rows and columns. Tables represent real-world entities like customers, accounts, or transactions in a banking system. Each column in a table defines a specific type of data (e.g., customer name, account balance), and each row represents an individual entry or record (e.g., a specific customer or transaction). Understanding tables, columns, and rows is fundamental when working with relational databases like PostgreSQL.

Here’s an overview of how tables are structured, followed by examples related to banking systems:

1. Creating a Table in PostgreSQL Tables are defined using theCREATE TABLE command. You specify the name of the table, columns, and data types. For example, to create aCustomers

   CREATE TABLE Customers (
   CustomerID SERIAL PRIMARY KEY,
   Name VARCHAR(100) NOT NULL,
   Email VARCHAR(100) UNIQUE,
   PhoneNumber VARCHAR(15),
   Address TEXT,
   DateOfBirth DATE,
   CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
   );
   

   This creates a table with the following columns:

   • CustomerID: A unique ID for each customer.
   • Name: The customer’s name.
   • Email: The customer’s email, which must be unique.
   • PhoneNumber: The customer’s phone number.
   • Address: The customer’s address.
   • DateOfBirth: The customer’s date of birth.
   • CreatedAt: The timestamp when the customer record was created, with a default value of the current time.
2. Creating Related Tables In a banking system, you may also need to define tables likeAccounts andTransactions. Each account is tied to a customer, and each transaction is tied to an account.
   • Accounts Table:

   CREATE TABLE Accounts (
   AccountID SERIAL PRIMARY KEY,
   CustomerID INT REFERENCES Customers(CustomerID),
   AccountNumber VARCHAR(20) UNIQUE NOT NULL,
   AccountType VARCHAR(50),
   Balance NUMERIC(12, 2) DEFAULT 0.00,
   CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
   );
   

   • AccountID: A unique ID for each account.
   • CustomerID: References the customer who owns the account.
   • AccountNumber: A unique account number.
   • AccountType: Type of the account (e.g., savings, checking).
   • Balance: The current balance of the account.
   • CreatedAt: The timestamp when the account was created.
3. Defining a Transactions Table The Transactions table logs all deposits, withdrawals, and transfers between accounts:

   CREATE TABLE Transactions (
   TransactionID SERIAL PRIMARY KEY,
   AccountID INT REFERENCES Accounts(AccountID),
   TransactionType VARCHAR(50) NOT NULL,
   Amount NUMERIC(12, 2) NOT NULL,
   TransactionDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
   );
   

   • TransactionID: A unique ID for each transaction.
   • AccountID: References the account associated with the transaction.
   • TransactionType: Describes the transaction type (e.g., deposit, withdrawal).
   • Amount: The transaction amount.
   • TransactionDate: The date and time when the transaction occurred.
4. Inserting Data into Tables creating the tables, you can insert rows into them. For example, to add a new customer:

   INSERT INTO Customers (Name, Email, PhoneNumber, Address, DateOfBirth)
   VALUES ('John Doe', 'john@example.com', '123-456-7890', '123 Elm St', '1985-05-15');
   
   

5. Retrieving Data from Tables To view the data in a table, use the SELECT For example, to retrieve all customers:

   SELECT * FROM Customers;
   

   You can also filter the results. For example, to get only customers with a specific email:

   SELECT * FROM Customers WHERE Email = 'john@example.com';
   
   

This structure gives students a solid foundation in understanding tables, columns, and rows in PostgreSQL, with practical examples tied to a banking system. The code samples and explanations will help them see how real-world entities map to database tables.

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

Database Tables and Their Components

Database tables are designed to store data in a structured format, using rows and columns. Each table represents a specific type of entity, such as users, products, or orders, with the columns representing attributes of that entity. Understanding these components is crucial for effective database design and management.

Components of Database Tables

The unique identifier for a table within a database, descriptive of the data it holds.

Columns represent the attributes of the entity. Each column has a specific data type and can be defined with various constraints:

• Data Type: The kind of data a column can hold (e.g., VARCHAR, INT, DATE).
• Constraints: Rules for the data stored in a column, including NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT.

Individual instances of the entity, with each row having a unique identifier through the primary key.

Special lookup tables that speed up data retrieval, analogous to an index in a book.

Defines how tables relate to each other, including One-to-One, One-to-Many, and Many-to-Many relationships.

Sample SQL Code

 CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    Age INT CHECK (Age > 0),
    EnrollmentDate DATE DEFAULT CURRENT_DATE
);

INSERT INTO Students (StudentID, FirstName, LastName, Email, Age)
VALUES (1, 'John', 'Doe', 'john.doe@example.com', 20);

SELECT * FROM Students WHERE Age >= 18;

UPDATE Students SET Email = 'new.email@example.com' WHERE StudentID = 1;

DELETE FROM Students WHERE StudentID = 1;

DATA TABLE - TOP 10 WEBSITES

DATA TABLE - TOP 10 POPULATED COUNTRIES

DATA TABLE - TOP 10 FOOTBALL TEAMS

DATA TABLE - TOP 10 CRICKET TEAMS

POSTGRESQL - EMPLOYEE TABLE WITH DATA

POSTGRESQL - CLIENTS TABLE WITH DATA

MYSQL TABLE LISTING

EXCEL DATA TABLE - PATIENT DATA

GRAPH DATA - NOT A SQL TABLE

Employee Table with Data

Employee JSON Document, NOT A SQL TABLE

[
{
    "employee_id": 1,
    "employee_number": "EMP-01",
    "first_name": "George",
    "last_name": "Bush",
    "extension": 101,
    "email": "George.Bush@tansyacademy.com",
    "designation": "CEO",
    "date_of_birth": null,
    "salary": 130000,
    "city": "Niagara Falls",
    "state": "NY",
    "department_id": 2
},
{
    "employee_id": 2,
    "employee_number": "EMP-02",
    "first_name": "Joe",
    "last_name": "Biden",
    "extension": 102,
    "email": "Joe.biden@tansyacademy.com",
    "designation": "CTO",
    "date_of_birth": null,
    "salary": 65000,
    "city": "Long Beach",
    "state": "NY",
    "department_id": 2
}
]