Text queries give the most powerful access to the education database. A text query consists of two parts separated by the symbol `@`:

• a comma delimited list of parameters to view (the SELECT clause in SQL parliance)
• an 'and' delimited list of conditions (the WHERE clause in SQL parliance)

This query breaks down as follows:

• The two requested parameters are LEANM (Local Education Agency Name) and the test result MCPT0605 which breaks down as follows:
  • M: Subject = Math
  • CP: Measure = Cut point of passing
  • T: Student Subgroup = Total (all students)
  • 0: Test = the main test
  • 6: Grade = 6th
  • 05: Year = 2005
• The condition `@1` gives all districts.

Let's add the 6th grade Math results for 2004 and the percent of students on Free or Reduced Lunch using:
LEANM,MCPT0605,MCPT0604,TOTFRL@1

We can restrict the districts to those with the 50% or more of students receiving Free or Reduced Lunch and with 50% more more White students using:
LEANM,MCPT0605,MCPT0604,TOTFRL,WHITE@50<TOTFRL and 50<WHITE

Query Tools: Each, Average, Improved, and Difference

One of the challenges of researching the education database is the variety of different fields for each state. E.g.: New York reports test results at grades 4 and 8 while Tennessee reports results for grades 3,4,5,6,7,8,9,10,11, and 12. There are also variations in the student subgroups reported, the tests administered and the years of available data. Regular expression matching is used within the 8 character code to meet the challenges of variable fields among states.

Each
To see the district name and the percent passing the 2005 Math test for districts which begin with the letter 'A' in Texas for Each grade use: LEANM,E(MCPT0.05)@LEANM[0]='A'
Note that the period in the grade position of the 8 character code matches all grades and the condition `@LEANM[0]='A'` returns all districts which start with the letter 'A'. In the resulting sortable table there are headers of MCPT0105, MCPT0305, MCPT0405, MCPT0505, MCPT0605, MCPT0705, MCPT0805, MCPT0905, MCPT0A05, and MCPT0a05 indicating Math results at grades of 10, 3, 4, 5, 6, 7, 8, 9, 11. The last column (MCPT0a05) has an `a` in the grade position indicating the weighted average over all grades. You can eliminate the last column using E(MCPT0[^a]05).

Average
To see the district name and percent passing the 2005 Math test in Texas averaged over all grades use: LEANM,A(MCPT0.05)@1. Likewise, to see the district name and percent passing in Texas averaged over all grades and subjects use: LEANM,A(.CPT0.05)@1.

Improved
To see the district name and percent of NCLB student subgroups showing Improvement in Math and Reading in 2005 use: LEANM,I([RM]CP[TWBAHISLD]0a05)@1. Note that the regular expression in the student breakdown position, [TWBAHISLD], indicates Total, White, Black, Asian, Hispanic, American Indian, Students with disability, Limited English, and economically Disadvantaged. Likewise to show this only for districts with a percent minority greater than 50% use: LEANM,I([RM]CP[TWBAHISLD]0a05) as Improved,WHITE as % White@WHITE<50. Note that the select clause here has custom header names instantiated with ' as Improved' and ' as % White'. These 'as' terms are simply for making the resulting sortable table more readable.

Difference
The Difference method can be used in conjunction with the Each and Average methods. To see the Difference between 2004 to 2005 percent passing the 3rd grade Texas Math test of Each NCLB subgroup use: LEANM,E(MCP[TWBAHISLD]03D(05-04))@1. The Difference term can occur anywhere in the 8 character code. To see the difference between the percent pass for Math and for Reading use: LEANM,E(D(M-R)CPT0305)@1

To see the Average Difference of all grades from 2004 to 2005 in percent passing the Massachusetts Math test use: LEANM,A(MCPT0[2-91AB]0D(5-4))@1.

As a way to further explore the query functionality, let's compare this average of differences with the difference of averages using: LEANM,A(MCPT0[2-91AB]0D(5-4)),A(MCPT0[2-91AB]05) - A(MCPT0[2-91AB]04),A(MCPT0[2-91AB]0D(5-4))-A(MCPT0[2-91AB]05) + A(MCPT0[2-91AB]04)@1. Note that there are often small differences between the values in these two columns. E.g.: the Bedford district has a slightly positive average of differences and a slightly negative difference of averages. To see what's going on here let's investigate the Bedford district by selecting the Math results and number of test takers for Each grade using: LEANM,E(MCPT0[2-91AB]0[45]),E(MNTT0[2-91AB]0[45])@LEANM='BEDFORD'. This yields the following result:

The weighted average of scores in 2005 is :
(73*187+58*165+63*187+88*171)/(187+165+187+171) = 70.493
and in 2004 is:
(70*163+69*157+64*159+80*195)/(163+157+159+195) = 71.245
Which gives a change from 2004 to 2005 of -0.75.

For the average of differences the weights are not simply number of test takers (N) but N05*N04/(N05+N04) which gives:
((73-70)*(163*187)/(163+187) + (58-69)*(165*157)/(165+157) + (63-64)*(187*159)/(187+159) + (88-80)*(171*195)/(171+195)) /((163*187)/(163+187) + (165*157)/(165+157) + (187*159)/(187+159) + (171*195)/(171+195)) = +0.06

I hope this very brief guide provides enough information for researchers to get started on their own text queries. Please e-mail me at jameyer@BuckmanU.org with corrections and suggestions.

To see the difference between the Total student population and economically Disadvantaged students in the percent passing the Texas Math test in 2005 averaged over all grades along with this difference for each grade use:
LEANM or LEAID, A(MCPD(T-D)0[2-91AB]05), E(MCPD(T-D)0.05)@1
Note the first SELECT term `LEANM or LEAID` returns the Local Education Agency ID if the Local Education Agency NaMe is not provided.

To repeat this query for only high school grades and to limit the districts returned to those with at least 50% minority and at least 70% Free or Reduced Lunch use:
LEANM or LEAID as District, A(MCPD(T-D)0[91AB]05), E(MCPD(T-D)0[91AB]05), WHITE, TOTFRL@WHITE<50 and 70<TOTFRL
Note that the regular expression in the grade position, [91AB], matches grades 9 (9), 10 (1), 11 (A), and 12 (B).