Monday, May 3, 2010
Q: Are views automatically updated when base tables are updated?
Q: Can a trigger written for a view?
Q: If all the values from a cursor have been fetched and another fetch is issued, the output will be : error, last record or first record ?
A: Last Record
Q: A table has the following data : [[5, Null, 10]]. What will the average function return ?
Q: Is Sysdate a system variable or a system function?
A: System Function
Q: Consider a sequence whose currval is 1 and gets incremented by 1 by using the nextval reference we get the next number 2. Suppose at this point we issue an rollback and again issue a nextval. What will the output be ?
Q: Definition of relational DataBase by Dr. Codd (IBM)?
A: A Relational Database is a database where all data visible to the user is organized strictly as tables of data values and where all database operations work on these tables.
Q: What is Multi Threaded Server (MTA) ?
A: In a Single Threaded Architecture (or a dedicated server configuration) the database manager creates a separate process for each database user. But in MTA the database manager can assign multiple users (multiple user processes) to a single dispatcher (server process), a controlling process that queues request for work thus reducing the databases memory requirement and resources.
Q: Which are initial RDBMS, Hierarchical & N/w database ?
A: RDBMS - R system
Hierarchical - IMS
N/W - DBTG
Q: What is Functional Dependency?
A: Given a relation R, attribute Y of R is functionally dependent on attribute X of R if and only if each X-value has associated with it precisely one -Y value in R
Q: What is Auditing ?
A: The database has the ability to audit all actions that take place within it.
a) Login attempts,
b) Object Accesss,
c) Database Action
Q: Result of Greatest(1,NULL) or Least(1,NULL)
Q: While designing in client/server what are the 2 imp. things to be considered ?
A: Network Overhead (traffic), Speed and Load of client server
Q: What are the disadvantages of SQL ?
A: Disadvantages of SQL are :
· Cannot drop a field
· Cannot rename a field
· Cannot manage memory
· Procedural Language option not provided
· Index on view or index on index not provided
· View updation problem
Q: When to create indexes?
A: To be created when table is queried for less than 2% or 4% to 25% of the table rows.
Q: How can you avoid indexes?
A: To make index access path unavailable
· Use FULL hint to optimizer for full table scan
· Use INDEX or AND-EQUAL hint to optimizer to use one index or set to indexes instead of another.
· Use an expression in the Where Clause of the SQL.
Q: What is the result of the following SQL:
Select 1 from dual
Select 'A' from dual;
Q: Can database trigger written on synonym of a table and if it can be then what would be the effect if original table is accessed.
A: Yes, database trigger would fire.
Q: Can you alter synonym of view or view ?
Q: Can you create index on view
Q: What is the difference between a view and a synonym ?
A: Synonym is just a second name of table used for multiple link of database. View can be created with many tables, and with virtual columns and with conditions. But synonym can be on view.
Q: What is the difference between alias and synonym ?
A: Alias is temporary and used with one query. Synonym is permanent and not used as alias.
Q: What is the effect of synonym and table name used in same Select statement ?
Q: What's the length of SQL integer?
A: 32 bit length
Q: What is the difference between foreign key and reference key?
A: Foreign key is the key i.e. attribute which refers to another table primary key.
Reference key is the primary key of table referred by another table.