Retrieve few minutes older records from SQL in Oracle

You can use following SQL syntax to retrieve data in 5 minute interval in Oracle database. 

select * FROM test_table where added_time <= systimestamp - numtodsinterval(5,'MINUTE')

Above SQL returns values which are older than 5 minutes  than system time in added_time column.

Informix query optimizer

 

The Informix Query Optimizer

• The query optimizer attempts to determine the most efficient way to execute a SQL statement

 • Examines every possible method to implement the query and selects the least costly method

• It is Dynamic, so when data changes the same SQL can select a better path

How Optimizes the SQL statement to determine the best access method

– Which table to read first….

– Which index to use first…

– Which filter to use first…

 

How to Turn ON Query Explain? To See the SQL Optimizer Query Plan

After executes below queries with “SET EXPLAIN” it is creates a output file called “SQL_EXPLAIN.OUT”

Method#1

                SET EXPLAIN ON -

                                Display the query plan that optimizer chooses, then execute the query.

                                Example:

                                                set explain on;

                                                select cust_name,cust_addrs from customer where cust_code=”C001”;

Method#2

                SET EXPLAIN ON FILE TO [“path/to/filename”]

                                Save the query plan into a specific file also execute the query.

Can useful when programming unit testing.

Method#3

                SET EXPLAIN ON AVOID_EXECUTE

                                Display the query plan that optimizer chooses, but do not execute the query.

Can useful when a query takes hours to run/for time consuming queries.

“SQL_EXPLAIN.OUT” file results explanation

A. Show SQL/SELECT statement.

B. Query cost

C. Number of Rows Expected to return

D. Order to access tables during execution.

E. Access Plan:

                SEQUENTIAL Scan - read all rows in sequence

                INDEX PATH - Scan 1 or more indexes

                AUTO INDEX PATH: Optimizer suggest to create an indexes.

F. Query Statistics

Shows tables map, estimated row count and estimated query costs step by step.

                This will helps to find out in which part of the query cost is high.

                So can change the query based on results output.

Retrieve few minutes older records from SQL in Informix

You can use following SQL to retrieve records older than 15 minutes in Informix. init_time is in datetime format.  

select * from table_name where init_time > (current - 15 units minute)

Timestamp format in MySQL

This inbuilt MySQL command can be used to cast timestamp values as required.

SELECT DATE_FORMAT('field_name', '%Y-%m-%d %H:%i:%s') FROM 'table_name'

Above SQL will format timestamp in "YYYY-MM-DD HH:MM:SS" format. If you require only date you can format timestamp as following SQL.

SELECT DATE_FORMAT('field_name', '%Y-%m-%d') FROM 'table_name'

Add number of days to timestamp in MySQL

Following DATE_ADD('field_name', INTERVAL) can be used to increase the number of days in the 'field_name' in MySQL databases. 

SELECT SQL

SELECT DATE_ADD('field_name', INTERVAL 2 DAY)FROM 'table_name';

Update SQL

UPDATE 'table_name'
SET field_name= DATE_ADD('field_name' , INTERVAL 2 DAY)
WHERE 'id' = 1;

FIND_IN_SET vs IN SQL commands

IN
This keyword is used to get data values equals to IN clause values.

Example :-

Select * from test_table where field_1 IN ('123','abc','873','345')

Above SQL returns values equals to 123,abc,873,345 in field_1.

FIND_IN_SET

Suppose you have a String values like "123,abc,873,345". You can use FIND_IN_SET to get IN clause supported string as '123','abc','873','345'.

Select * from test_table where field_1 FIND_IN_SET (',','123,abc,873,345')

This SQL is also return above IN SQL results.

Check highlighted values.

Run SQL script in Background in MySQL server

Following command can be used to run SQL script in background in MySQL server.

mysql --user=sujith --pass=123suji --database=postdb --host=localhost script_file results.out &

This will return output of script  to result.out file.

Grouping data by SQL expression

Following SQL syntax can be used to group data using expression.

select date(added_time) as a,count(*) from test_table group by a                
This will group data by date and display its count. It doesn't work grouping with expression, So need to use alias naming for expression.

Find 12 months previous date

Following SQL commands can be used to find the date before 12 months

In Informix,
select * from table where connected_date  < today - (12 UNITS MONTH)

In MySQL,

select * from table where added_date>DATE_ADD(TODAY,INTERVAL -12 MONTH)

SQL concat string in informix

Suppose database contains mobile_no field which has records like "0715355403".....

If you want to remove front "0" and add "94", you can use the following SQL command.

select  "94"||mobile_no[2,10] from sms_request

This is can used in SQL where clause, too.

Copy data from one table to another table

You can copy data from one table to another table using following SQL command. This worked fine in informix database.

insert into TableA (field1, field2,field3)
select  field1, field2,field3 from TableB where added_on=today

MySQL days between SQL Syntax (Between Days)

In MySQL, there is a special ways to get data between two days.

select * from ret_bonus where DATE(start_time) between date_sub(curdate(), interval 5 day) and CURDATE()

Above SQL returns data between today and 5days before today. 

date_sub(curdate(), interval 5 day)

This MySQL command returns 5days before today. 

SQL Joins Graphical Illustration

http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

Informix Database LOCK Transaction for UPDATE

In some cases, we have to lock records for update. Below code segments can be used for locking table and update desired record in informix database server.

• statement.executeUpdate("Begin Work");
• statement.executeUpdate("set transaction isolation level serializable");

Above code must be used be before the SELECT statement execution which must be UPDATE.

• String sql_1="select * from table where name='sujith' FOR UPDATE";

Execute the sql and proceed with the business logic. When you want to UPDATE the table do the following.

• String sql_2=update table set name="delp" where name="sujith";

Execute the UPDATE statement, like above statement.

Then use below command for commiting transaction to database.

• statement.executeUpdate("commit");

Mysql Current date & time

Use the Mysql server curdate() and curtime() functions to get current date & time values from the system.
Below SQL demos, the usage of the functions.

insert into test(cur_date,cur_time) values(curdate(),curtime());

Mysql Triggers Example

Mysql triggers are like schedulers run on table changes.  When INSERT, DELETE, UPDATE command executions, these triggers are executed automatically.

Below is a sample of SQL commands to create and test Triggers when insert happens to "test1" table.

Create below four tables and insert values into "test3" & "test4" tables.

Trigger

This Trigger is executed when insert happens into "test1" table. It gets "test1.a1" value and insert it into "test2" table. And, delete it from "test3" table. Update  "test4" b4 value.

Create Tables

CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0
);

Create Trigger

delimiter |

CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END;
|

Insertions

delimiter ;

INSERT INTO test3 (a3) VALUES
(NULL), (NULL), (NULL), (NULL), (NULL),
(NULL), (NULL), (NULL), (NULL), (NULL);

INSERT INTO test4 (a4) VALUES
(0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

Executes following SQL to run the Trigger.

INSERT INTO test1 VALUES (1), (3), (1), (7), (1), (8), (4), (4);

limit Resultset size in informix

Use the following SQL to limit the size of the result set in informix. 

SELECT FIRST 1 * FROM orders

Encrypt, Decrypt password in Mysql

Inserting password encrypted to database using the following sql.

INSERT INTO PASSWORD(pass) VALUES(DES_ENCRYPT('user password')); 

This SQL will encrypt the user inserted password to a encrypted text. In table level, password is unreadable.

Instead of DES_ENCRYPT() method you can come up with your own function.

Selecting password from database to a string.

SELECT DES_DECRYPT(pass) FROM PASSWORD ;

This SQL will return user password as user inserted.

sql for monthly,yearly report

select year(added_on),month(added_on),count(*) from table_name
group by 1,2
order by 2

This will produce a resultset for monthly yearly reports.

Ex :-

Year Month Records
------ ------ --------
2009 5 14416
2009 6 788
2009 7 15413
2009 8 16091

Mysql today in where clause

select * from table where added_on=date(now());

To use this sql you must have used "date" datatype for your table column.
select * from table where added_at=now();

This works fine for "time" columns too .