SYSDBA and SYSOPER are special system privileges that enable access to a database instance even when the database is not open. Control of these privileges is outside of the database itself. When you connect with the SYSDBA system privilege, you are in the schema owned by SYS. When you connect as SYSOPER, you are in the public schema. SYSOPER privileges are a subset of SYSDBA privileges.
To find the nth highest salary
# First Method - Oracle
SELECT *
FROM scott.emp
WHERE (&n) = (SELECT COUNT (DISTINCT (emp1.sal))
FROM scott.emp emp1
WHERE emp.sal <= emp1.sal);
# 2nd Method - Oracle
SELECT sal
FROM (SELECT e1.*, DENSE_RANK () OVER (ORDER BY sal DESC) rnk
FROM scott.emp e1)
WHERE rnk = 3;
# 3rd Method - Oracle
SELECT DISTINCT (a.sal)
FROM scott.emp a
WHERE 3 = (SELECT COUNT (DISTINCT (b.sal))
FROM scott.emp b
WHERE a.sal <= b.sal);
# 4th Method - PostgreSql
select distinct (sal) from employees order by emp desc limit 3 offset 3;
# 5th Method - SQL Server
SELECT Salary FROM Employee
ORDER BY Salary DESC OFFSET N-1 ROW(S)
FETCH FIRST ROW ONLY
# 6th Method - SQL Server
SELECT TOP 1 Salary
FROM (
SELECT DISTINCT TOP N Salary
FROM Employee
ORDER BY Salary DESC
) AS Emp
ORDER BY Salary
# 7th Method - MySQL
SELECT Salary FROM Employee
ORDER BY Salary DESC LIMIT n-1,1
Nice..
ReplyDelete