ORACLE PL/SQL Chapter Wise Interview Questions – General-Theory

ORACLE PL/SQL Chapter Wise Interview Questions – General-Theory

Question 1:
What is SQL?
Answer:
Structured Query Language (SQL) is a language that provides an interface to relational database systems.
In common usage SQL also encompasses DML (Data Manipulation Language), for INSERTS, UPDATES, DELETES and DDL (Data Definition Language), used for creating and modifying tables and other database structures.

Question 2:
What is database normalization?
Answer:
Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminating redundant data (for example, storing the same data in more than one table) and ensuring data dependencies make sense (only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.

Question 3:
What do you understand by Deadlock situation?
Answer:
Deadlock is a situation in a multiuser system that causes two or more users to wait indefinitely for Locked resources. First user needs a resource locked by the second user and the second user needs a resource locked by the first user. To avoid dead locks, avoid using exclusive table locks and if using, use it in the same sequence and use commit frequently to release locks.

Question 4:
How can you generate debugging output from PL/SQL?
Answer:
Use the DBMS_OUTPUT package. Another possible method is to just use the SHOW ERROR command, but this only shows errors. The DBMS_OUTPUT package can be used to show intermediate results from loops and the status of variables as the procedure is executed. The new package UTL_FILE can also be used.

Question 5:
What is a Dual?
Answer:
Dual is a Table that contains a single row. It has only one column which has varchar2 data type and the value “X”.

e.g.
SQL> describe dual;
SQL> Name Null? Type
DUMMY VARCHAR2(1)
SQL> select *from dual;
D
--
X

Question 6:
What is a User Account in Oracle?
Answer:
A user account is identified by a user name and it defines the user’s attributes like,

1. Password for database authentication
2. Privileges & role
3. default tablespace for data objects
4. default temporary tablespace for query processing workspace.

Question 7:
What command is used to debug the errors?
Answer:
Unless your program is an anonymous block, your errors will not be reported.

Instead, SQL*Plus will display the message
"warning: procedure created with compilation errors”.
You will then need to type:
SQL> SHOW ERRORS

Question 8:
A table has the following data: [[5, Null, 10]].What will the average function return?
Answer:
7.5

Question 9:
Is Sysdate a system variable or a system function?
Answer:
System Function

Question 10:
What is the result of the following SQL?
Answer:
Error

Question 11:
Are views automatically updated when base tables are updated?
Answer:
Yes

Question 12:
Can you alter synonym of view or view?
Answer:
No

Question 13:
What is the difference between a view and a synonym?
Answer:
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.

Question 14:
What’s the length of SQL integer?
Answer:
32 bit length

Question 15:
What is the difference between foreign key and reference key?
Answer:
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.

Question 16:
Can dual table be deleted, dropped or altered or updated or inserted?
Answer:
Yes

Question 17:
If content of dual is updated to some value computation takes place or not?
Answer:
Yes

Question 18:
If any other table same as dual is created would it act similar to dual?
Answer:
Yes

Question 19:
Assume that there are multiple databases running on one machine. How can you switch from one to another?
Answer:
Changing the ORACLE.SID

Question 20:
If I have an execute privilege on a procedure in another users schema, can I execute his procedure even though I do not have privileges on the tables within the procedure?
Answer:
Yes

Question 21:
If you insert a row in a table, then create another table and then say Rollback. In this case will the row be inserted?
Answer:
Yes, as “create table” is a DDL statement, which commits automatically as soon as it is executed. The DDL commits the transaction even if the create statement fails internally (e.g. table already exists error) and not syntactically.

Question 22:
How to display row number with records?
Answer:
To achieve this use “rownum” pseudocolumn with query, like
SQL> select rownum, ename from emp;
Output:

Question 23:
How to display the records between two ranges?
Answer:
For example consider the following query with table name “emp”.

SQL>select rownum, empno, ename from emp where round in
(select round from emp where rownum <=&upto minus
select rowidfrom emp where rownum<&Start);

Enter value for upto: 10
Enter value for Start: 7

Question 24:
Which date function returns number value?
Answer:
months between

Question 25:
What is the maximum no. of columns a table can have?
Answer:
254

Question 26:
Describe Oracle database’s physical and logical structure?
Answer:
Physical: Data files, Redo Log files, Control file.
Logical: Tables, Views, Tablespaces, etc,

Question 27:
What are the various types of queries?
Answer:
The types of queries are:

1. Normal Queries
2. Sub Queries
3. Co-related queries
4. Nested queries
5. Compound queries

Question 28:
What is SELECT statement?
Answer:
The SELECT statement lets you select a set of values from a table in a database. The values selected from the database table would depend on the various conditions that are specified in the SQL query.

Question 29:
What is a transaction?
Answer:
A transaction is a set of SQL statements between any two COMMIT and ROLLBACK statements.

Question 30:
Which of the following is not a schema object: Indexes, tables, public synonyms, triggers and packages?
Answer:
Public synonyms

Question 31:
Other way to replace query result null value with a text
Answer:

SQL> Set NULL 'N/A’ to 
reset SQL> Set NULL "

Question 32:
What are the more common pseudo-columns?
Answer:
SYSDATE, USER , UID, CURRVAL, NEXTVAL, ROWID, ROWNUM

Question 33:
What is the output of SIGN function?
Answer:
1 for positive value,
0 for Zero,
-1 for Negative value.

Question 34:
Can CHECK constraint be used for self referential integrity?
Answer:
Yes. In the CHECK condition for a column of a table, we can reference some other column of the same table and thus enforce self referential integrity.

Question 35:
What are the minimum no of extents allocated to a rollback extent?
Answer:
Two

Question 36:
What are the states of a rollback segment? What is the difference between partly available and needs recovery?
Answer:
The various states of a rollback segment are:

1. ONLINE
2. OFFLINE
3. PARTLY AVAILABLE
4. NEEDS RECOVERY
5. INVALID

Question 37:
An insert statement followed by a create table statement followed by rollback? Will the rows be inserted?
Answer:
No

Question 38:
Can you define multiple Savepoints?
Answer:
Yes

Question 39:
Can you Rollback to any Savepoint?
Answer:
Yes