CS 474 GP3: Queries
James Madison University, Spring 2014 Semester

Electronic submission due Mar 7 at 11:59 PM.
Each group commits a queries directory via svn.

Overview

The main goal of this assignment is for you to finish designing the database back-end of your application. We will begin implementing the front-end (i.e., web-based interface) after spring break. You have two objectives at this point:

  1. Revise your schema design, based on your experience writing queries for lab and specific feedback from the instructor about GP2.

  2. Design and implement ten or more queries that your application will use. Your queries should be both interesting and comprehensive. Think about the questions that you want to ask and be able to write queries that answer them.

Note that these two steps should be iterative. You may find while writing queries that you are missing some of the data you need. In that case, you should revise and rerun your scripts from GP2. Be sure to commit any changes you make to your code in subversion, and document each revision with a "-m" message.

Please do NOT wait to start until a few days before the deadline!

Instructions

One group member should create a queries directory in your svn repository. This directory should contain at least ten .sql files when you have completed this assignment. As before, each member of your group must make at least one commit, and all commits should have a brief "-m" message in svn.

-- Looking at the state-wide December 1st counts for a given year and grade 
-- level, how many students were there for each primary disability type?-- 
-- Parameters: school_year, grade_code --  
SELECT   primary_disability_type AS dis_type,   
dec1_cnt FROM dec_child_count WHERE school_year = '2011-2012'   
AND level_code = 'STATE'   
AND grade_code = '01'   
AND primary_disability_type IS NOT NULL   
-- do not consider sub-groups   
AND federal_race_code IS NULL   
AND gender IS NULL    
-- disability_flag = 'Y'   
AND lep_flag IS NULL   
AND disadvantaged_flag IS NULL;

See above for an example of how your queries should look for this assignment. The filename should resemble a function or method name for the query. Each file should contain a brief comment at the top that explains the query in simple English. You should also identify any parameters to the query in the comment.

You do not need to parameterize your queries at this point (i.e., using ?'s or variable names). Simply use hard-coded values that illustrate meaningful query results. For example, you may use a particular school name and/or year in a WHERE clause, rather than making the query work for any given school name and/or year.

Here are several ideas of queries you may need to write, in no particular order: