Understanding Complex MySQL JOIN

Fetching complex data from multiple table using nested MySQL queries, JOIN operations, LIKE operator and aggregate functions(eg. GROUP BY, GROUP_CONCAT, etc.)