DUAL table in database

DUAL is a dummy table you can use to view results from functions and calculations.

The DUAL table is owned by the user SYS and can be accessed by all users. It contains one column, DUMMY, and one row with the value X.

The DUAL table is useful when you want to return a value once only, for instance, the value of a constant, pseudo column, or expression that is not derived from a table with user data.

The DUAL table is generally used for SELECT clause syntax completeness, because both SELECT and FROM clauses are mandatory, and several calculations do not need to select from actual tables. 

The use of the dual table allows you to enter your values in a select statement, although not being stored in table.

Syntax

DUAL 

Examples

How to create a table in database

The CREATE TABLE statement is one of the data definition language (DDL) statements. DDL are SQL statements used to create, modify, or remove database structures. To create a table, a user must have the CREATE TABLE rights and sufficient storage in which user can create objects.

Syntax

CREATE TABLE [schema.]table

(column datatype [DEFAULT expr][, ...]);

Options

schema	Is  the same as the owner’s name
table	Is  the name of the table
DEFAULT expr	Specifies a default value if a value is omitted in the INSERT statement
column	Is the name of the column
datatype	Is the column’s data type and length

When the table is not present in the DB it will throw error message as shown below.

Example

Let us create table students having First name, Last name, Roll no, Address and City column.

CREATE TABLE students

(

FirstName varchar(255),

LastName varchar(255),

RollNo int,

Address varchar(255),

City varchar(255)

);

Now when the table is created we can show all the records of table by select statement as shown below:

select * from students

As we have not inserted any records in the table, hence no rows are reported.

TAB and COL

We have tab to show all the tables currently present in the database.

Also we have col to search for specific table name when we only know column name for reference.

This two are very useful for finding tables in day to day operations.

select * from tab

where tname = 'STUDENTS'

select * from col

where cname = 'FIRSTNAME'