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; |
|
|