SQL (Structured Query Language) is a standard programming language used to manage and manipulate relational databases. It allows you to create, read, update, and delete data in a structured way using tables, which are made up of rows (records) and columns (fields). PostgreSQL, as a powerful open-source relational database system, uses SQL to define and manage data structures such as tables, which organize information in rows and columns for easy retrieval and management.

Here’s how tables, columns, and rows work in PostgreSQL with an example from a banking database system:

1. Creating Tables:
   • In PostgreSQL, tables are where the data is stored. Each table consists of columns (fields), which define the type of data stored, and rows (records), which hold the actual data.
   • Example: To create a table for customers in a banking system:

      CREATE TABLE customers (
      customer_id SERIAL PRIMARY KEY,
      first_name VARCHAR(50) NOT NULL,
      last_name VARCHAR(50) NOT NULL,
      email VARCHAR(100) UNIQUE,
      phone VARCHAR(15),
      address TEXT
   );
   
   

2. Adding Columns to a Table:
   • Columns represent the type of data in the table. Each column has a specific data type (likeVARCHAR, INTEGER, or TEXT) that defines what kind of data it can hold.
   • In thecustomers table above, we have several columns:
     • customer_id: A unique identifier for each customer (auto-incremented).
     • first_name andlast_name: Strings for storing customer names.
     • email: A string to store the customer's email address, which must be unique.
     • phone: A string to store the customer's phone number.
     • address: A text field for storing the customer's address.
3. Inserting Rows (Records):
   • Rows are the actual data stored in the table. Each row represents one entry, and every column in that row contains a value.
   • Example: To insert a row of data into the customers table:

      INSERT INTO customers (first_name, last_name, email, phone, address)
      VALUES ('John', 'Doe', 'john.doe@example.com', '123-456-7890', '123 Main St,
      Anytown, USA');
      
   

4. Defining a Table for Bank Accounts:
   • Let’s create a table to store information about customer bank accounts.

     CREATE TABLE accounts (
     account_id SERIAL PRIMARY KEY,
     customer_id INTEGER REFERENCES customers(customer_id),
     account_type VARCHAR(20) NOT NULL,
     balance DECIMAL(15, 2) DEFAULT 0.00,
     opened_on DATE DEFAULT CURRENT_DATE
     );
     
     

   • Here:
     • account_id: A unique identifier for the account.
     • customer_id: A foreign key referencing the customers table to link accounts to specific customers.
     • account_type: The type of account (e.g., savings, checking).
     • balance: The current balance in the account.
     • opened_on: The date the account was opened, with a default value of the current date.
5. Defining a Table for Transactions:
   • To track customer transactions, we need a transactions table.

     CREATE TABLE transactions (
     transaction_id SERIAL PRIMARY KEY,
     account_id INTEGER REFERENCES accounts(account_id),
     transaction_date DATE DEFAULT CURRENT_DATE,
     amount DECIMAL(10, 2) NOT NULL,
     transaction_type VARCHAR(10) CHECK (transaction_type IN ('credit', 'debit')),
     description TEXT
     );
     
     

   • In this table:
     • transaction_id: A unique identifier for each transaction.
     • account_id: A foreign key linking each transaction to an account.
     • transaction_date: The date of the transaction.
     • amount: The transaction amount.
     • transaction_type: Specifies whether the transaction is a credit or debit.
     • description: Additional details about the transaction.
6. Retrieving Data (Using SELECT Queries):
   • Once data is in the tables, you can retrieve it using the SELECTstatement.
   • Example: To get all customers from the customers table:

     SELECT * FROM customers;
     
     

   • Example: To find all transactions for a specific account:

     SELECT * FROM transactions WHERE account_id = 1;
     
     

This structured explanation introduces the fundamental concepts of SQL and PostgreSQL using simple examples that are easy for beginners to follow, particularly within the context of a banking system.

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