Oracle Dual Table

Oracle, DUAL is a table which is created with every installation of Oracle

The DUAL table's column, the column name, its datatype and even its value are not important. Technically it could be any datatype. DUAL exists just so we have a 1 row table we can reliably select from.

Don't EVER add rows to DUAL, fiddle with its column, or alter it in any way. If you do, you can expect some very strange and potentially destructive things to happen in your database, assuming the database doesn't just crash and burn completely

select sysdate from emp

1) select 1 from DUAL;
    returns 1

2) select 1+2 from DUAL;
    returns 3

3) select sysdate from DUAL;
    returns system date of oracle database.

4) select `sql` from DUAL;
    returns`sql`

5) select user from DUAL;
    returns oracle user logged in.

6) select
   (select empno from emp where empno = 10)
   from DUAL;
    returns 10

DUAL table can be used to test the SQL functions ( Both in-built and user defined functions)

7)  select func_salary(10) from DUAL;
where func_salary is a user defined function returns a value when argument is 10.

8) select LOWER(`ORAclE`) from DUAL;
 returns`oracle` where LOWER is a in-built SQL function.

Interview tips

If DUAL is supposed to have only one row, why is it called DUAL? Why not SINGLE?!

According to this old article on the history of Oracle’s DUAL table, DUAL was originally not meant to be seen itself but instead used inside a view that was expected to be queried. The idea was that you could do a JOIN to the DUAL table and create two rows in the result for every one row in your table. In that context, the name DUAL seemed fine.

Is it valid or not??
CREATE TABLE xxemp1
AS
   SELECT   * FROM DUAL

*Read*Explore*Succeed*