Advance Queries

Group By Clause and Its Extensions
Subqueries
Table Partitioning
Materialized View
Flashback Operations

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;




Comments