ORACLE INTERVIEW QUESTIONS AND ANSWERS

ORACLE Objects?

tables, views, sequences, stored procedures, synonyms, indexes, materialized view etc.

Dual?

is an Oracle built in Sudo table. It contains only one column and no records.

SELECT SYSDATE from dual;  –This query will fetch current system date.

Select 1 from dual; –This query will fetch 1.

Select ‘Mahesh’ from dual; –This query will fetch Mahesh.

Primary Key?

Primary key is a single field or combination of fields that uniquely defines a record. None of the fields that are part of the primary key can contain a null value. A table can have only one primary key.

A primary key can not contain more than 32 columns.

UNIQUE Constraint ?

A unique constraint is a single field or combination of fields that uniquely defines a record. Some of the fields can contain null values as long as the combination of values is unique.

Difference between primary key and unique constraint

– A Table can have only one primary key, but there can be multiple unique constraint in one table.

– None of the fields that are part of the primary key can contain a null value but in Unique constraint some of the fields can contain null values as long as the combination of values is unique

Foreign Key 

A foreign key means that values in one table must also appear in another table. The referenced table is called the parent table while the table with the foreign key is called the child table.

Check constraint?

It is used to specify a condition on each row in a table

How to Create table

CREATE TABLE Employee

(

Emp_id Number,

Emp_name Varchar2(200),

Salary Number

);

How to create table from some other table

CREATE TABLE New_Employee_Table AS

(SELECT * FROM Old_Employee_Table);

How to Insert Into Table

INSERT INTO Employee values(100,’Mahesh’,1000);

INSERT INTO Employee values(100,Mukesh,3000);

INSERT INTO Employee values(200,’Megha’,8000);

INSERT INTO Employee values(300,’Megha’,500);

How to insert into table from some other table

INSERT INTO Employee

Select * from Old_Employee_Table;  –This will insert all records of Old_Employee_Table into Employee

If you wanted to Insert the employee who are having salary = 2000 then query will be as mentioned below:

INSERT INTO Employee

SELECT * from Old_Employee_Table

WHERE salary = 2000;

SELECT Clause

In Select clause, we mention the column names of the table mentioned in From clause.

FROM Clause

In From clause, we mention the name of the tables from which we wanted to fetch data.

WHERE Clause

In where clause, we defines the filters/conditions.

SELECT salary

FROM employee

WHERE name = ‘Megha’

AND emp_id = 2000

;

ORDER BY Clause

It is used to sort the result set.

Employee(emp_id, emp_name, salary) table is having records as

(101, Megha,2000)

(99, Mahesh, 3000)

(200,Arjun,9000)

(500,Nakul,3000)

(400,Nakul,1000)

If I wanted to fetch the emp_name and salary and result should be sorted by salary in ascending order then

SELECT  emp_name, salary

FROM employee

Order by salary;

The result will be

(Nakul,1000)

(Megha,2000)

(Nakul,3000)

(Mahesh, 3000)

(Arjun,9000)

If I want that result should be sorted by salary in Descending order then

SELECT  emp_name, salary

FROM employee

Order by salary DESC;

The result will be

(Arjun,9000)

(Nakul,3000)

(Mahesh, 3000)

(Megha,2000)

(Nakul,1000)

IN Condition

If I wanted to fetch name and salary of all employee having salary as 1000, 2000 then

SELECT emp_name, salary

FROM employee

WHERE salary IN (1000, 2000) ;

IS NULL and IS NOT NULL?

Suppose there is table employee(emp_id, emp_name, salary, technology_worked) having data as

(Null means No Value)

(100, Megha, 2000, ‘Java’);

(300, Mahesh, 2000, ‘Oracle’);

(500, Arjun, 4000, null);

If I wanted to fetch all employees not working on any technology then the query will be

SELECT *

FROM Employee

WHERE technology_worked IS NULL;

 

Result will be

(500, Arjun, 4000, null);

If I wanted to fetch all employees working on some technology then the query will be

SELECT *

FROM Employee

WHERE technology_worked IS NOT NULL;

Result will be

(100, Megha, 2000, ‘Java’);

(300, Mahesh, 2000, ‘Oracle’);

 

Between?

If I wanted to fetch all employee having salary between 5000 to 10000 then

SELECT *

FROM Employee

WHERE salary BETWEEN 5000 AND 10000;

View?

View is a named select query that is compiled and stored in Database. View doesn’t physically store data. When we use the view in our program, this compiled query executes and gives the result.

Why we use View?

Suppose there is a query to fetch Maximum Salary of the employee and it is a complex query. If we wanted to fetch Maximum Salary at many places then we will create View on this query and will use this view everywhere.

Example:

CREATE VIEW view_max_salary

SELECT MAX(salary) as max_sal FROM Employee;

If at some place I wanted to fetch the name of the employee having  highest salary then I can use this view as mentioned below:

Select emp_name FROM Employee

WHERE salary = (Select max_sal FROM view_max_salary);

UNION Operator?

Union is used to combine the result of two queries. It doesn’t show duplicates.

Suppose 1st query fetch (Megha, 2000), (Mahesh, 4000) and second query also fetch (Megha, 2000) then in final result there will be one record as (Megha, 2000) and another record as (Mahesh, 4000).

Example: Suppose I want to fetch name and salary of all employees having salary greater than 2000. Also I wanted to fetch name and salary of all employees having employee name = Megha

SELECT emp_name, salary

FROM Employee

WHERE salary > 2000

UNION

SELECT emp_name, salary

FROM Employee

WHERE emp_name = ‘Megha’ ;

UNION ALL Operator?

Union ALL is used to combine the result of two queries. It shows duplicates as well.

Suppose 1st query fetch (Megha, 2000), (Mahesh, 4000) and second query also fetch (Megha, 2000) then in final result there will be only Three record as (Megha, 2000), (Megha, 2000), (Mahesh, 4000).

Example: Suppose I want to fetch name and salary of all employees having salary greater than 2000. Also I wanted to fetch name and salary of all employees having employee name = Megha

SELECT emp_name, salary

FROM Employee

WHERE salary > 2000

UNION ALL

SELECT emp_name, salary

FROM Employee

WHERE emp_name = ‘Megha’ ;

Note: UNION ALL is faster than UNION

INTERSECT?

INTERSECT is used to fetch the records common in two queries.

Suppose 1st query fetch (Megha, 2000), (Mahesh, 4000) and second query also fetch (Megha, 2000) then in final result there will be only ONE record as (Megha, 2000).

SELECT emp_name, salary

FROM Employee

WHERE salary > 2000

INTERSECT

SELECT emp_name, salary

FROM Employee

WHERE emp_name = ‘Megha’ ;

DISTINCT Clause?

It is used to fetch distinct records.

Suppose in employee(emp_id, emp_name, salary) table records are

(1, Megha, 2000)

(2, Mahesh, 3000)

(3, Megha, 5000)

The query

Select emp_name FROM employee;

Will fetch Megha, Mahesh, Megha

If I wanted to fetch Distinct records (Distinct emp_name) then I will use this Distinct clause as mentioned below:

SELECT DISTINCT emp_name FROM employee;

It will fetch Megha, Mahesh

CURSOR?

Cursor acts as pointer to the result returned from Select statement. We can use this cursor to retrieve the rows from this result set.

Cursor is of two types: Implicit Cursor and Explicit Cursor

Implicit cursor: This cursor is created by Oracle system itself when we are performing any SELECT INTO, Insert, Update, Delete operation.

Explicit cursor: These cursor are created by Programmer.

Question: Write a program that will Increase 10% salary of all employees.

Table:

Employee (

Emp_id Number,

Emp_name Varchar2,

Salary Number

);

CREATE OR REPLACE PROCEDURE salary_revision    —

AS

CURSOR c_emp_sal IS – Step 1- Here we are declaring cursor

SELECT emp_id, emp_sal

FROM Employee ;

E_id     Employee.emp_id%type;;

E_sal   Employee.salary%type;

E_revised_sal  Number;

BEGIN

OPEN c_emp_sal;  –Step 2- Here we are opening the cursor

LOOP

EXIT WHEN c_emp_sal%notfound    –loop until all records fetched

Fetch c_emp_sal INTO e_id, e_sal ;  –Fetch each record into corresponding variables

E_revised_sal := e_sal + (e_sal*10)/100 ;

UPDATE Employee    –Update the revised salary of each employee

SET salary = E_revised_sal;

WHERE emp_id = e_id;

END LOOP;

CLOSE c_emp_sal;   –Close the cursor

END salary_revision;

Synonym?

synonym is an alternative name for objects.

Function ?

A function is a named pl/sql block that we create to perform specific operation.

It can contain IN, OUT and INOUT parameters.  A function must  return value. The return type we specify on function specification.

IN parameter is used to supply the value to function.

OUT parameter is used to get/return the value from function.

INOUT parameter is used to supply and get/return value from function.

Procedure?

A procedure is a named pl/sql block that we create to perform specific operation/transaction.  It can contain IN, OUT and INOUT parameters.  But procedure doesn’t have a return value.

Difference between procedure and function?

  1. Function must return value but procedure doesn’t.
  2. We can use function in Select statement but we cannot use procedure in select statement.

Where we should use function and procedure?

  1. We should use function when we are calculating something. Procedure we should use where we are performing some transaction involves dml operation.

How we can return many values from function?

Answer: By adding OUT parameter.

Why we use function and procedures?

Answer: For reusability. Suppose we created a function/procedure that calculates the sum of two values as entered in parameter.

So wherever we want calculate the sum of two values, then instead of writing the complete logic of summing we will call this function. So we won’t have to write the same logic again and again.

Note: Both procedure and function can have many OUT parameter.

What is Package?

Package is used to logically group related variables, functions, procedures etc.

Packages have two parts, a specification and a body. The specification is the interface to your applications where you declare the related variables, cursors, and subprograms (Functions, Procedures).

Whatever procedure function variables we define in package specification are global in scope. It means we can call print_salary_name procedure from outside of this package.

Whatever procedure, function we define inside package body, but don’t define in package specification are local in scope. It means that we can call the function emp_name from within package only (We are calling it in print_salary_name procedure). We cannot call it from outside the package(It means we can not call it from other package or procedure not defined inside employee package).

Package Specification

CREATE PACKAGE employee AS

PROCEDURE print_salary_name(employee_id Number);

END employee;

Package Body

CREATE OR REPLACE PACKAGE BODY employee AS  –Package employee body start

PROCEDURE print_salary_name (employee_id Number) IS –Procedure print_salary_name start

emp_salary Number;

e_name Varchar2(200);

BEGIN

SELECT salary INTO emp_salary

FROM Employee

WHERE emp_id = employee_id;

dbms_output.put_line(‘Employee Salary is: ‘|| emp_salary); –It will print employee salary

e_name := emp_name(employee_id);

dbms_output.put_line(‘Employee Name is: ‘|| e_name); –It will print employee Name

END print_salary_name; –Procedure print_salary_name end

Function emp_name(e_id Number) return varchar2 –Function emp_name start

IS

Employee_name Varchar2(200);

Begin

–This below query will fetch the name of employee having employee id = e_id  and assign this value into Employee_name variable.

Select emp_name INTO Employee_name

FROM Employee

WHERE emp_id = e_id;

Return Employee_name;  –This will return the value to the procedure print_salary_name calling this function

End emp_name; –Function emp_name end

END Employee;  –Package body end

Explain the Oracle version numbering system.

We’ll use the release number 10.1.0.3.2 as an example.

10 is the major database release number.

1 is the database maintenance release number.

0 is the application server release number.

3 is the component-specific release number.

2 is the platform-specific release number.

What file types compose the physical structure of an Oracle database?

At least one data file, redo log file, and control file.

Define a synonym, how many types there are, and explain the difference between them.

A synonym is another term for a table, sequence, or view. There are private synonyms and public synonyms. Only the owner of a private synonym has access to it, while public synonyms can be accessed by any of the database’s users.

Where are errors and other corruptions stored for easy access?

The alert log.

What’s inside a control file?

A database’s physical components, including the database name, a time stamp of its creation, and the name and locations of its files.

Define a rollback segment.

A rollback segment is an object in the database used to temporarily store before images.

Define a schema, and give five examples.

A schema is a collection of logical database objects owned by, and with the same name of, the database user. Schema objects can include tables, indexes, clusters, sequences, and views.

What is a snapshot?

A snapshot, or a materialized view, replicates a target master table at specified times.

What are the two types of snapshots? What’s the difference?

Simple: Simple snapshot queries exclude CONNECT BY and GROUP BY clauses, JOINs, snapshots of operations, and sub-queries.

Complex: Complex snapshots will contain at least one of the aforementioned.

How can you refresh a snapshot?

FORCE: The server that the snapshot is on will try to perform a fast refresh, and then opt for a complete refresh if that doesn’t work.

COMPLETE: The server will execute the snapshot query, replacing the snapshot with the query’s result set.

FAST: The latest changes applied to the master table since the last refresh are applied to the snapshot.

What’s the advantage of using an index?

Indexes provide quicker access to data blocks.

How do you rebuild an index?

ALTER INDEX index_name REBUILD;

What is a dictionary cache?

A dictionary cache uses a data dictionary table to store information about database objects.

What is a cluster?

A cluster is one or more tables physically grouped together based on shared columns.

What is the benefit to using a hash cluster?

It makes data retrieval quicker and more efficient, requiring less I/Os to be performed, making it preferable to using indexed tables. This is so because it hashes the cluster key value, which will directly locate the specified rows stored on the disk.

Explain the four cursor attributes.

%ROWCOUNT:

– Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed.

– Returns the number of rows fetched.

– The ROWCOUNT attribute doesn’t give the real row count until you have iterated through the entire cursor. In other words, you shouldn’t rely on this attribute to tell you how many rows are in a cursor after it is opened.

%NOTFOUND:

– Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed.

– Return NULL if cursor is open, but fetch has not been executed.

– Returns FALSE if a successful fetch has been executed.

– Returns TRUE if no row was returned.

%FOUND:

– Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed.

– Returns NULL if cursor is open, but fetch has not been executed.

– Returns TRUE if a successful fetch has been executed.

– Returns FALSE if no row was returned.

%ISOPEN:- Returns TRUE if the cursor is open, FALSE if the cursor is closed.

What is the minimum and maximum amount of columns a table can have?

Between 1 and 254.

How many types of segments are there? What are they?

Four types: data segment, index segment, temporary segment, and rollback segment.

What are V$SESSION and V$SESSION_WAIT used for?

They are used to determine connected sessions, and which resources those sessions are waiting for.

What does it mean to overload a procedure?

Overloading a procedure is very similar to overloading functions in general programming. The process includes the creation of numerous procedures within the same package, and all with the same name, but with different arguments.

Reference Cursor ?

A reference cursor is a pointer to a memory location that can be passed between different PL/SQL clients, thus allowing query result sets to be passed back and forth between clients.

A reference cursor is a variable type defined using the PL/SQL TYPE statement within an Oracle package, much like a PL/SQL table:

TYPE ref_type_name IS REF CURSOR RETURN return_type;

Here, ref_type_name is the name given to the type and return_type represents a record in the database. You do not have to specify the return type as this could be used as a general catch-all reference cursor. Such non-restrictive types are known as weak, whereas specifying the return type is restrictive, or strong. The following example uses %ROWTYPE to define a strong return type that represents the record structure of the emp table:

DECLARE TYPE EmpCurType IS REF CURSOR RETURN emp%ROWTYPE;

 Constraints ?

Integrity constraints are used to enforce Business logic. Enforcing rules with integrity constraints is less costly than enforcing the  equivalent rules by issuing SQL statements in the application.

Use the combination of NOT NULL and UNIQUE key integrity constraints to force the input of values in the UNIQUE key; this combination of data integrity rules eliminates the possibility that any new row’s data will ever attempt to conflict with an existing row’s data.

Choosing a Table’s Primary Key :

-Choose a column whose data values are unique.

-Choose a column whose data values are never changed.

Outer Join

To write a query that performs an outer join of tables A and B and returns all rows from A( left outer join), apply the outer join operator (+) to all columns of B in the join condition.

For example,

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date

FROM suppliers

LEFT OUTER JOIN orders

ON suppliers.supplier_id = orders.supplier_id;

This LEFT OUTER JOIN example would return all rows from the suppliers table and only those rows from the orders table where the joined fields are equal.

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date

FROM suppliers, orders

WHERE suppliers.supplier_id = orders.supplier_id(+);

Left outer join – All rows from table1 and only matching rows from table2

 FULL OUTER JOIN

This type of join returns all rows from the LEFT-hand table and RIGHT-hand table with nulls in place where the join condition is not met.

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date

FROM suppliers

FULL OUTER JOIN orders

ON suppliers.supplier_id = orders.supplier_id;

This FULL OUTER JOIN example would return all rows from the suppliers table and all rows from the orders table and whenever the join condition is not met, would be extended to those fields in the result set.

One method to simulate a full join is to take the union of two outer joins, for example,

select * from apples as a

left outer join oranges as o on a.price = o.price

union

select * from apples as a

right outer join oranges as o on a.price = o.price

This gives the desired results in this case, but it isn’t correct for all cases. Suppose there are duplicate records in the tables (remove the primary key and insert twice to create this situation). UNION eliminates duplicates, which a full join doesn’t do. UNION ALL isn’t the right answer either, because it will cause spurious duplicates. In fact, UNION generates two independent result sets and then combines them, so there is no way to get around this, because the two result sets need to “know about each other” to produce the right results.

One way to make UNION include only the duplicates I want is to use an exclusion join to eliminate anything from the second result that is already included in the first, like this:

select * from apples as a

left outer join oranges as o on a.price = o.price

union all

select * from apples as a

right outer join oranges as o on a.price = o.price

where a.price is null;

This handles duplicate rows correctly and doesn’t include anything it shouldn’t. It’s necessary to use UNION ALL instead of plain UNION, which would eliminate the duplicates I want to keep. This may be significantly more efficient on large result sets, since there’s no need to sort and remove duplicates.

RAISE and RAISE_APPLICATION_ERROR is part of package DBMS_STANDARD

Decode ? Using Decode map this logic,

If A>B Display ‘A is Big’, If A=B Display ‘A equals B’

Else ‘B is Big’

DECODE(Greatest(A,B), A, ‘A is big’,

B, ‘B is Big’,

‘A equals B’)

What is an index? what are diff type of indices? what is Clustered and Non Clustered Indexes ?

Indexing is typically listing of keywords alongwith its location and is done to speed up the Data base.

Create index WORKERSKILL_NAME_SKILL on WORKERSKILL(Name,Skill);

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

Mutating Error ?

When we use a row level triger on a table, and at the same time if we query/insert/delete/update on the same table, it will give the mutating error. Excessive use of triggers can result in complex interdependencies, which can be difficult to maintain in a large application

Difference between Delete and Truncate.

Truncate will permenantly delete the record and no rollback capability will exist. The delete command remove records from a table. Delete moves records to the rollback segment enabling rollback capability.

What are the default packages provided by oracle

The ones with “DBMS_” prefix. Eg. DBMS_Output, DBMS_ALERT

using select Statement how you will retrieve the user who is logged in?

SELECT SYS_CONTEXT (‘USERENV’, ‘SESSION_USER’) FROM DUAL;

how to generate the out of the select statement in a file?

SPOOL

What is normalised and denormalised data?

process of removing redundancy in data by separating the data into multiple tables.

What are cursors ? After retrieving the records into the cursor can we update the record in the table for the retrieved record. What effect will it have on the cursor?

Cursors – Oracle uses work areas to execute SQL statements and store processing information. A PL/SQL construct called a cursor lets you name a work area and access its stored information.

You will get error – fetch out of sequence

What is sql injection attack???

The easiest way for the login.asp to work is by building a database query that looks like this:

SELECT id

FROM logins

WHERE username = ‘$username’

AND password = ‘$password’

If the variables $username and $password are requested directly from the user’s input, this can easily be compromised. Suppose that we gave “Joe” as a username and that the following string was provided as a password: anything’ OR ‘x’=’x

SELECT id

FROM logins

WHERE username = ‘Joe’

AND password = ‘anything’ OR ‘x’=’x’

As the inputs of the web application are not properly sanitised, the use of the single quotes has turned the WHERE SQL command into a two-component clause. The ‘x’=’x’ part guarantees to be true regardless of what the first part contains. This will allow the attacker to bypass the login form without actually knowing a valid username / password combination!

List and explain each of the ACID properties that collectively guarantee that database transactions are processed reliably.

ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably. They are defined as follows:

Atomicity. Atomicity requires that each transaction be “all or nothing”: if one part of the transaction fails, the entire transaction fails, and the database state is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors, and crashes.

Consistency. The consistency property ensures that any transaction will bring the database from one valid state to another. Any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof.

Isolation. The isolation property ensures that the concurrent execution of transactions results in a system state that would be obtained if transactions were executed serially, i.e., one after the other. Providing isolation is the main goal of concurrency control. Depending on concurrency control method (i.e. if it uses strict – as opposed to relaxed – serializability), the effects of an incomplete transaction might not even be visible to another transaction.

Durability. Durability means that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors. In a relational database, for instance, once a group of SQL statements execute, the results need to be stored permanently (even if the database crashes immediately thereafter). To defend against power loss, transactions (or their effects) must be recorded in a non-volatile memory.

Table Collections: Examples

You can perform DML operations on nested tables only if they are defined as columns of a table.the collection expression must be a subquery that uses the TABLE function to select the nested table column of the table.

Suppose the database contains a table hr_info with columns department_id, location_id, and manager_id, and a column of nested table type people which has last_name, department_id, and salary columns for all the employees of each respective manager:

CREATE TYPE people_typ AS OBJECT (

last_name      VARCHAR2(25),

department_id  NUMBER(4),

salary         NUMBER(8,2));

/

CREATE TYPE people_tab_typ AS TABLE OF people_typ;

/

CREATE TABLE hr_info (

department_id   NUMBER(4),

location_id     NUMBER(4),

manager_id      NUMBER(6),

people          people_tab_typ)

NESTED TABLE people STORE AS people_stor_tab;

INSERT INTO hr_info VALUES (280, 1800, 999, people_tab_typ());

The following example inserts into the people nested table column of the hr_info table for department 280:

INSERT INTO TABLE(SELECT h.people FROM hr_info h

WHERE h.department_id = 280)

VALUES (‘Smith’, 280, 1750);

The next example updates the department 280 people nested table:

UPDATE TABLE(SELECT h.people FROM hr_info h

WHERE h.department_id = 280) p

SET p.salary = p.salary + 100;

The next example deletes from the department 280 people nested table:

DELETE TABLE(SELECT h.people FROM hr_info h

WHERE h.department_id = 280) p

WHERE p.salary > 1700;

Collection Unnesting: Examples

To select data from a nested table column, use the TABLE function to treat the nested table as columns of a table. This process is called collection unnesting.

You could get all the rows from hr_info, which was created in the preceding example, and all the rows from the people nested table column of hr_info using the following statement:

SELECT t1.department_id, t2.* FROM hr_info t1, TABLE(t1.people) t2

WHERE t2.department_id = t1.department_id;

Now suppose that people is not a nested table column of hr_info, but is instead a separate table with columns last_name, department_id, address, hiredate, and salary. You can extract the same rows as in the preceding example with this statement:

SELECT t1.department_id, t2.*

FROM hr_info t1, TABLE(CAST(MULTISET(

SELECT t3.last_name, t3.department_id, t3.salary

FROM people t3

WHERE t3.department_id = t1.department_id)

AS people_tab_typ)) t2;

Finally, suppose that people is neither a nested table column of table hr_info nor a table itself. Instead, you have created a function people_func that extracts from various sources the name, department, and salary of all employees. You can get the same information as in the preceding examples with the following query:

SELECT t1.department_id, t2.* FROM hr_info t1, TABLE(CAST

(people_func( … ) AS people_tab_typ)) t2;

Highest salary example

2nd highest salary

select MAX(Salary) from Employee

WHERE Salary (select MAX(Salary) from Employee )

Nth highest salary

SELECT *

FROM Employee Emp1

WHERE (N-1) = (

SELECT COUNT(DISTINCT(Emp2.Salary))

FROM Employee Emp2

WHERE Emp2.Salary > Emp1.Salary)

Nth highest salary

select * FROM (

select EmployeeID, Salary

,rank() over (order by Salary DESC) ranking

from Employee

)

WHERE ranking = N;

Delete duplicate records

delete from emp

where rowid not in

(select max(rowid) from emp group by empno);

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s