Enter password: *****

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 22

Server version: 8.0.41 MySQL Community Server - GPL

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use punebank;

Database changed

mysql> -- =======================================

mysql> -- DDL COMMANDS

mysql> -- =======================================

mysql>

mysql> -- 1. ALTER TABLE Person: Add a column 'Gender'

mysql> ALTER TABLE Person ADD COLUMN Gender VARCHAR(10);

ERROR 1060 (42S21): Duplicate column name 'Gender'

mysql> SELECT * FROM Person;

+----------+----------+-----------+-------------+-----------------------------+-------------+-------------+---------------+--------
+------+
| PersonID | LastName | FirstName | DateOfBirth | Email | PhoneNumber | Address |
TaxIdentifier | Gender | DOB |

+----------+----------+-----------+-------------+-----------------------------+-------------+-------------+---------------+--------
+------+

| 1 | Sharma | Amit | 1990-01-15 | [email protected] | 9999999991 | Pune |

TAX001 | NULL | NULL |

| 2 | Patil | Rohit | 1992-03-22 | [email protected] | 9999999992 | Pune |

TAX002 | NULL | NULL |

| 3 | Desai | Sneha | 1995-07-19 | [email protected] | 9999999993 | Pune |

TAX003 | NULL | NULL |

| 4 | Joshi | Neha | 1991-11-11 | [email protected] | 9999999994 | Pune |

TAX004 | NULL | NULL |

| 5 | Kumar | Anil | 1988-12-25 | [email protected] | 9999999995 | Pune |

TAX005 | NULL | NULL |

| 6 | Gupta | Ravi | 1993-09-30 | [email protected] | 9999999996 | Pune |

TAX006 | NULL | NULL |

| 7 | Mehta | Priya | 1996-06-05 | [email protected] | 9999999997 | Pune |

TAX007 | NULL | NULL |

| 8 | Kulkarni | Vikas | 1989-05-18 | [email protected] | 9999999998 | Pune |

TAX008 | NULL | NULL |

| 9 | Reddy | Kiran | 1994-04-12 | [email protected] | 9999999999 | Pune |

TAX009 | NULL | NULL |

| 10 | Singh | Pooja | 1990-10-10 | [email protected] | 9999999910 | Pune |

TAX010 | NULL | NULL |

| 11 | Patil | Sneha | 1995-08-14 | [email protected] | 9876543211 | Pune, India |

TXP112233 | NULL | NULL |

| 12 | Sharma | Abhishek | 1996-04-23 | [email protected] | 1029837464 | Pune,

India | TXP1112313 | NULL | NULL |

+----------+----------+-----------+-------------+-----------------------------+-------------+-------------+---------------+--------
+------+

12 rows in set (0.00 sec)

mysql>

mysql> -- 2. ALTER TABLE Account: Add a CHECK constraint

mysql> ALTER TABLE Account ADD CONSTRAINT chk_balance CHECK (CurrentBalance >= 0);

ERROR 3822 (HY000): Duplicate check constraint name 'chk_balance'.

mysql> SELECT * FROM Account;

+-----------+---------------+-------------+----------------+------------+------------+---------------+

| AccountID | AccountNumber | AccountType | CurrentBalance | DateOpened | DateClosed |

AccountStatus |

+-----------+---------------+-------------+----------------+------------+------------+---------------+

| 1 | ACC10001 | Savings | 50000.00 | 2020-01-01 | NULL | Active |

| 2 | ACC10002 | Current | 75000.00 | 2020-02-01 | NULL | Active |

| 3 | ACC10003 | Savings | 100000.00 | 2020-03-01 | NULL | Active |

| 4 | ACC10004 | Savings | 25000.00 | 2020-04-01 | NULL | Active |

| 5 | ACC10005 | Current | 60000.00 | 2020-05-01 | NULL | Active |

| 6 | ACC10006 | Savings | 45000.00 | 2020-06-01 | NULL | Inactive |

| 7 | ACC10007 | Savings | 150000.00 | 2020-07-01 | NULL | Active |

| 8 | ACC10008 | Savings | 30000.00 | 2020-08-01 | NULL | Active |

| 9 | ACC10009 | Current | 90000.00 | 2020-09-01 | NULL | Active |

| 10 | ACC10010 | Savings | 80000.00 | 2020-10-01 | NULL | Active |

+-----------+---------------+-------------+----------------+------------+------------+---------------+

10 rows in set (0.00 sec)

mysql>

mysql> -- 3. CREATE INDEX on BranchName

mysql> CREATE INDEX idx_BranchName ON Branch(BranchName);

ERROR 1061 (42000): Duplicate key name 'idx_BranchName'

mysql> SELECT * FROM Branch;

+----------+------------------+------------+---------------------+-------------+

| BranchID | BranchName | BranchCode | Address | PhoneNumber |

+----------+------------------+------------+---------------------+-------------+

| 1 | Pune Main | PM001 | MG Road, Pune | 0201234567 |

| 2 | Pune West | PW001 | FC Road, Pune | 0201234568 |

| 3 | Pune East | PE001 | Kalyani Nagar, Pune | 0201234569 |

| 4 | Pune South | PS001 | Swargate, Pune | 0201234570 |

| 5 | Pune North | PN001 | Chinchwad, Pune | 0201234571 |

| 6 | Pune Central | PC001 | Camp, Pune | 0201234572 |

| 7 | Pune Airport | PA001 | Airport Road, Pune | 0201234573 |

| 8 | Pune Hadapsar | PH001 | Hadapsar, Pune | 0201234574 |

| 9 | Pune Kothrud | PK001 | Kothrud, Pune | 0201234575 |

| 10 | Pune Viman Nagar | PV001 | Viman Nagar, Pune | 0201234576 |

+----------+------------------+------------+---------------------+-------------+

10 rows in set (0.00 sec)

mysql>

mysql> -- 4. ALTER TABLE Loan: Modify LoanAmount datatype

mysql> ALTER TABLE Loan MODIFY LoanAmount DECIMAL(12,2);

Query OK, 10 rows affected (0.94 sec)

Records: 10 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM Loan;

+--------+----------+------------+--------------+------+------------+------------+------------+

| LoanID | LoanType | LoanAmount | InterestRate | Term | StartDate | EndDate | LoanStatus |

+--------+----------+------------+--------------+------+------------+------------+------------+

| 1 | Home | 1000000.00 | 7.50 | 240 | 2020-01-01 | 2040-01-01 | Active |

| 2 | Car | 500000.00 | 9.00 | 60 | 2020-02-01 | 2025-02-01 | Active |

| 3 | Personal | 200000.00 | 12.00 | 36 | 2020-03-01 | 2023-03-01 | Closed |

| 4 | Home | 1500000.00 | 8.00 | 300 | 2020-04-01 | 2045-04-01 | Active |

| 5 | Car | 400000.00 | 9.50 | 48 | 2020-05-01 | 2024-05-01 | Active |

| 6 | Personal | 300000.00 | 13.00 | 24 | 2020-06-01 | 2022-06-01 | Closed |

| 7 | Home | 1200000.00 | 7.80 | 240 | 2020-07-01 | 2040-07-01 | Active |

| 8 | Car | 550000.00 | 9.20 | 60 | 2020-08-01 | 2025-08-01 | Active |

| 9 | Personal | 250000.00 | 11.50 | 36 | 2020-09-01 | 2023-09-01 | Closed |

| 10 | Home | 1100000.00 | 7.20 | 240 | 2020-10-01 | 2040-10-01 | Active |

+--------+----------+------------+--------------+------+------------+------------+------------+

10 rows in set (0.00 sec)

mysql>

mysql> -- 5. ALTER TABLE Transaction: Add FOREIGN KEY constraint

mysql> ALTER TABLE Transaction ADD CONSTRAINT fk_transaction_account FOREIGN KEY (AccountID)
REFERENCES Account(AccountID);

ERROR 1072 (42000): Key column 'AccountID' doesn't exist in table

mysql> SELECT * FROM Transaction;

+---------------+-----------------+----------+---------------------+

| TransactionID | TransactionType | Amount | TransactionDate |

+---------------+-----------------+----------+---------------------+

| 1 | Deposit | 5000.00 | 2025-04-01 10:00:00 |

| 2 | Withdrawal | 2000.00 | 2025-04-01 11:00:00 |

| 3 | Deposit | 15000.00 | 2025-04-01 12:00:00 |

| 4 | Deposit | 7000.00 | 2025-04-01 13:00:00 |

| 5 | Withdrawal | 3000.00 | 2025-04-01 14:00:00 |

| 6 | Deposit | 8000.00 | 2025-04-01 15:00:00 |

| 7 | Deposit | 6000.00 | 2025-04-01 16:00:00 |

| 8 | Withdrawal | 1000.00 | 2025-04-01 17:00:00 |

| 9 | Deposit | 9000.00 | 2025-04-01 18:00:00 |

| 10 | Withdrawal | 4000.00 | 2025-04-01 19:00:00 |

+---------------+-----------------+----------+---------------------+

10 rows in set (0.00 sec)

mysql>

mysql> -- =======================================

mysql> -- DML COMMANDS (10 commands)

mysql> -- =======================================

mysql>

mysql> -- 1. Insert into Person

mysql> INSERT INTO Person (PersonID, Name, Address, PhoneNumber, Email, DOB, Gender)

-> VALUES (31, 'Neha Kulkarni', 'Pune', '9876543210', '[email protected]', '1998-08-15', 'Female');

ERROR 1054 (42S22): Unknown column 'Name' in 'field list'

mysql> SELECT * FROM Person;

+----------+----------+-----------+-------------+-----------------------------+-------------+-------------+---------------+--------
+------+

| PersonID | LastName | FirstName | DateOfBirth | Email | PhoneNumber | Address |

TaxIdentifier | Gender | DOB |
+----------+----------+-----------+-------------+-----------------------------+-------------+-------------+---------------+--------
+------+

| 1 | Sharma | Amit | 1990-01-15 | [email protected] | 9999999991 | Pune |

TAX001 | NULL | NULL |

| 2 | Patil | Rohit | 1992-03-22 | [email protected] | 9999999992 | Pune |

TAX002 | NULL | NULL |

| 3 | Desai | Sneha | 1995-07-19 | [email protected] | 9999999993 | Pune |

TAX003 | NULL | NULL |

| 4 | Joshi | Neha | 1991-11-11 | [email protected] | 9999999994 | Pune |

TAX004 | NULL | NULL |

| 5 | Kumar | Anil | 1988-12-25 | [email protected] | 9999999995 | Pune |

TAX005 | NULL | NULL |

| 6 | Gupta | Ravi | 1993-09-30 | [email protected] | 9999999996 | Pune |

TAX006 | NULL | NULL |

| 7 | Mehta | Priya | 1996-06-05 | [email protected] | 9999999997 | Pune |

TAX007 | NULL | NULL |

| 8 | Kulkarni | Vikas | 1989-05-18 | [email protected] | 9999999998 | Pune |

TAX008 | NULL | NULL |

| 9 | Reddy | Kiran | 1994-04-12 | [email protected] | 9999999999 | Pune |

TAX009 | NULL | NULL |

| 10 | Singh | Pooja | 1990-10-10 | [email protected] | 9999999910 | Pune |

TAX010 | NULL | NULL |

| 11 | Patil | Sneha | 1995-08-14 | [email protected] | 9876543211 | Pune, India |

TXP112233 | NULL | NULL |

| 12 | Sharma | Abhishek | 1996-04-23 | [email protected] | 1029837464 | Pune,

India | TXP1112313 | NULL | NULL |

+----------+----------+-----------+-------------+-----------------------------+-------------+-------------+---------------+--------
+------+

12 rows in set (0.00 sec)

mysql>
mysql> -- 2. Insert into Customer

mysql> INSERT INTO Customer (CustomerID, PersonID)

-> VALUES (31, 31);

ERROR 1054 (42S22): Unknown column 'PersonID' in 'field list'

mysql> SELECT * FROM Customer;

+------------+--------------+

| CustomerID | CustomerType |

+------------+--------------+

| 1 | Retail |

| 2 | Retail |

| 4 | Retail |

| 6 | Retail |

| 7 | Business |

| 8 | Retail |

| 9 | Business |

| 10 | Retail |

+------------+--------------+

8 rows in set (0.00 sec)

mysql>

mysql> -- 3. Insert into Branch

mysql> INSERT INTO Branch (BranchID, BranchName, City)

-> VALUES (20, 'Kothrud Branch', 'Pune');

ERROR 1054 (42S22): Unknown column 'City' in 'field list'

mysql> SELECT * FROM Branch;

+----------+------------------+------------+---------------------+-------------+

| BranchID | BranchName | BranchCode | Address | PhoneNumber |

+----------+------------------+------------+---------------------+-------------+

| 1 | Pune Main | PM001 | MG Road, Pune | 0201234567 |

| 2 | Pune West | PW001 | FC Road, Pune | 0201234568 |

| 3 | Pune East | PE001 | Kalyani Nagar, Pune | 0201234569 |

| 4 | Pune South | PS001 | Swargate, Pune | 0201234570 |

| 5 | Pune North | PN001 | Chinchwad, Pune | 0201234571 |

| 6 | Pune Central | PC001 | Camp, Pune | 0201234572 |

| 7 | Pune Airport | PA001 | Airport Road, Pune | 0201234573 |

| 8 | Pune Hadapsar | PH001 | Hadapsar, Pune | 0201234574 |

| 9 | Pune Kothrud | PK001 | Kothrud, Pune | 0201234575 |

| 10 | Pune Viman Nagar | PV001 | Viman Nagar, Pune | 0201234576 |

+----------+------------------+------------+---------------------+-------------+

10 rows in set (0.00 sec)

mysql>

mysql> -- 4. Insert into Account

mysql> INSERT INTO Account (AccountID, AccountType, CurrentBalance, CustomerID, BranchID)

-> VALUES (31, 'Savings', 30000.00, 31, 20);

ERROR 1054 (42S22): Unknown column 'CustomerID' in 'field list'

mysql> SELECT * FROM Account;

+-----------+---------------+-------------+----------------+------------+------------+---------------+

| AccountID | AccountNumber | AccountType | CurrentBalance | DateOpened | DateClosed |

AccountStatus |

+-----------+---------------+-------------+----------------+------------+------------+---------------+
| 1 | ACC10001 | Savings | 50000.00 | 2020-01-01 | NULL | Active |

| 2 | ACC10002 | Current | 75000.00 | 2020-02-01 | NULL | Active |

| 3 | ACC10003 | Savings | 100000.00 | 2020-03-01 | NULL | Active |

| 4 | ACC10004 | Savings | 25000.00 | 2020-04-01 | NULL | Active |

| 5 | ACC10005 | Current | 60000.00 | 2020-05-01 | NULL | Active |

| 6 | ACC10006 | Savings | 45000.00 | 2020-06-01 | NULL | Inactive |

| 7 | ACC10007 | Savings | 150000.00 | 2020-07-01 | NULL | Active |

| 8 | ACC10008 | Savings | 30000.00 | 2020-08-01 | NULL | Active |

| 9 | ACC10009 | Current | 90000.00 | 2020-09-01 | NULL | Active |

| 10 | ACC10010 | Savings | 80000.00 | 2020-10-01 | NULL | Active |

+-----------+---------------+-------------+----------------+------------+------------+---------------+

10 rows in set (0.00 sec)

mysql>

mysql> -- 5. Insert into Loan

mysql> INSERT INTO Loan (LoanID, LoanAmount, LoanType, CustomerID)

-> VALUES (31, 150000.00, 'Education Loan', 31);

ERROR 1054 (42S22): Unknown column 'CustomerID' in 'field list'

mysql> SELECT * FROM Loan;

+--------+----------+------------+--------------+------+------------+------------+------------+

| LoanID | LoanType | LoanAmount | InterestRate | Term | StartDate | EndDate | LoanStatus |

+--------+----------+------------+--------------+------+------------+------------+------------+

| 1 | Home | 1000000.00 | 7.50 | 240 | 2020-01-01 | 2040-01-01 | Active |

| 2 | Car | 500000.00 | 9.00 | 60 | 2020-02-01 | 2025-02-01 | Active |

| 3 | Personal | 200000.00 | 12.00 | 36 | 2020-03-01 | 2023-03-01 | Closed |

| 4 | Home | 1500000.00 | 8.00 | 300 | 2020-04-01 | 2045-04-01 | Active |

| 5 | Car | 400000.00 | 9.50 | 48 | 2020-05-01 | 2024-05-01 | Active |

| 6 | Personal | 300000.00 | 13.00 | 24 | 2020-06-01 | 2022-06-01 | Closed |

| 7 | Home | 1200000.00 | 7.80 | 240 | 2020-07-01 | 2040-07-01 | Active |

| 8 | Car | 550000.00 | 9.20 | 60 | 2020-08-01 | 2025-08-01 | Active |

| 9 | Personal | 250000.00 | 11.50 | 36 | 2020-09-01 | 2023-09-01 | Closed |

| 10 | Home | 1100000.00 | 7.20 | 240 | 2020-10-01 | 2040-10-01 | Active |

+--------+----------+------------+--------------+------+------------+------------+------------+

10 rows in set (0.00 sec)

mysql>

mysql> -- 6. Insert into Transaction

mysql> INSERT INTO Transaction (TransactionID, TransactionType, Amount, TransactionDate, AccountID)

-> VALUES (31, 'Withdrawal', 2000.00, NOW(), 31);

ERROR 1054 (42S22): Unknown column 'AccountID' in 'field list'

mysql> SELECT * FROM Transaction;

+---------------+-----------------+----------+---------------------+

| TransactionID | TransactionType | Amount | TransactionDate |

+---------------+-----------------+----------+---------------------+

| 1 | Deposit | 5000.00 | 2025-04-01 10:00:00 |

| 2 | Withdrawal | 2000.00 | 2025-04-01 11:00:00 |

| 3 | Deposit | 15000.00 | 2025-04-01 12:00:00 |

| 4 | Deposit | 7000.00 | 2025-04-01 13:00:00 |

| 5 | Withdrawal | 3000.00 | 2025-04-01 14:00:00 |

| 6 | Deposit | 8000.00 | 2025-04-01 15:00:00 |

| 7 | Deposit | 6000.00 | 2025-04-01 16:00:00 |

| 8 | Withdrawal | 1000.00 | 2025-04-01 17:00:00 |

| 9 | Deposit | 9000.00 | 2025-04-01 18:00:00 |

| 10 | Withdrawal | 4000.00 | 2025-04-01 19:00:00 |

+---------------+-----------------+----------+---------------------+

10 rows in set (0.00 sec)

mysql>

mysql> -- 7. Insert into LoanPayment

mysql> INSERT INTO LoanPayment (PaymentID, LoanID, PaymentAmount, PaymentDate)

-> VALUES (31, 31, 8000.00, NOW());

ERROR 1054 (42S22): Unknown column 'PaymentID' in 'field list'

mysql> SELECT * FROM LoanPayment;

+---------------+----------------------+---------------+-----------------+----------------+-------------+------------+

| LoanPaymentID | ScheduledPaymentDate | PaymentAmount | PrincipalAmount | InterestAmount |

PaymentDate | PaidDate |

+---------------+----------------------+---------------+-----------------+----------------+-------------+------------+

| 1 | 2025-05-01 | 10000.00 | 8000.00 | 2000.00 | 2025-05-02 | 2025-05-02 |

| 2 | 2025-06-01 | 10000.00 | 8200.00 | 1800.00 | 2025-06-02 | 2025-06-02 |

| 3 | 2025-07-01 | 10000.00 | 8300.00 | 1700.00 | 2025-07-02 | 2025-07-02 |

| 4 | 2025-08-01 | 10000.00 | 8500.00 | 1500.00 | 2025-08-02 | 2025-08-02 |

| 5 | 2025-09-01 | 10000.00 | 8600.00 | 1400.00 | 2025-09-02 | 2025-09-02 |

| 6 | 2025-10-01 | 10000.00 | 8700.00 | 1300.00 | 2025-10-02 | 2025-10-02 |

| 7 | 2025-11-01 | 10000.00 | 8800.00 | 1200.00 | 2025-11-02 | 2025-11-02 |

| 8 | 2025-12-01 | 10000.00 | 8900.00 | 1100.00 | 2025-12-02 | 2025-12-02 |

| 9 | 2026-01-01 | 10000.00 | 9000.00 | 1000.00 | 2026-01-02 | 2026-01-02 |

| 10 | 2026-02-01 | 10000.00 | 9100.00 | 900.00 | 2026-02-02 | 2026-02-02 |

+---------------+----------------------+---------------+-----------------+----------------+-------------+------------+

10 rows in set (0.00 sec)

mysql>

mysql> -- 8. Update Person Email

mysql> UPDATE Person

-> SET Email = '[email protected]'

-> WHERE PersonID = 31;

Query OK, 0 rows affected (0.00 sec)

Rows matched: 0 Changed: 0 Warnings: 0

mysql> SELECT * FROM Person;

+----------+----------+-----------+-------------+-----------------------------+-------------+-------------+---------------+--------
+------+

| PersonID | LastName | FirstName | DateOfBirth | Email | PhoneNumber | Address |

TaxIdentifier | Gender | DOB |

+----------+----------+-----------+-------------+-----------------------------+-------------+-------------+---------------+--------
+------+

| 1 | Sharma | Amit | 1990-01-15 | [email protected] | 9999999991 | Pune |

TAX001 | NULL | NULL |

| 2 | Patil | Rohit | 1992-03-22 | [email protected] | 9999999992 | Pune |

TAX002 | NULL | NULL |

| 3 | Desai | Sneha | 1995-07-19 | [email protected] | 9999999993 | Pune |

TAX003 | NULL | NULL |

| 4 | Joshi | Neha | 1991-11-11 | [email protected] | 9999999994 | Pune |

TAX004 | NULL | NULL |

| 5 | Kumar | Anil | 1988-12-25 | [email protected] | 9999999995 | Pune |

TAX005 | NULL | NULL |
| 6 | Gupta | Ravi | 1993-09-30 | [email protected] | 9999999996 | Pune |
TAX006 | NULL | NULL |

| 7 | Mehta | Priya | 1996-06-05 | [email protected] | 9999999997 | Pune |

TAX007 | NULL | NULL |

| 8 | Kulkarni | Vikas | 1989-05-18 | [email protected] | 9999999998 | Pune |

TAX008 | NULL | NULL |

| 9 | Reddy | Kiran | 1994-04-12 | [email protected] | 9999999999 | Pune |

TAX009 | NULL | NULL |

| 10 | Singh | Pooja | 1990-10-10 | [email protected] | 9999999910 | Pune |

TAX010 | NULL | NULL |

| 11 | Patil | Sneha | 1995-08-14 | [email protected] | 9876543211 | Pune, India |

TXP112233 | NULL | NULL |

| 12 | Sharma | Abhishek | 1996-04-23 | [email protected] | 1029837464 | Pune,

India | TXP1112313 | NULL | NULL |

+----------+----------+-----------+-------------+-----------------------------+-------------+-------------+---------------+--------
+------+

12 rows in set (0.00 sec)

mysql>

mysql> -- 9. Update Account Balance

mysql> UPDATE Account

-> SET CurrentBalance = CurrentBalance - 2000

-> WHERE AccountID = 31;

Query OK, 0 rows affected (0.00 sec)

Rows matched: 0 Changed: 0 Warnings: 0

mysql> SELECT * FROM Account;

+-----------+---------------+-------------+----------------+------------+------------+---------------+

| AccountID | AccountNumber | AccountType | CurrentBalance | DateOpened | DateClosed |

AccountStatus |

+-----------+---------------+-------------+----------------+------------+------------+---------------+

| 1 | ACC10001 | Savings | 50000.00 | 2020-01-01 | NULL | Active |

| 2 | ACC10002 | Current | 75000.00 | 2020-02-01 | NULL | Active |

| 3 | ACC10003 | Savings | 100000.00 | 2020-03-01 | NULL | Active |

| 4 | ACC10004 | Savings | 25000.00 | 2020-04-01 | NULL | Active |

| 5 | ACC10005 | Current | 60000.00 | 2020-05-01 | NULL | Active |

| 6 | ACC10006 | Savings | 45000.00 | 2020-06-01 | NULL | Inactive |

| 7 | ACC10007 | Savings | 150000.00 | 2020-07-01 | NULL | Active |

| 8 | ACC10008 | Savings | 30000.00 | 2020-08-01 | NULL | Active |

| 9 | ACC10009 | Current | 90000.00 | 2020-09-01 | NULL | Active |

| 10 | ACC10010 | Savings | 80000.00 | 2020-10-01 | NULL | Active |

+-----------+---------------+-------------+----------------+------------+------------+---------------+

10 rows in set (0.00 sec)

mysql>

mysql> -- 10. Delete a Transaction

mysql> DELETE FROM Transaction

-> WHERE TransactionID = 31;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM Transaction;

+---------------+-----------------+----------+---------------------+

| TransactionID | TransactionType | Amount | TransactionDate |

+---------------+-----------------+----------+---------------------+
| 1 | Deposit | 5000.00 | 2025-04-01 10:00:00 |

| 2 | Withdrawal | 2000.00 | 2025-04-01 11:00:00 |

| 3 | Deposit | 15000.00 | 2025-04-01 12:00:00 |

| 4 | Deposit | 7000.00 | 2025-04-01 13:00:00 |

| 5 | Withdrawal | 3000.00 | 2025-04-01 14:00:00 |

| 6 | Deposit | 8000.00 | 2025-04-01 15:00:00 |

| 7 | Deposit | 6000.00 | 2025-04-01 16:00:00 |

| 8 | Withdrawal | 1000.00 | 2025-04-01 17:00:00 |

| 9 | Deposit | 9000.00 | 2025-04-01 18:00:00 |

| 10 | Withdrawal | 4000.00 | 2025-04-01 19:00:00 |

+---------------+-----------------+----------+---------------------+

10 rows in set (0.00 sec)

mysql>

mysql> -- =======================================

mysql> -- CURSOR with CONTROL STRUCTURES & EXCEPTION HANDLING

mysql> -- =======================================

mysql>

mysql> DELIMITER //

mysql>

mysql> CREATE PROCEDURE UpdateAccountBalances()

-> BEGIN

-> DECLARE done INT DEFAULT 0;

-> DECLARE acc_id INT;

-> DECLARE acc_balance DECIMAL(12,2);

->

-> DECLARE cur CURSOR FOR

-> SELECT AccountID, CurrentBalance FROM Account;

->

-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

->

-> OPEN cur;

->

-> read_loop: LOOP

-> FETCH cur INTO acc_id, acc_balance;

->

-> IF done THEN

-> LEAVE read_loop;

-> END IF;

->

-> -- Control structure: Check if balance is less than 1000

-> IF acc_balance < 1000 THEN

-> UPDATE Account

-> SET CurrentBalance = 1000

-> WHERE AccountID = acc_id;

-> END IF;

->

-> END LOOP;

->

-> CLOSE cur;

 -> END //

Query OK, 0 rows affected (0.19 sec)

mysql>

mysql> DELIMITER ;

mysql>

mysql> -- Call the procedure

mysql> CALL UpdateAccountBalances();

Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> -- Show Account table after Cursor operation

mysql> SELECT * FROM Account;

+-----------+---------------+-------------+----------------+------------+------------+---------------+

| AccountID | AccountNumber | AccountType | CurrentBalance | DateOpened | DateClosed |

AccountStatus |

+-----------+---------------+-------------+----------------+------------+------------+---------------+

| 1 | ACC10001 | Savings | 50000.00 | 2020-01-01 | NULL | Active |

| 2 | ACC10002 | Current | 75000.00 | 2020-02-01 | NULL | Active |

| 3 | ACC10003 | Savings | 100000.00 | 2020-03-01 | NULL | Active |

| 4 | ACC10004 | Savings | 25000.00 | 2020-04-01 | NULL | Active |

| 5 | ACC10005 | Current | 60000.00 | 2020-05-01 | NULL | Active |

| 6 | ACC10006 | Savings | 45000.00 | 2020-06-01 | NULL | Inactive |

| 7 | ACC10007 | Savings | 150000.00 | 2020-07-01 | NULL | Active |

| 8 | ACC10008 | Savings | 30000.00 | 2020-08-01 | NULL | Active |

| 9 | ACC10009 | Current | 90000.00 | 2020-09-01 | NULL | Active |

| 10 | ACC10010 | Savings | 80000.00 | 2020-10-01 | NULL | Active |

+-----------+---------------+-------------+----------------+------------+------------+---------------+

10 rows in set (0.00 sec)

mysql>