Prasanna Says

Saturday, March 15, 2008

Sql Part II

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
FROM DEPT
WHERE EXISTS (SELECT * FROM EMP WHERE
DEPT.DEPTNO = EMP.DEPTNO)


3. To Find The Not Null Column Alone In A Table.

SELECT COLUMN_NAME
FROM USER_TAB_COLUMNS
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.

To summarise:

True:
* 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.

Not true:
* 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

UNION
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.

UNION ALL
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

No comments: