Top Rated Database Management Interview Questions

Q – 1 How to store space between two strings in database i.e oracle?

Ans- select || ‘ <> ‘ || from dual; ex. select ‘RAM’ || ‘ ‘ || ‘PRASAD’ FullName from dual;

Q – 2 What is the difference between inline query and corelated subquery?

Ans- Inline query : select * from emp where empno=(select max(empno) from emp);

Co-Related sub query: Select * from emp where dob> (select distinct dob where
deptno= 10);

Note: In the Inline subquery , The main query depends upon the outcome value of the inline sub query which is in-lined but in case of co-related sub query the main query and sub query both run simultaneously then the check occurs.

Q – 3 Tell me Dept wise average salary?

Ans- select deptno,trunc(avg(sal))as avg from emp group by deptno;

Q – 4 Do you know how to join two table?

Ans- There are two types of join inner join and outer join.

(1)Inner join will return all the common records in both the table

Ex: Select e.ename from Employee e,Dept d where e.did=d.did;

(2)Outer join is of 3 types Left Outer,Right Outer and full outer.
Left outer retrieves common records from both the table and all the record from the left table.
Similarly Right Outer all the records from right table and Full outer from both the tables

Q – 5 Explain What are the uses of Oracle clinical?

Ans- The Major Functions Supported by Oracle Clinical are:

* Clinical Study Protocol Definition and Management
* Definition of Metadata collected during a Clinical Study.
* Creation of Data Entry System
* Creation of Data Management System to clean and reconcile Data
* Validation Procedures
* Data Loading and Extracting
* Thesaurus Management System for Coding Medical Terms
* Laboratory Reference Range Management System

Q – 6 What is ORACLE CLINICAL?

Ans- Oracle Clinical or O*C is a database management system designed by Oracle to provide data management, data entry and data validation functionalities to Clinical Trials process.

Q – 7 What is the difference between delete and truncate?

Ans- Truncate is a DDL command, which has autocommit hence cant be rolled back where as delete is DML with user commit & roll back option. Truncate is faster then delete since it wont store any data to backup tablespace. before delting.

Q – 8 What is the difference between oracle CPU and PSU?

Ans- Patch Set Updates (PSUs) are proactive cumulative patches containing recommended bug fixes that are released on a regular and predictable schedule. PSUs are on the same quarterly schedule as the Critical Patch Updates (CPU), specifically the Tuesday closest to the 17th of January, April, July, and October.

Q – 9 What is sufficient to normalize a database up to 3NF?

Ans- dependent on application. Most transactional based databases require this to avoid duplication etc. However, OLAP, these type of databases (typically those used in data warehouses etc.) are not necesarrily normalised as they are built for performance of querying the data contained within.

Q – 10 How to access the default tables of 9i?

Ans- first connect to sysdba user then write sql>grant resource to scott;

Q – 11 Can we update table which is created from view?

Ans- yes we can update view

Q – 12 What is candidate key and primary key, explain different between these two?

Ans- candidate key: when we reduce unnecessary columns from a super key it is called as candidate key primary key: primary key is a constraint, in a table there is only one primary key,a primary key is simple or composite, a simple primary key is based on one column which can be specified either inline outline of the constraint,a composite primary key is based on (2-32)columns which can be specified only out line of the constraint,a primary dose not allows duplicates.

Q – 13 Which acid property will effect when we use commit in trigger?

Ans- it shows run time error….. choosing pragma autonomous_transaction to overcome this problem

Q – 14 How to displaying the eno, ename in a table from row 2 to row 5?

Ans- select *
from (
select rownum num , empno, ename
from emp
)
where num between 2 and 5

——————

we can also use row_number function

Q – 15 What is datapump?

Ans- 1. you can deattach or reattch datapump job.
2. you can parallelise datapump job [PARALLEL=4]
3. without export can do import using NETWORK_LINK parameter
4. can calculate export dump file size using ESIMATE_ONLY
5. expdp utility used which is caled by dbms_datapump procudure
6. remapping capabilities during import
[REMAP_DATAFILE,EMAP_SCHEMA,REMAP_TABLESPACE,REUSE_DATAFILES