oracle tutorial

Oracle-9

2008-01-07T06:55:00.001+05:30

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<=(SELECT COUNT(*) from
customer C2 WHERE C1.payment <= C2.payment)

What is the purpose of a cluster ?
Oracle does not allow a user to specifically locate tables, since that is a part of the function
of the RDBMS. However, for the purpose of increasing performance, oracle allows a developer
to create a CLUSTER. A CLUSTER provides a means for storing data from different tables
together for faster retrieval than if the table placement were left to the RDBMS.

What is a cursor ?
Oracle uses work area to execute SQL statements and store processing information PL/SQL
construct called a cursor lets you name a work area and access its stored information A cursor is a
mechanism used to fetch more than one row in a PL/SQL block.
Difference between an implicit & an explicit cursor ?
PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including
quries that return only one row. However,queries that return more than one row you must declare
an explicit cursor or use a cursor FOR loop.Explicit cursor is a cursor in which the cursor name is explicitly assigned to a SELECT
statement via the CURSOR...IS statement. An implicit cursor is used for all SQL statements
Declare, Open, Fetch, Close. An explicit cursors are used to process multirow SELECT statements
An implicit cursor is used to process INSERT, UPDATE, DELETE and single row SELECT.
.INTO statements.

oracle 8

2007-08-07T15:07:00.000+05:30

Questions and Answers :

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 (2
nd
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.

Oracle 7

2007-06-11T12:14:00.000+05:30

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<1 .................. end loop;
while ..... loop.................end loop;
Data Types : number, integer, varchar2, char, date, Boolean;
goto procedure name;..............<>..............
exit, rollback, commit, savepoint.
References: This keyword is used to refer two similar fields of different tables.

What are different built in functions ?
CONCATENATE, INITCAP, LENGTH, LOWER, UPPER, LPAD, RPAD, LTRIM
(removes the character specified left side of word), RTRIM, SUBSTR (displays values from
specified location onwards), TRANSLATE (for single character), REPLACE (for more than one
character ),CHR ( prints character of given ASCII value), ASCII (value of given character ).
ABS, CEIL, FLOOR, SQRT, POWER, SIGN, TRUNC, ROUND, EXP, MOD, LOG, LN,
GREATEST, LEAST, SYSDATE, ADD_MONTHS, MONTHS_BETWEEN.

what are emp.sal%type and emp%rowtype ?
First one is to assign a variable of type sal only.
Second is to assign a complete row of type emp table.

What are constraints ?
Constraint Clause : This will constrain a single column or a group of columns in a table.
and are used for maintaining integrity of the database.
Different Constraints are : candidate keys, primary keys, foreign keys, check conditions.
Can specify as a part of column definition, or at the end of the table (if more than one column).
Check Constraint : for validations to fields.
Naming Constraint (table constraints): Can name the constraint else system will name it
by default.

Different types of joins ?
Equi join : where emp.deptno = dept.deptno;
Non Equi join : where e.sal >= 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.

Oracle 6

2007-04-18T12:48:00.000+05:30

INTRODUCTION
According to Elmasri and Navathe (1994), Dr. E. F. Codd, the originator of the relational
data model, published a two-part article in ComputerWorld (Codd, 1985) that lists 12 rules for
how to determine whether a DBMS is relational and to what extent it is relational. These rules
provide a very useful yardstick for evaluating a relational system. Codd also mentions that,according to these rules, no fully relational system is available yet. In particular, rules 6, 9, 10, 11,
and 12 are difficult to satisfy.

THE 12 RULES

Rule 1: The Information Rule
All information in a relational database is represented explicitly at the logical level in
exactly one way by values in tables.
All data is represented in the form of relations/tables (with rows and columns)Simple
Consistent and versatile

Rule 2: Guaranteed Access Rule
Each and every datum (atomic value) in a relational database is guaranteed to be logically
accessible by resorting to a table name, primary key value, and column name. All Data is uniquely
identified and accessible via this identity.

Rule 3: Systematic Treatment of Null Values
Null values (distinct from empty character string or a string of blank characters and distinct
from zero or any other number) are supported in the fully relational DBMS for representing
missing information in a systematic way, independent of data type.

Rule 4: Dynamic On-line Catalog based on the Relational Model
The database description is represented at the logical level in the same way as ordinary
data, so authorized users can apply the same relational language to its interrogation as they apply
to regular data.

Rule 5: Comprehensive Data Sublanguage Rule
A relational system may support several languages and various modes of terminal use (for
example, the fill-in-blanks mode). However, there must be at least one language whose statements
are expressible, per some well-defined syntax, as character strings and whose ability to support all
of the following is comprehensible: data definition, view definition, data manipulation (interactive
and by program), integrity constraints, and transaction boundaries (begin, commit, and rollback).

Rule 6: View Updating Rule
All views that are theoretically updateable are also updateable by the system.
Views are virtual tables. They appear to behave as conventional tables except that they are built
dynamically when the query is run. This means that a view is always up to date. It is not always
theoretically possible to update views. One problem exists when a view relates to part of a table
not including a candidate key. This means that potential updates would violate the entity integrity
rule.

Rule 7: High-level Insert, Update, and Delete
The capability of handling a base relation or a derived relation as a single operand applies
not only to the retrieval of data but also to the insertion, update, and deletion of data. i.e. The user
should be allowed to Delete, Update a set of tuples (rather than row by row)

Rule 8: Physical Data Independence
Application programs and terminal activities remain logically unimpaired whenever any
changes are made in either storage representation or access methods. i.e. Users and programs are
not dependent on the physical structure of the database.

Rule 9: Logical Data Independence
Application programs and terminal activities remain logically unimpaired when
information-preserving changes of any kind that theoretically permit unimpairment are made to
the base tables. Users and Programs are independent of the logical structure of the database, i.e.
the logical structure of the data can evolve with minimal impact on the programs.

Rule 10: Integrity Independence
Integrity constraints specific to a particular relational database must be definable in the
relational data sublanguage and storable in the catalog, not in the application programs. A
minimum of the following two integrity constraints must be supported:
1. Entity integrity: No component of a primary key is allowed to have a null value.
2. Referential integrity: For each distinct non-null foreign key value in a relational database,
there must exist a matching primary key value from the same domain.

Rule 11: Distribution Independence
A relational DBMS has distribution independence. Distribution independence implies that
users should not have to be aware of whether a database is distributed. Applications should still
work in a distributed database (DDB).

Rule 12: Nonsubversion Rule
If a relational system has a low-level (single-record-at-a-time) language, that low-level
language cannot be used to subvert or bypass the integrity rules or constraints expressed in the
higher-level (multiple-records-at-a-time) relational language.
If there is a record at a time interface (e.g. via 3GL), Security and integrity of the database
must no be violated. There should be no backdoor to bypass the security imposed by the DBMS.

On the basis of the above rules, there is no fully relational DBMS available today.

Oracle 5

2007-04-11T19:13:00.000+05:30

NORMALIZATION
Normalization is a process of simplifying the relationship between data elements in a record. Through Normalization a collection of data in a record structure is replaced by successive record structures that are simpler and more predictable and therefore more manageable. It is process of reducing redundancy and eliminating the anomalies. (anamali means inconsistent state.)
Normalization is carried out for four reasons: · To Structure the data so that any pertinent relationships between entities can be represented. · To permit simple retrieval of data in response to query and report requests. · To simplify the maintenance of the data through updates, insertions, and deletion · To reduce the need to restructure or reorganize data when new application requirements arise. · Normalization can improve the quality of design for an application.
There are totally Five Normal Forms(Rules) out of which, any database should necessarily satisfy three of them, those Three Normal Forms are as follows:
First normal form is achieved when all repeating groups are removed so that a record is of fixed length. A repeating group, the re-occurrence of a data item within a record, is actually another relation. Hence, it is removed from the record and treated as an additional record structure, or relation.
Second Normal form should be in first normal form and no partial dependency exists. Means if we have a composite primary key(combination of 2 or more fields) then all the non keys must depend on the composite primary key. no attribute should depend upon the part of key.(for example if Rollno and SSno is a composite key, then no other field in that particular table should be depedent upon either Rollno or ssno individually)
Third Normal form is achieved when Transitive Dependencies are removed from a record design. The general case is as follows : A,B and C are three data items in a record. If C is functionally dependent on B (2nd N.F) and B is functionally dependent on A Then C is functionally dependent on A Therefore, a Transitive Dependency exists. In data management, transitive dependency is a concern because data can inadvertently be lost when the relationship is hidden. In the general case above, if A is deleted, then B and C are deleted also, whether or not this is intended. This problem is eliminated by designing the record for third normal form. Conversion to third normal form removes the transitive dependency by splitting the relation into two separate relations.
BCNF must be 3NF and every determinant is a candidate key. Candidate key is other than primary key, which uniquely identify each row in the table. for example ssno is PK then rollno becomes Candidate key.
4NF and 5NF are depends on instance. we can not give a proper def. bcz it all depends on joins. "CODD's 12 RULES" for a fully Relational DBMS

Oracle 4

2007-02-22T09:57:00.001+05:30

WHY ORACLE ? Oracle is an open system i.e. the database is accessing by different data access languages like SQL, Visual Basic, Power Builders,Delphi,VC++,JAVA etc. Oracle supports database upto gigabytes in size. * Oracle supports large number of concurrent users. * Oracle supports true client/server environment.if enable processing to be spilt between database server and the client application programs. * Oracle provides high levelof data security in terms of users, passwords privileges, and permissions. * Oracle database behaves same on all platform line Windows,Unix,Dos,Mainframes etc.
Structure of Oracle Database:
physical structure: one or more data files, Two of more log files, One control file. Logical structure: Table spaces, Segments, Extents, Data blocks.
The data files contain all user data in terms of tables, index, and views. The log files contain the information to open and be recovered, of Undone after a transaction(Rollback).
The control file physical data, media information to open and manage data files .If the control file is damaged the server will not be able to open are use the database even if
the database is undamaged.
What are the Back ground processes in Oracle and what are they. ? There are basically 9 Processes but in a general system we need to mention the first five background processes. They do the house keeping activities for the Oracle and are common
in any system. a) Data Base Writer(DBWR) :: Data Base Writer Writes Modified blocks from Database buffer cache to Data Files.This is required since the data is not written whenever a transaction is commited. b)LogWriter(LGWR) :: LogWriter writes the redo log entries to disk. Redo Log data is generated in redo log
buffer of SGA. As transaction commits and log buffer fills, LGWR writes log entries into a online
redo log file. c) System Monitor(SMON) :: The System Monitor performs instance recovery at instance startup. This is useful for recovery from system failure. d)Process Monitor(PMON) :: The Process Monitor peforms process recovery when user Process fails. Pmon Clears and Frees resources that process was using. e) CheckPoint(CKPT) :: At Specified times, all modified database buffers in SGA are written to data files by DBWR at Checkpoints and Updating all data files and control files of database to indicate
the most recent checkpoint. f)Archieves(ARCH) :: The Archiver copies online redo log files to archival storal when they are busy. g) Recoveror(RECO) :: The Recoveror is used to resolve the distributed transaction in network h) Dispatcher (Dnnn) :: The Dispatcher is useful in Multi Threaded Architecture i) Lckn :: We can have upto 10 lock processes for inter instance locking in parallel sql.

Oracle 3

2007-02-22T09:55:00.000+05:30

The Oracle Environment Oracle is a modular system that consists of the Oracle database and several functional programs. Oracle's tools do four major things of work. 1. Database Management. 2. Data access and Manipulation. 3. Programming 4. Connectivity.
Database Management Tools
This (is usually known as RDBMS by oracle) includes the core programs for Oracle's database management system, the Oracle database with its associated tables and views, which are stores information related to every fact of the database system.User name, user access rights, table attribute, storage information and auditing data for disaster recovery are stored in the data dictionary.
The Database Administrator
The person responsible for a Database is called the DatabaseAdministrator(DBA).The DBA has a special set of previleges and has complete over the database.Any system with more than one user should have a DBA.The function of DBA include. Creating primary database storage structures. Modifying the structure of the database. Backing up and restoring the database.
Monitoring database performance and efficiency. Transfering data between database and external files. Manipulating the phisical location of the database.
THE END USER End user have less complex but equally important tasks.They must be able to · Query the database. · Generate printed output. · Share data with or secure data from others. · Use non-export methods to update the database. · Define applications from a user point of view.

Oracle 2

2007-02-12T11:07:00.001+05:30

Oracle the Right Tool To keep track of records is often the best reason to consider buying a software package.Choosing a proven product increase the chance that we will get a system capable of meeting all our needs .Oracle has had many years in the relational market to often on the large,Complex applications. Although Oracle demands greater expertise on the part of the application developed,an application developed oracle will be able to keep pace with growth and change in the world at large.
Oracle Gives You Security and Control
Oracle has several features that ensure the integrity of the data base. If an interruption occurs in processing, a Rollback can reset the database to a point before the disaster. If a restore is necessary, Oracle has a Roll forward command for recreating your database to its mast resent save point.
Oracle provides user with several functions for securing data. Grant and Revoke commands limit access to information down to the row and column levels. Views are valuable feature for limiting access to the primary tables in the database.

Introduction to Oracle

2007-02-12T11:04:00.000+05:30

Introduction to Oracle. Oracle is a comprehensive operating environment that packs the power of mainframe relation database management system into user's micro computer. It provides a set of functional programs that user can use as tools to build structures and perform tasks. Because applications are developed on oracle are completely portable to the other versions of the programmer can create a complex application in a single user, environment and them move it to a multi user platform.Users do not have to be an expert to appriciate Oracle but the better user understand the program,the more productively and creatively he can use the tools it provides Relational Database Management system A Relational Database Management System (RDBMS) can perform a wide range of tasks. It acts as a transparent interface between the physical storage and the logical presentation of data.It provides a set of more or less flexible and sofesticated tools for handling information. Users can use the tools to
· Define a Database
· Query the Database
· Add,Edit and Delete data.
· Modify the structure of the Database.
· Communicate with in the Networks.
· Exports and Imports the data.
· Security.

welcome to oracle tutorial

2007-01-29T10:23:00.000+05:30

welcome to oracle tutorial