DATABASE TESTING INTERVIEW PART-3
Please go through the most frequently asked database testing interview topics.
DML (Data Manipulation Language)
It inserts a record to a table. Let us observe how it is done,
This is how we insert values into a table. All characters and alphanumeric characters(ex – 10023sdf78) must be enclosed in single quotes (‘ ‘ ) and each value must be separated by a comma. Also, we must be careful in entering the data without violating the primary key, foreign key, unique constraints.
Now let us see the table in which the data has been inserted,
Now, let us insert data into the table orders in which a foreign key is referencing the primary key,
Here, we see that 1001 is the same prodid as in the earlier table. Sysdate – it displays the current date set in the system.
Now, let us see the table,
Another way of inserting data into the table is shown below,
Now, let us see the table,
It updates one or more records.
For example – 1) Let us update salary by increasing it by Rs200 and also give a commission of Rs100 where emp no = 7369.
Now, let us update the said record as shown below,
Let us verify if the record has been updated,
Thus, the record(emp no – 7369) has been updated.
2) Increase all salary by 10%
Let us verify it,
It deletes one / some / all the records.
Let us create a table test from table emp – and see how to delete 1 record and how to delete all records from it,
Thus, we have created the table test.
Thus 1 row, ‘miller’ has been deleted.
TCL (Transaction Control Language)
Any DML change on a table is not a permanent one.
We need to save the DML changes in order to make them permanent. We can also undo (ignore) the same DML changes on a table.
The DDL changes cannot be undone as they are implicitly saved.
It undoes the DML changes performed on a table. Let us see in the below example how rollback works,
Let us delete the employee table. When we perform a select operation on emp, we can see that all the rows have been deleted.
We now perform the rollback operation,
Now let us perform the select operation,
Thus performing the rollback operation, we can retrieve all the records which had been deleted.
It saves the DML changes permanently to the database.
Committing after rollback & vice versa will not have any effect
Let us explain the above statement with an example,
We can see that commit has no effect after the rollback operation.
Thus, from above – we can see that rollback has no effect after commit operation.
During an abnormal exit – i.e, shutdown or if the SQL window is closed by mouse click – then all the DML’s will be rolled back automatically.
During a normal exit – exit; – all the DML’s will be auto-committed – and there will be no rollback.
When we perform the following operations in the same order for a table – then INSERT, UPDATE will be committed – because ALTER is a DDL – and thus all the DML’s above it will also be committed – because DDL operations cannot be undone.
Here – only DELETE will be rolled back because it’s a DML.
Here, all are rolled back.