SHARE
TWEET

Untitled

a guest Sep 29th, 2019 99 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- SQL_Practice2 Friendbook.
  2. -- 1).
  3. SELECT DISTINCT hometown FROM users ORDER BY hometown;
  4. -- 2).
  5. SELECT fr.firstname||' '||fr.lastname AS "Friend Name", fr.birthday
  6. FROM friends AS fr
  7. JOIN users us ON fr.user_id=us.user_id
  8. WHERE fr.birthday LIKE '%SEP%' AND us.user_id=1;
  9. -- 3).
  10. SELECT user_id, firstname, lastname, gender
  11. FROM users
  12. WHERE birthday IS NULL OR hometown IS NOT NULL;
  13. -- 4).
  14. SELECT news.post_date, news.content, uc.comm_date, uc.COMMENT
  15. FROM user_comments uc
  16. JOIN news ON user_comments.news_id=news.news_id;
  17. -- 5).
  18. SELECT users.firstname||' '||users.lastname AS "User Name", news.post_date
  19. FROM news
  20. JOIN users ON news.user_id=users.user_id
  21. WHERE news.post_date>='12-AUG-2013' AND
  22.     news.post_date<='24-SEP-2013';
  23. -- 6).
  24. SELECT post_date||' '||content AS "Post Detail"
  25. FROM news
  26. WHERE location IN('Bangkok', 'Chiang Mai', 'Phuket', 'Pattaya')
  27. ORDER BY location, post_date DESC;
  28. -- 7).
  29. SELECT COUNT(gender) AS "Numbers of female user." FROM users
  30. WHERE gender='Female';
  31. -- 8).
  32. SELECT us.firstname||' '||us.lastname AS "User Name", COUNT(*) AS "Numbers of friend."
  33. FROM friends fr
  34. JOIN users us ON fr.user_id=us.user_id
  35. GROUP BY us.firstname||' '||us.lastname
  36. ORDER BY us.firstname||' '||us.lastname;
  37. -- 9).
  38. SELECT news.news_id, COUNT(*) AS "#likes."
  39. FROM user_likes
  40. JOIN news ON user_likes.news_id=news.news_id
  41. WHERE news.user_id=1
  42. GROUP BY news.news_id;
  43. -- 10).
  44. SELECT users.user_id, COUNT(*) AS "Numbers of comments without HBD"
  45. FROM users
  46. JOIN friends fr ON users.user_id=fr.user_id
  47. JOIN user_comments uc ON uc.friend_user_id=fr.friend_user_id
  48. WHERE uc.COMMENT NOT LIKE '%HBD%' AND users.user_id=1;
  49. GROUP BY users.user_id HAVING COUNT(*)>10;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top