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

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

An index is uset to performace tunnig in queries. which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

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.

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.

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

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

%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 tables or views and variables.

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

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.

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.

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

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.

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

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

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.

Subquery is a query embedded with another Query
Outer Query based on the Inner Query

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

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.

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.

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.

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.

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.

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

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

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

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.

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

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.

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

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

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

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

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

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

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

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

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

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

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

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

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.

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

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

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

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

To select second max salary

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

To select nth max salary 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)

To select nth min salary 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)

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

To select top(n) min salaries select * from sivaemp a where &n > (select count(*) from sivaemp b where a.salary > b.salary)

To select records from particular row(the remaining rows) select * from sivaemp minus select * from sivaemp where rownum < = (select count(*)- &n-1 from sivaemp )

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

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.

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:

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

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

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);

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

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

Display those employees whose salary is ODD value? SELECT ENAME, SAL FROM EMP
WHERE MOD (SAL, 2) < > 0;

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

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

Display name of those employees who are getting the highest salary? SELECT * FROM EMP WHERE SAL IN (SELECT MAX (SAL) FROM EMP);

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

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.

Indexes, tables, public synonyms, triggers and packages ?

Public synonyms

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.

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;

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

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;

IN, OUT AND INOUT.

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

So that Grants are not dropped.

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

A triggering event or statement
A trigger restriction
A trigger action

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

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

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

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

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

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

Yes, by adding datafiles to it.

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

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

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

254.

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;

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

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

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

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

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

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

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

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