1. How to store space between two strings in database i.e
oracle?
select <string1>|| ' <<Number of spaces needed with in
single queotes>> ' ||<string2> from dual;
ex. select 'RAM' || ' ' || 'PRASAD' FullName from dual;
2. What is the difference between inline query and corelated subquery?
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.
3. Tell me Dept wise average salary?
select deptno,trunc(avg(sal))as avg from emp group by deptno;
4. Do you know how to join two table?
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
5. Explain What are the uses of Oracle clinical?
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
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.
7. What is the difference between delete and truncate?
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.
8. What is the difference between oracle CPU and PSU?
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.
9. What is sufficient to normalize a database up to 3NF?
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.
10. How to access the default tables of 9i?
first connect to sysdba user then write
sql>grant resource to scott;
