1. What are the different types of joins?
Inner Join
outer join (left outerjoin , right outer join)
full outer join
2.
Explain normalization with examples.
First Normal Form : Avaoids relation within a relation
Second Normal Form : Fully functional depended on each key
Third Normal Form : Avoids Transitivity dependency
First Normal Form : Avaoids relation within a relation
Second Normal Form : Fully functional depended on each key
Third Normal Form : Avoids Transitivity dependency
3. What cursor type do you use to retrieve multiple recordsets?
ref cursors'
4. State the difference between a "where" clause and a "having" clause
having comes along with group by
5. What is the difference between "procedure" and "function"?
procedure doesnot return a value thru return statement
6. How will you copy the structure of a table without copying the data?
select * from table_naem where 1 > 3
7. How to find out the database name from SQL*PLUS command prompt?
Select name from v$database
8. Tadeoffs with having indexes
9. Talk about "Exception Handling" in PL/SQL?
10. What is the difference between "NULL in C" and "NULL in Oracle?"
11. What is Pro*C? What is OCI?
12. Give some examples of Analytical functions.
13. What is the difference between "translate" and "replace"?
translate and replace both are replacing 3rd argument into
first when 2nd argument is match with first.
but only difference is replace function replace whole string
but translate fucntion replace character when
matching 2nd argument with first.
syntax: translate(string, if , then)
if condition matching
14. What is DYNAMIC SQL method 4?
SELECT ename, job, sal + comm FROM emp WHERE deptno = 20
15.
How to remove duplicate records from a table?
SQL> DELETE FROM table_name A WHERE ROWID > (
2 SELECT min(rowid) FROM table_name B
3 WHERE A.key_values = B.key_values);
Delete all rowids that is BIGGER than the SMALLEST rowid value
(for a given key).
Method 2:
SQL> create table table_name2 as select distinct * from
table_name1;
SQL> drop table table_name1;
SQL> rename table_name2 to table_name1;
16.
What is the use of Analyzing the tables?
Whenever we have some performance issues. First thing we will do
is run this command on all the tables in our schema
so all the statistics are up to date.
ANALYZE TABLE <TABLE_NAME> COMPUTE STATISTICS;
We have Oracle 11g R2 database.
1) Is the command what we are using above is pretty old ? Is there a newer one ?
so all the statistics are up to date.
ANALYZE TABLE <TABLE_NAME> COMPUTE STATISTICS;
We have Oracle 11g R2 database.
1) Is the command what we are using above is pretty old ? Is there a newer one ?
yes & yes
>
>
2) What command can we use as SYS user to ANALYZE an entire
schema ?
DBMS_STATS.GATHER_SCHEMA_STATS
17. How to run SQL script from a Unix Shell?
18.
What is a "transaction"? Why are they necessary?
19.
Explain Normalization and Denormalization with examples.
20.
When do you get constraint violation? What are the types of constraints?
21.
How to convert RAW datatype into TEXT?
22. What is the
difference between Primary Key and Aggregate Key
Primary Key is a much similar to unique key. Only
difference is that unique key can be null but primary key can not be null.
Primary key is used to avoid duplication of data.
A primary key consists of more than one column. Also known
as a concatenated key or Aggregate Key. it is also called as
composite key.
Example for varchar & varchar2()
Emp_name varchar(10) - if you enter value less than 10
then remaining space can not be deleted. it used total 10 spaces.
Emp_name varchar2(10) - if you enter value less than 10 then
remaining space is automatically deleted.
23.
How functional dependency is related to database table design?
24. What is a "trigger"?
25.
Why can a "group by" or "order by" clause be expensive to
process?
26.
What are "HINTS"? What is "index covering" of a query?
27.
What is a VIEW? How to get script for a view?
View is a logical table based on a table or another view. logical in the sense it doesn't contains data of its own it a saved query in the database. 2 types are avialable simple view and complex view. The tables on which a view is based is called as base table. Views are saved as a select statement in the data dictionary user_views
View is a logical table based on a table or another view. logical in the sense it doesn't contains data of its own it a saved query in the database. 2 types are avialable simple view and complex view. The tables on which a view is based is called as base table. Views are saved as a select statement in the data dictionary user_views
28.
What are the Large object types supported by Oracle?
29.
What is SQL*Loader?
sql * loader is an enviornment where you are exucting sql and
plsql statements.
30. Difference between "VARCHAR" and "VARCHAR2" datatypes.
The difference between Varchar and Varchar2 is
both are variable length but only 2000 bytes of character of data can be store
in varchar where as 4000 bytes of character of data can be store in varchar2.
31.
What is the difference among "dropping a table", "truncating a
table" and "deleting all records" from a table.
32.
Difference between "ORACLE" and "MICROSOFT ACCESS"
databases.
33. How to create a database link ?
33. How to create a database link ?
EmoticonEmoticon