Useful info

--Question 4.4.2.1
update agents a1
set a1.deactivation_date = (select min(a3.deactivation_date)
from agents a3
where to_char(a3.activation_date, 'YYYY') =
to_char(a1.activation_date, 'YYYY')
and deactivation_date is not null)
where a1.agent_id IN
(select a.agent_id
from agents a, missions_agents ma
where a.deactivation_date is not null
and a.agent_id = ma.agent_id having count(mission_id) > 7
group by a.agent_id);

commit;

drop table AM_X_442_1;

create table AM_X_442_1 as
select * from agents;

--Question 4.4.2.2

drop table AM_X_442_2;

create table AM_X_442_2 as
select *
from missions
where decode(instr(code_name, ' '),
0,
length(code_name),
instr(code_name, ' ') - 1) > 7
and mission_date in
(select mission_date
from (select * from missions order by mission_date desc)
where rownum <= 10);

update am_x_442_2 am
set security_level = (select max(security_level)
from missions m
where m.mission_type_id = am.mission_type_id);

commit;

--Question 4.4.3.5
select description location,
sum(decode(greatest(cost, 0), least(cost, 0.9), 1, 0)) "<1",
sum(decode(greatest(cost, 1), least(cost, 2.99), 1, 0)) "1 - 2.99",
sum(decode(greatest(cost, 3), least(cost, 4.99), 1, 0)) "3 - 4.99",
sum(decode(greatest(cost, 5), least(cost, 6.99), 1, 0)) "5 - 6.99",
sum(decode(greatest(cost, 7), least(cost, 999), 1, 0)) ">= 7"
from (select tg.location_id,
l.description,
t.target_id,
sum(nvl((select cost
from access_cost
where t.security_level between
nvl(lower_bound_level, t.security_level) and
nvl(upper_bound_level, t.security_level)),
0)) cost
from information t, targets tg, locations l
where t.target_id = tg.target_id
and tg.location_id = l.location_id
and tg.location_id in
(select location_id
from targets
group by location_id
having count(target_id) >= 3)
group by tg.location_id, l.description, t.target_id
order by tg.location_id)
group by location_id, description
order by location_id;

drop table AM_X_443_5;

create table AM_X_443_5 as
select description location,
sum(decode(greatest(cost, 0), least(cost, 0.9), 1, 0)) "<1",
sum(decode(greatest(cost, 1), least(cost, 2.99), 1, 0)) "1 - 2.99",
sum(decode(greatest(cost, 3), least(cost, 4.99), 1, 0)) "3 - 4.99",
sum(decode(greatest(cost, 5), least(cost, 6.99), 1, 0)) "5 - 6.99",
sum(decode(greatest(cost, 7), least(cost, 999), 1, 0)) ">= 7"
from (select tg.location_id,
l.description,
t.target_id,
sum(nvl((select cost
from access_cost
where t.security_level between
nvl(lower_bound_level, t.security_level) and
nvl(upper_bound_level, t.security_level)),
0)) cost
from information t, targets tg, locations l
where t.target_id = tg.target_id
and tg.location_id = l.location_id
and tg.location_id in
(select location_id
from targets
group by location_id
having count(target_id) >= 3)
group by tg.location_id, l.description, t.target_id
order by tg.location_id)
group by location_id, description
order by location_id;

--Question 4.4.3.6

select first_name ' ' last_name NAME,
JAN,
FEB,
MAR,
APR,
MAY,
JUN,
JUL,
AUG,
SEP,
OCT,
NOV,
DEC
from (select x.agent_id,
a.first_name,
a.last_name,
sum(decode(month, 'JAN', AVG_COST, 0)) JAN,
sum(decode(month, 'FEB', AVG_COST, 0)) FEB,
sum(decode(month, 'MAR', AVG_COST, 0)) MAR,
sum(decode(month, 'APR', AVG_COST, 0)) APR,
sum(decode(month, 'MAY', AVG_COST, 0)) MAY,
sum(decode(month, 'JUN', AVG_COST, 0)) JUN,
sum(decode(month, 'JUL', AVG_COST, 0)) JUL,
sum(decode(month, 'AUG', AVG_COST, 0)) AUG,
sum(decode(month, 'SEP', AVG_COST, 0)) SEP,
sum(decode(month, 'OCT', AVG_COST, 0)) OCT,
sum(decode(month, 'NOV', AVG_COST, 0)) NOV,
sum(decode(month, 'DEC', AVG_COST, 0)) DEC
FROM (select i.agent_id,
to_char(i.gleaned_date, 'MON') Month,
AVG(nvl((select cost
from access_cost
where i.security_level between
nvl(lower_bound_level, i.security_level) and
nvl(upper_bound_level, i.security_level)),
0)) AVG_COST
from information i
where to_char(gleaned_date, 'YYYY') = '2000'
and agent_id is not null
group by i.agent_id, to_char(i.gleaned_date, 'MON')
order by agent_id) x,
agents a
where x.agent_id = a.agent_id
group by x.agent_id, a.first_name, a.last_name)
order by last_name;

drop table AM_X_443_6;

create table AM_X_443_6 as
select first_name ' ' last_name NAME,
JAN,
FEB,
MAR,
APR,
MAY,
JUN,
JUL,
AUG,
SEP,
OCT,
NOV,
DEC
from (select x.agent_id,
a.first_name,
a.last_name,
sum(decode(month, 'JAN', AVG_COST, 0)) JAN,
sum(decode(month, 'FEB', AVG_COST, 0)) FEB,
sum(decode(month, 'MAR', AVG_COST, 0)) MAR,
sum(decode(month, 'APR', AVG_COST, 0)) APR,
sum(decode(month, 'MAY', AVG_COST, 0)) MAY,
sum(decode(month, 'JUN', AVG_COST, 0)) JUN,
sum(decode(month, 'JUL', AVG_COST, 0)) JUL,
sum(decode(month, 'AUG', AVG_COST, 0)) AUG,
sum(decode(month, 'SEP', AVG_COST, 0)) SEP,
sum(decode(month, 'OCT', AVG_COST, 0)) OCT,
sum(decode(month, 'NOV', AVG_COST, 0)) NOV,
sum(decode(month, 'DEC', AVG_COST, 0)) DEC
FROM (select i.agent_id,
to_char(i.gleaned_date, 'MON') Month,
AVG(nvl((select cost
from access_cost
where i.security_level between
nvl(lower_bound_level,
i.security_level) and
nvl(upper_bound_level,
i.security_level)),
0)) AVG_COST
from information i
where to_char(gleaned_date, 'YYYY') = '2000'
and agent_id is not null
group by i.agent_id, to_char(i.gleaned_date, 'MON')
order by agent_id) x,
agents a
where x.agent_id = a.agent_id
group by x.agent_id, a.first_name, a.last_name)
order by last_name;

--Question 4.4.4.1
--Solution 1
select (first_name ' ' last_name) NAME
from agents a, missions_agents ma
where a.agent_id = ma.agent_id(+)
and ma.mission_id is null;

--Solution 2
select (first_name ' ' last_name) NAME
from agents
where agent_id not in (select distinct agent_id from missions_agents);

drop table AM_x_441_1;

Create table AM_x_441_1 as
select (first_name ' ' last_name) NAME
from agents a, missions_agents ma
where a.agent_id = ma.agent_id(+)
and ma.mission_id is null;

--Question 4.4.4.2

select x.location_id LOCATION, x.name YOUNGEST, y.name ELDEST
from (select b.location_id,
b.first_name ' ' b.last_name name,
b.birth_date
from (select location_id, max(birth_date) birth_date
from agents
group by location_id) a,
agents b
where a.location_id = b.location_id
and a.birth_date = b.birth_date) x,
(select b.location_id,
b.first_name ' ' b.last_name name,
b.birth_date
from (select location_id, min(birth_date) birth_date
from agents
group by location_id) a,
agents b
where a.location_id = b.location_id
and a.birth_date = b.birth_date) y
where x.location_id = y.location_id;

drop table AM_X_444_2;

Create table AM_X_444_2 as
select x.location_id LOCATION, x.name YOUNGEST, y.name ELDEST
from (select b.location_id,
b.first_name ' ' b.last_name name,
b.birth_date
from (select location_id, max(birth_date) birth_date
from agents
group by location_id) a,
agents b
where a.location_id = b.location_id
and a.birth_date = b.birth_date) x,
(select b.location_id,
b.first_name ' ' b.last_name name,
b.birth_date
from (select location_id, min(birth_date) birth_date
from agents
group by location_id) a,
agents b
where a.location_id = b.location_id
and a.birth_date = b.birth_date) y
where x.location_id = y.location_id;

--Question 4.4.4.3

select location_id, cost
from (select tg.location_id,
l.description,
sum(nvl((select cost
from access_cost
where t.security_level between
nvl(lower_bound_level, t.security_level) and
nvl(upper_bound_level, t.security_level)),
0)) cost,
dense_rank() over(order by sum(nvl((select cost
from access_cost
where t.security_level between
nvl(lower_bound_level,
t.security_level) and
nvl(upper_bound_level,
t.security_level)), 0)) desc) toprank
from information t, targets tg, locations l
where t.target_id = tg.target_id
and tg.location_id = l.location_id
group by tg.location_id, l.description)
where toprank <= 10;

drop table AM_X_444_3;

Create table AM_X_444_3 as
select location_id, cost
from (select tg.location_id,
l.description,
sum(nvl((select cost
from access_cost
where t.security_level between
nvl(lower_bound_level, t.security_level) and
nvl(upper_bound_level, t.security_level)),
0)) cost,
dense_rank() over(order by sum(nvl((select cost
from access_cost
where t.security_level between
nvl(lower_bound_level,
t.security_level) and
nvl(upper_bound_level,
t.security_level)), 0)) desc) toprank
from information t, targets tg, locations l
where t.target_id = tg.target_id
and tg.location_id = l.location_id
group by tg.location_id, l.description)
where toprank <= 10;

--Question 4.4.5.1
select a.first_name ' ' a.last_name NAME
from (select agent_id
from information
where gleaned_date > to_date('4-MAR-1963', 'DD-MON-YYYY')
and agent_id is not null
order by gleaned_date asc) i,
agents a
where a.agent_id = i.agent_id
and rownum <= 1;

drop table AM_X_445_1;

create table AM_X_445_1 as
select a.first_name ' ' a.last_name NAME
from (select agent_id
from information
where gleaned_date > to_date('4-MAR-1963', 'DD-MON-YYYY')
and agent_id is not null
order by gleaned_date asc) i,
agents a
where a.agent_id = i.agent_id
and rownum <= 1;

-- Phase Four General Exercise
-- Question 1.

drop table am_x_gen4_1;

create table am_x_gen4_1
(
fullname varchar2(50), person_type varchar2(50), involvement_status varchar2(50),location varchar2(50)
);


drop table am_agents;
create table am_agents
as(
select a.first_name ' 'a.last_name fullname,

case
when count(m.mission_id) < 4
then 'Number of missions involved in:'' 'count(m.mission_id)
else 'This agent is too busy'
end involvement_status,

'agent' person_type,

l.description

from
agents a, missions_agents m, locations l
where
a.agent_id = m.agent_id
and
l.location_id = a.location_id

group by
a.first_name ' ' a.last_name, l.description
);


drop table am_targets;
create table am_targets
as(
select t.first_name ' 't.last_name fullname,

case
when count(m.mission_id) < 3
then 'Needs more missions'
else 'Number of missions involved in' ' ' count(m.mission_id)
end involvement_status,

'target' person_type,
l.description
from
targets t, missions_targets m, locations l
where
t.target_id = m.target_id
and
l.location_id = t.location_id

group by
t.first_name ' ' t.last_name, l.description
);

drop table insert_am_x_4gen_1;
create table insert_am_x_4gen_1
AS(
select *
from am_agents
union all
select *
from am_targets
)





insert into am_x_4gen_1
select fullname, involvement_status, person_type, locations
from insert_am_x_4gen_1;



--Phase Four General Exercise 2

drop table am_x_4gen_2;

create table am_x_4_gen_2
(
target_id number(6), salience varchar2(20)
);


insert into am_x_4gen_2

select
targets,
case
when
associates_count < 11
then 'WRAITH'
when
associates_count between 11 and 20
then 'NINJA'
when
associates_count between 21 and 30
then 'BANDIT'
else 'BUCCANEER'
end
from
(
select targets, count(*) associates_count

from (
select
t.target_id targets, l.target_id associate

from
targets t, targets l
where
t.location_id = l.location_id

union

select
mt.target_id, mt2.target_id

from
missions_targets mt, missions_targets mt2

where mt.mission_id = mt2.mission_id
)

GROUP BY
targets
)