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(*) > 1

Now 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 BY username, email HAVING COUNT(*) > 1
SELECT username, COUNT(*) FROM customers_tableGROUP BY username HAVING COUNT(*) = 1

The same logic can be changed to find non duplicate rows only by changing the HAVING COUNT(*) > 1 to = 1, this will give us nonduplicated usernames.

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.empno);

Read More

DECODE FUNCTION IN ORACLE

Purpose
DECODE 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 highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.

The search, result, and default values can be derived from expressions. Oracle Database uses short-circuit evaluation. That is, the database evaluates each search value only before comparing it to expr, rather than evaluating all search values before comparing any of them with expr. Consequently, Oracle never evaluates a search if a previous search is equal to expr.

Oracle automatically converts expr and each search value to the datatype of the first search value before comparing. Oracle automatically converts the return value to the same datatype as the first result. If the first result has the datatype CHAR or if the first result is null, then Oracle converts the return value to the datatype VARCHAR2.

In a DECODE function, Oracle considers two nulls to be equivalent. If expr is null, then Oracle returns the result of the first search that is also null.
The maximum number of components in the DECODE function, including expr, searches, results, and default, is 255.

Examples For Decode
This example decodes the value warehouse_id. If warehouse_id is 1, then the function returns 'Southlake'; if warehouse_id is 2, then it returns 'San Francisco'; and so forth. If warehouse_id is not 1, 2, 3, or 4, then the function returns 'Non domestic'.

SELECT product_id,
DECODE (warehouse_id, 1, 'Southlake',
2, 'San Francisco',
3, 'New Jersey',
4, 'Seattle',
'Non domestic')
"Location of inventory" FROM inventories
WHERE product_id <>

Read More

connect by prior

TO DISPLAY HIERARCHICAL The START WITH
CONNECT 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 condition2
SPECIFIES 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 RELATIONSHIP IT CAN'T CONTAIN A LOOP(IF A ROW IS BOTH DIRECT ANCESTOR AND DIRECT DECENDANT OF ANOTHER ROW THERE IS A LOOP).

PRIOR
PRIOR IS A BUILT-IN ORACLE SQL OPERATOR THAT IS USED WITH THE HIERARCHICAL QUERIES ONLY. IN A HIERARCHICAL QUERY THE CONNECT BY CLAUSE SPECIFIES THE RELATION BETWEEN PARENT AND CHILD ROWS.WHEN THE PRIOR OPERATOR IS USED IN AN EXPRESSION IN THE CONNECT BY CONDITION, THE EXPRESSION FOLLOWING THE PRIOR KEYWORD IS EVALUATED FOR THE PARENT ROW OF THE CURRENT ROW IN THE QUERY.

Sample
SELECT ENAME,EMPNO,MGR FROM EMP START WITH HIREDATE=(SELECT MIN(HIREDATE) FROM EMP) CONNECT BY MGR=PRIOR EMPNO;

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.field1);

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 DUAL

Read More

Second Largest Number

Second Largest Number

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

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

Read More

Difference between procedure and function.

Difference between procedure and function.
A function always returns a value, while a procedure does not. When you call a function you must always assign its value to a variable.

Read More

Difference between database triggers and form triggers?

Difference between database triggers and form triggers?
Database triggers are fired whenever any database action like INSERT, UPATE, DELETE, LOGON LOGOFF etc occurs. Form triggers on the other hand are fired in response to any event that takes place while working with the forms, say like navigating from one field to another or one block to another and so on.

Read More

Differentiate between TRUNCATE and DELETE.

Differentiate between TRUNCATE and DELETE.
The Delete command will log the data changes in the log file where as the truncate will simply remove the data without it. Hence Data removed by Delete command can be rolled back but not the data

Read More

Function Purity Level

Function Purity Level
The function purity level defines what structures the function reads or modifies.Purity levels
WNDS - Writes No Database State i.e. Function does not modify any database tables (No DML)
RNDS - Reads No Database State i.e. Function does not read any tables (No select)
WNPS - Writes No Package State i.e. Function does not modify any packaged variables (packaged variables are variables declared in a package specification)
RNPS - Reads No Package State i.e. Function does not read any packaged variables
Syntax:
PRAGMA RESTRICT_REFERENCES (function_name, WNDS [, WNPS] [, RNDS] [, RNPS])Tells the compiler the purity level you believe your function meets or exceeds

Read More

Subquery Factoring

Subquery Factoring
Subquery factoring, also known as the WITH clause, provides a convenient and flexible way to define subquries and in-line views. The primary purpose of subquery factoring is to reduce repeated table accesses by generating temporary data sets during subquery execution.

Read More

Difference between Post database commit and Post-form commit

Difference between Post database commit and Post-form commit
Post-form commit fires once during the post and commit transactions process, after the database commit occurs. The post-form commit trigger fires after inserts, updates and deletes have been posted to the database but before the transactions have been finalized in, issuing the command. The Post-database commit trigger fires after oracle forms issues the commit to finalized transactions.

Read More

altering user

SQL> ALTER USER username ACCOUNT LOCK;SQL> ALTER USER username ACCOUNT UNLOCK;To change the password for a user:SQL> alter user username identified by new_password;All at once:
-- Expire, forces to change the password at first loginSQL> alter user username identified by password account lock password expire;

Read More