FAQ - Database Testing ( 31 to 40 )

31. What operator performs pattern matching?

LIKE operator

32. What are three SQL keywords used to change or set someone’s permissions?

GRANT, DENY, and REVOKE.

33. What is the value of Comm & Sal after executing the following query if the initial value of 'Sal' is 10000?

UPDATE EMP SET SAL = SAL + 1000, COMM = SAL * 0.1;

Sal = 11000, Comm = 1000

34. What is the advantage to use trigger in your PL?

Triggers are fired implicitly on the tables/views on which they are created. There are various advantages of using a trigger. Some of them are:- Suppose we need to validate a DML statement (insert/Update/Delete) that modifies a table then we can write a trigger on the table that gets fired implicitly whenever DML statement is executed on that table.- Another reason of using triggers can be for automatic updating of one or more tables whenever a DML/DDL statement is executed for the table on which the trigger is created.- Triggers can be used to enforce constraints. For eg : Any insert/update/ Delete statements should not be allowed on a particular table after office hours. For enforcing this constraint Triggers should be used.

- Triggers can be used to publish information about database events to subscribers. Database event can be a system event like Database startup or shutdown or it can be a user even like User login in or user logoff.

35. Which command displays the SQL command in the SQL buffer, and then executes it?

You set the LIST or L command to get the recent one from SQL Buffer

36. What the difference between UNION and UNION ALL?

Union will remove the duplicate rows from the result set while Union all does not.

37. How to store directory structure in a database?

We can do it by the following command,

CREATE OR REPLACE DIRECTORY AS 'C:\TMP'

38. How to copy SQL table?

COPY FROM DATABASE TO DATABASE ACTION - DESTINATION_TABLE (COLUMN_NAME, COLUMN_NAME...) USING QUERY Example:COPY FROM SCOTT/TIGER@ORCL92 - TO SCOTT/TIGER@ORCL92- CREATE NEW_EMP – USING SELECT * FROM EMP;

39. Which command executes the contents of a specified file?

START or @

40. What are the different types of Normalization forms?

There are five normal forms. It is necessary for any database to be in the third normal form to maintain referential integrity and non-redundancy.

First Normal Form
Every field of a table (row, column) must contain an atomic value.

Second Normal Form
All columns of a table must depend entirely on the primary key column.

Third Normal Form
All columns of a table must depend on all columns of a composite primary key.

Fourth Normal Form
A table must not contain two or more independent multi-valued facts. This normal form is often avoided for maintenance reasons.

Fifth Normal Form
It is about symmetric dependencies.Each normal form assumes that the table is already in the earlier normal form.