Initialize Banking Data with Insert, Update & Delete Operations

Business Scenario

Welcome!

Today is your third day as a Data Analyst at our digital banking company.

In the previous lab, you created the secure banking database structure for BankingDB using SQL DDL commands and constraints.

Now, the client wants the banking staff to perform daily banking operations using the stored banking data.

In this system, the bank staff should be able to:

  • Add new customer and account records
  • Update existing banking information
  • Remove unnecessary records
  • Verify stored data using SQL queries
  • Maintain secure and organized banking records

Your task is to initialize and manage banking data using SQL DML commands.

Pre-Lab Preparation

Topic : Mastering Data Manipulation (DML)

1) SQL's Data Addition Tool : INSERT

2) Data Maintenance with UPDATE

3) SQL Cleanup Crew

Git Pull

git pull origin branchName

Task 1: Insert Banking Records

Before performing banking operations, the database must contain customer, account, and transaction data.
The INSERT command is used to add new records into database tables.

In this task, we will initialize the BankingDB database by inserting records into the Customers, Accounts, and Transactions tables.

Insert Records into Customers Table

1

Click here to download previous lab file : SQL Lab 2 

INSERT INTO Customers
(CustomerID, FirstName, LastName, Email, Phone, DateOfBirth)
VALUES
(101,'Rahul','Sharma','rahul@gmail.com','9876543210','1998-04-15');

This inserts customer details into the Customers table.

Result:

Insert Records into Accounts Table

2

INSERT INTO Accounts
(AccountID, CustomerID, AccountType, Balance)
VALUES
(201,101,'Savings',25000);

This inserts account information linked with customers.

Result:

Activity

Insert 4 records in Customers, Accounts table and 5 records in the rest of the Tables in BankingDB database using SQL query in MySQL

Task 2: Update Existing Banking Records

Sometimes banking information changes and existing records must be modified.
The UPDATE command is used to update existing records inside database tables.

In this task, we will update customer and account information in the BankingDB database.

Update Customer Phone Number

1

SET SQL_SAFE_UPDATES=0

UPDATE Customers
SET Phone='9999999999'
WHERE CustomerID=101;

This updates the phone number of CustomerID 101.

Verify Updated Customer Record:

SELECT * FROM Customers
WHERE CustomerID = 101;

2

Update Customer Email Address

UPDATE Customers
SET Email='rahul.sharma@gmail.com'
WHERE CustomerID=101;

This updates the email address of CustomerID 102.

Verify Updated Customer Record:

SELECT * FROM Customers
WHERE CustomerID = 101;

Activity

Update the following banking records in the BankingDB database using UPDATE commands:

  1. Modify the balance of AccountID 201 to 30000
  2. Verify all updated records using SELECT queries.

Task 3: Delete Banking Records and Verify Data

Sometimes unnecessary or outdated records must be removed from the database.
The DELETE command is used to remove records from database tables while keeping the table structure intact.

In this task, we will delete transaction and account records from the BankingDB database and verify the remaining data.

1

Delete Transaction Record

DELETE FROM Transactions
WHERE TransactionID = 302;

This removes the transaction record from the Transactions table.

Verify Transaction Records

SELECT * FROM Transactions;

2

Delete Account Record

DELETE FROM Accounts
WHERE AccountID = 202;

This removes the account record from the Accounts table.

Verify Account Records

SELECT * FROM Accounts;

Activity

Delete the transaction record with TransactionID 302 and the account record with AccountID 202, then verify the remaining records using SELECT queries.

 

Great job!
You have successfully completed your lab on Initialize Banking Data with Insert, Update & Delete Operations.

In this lab, you have: Inserted records into banking tables, Verified stored data using SELECT queries, Updated existing banking records, Deleted unnecessary records, Managed banking information using DML commands

You are now ready to move to the next stage of SQL database management.

Checkpoint

   Git Push

git push origin branchName

Next-Lab Preparation

Topic: Querying into Data (DQL)

1) SELECT Statements, WHERE clause

2) Arithmetic, Comparison and Logical operators, Range operator, List operator, Like operator, Using           ORDER BY, DISTINCT, LIMIT and OFFSET

3) Using IS NULL and IS NOT NULL

4) CASE statement

5) Window Functions Decoded

Updated SQL Lab3 : Initialize Banking Data with Insert, Update & Delete Operations

By Content ITV

Updated SQL Lab3 : Initialize Banking Data with Insert, Update & Delete Operations

  • 9

More from Content ITV