SQL-EXAMPLES

_____________________________________________________________________________

1.  Exists Example.sql
_____________________________________________________________________________

-- EXISTS Example
/*  
    Select Ozone Daily Summary Data for Monitors that have submitted Precision Data
 In Arizona for 2002
*/
SELECT v.airs_monitor_id, d.daily_coll_date, d.daily_max_sample_value
FROM daily_summaries d, v_monitor_id v
WHERE d.mo_mo_id = v.mo_id
  AND TO_CHAR(d.daily_coll_date,'YYYY') = '2002'
  AND d.sd_duration_code = '1'
  AND d.edt_edt_id IN (0,2)
  AND v.parameter_code = '44201'
  AND v.state_code = '04'
  AND EXISTS (SELECT *
              FROM precision_data p
              WHERE p.mp_mo_mo_id = d.mo_mo_id)
ORDER BY v.airs_monitor_id, d.daily_coll_date

-- NOT EXISTS Example

SELECT * 
FROM monitor_type_assignments 
WHERE mo_mo_id = (SELECT mo_id 
                  FROM v_monitor_id 
                  WHERE airs_monitor_id = '04-013-2001-44201-1')
/*  
    Select Ozone Daily Summary Data for Monitors that have never been a SLAMS
 Monitor In Arizona for 2002
*/
SELECT v.airs_monitor_id, d.daily_coll_date, d.daily_max_sample_value
FROM daily_summaries d, v_monitor_id v
WHERE d.mo_mo_id = v.mo_id
  AND TO_CHAR(d.daily_coll_date,'YYYY') = '2002'
  AND d.sd_duration_code = '1'
  AND d.edt_edt_id IN (0,2)
  AND v.parameter_code = '44201'
  AND v.state_code = '04'
  AND NOT EXISTS (SELECT *
                  FROM monitor_type_assignments m
                  WHERE m.mo_mo_id = d.mo_mo_id
                    AND mt_monitor_type = 'SLAMS')
ORDER BY v.airs_monitor_id, d.daily_coll_date

/*  
    Select Ozone Daily Summary Data for Monitors that have never been a NAMS
 Monitor In Arizona for 2002
*/
SELECT v.airs_monitor_id, d.daily_coll_date, d.daily_max_sample_value
FROM daily_summaries d, v_monitor_id v
WHERE d.mo_mo_id = v.mo_id
  AND TO_CHAR(d.daily_coll_date,'YYYY') = '2002'
  AND d.sd_duration_code = '1'
  AND d.edt_edt_id IN (0,2)
  AND v.parameter_code = '44201'
  AND v.state_code = '04'
  AND NOT EXISTS (SELECT *
                  FROM monitor_type_assignments m
                  WHERE m.mo_mo_id = d.mo_mo_id
                    AND mt_monitor_type = 'NAMS')
ORDER BY v.airs_monitor_id, d.daily_coll_date

_____________________________________________________________________________

2.  Outer Join Example.sql
_____________________________________________________________________________

-- Use for a nullable column with a join
SELECT mo.mo_id, pa.parameter_desc, mo.ms_measurement_scale, ms.measurement_scale_definition 
FROM monitors mo, measurement_scales ms, parameters pa
WHERE mo.ms_measurement_scale = ms.measurement_scale(+)
  AND mo.pa_parameter_code = pa.parameter_code
ORDER BY mo.mo_id

-- Use where a parent record may not have any child records
SELECT m.mo_id, m.pa_parameter_code, po.pot_probe_obstr_type
FROM monitors m, probe_obstructions po 
WHERE m.mo_id = po.mo_mo_id(+)
  AND m.mo_id IN (651, 652)
ORDER BY m.mo_id

-- Outer join to multiple tables
SELECT v.airs_monitor_id, mo.mot_monitor_obj_type, u.uar_name, m.msa_name, c.cmsa_name
FROM monitor_objectives mo, v_monitor_id v, urbanized_areas u, msas m, cmsas c
WHERE v.mo_id = mo.mo_mo_id
  AND v.parameter_code = '44201'
  AND v.state_code = '48'
  AND mo.mot_monitor_obj_type != 'UNKNOWN'
  AND mo.ua_uar_code = u.uar_code(+)
  AND mo.msa_msa_code = m.msa_code(+)
  AND mo.cmsa_cmsa_code = c.cmsa_code(+)

-- A table can only be outer joined to 1 other table
SELECT v.airs_monitor_id, mo.mot_monitor_obj_type, u.uar_name, m.msa_name, c.cmsa_name
FROM monitor_objectives mo, v_monitor_id v, urbanized_areas u, msas m, cmsas c
WHERE v.mo_id = mo.mo_mo_id
  AND v.parameter_code = '44201'
  AND v.state_code = '48'
  AND mo.mot_monitor_obj_type != 'UNKNOWN'
  AND mo.ua_uar_code = u.uar_code(+)
  AND mo.msa_msa_code = m.msa_code(+)
  AND mo.cmsa_cmsa_code = c.cmsa_code(+)
  AND m.cmsa_cmsa_code = c.cmsa_code(+)


_____________________________________________________________________________

3.  Self Join Example.sql
_____________________________________________________________________________

-- Self Join Example
/*  
    Select the 1st 4 maximums for monitor id 04-013-2001-44201-1
    For 2002 for the 8-hour averages
*/
-- Without a Self Join
SELECT v.airs_monitor_id, sm.max_level, sm.max_sample_value
FROM v_monitor_id v, annual_summaries ans, summary_maximums sm
WHERE v.mo_id = ans.mo_mo_id
  AND ans.ans_id = sm.ans_ans_id
  AND sm.max_level < 5
  AND ans.edt_edt_id IN (0,2)
  AND v.airs_monitor_id = '04-013-2001-44201-1'
  AND ans.annual_summary_year = 2002
  AND ans.sd_duration_code = 'W'

-- With Self Joins
SELECT v.airs_monitor_id, sm1.max_sample_value max1, sm2.max_sample_value max2, 
       sm3.max_sample_value max3, sm4.max_sample_value max4
FROM v_monitor_id v, 
     annual_summaries ans, 
     summary_maximums sm1, 
     summary_maximums sm2,
     summary_maximums sm3,
     summary_maximums sm4
WHERE v.mo_id = ans.mo_mo_id
  AND ans.ans_id = sm1.ans_ans_id  -- Join to the 1st instance of Summary_Maximums
  AND ans.ans_id = sm2.ans_ans_id  -- Join to the 2nd instance of Summary_Maximums
  AND ans.ans_id = sm3.ans_ans_id  -- Join to the 3rd instance of Summary_Maximums
  AND ans.ans_id = sm4.ans_ans_id  -- Join to the 4th instance of Summary_Maximums
  AND sm1.max_level = 1  -- Defines SM1 as the 1st Max
  AND sm2.max_level = 2  -- Defines SM2 as the 2nd Max
  AND sm3.max_level = 3  -- Defines SM3 as the 3rd Max
  AND sm4.max_level = 4  -- Defines SM4 as the 4th Max
  AND ans.edt_edt_id IN (0,2)
  AND v.airs_monitor_id = '04-013-2001-44201-1'
  AND ans.annual_summary_year = 2002
  AND ans.sd_duration_code = 'W'
  
_____________________________________________________________________________

4.  Subquery Example.sql
_____________________________________________________________________________

-- Subquery Example
SELECT * 
FROM protocols
WHERE sd_duration_code = (SELECT duration_code
                          FROM sample_durations
                          WHERE duration_desc = '1 HOUR')
  AND sm_pa_parameter_code = '44201'

  
-- Multi-Level Subquery Example
SELECT * 
FROM protocols
WHERE sm_pa_parameter_code IN (SELECT pa_parameter_code
                               FROM parameter_classifications
                               WHERE cls_classification_code = 'CRITERIA'
                                 AND pa_parameter_code IN (SELECT parameter_code
                                                           FROM parameters 
                                                           WHERE un_unit_standard = '007'))
  AND sd_duration_code = '1'
ORDER BY sm_pa_parameter_code, un_unit

-- Correlated Subquery Example
SELECT v.airs_monitor_id, a.annual_summary_year ans_year, a.annual_arith_mean
FROM v_monitor_id v, annual_summaries a
WHERE v.mo_id = a.mo_mo_id
  AND a.annual_summary_year > 2000
  AND v.parameter_code = '44201'
  AND a.sd_duration_code = '1'
  AND a.edt_edt_id IN (0,2)
  AND a.mo_mo_id IN (SELECT mt.mo_mo_id
                     FROM monitor_type_assignments mt
                     WHERE mt.mt_monitor_type = 'SLAMS'
                       AND TO_DATE(a.annual_summary_year||'0101','YYYYMMDD') BETWEEN 
                         TRUNC(mt.monitor_type_begin_date,'YYYY')   
AND TRUNC(NVL(mt.monitor_type_end_date, SYSDATE),'YYYY'))

_____________________________________________________________________________

5.  Union Example.sql
_____________________________________________________________________________

/*  Select all Raw Data (Current and Old) For Mo ID 30 Sorted by Date */
  SELECT sampling_begin_datetime, std_sample_value
  FROM current_data 
  WHERE mp_mo_mo_id = 30
UNION
  SELECT sampling_begin_datetime, std_sample_value
  FROM old_data 
  WHERE mp_mo_mo_id = 30 
ORDER BY 1

/* Select all the Measurement Scales not Used by any Monitor */
  SELECT measurement_scale
  FROM measurement_scales
MINUS
  SELECT ms_measurement_scale
  FROM monitors

/* Select monitors that have precision data*/ 
  SELECT mo_id
  FROM monitors
INTERSECT