Illustration of SQL DML operations including insert, update, and delete, for managing tables effectively.

6.How do you update multiple rows in a table in a single UPDATE statement?
Example 1: Using WHERE clause with several conditions.
UPDATE Employees
SET Salary = Salary + 5000
WHERE Department = 'HR';
In this case an UPDATE statement is issued along with the WHERE clause to update selected rows based on some conditions. The query increases the Salary by 5000 for all employees who belong to the 'HR' department. Applying the condition WHERE Department = 'HR' only those rows that will meet the condition will be updated while others will not. This technique enables you to run batch updates where records are updated based on certain conditions, such as department or position or any other column value. This is a very simple, straightforward procedure when there is a need to update a number of records that meet the same condition.

Example 2: Using CASE expression to update multiple rows with different values.
UPDATE Employees
SET Salary = CASE
WHEN Department = 'HR' THEN Salary + 5000
WHEN Department = 'IT' THEN Salary + 7000
ELSE Salary
END;
Using a CASE expression within the UPDATE statement enables you to apply different values to different rows in a single query. In the example the `Salary` is updated based on the ‘Department’ value but each department gets a different salary increase. The ‘CASE’ expression checks the ‘Department’ for each employee and applies the corresponding salary increase. Employees in the 'HR' department get an increase of 5000 while employees in the 'IT' department receive a 7000 increase. This method is powerful because it allows you to update multiple rows with different values in a single query ensuring more complex updates are handled efficiently.

7.How do you use the WHERE clause with UPDATE to specify which rows to update?
The WHERE clause in an UPDATE statement is required to identify which rows in a table should be updated. If the WHERE clause is omitted, then all rows in the table would update and this will certainly have some very undesirable consequences. Here WHERE clause will determine and apply the update operation in rows matching some conditions or in a group of conditions.
For example, if one wants to rise the Salary of all employees working in the 'HR' department then the WHERE clause can be applied upon that table to fetch only those rows. Here is an example:
UPDATE Employees
SET Salary = Salary + 3000
WHERE Department = 'HR';
In this question, it will only increase the Salary of those rows whose Department is 'HR' to 3000. Thus, the rest of the employees in other departments must not be affected. A WHERE clause may also contain more complex conditions for instance, it might check multiple values or combine different conditions using logical operators like 'AND' and 'OR'. By the use of WHERE you can ensure that the UPDATE statement updates the correct rows.

8.How do you delete rows from a table using the DELETE statement?
The DELETE statement in SQL is used to delete rows from a table. If there is no WHERE clause, all rows in the table will be deleted. For example, if you want to delete all records from the employees table, you can write
DELETE FROM employees;
This operation will clean up the table but still holds the structure for later use. One must use a WHERE statement and condition to delete specified rows. For example, deleting employees with salaries below 5000.
DELETE FROM employees WHERE salary < 5000;
A condition is very necessary because it will help a person delete only targeted rows thus ensuring other data are intact. Always double-check your DELETE statement to avoid unwanted removal of data.

9.How do you delete all rows in a table without dropping its structure?
It is possible to delete all rows from a table that does not impact its structure through either the DELETE statement or the TRUNCATE statement, depending upon your needs
1. Using DELETE:
The DELETE statement deletes rows from a table but doesn't drop the table structure itself, indexes and constraints still exist.
Example:
DELETE FROM employees;
This one logs each row being deleted, so it might be slower for large tables but gives you the ability to add conditions if needed.
2.Using TRUNCATE:
TRUNCATE is much faster since it doesn't log individual deletions, for example: TRUNCATE TABLE employees;
it instantaneously deletes all rows. It resets auto-increment counters and doesn't even trigger any table events, yet it does not support adding conditions.
Both these methods retain the table structure prepared for re-use.

10.How do you use WHERE with DELETE to delete specific rows?
DELETE in SQL is used to remove rows from a table. However, if you want to delete only specific rows then you have to make use of the WHERE clause defining a condition. In its absence, all the rows from the table would get deleted which is bound to lead to losing unintended data. The WHERE clause is often considered as a intermediate filtering thus ensuring that the rows to be deleted satisfy the condition.
Suppose one wants to delete all employees belonging to "HR" department. To remove one employee or record the WHERE clause is used to specify the above kind of condition.

Example:
DELETE FROM employees WHERE department = 'HR';
This statement deletes only the employees whose department is "HR."
If you wish to delete even more specific rows for example, employees belonging to the "HR" department and also with salary less than 50,000 you can use several conditions in the WHERE clause:

DELETE FROM employees WHERE department = 'HR' AND salary < 50000;
This query will delete only those employees who belong to the "HR" department and also satisfy the salary condition. The key takeaway is that the WHERE clause lets you target certain data to delete, so it will protect other records from being deleted accidentally. Ensure that the condition in the WHERE clause is correct so that valuable data will not be lost.

11.How can I use INSERT ALL to perform batch inserts?
The INSERT ALL SQL statement allows you to insert multiple rows into a single table in one command.
Even though it would seem slightly cumbersome to insert rows of a table by calling separate INSERT statements on each record, inserting in this way would still benefit from improved performance because it could allow for many such inserts in one call to a SQL query.
INSERT ALL
INTO table_name (column1, column2, column3) VALUES (value1, value2, value3)
INTO table_name (column1, column2, column3) VALUES (value4, value5, value6)
INTO table_name (column1, column2, column3) VALUES (value7, value8, value9)
SELECT * FROM dual;
There you can enter multiple rows in table_name by making multiple INTO clauses which define the values of one row.
The part SELECT * FROM dual; is necessary for Oracle SQL for the purpose of terminating the statement.
Suppose you have an employees table and wish to insert multiple rows in a single query. Here's how you can do this:
INSERT ALL
INTO employees (id, name, salary) VALUES (1, 'John Doe', 50000)
INTO employees (id, name, salary) VALUES (2, 'Jane Smith', 60000)
INTO employees (id, name, salary) VALUES (3, 'Alice Brown', 55000)
SELECT * FROM dual;
In this case, three rows are inserted at once into the employees table. The INSERT ALL statement runs all these inserts as a single transaction, thus making it more efficient. This reduces the overhead of multiple INSERT statements and it becomes faster when you need to insert a large number of rows.
This approach is really helpful with batch inserts, data migration or where the values are static where they don't come from another table.

12.How do you insert data from one table into another table using INSERT INTO SELECT?
The use of the INSERT INTO SELECT statement in Oracle lets you easily copy data from one table to another. This may be useful for data transfer, archiving of records or even data transformation for a report. The SELECT queries retrieve data from the source table and the inserted data is entered into the destination table. The source and destination table structures have to be identical as far as column data type and order is concerned, except if column mapping is defined explicitly.
Syntax:
INSERT INTO destination_table (column1, column2,.)
SELECT column1, column2,.
FROM source_table
WHERE condition;

Example:
Assume that you have two tables: employees and employees_backup. The employees_backup table has the same structure as the employees table. To copy all records of employees whose salary is more than 50,000 from employees to employees_backup use the following statement:
INSERT INTO employees_backup (employee_id, name, salary)
SELECT employee_id, name, salary
FROM employees
WHERE salary > 50000;
In this case: 1.The SELECT statement fetches records from the employees table that have salaries greater than 50,000.
2.The INSERT INTO statement copies those records into the employees_backup table.
This is one way of transferring data. The advantage is that the user controls which rows are being inserted with the WHERE clause, especially when dealing with massive datasets.

13.How do you roll back changes after a DML operation using ROLLBACK?
In Oracle, the ROLLBACK command is used in the reversal of uncommitted changes done by DML operations, which are insert, update or delete. It maintains consistency with the data by turning back the database to what it was before errors were detected or modifications occurred unintentionally.
Example:
DELETE FROM employees WHERE department_id = 10;
ROLLBACK;
In this case, the ROLLBACK command rolls back the deleted records since the changes were not committed. It is especially useful when a series of DML operations must succeed together, if any operation fails, you can roll back the entire transaction to ensure data integrity. However, once changes are committed they cannot be rolled back.

14.How do you commit changes after a DML operation using COMMIT?
In Oracle, COMMIT command is used to make permanent the changes done by DML operations like INSERT, UPDATE or DELETE in the database. Once a COMMIT is issued, then changes can never be undone and are now visible to other users. It is typically used at the end of a successful transaction to ensure data consistency.

For example:
UPDATE employees SET salary = salary + 500 WHERE department_id = 10;
COMMIT;
In this case, changes for the employee, to update salary in department 10 will stay after the COMMIT. But if the COMMIT does not occur, changes exist in the current session not visible to the users.
It is very necessary to use COMMIT especially wherever several related operations have to be successful together. Where all the operations are successful, you can commit the changes and finalize the transaction, but where there is an error before committing you can simply revert all the changes using the command ROLLBACK. Therefore proper use of COMMIT will ensure the reliability of the data due to its avoidance of letting partial or incomplete transactions affecting the database.

15.How do you manage transaction control in SQL using SAVEPOINT?
In Oracle, the SAVEPOINT command marks a point in a transaction to which you can roll back if needed. This feature allows partial undoing of changes without affecting the entire transaction.
For example:
SAVEPOINT savepoint1;
UPDATE employees SET salary = 65000 WHERE employee_id = 1;
ROLLBACK TO SAVEPOINT savepoint1;
Here, the SAVEPOINT represents the transaction point before UPDATE operation. In case you realize this update was incorrect, just the ROLLBACK TO SAVEPOINT savepoint1 statement undoes those changes only after the saving point; it does leave prior changes intact. You have a much better control in transactions with this kind of process as well, especially when the processes will be complicated.
SQL Server: SAVE TRANSACTION and ROLLBACK TRANSACTION are available for savepoints.
PostgreSQL: Supports SAVEPOINT and ROLLBACK TO SAVEPOINT fully.

16.How would you update a record using a join in the UPDATE statement?
Update data that is matched on the corresponding records in the table by using an UPDATE statement with a JOIN.
UPDATE employees e JOIN departments d ON e.department_id = d.department_id SET e.salary = 60000 WHERE d.department_name = 'Sales';
This SQL query updates the salary of employees who work in the "Sales" department. It is a join operation between the employees table e and the departments table d using their common department_id. When the department name is 'Sales', it updates their salary to 60,000. The JOIN clause only lets employees from the concerned department be updated while the WHERE condition filters for employees in the "Sales" department.
Notes:
MySQL: This works as it is.
SQL Server: You need a subquery or MERGE since JOIN in UPDATE is not supported
PostgreSQL: Use FROM to join tables in an UPDATE
Oracle: Requires a MERGE statement.

17.How do you delete duplicate rows from a table?
To delete duplicate rows, you can use a CTE command table expression or subquery to identify and remove duplicates:
WITH cte AS (
SELECT employee_id, ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY employee_id) AS rn
FROM employees
)
DELETE FROM cte WHERE rn > 1;

18.What's the use of MERGE for an INSERT or an UPDATE based on condition?
The MERGE statement lets you specify that a record be either INSERTed or updated based upon whether or not it has been found:
MERGE INTO employees e
USING temp_employees t
ON (e.employee_id = t.employee_id)
WHEN MATCHED THEN UPDATE SET e.salary = t.salary
WHEN NOT MATCHED
THEN INSERT (employee_id, name, salary)
VALUES (t.employee_id, t.name, t.salary);

19.How do you use a subquery in the DELETE statement?

A subquery in a DELETE statement lets you delete from a table where conditions you are using for deletion you derive from another table. For example,
DELETE FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'Sales');
This would delete all the employees whose department is "Sales."
Notes:
MySQL: subqueries in DELETE are supported as described above.
SQL Server: the same syntax as shown above works in SQL Server but for complex deletes, you may also be able to use JOIN.
PostgreSQL: It also supports subqueries in DELETE statements as MySQL does.

20.How do you insert data into a table with default values?
You can simply leave out the columns that have default values defined in a table's schema. The DB fills in the default values themselves. Here is how it is done:
INSERT INTO employees (name, department_id)
VALUES ('John Doe', 3);
In this example, suppose the salary column has set a default value. Again, the DB will be able to fill in the respective value without your having to include an explicit mention of it in an INSERT statement.
Notes:
MySQL: Allows inserting rows with default values if columns are not specified.
SQL Server: Same case as MySQL, but this time uses default values for columns not mentioned.
PostgreSQL: Allows inserting data using default values by excluding columns with defaults from the INSERT statement.

21.How do you update values in a table using conditional logic (e.g., CASE)?
To apply conditional logic to updating the values in a table, the CASE expression is applied in an UPDATE statement. The CASE expression provides you with setting up values for different conditions.
Now let's have an example here below:
UPDATE employees
SET salary = CASE
WHEN department_id = 1 THEN salary * 1.1
WHEN department_id = 2 THEN salary * 1.05
ELSE salary
In this case, employees in department 1 get a 10% salary raise and those in department 2 get a 5% raise. The rest of the departments do not get any raise.
Notes:
MySQL: It supports CASE expressions in UPDATE statements as illustrated above.
SQL Server: It works like MySQL; it supports using CASE in an UPDATE statement.
PostgreSQL: It also supports using CASE in an UPDATE query for conditional logic on column values.

22.How do you bulk delete records from a table with conditions?
To delete multiple records from a table based on conditions you can use a DELETE statement with a WHERE clause that defines the conditions for the rows to be deleted.
Here is an example where multiple records are deleted based on certain conditions:
DELETE FROM employees
WHERE department_id = 3 AND hire_date < '01- JAN-2020';
All employees in department 3 that were hired before January 1, 2020, will be deleted in a mass operation.
Notes:
MySQL Supports bulk deletions as illustrated. You can have any number of conditions in the WHERE clause.
SQL Server The same case as MySQL. You can perform a batch delete using conditional logic in the WHERE clause.
PostgreSQL: Also allows this method of bulk deleting records based on some specific conditions. A number of conditions can be combined together by using the AND or OR operators within the WHERE clause.