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;