Creating Tables
deptno number,
name varchar2(50) not null,
location varchar2(50),
constraint pk_departments primary key (deptno)
);
Example below:
create table EMPLOYEES (
empno number,
name varchar2(50) not null,
job varchar2(50),
manager number,
hiredate date,
salary number(7,2),
commission number(7,2),
deptno number,
constraint pk_employees primary key (empno),
constraint fk_employees_deptno foreign key (deptno)
references DEPARTMENTS (deptno)
);
Creating Triggers
create or replace trigger DEPARTMENTS_BIU
before insert or update on DEPARTMENTS
for each row
begin
if inserting and :new.deptno is null then
:new.deptno := to_number(sys_guid(),
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
end if;
end;
/
create or replace trigger EMPLOYEES_BIU
before insert or update on EMPLOYEES
for each row
begin
if inserting and :new.empno is null then
:new.empno := to_number(sys_guid(),
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
end if;
end;
/
Inserting Data
Do this first
insert into departments (name, location) values
('Finance','New York');
insert into departments (name, location) values
('Development','San Jose');
Then this:
select * from departments;
Result
insert into EMPLOYEES
(name, job, salary, deptno)
values
('Sam Smith','Programmer',
5000,
(select deptno
from departments
where name = 'Development'));
insert into EMPLOYEES
(name, job, salary, deptno)
values
('Mara Martin','Analyst',
6000,
(select deptno
from departments
where name = 'Finance'));
insert into EMPLOYEES
(name, job, salary, deptno)
values
('Yun Yates','Analyst',
5500,
(select deptno
from departments
where name = 'Development'));
Indexing Columns
select table_name "Table",
index_name "Index",
column_name "Column",
column_position "Position"
from user_ind_columns
where table_name = 'EMPLOYEES' or
table_name = 'DEPARTMENTS'
order by table_name, column_name, column_position
Querying Data
Basic Select
select * from employees;
Basic query to join together two tables
select e.name employee,
d.name department,
e.job,
d.location
from departments d, employees e
where d.deptno = e.deptno(+)
order by e.name;
Alternative query to above
select e.name employee,
(select name
from departments d
where d.deptno = e.deptno) department,
e.job
from employees e
order by e.name;
Adding Columns
alter table EMPLOYEES
add country_code varchar2(2);
Updating Data - Basic Update
update employees