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

Summary
Review Date
Reviewed Item
General Database Queries And Practices
Author Rating
51star1star1star1star1star

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on General Database Queries And Practices"

    Leave a Message

    Your email address will not be published. Required fields are marked *

    Support


    Please leave a message and we'll get back to you soon.

    3 + 5