Important Queries -- Interview Questions

Write a query to fetch system date from oracle db.

select sysdate from dual

Query to view installed Oracle version information.

select banner from v$version;

How to view NLS parameters ?

select * from nls_database_parameters

Query to find open cursors.

1. SELECT user_Name, COUNT(SQL_Text), SQL_Text FROM v$open_cursor GROUP BY SQL_Text,user_Name ORDER BY user_Name ASC , COUNT(SQL_Text) DESC, SQL_Text;

2. SELECT COUNT(USER_NAME), USER_NAME FROM v$open_cursor GROUP BY USER_NAME;

How to view all indexes on any table ?

SELECT X.TABLE_NAME, X.COLUMN_NAME, X.DATA_TYPE, X.INDEX_TYPE, X.INDEX_NAME, Y.COLUMN_EXPRESSION

FROM

(SELECT A.TABLE_NAME, A.COLUMN_NAME, C.DATA_TYPE, B.INDEX_TYPE, A.INDEX_NAME

FROM User_Ind_Columns A, User_Indexes B, USER_TAB_COLUMNS C

WHERE A.Index_Name = B.Index_Name

AND A.TABLE_NAME = C.TABLE_NAME(+)

AND A.COLUMN_NAME = C.COLUMN_NAME(+)

AND A.TABLE_NAME LIKE 'TABLE_Name') X, USER_IND_EXPRESSIONS Y

WHERE X.INDEX_NAME = Y.INDEX_NAME(+)

What value one gets for "Select * from dual" ?

DUMMY

------

X

How can you take an Input from a User of the Database?

You can take it using the Input Operator. This is as follow:

SELECT * FROM table WHERE column = &input;

How will you copy the structure of a table without copying the data ?

CREATE TABLE DestTable AS SELECT * FROM SourceTable WHERE 1=2;

What is the difference between these 2 queries :

• Select count(*) from table
• Select count(1) from table

Nothing, they are the same, incur the same amount of work -- do the same thing, take the same amount of resources.

How do I eliminate the duplicate rows ?

delete from table_name where rowid not in (select max(rowid) from table group by

duplicate_values_field_name);

Write a query to display "Not Applicable" or "NA" instead of blank space if commission is null for a employee.

SQL> select nvl(to_char(comm),'NA') from emp;

Output :

NVL(TO_CHAR(COMM),'NA')

-----------------------

NA

300

500

NA

1400

NA

NA

Find out nth highest salary from emp table.

SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B

WHERE a.sal<=b.sal);

Display the number value in Words.

SQL> select sal, (to_char(to_date(sal,'j'), 'jsp'))

from emp;

SAL (TO_CHAR(TO_DATE(SAL,'J'),'JSP'))

--------- -----------------------------------------------------

800 eight hundred

1600 one thousand six hundred

1250 one thousand two hundred fifty

Display Odd number of records.

select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);

Display Even number of records.

select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)