Auto-generated Columns:

create table &table_name ( emp_id number(3) generated by default as identity, ename varchar(10), sal number(6), hra number(5), total_sal number(10) generated always as (sal+hra) );

See the columns which I didn't give are auto-generated.

Foriegn Key Constarint:

Now for example in case of separately tabkes for department and employee. The employee table will have a field department_id. We need to restrict that field in such a way that it will only take values which are already in the department table. Because if there is not such a constraint then an employee will be assigned a department which doesn't exist in the company.

For that porpose we need to use a constraint. A foreign key constraints. That foreign key constraint will create a relation between 2 tables.

Add a new field in the emp1 table.

A primary key should be use in a foriegn key constraint.

Creating relationship using foriegn key:

drop table auemp;

drop table depts;

create table depts ( deptno number(5) generated by default as identity, dname varchar2(20), constraint pk primary key (deptno) );

create table auemp ( emp_id number(3) generated by default as identity, ename varchar(10), sal number(10), hra number(5), total_sal number(10) generated always as (sal+hra), deptno number(5), constraint fk foreign key (deptno) references depts(deptno) );

We entered the value without a department number which will be null, we can do that.

See can't insert into this as the foreign key constraint got violented. The value 10 is not in the depts table. Without it that value can't be entered into deptno column.

Only after inserting the value did the data can be inserted into deptno column.

View:

A view is like a table but that table doesn't exist physically in the storage. Its a logical table. When the view is used it takes the data from the physical table. A view can have data from multiple tables via joins. So by using a view someone doesn't havee to write big join sqls.

create view ag as select e.employee_id "Employee Id", e.first_name || ' ' || e.last_name "Employee Name", m.employee_id "Manager Id", m.first_name || ' ' || m.last_name "Manager Name" from employees e, employees m where e.manager_id = m.employee_id order by e.employee_id;

If the view is already created:

create or replace view ag as select e.employee_id "Employee Id", e.first_name || ' ' || e.last_name "Employee Name", m.employee_id "Manager Id", m.first_name || ' ' || m.last_name "Manager Name" from employees e, employees m where e.manager_id = m.employee_id order by e.employee_id;

select * from ag;

select * from ag where "Manager Name" like 'Steven%';