How to display alternative rows in a table ?

SQL> select *from emp where (rowid, 0) in (select rowid,mod(rownum,2) from emp);

How to find the Top sal ?

Select * from EMP where sal= (select max (sal) from EMP);

How to find 2 nd highest Sal ?

Select empno, ename, sal, r from (select empno, ename, sal, dense_rank () over (order by sal desc) r from EMP) where r=2;

How to find the Dense rank ?

The DENSE_RANK function works acts like the RANK function except that it assigns consecutive ranks:

Select empno, ename, Sal, from (select empno, ename, sal, dense_rank () over (order by sal desc) r from emp);

How to find Top 5 salaries ?

Select empno, ename, sal,r from (select empno,ename,sal,dense_rank() over (order by sal desc) r from emp) where r<=5;
OR
Select * from (select * from EMP order by sal desc) where rownum<=5;

A query to assign the Ranks

Select empno, ename, sal, r from (select empno, ename, sal, rank () over (order by sal desc) r from EMP);

Write the query to transpose rows into columns.

select

emp_id,

max(decode(row_id,0,address))as address1,

max(decode(row_id,1,address)) as address2,

max(decode(row_id,2,address)) as address3

from (select emp_id,address,mod(rownum,3) row_id from temp order by emp_id )

group by emp_id

Other query:

select

emp_id,

max(decode(rank_id,1,address)) as add1,

max(decode(rank_id,2,address)) as add2,

max(decode(rank_id,3,address))as add3

from

(select emp_id,address,rank() over (partition by emp_id order by emp_id,address )rank_id from temp )

group by

emp_id

How to remove duplicates in the table ?

Delete from EMP where rowid not in (select max (rowid) from EMP group by empno);

How to get duplicate rows from the table ?

Select empno, count (*) from EMP group by empno having count (*)>1;

What are theTypes of Triggers ?

This section describes the different types of triggers:

• Row Triggers and Statement Triggers
• BEFORE and AFTER Triggers
• INSTEAD OF Triggers
• Triggers on System Events and User Events

Row Triggers

A row trigger is fired each time the table is affected by the triggering statement. For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement. If a triggering statement affects no rows, a row trigger is not run.

BEFORE and AFTER Triggers

When defining a trigger, you can specify the trigger timing--whether the trigger action is to be run before or after the triggering statement. BEFORE and AFTER apply to both statement and row triggers.

BEFORE and AFTER triggers fired by DML statements can be defined only on tables, not on views.

Difference between Trigger and Procedure

Triggers	Stored Procedures
In trigger no need to execute manually. Triggers will be fired automatically. Triggers that run implicitly when an INSERT, UPDATE, or DELETE statement is issued against the associated table.	Where as in procedure we need to execute manually.

Differences between stored procedure and functions

Stored Procedure	Functions
Stored procedure may or may not return values.	Function should return at least one output parameter. Can return more than one parameter using OUT argument.
Stored procedure can be used to solve the business logic.	Function can be used to calculations
Stored procedure is a pre-compiled statement.	But function is not a pre-compiled statement.
Stored procedure accepts more than one argument.	Whereas function does not accept arguments.
Stored procedures are mainly used to process the tasks.	Functions are mainly used to compute values
Cannot be invoked from SQL statements. E.g. SELECT	Can be invoked form SQL statements e.g. SELECT
Can affect the state of database using commit.	Cannot affect the state of database.
Stored as a pseudo-code in database i.e. compiled form.	Parsed and compiled at runtime.

Explain about Triggers ?

Oracle lets you define procedures called triggers that run implicitly when an INSERT, UPDATE, or DELETE statement is issued against the associated table

Triggers are similar to stored procedures. A trigger stored in the database can include SQL and PL/SQL

what is ment by Packages ?

Packages provide a method of encapsulating related procedures, functions, and associated cursors and variables together as a unit in the database.

package that contains several procedures and functions that process related to same transactions.

A package is a group of related procedures and functions, together with the cursors and variables they use, Packages provide a method of encapsulating related procedures, functions, and associated cursors and variables together as a unit in the database.

What are the differences between stored procedures and triggers ?

Stored procedure normally used for performing tasks But the Trigger normally used for tracing and auditing logs.

Stored procedures should be called explicitly by the user in order to execute But the Trigger should be called implicitly based on the events defined in the table.

Stored Procedure can run independently But the Trigger should be part of any DML events on the table.

Stored procedure can be executed from the Trigger  But the Trigger cannot be executed from the Stored procedures.

Stored Procedures can have parameters.But the Trigger cannot have any parameters.

Stored procedures are compiled collection of programs or SQL statements  in the database.

Using  stored procedure  we can access and modify data present in many tables. Also a stored procedure is not associated with any particular database object. 

But triggers are event-driven special procedures which are attached to a specific database object say a table. 

Stored procedures are not automatically run and they have to be called explicitly by the user. But triggers get executed when the particular event associated with the event gets fired.

What is your tuning approach if SQL query taking long time? Or how do u tune SQL query ?

If query taking long time then First will run the query in Explain Plan, The explain plan process stores data in the PLAN_TABLE.

 it will give us execution plan of the query like whether the query is using the relevant indexes on the joining columns or indexes to support the query are missing.

If joining columns doesn’t have index then it will do the full table scan if it is full table scan the cost will be more then will create the indexes on the joining columns and will run the query  it should give  better performance and also  needs to analyze the tables if analyzation happened long back. The ANALYZE statement can be used to gather statistics for a specific table, index or cluster using

ANALYZE TABLE employees COMPUTE STATISTICS;

If still have performance issue then will use HINTS, hint is nothing but a clue. We can use hints like

• ALL_ROWS
  One of the hints that 'invokes' the Cost based optimizer
  ALL_ROWS is usually used for batch processing or data warehousing systems.

(/*+ ALL_ROWS */) 

• FIRST_ROWS
  One of the hints that 'invokes' the Cost based optimizer
  FIRST_ROWS is usually used for OLTP systems.

(/*+ FIRST_ROWS */) 

• CHOOSE
  One of the hints that 'invokes' the Cost based optimizer
  This hint lets the server choose (between ALL_ROWS and FIRST_ROWS, based on statistics gathered.
• HASH
  Hashes one table (full scan) and creates a hash index for that table. Then hashes other table and uses hash index to find corresponding records. Therefore not suitable for < or > join conditions.

/*+ use_hash */

Hints are most useful to optimize the query performance.

Explain Plan ?

Explain plan will tell us whether the query properly using indexes or not.whatis the cost of the table whether it is doing full table scan or not, based on these statistics we can tune the query.

The explain plan process stores data in the PLAN_TABLE. This table can be located in the current schema or a shared schema and is created using in SQL*Plus as follows:

SQL> CONN sys/password AS SYSDBA

Connected

SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql

SQL> GRANT ALL ON sys.plan_table TO public;

SQL> CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;

Why hints Require ?

It is a perfect valid question to ask why hints should be used. Oracle comes with an optimizer that promises to optimize a query's execution plan. When this optimizer is really doing a good job, no hints should be required at all.

Sometimes, however, the characteristics of the data in the database are changing rapidly, so that the optimizer (or more accuratly, its statistics) are out of date. In this case, a hint could help.

You should first get the explain plan of your SQL and determine what changes can be done to make the code operate without using hints if possible. However, hints such as ORDERED, LEADING, INDEX, FULL, and the various AJ and SJ hints can take a wild optimizer and give you optimal performance Tables analyze and update Analyze Statement.

The ANALYZE statement can be used to gather statistics for a specific table, index or cluster. The statistics can be computed exactly, or estimated based on a specific number of rows, or a percentage of rows:

ANALYZE TABLE employees COMPUTE STATISTICS;

ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 15 PERCENT;

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');

Automatic Optimizer Statistics Collection

By default Oracle 10g automatically gathers optimizer statistics using a scheduled job called GATHER_STATS_JOB. By default this job runs within maintenance windows between 10 P.M. to 6 A.M. week nights and all day on weekends. The job calls the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC internal procedure which gathers statistics for tables with either empty or stale statistics, similar to the DBMS_STATS.GATHER_DATABASE_STATS procedure using the GATHER AUTO option. The main difference is that the internal job prioritizes the work such that tables most urgently requiring statistics updates are processed first.

Hint categories:

Hints can be categorized as follows:

• ALL_ROWS
  One of the hints that 'invokes' the Cost based optimizer
  ALL_ROWS is usually used for batch processing or data warehousing systems.

(/*+ ALL_ROWS */) 

• FIRST_ROWS
  One of the hints that 'invokes' the Cost based optimizer
  FIRST_ROWS is usually used for OLTP systems.

(/*+ FIRST_ROWS */) 

• CHOOSE
  One of the hints that 'invokes' the Cost based optimizer
  This hint lets the server choose (between ALL_ROWS and FIRST_ROWS, based on statistics gathered.

• Hints for Join Orders,
• Hints for Join Operations,
• Hints for Parallel Execution, (/*+ parallel(a,4) */)  specify degree either 2 or 4 or 16
• Additional Hints
• HASH
  Hashes one table (full scan) and creates a hash index for that table. Then hashes other table and uses hash index to find corresponding records. Therefore not suitable for < or > join conditions.

/*+ use_hash */

Use Hint to force using index

SELECT /*+INDEX (TABLE_NAME INDEX_NAME) */ COL1,COL2 FROM TABLE_NAME

 Select  ( /*+ hash  */ ) empno from

ORDERED-à This hint forces tables to be joined in the order specified. If you know table X has fewer rows, then ordering it first may speed execution in a join.

PARALLEL (table, instances)àThis specifies the operation is to be done in parallel.

If index is not able to create then will go for  /*+ parallel(table, 8)*/-----For select and update example---in where clase like st,not in ,>,< ,<> then we will use.

What is Ment By Indexes ?

1.      Bitmap indexes are most appropriate for columns having low distinct values—such as GENDER, MARITAL_STATUS, and RELATION. This assumption is not completely accurate, however. In reality, a bitmap index is always advisable for systems in which data is not frequently updated by many concurrent systems. In fact, as I'll demonstrate here, a bitmap index on a column with 100-percent unique values (a column candidate for primary key) is as efficient as a B-tree index.

2.      When to Create an Index

3.      You should create an index if:

4.       A column contains a wide range of values

5.      A column contains a large number of null values

6.      One or more columns are frequently used together in a WHERE clause or a join condition

7.      The table is large and most queries are expected to retrieve less than 2 to 4 percent of the rows

By default if u create index that is nothing but b-tree index.

What is the difference between sub-query & co-related sub query ?

A sub query is executed once for the parent statement whereas the correlated sub query is executed once for each row of the parent query.

Sub Query:

Example:

 Select deptno, ename, sal from emp a  where sal  in (select sal from Grade  where sal_grade=’A’ or  sal_grade=’B’)

Co-Related Sun query:

Example:

Find all employees who earn more than the average salary in their department.

SELECT last-named, salary, department_id  FROM employees A

WHERE salary > (SELECT AVG (salary)

FROM employees B WHERE B.department_id =A.department_id

Group by B.department_id)

EXISTS:

The EXISTS operator tests for existence of rows in

the results set of the subquery.

Select dname from dept where exists (select 1 from EMP where dept.deptno= emp.deptno);

Sub-query	Co-related sub-query
A sub-query is executed once for the parent Query	Where as co-related sub-query is executed once for each row of the parent query.
Example: Select * from emp where deptno in (select deptno from dept);	Example: Select a.* from emp e where sal >= (select avg(sal) from emp a where a.deptno=e.deptno group by  a.deptno);

Explain MERGE Statement in SQL ?

You can use merge command to perform insert and update in a single command.

Ex: Merge into student1 s1

        Using (select * from student2) s2

        On (s1.no=s2.no)

        When matched then

        Update set marks = s2.marks

        When not matched then

        Insert (s1.no, s1.name, s1.marks)   Values (s2.no, s2.name, s2.marks);

Differences between where clause and having clause ?

Where clause	Having clause
Both where and having clause can be used to filter the data.
Where as in where clause it is not mandatory.	But having clause we need to use it with the group by.
Where clause applies to the individual rows.	Where as having clause is used to test some condition on the group rather than on individual rows.
Where clause is used to restrict rows.	But having clause is used to restrict groups.
Restrict normal query by where	Restrict group by function by having
In where clause every record is filtered based on where.	In having clause it is with aggregate records (group by functions).