Mastering the SQL DML UPDATE Command for Banking Account Management

4.Write an SQL query to update the AccountType to 'Premium' for all accounts in the Accounts table where the balance is greater than or equal to 5000.
UPDATE Accounts
SET AccountType = 'Premium'
WHERE Balance >= 5000;

5.Write an SQL query to update the Email and Address of the customer with CustomerID = 2 in the Customers table. Set the new email to 'jane.newemail@example.com' and the new address to 'New Address, Othertown, USA'.
UPDATE Customers
SET Email = 'jane.newemail@example.com', Address = 'New Address, Othertown, USA'
WHERE CustomerID = 2;
Explanation
This SQL query intends to update the Email and Address fields of a customer through the CustomerID = 2 in the Customers table. In other words, the email field was updated to 'jane.newemail@example.com' while the address to 'New Address, Othertown, USA'.This query ensures that all contact details for this specific customer are corrected or updated. The WHERE clause will only update a record that has the CustomerID as 2. Therefore, no other customer's detail will be updated. This update operation would have no impact at all unless there exists a customer with that detailed Customer ID in the database. An update operation like this is very common for updating any information related to a single customer or an account to achieve integrity and accuracy in a database.

6.Write an SQL query to set the Balance to 0 for all accounts in the Accounts table that have a Status of 'Inactive'.
MySQL, PostgreSQL, and SQL Server
UPDATE Accounts
SET Balance = 0
WHERE AccountID IN (SELECT AccountID FROM Accounts WHERE Status = 'Inactive');
Explanation
SQL update statement changing Balance to 0 for each account of Accounts table to all AccountID which have respective record with Status set to 'Inactive' in the same Accounts table. This should work because it selects first AccountIDs from Accounts table by Status status 'Inactive', then sets Balance field in such accounts to 0. This will ensure that all the inactive account balances are reset to 0, indicating the accounts are no longer active or operational. Records will not be changed if accounts do not qualify.

7.Write an SQL query to increase the Amount by 50 for all transactions in the Transactions table that occurred within the last month.
UPDATE Transactions
SET Amount = Amount + 50
WHERE tDate >= ADD_MONTHS(SYSDATE, -1);
Explanation
The SQL query updates the Amount column in the table of transactions with 50 added to it for all transactions that occurred in the last month. The WHERE clause narrows records on tDate or transaction date to select the transactions of those dates when tDate is greater than or equal to one month ago from the current system date, SYSDATE. The function ADD_MONTHS(SYSDATE, -1) returns the date one month ago from today's date. This brings a balance of 50 to the Amount of qualifying transactions. No records will be updated if no transactions were found that meet the condition.

8.Write an SQL query to update the Address to 'Unknown' for all customers in the Customers table whose CustomerID exists in the Accounts table with a balance less than 100.
UPDATE Customers
SET Address = 'Unknown'
WHERE CustomerID
IN
(SELECT CustomerID FROM Accounts WHERE Balance < 100);
Explanation
Updates the column Address in table Customers to 'Unknown' for all customers whose account balance associated is less than 100. Using the WHERE clause with a subquery it first selects CustomerID values from the Accounts table in which Balance is less than 100. Then using the UPDATE statement the Address field is updated to 'Unknown' in the Customers table for those customers whose CustomerID matches those selected by the subquery. That would tag customers with account balances below the specified marked address as unknown for further verification and action. If there is no matching record, then no record update is done.

9.Write an SQL query to append ' (VIP)' to the Name of all customers in the Customers table whose CustomerID exists in the Accounts table with an 'Active' status.
UPDATE Customers
SET Name = Name || ' (VIP)'
WHERE CustomerID
IN
(SELECT DISTINCT CustomerID FROM Accounts WHERE Status = 'Active');
Explanation
It will update the Name field in the Customers table. It appends ' (VIP)' to the current Name where the Status in the Accounts table is 'Active'. A subquery in the WHERE clause selects only the Customer ID values from the Accounts table where the Status is 'Active'. The UPDATE statement then appends ' (VIP)' to the Name field for all these customers. This operation serves the purpose of identifying the active customers with the VIP tag. If no such customer exists, then no records will be updated.

10.Write a query to set the IsActive status to 'Y' for all customers found in the Customers table that have made at least one valid transaction with the system within the last month by using the Transactions table.
UPDATE Customers
SET IsActive = 'Y'
WHERE CustomerID
IN
(SELECT DISTINCT CustomerID FROM Transactions WHERE tDate >= ADD_MONTHS(SYSDATE, -1)); ;
The SQL statement is updating a Customers table by setting IsActive = 'Y' where the customers made transactions within the last month. In the WHERE clause, I used a subquery to get CustomerID values where tDate (transaction date) is greater than or equal to one month ago from the date returned by SYSDATE. This would ensure that only customers with recent transactions are targeted to be marked as active. The UPDATE statement marks the IsActive field to 'Y' for such customers, meaning them to be active. If the condition is not fulfilled by any customer, no record will get updated.

11.Write the SQL query to mark all of the accounts in the Accounts table as type 'Inactive' given they have made no transactions in the past 12 months based on the Transactions table.
UPDATE Accounts
SET AccountType = 'Inactive'
WHERE AccountID
NOT IN
(SELECT DISTINCT AccountID FROM Transactions WHERE tDate >= ADD_MONTHS(SYSDATE, -12));
SQL Query Update all AccountType to be 'Inactive' based on the Accounts table for all accounts that had no transactions in the past 12 months. The WHERE clause uses a subquery to pull out the AccountID values from the Transactions table where the transaction date, tDate is within the last 12 months the ADD_MONTHS function in SQL calculates this subtracting 12 from SYSDATE. The query sets to 'Inactive' any account which is not present as an AccountID in the subquery that is, it does not have any transactions within the last 12 months. None of the accounts will be updated if no accounts fulfill the condition.

12.Write an SQL query to update the Address to 'New Town' for all customers in the Customers table whose current address contains 'Old Town'.
UPDATE Customers
SET Address = 'New Town'
WHERE Address LIKE '%Old Town%';
Explanation
The query is used to change the address of the customers from Customers table. It searches for customers whose address contains "Old Town" in their address anywhere. The LIKE '%Old Town%' part means that "Old Town" can appear at any position in the address. After the query finds all those customers it now changes their address to "New Town." It uses the % symbols as wildcards to enable "Old Town" to be any place in the address. It is one query that updates more customer addresses in case a certain condition is met.

13.Write an SQL query to update the TransactionType to 'Transfer' for all transactions in the Transactions table that occurred on or after 01-JAN-2023.
UPDATE Transactions
SET TransactionType = 'Transfer'
WHERE tDate >= TO_DATE('01-JAN-2023', 'DD-MON-YYYY');
The SQL query used is to update the field TransactionType in the table of Transactions. Here it will be setting the value of the field to "Transfer" for the records whose tDate the transaction date is from or after 01/01/2023. The UPDATE statement is used to change existing data in the table. The SET clause specifies the new value to be assigned to the column TransactionType. The WHERE clause determines which rows get updated and only those transactions change whose date is greater than or equal to the '01-JAN-2023'. The function TO_DATE('01-JAN-2023', 'DD-MON-YYYY') converts the date string into an appropriate format for the database. This kind of query helps you to mark or categorize all those transactions that have been done after a certain date. For example, you may tag all the transactions that occurred after the 01 January of the year 2023 as "Transfer" for better organizational or reporting purposes.

14.Write SQL update query to update the Balance in the Accounts table by subtracting the total of transactions for each account. Update the balance only where there exist some associated transactions in table Transactions.
UPDATE Accounts a
SET Balance = Balance - (SELECT SUM(Amount) FROM Transactions WHERE AccountID = a.AccountID)
WHERE EXISTS
(SELECT 1 FROM Transactions WHERE AccountID = a.AccountID);
Explanation
The query updates the Balance field in the Accounts table to subtract the aggregate of all transaction amounts for each account. It first selects the Accounts table with an alias a, thus allowing references to its fields. Calculate the total transaction amount for each account by subtracting it from the current Balance using the following SET Balance = Balance - (SELECT SUM(Amount) FROM Transactions WHERE AccountID = a.AccountID). The subquery SELECT SUM(Amount) FROM Transactions WHERE AccountID = a.AccountID will add up all amounts of transactions related to the current account.
The condition WHERE EXISTS (SELECT 1 FROM Transactions WHERE AccountID = a.AccountID) will update balances only for accounts that have transactions. No update will be done for accounts without related transactions. An account with no transactions at all won't be updated: a subquery checks whether at least one transaction refers to the account; because of the presence of the EXISTS clause, an update for such an account's balance wouldn't be attempted, so now it can be known that there is at least one transaction referring to the account.

15.Write an SQL query to update the Phone field to 'N/A' for all customers in the Customers table where the phone number is currently NULL.
UPDATE Customers
SET Phone = 'N/A'
WHERE Phone IS NULL;

16.Write an SQL query to update the AccountType to 'Review' for all accounts in the Accounts table where the Balance is not between 100 and 1000.
UPDATE Accounts
SET AccountType = 'Review'
WHERE Balance NOT BETWEEN 100 AND 1000;

17.Write an SQL query to increase the Balance by 100 for all accounts in the Accounts table where the associated CustomerID belongs to customers whose Name starts with the letter 'J' in the Customers table.
UPDATE Accounts
SET Balance = Balance + 100
WHERE CustomerID
IN
(SELECT CustomerID FROM Customers WHERE Name LIKE 'J%');
This query specifically selects customers whose names begin with the letter 'J' by applying the LIKE operator with the pattern 'J%'. The % symbol is used as a wildcard and therefore can provide for any characters that would follow the 'J'. The subquery selects the CustomerID from the Customers table based on this condition. The list of CustomerIDs is then passed to the outer query where it updates corresponding accounts in the Accounts table. Adding 100 to the Balance of the matched accounts is that query which makes the update relevant only those accounts modified which are directly related to the customer. Thus, this creates a means for efficient bulk update of customer data.

18.Write an SQL query to update the Email field in the Customers table by replacing '@oldemail.com' with '@newemail.com' for all customers whose email addresses contain '@oldemail.com'.
UPDATE Customers
SET Email = REPLACE(Email, '@oldemail.com', '@newemail.com')
WHERE
Email LIKE '%@oldemail.com'; This query is used to update the email addresses of customers who currently have an email ending with '@oldemail.com'. It first identifies all customers whose email addresses contain the domain '@oldemail.com' using the LIKE clause. Then, it updates those records by replacing the old domain '@oldemail.com' with the new domain '@newemail.com' using the REPLACE function. This ensures that all affected customer emails are updated in one go, helping to streamline the process of changing the email domain across multiple customer records in the database. It’s an efficient method to handle bulk email updates.

19.Write an SQL query to update the TransactionType to 'Transfer' for all transactions in the Transactions table that occurred on or after 01-JAN-2023.
UPDATE Transactions
SET TransactionType = 'Transfer'
WHERE
tDate >= TO_DATE('01-JAN-2023', 'DD-MON-YYYY');

20.Write an SQL update statement that will update the Balance field in the Accounts table based on the aggregated sum of all transactions for each account. Apply the update only for those accounts that have corresponding transaction records in the Transactions table.
UPDATE Accounts a
SET Balance = Balance - (SELECT SUM(Amount) FROM Transactions
WHERE
AccountID = a.AccountID) WHERE EXISTS (SELECT 1 FROM Transactions WHERE AccountID = a.AccountID);
This SQL statement updates the Accounts table updating the Balance column by subtracting the total amount of each account from the amounts made within the Transactions column. The question will use a correlated subquery to compute the total sum of amounts for each account in the Transactions table and then subtract that sum from the balance already in the Accounts table. The where exists clause ensures that the update only happens for accounts that have at least one transaction recorded in the Transactions table and therefore only those accounts will be affected by this query. The above query will help you in balancing accounts according to the transaction history.

Q21.Write an SQL query to update the Phone field to 'N/A' for all customers in the Customers table where the phone number is currently NULL.
UPDATE Customers
SET Phone = 'N/A'
WHERE Phone IS NULL;
This SQL query updates the Customers table to change the Phone field of all customers with a NULL value in the column to 'N/A'. The UPDATE statement modifies the data of the table and uses the SET keyword to specify that the Phone field should be updated. The WHERE clause condition will have the effect of modifying only those rows where the Phone value is NULL so customers without a recorded telephone number will get an 'N/A' stuffed into the Phone field instead. This may prove very useful for producing a nice continuity for the database it replaces missing data with a placeholder value.