Guys,
It has been such a long time since I last blogged but a lot has changed since my last blog....
I will be updating this blog regularly in regards to the latest information for you avid technology consultants out there. When I was looking for work, I found the research I invested in compiling the material for the blog insightful and definitely helped me in preparation for the interviews I had. I certainly hope it will also help you!
Tuesday 28 July 2009
Monday 6 October 2008
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;
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;
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
)
Friday 16 November 2007
Market research - Oracle
ORACLE
Oracle is one of the major companies developing database management systems (DBMS), tools for database development, enterprise resource planning software (ERP), customer relationship management software (CRM) and supply chain management (SCM) software.
Founded in 1977 with offices in over 145 countries and a workforce in excess of 60,000 worldwide, Oracle can be regarded as very large software company, second only to Microsoft.
Oracle Consulting is the world's largest consulting organisation, with more than 8,000 consulting professionals supporting Oracle customers and partners in more than 145 countries, with direct access to Oracle Global Product Development and Oracle's award-winning support services.
Virtually synonymous with database technology, Oracles client roaster is a veritable and enviable list of major companies that encompass a variety of industries including a sizable number of Fortune 500 companies (100 of Fortune 100 companies are supported in some capacity by Oracle.) , including public, private and non profit organisations.
Not satisfied with its database domination, Oracle has long wanted to gain ascendancy in the comparatively glamorous world of business process applications (ERP, SCM & CRM). With its enhanced new product line, the company hopes unprecedented profits are just around the corner. But some analysts wonder whether Oracle paid too much.
GRADUATE TECHNICAL CONSULTANT
Working as a consultant with Oracle, you will design and deliver solutions that enable our customers to balance today’s business requirements with the demands of the future. By putting our existing and emerging technologies at our customers’ disposal, we’re able to transform the way they do business and deliver real competitive advantage.
You will join a group of consultants who are skilled technical developers with a track record in delivering innovative solutions using the latest Oracle applications and tools.
Life at Oracle is anything but predictable. Projects, technology, the team you’re working in – you’ll find that things can change extremely fast in this organisation. However, one component that will remain constant throughout your time with us is the ongoing learning and development. You’ll learn a huge amount from your colleagues and the projects you work on, of course, but we also have a full range of both technical and soft skills courses designed to fill in any gaps. You will start with a induction training focused on Consulting skills, methodologies and training on our Oracle’s applications and tools.
DEPARTMENT
Our Consulting Technical Practice is the place for someone looking for a career, working with complex and leading edge technologies, where they will be constantly challenged and required to deepen existing and develop new skills. You will have the opportunity to work at the front end of some of the world's leading organisations that use Oracle technology and be part of an organisation of smart, technically-minded people. People who understand the business process, products and technology.
People who help turn technology into solutions that provide effective results.
FOCUS
Our focus is on working more effectively together and operating as 'One Oracle' as we continue to lead the way in our industry. We are focusing on five key strategic areas:
Customer Success - We can only achieve success for Oracle if our customers achieve success.
Innovation - Innovation is about developing state-of-the-art solutions to address our customers' business requirements.
Partnering Excellence - Partnering is key to Oracle's successes, and we depend on our partners to grow.
Continuous Learning - The objective of Continuous learning is to increase the capability and effectiveness of our managers and employees, to succeed in today's competitive market.
Corporate Social Leadership - Corporate Social Leadership (CSL) is about Oracle taking a leadership role as an exemplary corporate citizen and using our resources to help the community.
Diversity
"Oracle is committed to creating a diverse environment and is proud to be an equal opportunity employer."
WHAT ORACLE OFFERS
We are looking for motivated graduates who want to fast track their career. These individuals will appreciate the advantages of working with people from different cultures with the same dedication to teamwork and excellence. We want candidates who possess determination, initiative, intelligence, who are flexible and good team players.
In return, Oracle will provide you, as a professional, with the opportunities and career progression that will shape you both personally and professionally.
Oracle considerably invests in employees training and in career development, depending on the line of business, includes intensive Foundation Training course, IT, product, sales and ongoing personal skills development training.
Oracle is teamwork orientated, which means it's easy here to strike up good friendships in a truly multicultural environment.
Talent Development at Oracle
0-12 Months
Year one of the programme facilitates the transition from college to the working environment and involves learning technical skills and the development of professional competency through practical experience and training.
Core elements covered during the first year of the programme include Setting Objectives, Assertive Communication, Working Globally, Time Management, Mentoring, Project Management, Influencing Skills in addition to the development specific to your job role. Your passion might be sales, consulting, development, finance or something else entirely. Whatever it is, you will receive specialized programmes to get you up to speed on how we do things at Oracle. In addition you will attend Graduate Days across your region to help you network and build good relationships with other graduates across Europe, the Middle East and Africa (EMEA).
12-18 Months
The remainder of the programme involves the application of skills and responsibilities relevant to your role and planning your next steps with Oracle. Core elements covered in the second year include Action Learning, Creativity & Innovation, and individual Support tailored to you and your needs.
Oracle provides powerful career development opportunities and a supportive environment to foster career self-management. We encourage regular, open, and honest communication between you and your manager. To proactively manage your career, you'll want to seek out the coaching, tools, and advice you need to excel in your current responsibilities and to develop in areas that advance your career goals.
Your career in Oracle can grow in many ways. We recognize and reward you for growing either as a "knowledge leader" (individual contributor role) or as a "people leader" (manager role). You can develop your career on either track, increasing your responsibilities, competencies, and financial rewards. Oracle's success depends more and more on having the right people for the right jobs at the right time. Therefore we are committed to developing internal talent and capabilities to meet our current and future capability needs. By joining our Team, you are committing yourself to an exciting career that provides you with continuous development and career opportunities.
PRODUCTS
PeopleSoft, Inc. was a company that provided Human resource management systems(HRMS), customer relationship management, Manufacturing, Financials, Enterprise Performance Management and Student Administration software solutions to large corporations, governments, and organizations.
The whole software suite of PeopleSoft moved from the traditional client-server based design to web-centric design, called PeopleSoft Internet Architecture (PIA) with their version 8 releases. The end result was that all of a company's business functions could be accessed and run on a web client.
Siebel
Siebel Systems was the dominant Customer Relationship Management vendor in the late 1990s, peaking at 45% of the CRM market share in 2002.
CRM - is a broad term that covers concepts used by companies to manage their relationships with customers, including the capture, storage and analysis of customer, vendor, partner, and internal process information.
Enterprise Resource Planning (ERP) systems integrate (or attempt to integrate) all data and processes of an organization into a unified system. A typical ERP system will use multiple components of computer software and hardware to achieve the integration. A key ingredient of most ERP systems is the use of a unified database to store data for the various system modules.
Customer relationship management (CRM) is a broad term that covers concepts used by companies to manage their relationships with customers, including the capture, storage and analysis of customer, vendor, partner, and internal process information.
The technology requirements of a CRM strategy can be complex and far reaching.
The basic building blocks:
· A database to store customer information. This can be a CRM specific database or an enterprise data warehouse.
Operational CRM requires customer agent support software.
Collaborative CRM requires customer interaction systems, e.g. an interactive website, automated phone systems etc.
Analytical CRM requires statistical analysis software as well as software that manages any specific marketing campaigns.
There are three aspects of CRM which can each be implemented in isolation from each other:
Operational - automation or support of customer processes that include a company’s sales or service representative
Collaborative - direct communication with customers that does not include a company’s sales or service representative
Analytical - analysis of customer data for a broad range of purposes
Supply chain management (SCM) is the process of planning, implementing, and controlling the operations of the supply chain as efficiently as possible. Supply Chain Management spans all movement and storage of raw materials, work-in-process inventory, and finished goods from point-of-origin to point-of-consumption.
Oracle is one of the major companies developing database management systems (DBMS), tools for database development, enterprise resource planning software (ERP), customer relationship management software (CRM) and supply chain management (SCM) software.
Founded in 1977 with offices in over 145 countries and a workforce in excess of 60,000 worldwide, Oracle can be regarded as very large software company, second only to Microsoft.
Oracle Consulting is the world's largest consulting organisation, with more than 8,000 consulting professionals supporting Oracle customers and partners in more than 145 countries, with direct access to Oracle Global Product Development and Oracle's award-winning support services.
Virtually synonymous with database technology, Oracles client roaster is a veritable and enviable list of major companies that encompass a variety of industries including a sizable number of Fortune 500 companies (100 of Fortune 100 companies are supported in some capacity by Oracle.) , including public, private and non profit organisations.
Not satisfied with its database domination, Oracle has long wanted to gain ascendancy in the comparatively glamorous world of business process applications (ERP, SCM & CRM). With its enhanced new product line, the company hopes unprecedented profits are just around the corner. But some analysts wonder whether Oracle paid too much.
GRADUATE TECHNICAL CONSULTANT
Working as a consultant with Oracle, you will design and deliver solutions that enable our customers to balance today’s business requirements with the demands of the future. By putting our existing and emerging technologies at our customers’ disposal, we’re able to transform the way they do business and deliver real competitive advantage.
You will join a group of consultants who are skilled technical developers with a track record in delivering innovative solutions using the latest Oracle applications and tools.
Life at Oracle is anything but predictable. Projects, technology, the team you’re working in – you’ll find that things can change extremely fast in this organisation. However, one component that will remain constant throughout your time with us is the ongoing learning and development. You’ll learn a huge amount from your colleagues and the projects you work on, of course, but we also have a full range of both technical and soft skills courses designed to fill in any gaps. You will start with a induction training focused on Consulting skills, methodologies and training on our Oracle’s applications and tools.
DEPARTMENT
Our Consulting Technical Practice is the place for someone looking for a career, working with complex and leading edge technologies, where they will be constantly challenged and required to deepen existing and develop new skills. You will have the opportunity to work at the front end of some of the world's leading organisations that use Oracle technology and be part of an organisation of smart, technically-minded people. People who understand the business process, products and technology.
People who help turn technology into solutions that provide effective results.
FOCUS
Our focus is on working more effectively together and operating as 'One Oracle' as we continue to lead the way in our industry. We are focusing on five key strategic areas:
Customer Success - We can only achieve success for Oracle if our customers achieve success.
Innovation - Innovation is about developing state-of-the-art solutions to address our customers' business requirements.
Partnering Excellence - Partnering is key to Oracle's successes, and we depend on our partners to grow.
Continuous Learning - The objective of Continuous learning is to increase the capability and effectiveness of our managers and employees, to succeed in today's competitive market.
Corporate Social Leadership - Corporate Social Leadership (CSL) is about Oracle taking a leadership role as an exemplary corporate citizen and using our resources to help the community.
Diversity
"Oracle is committed to creating a diverse environment and is proud to be an equal opportunity employer."
WHAT ORACLE OFFERS
We are looking for motivated graduates who want to fast track their career. These individuals will appreciate the advantages of working with people from different cultures with the same dedication to teamwork and excellence. We want candidates who possess determination, initiative, intelligence, who are flexible and good team players.
In return, Oracle will provide you, as a professional, with the opportunities and career progression that will shape you both personally and professionally.
Oracle considerably invests in employees training and in career development, depending on the line of business, includes intensive Foundation Training course, IT, product, sales and ongoing personal skills development training.
Oracle is teamwork orientated, which means it's easy here to strike up good friendships in a truly multicultural environment.
Talent Development at Oracle
0-12 Months
Year one of the programme facilitates the transition from college to the working environment and involves learning technical skills and the development of professional competency through practical experience and training.
Core elements covered during the first year of the programme include Setting Objectives, Assertive Communication, Working Globally, Time Management, Mentoring, Project Management, Influencing Skills in addition to the development specific to your job role. Your passion might be sales, consulting, development, finance or something else entirely. Whatever it is, you will receive specialized programmes to get you up to speed on how we do things at Oracle. In addition you will attend Graduate Days across your region to help you network and build good relationships with other graduates across Europe, the Middle East and Africa (EMEA).
12-18 Months
The remainder of the programme involves the application of skills and responsibilities relevant to your role and planning your next steps with Oracle. Core elements covered in the second year include Action Learning, Creativity & Innovation, and individual Support tailored to you and your needs.
Oracle provides powerful career development opportunities and a supportive environment to foster career self-management. We encourage regular, open, and honest communication between you and your manager. To proactively manage your career, you'll want to seek out the coaching, tools, and advice you need to excel in your current responsibilities and to develop in areas that advance your career goals.
Your career in Oracle can grow in many ways. We recognize and reward you for growing either as a "knowledge leader" (individual contributor role) or as a "people leader" (manager role). You can develop your career on either track, increasing your responsibilities, competencies, and financial rewards. Oracle's success depends more and more on having the right people for the right jobs at the right time. Therefore we are committed to developing internal talent and capabilities to meet our current and future capability needs. By joining our Team, you are committing yourself to an exciting career that provides you with continuous development and career opportunities.
PRODUCTS
PeopleSoft, Inc. was a company that provided Human resource management systems(HRMS), customer relationship management, Manufacturing, Financials, Enterprise Performance Management and Student Administration software solutions to large corporations, governments, and organizations.
The whole software suite of PeopleSoft moved from the traditional client-server based design to web-centric design, called PeopleSoft Internet Architecture (PIA) with their version 8 releases. The end result was that all of a company's business functions could be accessed and run on a web client.
Siebel
Siebel Systems was the dominant Customer Relationship Management vendor in the late 1990s, peaking at 45% of the CRM market share in 2002.
CRM - is a broad term that covers concepts used by companies to manage their relationships with customers, including the capture, storage and analysis of customer, vendor, partner, and internal process information.
Enterprise Resource Planning (ERP) systems integrate (or attempt to integrate) all data and processes of an organization into a unified system. A typical ERP system will use multiple components of computer software and hardware to achieve the integration. A key ingredient of most ERP systems is the use of a unified database to store data for the various system modules.
Customer relationship management (CRM) is a broad term that covers concepts used by companies to manage their relationships with customers, including the capture, storage and analysis of customer, vendor, partner, and internal process information.
The technology requirements of a CRM strategy can be complex and far reaching.
The basic building blocks:
· A database to store customer information. This can be a CRM specific database or an enterprise data warehouse.
Operational CRM requires customer agent support software.
Collaborative CRM requires customer interaction systems, e.g. an interactive website, automated phone systems etc.
Analytical CRM requires statistical analysis software as well as software that manages any specific marketing campaigns.
There are three aspects of CRM which can each be implemented in isolation from each other:
Operational - automation or support of customer processes that include a company’s sales or service representative
Collaborative - direct communication with customers that does not include a company’s sales or service representative
Analytical - analysis of customer data for a broad range of purposes
Supply chain management (SCM) is the process of planning, implementing, and controlling the operations of the supply chain as efficiently as possible. Supply Chain Management spans all movement and storage of raw materials, work-in-process inventory, and finished goods from point-of-origin to point-of-consumption.
Friday 26 October 2007
Welcome back, now lets start updating
If you are interesting in applying for operations roles within the financial sector I have devised an example CV that you may find relevant to your application. Don’t copy it word for word as I am currently using this template but get some ideas as to what recruiters are looking for.
Dear Sir/Madam,
I am a recent graduate from the xxxx. I achieved a xxxx in xxxx and I am presently undertaking my postgraduate degree in xxxx. I am writing in response to your Full-Time Graduate Analyst role in your Operations Division.
I am very much interested in the operations roles available with xxxx as I feel that my skills and professional experiences are a perfect fit for this role. I would like to apply for the September 2008 intake as I will be finished my MSc at this time. I feel that the skills I will acquire while studying my postgraduate degree will be beneficial to your company.
I became interesting in operations management during the second year of my undergraduate degree whilst studying a module called Operations and Service Management. I was able to learn key concepts such as Just in Time, TQM, Kaizen and Supply Chain Management which all have an impact on organisation providing products and services to end consumers. I have further researched this area of expertise and feel that my current skills and academic qualification are a perfect fit for this role.
I have devoted 3 years of my undergraduate study to the understanding of IT and the impact it has on businesses. During this time I have shown decisiveness, dedication and an ability to be trained, and learn new skills quickly.
Working for xxxx has long been an ambition of mine, collaborating with highly talented individuals who constantly push you for excellence fascinates me. I am in the constant mode of improvement and the traits I will pick up with xxxx during the formative periods of my career will make me a market leader within the industry.
The training, coaching, mentoring and on the job learning all your graduate undertake has made your graduate program the envy of the industry as it helps develop further leader within the financial sector.
I have strong evaluation, planning, and leadership skills. I also have the motivation and drive to be an asset to any company. I consistently present a high-profile, professional demeanour; complemented by a positive "can-do" attitude.
I truly want to become an Analyst with your operations division and I welcome the opportunity to elaborate on how I could make a substantial contribution to your organisation. I look forward to talking with you soon.
Please find enclosed a copy of my CV, detailing all my current skills and qualifications.
Thank you.
Sincerely,
xxxx
Dear Sir/Madam,
I am a recent graduate from the xxxx. I achieved a xxxx in xxxx and I am presently undertaking my postgraduate degree in xxxx. I am writing in response to your Full-Time Graduate Analyst role in your Operations Division.
I am very much interested in the operations roles available with xxxx as I feel that my skills and professional experiences are a perfect fit for this role. I would like to apply for the September 2008 intake as I will be finished my MSc at this time. I feel that the skills I will acquire while studying my postgraduate degree will be beneficial to your company.
I became interesting in operations management during the second year of my undergraduate degree whilst studying a module called Operations and Service Management. I was able to learn key concepts such as Just in Time, TQM, Kaizen and Supply Chain Management which all have an impact on organisation providing products and services to end consumers. I have further researched this area of expertise and feel that my current skills and academic qualification are a perfect fit for this role.
I have devoted 3 years of my undergraduate study to the understanding of IT and the impact it has on businesses. During this time I have shown decisiveness, dedication and an ability to be trained, and learn new skills quickly.
Working for xxxx has long been an ambition of mine, collaborating with highly talented individuals who constantly push you for excellence fascinates me. I am in the constant mode of improvement and the traits I will pick up with xxxx during the formative periods of my career will make me a market leader within the industry.
The training, coaching, mentoring and on the job learning all your graduate undertake has made your graduate program the envy of the industry as it helps develop further leader within the financial sector.
I have strong evaluation, planning, and leadership skills. I also have the motivation and drive to be an asset to any company. I consistently present a high-profile, professional demeanour; complemented by a positive "can-do" attitude.
I truly want to become an Analyst with your operations division and I welcome the opportunity to elaborate on how I could make a substantial contribution to your organisation. I look forward to talking with you soon.
Please find enclosed a copy of my CV, detailing all my current skills and qualifications.
Thank you.
Sincerely,
xxxx
Tuesday 7 August 2007
How to describe yourself
How to Describe Yourself
Self-starter
Analytical
Out-of-the-box thinker
Goal-oriented
Resourceful
Leader
High-energy
Inquisitive
Hard worker
Team-oriented
Successful
Numbers-oriented
Experienced
Recently married
Positive
Eager to learn
How NOT to Describe Yourself
Vacation-oriented
Inflexible
Silicon
Bodacious
Unprincipled
Touchy-feely
Combative
Weak-kneed
Indecisive
Follower
Status-oriented
Whiny
Ill-mannered
Short-tempered
Vengeful
Previously dinged
Self-starter
Analytical
Out-of-the-box thinker
Goal-oriented
Resourceful
Leader
High-energy
Inquisitive
Hard worker
Team-oriented
Successful
Numbers-oriented
Experienced
Recently married
Positive
Eager to learn
How NOT to Describe Yourself
Vacation-oriented
Inflexible
Silicon
Bodacious
Unprincipled
Touchy-feely
Combative
Weak-kneed
Indecisive
Follower
Status-oriented
Whiny
Ill-mannered
Short-tempered
Vengeful
Previously dinged
Monday 6 August 2007
Google are going down the toilet
Literally!
I came across the following page while I was searching for a new ISP, http://www.google.com/tisp/. Yes, thats right Google are continuing their quest for global dominance and expanding their service line. I thought it was a joke at first but after further reading I realised that they were being deadly serious.
I must confess, I have been an avid Google fan for as long as I can remember and I support their philosophy, and organisational culture but honestly do they sincerely want people to put wires down the toilet all in the aid of receiving free broadband service?
I will wait to see if this new trend of sticking wires down the toilet is embraced by the general public.
I came across the following page while I was searching for a new ISP, http://www.google.com/tisp/. Yes, thats right Google are continuing their quest for global dominance and expanding their service line. I thought it was a joke at first but after further reading I realised that they were being deadly serious.
I must confess, I have been an avid Google fan for as long as I can remember and I support their philosophy, and organisational culture but honestly do they sincerely want people to put wires down the toilet all in the aid of receiving free broadband service?
I will wait to see if this new trend of sticking wires down the toilet is embraced by the general public.
Subscribe to:
Posts (Atom)
