SQL Important Queries and Interview Questions

SQL queries:

 How to find Nth Salary

select min(sal) from (select * from mouse order by sal desc) where rownum<=n;

 Selecting Nth row from the table:

select * from cps_operator a where 5= ( select count(rowid) from cps_operator b where a.rowid>=b.rowid);
(OR)

select * from (select rownum r, emp.* from emp) where r=3;

Fetching top 5 rows from the table:

Select * from (select * from cps_operator where status='A' order by op_id desc) where rownum <=5;

Finding the Nth Salary or fetching top 3 Sal:

SELECT * FROM (SELECT empno, ename, sal, DENSE_RANK () OVER(ORDER BY sal DESC) TOP_RANK FROM emp) WHERE TOP_RANK <=3;

Finding the designation count for each dept.

SELECT deptno,SUM(DECODE (desg, 'MANAGER',1)) MANAGER, SUM(DECODE (desg, 'SALESMAN',1)) SALESMAN, SUM(DECODE (desg, 'CLERK',1)) CLERK FROM emp GROUP BY DEPTNO;

Segregate the values with ‘.’ or ’/’

Select SUBSTR('VIJAYAN/KUMAR',1,(INSTR('VIJAYAN/KUMAR', '/')-1)) PREFIX, SUBSTR('VIJAYAN/KUMAR',(INSTR('VIJAYAN/KUMAR', '/')+1)) SUFFIX from dual;

Copy the whole table:

CREATE TABLE table_name AS SELECT * FROM emp;

→ copy one column value to another table column:

insert into test first_name
select null,ename,null,null from emp

Copy only the structure of the table

CREATE TABLE table_name AS SELECT * FROM emp WHERE 1=0;

How does one select the LAST N rows from a table?

SELECT object_name, object_type FROM (SELECT object_name, object_type, RANK ( ) OVER (ORDER BY object_name) rank FROM user_objects) WHERE rank <=10;

Calculate number of days:

SELECT TRUNC(startdate)-TRUNC(enddate) FROM dual;

Self Join:

To find the employee name and manager name from the same table.

SELECT a.ename AS employeename, b.ename AS Mgrname FROM emp a LEFT OUTER JOIN emp b ON (a.mgr=b.empno)

→ Numer to words

select to_char(to_date(5455,'j'),'jsp') from dual;

EMPLOYEENAME	MGRNAME
JAMES	KING
vijayan	vijayan
ADAMS	JAMES
KING	-

 Write a query to list the length of service of the Employees (of the form n years and m months).

SELECT ENAME "EMPLOYEE",TO_CHAR(TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12))

||' YEARS '|| TO_CHAR(TRUNC(MOD(MONTHS_BETWEEN

(SYSDATE, HIREDATE),12)))||' MONTHS ' "LENGTH OF SERVICE"

FROM EMPLOYEE;

Display city, total sell and total buy for each coutries.

SELL	BUY	AMOUT
USD	EUR	64646
INR	AUD	116464
AUD	INR	464674
YEN	INR	69897979

QUERY:

Select CC.CITY,NVL(a.amt,0) TOTAL_SELL , NVL(B.amt,0) TOTAL_BUY from (SELECT * FROM (SELECT SELL,SUM(AMOUT) amt FROM CCT GROUP BY SELL)) a
FULL OUTER JOIN (sELECT * FROM (SELECT BUY,SUM(AMOUT) amt FROM CCT GROUP BY BUY)) b
ON (a.sell=b.buy)
LEFT OUTER JOIN
(SELECT SELL CITY FROM CCT
UNION
SELECT BUY FROM CCT) CC
ON (CC.CITY =A.SELL OR CC.CITY =B.BUY)

Output:

CITY	TOTAL_SELL	TOTAL_BUY
EUR	0	64646
INR	116464	70362653
AUD	464674	116464
USD	64646	0
YEN	69897979	0

Count the no. of spaces in the column:

In the below example test is user-defined value.

with test as

(select ' Hello I am Vijayan' col from dual)

select regexp_count(col, ' ') result

from test;

RESULT

4

How to segregate the values with dot(.)

select SUBSTR(12345.64644646,1,(INSTR(12345.64644646, '.')-1)) PREFIX, SUBSTR(12345.64644646,(INSTR(12345.64644646, '.')+1)) SUFFIX from dual;

PREFIX	SUFFIX
12345	64644646

Accept multiple values through IN parameter:

Advise user to pass multiple values by using comma (,), then do the below operation in the plsql logic. Then pass the values to select query IN condition.

REPLACE (REPLACE (UPPER(PROJECT_KEY),',',''','''),' ','')

Rows to single column in Oracle 8 or 9i:

REPLACE(RTRIM(XMLAGG (XMLELEMENT (e, comp.cname || '','')).extract (''//text()''), '',''), ''&amp;'', ''&'') componentname

Since oracle 10g, we can use the below function

Select LISTAGG(ename,',') WITHIN GROUP (ORDER BY ename) from emp;

NVL2 and decode difference:

NVL2 always evaluates both alternatives, DECODE doesn't. This could affect performance if
evaluation is expensive like function calls or nested queries.
It could also affect the program flow.

For Example:

SQL>var x number;
SQL>exec :x := 1;

PL/SQL procedure successfully completed.

SQL>select decode(:x, NULL, 1 / 0, 1) from dual;

DECODE(:X,NULL,1/0,1)

                1

SQL>select nvl2(:x, 1, 1 / 0) from dual;
select nvl2(:x, 1, 1 / 0) from dual
*
ERROR at line 1:
ORA-01476: divisor is equal to zero

You need to decide which one to use according to the necessity (keeping in mind the performance).

Fetch only string data

Select * from emp where regexp_like (ename, '[A-Z]');

Fetch the data, if any number embedded.

Select * from emp where regexp_like (ename, '[1-9]');

How to add the primary key constraints on the columns, which is having duplicate data.

The steps are:

1. Create index on the particular column;

2. Then, create constraint with the novalidate option.

3. If you trying to remove index, then it will throw an error like 'ORA-02429: cannot drop index used for enforcement of unique/primary key'. Hence, if you want to remove index, then you have to drop constraint 1st.

Example:

create index idx_ccc on ccc(eno);
alter table ccc add constraint eno_pk primary key (eno) novalidate;

Can we update a partioning column?

No. We can't update. It will throw an error like:

ORA-14402: updating partition key column would cause a partition change

If you still wants to edit then you have to use alter table t enable row movement;

Partition means you are physically storing the data on different location based on Partition Key.
So when you want to update the partition key you need to move the entire row from one partition to another. To enable this happen you need to instruct the table to Allow ROW MOVEMENT.

Split numbers and strings...

select regexp_replace(DNAME, '[^[:digit:]]'),REGEXP_REPLACE(DNAME, '[0-9]*', '' )  from dept; 

Find the gap between the numbers:

WITH aa AS (SELECT col1 AS cur_value, LEAD (col1) OVER (ORDER BY col1) AS next_value FROM tab1) SELECT cur_value + 1 AS start_gap, next_value - 1 AS end_gap FROM aa WHERE next_value - cur_value > 1 ORDER BY start_gap