Note: If you want to add an entirely new Assessment with several questions, go to “How do I create a New Assessment?

Note: You may want to review “How Do I Create My Own Assessment Questions?” to understand the general process of creating questions. 


1. Choose the Group and the Assessment where you want to add a question.  Click on the “Edit Questions” icon of the assessment you want to work with.  It is the pencil icon. 


2. Click on the “+New Question” button.  Note: There is a New Question button after each existing question.  The new question that you create will be inserted into the list of questions at the location of the “New Question” button that you use.  However, do not worry if your questions are out of order, you can drag them around to rearrange them as you desire. 



3.  The “Question Editor” window opens.  Click on the Question Type drop down button and choose SQL as the type of question. 



4.  Type in the question using the text editor box provided.  You should also assign the number of points for the question, and a size for the Answer Box that is displayed to the students. 


 


5.  The next step is to add the grading rules. To add a grading rule, click on the Add New Rule button and then choose the type of rule that you want to add.  As you can see in the figure, there are about 10 different rules that can be used. The first one, “SQL Contains Term” parses the input query that the student writes. The other rules provide various ways to check and verify the output results.

 

The following list provides a brief explanation of each type of rule. 

  • SQL Contains Term – This looks for an occurrence of a specific term or character string in the written query. It does not check for multiple occurrences. 

  • Number of Records – Counts and checks the total number of output records. 

  • Number of Fields – Counts and checks how many output fields (columns) are returned. 

  • Number of Records with Where Clause – Checks a field for a specific value and counts the number of records with that value in that field. 

  • Value Count in Records – Looks for a number and counts how many times it appears across all records and all columns. 

  • Field Name Exists – Verifies that a field (column) name is part of the result.

  • Sorted by Field(s) – Verifies the sort columns and the order of sort. 

  • All Field Names – Ensures that a set of column names exists in the result. (Tests for more than just one field name.)

  • Field Order – Verifies the order of the output column names.

  • Grading Query – Identifies another query that is used to test the output. Useful with data update queries. 


6. The next image illustrates an SQL Contains Term rule.  


In the first rule, the input query is searched for the word “distinct.”  The search is NOT case sensitive.  Two points are deducted (from the original 10 points) if it is not included.  Note the total of all the deductions for all the rules may exceed the 10 points.  The grading engine will not deduct more than the total assigned to the question.  


When the answer is not correct for a rule, general feedback is provided for that rule.  If you desire to also provide more specific feedback, then you can write the feedback in the textbox.  In this case, we want the student to know that the word distinct is required to eliminate duplicates. 


The second rule checks the query for the word “category.”  One point is assigned. 


The third rule checks for the word “ ‘boots’ “ including the single quotes.  In this case, we are requiring the answer to be all lower case. 


When you finish inserting a rule, or editing a rule, be sure to click the SAVE button. 



7. The next image illustrates several rules that check the output results. 


The first rule checks the Number of Records.  In this case we are expecting 11 records.  Since this is an important part of the question, we have assigned it 6 points. In other words, getting the right number of records is heavily weighted. 


The second rule simply checks to make sure that all three requested columns are provided. 


The third rule verifies that a single record has certain values in specific fields. In this case, we are checking a person’s name along with the product she has purchases. Note the need for single quotes when the answer field has embedded spaces. We assigned this rule 2 points. 


Be sure to click the SAVE button. 



8.  After the rules, you can enter feedback for the entire question.  Notice that you can enter feedback at both the rule level and the question level.  Normally we will enter a correct solution query for the question in this feedback textbox. 


Assessment settings controls when the student gets to see this feedback. 




9.  You can also set the expected difficulty and expected time for this question.  This is purely informational and does not impact the grading or the time allowed.  Note that either save button works to save your changes. 



The Section textbox refers to the paragraph or section in the text that discusses the concepts tested by this question. To determine what value to enter for the Section textbox, first select the Section textbox, then click on the magnifying glass. A list of all paragraphs and sections in the text appears. Clicking and saving the changes places the correct reference in the Section textbox.



10. Your final version of the question, with grading rules, will display this way within the assessment.