Lecture 11: Advanced Querying

Administering A Quiz Via HTML. PHP, and MySQL

We all tried our hand at an online quiz at http://www.communitylearningpartnership.org/quiz.php. The results were stored in a Quiz table in an online database on the site. Take a look at the PHP code associated with the quiz (see the top half of the downloadable file - the bottom half lets you recreate the database locally on your own MAMP or WAMP installation). We created a few queries together in class to investigate the responses.

Query Examples

We wrote a quick query to look at number of quiz takers that answered each option:


SELECT q1, count( q1 ) 
FROM `Quiz` 
GROUP BY q1
ORDER BY q1 ASC

We extended that query to look at what percent of answers were the correct answer for a question:


SELECT q1, count( q1 )*100 /14 AS CORRECT_PERCENT
FROM `Quiz`
GROUP BY q1
HAVING q1 = 'c'

We generalized that query to look at the percentage of students that answered each available answer:


SELECT q1, count( q1 )*100 /14 AS PERCENT
FROM `Quiz`
GROUP BY q1

And, then we ran a query across all answers to see how many times the answer c was chosen (c being the third answer counting vertically of the four available):


SELECT (SELECT (count( q1 ))
FROM `Quiz`
WHERE q1='c')
+
(SELECT (count( q2 ))
FROM `Quiz`
WHERE q2='c')
+
(SELECT (count( q3 ))
FROM `Quiz`
WHERE q3='c')
+
(SELECT (count( q4 ))
FROM `Quiz`
WHERE q4='c')
+
(SELECT (count( q5 ))
FROM `Quiz`
WHERE q5='c')
+
(SELECT (count( q6 ))
FROM `Quiz`
WHERE q6='c')
+
(SELECT (count( q7 ))
FROM `Quiz`
WHERE q7='c')
+
(SELECT (count( q8 ))
FROM `Quiz`
WHERE q8='c')
+
(SELECT (count( q9 ))
FROM `Quiz`
WHERE q9='c')
+
(SELECT (count( q10 ))
FROM `Quiz`
WHERE q10='c')
+
(SELECT (count( q11 ))
FROM `Quiz`
WHERE q11='c')
+
(SELECT (count( q12 ))
FROM `Quiz`
WHERE q12='c')
+
(SELECT (count( q13 ))
FROM `Quiz`
WHERE q13='c')
+
(SELECT (count( q14 ))
FROM `Quiz`
WHERE q14='c')
AS Num_Cs

which returns to us the answer of 64 (which is 32.6531% of all answers). We can calculate the percentage by putting an extra set of parentheses around all the subqueries and then dividing by 1.96 before giving the answer an alias.

More Query Examples

We then loaded the SQL from the citizen science examples and designed the following queries:

We took a look at the count of trawl sessions by school group:


SELECT org, COUNT(org) FROM session GROUP BY org ORDER BY COUNT(org) DESC

We took at look at wind direction information for summer trawl sessions:


SELECT w_direction, COUNT(w_direction) FROM weather GROUP BY w_direction ORDER BY COUNT(w_direction) DESC

We then took at look at wind direction composites for winter seal count sessions:


SELECT wind_direction, COUNT(wind_direction) FROM seal GROUP BY wind_direction ORDER BY COUNT(wind_direction) DESC

We then looked at the average number of counted seals that had hauled themselves out of the water by weather type:


SELECT weather, sum( count_hauled )/count(weather) AS Num
FROM `seal` 
GROUP BY weather
ORDER BY Num DESC

We realized that we could clean up the data to consolidate weather types that seemed identical. For example we updated to Cloudy all weather types that had the word Clouldy in them:


UPDATE seal SET weather='Cloudy' WHERE weather like '%Cloudy%'

We looked closer at the seal reporting form and saw that the weather types had been standardized more recently with a drop-down menu item for the weather field in the form. We re-ran the query to pick-up those reports that had been entered after the standardization of the field on the form:


SELECT weather, sum( count_hauled )/count(weather) AS Num
FROM `seal` 
WHERE date > '2009-10-31'
GROUP BY weather
ORDER BY Num DESC