SQL DML INSERT for Banking Account Management: A Guide to Managing Customer and Transaction Data

4.Add a new transaction record for the account with AccountID 1.
ORACLE
INSERT INTO Transactions (TransactionID, AccountID, tDate, Amount, TransactionType)
VALUES (1, 1, TO_DATE('01-JAN-23', 'DD-MON-YY'), 150.00, 'Deposit');
EXPLANATION
1.Oracle
TO_DATE function can be used in Oracle for specifying the date format, else we just use a simple string date format 'YYYY-MM-DD' which is used over here.
2. MySQL
In the mysql, we are just using 'YYYY-MM-DD' for the date format. We won't need to use any form of type conversion functions for the same date. This query too is gonna work.
3. PostgreSQL
Postgresql too is accepting the 'YYYY-MM-DD' for date formats. So again, date does not have to need any type conversions. The same query as well shall be eligible for being executed.
4. SQL Server
SQL Server also accepts date format 'YYYY-MM-DD', so it can also execute this query without any issues.

5.Insert a new customer record for Bob Brown.
ORACLE
INSERT INTO Customers (CustomerID, Name, Email, Phone, Address)
VALUES (4, 'Bob Brown', 'bob.brown@example.com', '555-987-6543', '101 Pine St, Newtown, USA');
Explanation
This SQL statement is to insert a new customer into the Customers table. The INSERT INTO statement indicates that we want to insert data into the table known as Customers and identify the columns we are filling CustomerID, Name, Email, Phone, and Address. In this case the CustomerID is assigned as 4 to uniquely identify this customer in the database. The customer's name is Bob Brown. From the name we identify him. The email address would be 'bob.brown@example.com' thus the communication channel to Bob is created. The Phone number would be '555-987-6543' as a way of accessing him directly. Finally, the address is recorded to be '101 Pine St, Newtown, USA' and that is where he resides. When this statement is executed, it creates a new record in the table Customers with all the details regarding 'Bob Brown'.

6.Insert a new account for customers who already have a balance greater than 1000.
MySQL, PostgreSQL, and SQL Server
INSERT INTO Accounts (AccountID, CustomerID, AccountType, AccountCreationDate, Balance)
SELECT 4, CustomerID, 'Checking', '2023-01-04', 500.00
FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Accounts WHERE Balance > 1000); ;
Explanation
It was intended for the purpose of inserting a new account within Accounts in MySQL, PostgreSQL and SQL Server. This works by the command saying you are going to insert a data set into Accounts along with column names such as AccountID, CustomerID, AccountType, AccountCreationDate, and Balance.
To acquire information for this new account the SELECT statement is used. First, the AccountID is set to 4, which is the identifier for this new account. Then it will acquire the CustomerID from the Customers table; in other words, it will make an account for every customer who qualifies for the account.
The account type is defined as 'Checking', which means that this new account will be a checking account. The date of creation of this account is set to '2023-01-04', which tells us when the account is established. Finally, the Balance for this account is set to 500.00, which indicates that this account will start with a balance of five hundred dollars.
The command involves the following in the WHERE clause: it specifies that only those customers who already possess an account with a balance of more than a thousand dollars will have their account qualified to be opened at a new checking account.
Summary This request provides a new checking account for each qualifying customer, a distinct account ID, a set balance, and a specified date of creation.

Oracle
INSERT INTO Accounts (AccountID, CustomerID, AccountType, AccountCreationDate, Balance)
SELECT 4, CustomerID, 'Checking', TO_DATE('04-JAN-23', 'DD-MON-YY'), 500.00
FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Accounts WHERE Balance > 1000);

7.Insert a new customer record using a sequence for the CustomerID.
INSERT INTO Customers (CustomerID, Name, Email, Phone, Address)
VALUES
(Customers_seq.NEXTVAL, 'Charlie Green', 'charlie.green@example.com', '333-444-5555', '202 Maple St, Metropolis, USA');
Explanation
This statement is an insert on a new customer record in Customers table. It creates one record for the customer of the name "Charlie Green," with the email being charlie.green@example.com and the phone number 333-444-5555 and his address was 202 Maple St, Metropolis, USA. Instead of setting the value of CustomerID manually, it used a sequence Customers_seq.NEXTVAL so that the CustomerID to be generated is unique so that no customer will get the same ID. This would automatically allow for the seamless addition of new customer records while making sure that all the databases would remain organized in preparation for updates or queries.

8.Use a PL/SQL block to insert a new customer record for Diana Prince.
Oracle
BEGIN
INSERT INTO Customers (CustomerID, Name, Email, Phone, Address)
VALUES (5, 'Diana Prince', 'diana.prince@example.com', '333-444-5555', '303 Cedar St, Metropolis, USA');
END;
Explanation
The SQL statement given here in Oracle aims to insert a new record in the Customers table using PL/SQL which is the oracle proprietary extension for the SQL language. The sequence will start off by beginning with the keyword BEGIN that indicates the start of a PL/SQL block. In this section, the INSERT INTO statement is used to add a new customer with the following particular details CustomerID = 5, name 'Diana Prince', email 'diana.prince@example.com', phone number '333-444-5555' and address '303 Cedar St, Metropolis USA'. The information is updated in the Customers table so that the database is supplied with recent details of customers. This block ends with the ". It is more effective for performing complex operations or having multiple SQL commands together while running them in Oracle.

MySQL
INSERT INTO Customers (CustomerID, Name, Email, Phone, Address)
VALUES (5, 'Diana Prince', 'diana.prince@example.com', '333-444-5555', '303 Cedar St, Metropolis, USA');
SQLSErver
INSERT INTO Customers (CustomerID, Name, Email, Phone, Address)
VALUES (5, 'Diana Prince', 'diana.prince@example.com', '333-444-5555', '303 Cedar St, Metropolis, USA');
PostgreSQL
INSERT INTO Customers (CustomerID, Name, Email, Phone, Address)
VALUES (5, 'Diana Prince', 'diana.prince@example.com', '333-444-5555', '303 Cedar St, Metropolis, USA');

9.Before inserting a new transaction, check if the account has sufficient balance.
Oracle
INSERT INTO Transactions (TransactionID, AccountID, tDate, Amount, TransactionType)
SELECT 1, 1, TO_DATE('06-JAN-23', 'DD-MON-YY'), 100.00, 'Withdrawal'
FROM dual
WHERE (SELECT Balance FROM Accounts WHERE AccountID = 1) >= 100.00;
Explanation
The Oracle SQL query is created to insert a new transaction into the Transactions table. The INSERT INTO statement identifies the target table and which columns would be filled TransactionID, AccountID, tDate, Amount and TransactionType. The SELECT will then create those values. It assigns TransactionID as 1, AccountID as 1 and converts the string '06-JAN-23' into a date format using the function TO_DATE to facilitate proper recognition by the database. The Amount is assigned as 100.00 and TransactionType as 'Withdrawal'.
The query is such that it contains a conditional check in the WHERE clause the balance of the account which has AccountID 1 is at least 100.00. A subquery in this query is given as SELECT Balance FROM Accounts WHERE AccountID = 1 which returns the balance of that account. And if this condition is satisfied, then a new record into the Transactions table is created with the fact that 100.00 was withdrawn from the account. This would ensure that only when there are sufficient funds to support the intended withdrawals they will be processed and serviced by the core banking system while maintaining the integrity of the financial data.

MySQL, PostgreSQL, and SQL Server
INSERT INTO Transactions (TransactionID, AccountID, tDate, Amount, TransactionType)
SELECT 1, 1, '2023-01-06', 100.00, 'Withdrawal'
WHERE (SELECT Balance FROM Accounts WHERE AccountID = 1) >= 100.00;

10.Insert a transaction for AccountID 1 with a specified transaction date.
MySQL, PostgreSQL, and SQL Server:
INSERT INTO Transactions (TransactionID, AccountID, tDate, Amount, TransactionType)
VALUES
(4, 1, '2023-01-07', 250.00, 'Deposit');
Insert a new row into the Transactions table. It is a transaction with TransactionID = 4 that is connected to AccountID = 1. The transaction date occurred on 2023-01-07 and it was a "Deposit" type of transaction for 250.00 amount. Date in the tDate column is displayed in the format YYYY-MM-DD, which MySQL, PostgreSQL and SQL Server will natively accept as a string without any further date conversion functions. This will create a new transaction and the deposit for 250.00 into the account mentioned above on the date indicated.
Oracle
INSERT INTO Transactions
(TransactionID, AccountID, tDate, Amount, TransactionType)
VALUES (4, 1, TO_DATE('07-JAN-23', 'DD-MON-YY'), 250.00, 'Deposit');
This Oracle SQL insert statement will insert a new transaction record into the Transactions table. It assigns this record the unique TransactionID of 4 it assigns the record to AccountID 1 and it assigns tDate as the date of the transaction 07-JAN-23 in a format of DD-MON-YY which the Oracle function will make an Oracle date. It identifies the type of transaction which is "Deposit" and the amount to be recorded is 250.00. This is a deposit of 250.00 to the account above on January 7, 2023. Using the function TO_DATE ensures that Oracle knows the format of the date.

11.add new account for customerid 2 with default value from the account type.
Oracle

INSERT INTO Accounts
(AccountID, CustomerID, AccountType, AccountCreationDate, Balance)
VALUES
(4, 2, 'Savings', TO_DATE('08-JAN-23', 'DD-MON-YY'), 1000.00);
This statement using Oracle SQL inserts one new row into the `Accounts` table. The new record states that account `AccountID` 4 is the customer `CustomerID` 2 and this is a 'Savings' type. The date it was created is set to fixed as January 8, 2023 by use of the Oracle function `TO_DATE`. Oracle translates the string '08-JAN-23' into their date format of choice, `DD-MON-YY`. The Balance column has been assigned an opening balance of 1000.00 for this account. It is this query that will add the information on a new savings account for this customer.

MySQL, PostgreSQL, and SQL Server
INSERT INTO Accounts
(AccountID, CustomerID, AccountType, AccountCreationDate, Balance)
VALUES
(4, 2, 'Savings', '2023-01-08', 1000.00);

12.Insert a transaction for an account of type 'Savings'.
MySQL, PostgreSQL
INSERT INTO Transactions (TransactionID, AccountID, tDate, Amount, TransactionType)
VALUES
(5, (SELECT AccountID FROM Accounts WHERE AccountType = 'Savings' LIMIT 1), CURRENT_DATE, 300.00, 'Deposit'); SQL Server
INSERT INTO Transactions (TransactionID, AccountID, tDate, Amount, TransactionType)
VALUES
(5, (SELECT TOP 1 AccountID FROM Accounts WHERE AccountType = 'Savings'), GETDATE(), 300.00, 'Deposit'); Oracle
INSERT INTO Transactions (TransactionID, AccountID, tDate, Amount, TransactionType)
VALUES
(5, (SELECT AccountID FROM Accounts WHERE AccountType = 'Savings' AND ROWNUM = 1), SYSDATE, 300.00, 'Deposit');

13.Insert a new customer record using a sequence for the CustomerID.
Oracle
INSERT INTO Customers (CustomerID, Name, Email, Phone, Address)
VALUES
(Customers_seq.NEXTVAL, 'Eve Adams', 'eve.adams@example.com', '444-555-6666', '404 Birch St, Smalltown, USA');
It is an SQL query for Oracle databases introduces a new row into the Customers table. In this statement the `CustomerID` is assigned the next value in a sequence called `Customers_seq` therefore, every new customer is assigned a unique identifier. The other fields will automatically fill in the details of the customer to be added name is set to "Eve Adams", the email as "eve.adams@example.com," the phone number as "444-555-6666," and the address as "404 Birch St, Smalltown, USA." This way, it can manage automatically the `CustomerID,` and this is very helpful in maintaining data integrity and avoiding duplication. A sequence will automatically provide unique IDs for all new records and it sends such a vast number of these numbers that the job of adding customers really becomes streamlined.

MySQL
INSERT INTO Customers (CustomerID, Name, Email, Phone, Address)
VALUES
(NULL, 'Eve Adams', 'eve.adams@example.com', '444-555-6666', '404 Birch St, Smalltown, USA');
Note:Assuming CustomerID is set to auto increment
The SQL statement is an insert of a new record in the `Customers` table in a MySQL database. In this insert statement, I set the `CustomerID` to `NULL`. This is the usual procedure when the column is set to auto-increment that the value of the `CustomerID` is `NULL`. This will ensure that the database is going to populate this value for `CustomerID` automatically since a new record should have already been added and each customer will be supplied with a unique value all without human effort. Other fields in the query are filled with the information of the customer name "Eve Adams," email address "eve.adams@example.com," phone number "444-555-6666," and address "404 Birch St, Smalltown, USA." This way, the data entry process is much easier and the chance of making errors while assigning IDs manually is reduced. This way, it enables the database to control the `CustomerID` hence upholding the performance along with keeping data integrity.
PostgreSQL
INSERT INTO Customers (CustomerID, Name, Email, Phone, Address)
VALUES
(nextval('Customers_seq'), 'Eve Adams', 'eve.adams@example.com', '444-555-6666', '404 Birch St, Smalltown, USA');

SQL Server
INSERT INTO Customers (CustomerID, Name, Email, Phone, Address)
VALUES
(NEXT VALUE FOR Customers_seq, 'Eve Adams', 'eve.adams@example.com', '444-555-6666', '404 Birch St, Smalltown, USA');
The SQL statement given is to add a new record in the Customers table using a sequence of CustomerID in SQL Server databases. In using NEXT VALUE FOR Customers_seq it selects the next value for the sequence named Customers_seq so that every newly added CustomerID will be unique and logically in ascending order. In this line of the query, the other columns are populated with the information of the user to be added. In the example, Name is set to "Eve Adams" and Email is "eve.adams@example.com" while Phone number is "444-555-6666" and Address is "404 Birch St, Smalltown, USA." This application of sequences is the most convenient way in SQL Server to produce an identity without a chance for collision. It is therefore an effective method of maintaining the integrity of the Customers table in data form. It is easier to insert new records since this automatically assigns unique values to the customer ID of the record making the system handle customer entries efficiently and effectively.

14.Add Record into transaction table Before attempting to add a new transaction, the account should have enough balance. In case the balance is available add the record.
MySQL, PostgreSQL, and SQL Server
INSERT INTO Transactions
(TransactionID, AccountID, tDate, Amount, TransactionType)
SELECT 6, 1, CURRENT_TIMESTAMP, 50.00, 'Withdrawal'
WHERE (SELECT Balance FROM Accounts WHERE AccountID = 1) >= 50.00;
Oracle
INSERT INTO Transactions (TransactionID, AccountID, tDate, Amount, TransactionType)
SELECT 6, 1, SYSDATE, 50.00, 'Withdrawal'
FROM dual
WHERE (SELECT Balance FROM Accounts WHERE AccountID = 1) >= 50.00;
Explanation
Insert a new transaction into the table `Transactions` of an Oracle database: SQL Statement Explanation This SQL statement is inserting a new transaction into the `Transactions` table of an Oracle database. This transaction will be recorded as a withdrawal of $50 from an account with the account ID of 1. The transaction then gets assigned an ID number of 6, and the date/time of the current day will be recorded as that on which the following transaction took place. However, before the execution of such a transaction, if the statement in some way checks whether the balance of the account with ID equal to 1 is at least $50, then the amount will be deducted from the account; otherwise, the amount, having found that the account balance was not enough for this withdrawal, would not be added.

15.Add a new account for CustomerID 3 with a specified account Oracle
INSERT INTO Accounts
(AccountID, CustomerID, AccountType, AccountCreationDate, Balance)
VALUES
(5, 3, 'Checking', TO_DATE('10-JAN-23', 'DD-MON-YY'), 2500.00);
Explanation
The SQL statement of Oracle shown will insert the record in the `Accounts` table. It says the `AccountID` set to 5 and associates it with the `CustomerID` of 3. The `AccountType` is assigned as 'Checking' and it explains whether this is a checking type of savings account type. In the `AccountCreationDate` the `TO_DATE` function is used to cast the string '10-JAN-23' into a format of date where the format to be used should appear in quotes as 'DD-MON-YY'. Finally, the `Balance` is set to 2500.00 which points to available initial amount on opening the account. The reason for performing this operation is that a new checking account has all details correctly captured in the database.
MySQL, PostgreSQL, and SQL Server
INSERT INTO Accounts (AccountID, CustomerID, AccountType, AccountCreationDate, Balance) VALUES (5, 3, 'Checking', '2023-01-10', 2500.00);

16.Insert multiple transactions for the account with AccountID 1 in a single operation.
Oracle
INSERT ALL
INTO Transactions
(TransactionID, AccountID, tDate, Amount, TransactionType)
VALUES
(8, 1, SYSDATE, 200.00, 'Withdrawal')
INTO Transactions
(TransactionID, AccountID, tDate, Amount, TransactionType)
VALUES
(9, 1, SYSDATE, 300.00, 'Deposit')
SELECT * FROM dual;
Explanation
In the given Oracle SQL statement, the statement adds records in to `Transactions` table using the `INSERT ALL` statement. This will enable to insert more than one record within a single statement quite efficiently. The first into clause introduces a new transaction with details like TransactionID = 8, AccountID = 1, TransactionDate= SYSDATE and Amount= 200.00 along with the type of transaction as 'Withdrawal'. The second INTO clause indicates that another transaction will be inserted with a Transaction ID of 9, account ID of 1 current system date and time an amount of 300.00, and transaction type of 'Deposit'. The last line is SELECT * FROM dual a dummy select statement this is used in Oracle only to complete the syntax-as INSERT ALL ends up requiring a SELECT` statement. This method has the benefits of entering more than one entry at one time, more efficiently compared to other methods.

MySQL
INSERT INTO Transactions
(TransactionID, AccountID, tDate, Amount, TransactionType)
VALUES
(8, 1, NOW(), 200.00, 'Withdrawal'),
(9, 1, NOW(), 300.00, 'Deposit');
PostgreSQL
INSERT INTO Transactions
(TransactionID, AccountID, tDate, Amount, TransactionType)
VALUES
(8, 1, CURRENT_TIMESTAMP, 200.00, 'Withdrawal'),
(9, 1, CURRENT_TIMESTAMP, 300.00, 'Deposit');
SQL Server
INSERT INTO Transactions
(TransactionID, AccountID, tDate, Amount, TransactionType)
VALUES
(8, 1, GETDATE(), 200.00, 'Withdrawal'),
(9, 1, GETDATE(), 300.00, 'Deposit');