DBMS Interview Questions

• It doesn’t require input from users during transactions.
• Transactions must not be opened while browsing through data.
• Make use of lower transaction levels.
• While transacting, least information of data must be accessed.

• Implicit cursor– Implicit cursors are automatically created whenever an SQL statement is executed and this happens without the user’s notice.
• Explicit cursor– Explicit cursors are created with the awareness of users. It handles the SQP/PL query in one row and exercises more control over the context area.

• DDL– Data Definition Language which includes CREATE, ALTER, DROP.
• DML– Data Manipulation Language which includes SELECT, UPDATE, INSERT, etc.
• DCL– Data Control Language which consists of GRANT and REVOKE.
• TCL– Transaction Control Language such as COMMIT and ROLLBACK.

Nested loop- A nested loop is a loop within a loop. It is an inner loop within an outer body that allows fewer entries. Individual entries in nested loop is processed individually in the inner loop. Example-

.Select col.*, col2.* from coll, col2 where coll.coll=col2.col2;

The nested way works in following format-

For i in (select*from coll) loop

For j in (select*from col2 where col2 = i.coll) loop

Results are displayed

End of the loop;

End of the loop;

For nested loop, first outer (driving) table is identified. Then inner tavle is assigned to the outer table and every row in inner column is accessible from every row of outer table. Nested loop is executed from hash join, inner loop and then outer loop.

Hash join- it is divided into-

• Build- this hash table has in-memory which is present on the smaller table.
• Probe – it is the hash value present in each second row element.
• Sort merge element- it joins two independent sources of data.

It is considered a better option as compared with nested loop when data volume is big. The full operation of hash join can be classified as-

Sort join operation-

Get first row R1 from input1

Get first row R2 from input2

Merge join operation- 'while' command is not used at loop's end.

If R1 joins with R2

Next row is got R2 from the input2

Return  (R1,R2)

else if R1 ,style="". Next row is got from R1 from

input1

else

next row is got from R2 from input 2

end of the loop.

• When rows are in one partition, query performance in such situations is improved.
• It also helps to access large parts of single partition.
• Data which are rarely used can be stored in slower and cheaper storage medium.

1. CREATE table command- It defines each column of the table in a unique and distinct manner like name, data type, size, etc.

   Syntax: CREATE TABLE [column name] ([column definitions]) [table parameters]

2. ALTER table command- ALTER is used to make changes and edit the existing table of database. Syntax: ALTER objecttype objectname parameters.

3. DROP table command – DROP is used for destroying a table and all the recorded data in it. It destroys an existing database.  Syntax: DROP objecttype objectname parameters.

Join depicts the relationship between two or more tables. It combines row of different tables and enables you to select data with reference to data of some other table. There are several joins in database like CROSS JOINs, NATURAL JOINs, EQUI-JOIN, etc. Two most important joins are –

• INNER JOIN – this join returns values that have matching record in both the tables. In this, blank rows are combined in middle.
• OUTER JOIN– it returns all the records of left table. It also returns the values that match with the right table. Blank rows are put in specified side by joining tables in other side.

• Only current database can have views. No other database is allowed to have views other than the current database.
• You cannot make any changes in the computed values of any view.
• You cannot apply full text index definitions.
• Temporary views cannot be created and temporary tables cannot have views.
• It is not associated with any default definitions.
• There are integrity constants that define the working of commands such as insert and delete.
• Triggers such as INSTEAD of is associated with views.