Wanna Be a DBA ?: Important Queries

II. 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.

Total 1980 1981 1982 1983

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

14 1 10 2 1

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;

Index

III. 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 :

=======

DEPTNO ENAME SAL SUM (SAL)

========= ======= ==== =========

10 KING 5000 11725

30 BLAKE 2850 10900

10 CLARK 2450 11725

10 JONES 2975 11725

30 MARTIN 1250 10900

30 ALLEN 1600 10900

30 TURNER 1500 10900

30 JAMES 950 10900

30 WARD 2750 10900

20 SMITH 8000 33000

20 SCOTT 3000 33000

20 MILLER 20000 33000

Index

IV. 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 :

Job Dept 10 Dept 20 Dept 30 Total

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

ANALYST 6000 6000

CLERK 1300 1900 950 4150

MANAGER 2450 2975 2850 8275

PRESIDENT 5000 5000

SALESMAN 5600 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 ;

Index

V. 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;

Index

VI. Retrieving the 5th row FROM a table :

SELECT DEPTNO, ENAME, SAL

FROM EMP

WHERE ROWID = (SELECT ROWID FROM EMP WHERE ROWNUM <= 5

MINUS

SELECT ROWID FROM EMP WHERE ROWNUM < 5)

Index

VII. 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';

Index

VIII. 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);

Index

IX. 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);

Index

X. 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;

Index

XI. 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).

SELECT

f2,

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;

Index

XII. 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;

XIII. 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;

Index

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

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

Index

XV. Query to display only the duplicate records in a table:

SELECT num

FROM satyam

GROUP BY num

HAVING COUNT(*) > 1;

Index

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

***

****

*****

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

FROM srinu1;

Index

XVII. Function for getting the Balance Value :

FUNCTION F_BALANCE_VALUE

(p_business_group_id number, p_payroll_action_id number,

p_balance_name varchar2, p_dimension_name varchar2) RETURN NUMBER

l_bal number;

l_defined_bal_id number;

l_assignment_action_id number;

BEGIN

SELECT assignment_action_id

INTO l_assignment_action_id

FROM

pay_assignment_actions

WHERE

assignment_id = :p_assignment_id

AND payroll_action_id = p_payroll_action_id;

SELECT

defined_balance_id

INTO

l_defined_bal_id

FROM

pay_balance_types pbt,

pay_defined_balances pdb,

pay_balance_dimensions pbd

WHERE

pbt.business_group_id = p_business_group_id

AND UPPER(pbt.balance_name) = UPPER(p_balance_name)

AND pbt.business_group_id = pdb.business_group_id

AND pbt.balance_type_id = pdb.balance_type_id

AND UPPER(pbd.dimension_name) = UPPER(p_dimension_name)

AND pdb.balance_dimension_id = pbd.balance_dimension_id;

l_bal := pay_balance_pkg.get_value(l_defined_bal_id,l_assignment_action_id);

RETURN (l_bal);

exception

WHEN no_data_found THEN

RETURN 0;

END;

Index

XVIII. Function for getting the Element Value :

FUNCTION f_element_value(

p_classification_name in varchar2,

p_element_name in varchar2,

p_business_group_id in number,

p_input_value_name in varchar2,

p_payroll_action_id in number,

p_assignment_id in number

)

RETURN number

l_element_value number(14,2) default 0;

l_input_value_id pay_input_values_f.input_value_id%type;

l_element_type_id pay_element_types_f.element_type_id%type;

BEGIN

SELECT DISTINCT element_type_id

INTO l_element_type_id

FROM pay_element_types_f pet,

pay_element_classifications pec

WHERE pet.classification_id = pec.classification_id

AND upper(classification_name) = upper(p_classification_name)

AND upper(element_name) = upper(p_element_name)

AND pet.business_group_id = p_business_group_id;

SELECT input_value_id

INTO l_input_value_id

FROM pay_input_values_f

WHERE upper(name) = upper(p_input_value_name)

AND element_type_id = l_element_type_id;

SELECT NVL(prrv.result_value,0)

INTO l_element_value

FROM pay_run_result_values prrv,

pay_run_results prr,

pay_assignment_actions paa

WHERE prrv.run_result_id = prr.run_result_id

AND prr.assignment_ACTION_ID = paa.assignment_action_id

AND paa.assignment_id = p_assignment_id

AND input_value_id = l_input_value_id

AND paa.payroll_action_id = p_payroll_action_id;

RETURN (l_element_value);

exception

WHEN no_data_found THEN

RETURN 0;

END;

Index

XIX. SELECT Query for counting No of words :

SELECT ename,

NVL(LENGTH(REPLACE(TRANSLATE(UPPER(RTRIM(ename)),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'' ',' @'),' ',''))+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 a space. 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.

Index

XX. Function to check for a leap year :

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

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';

Index

XXI. Query for removing all non-numeric :

SELECT

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

FROM DUAL;

Index

XXII. 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;

Index

XXIII. Function for displaying Rupees in Words :

CREATE OR REPLACE FUNCTION to_word_i

(amount IN NUMBER)

RETURN VARCHAR2

v_length INTEGER := 0;

v_num2 VARCHAR2 (50) := NULL;

v_amount VARCHAR2 (50) := TO_CHAR (TRUNC (amount));

v_word VARCHAR2 (4000) := NULL;

v_word1 VARCHAR2 (4000) := NULL;

TYPE myarray IS TABLE OF VARCHAR2 (255);

v_str myarray := myarray (' Thousand ',

' Lakh ',

' Crore ');

BEGIN

IF ((amount = 0) OR (amount IS NULL)) THEN

v_word := 'zero';

ELSIF (TO_CHAR (amount) LIKE '%.%') THEN

IF (SUBSTR (amount, INSTR (amount, '.') + 1) > 0) THEN

v_num2 := SUBSTR (amount, INSTR (amount, '.') + 1);

IF (LENGTH (v_num2) < 2) THEN

v_num2 := v_num2 * 10;

END IF;

v_word1 := ' AND ' || (TO_CHAR (TO_DATE (SUBSTR (v_num2, LENGTH (v_num2) - 1,2), 'J'),

'JSP' ))|| ' paise ';

v_amount := SUBSTR(amount,1,INSTR (amount, '.')-1);

v_word := TO_CHAR (TO_DATE (SUBSTR (v_amount, LENGTH (v_amount) - 2,3), 'J'), 'Jsp' ) || v_word;

v_amount := SUBSTR (v_amount, 1, LENGTH (v_amount) - 3);

FOR i in 1 .. v_str.COUNT

LOOP

EXIT WHEN (v_amount IS NULL);

v_word := TO_CHAR (TO_DATE (SUBSTR (v_amount, LENGTH (v_amount) - 1,2), 'J'), 'Jsp' ) || v_str (i) || v_word;

v_amount := SUBSTR (v_amount, 1, LENGTH (v_amount) - 2);

END LOOP;

END IF;

ELSE

v_word := TO_CHAR ( TO_DATE ( TO_CHAR ( amount, '999999999') , 'J'), 'JSP');

END IF;

v_word := v_word || ' ' || v_word1 || ' only ';

v_word := REPLACE (RTRIM (v_word), ' ', ' ');

v_word := REPLACE (RTRIM (v_word), '-', ' ');

RETURN INITCAP (v_word);

END to_word_i;

Index

XXIV. Query for deleting alternate even rows FROM a table :

DELETE

FROM srinu

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

FROM srinu);

Index

XXV. Query for deleting alternate odd rows FROM a table :

DELETE

FROM srinu

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

FROM srinu);

Index

XXVI. Procedure for sending Email :

CREATE OR REPLACE PROCEDURE Send_Mail

sender VARCHAR2(50) := 'sender@something.com';

recipient VARCHAR2(50) := 'recipient@something.com';

subject VARCHAR2(100) := 'Test Message';

message VARCHAR2(1000) := 'This is a sample mail ....';

lv_mailhost VARCHAR2(30) := 'HOTNT002';

l_mail_conn utl_smtp.connection;

lv_crlf VARCHAR2(2):= CHR( 13 ) || CHR( 10 );

BEGIN

l_mail_conn := utl_smtp.open_connection (lv_mailhost, 80);

utl_smtp.helo ( l_mail_conn, lv_mailhost);

utl_smtp.mail ( l_mail_conn, sender);

utl_smtp.rcpt ( l_mail_conn, recipient);

utl_smtp.open_data (l_mail_conn);

utl_smtp.write_data ( l_mail_conn, 'FROM: ' || sender || lv_crlf);

utl_smtp.write_data ( l_mail_conn, 'To: ' || recipient || lv_crlf);

utl_smtp.write_data ( l_mail_conn, 'Subject:' || subject || lv_crlf);

utl_smtp.write_data ( l_mail_conn, lv_crlf || message);

utl_smtp.close_data(l_mail_conn);

utl_smtp.quit(l_mail_conn);

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Error');

END;

Index

XXVII. Alternate Query for DECODE function :

SELECT case

WHEN sex = 'm' THEN 'male'

WHEN sex = 'f' THEN 'female'

ELSE 'unknown'

END

FROM mytable;

Index

XXVIII. 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)));

Index

XXIX. 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

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

S1 P1

S1 P2

S1 P3

S1 P4

S1 P5

S1 P6

S2 P1

S2 P2

S3 P2

S4 P2

S4 P4

S4 P5

Index

XXX. 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;

Index

XXXI. Query to get all those who have no children themselves :

table data :

id name parent_id

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

1 a NULL - the top level entry

2 b 1 - a child of 1

3 c 1

4 d 2 - a child of 2

5 e 2

6 f 3

7 g 3

8 h 4

9 i 8

10 j 9

SELECT ID

FROM MY_TABlE

WHERE PARENT_ID IS NOT NULL

MINUS

SELECT PARENT_ID

FROM MY_TABlE;

Index

XXXII. 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));

Index

XXXIII. SELECT with variables:

CREATE OR REPLACE PROCEDURE disp

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;

Index

XXXIV. Query to get the DB Name:

SELECT name FROM v$database;

Index

XXXV. Getting the current default schema :

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

Index

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

SELECT column_name

FROM all_tab_columns

WHERE TABLE_NAME = 'ORDERS';

Index

XXXVII. 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

Index

XXXVIII. Query for getting the current SessionID :

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

Index

XXXIX. 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);

SELECT ename

FROM emp

GROUP BY ROWNUM, ename

HAVING ROWNUM > 1 and ROWNUM < 3;

Index

XXXX. Query to count no. Of columns in a table:

SELECT COUNT(column_name)

FROM user_tab_columns

WHERE table_name = 'MYTABLE';

Index

XXXXI. 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;

Index

XXXXII. Inserting an & symbol in a Varchar2 column :

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

set define '~'

Index

XXXXIII. Create Query to restrict the user to a single row :

Index

XXXXIV. Query to get the first inserted record FROM a table :

Index

XXXXV. How to concatenate a column value with multiple rows :

Index

XXXXVI. Query to delete all the tables at once :

Index

XXXXVII. SQL Query for getting Orphan Records :

Index

XXXXVIII. How do you remove Trailing blanks in a spooled file :

Change the Environment Options Like this :
set trimspool on
set trimout on

Index

XXXXIX. Samples for executing Dynamic SQL Statements :

Sample :1

CREATE OR REPLACE PROCEDURE CNT(P_TABLE_NAME IN VARCHAR2)

SqlString VARCHAR2(200);

tot number;

BEGIN

SqlString:='SELECT COUNT(*) FROM '|| P_TABLE_NAME;

EXECUTE IMMEDIATE SqlString INTO tot;

DBMS_OUTPUT.PUT_LINE('Total No.Of Records In ' || P_TABLE_NAME || ' ARE=' || tot);

END;

Sample :2

DECLARE

sql_stmt VARCHAR2(200);

plsql_block VARCHAR2(500);

emp_id NUMBER(4) := 7566;

salary NUMBER(7,2);

dept_id NUMBER(2) := 50;

dept_name VARCHAR2(14) := ’PERSONNEL’;

location VARCHAR2(13) := ’DALLAS’;

emp_rec emp%ROWTYPE;

BEGIN

EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';

sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';

EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;

sql_stmt := 'SELECT * FROM emp WHERE empno = :id';

EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;

plsql_block := 'BEGIN emp_pkg.raise_salary(:id, :amt); END;';

EXECUTE IMMEDIATE plsql_block USING 7788, 500;

sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1

RETURNING sal INTO :2';

EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;

EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num'

USING dept_id;

EXECUTE IMMEDIATE ’ALTER SESSION SET SQL_TRACE TRUE’;

END;

Sample 3

CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS

v_cursor integer;

v_dname char(20);

v_rows integer;

BEGIN

v_cursor := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(v_cursor, 'select dname from dept where deptno > :x', DBMS_SQL.V7);

DBMS_SQL.BIND_VARIABLE(v_cursor, ':x', no);

DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20);

v_rows := DBMS_SQL.EXECUTE(v_cursor);

LOOP

IF DBMS_SQL.FETCH_ROWS(v_cursor) = 0 THEN

EXIT;

END IF;

DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname);

DBMS_OUTPUT.PUT_LINE('Deptartment name: '||v_dname);

END LOOP;

DBMS_SQL.CLOSE_CURSOR(v_cursor);

EXCEPTION

WHEN OTHERS THEN

DBMS_SQL.CLOSE_CURSOR(v_cursor);

raise_application_error(-20000, 'Unknown Exception Raised: '||sqlcode||' '||sqlerrm);

END;

Index

XXXXX. 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.

Index

XXXXXI. 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'));

Index

XXXXXII. Procedure to read/write data from a text file :

CREATE OR REPLACE PROCEDURE read_data

c_path varchar2(100) := '/usr/tmp';

c_file_name varchar2(20) := 'EKGSEP01.CSV';

v_file_id utl_file.file_type;

v_buffer varchar2(1022) := This is a sample text’;

BEGIN

v_file_id := UTL_FILE.FOPEN(c_path,c_file_name,'w');

UTL_FILE.PUT_LINE(v_file_id, v_buffer);

UTL_FILE.FCLOSE(v_file_id);

v_file_id := UTL_FILE.FOPEN(c_path,c_file_name,'r');

UTL_FILE.GET_LINE(v_file_id, v_buffer);

DBMS_OUTPUT.PUT_LINE(v_buffer);

UTL_FILE.FCLOSE(v_file_id);

END;

Index

XXXXXIII. Query to display random number between any two given numbers :

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

Index

XXXXXIV. 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;

Index

Wanna Be a DBA ?

Important Queries

No comments:

Post a Comment