besanttechnologies

Question 1

What are Schema Objects?

Answer 1

Schema objects are tables, views, sequences, synonyms, indexes,databases triggers, procedures, functions, packages.

Question 2

What is a Table?

Answer 2

The table is collection inforamation.A table is the basic of rows and columns.data’s are stored rows and columns.

Question 3

What is a View?

Answer 3

A View is a virtual table.Not a physical table.
representation of one or more tables
Every view has a query attached to it.

Question 4

What is an Index?

Answer 4

What is an Index?

Question 5

What is a Data file?

Answer 5

A database’s data files contain all the database data. The data of logical database structures is tables and indexes is physically stored in the data files allocated for a database.

Question 6

What is the difference between UNIQUE or PRIMARY KEY Constraint?

Answer 6

A column defined as PRIMARY KEY can contain unique values

same as UNIQUE but UNIQUE can contain NULLs while a column defined as PRIMARY KEY cannot contain NULLs.

Question 7

What is PL/SQL ?

Answer 7

PL/SQL is a programming language. SQL and procedural programming language constructs such as named blocks,iterations, conditional branching..

Question 8

What is the basic structure of PL/SQL ?

Answer 8

PL/SQL uses block structure as its basic two structure. Anonymous blocks or nested blocks can be used in PL/SQL.

Question 9

What is the difference between %ROWTYPE and TYPE RECORD ?

Answer 9

%ROWTYPE is its accept multiple data type.to be used whenever query returns an entire row of a table or view.
TYPE RECORD is accept single data type.to be used whenever query returns columns of different table or views and variables.

Question 10

What is a cursor ? Why cursor is required ?

Answer 10

Cursor is a named private sql area from where information can be passed and executed or accessed. Cursors are required to process rows individually for queries returning multiple rows.

Question 11

Explain the two types of cursors ?

Answer 11

Implicit cursors
Explicit cursors.
For loop cursors.
PL/SQL uses Implicit cursors for queries.
User defined cursors are called explicit cursors.
User using Cursors in for loop the Cursor called for loop Cursor.

Question 12

What is Raise_application_error ?

Answer 12

Raise_application_error is a procedure of package DBMS_STANDARD. that allows to user_defined error or error code and error messages from stored sub program or database trigger.

Question 13

What is the difference between a PROCEDURE and FUNCTION ?

Answer 13

A PROCEDURE may return one or more values or may not return at all.
A FUNCTION must returns a value using the return statement.

Question 14

What is Overloading of procedures ?

Answer 14

The same procedure name is repeated with parameters of different datatypes and parameters in different positions, varying number of parameters is called overloading of procedures.

Question 15

What is a package ? What are the advantages of package ?

Answer 15

database object that groups logically related procedures.
The advantages of packages are modularity, easier application design, information hiding, reusability and better performance

Question 16

What is difference between TRUNCATE and DELETE?

Answer 16

Truncate is Dml Statements. Cannot be rolled back. Delete allows the filtered deletion.
Deleted records can be rolled back or committed.

Question 17

What is a join? Explain the different types of joins?

Answer 17

Join is a query,retrieves data from related columns or rows from multiple tables.
Self join – Joining the table with itself
Equi Join – Joining two tables by equating two common columns
Non Equi Join – Joining two tables based on conditions other than equating two common columns
Outer Join – Joining two tables in such a way that query can also retrieve rows that do not have the corresponding join value in the other table.

Question 18

What is a Subquery?

Answer 18

Subquery is a query embedded with another Query

Outer Query based on the Inner Query

Question 19

What is correlated sub-query?

Answer 19

Correlated sub-query is a sub-query, which has reference to the main query.

Question 20

Difference between SUBSTR and INSTR?

Answer 20

INSTR(string1, string2 [,n,[m]])
its find the position of the string
SUBSTR(string1, n, m)
SUBSTR returns a character string of size m in string 1, starting from nth position of string1.

Question 21

Explain UNION, MINUS, UNION ALL, INTERSECT?

Answer 21

INTERSECT returns all commmon rows selected by both queries.
MINUS returns all distinct rows selected by the first query but not by the second.
UNION returns all unique rows selected by either query.
UNION ALL returns all rows selected by either query, included all duplicates.

Question 22

What are the Usages of SAVEPOINTS?

Answer 22

SAVEPOINTS are Transaction control Language.
its used to subdivide a transaction into smaller parts. It enables rolling back part of a transaction.
Maximums of five save points are allowed.

Question 23

What is ROWID?

Answer 23

ROWID is a pseudo column attached to each row of a table.

It is 18 characters long, blockno. Rownumber.filenumber are the components of ROWID.

Question 24

What is PRIMARY KEY, UNIQUE KEY, FOREIGN KEY?

Answer 24

PRIMARY KEY is a column or a combination of columns of a table which cab be used to uniquely identify a row of the table. PRIMARY KEY cannot be null.
UNIQUE KEY is a column or a combination of columns of a table, which can be used to identify a row of the table uniquely. UNIQUE KEY can be null.
FOREIGN KEY is a column or a combination of column which is the primary key or unique key of the referenced table. FOREIGN KEY can be null.

Question 25

What is ON DELETE CASCADE?

Answer 25

ORACLE maintains referential integrity by automatically removing dependent foreign key values if a referenced primary or unique key value is removed.

Question 26

What is difference between CHAR and VARCHAR2? What is maximum SIZE allowed for each type?

Answer 26

CHAR pads spaces to the maximum length. VARCHAR2 does not pad blank spaces. For CHAR it is 255 and 2000 for VARCHAR2.

Question 27

How to store the results of a query into a file?

Answer 27

The results of a query can be stored into a file by spooling into a file. Eg. SPOOL filename.

Question 28

Which two statements about subqueries are true? (Choose two.)

Answer 28

A. A single row subquery can retrieve data from only one table. B. A SQL query statement cannot display data from table B that is referred to in its subquery, unless table B is included in the main query’s FROM clause. C. A SQL query statement can display data from table B that is referred to in its subquery,without including table B in its own FROM clause. D. A single row subquery can retrieve data from more than one table. E. A single row subquery cannot be used in a condition where the LIKE operator is used for comparison. F. A multiple-row subquery cannot be used in a condition where the LIKE operator is used for comparison.

Question 29

Which three functions can be used to manipulate character, number, or date column values? (Choose three.)

Answer 29

``` A. CONCAT B. ROUND C. TRUNC D. RPAD E. INSTR ```

Question 30

Which statement about a table is true?

Answer 30

A. A table can have up to 10,000 columns. B. The size of a table does NOT need to be specified. C. A table CANNOT be created while users are using the database. D. The structure of a table CANNOT be modified while the table is online.

Question 31

What will be returned from SIGN(ABS(NVL(-32,0)))?

Answer 31

``` A. 1 B. 32 C. 1 D. 0 E. NULL ```

Question 32

Which functions could you use to strip leading characters from a character string. Select two.

Answer 32

``` A. LTRIM B. SUBSTR C. RTRIM D. INSTR E. MOD ```

Question 33

All the operators are used in single row subquery except one

Answer 33

a) Between … and b) < > c) = d) in

Question 34

All the commands executes in iSQLplus except one

Answer 34

a) Column b) Compute c) define d) Accept

Question 35

Ascript file which will be executed automatically in iSQLPlus is

Answer 35

a) afiedt.buf b) login.sql c) both a and b d) none of the above.

Question 36

A command in iSQL plus is used to give the status of old and new value of variable

Answer 36

a) set feedback b) set verify c) set confirm d) none of the above

Question 37

All commands are used to save the changes of the transaction except one

Answer 37

a) Commit b) exitting from sqlplus c) DDL command d) savepoint e) none of the above

Question 38

A Clause which is used in joining two tables other than equality operator is

Answer 38

a) join b) on c) in d) using

Question 39

A Clause which is the pseudocolumn used to know the current value of the sequence

Answer 39

a) nextval b) current_val c) currval d) none of the above

Question 40

A Query which is used in top-N analysis is

Answer 40

a) subquery b) correlated subquery c) inline query d) outer query

Question 41

An operator is used to get and display the redundant records

Answer 41

a) Union all b) Distinct c) Union d) Intersect

Question 42

All the datatypes with respect to Oracle 9i is true except one

Answer 42

a) DATE b) TIMESTAMP c) TIMSTAMP with TIME ZONE d) TIMESTAMP WITH LOCAL TIME ZONE e) None of the above

Question 43

What is database?

Answer 43

A database is a logically collection of data with some inherent meaning.the data’s stored rows and collumns database are used to store the informations.

Question 44

To select records from the given row?

Answer 44

select * from SIVAEMP where rownum < =(select count(*)-&n from SIVAEMP);

Question 45

To check the leap year

Answer 45

select decode(mod(2004,4),0,’leap year’, ‘not a leap year’) from dual

Question 46

To delete duplicate records

Answer 46

delete from sivaemp where rowid not in(select max(rowid) from sivaemp group by empno)

Question 47

To keep latest single record

Answer 47

delete from sivaemp where rowid not in(select min(rowid) from sivaemp group by empno)//to keep oldest record

Question 48

To select second max salary

Answer 48

select * from sivaemp where salary = (select max(salary) from sivaemp where salary not in (select max(salary) from sivaemp))

Question 49

To select nth max salary

Answer 49

select * from sivaemp a where &n-1 = (select count(*) from sivaemp b where a.salary < b .salary) select * from sivaemp a where &n = (select count(*) from sivaemp b where a.salary < =b.salary)

Question 50

To select nth min salary

Answer 50

select * from sivaemp a where &n-1 = (select count(*) from sivaemp b where a.salary > b.salary) select * from sivaemp a where &n = (select count(*) from sivaemp b where a.salary > =b.salary)

Question 51

To select top(n) max salaries

Answer 51

select * from sivaemp a where &n > (select count(*) from sivaemp b where a.salary < b .salary)

Question 52

To select top(n) min salaries

Answer 52

select * from sivaemp a where &n > (select count(*) from sivaemp b where a.salary > b.salary)

Question 53

To select records from particular row(the remaining rows)

Answer 53

select * from sivaemp minus select * from sivaemp where rownum < = (select count(*)- &n-1 from sivaemp )

Question 54

to select first n rows

Answer 54

select * from sivaemp where rownum < = (select count(*) + &n -count(*) from sivaemp)

Question 55

Explain the difference between trigger and stored procedure.

Answer 55

Trigger in act which is performed automatically before or after a event occur when DML operations are occur Trigger is Fire. Stored procedure is a set of functionality which is executed when it is explicitly invoked.

Question 56

Differences between DATE and TIMESTAMP in Oracle

Answer 56

Date is used to store date and time values including month, day, year, century, hours, minutes and seconds. TimeStamp datatype stores everything that Date stores and additionally stores fractional seconds. Date: 16:05:14 Timestamp: 16:05:14:

Question 57

Create a copy of EMP table without any data?

Answer 57

CREATE TABLE EMP1 AS SELECT * FROM EMP WHERE‘CHIRU’=’VENKY’;

Question 58

Delete the 10th record of EMP table?

Answer 58

DELETE FROM EMP WHERE ENAME= (SELECT ENAME FROM EMP WHERE ROWNUM < =10 MINUS SELECT ENAME FROM EMP WHERE ROWNUM<10);

Question 59

Find all the departments which have more than 3 employees?

Answer 59

SELECT D.DNAME, COUNT (E.ENAME) FROM EMP E, DEPT D WHERE D.DEPTNO=E.DEPTNO GROUP BY DNAME HAVING COUNT (EMPNO) > 3;

Question 60

Display the manager who is having maximum number of employees working under him?

Answer 60

SELECT DISTINCT M.ENAME, COUNT (E.ENAME) FROM EMP E, EMP M WHERE E.MGR=M.EMPNO GROUP BY M.ENAME HAVING COUNT (E.ENAME) > =ALL (SELECT COUNT (E.ENAME) FROM EMP E, EMP M WHERE E.MGR=M.EMPNO GROUP BY M.ENAME);

Question 61

Display the name of employees who joined on the same date?

Answer 61

SELECT A.ENAME FROM EMP A, EMP B | WHERE A.EMPNO < > B.EMPNO AND A.HIREDATE=B.HIREDATE;

Question 62

Delete those employees who joined the company 10 years back from today?

Answer 62

SELECT ENAME FROM EMP | WHERE (SYSDATE-HIREDATE)/365 > 10

Question 63

Display those employees whose salary is ODD value?

Answer 63

``` SELECT ENAME, SAL FROM EMP WHERE MOD (SAL, 2) < > 0; ```

Question 64

Find out the number of employees whose salary is greater than their manager salary?

Answer 64

``` SELECT COUNT (E.ENAME) FROM EMP E, EMP M WHERE E.MGR=M.EMPNO AND E.SAL > M.SAL ```

Question 65

Select the count of employees in each dept where count is greater than 3?

Answer 65

SELECT JOB, COUNT (*) FROM EMP | GROUP BY JOB HAVING COUNT (*) > 3;

Question 66

Display name of those employees who are getting the highest salary?

Answer 66

SELECT * FROM EMP WHERE SAL IN (SELECT MAX (SAL) FROM EMP);

Question 67

What is a transaction ?

Answer 67

A transaction is a set of SQL statements between any two COMMIT and ROLLBACK statements.

Question 68

What is implicit cursor and how is it used by Oracle ?

Answer 68

An implicit cursor is user pass single line queries that time a cursor which is internally created by Oracle.It is created by Oracle for each individual SQL.

Question 69

Which of the following is not a schema object : Indexes, tables, public synonyms, triggers and packages ?

Answer 69

Public synonyms

Question 70

Is there a PL/SQL Engine in SQL*Plus?

Answer 70

SQL*Plus does not have a PL/SQL engine.Thus, all your PL/SQL are send directly to the database engine for execution.This makes it much more efficient as SQL statements are not stripped off and send to the database individually.

Question 71

Can one read/write files from PL/SQL?

Answer 71

Included in Oracle 7.3 is a UTL_FILE package that can read and write files.The directory you intend writing to has to be in your INIT. Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command. DECLARE fileHandler UTL_FILE.FILE_TYPE; BEGIN fileHandler := UTL_FILE.FOPEN(‘/home/oracle/tmp’, ‘myoutput’,’W’); UTL_FILE.PUTF(fileHandler, ‘Value of func1 is %sn’, func1(1)); UTL_FILE.FCLOSE(fileHandler); END;

Question 72

How can I protect my PL/SQL source code?

Answer 72

PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code.This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original).This way you can distribute software without having to worry about exposing your proprietary algorithms and methods.SQL*Plus and SQL*DBA will still understand and know how to execute such scripts.Just be careful, there is no “decode” command available. The syntax is: wrap name=myscript.sql oname=xxxx.yyy

Question 73

Can one use dynamic SQL within PL/SQL? OR Can you use a DDL in a procedure ? How ?

Answer 73

From PL/SQL V2.1 one can use the DBMS_SQL package to execute dynamic SQL statements. Eg: CREATE OR REPLACE PROCEDURE DYNSQL AS cur integer; rc integer; BEGIN cur := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(cur,’CREATE TABLE X (Y DATE)’, DBMS_SQL.NATIVE); rc := DBMS_SQL.EXECUTE(cur); DBMS_SQL.CLOSE_CURSOR(cur); END;

Question 74

What are the various types of parameter modes in a procedure ?

Answer 74

IN, OUT AND INOUT.

Question 75

What are the constructs of a procedure, function or a package ?

Answer 75

The constructs of a procedure, function or a package are :variables and constants cursors exceptions

Question 76

Why Create or Replace and not Drop and recreate procedures ?

Answer 76

So that Grants are not dropped.

Question 77

Can you pass parameters in packages ? How ?

Answer 77

Yes.You can pass parameters to procedures or functions in a package.

Question 78

What are the parts of a database trigger ?

Answer 78

A triggering event or statement A trigger restriction A trigger action

Question 79

What are the various types of database triggers ?

Answer 79

There are 12 types of triggers Insert, Delete and Update Triggers. Before and After Triggers. Row and Statement Triggers.

Question 80

What is the advantage of a stored procedure over a database trigger ?

Answer 80

We have control over the firing of a stored procedure but we have no control over the firing of a trigger.

Question 81

What are cascading triggers? What is the maximum no of cascading triggers at a time?

Answer 81

When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading.Max = 32.

Question 82

What are mutating triggers ?

Answer 82

A trigger giving a SELECT on the table on which the trigger is written.

Question 83

What are constraining triggers ?

Answer 83

A trigger giving an Insert/Updat e on a table having referential integrity constraint on the triggering table.

Question 84

Describe Oracle database's physical and logical structure ?

Answer 84

Physical : Data files, Redo Log files, Control file. | Logical : Tables, Views, Tablespaces, etc.

Question 85

Can you increase the size of a tablespace ? How ?

Answer 85

Yes, by adding datafiles to it.

Question 86

What is the use of Control files ?

Answer 86

Contains pointers to locations of various data files, redo log files, etc.

Question 87

What is the use of Data Dictionary ?

Answer 87

It Used by Oracle to store information about various physical and logical structures e.g.Tables

Question 88

What are the states of a rollback segment ? What is the difference between partly available and needs recovery ?

Answer 88

``` The various states of a rollback segment are : ONLINE OFFLINE PARTLY AVAILABLE NEEDS RECOVERY INVALID. ```

Question 89

What is the maximum no.of columns a table can have ?

Answer 89

254.

Question 90

Can you pass a parameter to a cursor ?

Answer 90

Explicit cursors can take parameters,User create cursor called Explicit Cursor CURSOR c1 (median IN NUMBER) IS SELECT job, ename FROM emp WHERE sal > median;

Question 91

What are the various types of RollBack Segments ?

Answer 91

The types of Rollback segments are as follows : Public Available to all instances Private Available to specific instance

Question 92

What are the disadvantages of SQL ?

Answer 92

``` Cannot drop a field Cannot rename a field Cannot manage memory Procedural Language option not provided Index on view or index on index not provided View updation problem ```

Question 93

When to create indexes ?

Answer 93

To be created when table is queried for less than 2% or 4% to 25% of the table rows.

Question 94

How can you avoid indexes ?

Answer 94

To make index access path unavailable Use FULL hint to optimizer for full table scan Use INDEX or AND-EQUAL hint to optimizer to use one index or set to indexes instead of another

Question 95

What is the difference between a view and a synonym ?

Answer 95

Synonym is Schema Object,just a second name of table,.View can be created with many tables, and with virtual columns and with conditions.

Question 96

What is the difference between foreign key and reference key ?

Answer 96

Foreign key is attribute which refers to another table primary key. Reference key is the primary key of table referred by another table.

Question 97

What is a package cursor ?

Answer 97

a cursor declare in the package specification without an SQL statement. The SQL statement for the cursor is attached at runtime from calling procedures.

Question 98

What are snap shots and views

Answer 98

Snapshots are mirror or replicas of tables. Views are built using the columns from one or more tables.