ORACLE



• How to change User ID and Password in Oracle.

• what are differenct types of joins. explain outer join.

• How to print output in Oracle.

• what is a data control component.

• why do we use DBMS ?

• what are logical objects in RDBMS (tables & views).

• what do you know about IDL?

• can we maintain mirror data in different databases.

• Data Integrity constraint is

• (a)to ensure the presence of primary key

• What is indexing in databases?

• What is atomicity?

• What is DBMS and RDBMS ?

• types of DBMS,queries

• Difference between Unique and primary

• what is a cursor

• types of cursors

• normalization

• normal forms

• whis is the best normal form

• what is an os

• what exactly os consists ?

• Types of joins ?

• what is a trigger ?

• sql queries

• types of indexing

• DML,DDL, SQL querry,primary key, whts a null

• diff btw DDL,DML, whts parallel DML,

• whts normalisation explain with example till 3NF

• 6>need for normalisation

• differnce btw oracle 8.0,8i,9i

• what are snapshots.

• what is indexing. what are its advantages.

• what is cluster. advantages.

• how do you add primary key to the table already existing.

• what is table space.

• what partitioning. how many types of partitions.

• what is mutating table.

When a table is in state of transition it is said to be mutating.

eg :: If a row has been deleted then the table is said to be

mutating and no operations can be done on the table except select.

• what is a cursor. what are the types.

two types of cursors: implicit and explicit. PL/SQL declares a cursor implicitly for all

SQL data manipulation statements, including queries that return only one row. However,

for queries that return more than one row, you must declare an explicit cursor or use

a cursor FOR loop.

three commands to control the cursor: OPEN, FETCH, and CLOSE.

initialize the cursor with the OPEN statement, which identifies the active set.

Then, you use the FETCH statement to retrieve the first row.

You can execute FETCH repeatedly until all rows have been retrieved.

release the cursor with the CLOSE statement.

DECLARE

CURSOR c1 IS SELECT ename FROM emp;

name1 emp.ename%TYPE;

name2 emp.ename%TYPE;

name3 emp.ename%TYPE;

BEGIN

OPEN c1;

FETCH c1 INTO name1; -- this fetches first row

FETCH c1 INTO name2; -- this fetches second row

FETCH c1 INTO name3; -- this fetches third row

...

CLOSE c1;

END;

four attributes of cursor : %NOTFOUND, %FOUND, %ROWCOUNT, and %ISOPEN

• what is SGA.

The SGA is a shared memory region allocated by the Oracle that

contains Data and control information for one Oracle Instance.

It consists of Database Buffer Cache and Redo log Buffer.

• how do you override function/procedures in oracle.

• what is raise_application_error.

• what are datafiles.

• what are the default table spaces.

• what are the background processes in oracle.

• what are control files.

• what are the minimum privilege for the user to connect database.

• what is a role. why do you create it.

Questions and Answers :

What is Pseudo Colomn ?

USER, UID, SYSDATE, ROWNUM, ROWID, NULL, AND LEVEL

How to Display output in PL/SQL ?

we first set serveroutput on at SQL Prompt. and then in sql programming we specify as dbms_output.put_line(‘hdjhd’ || ‘sakdha’);

No Displays are allowed in PL/SQL programming with select statement

Write about Commands and Keywords ?

Alter : ALTER TABLE EMP ADD ………. , MODIFY ………. ;

for a in 1..10 loop ……….. end loop;

loop exit when a= g.losal;

Outer join :***********e.deptno(+)=d.deptno;

Different types of Keys ?

Foreign Key : This is a referential integrity constraint and specifies the values of primary key in other table. and is mentioned as :

DEPTNO number(2) REFERENCES DEPT(DEPTNO);

The clause ON DELETE CASCADE added to REFERENCES clause tells Oracle to delete the dependent rows when we delete the row in the parent table.

Unique Key : If declared, then does not accept duplicate and also NULL values. And can have many Unique key fields.

Primary Key : Similar to Unique key but, also maintains an index on primary key and also used to connect two tables.

Write about cursors ?

Implicit (SQL is implicit by default ) and Explicit cursors.

EXIT WHEN C1%NOTFOUND, IF C1%FOUND THEN, IF C1%ISOPEN THEN,

FOR LOOP in CURSOR will :

1) open the cursor.

2) fetches the records from cursor one by one.

3) keeps the track of number of records in the cursor.

Super Dynamism in Cursors is obtained by passing the values as parameters( ie pass by value) ie., for x in c1(a,b)

Multiple Cursors : These are nothing but nested cursors………..

In Implicit Cursors there is no need of declaration of cursors, by default system will create a cursor whenever a query is written…and the conditions are like if SQL%NOTFOUND

if SQL%ROWCOUNT > 2 will keep a count of the number of records updated.

SQL is a keyword and cannot be used to name as a cursor name explicitly.

Difference between group functions and single row functions ?

Group Function Single Row Function

A group function operates A single row function

on many rows returns one and result for one row.

returns single result.

Not allowed in Pl/sql procedural Allowed in Pl/Sql Procedural statements

eg SUM(),AVG,MIN,MAX etc eg UPPER,LOWER,CHR...

Difference between DECODE and TRANSLATE ?

DECODE is value by value TRANSLATE is character by

character replacement. replacement.

Ex SELECT DECODE('ABC','A',1,'B',2,'ABC',3) eg SELECT

from dual; o/p 3 TRANSLATE('ABCGH',

'ABCDEFGHIJ', 1234567899)

FROM DUAL; o/p 12378

(DECODE command is used to bring IF,THEN,ELSE logic to SQL.It tests for the IF values(s) and then aplies THEN value(s) when true, the ELSE value(s) if not.)

Difference between TRUNCATE and DELETE ?

TRUNCATE deletes much faster than DELETE

Truncate Delete

It is a DDL statement It is a DML statement

It is a one way trip,cannot One can Rollback

ROLLBACK

Doesn't have selective features (where clause) Has

Doesn't fire database triggers Does

It requires disabling of referential Does not require

constraints.

What is a CO-RELATED SUBQUERY ?

A CO-RELATED SUBQUERY is one that has a correlation name as table or view designator in the FROM clause of the outer query and the same correlation name as a qualifier of a search condition in the WHERE clause of the subquery.

eg SELECT field1 from table1 X WHERE field2>(select avg(field2) from table1 Y where field1=X.field1);

(The subquery in a correlated subquery is revaluated

for every row of the table or view named in the outer query.)

What are various joins used while writing SUBQUERIES ?

Self join-Its a join foreign key of a table references the same table.

Outer Join--Its a join condition used where One can query all the rows of one of the

tables in the join condition even though they don't satisfy the join condition.

Equi-join--Its a join condition that retrieves rows from one or more tables in which one

or more columns in one table are equal to one or more columns in the second table.

What are various constraints used in SQL ?

NULL, NOT NULL, CHECK, DEFAULT

What are different Oracle database objects ?

TABLES, VIEWS, INDEXES, SYNONYMS, SEQUENCES, TABLESPACES etc

What is difference between Rename and Alias ?

Rename is a permanent name given to a table or column whereas Alias is a temporary name given to a table or column which do not exist once the SQL statement is executed.

What is a view ?

A view is stored procedure based on one or more tables, it’s a virtual table.

What are various privileges that a user can grant to another user ?

SELECT, CONNECT, RESOURCES

What is difference between UNIQUE and PRIMARY KEY constraints ?

A table can have only one PRIMARY KEY whereas there can be any number of UNIQUE keys. The columns that compose PK are automatically define NOT NULL, whereas a column that compose a UNIQUE is not automatically defined to be mandatory must also specify the column is NOT NULL.

Can a primary key contain more than one columns ?

Yes

How you will avoid duplicating records in a query ?

By using DISTINCT

What is difference between SQL and SQL*PLUS ?

SQL*PLUS is a command line tool where as SQL and PL/SQL language interface and reporting tool. Its a command line tool that allows user to type SQL commands to be executed directly against an Oracle database. SQL is a language used to query the relational database(DML,DCL,DDL). SQL*PLUS commands are used to format query result, Set options, Edit SQL commands and PL/SQL.

Which datatype is used for storing graphics and images ?

LONG RAW data type is used for storing BLOB's (binary large objects).

How will you delete duplicating rows from a base table ?

DELETE FROM table_name A WHERE rowid>(SELECT min(rowid) from table_name B where

B.table_no=A.table_no);

CREATE TABLE new_table AS SELECT DISTINCT * FROM old_table;

DROP old_table

RENAME new_table TO old_table

DELETE FROM table_name A

WHERE rowid NOT IN (SELECT MAX(ROWID) FROM table_name

GROUP BY column_name)

What is difference between SUBSTR and INSTR ?

SUBSTR returns a specified portion of a string eg SUBSTR('BCDEF',4) output BCDE

INSTR provides character position in which a pattern is found in a string.

eg INSTR('ABC-DC-F','-',2) output 7 (2nd occurence of '-')

There is a string '120000 12 0 .125' ,how you will find the position of the decimal place ?

INSTR('120000 12 0 .125',1,'.')

output 13

There is a '%' sign in one field of a column. What will be the query to find it ?

'\' Should be used before '%'.

When you use WHERE clause and when you use HAVING clause ?

HAVING clause is used when you want to specify a condition for a group function and it is written after GROUP BY clause

The WHERE clause is used when you want to specify a condition for columns, single row functions except group functions and it is written before GROUP BY clause if it is used.

Which is more faster - IN or EXISTS ?

EXISTS is more faster than IN because EXISTS returns a Boolean value whereas IN returns a value.

What is a OUTER JOIN ?

Outer Join--Its a join condition used where you can query all the rows of one of the tables in the join condition even though they don’t satisfy the join condition.

What is a pseudo column. Give some examples ?

It is a column that is not an actual column in the table.

eg USER, UID, SYSDATE, ROWNUM, ROWID, NULL, AND LEVEL.

Suppose customer table is there having different columns like customer no, payments.What will be the query to select top three max payments ?

SELECT customer_no, payments from customer C1 WHERE 3 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download