...

Read More

Very useful info

Very useful info; Please don't delete this without forwarding.  Let it reach the 110 Crores Indians and the remaining if any. Kindly, share this valuable information wherever possible...

Read More

how to count the duplicate rows in a table

This question has been asked many times, how to find duplicate values in a database table. Many times if you are loading data table , As an example you might have a table called Customers and a field called Username, this column is suppose to be unique but now you have some duplicate usernames. If you want to count how many usernames are duplicated and how many usernames are still unique then you can use the GROUP BY and HAVING clause to find duplicate values.SELECT username, COUNT(*) FROM customers_tableGROUP BY username HAVING COUNT(*) > 1Now you can use the above technique to find duplicate rows in more than one column. If you want to find duplicates only where username and email address are same then we can add the email column in it.SELECT username, email, COUNT(*) FROM customers_tableGROUP...

Read More

Eliminate duplicate rows from table

Eliminate duplicate rows from table Delete from EMP a Where a.rowid <> (select min (b.rowid) from emp b where a.empno = b.empno); OR Delete from EMP a Where a.rowid <> (select max (b.rowid) from emp b where a.empno = b.empn...

Read More

DECODE FUNCTION IN ORACLE

PurposeDECODE compares expr to each search value one by one. If expr is equal to a search, then Oracle Database returns the corresponding result. If no match is found, then Oracle returns default. If default is omitted, then Oracle returns null.The arguments can be any of the numeric types (NUMBER, BINARY_FLOAT, or BINARY_DOUBLE) or character types.If expr and search are character data, then Oracle compares them using nonpadded comparison semantics. expr, search, and result can be any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The string returned is of VARCHAR2 datatype and is in the same character set as the first result parameter.If the first search-result pair are numeric, then Oracle compares all search-result expressions and the first expr to determine the argument with the...

Read More

connect by prior

TO DISPLAY HIERARCHICAL The START WITHCONNECT BY clause. The PRIOR operator. The LEVEL pseudocolumn. SYNTAX [[START WITH condition 1]CONNECT BY condition2] START WITH condition 1 IT SPECIFIES THE ROOT OF THE HIERARCHY. ALL ROWS THAT SATISFIES CONDITION1 ARE CONSIDERED ROOT ROWS.IF START WITH CLAUSE IS NOT SPECIFIED ALL ROWS ARE CONSIDERED ROOT ROWS.CONDITION 1 CAN INCLUDE A SUB-QUERY. CONNECT BY condition2SPECIFIES THE RELATIONSHIP BETWEEN PARENT ROWS AND CHILD ROWS.THE RELATIONSHIP IS EXPRESSED AS A COMPARISION EXPRESSION,WHERE COLUMNS FROM THE CURRENT ROWS ARE COMPARED TO CORRESPONDING PARENT COLUMNS.THE CONDITION2 MUST CONTAIN THE PRIOR OPERATOR WHICH IS USED TO IDENTIFY COLUMNS FROM THE PARENT ROWS.CONDITION2 CAN'T CONTAIN ANY SUB-QUERY.SINCE THE CONNECT BY CONDITION SPECIFIES THE PARENT-CHILD...

Read More

CO-RELATED SUBQUERY

CO-RELATED SUBQUERY A CO-RELATED SUBQUERY is one that has a correlation name as table or view designator in the FROM clause of the outer query and the same correlation name as a qualifier of a search condition in the WHERE clause of the subquery.eg:SELECT field1 from table1 XWHERE field2>(select avg(field2) from table1 Y where field1=X.field...

Read More

To know the current week of the year

To know the current week of the year SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'IW')) FROM D...

Read More

Second Largest Number

Second Largest NumberSELECT empno FROM (SELECT e.empno, RANK() over (ORDER BY e.empno DESC) rk FROM emp e ) WHERE rk =...

Read More

What are cursor attributes?

What are cursor attributes?Cursor attributes are used to get the information about the current status of your cursor. Both explicit and implicit cursors have four attributes, as shown:Name Description%FOUND Returns TRUE if record was fetched successfully, FALSE otherwise.%NOTFOUND Returns TRUE if record was not fetched successfully, FALSE otherwise.%ROWCOUNT Returns number of records fetched from cursor at that point in time.%ISOPEN Returns TRUE if cursor is open, FALSE otherwi...

Read More