API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Nov 16th, 2017
403
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 16.72 KB | None | 0 0
raw download clone embed print report
  1. --Alinea A
  2. CREATE KEYSPACE Videos WITH replication = {'class':'SimpleStrategy', 'replication_factor':1};
  3.  
  4. USE Videos
  5.  
  6. CREATE TABLE users (
  7.     username text,
  8.     nome text,
  9.     email text,
  10.     registryDate TIMESTAMP,
  11.     PRIMARY KEY(username, nome)
  12. )
  13.  
  14. CREATE TABLE videos (
  15.     userShared text,
  16.     nome text,
  17.     description text,
  18.     upload TIMESTAMP,
  19.     tags list<text>,
  20.     followers list<text>,
  21.     PRIMARY KEY(userShared, nome, upload)
  22. )
  23.  
  24. CREATE TABLE videos2 (
  25.     userShared text,
  26.     nome text,
  27.     description text,
  28.     upload TIMESTAMP,
  29.     tags list<text>,
  30.     followers list<text>,
  31.     PRIMARY KEY(userShared, upload)
  32. )
  33.  
  34. CREATE TABLE videos3 (  
  35.     nome text,
  36.     upload TIMESTAMP,  
  37.     PRIMARY KEY(upload, nome)
  38.  
  39. CREATE TABLE commentsByVideo (
  40.     videoNome text,
  41.     USER text,
  42.     DATE TIMESTAMP,
  43.     PRIMARY KEY(videoNome, DATE)
  44. ) WITH CLUSTERING ORDER BY (DATE DESC)
  45.  
  46. CREATE TABLE commentsByUser (
  47.     videoNome text,
  48.     USER text,
  49.     DATE TIMESTAMP,
  50.     PRIMARY KEY(USER, DATE)
  51. ) WITH CLUSTERING ORDER BY (DATE DESC)
  52.  
  53. CREATE TABLE events (
  54.     videoNome text,
  55.     USER text,
  56.     eventType text,
  57.     eventTime TIMESTAMP,
  58.     videoTime INT,
  59.     PRIMARY KEY(USER, videoNome, eventTime)
  60. )
  61.  
  62. CREATE TABLE ratings (
  63.     videoNome text,
  64.     rating INT,
  65.     PRIMARY KEY(videoNome)
  66. )
  67.  
  68. CREATE TABLE tagsForVideos (
  69.     tag text,
  70.     videoNome text,
  71.     PRIMARY KEY(tag, videoNome)
  72. )
  73.  
  74. --Querries em Json Alinea B
  75. SELECT JSON * FROM ratings
  76. SELECT JSON * FROM tagsForVideos
  77. SELECT JSON * FROM events
  78. SELECT JSON * FROM commentsByUser
  79. SELECT JSON * FROM commentsByVideo
  80. SELECT JSON * FROM videos2
  81. SELECT JSON * FROM videos1
  82. SELECT JSON * FROM users
  83.  
  84. -- Inserções Alinea B
  85.  
  86. BEGIN BATCH
  87. INSERT INTO users(username, nome, email, registryDate) VALUES ('user1', 'Rui', 'r.cbd@gmail.com', dateof(now()))
  88. INSERT INTO users(username, nome, email, registryDate) VALUES ('user2', 'Ana', 'a.cbd@gmail.com', dateof(now()))
  89. INSERT INTO users(username, nome, email, registryDate) VALUES ('user3', 'Joao', 'j.cbd@gmail.com', dateof(now()))
  90. INSERT INTO users(username, nome, email, registryDate) VALUES ('user4', 'Maria', 'm.cbd@gmail.com', dateof(now()))
  91. INSERT INTO users(username, nome, email, registryDate) VALUES ('user5', 'Pedro', 'p.cbd@gmail.com', dateof(now()))
  92. INSERT INTO users(username, nome, email, registryDate) VALUES ('user6', 'Rita', 'ri.cbd@gmail.com', dateof(now()))
  93. INSERT INTO users(username, nome, email, registryDate) VALUES ('user7', 'Jose', 'js.cbd@gmail.com', dateof(now()))
  94. INSERT INTO users(username, nome, email, registryDate) VALUES ('user8', 'Bianca', 'b.cbd@gmail.com', dateof(now()))
  95. INSERT INTO users(username, nome, email, registryDate) VALUES ('user9', 'Lau', 'l.cbd@gmail.com', dateof(now()))
  96. INSERT INTO users(username, nome, email, registryDate) VALUES ('user10', 'Marco', 'ma.cbd@gmail.com', dateof(now()))
  97. APPLY BATCH;
  98.  
  99. BEGIN BATCH
  100. INSERT INTO commentsByVideo(videoNome, USER, DATE) VALUES ('The Greatest Showman On Earth', 'user1', '2000-09-21 18:50' )
  101. INSERT INTO commentsByVideo(videoNome, USER, DATE) VALUES ('Wonder Wheel', 'user2', '2000-09-21 19:50' )
  102. INSERT INTO commentsByVideo(videoNome, USER, DATE) VALUES ('Birth of The Dragon', 'user3', '2000-09-21 20:50' )
  103. INSERT INTO commentsByVideo(videoNome, USER, DATE) VALUES ('Invisible', 'user4', '2000-09-21 17:50' )
  104. INSERT INTO commentsByVideo(videoNome, USER, DATE) VALUES ('Saw: Legacy', 'user5', '2000-09-21 16:50' )
  105. INSERT INTO commentsByVideo(videoNome, USER, DATE) VALUES ('The Greatest Showman On Earth', 'user6', '2000-09-21 15:50' )
  106. INSERT INTO commentsByVideo(videoNome, USER, DATE) VALUES ('Saw: Legacy', 'user10', '2000-09-21 14:50' )
  107. INSERT INTO commentsByVideo(videoNome, USER, DATE) VALUES ('Invisible', 'user7', '2000-09-21 13:50' )
  108. INSERT INTO commentsByVideo(videoNome, USER, DATE) VALUES ('Wonder Wheel', 'user8', '2000-09-21 12:50' )
  109. INSERT INTO commentsByVideo(videoNome, USER, DATE) VALUES ('Birth of The Dragon', 'user9', '2000-09-21 11:50' )
  110. INSERT INTO commentsByVideo(videoNome, USER, DATE) VALUES ('Birth of The Dragon', 'user1', '2000-09-21 12:50' )
  111.  
  112. INSERT INTO commentsByUser(videoNome, USER, DATE) VALUES ('The Greatest Showman On Earth', 'user1', '2000-09-21 18:50' )
  113. INSERT INTO commentsByUser(videoNome, USER, DATE) VALUES ('Wonder Wheel', 'user2', '2000-09-21 19:50' )
  114. INSERT INTO commentsByUser(videoNome, USER, DATE) VALUES ('Birth of The Dragon', 'user3', '2000-09-21 20:50' )
  115. INSERT INTO commentsByUser(videoNome, USER, DATE) VALUES ('Invisible', 'user4', '2000-09-21 17:50' )
  116. INSERT INTO commentsByUser(videoNome, USER, DATE) VALUES ('Saw: Legacy', 'user5', '2000-09-21 16:50' )
  117. INSERT INTO commentsByUser(videoNome, USER, DATE) VALUES ('The Greatest Showman On Earth', 'user6', '2000-09-21 15:50' )
  118. INSERT INTO commentsByUser(videoNome, USER, DATE) VALUES ('Saw: Legacy', 'user10', '2000-09-21 14:50' )
  119. INSERT INTO commentsByUser(videoNome, USER, DATE) VALUES ('Invisible', 'user7', '2000-09-21 13:50' )
  120. INSERT INTO commentsByUser(videoNome, USER, DATE) VALUES ('Wonder Wheel', 'user8', '2000-09-21 12:50' )
  121. INSERT INTO commentsByUser(videoNome, USER, DATE) VALUES ('Birth of The Dragon', 'user9', '2000-09-21 11:50' )
  122. INSERT INTO commentsByUser(videoNome, USER, DATE) VALUES ('Birth of The Dragon', 'user1', '2000-09-21 12:50' )
  123. APPLY BATCH;
  124.  
  125. BEGIN BATCH
  126. INSERT INTO events( videoNome, USER, eventType, eventTime, videoTime) VALUES ('The Greatest Showman On Earth', 'user1', 'play', '2000-09-21 10:50', 300)
  127. INSERT INTO events( videoNome, USER, eventType, eventTime, videoTime) VALUES ('Wonder Wheel', 'user2', 'stop', '2000-09-21 10:50', 200)
  128. INSERT INTO events( videoNome, USER, eventType, eventTime, videoTime) VALUES ('Birth of The Dragon', 'user3', 'pause', '2000-09-21 10:50', 100)
  129. INSERT INTO events( videoNome, USER, eventType, eventTime, videoTime) VALUES ('Invisible', 'user4', 'play', '2000-09-21 10:50', 350)
  130. INSERT INTO events( videoNome, USER, eventType, eventTime, videoTime) VALUES ('Saw: Legacy', 'user5', 'stop', '2000-09-21 10:50', 200)
  131. INSERT INTO events( videoNome, USER, eventType, eventTime, videoTime) VALUES ('The Greatest Showman On Earth', 'user6', 'pause', '2000-09-21 10:50', 300)
  132. INSERT INTO events( videoNome, USER, eventType, eventTime, videoTime) VALUES ('Saw: Legacy', 'user10', 'play', '2000-09-21 10:50', 300)
  133. INSERT INTO events( videoNome, USER, eventType, eventTime, videoTime) VALUES ('Invisible', 'user7', 'stop', '2000-09-21 10:50', 250)
  134. INSERT INTO events( videoNome, USER, eventType, eventTime, videoTime) VALUES ('Wonder Wheel', 'user8', 'pause', '2000-09-21 10:50', 100)
  135. INSERT INTO events( videoNome, USER, eventType, eventTime, videoTime) VALUES ('Birth of The Dragon', 'user9', 'play', '2000-09-21 10:50', 50)
  136. APPLY BATCH;
  137.    
  138. BEGIN BATCH
  139. INSERT INTO tagsForVideos(tag,videoNome) VALUES ('action', 'Birth of The Dragon')
  140. INSERT INTO tagsForVideos(tag,videoNome) VALUES ('action', 'Invisible')
  141. INSERT INTO tagsForVideos(tag,videoNome) VALUES ('comedy', 'Wonder Wheel')
  142. INSERT INTO tagsForVideos(tag,videoNome) VALUES ('romance', 'Wonder Wheel')
  143. INSERT INTO tagsForVideos(tag,videoNome) VALUES ('terror', 'Saw: Legacy')
  144. INSERT INTO tagsForVideos(tag,videoNome) VALUES ('comedy', 'Singing')
  145. INSERT INTO tagsForVideos(tag,videoNome) VALUES ('fantasy', 'Willman')
  146. INSERT INTO tagsForVideos(tag,videoNome) VALUES ('action', 'Saw: Legacy')
  147. INSERT INTO tagsForVideos(tag,videoNome) VALUES ('comedy', 'Pirates walking on Land')
  148. APPLY BATCH;
  149.  
  150. BEGIN BATCH
  151. INSERT INTO videos( userShared, nome, upload, description, tags, followers) VALUES ('user1', 'Birth of The Dragon', '2000-01-21 10:50', 'The dragon kills us all', ['action', 'drama' ], ['user1', 'user2' ])
  152. INSERT INTO videos( userShared, nome, upload, description, tags, followers) VALUES ('user2', 'Invisible', '2000-02-21 10:50', 'The man is invisible', ['action'], ['user3', 'user2' ])
  153. INSERT INTO videos( userShared, nome, upload, description, tags, followers) VALUES ('user4', 'Wonder Wheel', '2000-07-20 10:50', 'Wheel of fortune', ['romance', 'comedy' ], ['user6', 'user7' ])
  154. INSERT INTO videos( userShared, nome, upload, description, tags, followers) VALUES ('user2', 'Saw: Legacy', '2000-06-21 10:50', 'Another SAW', ['terror', 'drama' ], ['user1', 'user2', 'user3' ])
  155. INSERT INTO videos( userShared, nome, upload, description, tags, followers) VALUES ('user1', 'The Greatest Showman On Earth', '2000-09-21 10:50', 'Showman that does magic tricks', ['comedy' ], ['user5', 'user6' ])
  156. INSERT INTO videos( userShared, nome, upload, description, tags, followers) VALUES ('user7', 'Liberty City', '2000-10-21 10:50', 'Another thug', ['drama', 'trhiller'], ['user1', 'user2', 'user4' ])
  157. INSERT INTO videos( userShared, nome, upload, description, tags, followers) VALUES ('user9', 'Pirates walking on Land', '2000-07-21 10:50', 'The movie itself is a mess', ['comedy'], ['user10', 'user9' ])
  158. INSERT INTO videos( userShared, nome, upload, description, tags, followers) VALUES ('user7', 'Room on Fire', '2000-08-11 10:50', 'Comedy like the usual', ['comedy', 'romance' ], ['user4', 'user5' ])
  159. INSERT INTO videos( userShared, nome, upload, description, tags, followers) VALUES ('user3', 'The WillMan', '2000-08-19 10:50', 'The guy talks about Will', ['fantasy' ], ['user1', 'user4', 'user2' ])
  160. INSERT INTO videos( userShared, nome, upload, description, tags, followers) VALUES ('user8', 'Singing', '2000-08-20 10:50', 'She sings poorly', ['comedy' ], ['user5', 'user4' ])
  161. APPLY BATCH;
  162.  
  163. BEGIN BATCH
  164. INSERT INTO videos2(    userShared, nome, upload, description, tags, followers) VALUES ('user1', 'Birth of The Dragon', '2000-01-21 10:50', 'The dragon kills us all', ['action', 'drama' ], ['user1', 'user2' ])
  165. INSERT INTO videos2(    userShared, nome, upload, description, tags, followers) VALUES ('user2', 'Invisible', '2000-02-21 10:50', 'The man is invisible', ['action'], ['user3', 'user2' ])
  166. INSERT INTO videos2(    userShared, nome, upload, description, tags, followers) VALUES ('user4', 'Wonder Wheel', '2000-07-20 10:50', 'Wheel of fortune', ['romance', 'comedy' ], ['user6', 'user7' ])
  167. INSERT INTO videos2(    userShared, nome, upload, description, tags, followers) VALUES ('user2', 'Saw: Legacy', '2000-06-21 10:50', 'Another SAW', ['terror', 'drama' ], ['user1', 'user2', 'user3' ])
  168. INSERT INTO videos2(    userShared, nome, upload, description, tags, followers) VALUES ('user1', 'The Greatest Showman On Earth', '2000-09-21 10:50', 'Showman that does magic tricks', ['comedy' ], ['user5', 'user6' ])
  169. INSERT INTO videos2(    userShared, nome, upload, description, tags, followers) VALUES ('user7', 'Liberty City', '2000-10-21 10:50', 'Another thug', ['drama', 'trhiller'], ['user1', 'user2', 'user4' ])
  170. INSERT INTO videos2(    userShared, nome, upload, description, tags, followers) VALUES ('user9', 'Pirates walking on Land', '2000-07-21 10:50', 'The movie itself is a mess', ['comedy'], ['user10', 'user9' ])
  171. INSERT INTO videos2(    userShared, nome, upload, description, tags, followers) VALUES ('user7', 'Room on Fire', '2000-08-11 10:50', 'Comedy like the usual', ['comedy', 'romance' ], ['user4', 'user5' ])
  172. INSERT INTO videos2(    userShared, nome, upload, description, tags, followers) VALUES ('user3', 'The WillMan', '2000-08-19 10:50', 'The guy talks about Will', ['fantasy' ], ['user1', 'user4', 'user2' ])
  173. INSERT INTO videos2(    userShared, nome, upload, description, tags, followers) VALUES ('user8', 'Singing', '2000-08-20 10:50', 'She sings poorly', ['comedy' ], ['user5', 'user4' ])
  174. APPLY BATCH;
  175.  
  176. BEGIN BATCH
  177. INSERT INTO videos3( nome, upload) VALUES ('Birth of The Dragon', '2000-01-21 10:50')
  178. INSERT INTO videos3( nome, upload) VALUES ('Invisible', '2000-02-21 10:50')
  179. INSERT INTO videos3( nome, upload) VALUES ('Wonder Wheel', '2000-07-20 10:50')
  180. INSERT INTO videos3( nome, upload) VALUES ('Saw: Legacy', '2000-06-21 10:50')
  181. INSERT INTO videos3( nome, upload) VALUES ('The Greatest Showman On Earth', '2000-09-21 10:50')
  182. INSERT INTO videos3( nome, upload) VALUES ('Liberty City', '2000-10-21 10:50')
  183. INSERT INTO videos3( nome, upload) VALUES ('Pirates walking on Land', '2000-07-21 10:50')
  184. INSERT INTO videos3( nome, upload) VALUES ('Room on Fire', '2000-08-11 10:50')
  185. INSERT INTO videos3( nome, upload) VALUES ('The WillMan', '2000-08-19 10:50')
  186. INSERT INTO videos3( nome, upload) VALUES ('Singing', '2000-08-20 10:50')
  187. APPLY BATCH;
  188.  
  189.  
  190. BEGIN BATCH
  191. INSERT INTO ratings(videoNome, rating) VALUES ( 'Birth of The Dragon' , 3)
  192. INSERT INTO ratings(videoNome, rating) VALUES ( 'Birth of The Dragon' , 4)
  193. INSERT INTO ratings(videoNome, rating) VALUES ( 'Invisible' , 2)
  194. INSERT INTO ratings(videoNome, rating) VALUES ( 'Invisible' , 4)
  195. INSERT INTO ratings(videoNome, rating) VALUES ( 'Wonder Wheel' , 3)
  196. INSERT INTO ratings(videoNome, rating) VALUES ( 'Wonder Wheel' , 1)
  197. INSERT INTO ratings(videoNome, rating) VALUES ( 'Saw: Legacy' , 2)
  198. INSERT INTO ratings(videoNome, rating) VALUES ( 'Saw: Legacy' , 5)
  199. INSERT INTO ratings(videoNome, rating) VALUES ( 'The Greatest Showman On Earth' , 4)
  200. INSERT INTO ratings(videoNome, rating) VALUES ( 'The Greatest Showman On Earth' , 3)
  201. INSERT INTO ratings(videoNome, rating) VALUES ( 'Liberty City' , 2)
  202. INSERT INTO ratings(videoNome, rating) VALUES ( 'Liberty City' , 1)
  203. INSERT INTO ratings(videoNome, rating) VALUES ( 'Pirates walking on Land' , 2)
  204. INSERT INTO ratings(videoNome, rating) VALUES ( 'Pirates walking on Land' , 4)
  205. INSERT INTO ratings(videoNome, rating) VALUES ( 'Room on Fire' , 1)
  206. INSERT INTO ratings(videoNome, rating) VALUES ( 'Room on Fire' , 5)
  207. INSERT INTO ratings(videoNome, rating) VALUES ( 'The WillMan' , 4)
  208. INSERT INTO ratings(videoNome, rating) VALUES ( 'The WillMan' ,3)
  209. INSERT INTO ratings(videoNome, rating) VALUES ( 'Singing' ,5)
  210. INSERT INTO ratings(videoNome, rating) VALUES ( 'Singing' ,2)
  211. APPLY BATCH;
  212.  
  213. -- Alinea C)
  214.  
  215. --Permitir a pesquisa de todos os vídeos de determinado autor;
  216. SELECT * FROM videos WHERE autor = 'James Will'
  217. --Permitir a pesquisa de comentários por utilizador, ordenado inversamente pela data;
  218. SELECT * FROM commentsByUser WHERE USER = 'user1'
  219. --Permitir a pesquisa de comentários por vídeos, ordenado inversamente pela data;
  220. SELECT * FROM commentsByVideo WHERE videoNome = 'The Greatest Showman On Earth'
  221. --Permitir a pesquisa do rating médio de um vídeo e quantas vezes foi votado;
  222. SELECT videoNome, avg(rating) AS avgRate, COUNT(rating) AS COUNT  FROM ratings WHERE videoNome = 'The Greatest Showman On Earth'
  223.  
  224. --Alinea D)
  225. USE videos
  226.  
  227. --1. Os últimos 3 comentários introduzidos para um vídeo;
  228. SELECT * FROM commentsByVideo WHERE videoNome = 'The Greatest Showman On Earth'
  229. ORDER BY DATE ASC LIMIT 3
  230.  
  231. --2 Lista das tags de determinado vídeo;
  232. CREATE INDEX videos_name ON videos( nome );
  233. SELECT nome,tags FROM videos WHERE nome = 'The Greatest Showman On Earth'
  234.  
  235. --3 Todos os vídeos com a tag Aveiro;
  236. CREATE INDEX tags_index ON videos( tags );
  237. SELECT nome, tags FROM videos WHERE tags CONTAINS 'action';
  238.  
  239. --4 Os últimos 5 eventos de determinado vídeo realizados por um utilizador;
  240. SELECT USER, videoNome FROM events WHERE  USER= 'user10' AND videoNome = 'Saw: Legacy' LIMIT 5
  241.  
  242. --5 Vídeos partilhados por determinado utilizador (maria1987, por exemplo) num
  243. -- determinado período de tempo (Agosto de 2017, por exemplo);
  244. SELECT userShared, nome FROM videos2 WHERE userShared = 'user8' AND upload >= '2000-08-01 00:00:00' AND upload < '2000-09-01 00:00:00';
  245.  
  246. --6 Os últimos 10 vídeos, ordenado inversamente pela data da partilhada;
  247. SELECT * FROM videos3 ORDER BY upload LIMIT 10; --Não funciona.Order by tem de restringir a partition key.
  248. --Mesmo usando uma tabela onde o upload time é a partition key, teria que o restringir na mesma, o que não vai de encontro à solução
  249.  
  250. --7 Todos os seguidores (followers) de determinado vídeo;
  251. SELECT followers FROM videos WHERE nome = 'Invisible'
  252.  
  253. --8 Todos os comentários (dos vídeos) que determinado utilizador está a seguir (following);
  254. --Esta query requere 2 passos para ser feita. Poderia existir uma tabela que regista para determinado utilizador os videos que ele segue
  255. --Este seria o primeiro passo. O segundo passo seria no lado do cliente, para cada resultado de um select na tabela de cima ir à tabela de comentários por vídeo
  256. --E arranjar esses comentários. Em baixo fica um exemplo da tabela que seria preciso construir para esta query, em conjunto com a já existente
  257. --commentsByVideo
  258. CREATE TABLE followers (
  259.     USER text,
  260.     videoNome text,
  261.     PRIMARY KEY(USER, videoNome)
  262. )
  263.  
  264. --9. Os 5 vídeos com maior rating;
  265. --Pela mesma razão que em 6, é impossível ordenar sem restringir a partition key. Aqui queremos os videos ordenados
  266. --Mas não sabemos quais portanto não os podemos restringir.
  267.  
  268. --10 Uma query que retorne todos os vídeos e que mostre claramente a forma pela qual estão ordenados;
  269. --Mesmo caso que 9 e 6, está query é impossível
  270.  
  271. --11 Lista com as Tags existentes e o número de vídeos catalogados com cada uma delas;
  272. SELECT tag, COUNT(tag) AS counTag FROM tagsForVideos GROUP  BY tag
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!