DB/ Database notes (sql tips)

JDBC Format 

Sybase  (jconn3.jar)
jdbc:sybase:Tds:server:1050


Oracle
Use Service Name (format /)

jdbc:oracle:thin:@server:1521/fmwa

user SID (format :)

jdbc:oracle:thin:@bggwrsoa12:1521:fmwa

Limit Rows

// mysql
select col from tbl limit 20;


//Sybase / SqlServer

select top 10 * from people

 //Also
SET ROWCOUNT 10 // use SET ROWCOUNT 0 to turn off

// Oracle
select col from tbl where rownum <=20;

Using rank()

This query will return the top 10 rows..
select col1,col2 from (select rank() over (order by col1) r , col1,col2 from TABLE) where r<11 For bottom 10 rows use select col1,col2 from (select rank() over (order by col1 DESC) r , col1,col2 from TABLE) where r<11

Select unique

//Sybase / SqlServer
select distinct col from table

Oracle

MySql

Outer Join

// Oracle left outer Join
select * from Customer, Order where Order.customerId (+) = Customer.id
//right join
select * from Customer, Order where Order.customerId = Customer.id (+)

Backup

Mysql

>mysqldump --host vc2c09mmk0326.fmr.com --port=3306 -u datasift2 -p --set-gtid-purged=OFF datasift2  > datasift.sql

• This will create a sql with all the tables. Note the --set-gtid-purged=OFF is required on newer versions of the client. If you are running on vc2c09mmk0326 it is probably not required.
• Note the sql will be over 100MB in size

Oracle drop all tables

BEGIN
   FOR cur_rec IN (SELECT object_name, object_type
                     FROM user_objects
                    WHERE object_type IN
                             ('TABLE',
                              'VIEW',
                              'PACKAGE',
                              'PROCEDURE',
                              'FUNCTION',
                              'SEQUENCE'
                             ))
   LOOP
      BEGIN
         IF cur_rec.object_type = 'TABLE'
         THEN
            EXECUTE IMMEDIATE    'DROP '
                              || cur_rec.object_type
                              || ' "'
                              || cur_rec.object_name
                              || '" CASCADE CONSTRAINTS';
         ELSE
            EXECUTE IMMEDIATE    'DROP '
                              || cur_rec.object_type
                              || ' "'
                              || cur_rec.object_name
                              || '"';
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (   'FAILED: DROP '
                                  || cur_rec.object_type
                                  || ' "'
                                  || cur_rec.object_name
                                  || '"'
                                 );
      END;
   END LOOP;
END;
/

// HSQL
from Customer left join Order where Order.customerId = Customer.id

Could only get this working by explicitly linking the join in the hbm file, and marking it as optional e.g.

Need some more testing to see if this can be done without updating the hbm file

<class name="AdjustmentCode" table="adjustment_code">
<id name="id" column="adjustment_code" type="string">
<generator class="native">
<property name="description" column="description" type="string">
<property name="adjustmentCode" column="adjustment_code" type="string" insert="false" update="false">
<join table="adjustment_message">optional="true">
<key column="adjustment_code">
</join>
<class>
Insert if row not Exists

// MySql
if not exists (select * from url where url = ...)
insert into url...

//Oracle (where not exists)
insert into <table> (<row1>, <row2>)
select <value1>, <value2> from dual
where not exists (
select * from <table> where <col1> = <value1>)

//SQL Server
if not exists (select * from url where url = ...)
insert into url...
Date Operations

GetDate
//MySql
DATE()  see (http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html)

//Oracle
SYSDATE

// Sql Server
getDate()


To Date
//Oracle
to_date('2009-01-01','YYYY-MM-DD')
to_date('2009-05-19 18:32','YYYY-MM-DD HH24:MI')

http://www.dba-oracle.com/f_to_date.htm

Detecting Duplicates (group by criteria)
Say you want to detect duplicates (instances of more than one value) in a column. This is how to do it

select dupCol, count(dupCol) from table group by dupCol having count(dupCol)>1

Listing Constrainsts
Oracle
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'myTable';