On this page we document the demo queries available behind the corresponding buttons
on the main query page. All these demo queries address the various tables in the millimil database.
The following list gives quick links to the corresponding query.
H1
Find halos at a given redshift (snapnum) within a certain part of
the simulation volume (X,Y,Z).
select *
from millimil..MPAHalo
where snapnum=50
and np between 100 and 1000
and x between 10 and 20
and y between 10 and 20
and z between 10 and 20

H2
Find the whole progenitor tree, in depthfirst order, of a halo identified by its haloId
select PROG.*
from millimil..MPAHalo PROG,
millimil..MPAHalo DES
where DES.haloId = 1
and PROG.haloId between DES.haloId and DES.lastprogenitorId

H3
Find the progenitors at a given redshift (snapnum) of all halos of mass(np)≥4000 at a later redshift (snapnum). The progenitors are limited in mass as well.
select DES.haloId as descendant_id,
DES.np as descendant_mass,
PROG.*
from millimil..MPAHalo DES,
millimil..MPAHalo PROG
where DES.snapnum = 63
and DES.np > 4000
and PROG.haloId between DES.haloId and DES.lastprogenitorId
and PROG.snapnum = 30
and PROG.np > 100
order by DES.np desc, PROG.np desc

H4
Find all the halos of mass ≥ 1000 that have just had a major merger, defined by having at least two progenitors of mass ≥ 0.2*descendant mass.
Note that this uses the mass of the subhalos just prior to merging.
In general the minor partner in a merger will have been stripped of part of its mass prior tpo the actual merger,
so this mass ratio may not properly reflect the actual ratio when the merger commenced.
See paper by Bundy, Ellis & Treu (2006, ads) .
select D.haloId,
D.snapnum,
D.np as d_np,
P1.np as p1_np,
P2.np as p2_np
from millimil..MPAHalo P1,
millimil..MPAHalo P2,
millimil..MPAHalo D
where P1.SNAPNUM=P2.SNAPNUM
and P1.haloId < P2.haloId
and P1.descendantId = D.haloId
and P2.descendantId = D.haloId
and P1.np >= .2*D.np
and P2.np >= .2*D.np
and D.np > 1000

H5
Find the mass function of halos at z=0 using logarithmic intervals.
Note the use of the group by [expression] statement.
This groups all rows in the possible result of the SELECT .. FROM .. WHERE .. that have the same value
for the [expression] and for each group calculates an aggregate function, here the count(*).
NOTE this is not exactly equivalent to a histogram, as only bins for which there is at least one row before the binning will be returned.
I.e. when using the result of a query like this in a diagram one should beware of possibly missing bins, and not simply "connect the dots"!
select power(10, .1*(.5+floor(log10(np)/.1))) as mass,
count(*) as num
from millimil..MPAHalo
where snapnum=63
group by power(10, .1*(.5+floor(log10(np)/.1)))
order by mass

HF1
Find all halos residing in background overdensities between 2 and 3, at Gaussian smoothing radius 5 Mpc/h.
select h.*
from millimil..MPAHalo h,
millimil..MMField f
where f.g5 between 2 and 3
and f.snapnum=63
and f.snapnum = h.snapnum
and f.phkey = h.phkey

HF2
This query calculates conditional multiplicity functions
of halos in two different environments defined by different values for the
dark matter desntiy after Gaussian smoothing of 5/h Mpc.
The query consists of two nearly identical parts combined using a union. A union
simply adds the rows coming from both parts together in one result. Note that to identify from which
of these two a row in the result is returned, a special column (here called "lim") is added
which has value 0 for the first part, 1 for the second. We assigne these values in a slightly cumbersome way,
namely as 0*haloId and 0*haloid+1 respecitvley. This is due to a "feature" of
TSQL which does not allow "group by" on literals.
The multiplicity functions themselves are determined in a way that is equivalent to the example in
H5. See there also for a note on missing bins.
select 0*haloId as lim,
power(10, .1*(.5+floor(log10(h.np)/.1))) as mass,
count(*) as num
from millimil..MMHalo h,
millimil..MMField f
where f.g5 between 3 and 5
and f.snapnum=63
and f.snapnum = h.snapnum
and f.phkey = h.phkey
group by 0*haloId, power(10, .1*(.5+floor(log10(h.np)/.1)))
union
select 0*haloId+1 as lim,
power(10, .1*(.5+floor(log10(h.np)/.1))) as mass,
count(*) as num
from millimil..MMHalo h,
millimil..MMField f
where f.g5 between .2 and .4
and f.snapnum=63
and f.snapnum = h.snapnum
and f.phkey = h.phkey
group by 0*haloId+1, power(10, .1*(.5+floor(log10(h.np)/.1)))
order by lim,mass

HF3
Find formation time dependency on background overdensities for halos in particular mass bin.
select zmax, avg(g5) as g5, stdev(g5) as g5err,
avg(g10) as g10, stdev(g10) as g10err,
count(*) as num
from millimil..mmfield f,
( select des.haloId, des.np, des.phkey,max(PROG.redshift) as zmax
from millimil..MPAHalo PROG,
millimil..MPAHalo DES
where DES.snapnum = 63
and PROG.haloId between DES.haloId and DES.lastprogenitorId
and prog.np >= des.np/2
and des.np between 100 and 200
and des.haloId = des.firsthaloinfofgroupid
group by des.haloId, des.np ,des.phkey
) t
where t.phkey = f.phkey
and f.snapnum=63
group by zmax

G1
Find galaxies at a given redshift (snapnum) within a certain part of the simulation volume (X,Y,Z)
select *
from millimil..DeLucia2006a
where snapnum=63
and mag_b between 26 and 18
and x between 10 and 20
and y between 10 and 20
and z between 10 and 20

G2
Find the whole progenitor tree, in depthfirst order, of a galaxy identified by its galaxyId
select PROG.*
from millimil..DeLucia2006a PROG,
millimil..DeLucia2006a DES
where DES.galaxyId = 1
and PROG.galaxyId between DES.galaxyId and DES.lastprogenitorId

G3
Find the progenitors at a given redshift (snapnum) of all galaxies of brightness(magB) ≤ 20 at a later redshift (snapnum). The progenitors are limited in magnitude as well.
select DES.galaxyId as descendant_id,
DES.stellarMass as descendant_mass,
PROG.*
from millimil..DeLucia2006a DES,
millimil..DeLucia2006a PROG
where DES.snapnum = 63
and DES.mag_b < 20
and PROG.galaxyId between DES.galaxyId and DES.lastprogenitorId
and PROG.snapnum = 30
and PROG.mag_b < 19
order by DES.mag_b asc, PROG.mag_b asc

G4
Find all the galaxies of mag_b < 20 that have just had a major merger, defined by having at least two progenitors of mass ≥ 0.2*descendant mass.
select D.galaxyId,
D.snapnum,
D.mag_b as d_mag_b,
D.sfr as d_sfr,
P1.mag_b as p1_mag_b,
P2.mag_b as p2_mag_b,
D.stellarMass as d_mass,
P1.stellarMass as p1_mass,
P2.stellarMass as p2_mass
from millimil..DeLucia2006a P1,
millimil..DeLucia2006a P2,
millimil..DeLucia2006a D
where P1.SNAPNUM=P2.SNAPNUM
and P1.galaxyId< P2.galaxyId
and P1.descendantId = D.galaxyId
and P2.descendantId = D.galaxyId
and P1.stellarMass >= .2*D.stellarMass
and P2.stellarMass >= .2*D.stellarMass
and D.mag_b <20

G5
Find the luminosity function of galaxies at z=0.
Note the comment for query H5 regarding missing bins.
select .2*(.5+floor(mag_b/.2)) as mag,
count(*) as num
from millimil..DeLucia2006a
where mag_b < 10
and snapnum=63
group by .2*(.5+floor(mag_b/.2))
order by mag

G6
Find the TullyFisher relation, Mag vs Vvir for galaxies with bulge/total mass ratio < 0.1.
select vVir, mag_b, mag_v, mag_i, mag_r, mag_k
from millimil..DeLucia2006a
where (bulgeMass < 0.1*stellarMass or bulgeMass is null)
and snapnum = 41

HG1
Find the conditional luminosity functions for galaxies in two ranges of halo masses.
Note the comment for query H5 regarding missing bins.
select 0*galaxyId as lim,
.2*(.5+floor(mag_b/.2)) as mag,
count(*) as num
from millimil..DeLucia2006a g,
millimil..MPAHalo h
where h.np between 2000 and 3000
and h.snapnum=63
and g.haloId = h.haloId
and g.mag_b < 0
group by 0*galaxyId, .2*(.5+floor(mag_b/.2))
union
select 0*galaxyId+1 as lim,
.2*(.5+floor(mag_b/.2)) as mag,
count(*) as num
from millimil..DeLucia2006a g,
millimil..MPAHalo h
where h.np between 200 and 300
and h.snapnum= 63
and g.haloId = h.haloId
and g.mag_b < 0
group by 0*galaxyId+1, .2*(.5+floor(mag_b/.2))
order by lim, mag

HG2
Find average galaxy properties as function of halo mass.
select pow(10, .1*(.5+floor(log(g.np)/.1)))::real as halo_np,
avg(g.stellarMass) as stars_avg,
max(g.stellarMass) as stars_max,
avg(g.bulgeMass) as bulge_avg,
max(g.bulgeMass) as bulge_max,
avg(g.mag_bg.mag_v) as color_avg
from millimil..DeLucia2006a g
where g.snapnum= 63
and g.mag_b < 0
group by halo_np
order by halo_np

GF1
Find galaxy luminosity functions in overdensities at two different values.
select 0*galaxyId as lim,
.2*(.5+floor(mag_b/.2)) as mag,
count(*) as num
from millimil..DeLucia2006a g,
millimil..MMField f
where f.g5 between 3 and 5
and f.snapnum=63
and f.snapnum = g.snapnum
and f.phkey = g.phkey
and g.mag_b < 0
group by 0*galaxyId, .2*(.5+floor(mag_b/.2))
union
select 0*galaxyId+1 as lim,
.2*(.5+floor(mag_b/.2)) as mag,
count(*) as num
from millimil..DeLucia2006a g,
millimil..MMField f
where f.g5 between .2 and .4
and f.snapnum=63
and f.snapnum = g.snapnum
and f.phkey = g.phkey
and g.mag_b < 0
group by 0*galaxyId+1, .2*(.5+floor(mag_b/.2))
order by lim, mag

