Sql queries version 1

 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;

 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

Display Jan to February:

SELECT * FROM WWV_FLOW_MONTHS_MONTH;

Display 1^st Sunday of month

SELECT NEXT_DAY( TRUNC(SYSDATE, 'MM') - 1

               , 'Sunday')

  FROM dual

First day of the month

Select trunc(sysdate,'MM')
from dual

Suppose There is a string

A.B....C.......D.........E........F In this string dots (.)

are not having fixed count in between of string. I want the

output to have string with one dot between. I.e.

A.B.C.D.E.F

SELECT REGEXP_REPLACE (

REPLACE ('A.B....C.......D.........E........F', '.', ' '),

'( ){2,}',

'.')

FROM DUAL;

I have a word

***********hello********world******.

I require a o/p

**********hello world**********,

Need to delete the middle stars.

select (substr('***********hello********world******',1,instr

('***********hello********world******','o',1) )

||' '||substr('***********hello********world******',instr

('***********hello********world******','w',1) )

)

from dual;

how can we write a column values horizontal by using sql stmt;

ex:

select name from table_name;(actual output)

a

b

c

d

require output is

a b c d

select translate(wm_concat(ename),',' , ' ') from table_name;

Notes; The Oracle PL/SQL WM_CONCAT function is used to aggregate data from a number of rows into a single row, giving a list of data associated with a specific value. In effect, it cross-tabulates a comma delimited list.

If 100 tables are there in user_tables.I want to find in which table zero records are there with table name.Is it possible?

select table_name,num_rows from user_tables

where num_rows = 0

i hv 30 rows with date.ex:1month hav 4 weeks i want 1st day of the every week.write the qry for that.example jan has 4 weeks i need 1st dd for evry wk

select year,week,date1,to_char(date1,'Day') day

from

(select year, week,

next_day( to_date( '04-jan-' || year, 'dd-mon-yyyy' ) + (week-2)*7, 'mon' ) date1

from (select '2011' year, rownum week from all_objects where rownum <= 53 ))

Like shown below i have 3 columns(Name,No,Address). The values in name column i want to modity. Requirement : Keep only once space between two words(Fname,Lname) in the Name column.

For this what is the query?

Example:

Name No Address Reference

manoj kumar

kumar raja

vinzay kumar

rajendra prasad

gowri nath

output:

Name No Address Reference

Manoj kumar

Kumar raja

Vinzay kumar

rajendra prasad

gowri nath

select substring(NAME,1,charindex(' ',NAME))+' '+

replace(substring(NAME,charindex(' ',NAME),len(NAME)),' ','')

as NAME from test

-- utl_file write--

DECLARE

l_file utl_file.file_type;

begin

l_file := utl_file.fopen('TEMP_DIR','abc.xls','a', 32767);

utl_file.put_line(l_file, sysdate || ' : ' || to_char(sysdate, 'dd-mon-yyyy hh24:mi:ss'));

utl_file.fclose(l_file);

exception

when others then

dbms_output.put_line(sqlerrm);

end;

Display date to letters

select to_char(sysdate, 'DDSPTH MONTH YEAR') FROM DUAL;

TO_CHAR(SYSDATE,'DDSPTHMONTHYEAR')

TWENTY-NINTH MARCH TWENTY TWELVE

How to count the number of spaces in the particular 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;

ANALYTICAL FUNCTIONS:

ROW_NUMBER:

1. It will provide the serial number of the result set.

SELECT ename, empno, deptno, hiredate, ROW_NUMBER () OVER (ORDER BY HIREDATE NULLS LAST) serialno FROM emp WHERE deptno in (10, 20);

RANK ( ) and DENSE_RANK( ):

Looks like, RANK ( ) and DENSE_RANK ( ) are same. However, there is some variation in each result.

Below query shows the usage of both RANK and DENSE_RANK. For DEPTNO 20 there are two contenders for the first position (EMPNO 7788 and 7902). Both RANK and DENSE_RANK declares them as joint toppers. RANK skips the next value that is 2 and next employee EMPNO 7566 is given the position 3. For DENSE_RANK there are no such gaps and gives the rank 1, 2, 3 like that.

SELECT ename,empno, deptno, sal, RANK() OVER (PARTITION BY DEPTNO ORDER BY sal DESC NULLS LAST) RANK, DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC NULLS LAST) dense_rank FROM emp where deptno in (10,20) order by 3, RANK;

ENAME	EMPNO	DEPTNO	SAL	RANK	DENSE_RANK
KING	7839	10	5000	1	1
CLARK	7782	10	2450	2	2
MILLER	7934	10	1300	3	3
SCOTT	7788	20	3000	1	1
FORD	7902	20	3000	1	1
JONES	7566	20	2975	3	2
ADAMS	7876	20	1100	4	3
SMITH	7369	20	800	5	4

LEAD ( ) and LAG ( )

Syntax:

LEAD(sal, 1, 0) OVER (PARTITION BY deptno ORDER BY sal DESC NULLS LAST) next_lower_sal

Sal – To compare the other salaries.

1. -- It’ll increment 1 by 1. It means it’ll calculate the next person value. If you want to calculate 3^rd person value, then put 2 here.

1. - If the salary is higher than the current salary, then it’ll put the value is 0.

LAG(sal, 1, 0) OVER (PARTITION BY deptno ORDER BY sal DESC NULLS LAST) previous_higher_sal

Sal – To compare the other salaries.

2. – It’ll decrease 1 by 1. It means it’ll calculate the previous person value. If you want to calculate 3^rd person value, then put 2 here.

1. -- If the salary is lower than the current salary, then it’ll return the value is 0.

LEAD is fetching the value, which is come after the current row. LAG is fetching the value, which is come before the current row.

SELECT ename,empno, deptno, sal, LEAD(sal, 1, 0) OVER (PARTITION BY deptno ORDER BY sal DESC NULLS LAST) NEXT_LOWER_SAL, LAG(sal, 1, 0)OVER (PARTITION BY deptno ORDER BY sal DESC NULLS LAST) previous_higher_sal FROM emp where deptno in (10,20) order by 3, sal desc;

ENAME	EMPNO	DEPTNO	SAL	NEXT_LOWER_SAL	PREVIOUS_HIGHER_SAL
KING	7839	10	5000	2450	0
CLARK	7782	10	2450	1300	5000
MILLER	7934	10	1300	0	2450
SCOTT	7788	20	3000	3000	0
FORD	7902	20	3000	2975	3000
JONES	7566	20	2975	1100	3000
ADAMS	7876	20	1100	800	2975
SMITH	7369	20	800	0	1100

FIRST_VALUE ( )

After doing the ORDER by it’ll pick the first value. Below query helps to find out the days after the 1^st employee joined in the department.

SELECT ename, empno, deptno, hiredate - FIRST_VALUE (hiredate) OVER (PARTITION BY deptno ORDER BY hiredate) day_gap from emp where deptno in (20, 30) order by 3, day_gap;

ENAME	EMPNO	DEPTNO	DAY_GAP
SMITH	7369	20	0
JONES	7566	20	106
FORD	7902	20	351
SCOTT	7788	20	722
JAMES	7900	30	286

Difference between triggers and constraints in Oracle

• Both will be used to implement business rules.

• Constraints will do memory location to table comparision. Where as triggers will do table to table comparision. for this triggers will use magic tables(inserted,deleted).

• In the order of precedence first Constraints next Triggers,
• But performance wise triggers will give best performance because table to table comparision is faster than memeory location to table comparision.

Cluster index and non cluster index:

Clustered Index:- A Clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table may have only one clustered index.Non-Clustered Index:- A Non-Clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows in the disk. The leaf node of a non-clustered index does not consist of the data pages. Instead the leaf node contains index rows.

one more point is we can have only clustered index in a table where as we can have many non clustered indexes.
primary key constraints create clustered indexes automatically if no clustered index already exists on the table and a nonclustered index is not specified when you create the primary key constraint.

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;

1. 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.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),',',''','''),' ','')

Index Organized Tables

Index Organized Tables have their primary key and non-key column data stored within the same B-Tree structure.

The data is stored within the primary key index.

Use:

IOTs make accessing data via the primary key quicker as the key and the data reside in the same structure.

Since there is no need to read an index, the table data is read in a separate structure.

Less duplication of the key columns in an index and table mean low storage requirements.

Single values to multiple values to use it in the IN statement.

SELECT (column_value).getstringval() a FROM xmltable('"a","b","c","d","e","f"');

OR

select (column_value).getstringval() as var from dual,xmltable('1,2');