-- SQL_Practice2 Friendbook.
-- 1).
SELECT DISTINCT hometown FROM users ORDER BY hometown;
-- 2).
SELECT fr.firstname||' '||fr.lastname AS "Friend Name", fr.birthday
FROM friends AS fr
JOIN users us ON fr.user_id=us.user_id
WHERE fr.birthday LIKE '%SEP%' AND us.user_id=1;
-- 3).
SELECT user_id, firstname, lastname, gender
FROM users
WHERE birthday IS NULL OR hometown IS NOT NULL;
-- 4).
SELECT news.post_date, news.content, uc.comm_date, uc.COMMENT
FROM user_comments uc
JOIN news ON user_comments.news_id=news.news_id;
-- 5).
SELECT users.firstname||' '||users.lastname AS "User Name", news.post_date
FROM news
JOIN users ON news.user_id=users.user_id
WHERE news.post_date>='12-AUG-2013' AND
    news.post_date<='24-SEP-2013';
-- 6).
SELECT post_date||' '||content AS "Post Detail"
FROM news
WHERE location IN('Bangkok', 'Chiang Mai', 'Phuket', 'Pattaya')
ORDER BY location, post_date DESC;
-- 7).
SELECT COUNT(gender) AS "Numbers of female user." FROM users
WHERE gender='Female';
-- 8).
SELECT us.firstname||' '||us.lastname AS "User Name", COUNT(*) AS "Numbers of friend."
FROM friends fr
JOIN users us ON fr.user_id=us.user_id
GROUP BY us.firstname||' '||us.lastname
ORDER BY us.firstname||' '||us.lastname;
-- 9).
SELECT news.news_id, COUNT(*) AS "#likes."
FROM user_likes
JOIN news ON user_likes.news_id=news.news_id
WHERE news.user_id=1
GROUP BY news.news_id;
-- 10).
SELECT users.user_id, COUNT(*) AS "Numbers of comments without HBD"
FROM users
JOIN friends fr ON users.user_id=fr.user_id
JOIN user_comments uc ON uc.friend_user_id=fr.friend_user_id
WHERE uc.COMMENT NOT LIKE '%HBD%' AND users.user_id=1;
GROUP BY users.user_id HAVING COUNT(*)>10;