MisterTootor M.S., B.S., A.S., A.S.B
I'm a paragraph. Click here to add your own text and edit me. It's easy.
deptno number,
name varchar2(50) not null,
location varchar2(50),
constraint pk_departments primary key (deptno)
);
​
​
Example below:
Creating Tables
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;
​​
​
Here is the 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
set country_code = 'US';
Building a Test Table
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
salary NUMBER,
department_id NUMBER
);
INSERT INTO employees VALUES (101, 'Alice', 'Johnson', 48000, 10);
INSERT INTO employees VALUES (102, 'Jane', 'Smith', 90000, 20);
INSERT INTO employees VALUES (103, 'John', 'Doe', 43000, 10);
INSERT INTO employees VALUES (105, 'Carlos', 'Rivera', 75000, 30);
INSERT INTO employees VALUES (107, 'Mei', 'Tanaka', 51000, 40);
COMMIT;
SELECT employee_id,
first_name,
last_name,
salary
FROM employees
WHERE salary > 50000
ORDER BY salary DESC;
Example Output:
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
102 Jane Smith 90000
105 Carlos Rivera 75000
107 Mei Tanaka 51000