You do not have Permission to view this Post

You do not have Permission to view this Post

SOLUTION1

select * from ( select ename,sal,dense_rank() over (order by sal desc) rank from emp) where rank=2

SOLUTION2

select * from ( select ename,sal,deptno,dense_rank() over (partition by deptno order by sal desc) rank from emp) where rank=2

SOLUTION3

SELECT * FROM emp a WHERE (&n-1)=(SELECT count(DISTINCT b.sal) FROM emp bWHERE a.sal<b.sal);

SOLUTION4

 To find the nth highest salary and find the second largest value in a given column as you asked both are same. So, both of the above scenarios the below is the query:

SELECT * FROM table_name a WHERE &n=(SELECT count(DISTINCT(sal)) FROMTable_name b WHERE a.sal<b.sal);

'select the count from two tables

select (select count(*) from emp) as count1,(select count (*)  from empnew5) count2 from dual
++++++++++++++++++++++++++++++++++++++++++

'HOW TO ELIMINATE DUPLICATE ROWS WHILE DISPLAYING THE DATA

select distinct * from empnew where ename in

(SELECT ename FROM empnew GROUP BY ename HAVING ( COUNT(ename) > 1 or COUNT(ename)=1))

++++++++++++++++++++++++++++++++++++++++

'select the count from two tables

select (select count(*) from emp) as count1,(select count (*)  from empnew5) count2 from dual

+++++++++++++++++++++++++++++++++++++++++++

select distinct empno,ename,sal,deptno,mgr from empnew where empno in

         (SELECT empno FROM empnew GROUP BY empno HAVING ( COUNT(empno) > 1 ))

++++++++++++++++++++++++++++++++++++++++++++

SELECT ename,sal,count(*) NoOfOccurences FROM empnew GROUP BY ename HAVING ( COUNT(ename) > 1 )

SELECT ename FROM empnew GROUP BY ename HAVING ( COUNT(ename) > 1 )

+++++++++++++++++++++++++++++++++++++++++

select distinct * from empnew where ename in

(SELECT ename FROM empnew GROUP BY ename HAVING ( COUNT(ename) > 1 ))

+++++++++++++++++++++++++++++++++++++++++++

'HOW TO ELIMINATE DUPLICATE ROWS WHILE DISPLAYING THE DATA

select distinct * from empnew where ename in

(SELECT ename FROM empnew GROUP BY ename HAVING ( COUNT(ename) > 1 or COUNT(ename)=1))

select min(rowid)from empnew2 group by ename

delete from empnew2 where rowid not in(select min(rowid)from empnew2 group by ename)

CREATE TABLE EMPNEW3 AS selecT * FROM EMPNEW

+++++++++++++++++++++++++++++++++++++++

select distinct empno,ename,sal,deptno,mgr from empnew where empno in

         (SELECT empno FROM empnew GROUP BY empno HAVING ( COUNT(empno) > 1 ))

SELECT empno FROM empnew GROUP BY empno HAVING ( COUNT(empno) = 1 )

+++++++++++++++++++++++++++++++++++++++

'intersect will display the common rows between the two tables

select * from empnew intersect select * from empnew5

+++++++++++++++++++++++++++++++

'select the count from two tables

select (select count(*) from emp) as count1,(select count (*)  from empnew5) count2 from dual

CREATE DATABASE LINK

 Purpose

Use the CREATE DATABASE LINK statement to create a database link. A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.

Once you have created a database link, you can use it to refer to tables and views on the other database. In SQL statements, you can refer to a table or view on the other database by appending @dblink to the table or view name. You can query a table or view on the other database with the SELECT statement. You can also access remote tables and views using any INSERT, UPDATE, DELETE, or LOCK TABLE statement.

Prerequisites

To create a private database link, you must have the CREATE DATABASE LINK system privilege. To create a public database link, you must have the CREATE PUBLIC DATABASE LINK system privilege. Also, you must have the CREATE SESSION system privilege on the remote Oracle database.

Oracle Net must be installed on both the local and remote Oracle databases.

CONNECT TO Clause

The CONNECT TO clause lets you enable a connection to the remote database.
CURRENT_USER Clause

Specify CURRENT_USER to create a current user database link. The current user must be a global user with a valid account on the remote database.

If the database link is used directly, that is, not from within a stored object, then the current user is the same as the connected user.

When executing a stored object (such as a procedure, view, or trigger) that initiates a database link, CURRENT_USER is the username that owns the stored object, and not the username that called the object. For example, if the database link appears inside procedure scott.p (created by scott), and user jane calls procedure scott.p, the current user is scott.

However, if the stored object is an invoker-rights function, procedure, or package, the invoker's authorization ID is used to connect as a remote user. For example, if the privileged database link appears inside procedure scott.p (an invoker-rights procedure created by scott), and user Jane calls procedure scott.p, then CURRENT_USER is jane and the procedure executes with Jane's privileges.

Examples

The examples that follow assume two databases, one with the database name local and the other with the database name remote. The examples use the Oracle Database domain. Your database domain will be different.


Defining a Public Database Link: Example

The following statement defines a shared public database link named remote that refers to the database specified by the service name remote:


CREATE PUBLIC DATABASE LINK remote USING 'remote';

This database link allows user hr on the local database to update a table on the remote database (assuming hr has appropriate privileges):


UPDATE employees@remote SET salary=salary*1.1 WHERE last_name = 'Baer';


Defining a Fixed-User Database Link: Example

In the following statement, user hr on the remote database defines a fixed-user database link named local to the hr schema on the local database:


CREATE DATABASE LINK local CONNECT TO hr IDENTIFIED BY hr USING 'local';

Once this database link is created, hr can query tables in the schema hr on the local database in this manner:


SELECT * FROM employees@local;

User hr can also use DML statements to modify data on the local database:


INSERT INTO employees@local (employee_id, last_name, email, hire_date, job_id) VALUES (999, 'Claus', 'sclaus@oracle.com', SYSDATE, 'SH_CLERK'); UPDATE jobs@local SET min_salary = 3000 WHERE job_id = 'SH_CLERK'; DELETE FROM employees@local WHERE employee_id = 999;

Using this fixed database link, user hr on the remote database can also access tables owned by other users on the same database. This statement assumes that user hr has SELECT privileges on theoe.customers table. The statement connects to the user hr on the local database and then queries the oe.customers table:


SELECT * FROM oe.customers@local;


Defining a CURRENT_USER Database Link: Example

The following statement defines a current-user database link to the remote database, using the entire service name as the link name:


CREATE DATABASE LINK remote.us.oracle.com CONNECT TO CURRENT_USER USING 'remote';

The user who issues this statement must be a global user registered with the LDAP directory service.

You can create a synonym to hide the fact that a particular table is on the remote database. The following statement causes all future references to emp_table to access the employees table owned by hron the remote database:


CREATE SYNONYM emp_table FOR oe.employees@remote.us.oracle.com;


COURTESY:


http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_5005.htm



 

You do not have Permission to view this Post