SQL - How to find anomalies (Part 1 - Introduction)

I would like to give you some idea how to find anomalies within your courses.
To be able to do that, you need access to your database and run some SQL reports. Probably the simplest way is to use the ​Configurable Reports block. I am not going to teach you how to use that block. I am not going to teach you how to write SQL scripts. But I am going to give you a few examples and the very basics, so if you have a bare minimal SQL knowledge and if you are not afraid to experiment, you will be fine. I am going to give you the SQL code with a brief explanation, so you can modify the code to match your own needs. Obviously I am going to start with a very simple one, don't worry.

---

First of all, let's talk about the database itself. I am not talking about the 'database' activity within Moodle, I am talking about the actual database in the background to store all your settings and courses and their content.
The Moodle database has around 200 tables, and can be quite daunting at first sight. The good news is that you don't have to understand it all at once. For example, there are ten tables called 'forum_something' (Moodle 3.8.2). If you are interested in the forum module, then obviously you need to understand these tables, and the places they link into core tables.
If you want a better understanding but you are not confident to access the database itself or you don't have permission to do that, you can still use the Database navigation tool. Using that tool you can actually look into the tables and gain a much better understanding of the tables and how they are related to each other and what they are there for. You have read-only permission here, so you wont be able to ruin the database, which could be fatal if you don't know what you are doing.
The other thing we have to clear before we move forward: the prefix of the tables. Normally every table name has an extra bit at the beginning of their name, the default is 'mdl_', therefore the table about forums is called 'mdl_forum' most probably. However the Configurable Reports block uses the 'prefix_' prefix, so the code you write works even if you don't know the actual prefix and also you can share your code with others no matter what their prefix is. I hope you are still with me.
​Let me give you a wee example:

This would be an acceptable code if you are accessing the database directly via phpMyAdmin or MySQL workbench and you know the prefix is 'mdl_' for sure:

MySQL

And this is the code you have to use in the Configurable Reports block, it turns out that a lot of tools don't like the semicolon at the end, so don't use it, otherwise you will get an error message:

MySQL

See the code and the result on my dummy Moodle site: here (you have to log in as a guest).
Let me explain to you the result:

1. id: every row should have a unique id in every table, this one helps to identify the actual forum
2. course: this is the id of the course, which is in the mdl_course table, so the forum table and the course table are linked to each other
3. type: it can be 'news' for example
4. name: the name of the forum, this is what the user can see on the course page
5. intro: or description of the forum
6. and so on, the name of the column helps a lot most of the time

Let's focus on 'news' forums. They are very special forums, nowadays they are called 'Announcements forum' but they used to be called 'News forum'.

Note: If your Moodle site has been upgraded to Moodle 3.1 from an earlier version of Moodle, then only newly created courses will have Announcements. Existing courses and restored courses will still have a forum named 'News forum'.

The Announcements forum is a special forum for general news and announcements. A course may only have one Announcements forum unless it has been imported from another system which supports more than one Announcements forum.
This forum is automatically created in a new course. By default, it is placed in the top of the central section and only teachers and only users with appropriate permissions (by default teachers) can post in it. The forum has forced subscription set by default.
However if your teachers don't know about it or not fully aware of the difference between different forum types, they can create/use a 'regular' forum as a 'news' forum without realising the issues. The permissions are different, for example a student is not allowed to reply to a news forum post but allowed to reply to a 'general' forum post. And the response can go to all the other students and tutors and that is not good at all, I am telling you.
So let's find 'fake' news forums, where the name of the forum suggests it is a misused forum.
More specifically:

• The name of the forum contains the word 'news' or 'announcement' but the type of the forum is not 'news'.

MySQL

You can see how it works here (don't forget to log in as a guest).
I think that is enough for today.
Next time I am going to show you how to create an actual link to those 'fake' news forums.
​Be good.