MySQL/Oracle Database Examples

How to find Difference b/w dates
####################################
select WSM_RMS_MSISDN_TYPE, COUNT(*) from WSM_REMINDER_SERVICE_LOG where
WSM_RMS_SEND_DT >= to_date('30-11-2008 00:00:00','DD-MM-YYYY HH24:MI:SS') and
WSM_RMS_SEND_DT <= to_date('02-12-2008 23:59:59','DD-MM-YYYY HH24:MI:SS') group by WSM_RMS_MSISDN_TYPE;

SQL> create table datemath (start_date date,end_date date);
SQL> insert into datemath (start_date,end_date)
values ( to_date('01-OCT-2006 13:00:00', 'DD-MON-YYYY HH24:MI:SS'), to_date('01-OCT-2006 14:00:00','DD-MON-YYYY HH24:MI:SS') );
SQL> commit;

SQL> select to_char(start_date,'DD-MON-YYYY HH24:MI:SS') START_DATE ,to_char(end_date,'DD-MON-YYYY HH24:MI:SS') END_DATE
     from datemath;
START_DATE END_DATE
-------------------- --------------------
01-OCT-2006 13:00:00 01-OCT-2006 14:00:00


SQL> select to_char(start_date,'DD-MON-YYYY HH24:MI:SS') START_DATE ,to_char(end_date,'DD-MON-YYYY HH24:MI:SS') END_DATE
from datemath;

START_DATE END_DATE
-------------------- --------------------
01-OCT-2006 13:10:00 10-OCT-2006 14:00:00


****************************************************************************************************************
SQL> select to_char( start_date, 'dd-mon-yyyy hh24:mi:ss' ) start_date,
2 trunc( end_date-start_date ) days,
3 trunc( mod( (end_date-start_date)*24, 24 ) ) hours,
4 trunc( mod( (end_date-start_date)*24*60, 60 ) ) Minutes,
5 trunc( mod( (end_date-start_date)*24*60*60, 60 ) ) Seconds,
6 to_char( end_date, 'dd-mon-yyyy hh24:mi:ss' ) end_date
7* from datemath;

START_DATE           Days  HOURS  MINUTES   SECONDS END_DATE
-------------------- ----- -----  --------  ------  ----------
01-oct-2006 13:10:00 9     0      50        0       10-oct-2006 14:00:00
****************************************************************************************************************

sql> select end_date - start_date from datemath;
END_DATE - START_DATE
-------------------
.041666667

SQL> select (end_date-start_date)*24 hours from datemath;
HOURS
----------
1

Multiply by 24 and again by 60 to get the number of minutes

SQL> select (end_date-start_date)*24*60 minutes from datemath;
MINUTES
----------
60

Multiply by 24 and again by 60 and again by 60 to get the number of seconds

SQL> select (end_date-start_date)*24*60*60 seconds from datemath;
SECONDS
----------
3600


MySQL/Oracle Statements
##########################

Select * from eng_traslate where PROG_ID = '9041' and rownum > 0 and rownum <=2;

select * from (select WSM_RMS_MSISDN, COUNT(*) from WSM_REMINDER_SERVICE_LOG
                where WSM_RMS_SEND_DT >= to_date('30-11-2008 00:00:00','DD-MM-YYYY HH24:MI:SS') and
                      WSM_RMS_SEND_DT <= to_date('02-12-2008 23:59:59','DD-MM-YYYY HH24:MI:SS')
                group by WSM_RMS_MSISDN
              )
where ROWNUM >0 and ROWNUM <= 2; 


select WSM_RMS_MSISDN_TYPE as op_type, WSM_RMS_STATUS as status, COUNT(*) as CNT
from WSM_REMINDER_SERVICE_LOG
where WSM_RMS_SEND_DT >= to_date('30-11-2008 00:00:00','DD-MM-YYYY HH24:MI:SS') and
      WSM_RMS_SEND_DT <=  to_date('02-12-2008 23:59:59','DD-MM-YYYY HH24:MI:SS')
group by WSM_RMS_MSISDN_TYPE, WSM_RMS_STATUS
order by op_type;


sql> CREATE TABLE DATETEST(DATECOL DATE);
sql> INSERT INTO DATETEST(DATECOL) VALUES (TO_DATE('01-JAN-2006 08:18:23','DD-MON-YYYY HH24:MI:SS'));
sql> commit;

- The default dispaly of DATE column is 'DD-MON-YY'
- U can change the format of the default dispaly of DATE easily.

SQL> select datecol from datetest;
DATECOL
---------
01-JAN-06

SQL> alter session set nls_date_format='DD/MON/YYYY';
Session altered.

SQL> select datecol from datetest;
DATECOL
-----------
01/JAN/2006



SQL> create or replace view text2date as select to_date('05-JAN-2006','DD-MON-YY') text2date111 from dual;
View created.

SQL> desc text2date
Name Null? Type
-----------------
TEXT2DATE111 DATE


SQL> select text2date111 from text2date;
TEXT2DATE111
------------
05-JAN-06

SQL> select to_char(text2date,'Day') text2date from text2date;
TEXT2DATE111
-------------
Thursday


Enum:
#######
CREATE TABLE employee_person (
    id int unsigned not null primary key,
    address varchar(60),
    phone int,
    email varchar(60),
    birthday DATE,
    sex ENUM('M', 'F'),
    m_status ENUM('Y','N'),
    s_name varchar(40),
    children int
);

Grant Previliges
#####################

create database atmail123;                                              

mysql> grant all on atmail123.* to root@112.118.61.125 identified by 'mp0d';
mysql> grant all on atmail123.* to webmail@112.119.600.115 identified by 'webmail';
mysql> grant all on atmail123.* to root@114.135.61.102 identified by 'mp0d';