Mastering SQL DML DELETE Statement for Account Management: A Comprehensive Guide

4.Delete all customers with ID less than 100
UPDATE Accounts
DELETE FROM Customers WHERE CustomerID < 100;
Explanation
SQL command DELETE FROM Customers WHERE CustomerID < 100; deletes records in the "Customers" table when the "CustomerID" is less than 100. That means it would delete all those customers whose ID number is less than 100 and delete their information from the database. This may be done for various reasons such as cleaning up old data even test information that has already been inserted. However, this search should be done with utmost care so as not to accidentally delete essential or legitimate customer data. Data should always have a copy before these types of operations are made.

5.Delete all those customers whose names are 'John Doe' or 'Jane Smith'.
DELETE FROM Customers WHERE Name IN ('John Doe', 'Jane Smith');
Explanation
In SQL, the command DELETE FROM Customers WHERE Name IN ('John Doe', 'Jane Smith'); removes records from the "Customers" table when the "Name" field is equal to either "John Doe" or "Jane Smith." It will delete all records of customers who have these names exactly. However, to delete certain customers by their names this query comes in handy. Be very careful when using this query as you would want only those records to be deleted. Always double check for names and copy the data before running such a deletion.

6.Delete all customers with email not 'test@example.com'.
DELETE FROM Customers WHERE Email <> 'test@example.com';
Explanation
The SQL query DELETE FROM Customers WHERE Email <> 'test@example.com'; This deletes all records from the table "Customers" where the "Email" field does not match 'test@example.com'. Therefore, using the operator<> meaning "not equal to" this query deletes all customers whose email address is not 'test@example.com'. Be extremely cautious with this type of query since if you have many customers and they all have different email addresses, it will delete thousands of records. And always be certain before deleting records to check the condition or do a SELECT query first to ensure you're about to delete the correct records.

7.Delete the customers whose customer ID falls within the range of 50 to 150.
DELETE FROM Customers WHERE CustomerID BETWEEN 50 AND 150;
Explanation
The SQL query DELETE FROM Customers WHERE CustomerID BETWEEN 50 AND 150; deletes all records from the "Customers" table where the "CustomerID" falls within the range of 50 and 150 inclusive. The BETWEEN operator is a set notation for indicating a range of values. This query will delete all customers whose CustomerID falls within this range including both the lower and upper values that is CustomerID 50 and CustomerID 150. Before you do this you should check that you are really deleting the records you intend to delete, since once a deletion is done it can't be rolled back in a transaction.

8.Delete customers who do not have an account.
DELETE FROM Customers WHERE CustomerID NOT IN (SELECT CustomerID FROM Accounts);
Explanation
This SQL query removes rows from the "Customers" table where "CustomerID" is not found in the "Accounts" table. NOT IN will search for values not returned by a result of a subquery. In this case the subquery (SELECT CustomerID FROM Accounts) fetches all the CustomerIDs from the "Accounts" table and the main query deletes customers whose CustomerID does not appear in this list. Therefore customers who do not have any account will be deleted from the "Customers" table. Always remember to backup your data before running such queries since this deletion cannot be undone except if it's done within a transaction.

9.Delete those customers whose name comprises 'Admin' or 'User'.
DELETE FROM Customers WHERE Name LIKE '%Admin%' OR Name LIKE '%User%';
Explanation
The SQL query FROM the "Customers" table where the "Name" field will contain the words "Admin" or "User." The % wildcard is applied with the LIKE operator meaning it will match any names that have "Admin" or "User" anywhere within them. This query will delete customer names containing these words this can be used to delete system or test users from the database. It is, therefore important that one should be careful as the conditions are carried out by this query in order not to delete valid records accidentally.

10.Delete all customers whose names do not contain the word 'Test'.
DELETE FROM Customers WHERE Name NOT LIKE '%Test%';
The SQL query deletes the "Customers" table records where the "Name" column does not contain the word "Test." The NOT LIKE operator using the % wildcard allows a partial match so that any customer whose name does not include "Test" anywhere in it will be deleted from the database. This query is handy if you're interested in keeping only those records containing a name that includes "Test" perhaps for testing purposes, but eliminating all the other customer records. Be sure to delete only what you wanted to delete before running this query.

11.Delete the accounts with a balance of 10000 or more.
DELETE FROM Accounts WHERE Balance >= 10000;

12.Delete accounts that are of type 'Savings' and were created before January 1, 2020.
Oracle
DELETE FROM Accounts WHERE AccountType = 'Savings' AND AccountCreationDate < TO_DATE('2020-01-01', 'YYYY-MM-DD');
Explanation
This statement is intended to delete rows from the Oracle table "Accounts" in which account type is "Savings" and account creation date is less than January 1, 2020. It uses the TO_DATE() function to change the string '2020-01-01' into a proper date format that Oracle understands. This question helps remove older savings account records, which may be part of a cleaning process or some form of maintenance of accounts.

Mysql,postgreSQL, SQLServer
DELETE FROM Accounts WHERE AccountType = 'Savings' AND AccountCreationDate < '2020-01-01';
The SQL statement DELETE FROM Accounts WHERE AccountType = 'Savings' AND AccountCreationDate < '2020-01-01'; delete records from the "Accounts" table with the account type being "Savings" and an account creation date prior to January 1, 2020. This query will filter rows based upon two conditions checking for the account type and then comparing it with a given date to ensure that only those accounts created prior to the date are deleted. It also helps clean up or manage older account records according to certain business rules or data management needs.

13. delete account whose ID is neither 1 nor 2.
DELETE FROM Accounts WHERE AccountID NOT IN (1, 2);
The command DELETE FROM Accounts WHERE AccountID NOT IN (1, 2); would delete all of the records from the Accounts table if the account ID is not one of the values 1 or 2. It just means in layman's terms that it deletes any account that does not have an ID of 1 or 2. For example, if accounts with IDs 3, 4, or 5 exist, then those records would be deleted but accounts with IDs 1 and 2 would not be touched. This is helpful if you want to delete most of the records but keep a certain number of specific accounts in the database. Be careful whenever you use the DELETE command because once deleted, the records are lost unless you have a backup.

14.Delete accounts whose creation date is between January 1, 2020, and December 31, 2020
Oracle
DELETE FROM Accounts WHERE AccountCreationDate BETWEEN TO_DATE('2020-01-01', 'YYYY-MM-DD') AND TO_DATE('2020-12-31', 'YYYY-MM-DD');
Explanation
The SQL query DELETE FROM Accounts WHERE AccountCreationDate BETWEEN TO_DATE('2020-01-01', 'YYYY-MM-DD') AND TO_DATE('2020-12-31', 'YYYY-MM-DD'); deletes entries in the Accounts table whose AccountCreationDate falls inside this date range: between January 1, 2020 and December 31, 2020. In Oracle, to compare these properly dates the function TO_DATE() is applied on string representations of those dates. The question will delete all the accounts that are present in the year 2020 and therefore, clean up that table for that specific period.
Mysql,postgreSQL, SQLServer
DELETE FROM Accounts WHERE AccountCreationDate BETWEEN '2020-01-01' AND '2020-12-31';
This query deletes all records from the Accounts table that have a creation date between 01/01/2020 and 12/31/2020, inclusive it specifically looks for accounts opened all of last year. Thus, any account registered during this period is deleted permanently. This can be useful for removing old or unused accounts from the database, keeping the data current and relevant. It is usually a good practice to run through such a query by reviewing the data in advance or taking a backup, so if something crucial gets deleted due to this query, there would not be an irretrievable loss. This will avoid accidental deletions that might still be required for auditing or other purposes of analysis.

15.Delete account created after 1st March, 2021 if the balance is not equal to 100
Oracle
DELETE FROM Accounts WHERE AccountCreationDate > TO_DATE('2021-03-01', 'YYYY-MM-DD') AND Balance != 100;
The SQL statement for deleting records in the Accounts table based on two conditions is as follows: DELETE FROM Accounts WHERE AccountCreationDate > TO_DATE('2021-03-01', 'YYYY-MM-DD') AND Balance ! = 100;. This also makes sure the Balance is not equal to 100. The question with this helps to eliminate some accounts that appear to have a balance other than the amount set to match, as they were created after a cut off and cleanse the data.
Mysql,postgreSQL, SQLServer
DELETE FROM Accounts WHERE AccountCreationDate > '2021-03-01' AND Balance != 100;
A SQL statement DELETE FROM Accounts WHERE AccountCreationDate > '2021-03-01' AND Balance ! = 100; is an example of a command to delete data from the Accounts table that have two conditions. The first one is where the AccountCreationDate is after March 1, 2021. The other condition is that the Balance of an account should not be 100. Such a query deletes specific accounts created after a particular date but don't meet the balance required condition and thus, filters unwanted data and allows more proper account management.

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;

16. Delete accounts with a balance that is neither 0 nor 100
DELETE FROM Accounts WHERE Balance NOT IN (0, 100);
The SQL query DELETE FROM Accounts WHERE Balance NOT IN (0, 100); This query deletes all the records from the Accounts table in which Balance is neither 0 nor 100. The NOT IN condition checks for any values in the Balance column that are not in the specified list of values (0, 100). This question deletes the accounts, if the balances outside the specified amount exist, that is useful for accounts management, as there might be balances that do not meet the criteria because they are unusual or unwanted.

17.Delete transactions with a type of 'Withdrawal' and an amount less than 100
DELETE FROM Transactions
WHERE
TransactionType = 'Withdrawal' AND Amount < 100;
The SQL query DELETE FROM Transactions WHERE TransactionType = 'Withdrawal' AND Amount < 100; would delete all records from the Transactions table with the transaction type as 'withdrawal' and where an amount is less than 100. That is, such a query will delete particular transactions depending on their withdrawal amounts being below 100. In this regard, such deletion minimizes unnecessary or small withdrawal transactions that may be considered not so important for analysis, reporting or business purposes. This will leave the table with meaningful withdrawal transactions only.

18.Delete transactions that are either 'Deposit' or 'Transfer' and occurred before February 1, 2022
Oracle
DELETE FROM Transactions
WHERE
TransactionType IN ('Deposit', 'Transfer')
AND
tDate < TO_DATE('2022-02-01', 'YYYY-MM-DD');
This SQL query will delete rows from the Transactions table where TransactionType is 'Deposit' or 'Transfer' and tDate the transaction date was before February 1, 2022. In Oracle TO_DATE is used to ensure that the date format will be compatible with how the database stores dates. The IN clause allows more than one value to be checked for the column TransactionType, making the query even more flexible and efficient in its process. These older records done away with would give the businesses a cleaner database, optimized performance and ensure that only relevant data is held and saved for analysis or reporting purposes. This query will help companies clean out unnecessary or old transaction data.
Mysql,postgreSQL, SQLServer
DELETE FROM Transactions
WHERE
TransactionType IN ('Deposit', 'Transfer')
AND
tDate < '2022-02-01';
This SQL query deletes records from the table of transactions if TransactionType is 'Deposit' or 'Transfer' and the tDate that is the transaction date is prior to February 1, 2022. That would delete the older deposit and transfer transactions possibly now considered no longer relevant to current business processes, reporting or data analysis. The IN clause ensures to check on multiple transaction types. However the date condition ensures that only the transactions before the date when this command will be executed are to be deleted.

19.Delete transactions that do not have an account associated with them.
DELETE FROM Transactions WHERE AccountID NOT IN (SELECT AccountID FROM Accounts);
This SQL query deletes all the records from the table called Transactions where the AccountID is not found in the table called Accounts. This is checked by using the keyword NOT IN where it checks whether the AccountID found in the table called Transactions exists or does not exist in the AccountID column of the Accounts table. Where the Account ID found in the Accounts table does not exist then that record gets deleted from the Transactions table. For instance, this can be helpful in eliminating orphaned transaction records only guaranteeing that those transactions that are connected with existing accounts are stored in the database.

20.Delete transactions that occurred on the weekend over either a Saturday or a Sunday.
Oracle
DELETE FROM Transactions WHERE TO_CHAR(tDate, 'D') IN (1, 7);
MySQL
DELETE FROM Transactions WHERE DAYOFWEEK(tDate) IN (1, 7);
PostgreSQL
DELETE FROM Transactions WHERE EXTRACT(DOW FROM tDate) IN (0, 6);
SQL Server
DELETE FROM Transactions WHERE DATEPART(WEEKDAY, tDate) IN (1, 7);

21.Delete transactions that are between two specific amounts (200 AND 500)
DELETE FROM Transactions WHERE Amount BETWEEN 200 AND 500;
The SQL statement deletes rows from the Transactions table based on a condition where the Amount is between 200 and 500. The BETWEEN operator is used in order to filter rows where the column Amount has values that are greater or equal to 200 and less or equal to 500. Results of such an operation will delete all transactions in the table for this specified range. Thus, more care must be taken while executing queries. Before running the DELETE query, one needs to have a SELECT query with the same condition to preview the records that are going to be affected.