SQL QUERIES - ppinion/ppinion-db GitHub Wiki

1 - update peepinion.users set user_id = (max user_id + 1 manually code), username = input, email = input, password = input

1A - INSERT INTO peepinion.users ( USERNAME, EMAIL, PASSWORD, CREATE_DT, UPDATE_DT) VALUES (

-- get username input -- get email input -- get password input sysdate(), sysdate() );

2 - select c.topic from ( select a.topic, @rownum:=@rownum+1 as rownum from peepinion.topics a, (select topic_id, sum(num_likes) from peepinion.comments group by topic_id order by sum(num_likes) desc) b, (SELECT @rownum:=0) r where a.topic_id = b.topic_id) c where rownum = 1;

3 -select c.username from ( select a.username, @rownum:=@rownum+1 as rownum from peepinion.users a, (select user_id, sum(num_likes) from peepinion.comments group by user_id order by sum(num_likes) desc) b, (SELECT @rownum:=0) r where a.user_id = b.user_id) c where rownum = 1

4-SELECT USERNAME FROM PEEPINION.USERS

5-select a.topic from peepinion.topics a, (select topic_id, sum(num_likes), @rownum:=@rownum+1 as rownum from peepinion.comments, (SELECT @rownum:=0) r group by topic_id order by create_dt desc) b where a.topic_id = b.topic_id and rownum = 1 and user_id = 'logged in user'

6-select d.side from (select a.topic_id,b.side_id, b.num_likes from peepinion.topics a, (select topic_id, side_id, sum(num_likes) num_likes, @rownum:=@rownum+1 as rownum from peepinion.comments, (SELECT @rownum:=0) r group by side_id order by create_dt desc) b where a.topic_id = b.topic_id and rownum = 1 /and user_id = 'logged in user'/) c, peepinion.sides d where c.topic_id = d.topic_id and c.side_id = d.side_id and d.side_num = 1;

7-select c.num_likes from (select a.topic_id,b.side_id, b.num_likes from peepinion.topics a, (select topic_id, side_id, sum(num_likes) num_likes, @rownum:=@rownum+1 as rownum from peepinion.comments, (SELECT @rownum:=0) r group by side_id order by create_dt desc) b where a.topic_id = b.topic_id and rownum = 1 /and user_id = 'logged in user'/) c, peepinion.sides d where c.topic_id = d.topic_id and c.side_id = d.side_id and d.side_num = 1;

12- select c.topic_type from (select a.topic_type_id from peepinion.topics a, (select topic_id, sum(num_likes), @rownum:=@rownum+1 as rownum from peepinion.comments, (SELECT @rownum:=0) r group by topic_id order by sum(num_likes) desc) b where a.topic_id = b.topic_id and rownum = 1) a, peepinion.topic_types c where a.topic_type_id = c.topic_type_id;

13- select b.num_likes from peepinion.topics a, (select topic_id, sum(num_likes) num_likes, @rownum:=@rownum+1 as rownum from peepinion.comments, (SELECT @rownum:=0) r group by topic_id order by sum(num_likes) desc) b where a.topic_id = b.topic_id and rownum = 1 /and a.topic_type_id = 'input topic type id'/

14- select d.side, c.num_likes from (select a.topic_id,b.side_id, b.num_likes from peepinion.topics a, (select topic_id, side_id, sum(num_likes) num_likes, @rownum:=@rownum+1 as rownum from peepinion.comments, (SELECT @rownum:=0) r group by side_id order by create_dt desc) b where a.topic_id = b.topic_id and rownum = 1 /and a.topic_type_id = 'input topic type id'/) c, peepinion.sides d where c.topic_id = d.topic_id and c.side_id = d.side_id and d.side_num = 1;

15- select a.topic from peepinion.topics a, (select topic_id, sum(num_likes) num_likes, @rownum:=@rownum+1 as rownum from peepinion.comments, (SELECT @rownum:=0) r group by topic_id order by sum(num_likes) desc) b where a.topic_id = b.topic_id and rownum = 1 /and a.topic_type_id = 'input topic type id'/

27- select a.topic from peepinion.topics a where /and a.user_id = 'logged in user'/ /and a.topic_id = 'selected topic id'/ 28-select a.summary from peepinion.topics a where /and a.user_id = 'logged in user'/ /and a.topic_id = 'selected topic id'/ 29 - select a.side from peepinion.sides a where a.side_num = 1 /and a.topic_id = 'selected topic id'/

30 - select b.comment, b.num_likes from peepinion.sides a, (select comment, side_id, sum(num_likes) num_likes, @rownum:=@rownum+1 as rownum from peepinion.comments, (SELECT @rownum:=0) r group by comment_id order by sum(num_likes) desc) b where a.side_id = b.side_id and rownum = 1 and side_num = 1 /and a.topic_id = 'selected topic id'/

33- select e.username from ( select b.comment, b.user_id, b.num_likes from peepinion.sides a, (select comment, user_id, side_id, sum(num_likes) num_likes, @rownum:=@rownum+1 as rownum from peepinion.comments, (SELECT @rownum:=0) r group by comment_id order by create_dt desc) b where a.side_id = b.side_id and rownum = 1 and side_num = 1 /and a.topic_id = 'selected topic id'/) d, peepinion.users e where d.user_id = e.user_id

34- select b.comment, b.num_likes from peepinion.sides a, (select comment, side_id, sum(num_likes) num_likes, @rownum:=@rownum+1 as rownum from peepinion.comments, (SELECT @rownum:=0) r group by comment_id order by create_dt desc) b where a.side_id = b.side_id and rownum = 1 and side_num = 1 /and a.topic_id = 'selected topic id'/

42 - select b.num_likes from peepinion.sides a, (select side_id, sum(num_likes) num_likes from peepinion.comments ) b where a.side_id = b.side_id and side_num = 1 /and a.topic_id = 'selected topic id'/

46 - select a.topic_type from peepinion.topics a,peepinion.topic_types b where a.topic_id = b.topic_id /and a.user_id = 'logged in user'/ /and a.topic_id = 'selected topic id'/

47A -INSERT INTO peepinion.topics ( USER_ID, TOPIC_TYPE_ID, TOPIC, SUMMARY, NUM_SIDES, CREATE_DT, UPDATE_DT) VALUES ( /USER_ID FROM USER LOGGED IN/, /TOPIC_TYPE_ID FROM THE AUTOSELECT DROP DOWN/, 'TOPIC_INPUT', 'SUMMARY_INPUT', 2, SYSDATE(), SYSDATE()

-- SCENARIOS IF TOPIC_TYPE IS NEW-- INSERT INTO peepinion.topic_types ( TOPIC_TYPE, CREATE_DT, UPDATE_DT) VALUES ( 'TOPIC_INPUT', SYSDATE(), SYSDATE() );

47 - update peepinion.topics a set a.topic = 'TOPIC INPUT', a.topic_id = 'generated int', a.user_id = 'logged in user', a.num_sides = 2 /and a.user_id = 'logged in user'/

48 - update peepinion.topics a set a.summary = 'SUMMARYINPUT', a.topic_id = 'generated int', a.user_id = 'logged in user', a.num_sides = 2 /and a.user_id = 'logged in user'/

49 - update peepinion.sides a set a.side = 'SIDE INPUT', a.side_id = 'generate int', /and a.topic_id = 'selected topic id'/ a.topic_id = 'grab topic id', a.side_num = 1 49A - INSERT INTO peepinion.sides ( TOPIC_ID, SIDE, SIDE_NUM, CREATE_DT, UPDATE_DT) VALUES (

-- GRAB TOPIC_ID FROM INPUT, 'SIDE_INPUT', 1 SYSDATE() SYSDATE() );

53 - update peepinion.comments a set a.comment = 'COMMENT INPUT', a.comment_id = 'generate int', /and a.topic_id = 'selected topic id'/ a.user_id = 'grab user id', a.topic_id = 'grab topic id', a.side_id = 'grab side id'

53A - INSERT INTO peepinion.comments ( USER_ID, TOPIC_ID, SIDE_ID, USER_ID_ADDRESSING, COMMENT, CREATE_DT, UPDATE_DT) VALUES ( -- USER_ID LOGGED IN -- TOPIC_ID GRABBED FROM LOGGED IN WEBPAGE -- SIDE ID IN INPUT '', 'HELLO WORLD' SYSDATE(), SYSDATE() );

54 -update peepinion.comments a set a.num_likes = 'LIKE BUTTON CLICK', a.comment_id = 'grab int', /and a.topic_id = 'selected topic id'/ a.user_id = 'grab user id', a.topic_id = 'grab topic id', a.side_id = 'grab side id' 54-update peepinion.users_to_likes a set a.like = 1, a.comment_id = 'grab comment id', /and a.topic_id = 'selected topic id'/ a.user_id = 'grab user id' 54A - 54A- INSERT INTO peepinion.users_to_likes ( USER_ID, COMMENT_ID, LIKES, CREATE_DT, UPDATE_DT) VALUES ( -- GET USER ID , -- GET COMMENT_ID, 1 SYSDATE(), SYSDATE() );

55 - update peepinion.topic_types a set a.topic_type = 'TOPIC_TYPE INPUT', a.topic_type_id = 'generated int'