Java environment setup

As of this writing, Java 6 update 6 is the latest version. You can find it here.
Execute it and follow the instructions to install it.

First, Set up JAVA_HOME environment variable pointing to JDK directory. By default, on windows, its going to be C:\Program Files\Java\jdk1.6.0_06. You can set it up ..

My Computer (Right Click) -> Properties -> Advanced -> Environment Variables -> New and then set up variable name and value. Here is the snap shot..



Same way extend the PATH variable with the bin directory of jdk.

My Computer (Right Click) -> Properties -> Advanced -> Environment Variables -> Path and then EDIT. Add ";C:\Program Files\Java\jdk1.6.0_06\bin" at the end of the path value.

Also, you can extend the path as shown below, but its just temporary for that session.

set PATH=%PATH%;"C:\Program Files\Java\jdk1.6.0_06\bin"

To verify successful installation, open new command line window and type "javac -version" and "java -version".

Get back to Developer Digest

Incorrect Rownum in Oracle

The following query gives incorrect Rownum. The expectation is to count the rows after ordering has been done. But this query counts before ordering.

SELECT ROWNUM rowno2, a.*, pageBase FROM ramesh.EMPLOYEE  a, (
    SELECT (rowno - MOD(rowno,5)) AS pageBase FROM (
        SELECT ROWNUM rowno, ID FROM (
            SELECT JOINED_DATE, ID FROM ramesh.EMPLOYEE
        ) WHERE (JOINED_DATE > TO_DATE('2003-04-12' , 'yyyy-mm-dd')) ORDER BY ID DESC
    ) WHERE (ID = '17') 
) WHERE (JOINED_DATE > TO_DATE('2003-04-12' , 'yyyy-mm-dd')) ORDER BY ID DESC

ROWNO2	ID	FIRST_NAME	LAST_NAME	JOINED_DATE	DEPT	ROLE	PAGEBASE
18	29	FN29	LN29	2003-04-29 00:00:00.0	DEP29	ROLE29	10
17	28	FN28	LN28	2003-04-28 00:00:00.0	DEP28	ROLE28	10
16	27	FN27	LN27	2003-04-27 00:00:00.0	DEP27	ROLE27	10
15	26	FN26	LN26	2003-04-26 00:00:00.0	DEP26	ROLE26	10
14	25	FN25	LN25	2003-04-25 00:00:00.0	DEP25	ROLE25	10
13	24	FN24	LN24	2003-04-24 00:00:00.0	DEP24	ROLE24	10
12	23	FN23	LN23	2003-04-23 00:00:00.0	DEP23	ROLE23	10
11	22	FN22	LN22	2003-04-22 00:00:00.0	DEP22	ROLE22	10
10	21	FN21	LN21	2003-04-21 00:00:00.0	DEP21	ROLE21	10
9	20	FN20	LN20	2003-04-20 00:00:00.0	DEP20	ROLE20	10
8	19	FN19	LN19	2003-04-19 00:00:00.0	DEP19	ROLE19	10
7	18	FN18	LN18	2003-04-18 00:00:00.0	DEP18	ROLE18	10
6	17	FN17	LN17	2003-04-17 00:00:00.0	DEP17	ROLE17	10
5	16	FN16	LN16	2003-04-16 00:00:00.0	DEP16	ROLE16	10
4	15	FN15	LN15	2003-04-15 00:00:00.0	DEP15	ROLE15	10
3	14	FN14	LN14	2003-04-14 00:00:00.0	DEP14	ROLE14	10
2	13	FN13	LN13	2003-04-13 00:00:00.0	DEP13	ROLE13	10
1	4	FN4	LN4	2004-01-20 00:00:00.0	DEP4	ROLE4	10

Get back to Developer Digest

Oracle - Creating Test Data

Here are the queries that I used to create the EMPLOYEE test data..

You need a tablespace to create tables in it. Either you can use you existing table space or create a new one like below..

create tablespace rameshdb
       datafile 'rameshdb.dat' size 500k reuse
       autoextend on next 500k maxsize UNLIMITED;

The following query creates user, ramesh.

create user ramesh
    identified by ramesh
    default tablespace rameshdb
    quota UNLIMITED on rameshdb
    temporary tablespace temp
    quota 5m on system;

The following query grants all permissions to the user, ramesh.

grant all privileges to ramesh;

This is the query to create a table called EMPLOYEE which has been

create table ramesh.EMPLOYEE (
       ID       number(6) not null,
       FIRST_NAME           varchar2(256) not null,
       LAST_NAME            varchar2(256) null,
       JOINED_DATE          date null,
       DEPT      varchar2(256) null,
       ROLE      varchar2(256) null,
       primary key          (ID)
);

Here is the sql to insert some test data

INSERT INTO ramesh.EMPLOYEE
VALUES (1, 'Ramesh', 'Dara', to_date('2000/01/04', 'yyyy/mm/dd'),'Software','Developer');

Get back to Developer Digest

PageBase and Rownum Data

SELECT @rowno2_:=@rowno2_+1 rowno2, e3.*, pageBase FROM EMPLOYEE e3, (              
    SELECT @pageBase:=(rowno - MOD(rowno,5)) pageBase, @rowno2_:=0 FROM (
        SELECT @rowno_:=@rowno_+1 rowno, e.ID FROM (
            (SELECT @rowno_:=0) r, EMPLOYEE e
        ) WHERE JOINED_DATE > ('2003-04-12') ORDER BY ID DESC
    ) e2 WHERE ID = 17
) e4 WHERE JOINED_DATE > ('2003-04-12') ORDER BY ID DESC

rowno2	ID	FIRST_NAME	LAST_NAME	JOINED_DATE	DEPT	ROLE	pageBase
1	29	FN29	LN29	2004-04-29	DEP29	ROLE29	10.0
2	28	FN28	LN28	2004-04-28	DEP28	ROLE28	10.0
3	27	FN27	LN27	2004-04-27	DEP27	ROLE27	10.0
4	26	FN26	LN26	2004-04-26	DEP26	ROLE26	10.0
5	25	FN25	LN25	2004-04-25	DEP25	ROLE25	10.0
6	24	FN24	LN24	2004-04-24	DEP24	ROLE24	10.0
7	23	FN23	LN23	2004-04-23	DEP23	ROLE23	10.0
8	22	FN22	LN22	2004-04-22	DEP22	ROLE22	10.0
9	21	FN21	LN21	2004-04-21	DEP21	ROLE21	10.0
10	20	FN20	LN20	2004-04-20	DEP20	ROLE20	10.0
11	19	FN19	LN19	2003-04-19	DEP19	ROLE19	10.0
12	18	FN18	LN18	2003-04-18	DEP18	ROLE18	10.0
13	17	FN17	LN17	2003-04-17	DEP17	ROLE17	10.0
14	16	FN16	LN16	2003-04-16	DEP16	ROLE16	10.0
15	15	FN15	LN15	2003-04-15	DEP15	ROLE15	10.0
16	14	FN14	LN14	2003-04-14	DEP14	ROLE14	10.0
17	13	FN13	LN13	2003-04-13	DEP13	ROLE13	10.0

Get back to Developer Digest

Rownum and ID Data

SELECT @rowno_:=@rowno_+1 rowno, e.ID from (
    (SELECT @rowno_:=0) r, EMPLOYEE e
) WHERE JOINED_DATE > ('2003-04-12') ORDER BY ID DESC

rowno	ID
1	29
2	28
3	27
4	26
5	25
6	24
7	23
8	22
9	21
10	20
11	19
12	18
13	17
14	16
15	15
16	14
17	13

Get back to Developer Digest

Sample Employee Data

ID	FIRST_NAME	LAST_NAME	JOINED_DATE	DEPT	ROLE
1	Ramesh	Dara	2000-01-20	Software	Developer
2	FN2	LN2	2001-01-20	DEP2	ROLE2
3	FN3	LN3	2002-01-20	DEP3	ROLE3
4	FN4	LN4	2002-01-21	DEP4	ROLE4
5	FN5	LN5	2002-05-21	DEP5	ROLE5
6	FN6	LN6	2002-06-21	DEP6	ROLE6
7	FN7	LN7	2002-07-21	DEP7	ROLE7
8	FN8	LN8	2002-08-21	DEP8	ROLE8
9	FN9	LN9	2002-09-21	DEP9	ROLE9
10	FN10	LN10	2003-04-10	DEP10	ROLE10
11	FN11	LN11	2003-04-11	DEP11	ROLE11
12	FN12	LN12	2003-04-12	DEP12	ROLE12
13	FN13	LN13	2003-04-13	DEP13	ROLE13
14	FN14	LN14	2003-04-14	DEP14	ROLE14
15	FN15	LN15	2003-04-15	DEP15	ROLE15
16	FN16	LN16	2003-04-16	DEP16	ROLE16
17	FN17	LN17	2003-04-17	DEP17	ROLE17
18	FN18	LN18	2003-04-18	DEP18	ROLE18
19	FN19	LN19	2003-04-19	DEP19	ROLE19
20	FN20	LN20	2004-04-20	DEP20	ROLE20
21	FN21	LN21	2004-04-21	DEP21	ROLE21
22	FN22	LN22	2004-04-22	DEP22	ROLE22
23	FN23	LN23	2004-04-23	DEP23	ROLE23
24	FN24	LN24	2004-04-24	DEP24	ROLE24
25	FN25	LN25	2004-04-25	DEP25	ROLE25
26	FN26	LN26	2004-04-26	DEP26	ROLE26
27	FN27	LN27	2004-04-27	DEP27	ROLE27
28	FN28	LN28	2004-04-28	DEP28	ROLE28
29	FN29	LN29	2004-04-29	DEP29	ROLE29

Get back to Developer Digest

My SQL - Creating Test Data

MySQL: Creating Test Data

Here are the queries that I used to create testbed for framing and testing my MySQL query..

To create database, named "ramesh",

CREATE DATABASE ramesh

Then you need to use that database to frame queries on the tables in that database

USE ramesh

Created following EMPLOYEE table..

create table EMPLOYEE (
ID INT not null,
FIRST_NAME varchar(256) not null,
LAST_NAME varchar(256) null,
JOINED_DATE date null,
DEPT varchar(256) null,
ROLE varchar(256) null,
primary key (ID)
);

And then created data ..

INSERT INTO EMPLOYEE
VALUES (1, 'Ramesh', 'Dara', '2000-01-20','Software','Developer');

INSERT INTO EMPLOYEE
VALUES (2, 'FN2', 'LN2', '2001-01-20','DEP2','ROLE2'),
(3, 'FN3', 'LN3', '2001-01-23','DEP3','ROLE3'),
(4, 'FN4', 'LN4', '2004-01-20','DEP4','ROLE4');

The IDs that I created are nothing but Serial Numbers and the sample data followed that number, which eases verification.

Here is Sample MySQL Employee Data

Get back to Developer Digest