SELECT 	est.s_rank, sn.scientific_name sname
FROM	element_subnational est,
			scientific_name sn
WHERE	
est.sname_id = sn.scientific_name_id;

SELECT eo.eo_id, egt.elcode_bcd, est.element_subnational_id, est.s_rank, sn.scientific_name, egt.g_rank
FROM eo, element_subnational est, element_national ent, element_global egt, scientific_name sn
WHERE eo.rec_create_date BETWEEN to_date('01-01-2003', 'dd-mm-yyyy') AND to_date('31-12-2003', 'dd-mm-yyyy') AND
  eo.element_subnational_id = est.element_subnational_id AND
  est.element_national_id = ent.element_national_id AND
  ent.element_global_id = egt.element_global_id AND
  egt.gname_id = sn.scientific_name_id; 



SELECT eo.eo_id, dr.basic_eo_rank_desc eo_rank
FROM 	eo, d_basic_eo_rank dr
WHERE eo.d_basic_eo_rank_id = dr.d_basic_eo_rank_id(+);


SELECT eo.eo_id, 
NVL(dr.basic_eo_rank_desc, 'Not ranked!!') eo_rank
FROM 	eo, d_basic_eo_rank dr
WHERE eo.d_basic_eo_rank_id = dr.d_basic_eo_rank_id(+);



SELECT est.element_subnational_id, 
			count(eo.eo_id)
FROM   element_subnational est, eo
WHERE est.subnation_id = 28 AND
			est.element_subnational_id = eo.element_subnational_id
GROUP BY est.element_subnational_id;


SELECT dr.basic_eo_rank_desc EO_RANK, count(eo.eo_id)
FROM d_basic_eo_rank dr, eo
WHERE eo.d_basic_eo_rank_id = dr.d_basic_eo_rank_id
GROUP BY dr.basic_eo_rank_desc;

select count(*) from eo;


SELECT NVL(dr.basic_eo_rank_desc, 'NULL VALUE!!!!') EO_RANK, count(eo.eo_id)
FROM d_basic_eo_rank dr, eo
WHERE eo.d_basic_eo_rank_id = dr.d_basic_eo_rank_id(+)
GROUP BY dr.basic_eo_rank_desc

SELECT eo.eo_id, 
FROM eo, eo_managed_area eoma
WHERE eo.eo_id = eoma.eo_id AND
	eoma.managed_area_id in 
			(SELECT managed_area_id
			FROM ma
			WHERE managed_area_name = 'Buttermilk Falls State Park');

CREATE OR REPLACE VIEW EO_2003_VW (eo_id, elcode, eo_rank, est_id, s_rank,  sname, g_rank) AS
SELECT eo.eo_id, egt.elcode_bcd, dr.basic_eo_rank_cd, est.element_subnational_id, est.s_rank, sn.scientific_name, egt.g_rank
FROM eo, element_subnational est, element_national ent, element_global egt, scientific_name sn, d_basic_eo_rank dr
WHERE eo.rec_create_date BETWEEN to_date('01-01-2003', 'dd-mm-yyyy') AND to_date('31-12-2003', 'dd-mm-yyyy') AND
  eo.element_subnational_id = est.element_subnational_id AND
  est.element_national_id = ent.element_national_id AND
  ent.element_global_id = egt.element_global_id AND
  egt.gname_id = sn.scientific_name_id AND
  eo.d_basic_eo_rank_id = dr.d_basic_eo_rank_id; 


SELECT * 
FROM eo_2003_vw
WHERE eo_rank = 'B';

SELECT eovw.eo_id, eovw.eo_rank, eovw.elcode 
FROM eo_2003_vw EOVW, eo_managed_area eoma
WHERE eovw.eo_id = eoma.eo_id AND
		eoma.managed_area_id in 
		(SELECT managed_area_id
		FROM ma
		WHERE managed_area_name = 'Buttermilk Falls State Park');