SQL Dual Table

Dual Table

Dual Table :-Dual is a dummy table in Oracle .Owner of dual table is SYS user.  This table is used for calculation purpose. Only one column and one record is there in Dual Table.

Note:- We can perform any operation from dual table as well as from any table, main difference is that output of any operation from dual table will be printed only one time as Dual table has only one record while from any other table output will be printed number of records count in that table times. 

SQL>SELECT * FROM DUAL;
SQL>DESC DUAL;
SQL>SELECT TABLE_NAME,OWNER FROM ALL_TABLES WHERE       TABLE_NAME='DUAL';

Examples:-

SQL>SELECT 2+5 FROM DUAL;

SQL>SELECT SYSDATE FROM DUAL;

SQL>SELECT USER FROM DUAL;

SQL>SELECT 1,2,3,4 FROM DUAL;

SQL>SELECT (SELECT COUNT(*) FROM EMP)+(SELECT COUNT(*) FROM DEPT) TOTAL FROM DUAL;

SQL>SELECT SQRT(144) FROM DUAL;

SQL>SELECT POWER(3,4) FROM DUAL;

SQL>SELECT EXP(3) FROM DUAL;--exponential e*e*e (e=2.718)

SQL>SELECT MOD(17,3) FROM DUAL;

SQL>SELECT ROUND(12.345678,2) FROM DUAL;

SQL>SELECT TRUNC(12.34567,2) FROM DUAL;

SQL>SELECT FLOOR(12.345678) FROM DUAL;

SQL>SELECT CEIL(12.345678) FROM DUAL;

SQL>SELECT 2*6 FROM DUAL;

SQL>SELECT 2*(13-9) FROM DUAL;

SQL>SELECT TRUNC(13.2365,2) FROM DUAL;

SQL>SELECT ROUND(13.2365,2) FROM DUAL;

SQL>SELECT CEIL(13.5674) FROM DUAL;

SQL>SELECT FLOOR(13.5674) FROM DUAL;

SQL>SELECT POWER(3,4) FROM DUAL;

SQL>SELECT SQRT(81) FROM DUAL;

 

Here we can perform any of above operation from any database table in place of dual table with restricted record count.

 

Example 

 

SQL>select sqrt(81) from emp where rownum<2 div="">