SQL Code Examples

ICS (Instrument Capabilities Service)

DESCRIBE instruments
SELECT DISTINCT inst_oe2 FROM instrument

SELECT * FROM instrument LIMIT 100
SELECT * FROM instrument WHERE observatory_name='SOHO'
SELECT * FROM instrument WHERE (inst_oe2='EUV' OR inst_oe2='SXR') AND keywords LIKE '%imager%'
SELECT * FROM instrument WHERE (inst_oe2='EUV' OR inst_oe2='SXR') AND keywords LIKE '%spectrometer%'
SELECT * FROM instrument WHERE (keywords LIKE '%spectrometer%' or keywords LIKE '%imager%') AND (inst_oe2='EUV' OR inst_oe2='SXR')
SELECT * FROM instrument WHERE inst_od1!='Earth' AND keywords LIKE '%magnetometer%'
SELECT * FROM instrument WHERE inst_od1='Earth' AND keywords LIKE '%imager%'
SELECT * FROM instrument WHERE keywords LIKE '%coronagraph%'
SELECT * FROM instrument WHERE group_name like '_GONG'
select * from instrument where '2008-12-11 00:00:00'<=time_end and '2008-12-17 23:59:59'>=time_start and inst_od1='Earth'

DESCRIBE observatory
SELECT * FROM observatory LIMIT 20
SELECT * FROM observatory WHERE name='galileo'
SELECT * FROM observatory WHERE name LIKE 'GOES%'
SELECT name,loc_gen,loc_p1,loc_p2,sat_id FROM observatory WHERE loc_gen='ERO' OR loc_gen='LPO' LIMIT 100
SELECT name,loc_gen,loc_p1,loc_p2,longname,date(time_start) as time_start,date(time_end) as time_end FROM observatory WHERE loc_gen <> 'GBO' and loc_gen <> 'ERO'
SELECT name,loc_gen,loc_p1,loc_p2,longname,time_start,time_end,sat_id FROM observatory WHERE loc_gen like 'HP%' order by time_end,time_start
SELECT name,loc_gen,loc_p1,loc_p2,longname,date(time_start) as time_start,date(time_end) as time_end FROM observatory WHERE loc_gen like 'GBO'
SELECT name,loc_gen,loc_p1,loc_p2,longname,date(time_start) as time_start,date(time_end) as time_end FROM observatory where loc_gen='GBO' order by convert(loc_p1, SIGNED INTEGER)

SELECT * FROM instrument JOIN observatory ON instrument.observatory_name=observatory.name AND observatory.loc_gen='GBO'
SELECT * FROM instrument JOIN observatory ON instrument.observatory_name=observatory.name AND observatory.loc_gen='GBO' AND instrument.keywords LIKE '%coronagraph%'  

SELECT * FROM instrument JOIN dpas_pat ON instrument.obsinst_key=dpas_pat.dpas_obsinst AND instrument.inst_od1 LIKE '%Sun%'
SELECT * FROM instrument JOIN dpas_pat ON instrument.obsinst_key=dpas_pat.dpas_obsinst AND instrument.keywords LIKE '%coronagraph%' 

DESCRIBE instrument; DESCRIBE instrument_observatory; DESCRIBE observatory; DESCRIBE flybys

ILS (Instrument Location Service)

SELECT DISTINCT target_obj FROM trajectories

SELECT * FROM trajectories WHERE exe_date_time BETWEEN '2000-01-01' AND '2000-01-03' LIMIT 30
SELECT * FROM trajectories ORDER BY exe_date_time LIMIT 30

SELECT * FROM trajectories WHERE target_obj='GALILEO' LIMIT 50
SELECT * FROM trajectories WHERE target_obj='GALILEO' OR target_obj='CASSINI' LIMIT 50

SELECT * FROM trajectories WHERE exe_date_time BETWEEN '2000-01-01' AND '2000-01-20' and (target_obj='GALILEO' OR target_obj='CASSINI') LIMIT 50

SELECT * FROM keyevents 
SELECT * FROM keyevents WHERE type='flyby'
SELECT * FROM keyevents WHERE observatory='GALILEO'

select * from obs_hbo where time_start between '2000-10-28 00:00:00' and '2011-11-03 23:59:59' 

DESCRIBE keyevents; DESCRIBE trajectories; DESCRIBE obs_hbo

General

SHOW tables
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='observatory'