I'd suggest a moderator move this post to the development forum.
You're going to have a fair bit of work to do doing this via hand-coded SQL. See the schema pic here (not sure if it's up to date). You at least need to join on several tables.
https://docs.moodle.org/dev/File:RolesDatabase.png
Part of the difficulty is that roles are set per 'context' - so a student in one course might be a teacher in another course - so you have to account for that.
You might be better off actually using some Moodle code to do this. But that requires a bit of knowledge of Moodle internals, and you shouldn't place a PHP script on a public web server unless you know what you are doing (or you risk data exposure) - eg you may want to place is_siteadmin() || die(); at the top of the script.
Here's an example of code that may help achieving what you're after, although I can't vouch for its correctness:
https://moodle.org/mod/forum/discuss.php?d=257611#p1117390
You're going to have a fair bit of work to do doing this via hand-coded SQL. See the schema pic here (not sure if it's up to date). You at least need to join on several tables.
https://docs.moodle.org/dev/File:RolesDatabase.png
Part of the difficulty is that roles are set per 'context' - so a student in one course might be a teacher in another course - so you have to account for that.
You might be better off actually using some Moodle code to do this. But that requires a bit of knowledge of Moodle internals, and you shouldn't place a PHP script on a public web server unless you know what you are doing (or you risk data exposure) - eg you may want to place is_siteadmin() || die(); at the top of the script.
Here's an example of code that may help achieving what you're after, although I can't vouch for its correctness:
https://moodle.org/mod/forum/discuss.php?d=257611#p1117390