Real power of databases is in using several datasets in single search. We mention join as main mechanism for
this (in SQL) though there are also subqueries - feel free to use various approaches - supposedly this exercise
won't take more than few minutes of your time!
One of the recent improvements to this site - it was decided to grant certificates
for solving problems specially marked with c-1 tag. But how many such problems were solved by given user?
Let's see, here are some tables:
(id, username)(id, title)(taskid, userid, solved) - no more than 1 solution for any (taskid, userid) pair(id, tag)(tagid, taskid)So for example, I look at user with username john_doe to find corresponding user id. Browsing by this userid
I can find all submissions, though I of course need only those with solved = 1. However, what about tags?
We need to look up tag id corresponding to c-1 and then filter only those submissions which has taskid
mentioned in pair with such tagid in the TagsToTasks table.
Well, we don't pretend it is very clever database structure (perhaps we could profit from merging Tags and
TagsToTasks, i.e. "denormalizing" database a bit) - but since it exists, let's solve this small exercise.
Create a query which returns list of task titles having c-1 tag and solved by person with surname poe.