Database Interview Questions - 3

Q: Are views automatically updated when base tables are updated?
A: Yes

Q: Can a trigger written for a view?
A: No

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 ?
A: 7.5

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 ?
A: 3

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)
A: 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
UNION
Select 'A' from dual;
A: Error

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 ?
A: No

Q: Can you create index on view
A: No.

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 ?
A: Valid

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.