TO_DATE Function in SQL

How to find the names of employees who joined between 10th and 20th of August 2008.

SELECT empName FROM emp WHERE joining_date between to_date ('2008/08/10', 'yyyy/mm/dd')AND to_date ('2008/08/20', 'yyyy/mm/dd');

Link-1 and Link-2 helps us understand, to_date and to_char functions better.

ORDER BY TO_DATE(TO_CHAR(APPROVEDBYDATE,'MM/DD/YYYY'),'MM/DD/YYYY')

How to sort null values first using ORDER BY

This link helps to understand the sorting of null values better.


CASE WHEN(rd.riskdistributionflag LIKE 'Y' AND rd.fundingstatustypekey=2)
THEN 'RDU'
WHEN (rd.riskdistributionflag LIKE 'Y' AND rd.fundingstatustypekey=1)
THEN 'RDF'
END AS RDUF,

ORDER BY nvl(RDUF,'AAA')

The above query ensures the NULL rows are ordered first and then the data comes later.

This link helps to understand the sorting order

Finding Duplicates in a table

Here's a handy query for finding duplicates in a table. Suppose you want to find all email addresses in a table that exist more than once:

SELECT email,
COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

You could also use this technique to find rows that occur exactly once:

SELECT email
FROM users
GROUP BY email
HAVING ( COUNT(email) = 1 )

ROWNUM

Here is the link that explains about ROWNUM concept.

Functions in Oracle

Here is the link to Oracle functions like

1. Trim
2. Length
3. Replace
4. Substr
5. Upper
6. Coalesce
7. Decode
8. Nvl/ Nvl2
9. To_Date
10 To_Number

UNION keywork

Here is a tutorial on how to use union in sql queries.

Sequence Number in J2EE/Oracle web app

In a medium to large scale web app we have different modules with relevant tables.
In those tables we have sequence number which has to be filled in automatically. So to create this kind of auto generated numbers we use Sequences in Oracle.


public static final Class CLASS_NAME = PKSequenceUtility.class;
public static Integer populateApplnData(String tableName){
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
int seqNum = 0;
try {
con = DBUtility.getConnection();
// Constants.SCHEMA_NAME+".sq_"+tableName+".nextval from dual ==> "own_abc.sq_requesttable.nextval from dual"
String sql = "select "+Constants.SCHEMA_NAME+".sq_"+tableName+".nextval from dual";
ps = con.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
seqNum = rs.getInt(1);
}
} catch (SQLException sqle) {
sqle.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
finally
{
DBUtility.closeResultSet(rs);
DBUtility.closeStatement(ps);
DBUtility.releaseConnection(con);
}
return (seqNum);
}