Oracle Interview Questions

Solution for Test Your Knowledge 2

Test your knowledge by answering following questions. Note: • COUNTRIES • DEPARTMENTS • EMPLOYEES • JOBS • JOB_HISTORY • LOCATIONS • REGIONS 1. Write a query in SQL to display the first name, last name, department number, and department name for each employee. Solution: 2. Write a query in SQL to display the first andContinue reading “Solution for Test Your Knowledge 2”

Analytical Functions

Q1. What is Rollup? Answer. ROLLUP produces group subtotals from right to left and a grand total. If “n” is the number of columns listed in the ROLLUP, then there will be n+1 levels of subtotals. FACT1_ID FACT2_ID SALES_VALUE 1 1 4363.55 1 2 4794.76 1 3 4718.25 1 4 5387.45 1 5 5027.34 1Continue reading “Analytical Functions”

Subquery Factoring or Common Table Expression (CTE)

Q1. What is Subquery Factoring? Answer. Subquery factoring is implemented with the usage of with clause. The WITH clause may be processed as an inline view or resolved as a temporary table. The advantage is that repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary table,Continue reading “Subquery Factoring or Common Table Expression (CTE)”

Triggers

Q1. Explain two virtual tables available at the time of database trigger execution. Answer. :OLD and :NEW are the 2 virtual tables available at the time of database trigger execution. The table columns are referred as :OLD.column_name and :NEW.column_name. Trigger Event Availability of values in :NEW.column_name :OLD.column_name Insert Available Not Available Update Available Available DeleteContinue reading “Triggers”

Functions, Procedures & Packages

Q1. What are the various types of parameter modes in a procedure / function? Answer. IN, OUT AND INOUT. Q2. What is a transaction? Answer. A transaction is a set of SQL statements between any two COMMIT and ROLLBACK statements. Q3. What is Sysdate? A function or A pseudo column or A system defined variable?Continue reading “Functions, Procedures & Packages”

Cursors

Q1. What is a cursor? Why Cursor is required? Answer. Cursor is a named private SQL area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows. Cursors are unidirectional only. Q2. What are cursor attributes? Explain them. Answer. Following are the cursor attributes:• %Found – toContinue reading “Cursors”

Indexes

Q1. What is the difference between foreign key and reference key? Answer. A Foreign key is the key which refers to another table’s primary key.A Reference key is the primary key of table referred by another table. Q2. Differentiate between Primary key and Unique Key Answer. Primary key and Unique key both enforces uniqueness ofContinue reading “Indexes”

Views & Materialized Views

Q1. Why are views created? Answer. Views are created for:• Storing complicated queries in the database so that they are not to be re-written every time they are required.• Hide the complicated logics on joins from the users.• Hide the sensitive data from the users by limiting their view to limited fields mentioned in views.•Continue reading “Views & Materialized Views”

Tables & External Tables

Q1. What are the various constraints that can be applied to a Table? Answer. NULL, NOT NULL, CHECK, DEFAULT, Primary Key & Foreign Key. Q2. What is the purpose of a CLUSTER? Answer. Oracle does not allow a user to specifically locate tables, since that is a part of the function of the RDBMS. However,Continue reading “Tables & External Tables”

General SQL & PL/SQL

Q1. Difference between DBMS & RDBMS Answer. DBMS RDBMS Data is stored in form of files. Data is stored in form of tables. Normalization is not applied. Normalization is applied. Relations between different data is not set as data is stored in form of files. Relationship between tables can be set as data is storedContinue reading “General SQL & PL/SQL”