ORACLE PL/SQL Chapter Wise Interview Questions – General-Theory
What is SQL?
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.
What is database normalization?
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.
What do you understand by Deadlock situation?
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.
How can you generate debugging output from PL/SQL?
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.
What is a Dual?
Dual is a Table that contains a single row. It has only one column which has varchar2 data type and the value “X”.
SQL> <em>describe dual</em>;
SQL> <em>Name Null? Type</em>
SQL> select *from dual;
What is a User Account in Oracle?
A user account is identified by a user name and it defines the user’s attributes like,
- Password for database authentication
- Privileges & role
- default tablespace for data objects
- default temporary tablespace for query processing workspace.
What command is used to debug the errors?
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
A table has the following data: [[5, Null, 10]].What will the average function return?
Is Sysdate a system variable or a system function?
What is the result of the following SQL?
Are views automatically updated when base tables are updated?
Can you alter synonym of view or view?
What is the difference between a view and a synonym?
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.
What’s the length of SQL integer?
32 bit length
What is the difference between foreign key and reference key?
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.
Can dual table be deleted, dropped or altered or updated or inserted?
If content of dual is updated to some value computation takes place or not?
If any other table same as dual is created would it act similar to dual?
Assume that there are multiple databases running on one machine. How can you switch from one to another?
Changing the ORACLE.SID
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?
If you insert a row in a table, then create another table and then say Rollback. In this case will the row be inserted?
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.
How to display row number with records?
To achieve this use “rownum” pseudocolumn with query, like
SQL> select rownum, ename from emp;
How to display the records between two ranges?
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
Which date function returns number value?
What is the maximum no. of columns a table can have?
Describe Oracle database’s physical and logical structure?
Physical: Data files, Redo Log files, Control file.
Logical: Tables, Views, Tablespaces, etc,
What are the various types of queries?
The types of queries are:
- Normal Queries
- Sub Queries
- Co-related queries
- Nested queries
- Compound queries
What is SELECT statement?
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.
What is a transaction?
A transaction is a set of SQL statements between any two COMMIT and ROLLBACK statements.
Which of the following is not a schema object: Indexes, tables, public synonyms, triggers and packages?
Other way to replace query result null value with a text
SQL> Set NULL 'N/A’ to
reset SQL> Set NULL "
What are the more common pseudo-columns?
SYSDATE, USER , UID, CURRVAL, NEXTVAL, ROWID, ROWNUM
What is the output of SIGN function?
1 for positive value,
0 for Zero,
-1 for Negative value.
Can CHECK constraint be used for self referential integrity?
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.
What are the minimum no of extents allocated to a rollback extent?
What are the states of a rollback segment? What is the difference between partly available and needs recovery?
The various states of a rollback segment are:
- PARTLY AVAILABLE
- NEEDS RECOVERY
An insert statement followed by a create table statement followed by rollback? Will the rows be inserted?
Can you define multiple Savepoints?
Can you Rollback to any Savepoint?