Advance Queries

Hierarchical Queries

Group By Clause and Its Extensions

Subqueries

Table Partitioning

Materialized View

Flashback Operations

Regular Expressions

Analytical Functions

Default Values

Create table employee

(

employee_id number,

Employee_name varchar2(20),

expiry_date date default to_date('31-dec-2200','dd-mon-yyyy'

);

Now when we insert and you dont have expiry date, it will enter the default value, like below it will insert default value for the expiry_date

insert into employee(employee_id,emloyee_name) values (1','Bob');

insert into employee(employee_id,emloyee_name,expiry_date) values (1','Bob',default);

insert into employee(employee_id,emloyee_name,expiry_date) values (1','John',to_date('1-jan-2017','dd-mon-yyyy'));

Virtual Column

Virtual Columns appear like normal columns, but their value is not stored on the disk , its derived at run-time.

create table sales

(

transaction_id number,

transaction_date date,

sale_amount number(10,2),

commision number generated always as (sale_amount * 0.01) virtual

);

other way to add virtual column

1)

create table sales

(

transaction_id number,

transaction_date date,

sale_amount number(10,2),

commision as (sale_amount*0.01)

);

2) alter table sales add commision as (sale_amount*0.01);

Arithmetic calculation / expression on NULL Values.

select 1 + null from dual; returns null

select 1 * null from dual; returns null

select 1 / null from dual; returns null

select avg(sales_amount) from sales; will return wrong amount if null present in the column

Therefore we normally use nvl function

select avg(nvl(amount),0)) from sales;

select count(nvl(amount),0)) from sales;