Sql Part II Lets see some of the frequently asked queries in the interview.
1. To find The Nth Maximum Salary.
SELECT DISTINCT SAL
FROM EMP A
WHERE &N=(SELECT COUNT (DISTINCT B.SAL) FROM EMP B WHERE A.SAL<=B.SAL);
2. To use Exists Clause. Correlated subquery
SELECT DNAME, DEPTNO
WHERE EXISTS (SELECT * FROM EMP WHERE
DEPT.DEPTNO = EMP.DEPTNO)
3. To Find The Not Null Column Alone In A Table.
WHERE NULLABLE = 'N'
AND TABLE_NAME = 'COUNTRY'
4.To delete The Duplicate Rows Alone in A Table. (frequently asked question)
DELETE DEPT WHERE ROWID NOT IN (SELECT MAX (ROWID)
FROM DEPT GROUP BY DEPTNO HAVING COUNT (*) >=1)
5.To find The Max Salary without MAX Function.
1. SELECT DISTINCT SAL
FROM EMP1 WHERE SAL NOT IN
(SELECT SAL FROM EMP1 WHERE SAL < ANY (SELECT SAL FROM EMP1))
2. SELECT SAL FROM EMP WHERE SAL >= ALL (SELECT SAL FROM EMP)
6. To find the database name
select name from v$database;
7. To convert the given number to word
SELECT TO_CHAR (TO_DATE (&NUM,'J'),'JSP') FROM DUAL;
8. What is the difference between unique and distinct keyword in Oracle.
* Only one primary key is allowed per table (any number of unique keys are allowed).
* Primary key columns automatically become NOT NULL (unique keys can contain nulls).
* UNIQUE and DISTINCT are synonymous in a SELECT list.
* A unique key cannot be referenced in a foreign key definition (it can)
* A primary key will have a unique index (it might not)
* DISTINCT and UNIQUE handle LOBs differently (they do not)
* UNIQUE is Oracle syntax while DISTINCT is ANSI (both appear to be ANSI-compliant according to the SQL*Plus flagger option, although I couldn't find this standard documented. Oracle did not support SELECT UNIQUE until 8.1.5.)
9. What is the difference between union and union all
1. returns all distinct rows selected by either query
2. does the multi level sorting on data starting from first column
3. due to sorting query performance goes down dramatically.
1. returns all the rows selected by either query including all duplicates.
2. No sorting is applied on the data.
3. No performance issue in this set operation. And it is advisable to use union all instead of union where ever applicable.
Also visit : Sql Part 1