FAQ - Database Testing ( 61 to 70 )

61. Can we call user-defined packages in SQL statements?

I do not agree that we can't call user- defined packages in the SQL statements. Instead we can call user defined packaged functions in the SELECT statement.
e.g. SELECT pkg.test(10) FROM DUAL;

62. What is table space?

Table-space is a physical concept. It has pages where the record of the database is stored with a logical perception of tables. So table space contains tables.

63. Explain normalization with examples?

Normalization is a process of eliminating the redundancy and increasing the integrity.

64. Given an unnormalized table with columns?

The query will be:

DELETE FROM TABNAME WHERE ROWID NOT IN (SELECT MAX (ROWID) FROM TABNAME GROUP BY NAME;

Here tabname is the table name

65. What is Reference cursor?

Reference cursor is dynamic cursor used with SQL statement like For select* from emp;

66. What is the difference between SQL and SQL Server?

SQLServer is an RDBMS just like oracle, DB2 from Microsoft whereas Structured Query Language (SQL), pronounced "sequel", is a language that provides an interface to relational database systems. IBM developed it in the 1970s for use in System R. SQL is a de facto standard, as well as an ISO and ANSI standard. SQL is used to perform various operations on RDBMS.

67. Difference between Store Procedure and Trigger?

Information related to Stored procedure you can see in,
USER_SOURCE, USER_OBJECTS (current user) tables

Information related to triggers stored in USER_SOURCE,USER_TRIGGERS (current user) Tables.Stored procedure can't be inactive but trigger can be Inactive.

68. I have a table with duplicate names in it. Write me a query which returns only duplicate rows with number of times they are repeated?

SELECT COL1 FROM TAB1 WHERE COL1 IN (SELECT MAX (COL1) FROM TAB1 GROUP BY COL1 HAVING COUNT (COL1) > 1 )

69. What is cluster? What is cluster index & non-cluster index?

Clustered Index: - A Clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table may have only one clustered index. Non-Non-Non-Clustered Index:- A Non-Clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows in the disk. The leaf nodes of a non-clustered index do not consists of the data pages. Instead the leaf node contains index rows.

70. What is Trigger?

A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS.Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; the DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures.

Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.