One last application
The last step in our quiz system project is to construct an application to display the results of quizzes that students have taken.
I would like to display information such as how many questions a particular student got right on a particular quiz. To be able to answer those kinds of questions we are going to need to make use of some more advanced features in the SQL language.
Setting the default schema
If you would like to run the examples below you should have the quiz database installed in MySQL. (Since you used this database for the assignment, you should already have it installed.)
Also, you will need to make sure that the quiz database is set as the default database in the workbench. To do this, right-click on the quiz database in the Schema view and select the command Set as Default Schema.
Making the responses table more useful
When students submit answers to quiz questions we will be storing their responses in the responses table. One small problem with the way that that table is structured is that although there is a column for the question number, there is no column for the quiz number. This is intentional, since that information is available in the table of questions. If you know the question number, you can go to the questions table and look up the quiz number. This database structure is in keeping with one of the primary goals in relational databases, which is to not have redundant information.
Even so, this arrangement causes some obvious problems. Suppose we wanted to show only the responses that came in for a particular quiz. There is no way right now to run that query on the responses table, since that table does not have a column for quiz number.
The solution to this problem is to use a powerful feature in the SQL language called a join. This feature allows us to synthesize new tables out of existing tables by merging together two or more separate tables.
Here is an example of a SQL join statement.
select * from responses join questions on questions.id = responses.question
This produces a new, larger table that results from merging the responses table with the questions table. A join synthesizes rows by taking every possible row from the first table and merging it with every possible row from the second table. Most of those new rows make no sense, so we will also need to limit the set of rows that are generated by adding an on clause that tells us what rows from the first table to merge with what rows from the second table. The obvious rule in this case is to match rows from the first table that refer to a particular question with only those rows from the second table that refer to the same question.
Another typical problem with joins is that the resulting tables have too many columns. Worse yet, you can also sometimes end up with multiple columns that share the same name but display different information. Here is an example of an updated statement that fixes both of these problems.
select responses.id, student, responses.question, quiz, response, answer, correct from responses join questions on questions.id = responses.question
Here I used a combination of a table name and a column name to indicate which id column I would like to display and which question column I wanted.
Making a view
The SQL code I just constructed shows us handy summary information about student responses. This information is so handy that we might want to make it easy to access this summary information in an even easier way. To do this, we will use the SQL statement to create view.
To make a view in the MySQL workbench we right-click on the Views section in the database structure view and select the New View... command. This brings up a screen where we can define the new view.
In that screen we can enter the following:
create view 'results' as select responses.id, student, responses.question, quiz, response, answer, correct from responses join questions on questions.id = responses.question
and then click the Apply button to make the view. (I have already done this in the example database, so you won't need to do this step.)
Once the view is created we can query it like any other table.
select * from results;
Getting summary information
Once we have graded the quizzes, we will want to display various bits of summary information, such as how many questions each student got right, and how many students answered each of the questions correctly.
The key to extracting summary information in SQL is to use the GROUP BY clause in a select statement. As the name of the command suggests, this clause puts rows into groups if they have a common characteristic. Once we have grouped the rows, we can apply various summary operations such as sum(), min(), and count() to the groups.
Here is an example of the GROUP BY clause being used in combination with the sum() function to make a report of how many questions each student got right.
select student,sum(correct) as score from results where quiz = 5 group by student
Another example is printing a report of how many students got each particular question right.
select question,sum(correct)as correctAnswers from results where quiz = 5 group by question
The summary app
We are now in a position to build the last application for our quiz system. This application will allow the user to generate a couple of useful summary reports.
The app can generate two different reports. The Quiz Summary report allows the user to generate a report that shows how all the students did on a particular quiz. The Student Summary report allows the user to generate a report for a particular student that shows how many questions that student answered and how many questions that student got correct for each of the quizzes in the database.
Here is an example of one of these reports: we ask the system to generate a quiz report for the arithmetic quiz.
The report is fairly crude. After running the relevant query we construct a block of text from the results that come back and then simply place that text in a TextArea for the user to view.
Two queries
To generate these reports I had to construct a pair of SQL statements. Here is the code for those statements. First, the statement that generates the student report:
select quiz, sum(correct) as correct, count(question) as answered from results where student=? group by quiz
The statement that generates the quiz report is a little more elaborate:
select name, sum(correct) as correct, count(question) as answered from results join students on results.student=students.id where quiz=? group by student
An extra step that I had to do in this case has to do with the way that the results view works. That view identifies students by their id numbers. Since we want the summary to show the student's names I had to do a join involving the results view and the students table to get access to the names.