General Database Queries And Practices

GENERAL DATABASE QUERIES AND PRACTICES

The following query retrieves "2" highest paid employees FROM each Department:

SELECT deptno, empno, sal

FROM emp e

WHERE 2 > ( SELECT COUNT(e1.sal)

FROM emp e1

WHERE e.deptno = e1.deptno AND e.sal < e1.sal

)

ORDER BY 1,3 DESC;

SELECT * FROM EMP A

WHERE &N > ( SELECT COUNT(DISTINCT SAL) FROM EMP B WHERE B.SAL>A.SAL)

ORDER BY A.SAL DESC;

SELECT * FROM

(SELECT * FROM emp ORDER BY salary DESC) WHERE ROWNUM <3

Query that will display the total no. of employees, and of that total the number who were hired in 1980, 1981, 1982, and 1983. Give appropriate column headings.

I am looking at the following output. We need to stick to this format.

Screenshot_142

SELECT COUNT (*), COUNT(DECODE(TO_CHAR (hiredate, 'YYYY'),'1980', empno))

"1980",

COUNT (DECODE (TO_CHAR (hiredate, 'YYYY'), '1981', empno))

"1981",

COUNT (DECODE (TO_CHAR (hiredate, 'YYYY'), '1982', empno))

"1982",

COUNT (DECODE (TO_CHAR (hiredate, 'YYYY'), '1983', empno))

"1983"

FROM emp;

Query for listing Deptno, ename, sal, SUM(sal in that dept) :

SELECT a.deptno, ename, sal, (SELECT SUM(sal) FROM emp b WHERE a.deptno =b.deptno)

FROM emp a

ORDER BY a.deptno;

OUTPUT :

=======
DEPTNOENAMESALSUM (SAL)
=============================
10KING500011725
30BLAKE285010900
10CLARK245011725
10JONES297511725
30MARTIN125010900
30ALLEN160010900
30TURNER150010900
30JAMES95010900
30WARD275010900

20SMITH800033000
20SCOTT300033000
20MILLER2000033000

Interested in mastering Microstrategy Training? Enroll now for FREE demo on Microstrategy Training.

Create a matrix query to display the job, the salary for that job based on department number, and the total salary for that job for all departments, giving each column an appropriate heading.

The output is as follows - we need to stick to this format :

JobDept 10Dept 20Dept 30
Total
----------------------------------------------------
--------
ANALYST6000
6000
CLERK13001900950
4150
MANAGER245029752850
8275
PRESIDENT5000

5000
SALESMAN5600
5600

SELECT job "Job", SUM (DECODE (deptno, 10, sal)) "Dept 10",

SUM (DECODE (deptno, 20, sal)) "Dept 20",

SUM (DECODE (deptno, 30, sal)) "Dept 30",

SUM (sal) "Total"

FROM emp GROUP BY job ;

 4th Top Salary of all the employees 

SELECT DEPTNO, ENAME, SAL

FROM EMP A

WHERE

3 = (SELECT COUNT(B.SAL) FROM EMP B WHERE A.SAL < B.SAL) ORDER BY SAL DESC;

SELECT * FROM EMP A

WHERE &N-1 = (SELECT COUNT (DISTINCT SAL) FROM EMP B WHERE B.SAL>A.SAL);

Alternate

SELECT ename, deptno, sal

ROM (SELECT * FROM emp ORDER BY sal DESC)

WHERE ROWNUM < N;

 Retrieving the 5th row FROM a table :

ELECT DEPTNO, ENAME, SAL

FROM EMP

WHERE ROWID = (SELECT ROWID FROM EMP

WHERE ROWNUM <= 5 MINUS

SELECT ROWID FROM EMP WHERE ROWNUM < 5

Display the alternate row from the table.

select rownum,empno,ename from emp

group by rownum,empno,ename

having mod(rownum,2)=0;

OR

select * from emp

where rowid in (select decode(mod(rownum,2),0,rowid) from emp)

OR

For Even Rownumber

Select * from emp

Where (rowid,0) in (select rowid, mod(rownum,2) from emp)

For Odd Rownumber

Select * from emp

Where (rowid, 1) in (select rowid, mod(rownum,2) from emp)

Tree Query :

Name Null? Type

-------------------------------------------------------------------

SUB NOT NULL VARCHAR2(4)

SUPER VARCHAR2(4)

PRICE NUMBER(6,2)

SELECT sub, super

FROM parts

CONNECT BY PRIOR sub = super

START WITH sub = 'p1';

Eliminate duplicates rows in a table :

DELETE FROM table_name A

WHERE ROWID > ( SELECT min(ROWID) FROM table_name B WHERE A.col = B.col);

OR

DELETE FROM table_name A

WHERE ROWID < ( SELECT max(ROWID) FROM table_name B WHERE A.col =

B.col);

Displaying EVERY 4th row in a table : (If a table has 14 rows, 4,8,12 rows will be selected)

SELECT *FROM emp

WHERE (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,4)

FROM emp);

ALTERNATE

SELECT * FROM TB_CNR014_DIM_BRAND

WHERE ROWID IN (SELECT DECODE(MOD(BRAND_ID, 4), 0, ROWID) FROM

TB_CNR014_DIM_BRAND)

Learn more about Microstrategy Advanced Interview Questions in this blog post.

Top N rows FROM a table : (Displays top 9 salaried people)

SELECT ename, deptno, sal

FROM (SELECT * FROM emp ORDER BY sal DESC)

WHERE ROWNUM < 10;

How does one count/sum RANGES of data values in a column? A value x will be between values y and z if GREATEST(x, y) = LEAST(x, z).

COUNT(DECODE(greatest(f1,59), least(f1,100), 1, 0)) "Range 60-100",

COUNT(DECODE(greatest(f1,30), least(f1, 59), 1, 0)) "Range 30-59",

COUNT(DECODE(greatest(f1,29), least(f1, 0), 1, 0)) "Range 00-29"

FROM  my_table

GROUP BY f2;

Correct Answer: Do not Consider 0

SELECT COUNT(*),

count(decode( greatest(field_3,20),least(field_3,49),1)) AS "Range 20-50",

count(decode( greatest(field_3,50),least(field_3,59),1)) AS "Range 50-60",

count(decode( greatest(field_3,60),least(field_3,69),1)) AS "Range 60-70",

count(decode( greatest(field_3,90),least(field_3,99),1)) AS "Range 90-100"

FROM test_mapping

For equal size ranges it migth be easier to calculate it with DECODE(TRUNC(value/range), 0, rate_0, 1, rate_1, ...).

SELECT ename "Name", sal "Salary",

DECODE( TRUNC(sal/1000, 0), 0, 0.0,

1, 0.1,

2, 0.2,

3, 0.3) "Tax rate"

FROM emp;

How does one count different data values in a column?

COL NAME DATATYPE

----------------------------------------

DNO NUMBER

SEX CHAR

SELECT dno, SUM(DECODE(sex,'M',1,0)) MALE,

SUM(DECODE(sex,'F',1,0)) FEMALE,

COUNT(DECODE(sex,'M',1,'F',1)) TOTAL

FROM t1

GROUP BY dno;

Query to get the product of all the values of a column :

SELECT EXP(SUM(LN(col1))) FROM srinu;

Query to display only the duplicate records in a table:

SELECT num

FROM satyam

GROUP BY num

HAVING COUNT(*) > 1;

Query for getting the following output as many number of rows in the table :

*

**

***

****

*****

SELECT RPAD(DECODE(temp,temp,'*'),ROWNUM,'*')

FROM srinu1;

Find the date of last Thursday of the Month

select next_day(last_day(sysdate)-7,’thursday’) from dual;

Update multiple row in using single update statement.

Update emp set sal=

Case job

When ‘CLERK’ then sal+500

When ‘SALESMAN’ then sal+600

When ‘MANAGER’ then sal+2000

Else sal

End;

OR

Update emp set sal=

Decode(job,’CLERK’,sal+200,’SALESMAN’,sal+300,’MANAGER’,sal+500);

Show the ename of all employees together with the number of completed months that they have been employed.

SELECT ENAME,TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12) YEAR,

MOD(TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)),12) MONTH FROM EMP

Display the following output:

DNOASALECOUNTENAMESAL JOB
---- ---------- --------------------  -------------------
102916.666673 CLARK 2450 MANAGER

KING5000 PRESIDENT
MILLER1300 CLERK
3614.85 SMITH4000 CLERK
ADAMS1100 CLERK
FORD3000 ANALYST
SCOTT6999 ANALYST
JONES2975 MANAGER
14006 ALLEN600 SALESMAN
BLAKE2850 MANAGER
MARTIN1250 SALESMAN
JAMES950 CLERK
TURNER1500 SALESMAN
WARD1250 SALESMAN

SOLUTION:

BREAK ON DNO ON ASAL ON ECOUNT SKIP 1

select d.deptno dno,avg(d.sal) asal,count(d.ename) ecount, e.ename,e.sal,e.job from emp d,emp e where d.deptno=e.deptno group by d.deptno,e.ename,e.sal,e.job

OR

SELECT DNO,ASAL,ECOUNT,E.ENAME,E.SAL,E.JOB FROM (SELECT DEPTNO DNO, AVG(SAL) ASAL,COUNT(ENAME) ECOUNT FROM EMP GROUP BY DEPTNO),EMP E WHERE DNO=E.DEPTNO;

Display the first and second higest sal from each deprtment

select deptno,max(sal) from emp group by deptno union

select deptno,max(sal) from (select deptno,sal from emp minus select deptno,max(sal) from emp group by deptno) group by deptno

or

select sal from emp e1 where &n>=(select count(distinct sal) from emp e2 where e1.sal<=e2.sal and e1.deptno=e2.deptno)

Date related Query.

Find out the current date:

Select sysdate from dual;

Find out the date of the next dateSelect sysdate+1 from dual;

Find out the one hour from noSelect to_char(sysdate+1/24,’hh’) from dual;

Find out the three hours from now

Select to_char(sysdate+3/24,’hh’) from dual;

Find out the half an hour from now

Select to_char(sysdate+1/48,’hh:mi’) from dual;

Find out the 10 minutes from now

Select to_char(sysdate+10/1440,’mi’) from dual;

Find out the 10 second from now

Select to_char(sysdate+10/86400,’SS’) from dual;

Find out the tomorrow at the midinight 12:00

Select to_char(trunc(sysdate+1),’dd-mm-yy-/hh:mi:ss’) from dual;

Find the date of tomorrow at 8:00 A.M.

Select to_char(trunc(sysdate+1)+8/24) from dual;

Find out the next Monday at 12:00 noon

Select next_day(trunc(sysdate),'Monday')+12/24 from dual;

Find out the first day of next month at 12 mid night

Select trunc(last_day(sysdate)+1) from dual;

Find out the first day of current month

Select last_day(add_months(sysdate,-1))+1 from dual;

Display the bytes occupied by each ename in the emp table.

Select vsize(ename) from emp;

Diplay the our age in days.

Select to_date(sysdate)-to_date('14-Aug-1980') Days from dual;

Display the our age in year.

select trunc(trunc(months_between(sysdate,'14-aug-80'))/12) year from dual;

Display the emp whose sal is greater than their manager.

select e.ename from emp e,emp m where e.mgr=m.empno and e.sal>m.sal;

Display the emp who are working in the same dept where his manager is working.

select e.ename from emp e,emp m where e.mgr=m.empno and e.deptno=m.deptno;

Display the ename whose manager is blake.

select e.ename from emp e,emp m where e.mgr=m.empno and m.ename=’BLAKE’;

Find the date for nearest Saturday after current date.

Select next_day(sysdate,’saturday’) from dual;

DDL Queries.

Rename the column name from the table.

alter table ddl rename column empno to eno;

Rename the table name

rename ddl to emp;

Drop the column from any table

alter table ttl drop column sal

Add the column in the table.

Alter table ttl add(sal number(4));

Add constraint in the table.

Alter table ttl add(empno number primary key);

Count the No. of Column from particular table.

select count(*) from cols where table_name='EMP';

Insert data from one table to another table.

Insert into emp1 select * from emp;

Create table structure from another table.

Create table emp1 as select * from emp;

Create index command

create index i1 on ttl(empno);

create bitmap index bi on ttl(mgr);

create index i2 on ttl(job) online;

Create cluster command.

Create cluster c1 (empno number);

Multi table update using trigger.

create or replace trigger multi_table_update

after update of sal on emp

for each row

begin

update emp1 set sal=:new.sal where empno=7369;

update emp2 set sal=:new.sal where empno=7369;

end;

/

DROP SEQUENCE S1;

CREATE SEQUENCE S1

START WITH 1

INCREMENT BY 1

MAXVALUE 15000

MINVALUE 1

NOCACHE

NOCYCLE;

EXEC PROCEDURE_SCD2;

SELECT * FROM PROC_SCD2;

Query for deleting alternate even rows FROM a table :

DELETE

FROM srinu

WHERE (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,2)

FROM srinu);

Query for deleting alternate odd rows FROM a table :

DELETE

FROM srinu

WHERE (ROWID,1) IN (SELECT ROWID, MOD(ROWNUM,2)

FROM srinu);

SELECT Query for counting No of words:

SELECT ename,

NVL(LENGTH(REPLACE(TRANSLATE(UPPER(RTRIM(ename)),'ABCDEFGHIJKLMNOPQRS

TUVWXYZ'' ',' @'),' ',''))+1,1) word_length

FROM emp;

Explanation :

TRANSLATE(UPPER(RTRIM(ename)),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'' ','

@') -- This will translate all the characters FROM A-Z including a single quote to aspace. It will also translate a space to a @.

REPLACE(TRANSLATE(UPPER(RTRIM(ename)),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'' ','@'),' ','') -- This will replace every space with nothing in the above result.

LENGTH(REPLACE(TRANSLATE(UPPER(RTRIM(ename)),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'' ',' @'),' ',''))+1 --

This will give u the count of @ characters in the above result.

Alternate:

SELECT length('samir') - length(REPLACE('samir','r','')) FROM dual;

Function to check for a leap year:

CREATE OR REPLACE FUNCTION is_leap_year (p_date IN DATE) RETURN VARCHAR2

AS

v_test DATE;

BEGIN

v_test := TO_DATE ('29-Feb-' || TO_CHAR (p_date,'YYYY'),'DD-Mon-YYYY');

RETURN 'Y';

EXCEPTION

WHEN OTHERS THEN

RETURN 'N';

END is_leap_year;

SQL> SELECT hiredate, TO_CHAR (hiredate, 'Day') weekday

FROM emp

WHERE is_leap_year (hiredate) = 'Y';

Query for removing all non-numeric :

SELECT

TRANSLATE(LOWER(ssn),'abcdefghijklmnopqrstuvwxyz- ','')

FROM DUAL;

Query for translating a column values to INITCAP :

SELECT

TRANSLATE(INITCAP(temp),

SUBSTR(temp, INSTR(temp,'''')+1,1), LOWER(SUBSTR(temp, INSTR(temp,'''')+1)))

FROM srinu1;

Function for displaying Numbers in Words:

SELECT TO_CHAR( TO_DATE( SUBSTR( TO_CHAR(5373484),1),'j'),'Jsp') FROM DUAL;

Only up to integers from 1 to 5373484

Alternate Query for DECODE function :

SELECT case

WHEN sex = 'm' THEN 'male'

WHEN sex = 'f' THEN 'female'

ELSE 'unknown'

END

FROM mytable

Create table adding Constraint to a date field to SYSDATE or 3 months later:

CREATE TABLE srinu(dt1 date DEFAULT SYSDATE, dt2 date,

CONSTRAINT check_dt2 CHECK ((dt2 >= dt1) AND (dt2 <=

ADD_MONTHS(SYSDATE,3)));

Query to list all the suppliers who supply all the parts supplied by supplier 'S2' :

SELECT DISTINCT a.SUPP

FROM ORDERS a

WHERE a.supp != 'S2'

AND a.parts IN

(SELECT DISTINCT PARTS FROM ORDERS WHERE supp = 'S2')

GROUP BY a.SUPP

HAVING

COUNT(DISTINCT a.PARTS) >=

(SELECT COUNT(DISTINCT PARTS) FROM ORDERS WHERE supp = 'S2');

Table : orders

SUPP                  PARTS

------------------ -------

S1P1
S1P2
S1P3
S1P4
S1P5

S1P6
S2P1
S2P2
S3P2
S4P2
S4P4
S4P5

Query to get the last Sunday of any month :

SELECT NEXT_DAY(LAST_DAY(TO_DATE('26-10-2001','DD-MM-YYYY')) - 7,'sunday')

FROM DUAL;

SELECT NEXT_DAY(to_date(LAST DAY OF THE MONTH)-7, 'sunday') FROM dual

Query to get all those who have no children themselves :

table data :

id        name parent_id

-------------------------------

a NULL - the top level entry

b 1 - a child of 1

3c1
4d2 - a child of 2
5e2
6f3
7g3
8h4
9i8

10       j          9

SELECT ID

FROM MY_TABlE

WHERE PARENT_ID IS NOT NULL

MINUS

SELECT PARENT_ID

FROM MY_TABlE;

Query to SELECT last N rows FROM a table :

SELECT empno FROM emp WHERE ROWID in

(SELECT ROWID FROM emp

MINUS

SELECT ROWID FROM emp WHERE ROWNUM <= (SELECT COUNT(*)-5 FROM emp));

SELECT * FROM test_mapping WHERE ROWNUM < 11

MINUS

SELECT * FROM test_mapping WHERE ROWNUM < 9

SELECT with variables:

CREATE OR REPLACE PROCEDURE disp

AS

xTableName varchar2(25):='emp';

xFieldName varchar2(25):='ename';

xValue NUMBER;

xQuery varchar2(100);

name varchar2(10) := 'CLARK';

BEGIN

xQuery := 'SELECT SAL FROM ' || xTableName || ' WHERE ' || xFieldName ||

' = ''' || name || '''';

DBMS_OUTPUT.PUT_LINE(xQuery);

EXECUTE IMMEDIATE xQuery INTO xValue;

DBMS_OUTPUT.PUT_LINE(xValue);

END;

Query to get the DB Name:

SELECT name FROM v$database;

Getting the current default schema :

SELECT SYS_CONTEXT('USERENV','CURRENT_SCHEMA') FROM DUAL;

Query to get all the column names of a particular table :

SELECT column_name

FROM all_tab_columns

WHERE TABLE_NAME = 'ORDERS';

How do I spool only the query result to a file in SQLPLUS :

Place the following lines of code in a file and execute the file in SQLPLUS :

set heading off

set feedback off

set colsep ' '

set termout off

set verify off

spool c:\srini.txt

SELECT empno,ename FROM emp; /* Write your Query here */

spool off

/

Query for getting the current SessionID :

SELECT SYS_CONTEXT('USERENV','SESSIONID') Session_ID FROM DUAL;

Query to display rows FROM m to n :

To display rows 5 to 7 :

SELECT DEPTNO, ENAME, SAL

FROM EMP

WHERE ROWID IN

(SELECT ROWID FROM EMP

WHERE ROWNUM <= 7

MINUS

SELECT ROWID FROM EMP

WHERE ROWNUM < 5);

OR

SELECT ename

FROM emp

GROUP BY ROWNUM, ename

HAVING ROWNUM > 1 and ROWNUM < 3;

Query to count no. Of columns in a table:

SELECT COUNT(column_name)

FROM user_tab_columns

WHERE table_name = 'MYTABLE';

Procedure to increase the buffer length :

dbms_output.enable(4000); /*allows the output buffer to be increased to the

specified number of bytes */

DECLARE

BEGIN

dbms_output.enable(4000);

FOR i IN 1..400

LOOP

DBMS_OUTPUT.PUT_LINE(i);

END LOOP;

END;

/

Inserting an & symbol in a Varchar2 column :

Set the following to some other character. By default it is &.

set define '~'

How do you remove Trailing blanks in a spooled file :

Change the Environment Options Like this :

set trimspool on

set trimout on

Differences between SQL and MS-Access :

Difference 1:

  • Oracle : select name from table1 where name like 'k%';
  • Access: select name from table1 where name like 'k*';

Difference 2:

  • Access: SELECT TOP 2 name FROM Table1;
  • Oracle : will not work there is no such TOP key word.

Query to display all the children, sub children of a parent :

SELECT organization_id,name

FROM hr_all_organization_units

WHERE organization_id in

(

SELECT ORGANIZATION_ID_CHILD FROM PER_ORG_STRUCTURE_ELEMENTS

CONNECT BY PRIOR

ORGANIZATION_ID_CHILD = ORGANIZATION_ID_PARENT

START WITH

ORGANIZATION_ID_CHILD = (SELECT organization_id

FROM hr_all_organization_units

WHERE name = 'EBG Corporate Group'));

Query to display random number between any two given numbers :

SELECT DBMS_RANDOM.VALUE (1,2) FROM DUAL;

How can I get the time difference between two date columns :

SELECT

FLOOR((date1-date2)*24*60*60)/3600)

|| ' HOURS ' ||

FLOOR((((date1-date2)*24*60*60) -

FLOOR(((date1-date2)*24*60*60)/3600)*3600)/60)

|| ' MINUTES ' ||

ROUND((((date1-date2)*24*60*60) -

FLOOR(((date1-date2)*24*60*60)/3600)*3600 -

(FLOOR((((date1-date2)*24*60*60) -

FLOOR(((date1-date2)*24*60*60)/3600)*3600)/60)*60)))

|| ' SECS ' time_difference

FROM my_table;

Using INSTR and SUBSTR

I have this string in a column named location

LOT 8 CONC3 RR

Using instr and substr, I want to take whatever value follows LOT and put it into a different column and whatever value follows CONC and put it into a different column

select substr('LOT 8 CONC3 RR',4,instr('LOT 8 CONC3 RR','CONC')-4) from

dual;

select substr('LOT 8 CONC3 RR',-(length('LOT 8 CONC3 RR')-(instr('LOT 8

CONC3 RR','CONC')+3)))

from dual

View procedure code

select text from all_source where name = 'X'

order by line;

select text from user_source where name = 'X'

select text from user_source where type = 'procedure' and

name='procedure_name';

select name,text from dba_source where name='ur_procedure'

and owner='scott';

To convert signed number to number in oracle

select to_number('-999,999.99', 's999,999.99') from dual; -999,999.99

select to_number('+0,123.45', 's999,999,999.99') from dual; 123.45

select to_number('+999,999.99', 's999,999.99') from dual; 999,999.99

Columns of a table

select column_name from user_tab_columns where TABLE_NAME = 'EMP'

select column_name from all_tab_columns where TABLE_NAME = 'EMP'

select column_name from dba_tab_columns where TABLE_NAME = 'EMP'

select column_name from cols where TABLE_NAME = 'EMP'

Delete rows conditionally

I have a table have

a,b,c field,

a,b should be unique, and leave max(c) row in.

How can I delete other rows?

delete from 'table'

where (a,b,c) not in (select a,b,max(c) from 'table' group by a,b);

Double quoting a Single quoted String

declare

-- we need one here to get a single quote into the variable

v_str varchar2 (20) := 'O''reilly''s';

begin

DBMS_OUTPUT.PUT_LINE ( 'original single quoted v_str= ' || v_str );

v_str := replace(v_str, '''', '''''');

DBMS_OUTPUT.PUT_LINE ( 'after double quoted v_str= ' || v_str );

end;

SQL> /

original single quoted v_str= O'reilly's

after double quoted v_str= O''reilly''s

Time Conversion

CREATE OR REPLACE FUNCTION to_hms (i_days IN number)

RETURN varchar2

IS

BEGIN

RETURN TO_CHAR (TRUNC (i_days)) &#124&#124 ' days ' &#124&#124

TO_CHAR (TRUNC (SYSDATE) + MOD (i_days, 1), 'HH24:MI:SS');

END to_hms;

select to_hms(to_date('17-Jan-2002 13:20:20', 'dd-Mon-yyyy hh24:mi:ss') -

to_date('11-Jan-2002 11:05:05', 'dd-Mon-yyyy hh24:mi:ss')) from

dual;

Table comparison

The table in both the schemas should have exactly the same structure. The data in it could be same or different

a-b and b-a

select * from a.a minus select * from b.a and select * from b.a minus select *

from a.a

Running Jobs

select * from user_jobs;

exec dbms_job.remove(job_no);

Switching Columns

Update tblname

Set column1 = column2,

Column2 = column1;

Replace and Round

I have the number e.g. 63,9823874012983 and I want to round it to 63,98 and at the

same time change the , to a .

select round(replace('63,9823874012983',',','.'),2) from dual;

First date of the year

select trunc(sysdate, 'y') from dual;

01-jan-2002

last year this month through a select statement

select add_months(sysdate, -12) from dual;

05-APR-01

Create Sequence

create sequence sh increment by 1 start with 0;

Current Week

select next_day(sysdate-7,'SUNDAY'), next_day(sysdate,'SATURDAY') from dual;

NEXT_DAY( NEXT_DAY(

--------- ---------

07-APR-02 13-APR-02

For Indepth understanding of MicroStrategy click on