Additions

==================================================================

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;