/* query 1 */
SELECT
articles.title,
upper(articles.body) as content, /*EVIDENCE HERE*/
articles.article_id,
COUNT(ReplyJoin.comment_id) as replies_count
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, /*EVIDENCE HERE*/
GROUP_CONCAT(comments.body SEPARATOR ' + ') AS comment_summary,
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) /*EVIDENCE HERE*/ > 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
users.name,
comments.body as 'content',
HEX(SUBSTRING_INDEX(comments.body, ' ', -1)) as 'secret', /*EVIDENCE HERE*/
COUNT(users.name) as 'stack'
FROM
comments
LEFT OUTER JOIN
users
ON
users.user_id = comments.user_id
LEFT OUTER JOIN
comments as parent_comment
ON
comments.parent_id = parent_comment.comment_id
WHERE
((comments.parent_id % 2) = 0 and users.name != 'jables') or comments.parent_id = 5
GROUP BY
secret
HAVING
secret != HEX('billy') and stack = 2 /*EVIDENCE HERE*/
ORDER BY
secret DESC, users.name ASC
;
/* query 4 */
SELECT
(
SELECT
concat_ws(' - ', articles.article_id, articles.title, COUNT(comments.comment_id)) /*EVIDENCE HERE*/
FROM
articles
LEFT OUTER JOIN
comments
ON
comments.article_id = articles.article_id
GROUP BY
articles.article_id
ORDER BY
COUNT(comments.comment_id) DESC
LIMIT 1
)
AS 'article with most replies (id - title - count)',
(
SELECT
concat_ws(' - ', users.name, COUNT(comments.comment_id)) /*EVIDENCE HERE*/
FROM
users
LEFT OUTER JOIN
comments
ON
users.user_id = comments.user_id
GROUP BY
users.user_id
ORDER BY
COUNT(comments.comment_id) DESC
LIMIT 1
)
AS 'user with most comments (name - count)',
(
SELECT
CONCAT_WS(' - ', article_id, MAX(LENGTH(body))) /*EVIDENCE HERE*/
FROM
articles
)
AS 'article with most chars (id - count)',
(
SELECT
CONCAT_WS(' - ', comment_id, MAX(LENGTH(body))) /*EVIDENCE HERE*/
FROM
comments
)
AS 'comment with most chars (id - count)';
;
