==================================================================
We start by calculating the number of assosciates, here it is stated :
"Salience is dependent on the number of associates a target has. A target's associates are other targets who share the same home location as that target, or who have been on missions with that target."
So, condition #1: A target's associates are other targets who share the same home location as that target
select t1.target_id, t2.target_id assoc
from targets t1, targets t2
where t1.location_id = t2.location_id
and t1.target_id <> t2.target_id
Here, we join table targets to the same targets table to find out which target has the same location_id as the target.
Eg:
target_id, location
1, 10
2, 10
3, 10
if we join the above table to itself by matching the location, we will get
target_id, location, target_id, location
1, 10, 1, 10
1, 10, 2, 10
1, 10, 3, 10
2, 10, 1, 10
2, 10, 2, 10
2, 10, 3, 10
3, 10, 1, 10
3, 10, 2, 10
3, 10, 3, 10
So, for target_id = 1, there are 3 targets in the same location (10), but we have included target_id himself, which we need to exclude, so we add in a condition that target_id <> target_id, the final sql is :
select t1.target_id, t2.target_id assoc
from targets t1, targets t2
where t1.location_id = t2.location_id
and t1.target_id <> t2.target_id
The above will give us each targets and another targets that share the same location as him, but exclude himself.
condition #2: A target's associates are other targets who have been on missions with that target.
Similarly with condition #1, but we use missions_targets table
select mt1.target_id, mt2.target_id assoc
from missions_targets mt1, missions_targets mt2
where mt1.mission_id = mt2.mission_id
and mt1.target_id <> mt2.target_id
order by mt1.target_id
notice everything is the same, except the table is missions_targets (instead of targets), and the join is by mission_id (instead of location_id).
So, the above sql will give us each targets and another targets that have been on missions with him, but exclude himself.
Now, we have to UNION both together:
Let say sql #1 give us:
Target_id, Associate
50, 1001
50, 1002
50, 1003
50, 1004
and sql #2 give us:
Target_id, Associate
50, 1002
50, 1003
50, 1005
50, 1006
If we use "UNION ALL", we will get:
50, 1001
50, 1002
50, 1003
50, 1004
50, 1002 <-- duplicate
50, 1003 <-- duplicate
50, 1005
50, 1006
Notice there are duplicate. As the question has warned us : "Be sure not to count the same associate twice over!"
So, to overcome that, we use "UNION" instead of UNION ALL, which will give us:
50, 1001
50, 1002
50, 1003
50, 1004
50, 1005
50, 1006
so, now let's UNION the 2 actual SQL together:
(select t1.target_id, t2.target_id assoc
from targets t1, targets t2
where t1.location_id = t2.location_id
and t1.target_id <> t2.target_id
)
UNION
(select mt1.target_id, mt2.target_id assoc
from missions_targets mt1, missions_targets mt2
where mt1.mission_id = mt2.mission_id
and mt1.target_id <> mt2.target_id)
This will give us the target, and the associates (that meet the 2 conditions)
Now, we need to count the number of associate so that we can get the SALIENCE rating.
So, we just treat the UNIONed sql above as a table, in our example the table look like :
target_id, assoc
50, 1001
50, 1002
50, 1003
50, 1004
50, 1005
50, 1006
In order to count, we do select target_id, count(assoc) from ... group by target_id, which in our example, will give us:
target_id, count(assoc)
50, 6
Hence, the actual SQL is :
select target_id, count(assoc) SALIANCE
from
(
(select t1.target_id, t2.target_id assoc
from targets t1, targets t2
where t1.location_id = t2.location_id
and t1.target_id <> t2.target_id
)
UNION
(select mt1.target_id, mt2.target_id assoc
from missions_targets mt1, missions_targets mt2
where mt1.mission_id = mt2.mission_id
and mt1.target_id <> mt2.target_id)
)
group by target_id
After we have the COUNT, we need to use CASE to convert it to SALIANCE RATING:
The four salience ratings are defined as follows:
WRAITH: under 11 associates
NINJA: 11-20 associates
BANDIT: 21-30 associates
BUCCANEER: more than 30 associates
So, we USE CASE like this:
select target_id, count(assoc) SALIANCE,
(CASE
WHEN count(assoc) < 11 THEN 'WRAITH'
WHEN count(assoc) between 11 and 20 THEN 'NINJA'
WHEN count(assoc) between 21 and 30 THEN 'BANDIT'
WHEN count(assoc) > 30 THEN 'BUCCANDEER'
END
)
from
(
(select t1.target_id, t2.target_id assoc
from targets t1, targets t2
where t1.location_id = t2.location_id
and t1.target_id <> t2.target_id
)
UNION
(select mt1.target_id, mt2.target_id assoc
from missions_targets mt1, missions_targets mt2
where mt1.mission_id = mt2.mission_id
and mt1.target_id <> mt2.target_id)
)
group by target_id
If you run this sql, you will get the target_id, the count of associate, and the RATING base on the count
but the question only ask for TARGET_ID and SALIANCE (rating), and also Using the 'inserting through a SELECT' technique from Phase 2, insert every TARGET_ID from the TARGETS table, and his 'salience' rating into AM_X_GEN_2.
so, we do first create the table
CREATE TABLE AM_X_4GEN_2 (TARGET_ID NUMBER(6), SALIENCE VARCHAR2(20) );
then insert:
INSERT INTO AM_X_4GEN_2
select target_id,
(CASE
WHEN count(assoc) < 11 THEN 'WRAITH'
WHEN count(assoc) between 11 and 20 THEN 'NINJA'
WHEN count(assoc) between 21 and 30 THEN 'BANDIT'
WHEN count(assoc) > 30 THEN 'BUCCANDEER'
END
)
from
(
(select t1.target_id, t2.target_id assoc
from targets t1, targets t2
where t1.location_id = t2.location_id
and t1.target_id <> t2.target_id
)
UNION
(select mt1.target_id, mt2.target_id assoc
from missions_targets mt1, missions_targets mt2
where mt1.mission_id = mt2.mission_id
and mt1.target_id <> mt2.target_id)
)
group by target_id;
Monday, 6 October 2008
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
)
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
)
Subscribe to:
Posts (Atom)
