DATABASE TESTING INTERVIEW PART-3

Please go through the most frequently asked database testing interview topics.

DML (Data Manipulation Language)

INSERT

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,

UPDATE

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,

DELETE

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.

ROLLBACK

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.

COMMIT

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.

Example:

INSERT
UPDATE
ALTER
DELETE
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.

Example:

INSERT
UPDATE
DELETE
ROLLBACK

Here, all are rolled back.

SAVEPOINT

It is like a pointer (break-point) till where a DML will be rolled back.

Example:

Insert …

Save point x ;

Update …

Delete ..

Rollback to x ;

…
…

Here, only DELETE & UPDATE are rolled back. INSERT is neither rolled back nor committed.

CHECK DATABASE TESTING INTERVIEW PART-4

Wikipedia SQL

DATABASE TESTING INTERVIEW PART-3