see comments

/* query 1 */

SELECT
    articles.title,
    upper(articles.body) as content,
    articles.article_id,    
    COUNT(ReplyJoin.comment_id) as replies_count /*aggregate function*/
FROM 
    articles
LEFT OUTER JOIN 
    comments AS ReplyJoin
ON 
    ReplyJoin.article_id = articles.article_id
WHERE 
    (articles.body like '%SWEET%' and articles.user_id < 4) or articles.title like '%blandit%'
GROUP BY 
    articles.article_id
HAVING 
    COUNT(ReplyJoin.comment_id) > 1
ORDER BY 
    articles.title
;

/*query 2*/

SELECT
    lower(articles.title) as lower_title,   
    GROUP_CONCAT(comments.body SEPARATOR ' + ') AS comment_summary, /*aggregate function*/
    articles.article_id
FROM 
    articles
LEFT OUTER JOIN 
    comments
ON 
    comments.article_id = articles.article_id
WHERE
    articles.article_id < 5 and LENGTH(articles.body) > 50 and comments.parent_id is not NULL
GROUP BY 
    articles.article_id
HAVING
    NOT comment_summary like '%bart%'
ORDER BY
    LENGTH(articles.body) ASC
;

/*query 3*/
SELECT MIN(LENGTH(name)) /* aggregate func */
FROM users 
WHERE name != 'Billyjim Cricketwhisker' and (user_id % 2 = 1 or name like '%o%');

/*query 4*/
SELECT
    comment_id,
    MAX(LENGTH(body)) as 'length' /*aggregate function*/
FROM
    comments;