Advertisement
BigBlueLemon

Untitled

Dec 19th, 2020
326
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DROP TABLE user_rate_publication;
  2.  
  3. DROP TABLE publication_has_genre;
  4.  
  5. DROP TABLE comments;
  6.  
  7. DROP TABLE publications;
  8.  
  9. DROP TABLE users;
  10.  
  11. DROP TABLE genres;
  12.  
  13.  
  14. CREATE TABLE users
  15.   (
  16.      id       INT PRIMARY KEY,
  17.      username VARCHAR2(20) UNIQUE NOT NULL,
  18.      password_hash VARCHAR2(20) NOT NULL CHECK (LENGTH(password_hash) > 5)
  19.   );
  20.  
  21. CREATE TABLE publications
  22.   (
  23.      id         INT PRIMARY KEY,
  24.      title      VARCHAR2(100) NOT NULL,
  25.      content    VARCHAR2(6398) NOT NULL,
  26.      created_at DATE DEFAULT CURRENT_DATE,
  27.      n_rating   INT DEFAULT 0 NOT NULL CHECK (n_rating >= 0),
  28.      sum_rating INT DEFAULT 0 NOT NULL CHECK (sum_rating >= 0),
  29.      user_id    INT,
  30.      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
  31.   );
  32.  
  33. CREATE TABLE genres
  34.   (
  35.      id   INT PRIMARY KEY,
  36.      name VARCHAR2(20) NOT NULL
  37.   );
  38.  
  39. CREATE TABLE comments
  40.   (
  41.      id             INT PRIMARY KEY,
  42.      content        VARCHAR2(200) NOT NULL CHECK (content != ''),
  43.      created_at     DATE DEFAULT CURRENT_DATE,
  44.      user_id        INT,
  45.      publication_id INT,
  46.      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  47.      FOREIGN KEY (publication_id) REFERENCES publications(id) ON DELETE CASCADE
  48.   );
  49.  
  50. CREATE TABLE publication_has_genre
  51.   (
  52.      publication_id INT,
  53.      genre_id    INT,
  54.      PRIMARY KEY (publication_id, genre_id),
  55.      FOREIGN KEY (publication_id) REFERENCES publications(id) ON DELETE CASCADE,
  56.      FOREIGN KEY (genre_id) REFERENCES genres(id) ON DELETE CASCADE
  57.   );
  58.  
  59. CREATE TABLE user_rate_publication
  60.   (
  61.      user_id        INT,
  62.      publication_id INT,
  63.      rating         INT CHECK (rating BETWEEN 1 AND 5),
  64.      PRIMARY KEY (user_id, publication_id),
  65.      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  66.      FOREIGN KEY (publication_id) REFERENCES publications(id) ON DELETE CASCADE
  67.   );
  68.  
  69. --Insert data
  70.  
  71. INSERT INTO genres VALUES (1, 'drama');
  72. INSERT INTO genres VALUES (2, 'action');
  73. INSERT INTO genres VALUES (3, 'adventure');
  74. INSERT INTO genres VALUES (4, 'comedy');
  75. INSERT INTO genres VALUES (5, 'crime');
  76. INSERT INTO genres VALUES (6, 'fantasy');
  77. INSERT INTO genres VALUES (7, 'horror');
  78. INSERT INTO genres VALUES (8, 'romance');
  79. INSERT INTO genres VALUES (9, 'science fiction');
  80. INSERT INTO genres VALUES (10, 'historical');
  81.  
  82. --Register users
  83.  
  84. INSERT INTO users VALUES (1, 'ann black', 'annpass');
  85. INSERT INTO users VALUES (2, 'dan white', 'danpass');
  86. INSERT INTO users VALUES (3, 'ben grey', 'benpass');
  87. INSERT INTO users VALUES (4, 'rachel green', 'rachelpass');
  88.  
  89. --Create index for publications
  90. CREATE INDEX idx_title ON publications (title);
  91.  
  92. --Create index for publication-genre relationship
  93. CREATE INDEX idx_genre ON publication_has_genre (genre_id);
  94.  
  95. --login (unsuccessful)
  96.  
  97. SELECT * FROM users WHERE users.id = 3 AND users.password_hash = 'Bpass';
  98.  
  99. --login (successful)
  100.  
  101. SELECT * FROM users WHERE users.id = 3 AND users.password_hash = 'benpass';
  102.  
  103. --edit username
  104.  
  105. UPDATE users SET users.username = 'Benjamin Grey' WHERE users.id = 3 AND users.password_hash = 'benpass';
  106.  
  107. --edit password
  108.  
  109. UPDATE users SET users.password_hash = 'NewSTrongPassword113' WHERE users.id = 3 AND users.password_hash = 'benpass';
  110.  
  111. --adding publications
  112.  
  113. SET TRANSACTION NAME 'publish_11';
  114.     INSERT INTO publications (id, title, content, user_id) VALUES (11, 'My Novel', 'This is my firs novel', 1);
  115.     INSERT INTO publication_has_genre VALUES (11, 1);
  116.     INSERT INTO publication_has_genre VALUES (11, 8);
  117.     INSERT INTO publication_has_genre VALUES (11, 10);
  118. COMMIT;
  119.  
  120. SET TRANSACTION NAME 'publish_21';
  121.     INSERT INTO publications (id, title, content, user_id) VALUES (21, 'New detective by Mr White', 'Best detecrive you will ever read', 2);
  122.     INSERT INTO publication_has_genre VALUES (21, 5);
  123.     INSERT INTO publication_has_genre VALUES (21, 2);
  124. COMMIT;
  125.  
  126. SET TRANSACTION NAME 'publish_22';
  127.     INSERT INTO publications (id, title, content, user_id) VALUES (22, 'New detective by Mr White: The Chilling Sequel', 'Detective Spectre faces The Horror', 2);
  128.     INSERT INTO publication_has_genre VALUES (22, 5);
  129.     INSERT INTO publication_has_genre VALUES (22, 7);
  130. COMMIT;
  131.  
  132. SET TRANSACTION NAME 'publish_23';
  133.     INSERT INTO publications (id, title, content, user_id) VALUES (23, 'New detective by Mr White: Grand Finale', 'Detective Spectre gets the girl', 2);
  134.     INSERT INTO publication_has_genre VALUES (23, 5);
  135.     INSERT INTO publication_has_genre VALUES (23, 8);
  136. COMMIT;
  137.  
  138. --rate publication
  139.  
  140. SET TRANSACTION NAME 'rate_3_11';
  141.     INSERT INTO user_rate_publication VALUES (3, 11, 2);
  142.     UPDATE publications p SET p.n_rating = p.n_rating+1 WHERE p.id=11;
  143.     UPDATE publications p SET p.sum_rating = p.sum_rating+(SELECT urp.rating
  144.     FROM user_rate_publication urp WHERE urp.user_id=3 AND urp.publication_id=11) WHERE p.id=11;
  145. COMMIT;
  146.  
  147. SET TRANSACTION NAME 'rate_3_21';
  148.     INSERT INTO user_rate_publication VALUES (3, 21, 3);
  149.     UPDATE publications p SET p.n_rating = p.n_rating+1 WHERE p.id=21;
  150.     UPDATE publications p SET p.sum_rating = p.sum_rating+(SELECT urp.rating
  151.     FROM user_rate_publication urp WHERE urp.user_id=3 AND urp.publication_id=21) WHERE p.id=21;
  152. COMMIT;
  153.  
  154. SET TRANSACTION NAME 'rate_1_21';
  155.     INSERT INTO user_rate_publication VALUES (1, 21, 4);
  156.     UPDATE publications p SET p.n_rating = p.n_rating+1 WHERE p.id=21;
  157.     UPDATE publications p SET p.sum_rating = p.sum_rating+(SELECT urp.rating
  158.     FROM user_rate_publication urp WHERE urp.user_id=1 AND urp.publication_id=21) WHERE p.id=21;
  159. COMMIT;
  160.  
  161. SET TRANSACTION NAME 'rate_4_21';
  162.     INSERT INTO user_rate_publication VALUES (4, 21, 5);
  163.     UPDATE publications p SET p.n_rating = p.n_rating+1 WHERE p.id=21;
  164.     UPDATE publications p SET p.sum_rating = p.sum_rating+(SELECT urp.rating
  165.     FROM user_rate_publication urp WHERE urp.user_id=4 AND urp.publication_id=21) WHERE p.id=21;
  166. COMMIT;
  167.  
  168. SET TRANSACTION NAME 'rate_4_22';
  169.     INSERT INTO user_rate_publication VALUES (4, 22, 5);
  170.     UPDATE publications p SET p.n_rating = p.n_rating+1 WHERE p.id=22;
  171.     UPDATE publications p SET p.sum_rating = p.sum_rating+(SELECT urp.rating
  172.     FROM user_rate_publication urp WHERE urp.user_id=4 AND urp.publication_id=22) WHERE p.id=22;
  173. COMMIT;
  174.  
  175. --unrate publication
  176. SET TRANSACTION NAME 'rate_4_21';
  177.     UPDATE publications p SET p.n_rating = p.n_rating-1 WHERE p.id=21;
  178.     UPDATE publications p SET p.sum_rating = p.sum_rating-(SELECT urp.rating
  179.     FROM user_rate_publication urp WHERE urp.user_id=4 AND urp.publication_id=21) WHERE p.id=21;
  180.     DELETE FROM user_rate_publication urp WHERE urp.user_id=4 AND urp.publication_id=21;
  181. COMMIT;
  182.  
  183. --create comments
  184. INSERT INTO comments (id, content, user_id, publication_id) VALUES (1, 'Marvellous!', 4, 21);
  185.  
  186. --find all published crime stories
  187.  
  188. SELECT p.id, p.title, p.created_at, p.user_id, (CASE WHEN p.n_rating = 0 THEN 0 ELSE TRUNC(CAST(p.sum_rating AS FLOAT) / p.n_rating, 2) END) AS rating
  189. FROM publications p INNER JOIN  publication_has_genre pc ON p.id = pc.publication_id
  190. WHERE pc.genre_id = (SELECT genres.id FROM genres WHERE genres.name='crime');
  191.  
  192. --sort publications by rating
  193.  
  194. SELECT p.id, p.title, p.created_at, p.user_id, (CASE WHEN p.n_rating = 0 THEN 0 ELSE TRUNC(CAST(p.sum_rating AS FLOAT) / p.n_rating, 2) END) AS rating
  195. FROM publications p ORDER BY rating DESC;
  196.  
  197. --delete user
  198. SET TRANSACTION NAME 'delete_1';
  199.     UPDATE publications p SET p.n_rating = p.n_rating-1 WHERE p.id IN (SELECT urp.publication_id
  200.     FROM user_rate_publication urp WHERE urp.user_id=1);
  201.     UPDATE publications p SET p.sum_rating =  (SELECT (p.sum_rating-urp.rating)
  202.     FROM publications p INNER JOIN user_rate_publication urp ON p.id=urp.publication_id WHERE urp.user_id = 1) WHERE p.id IN (SELECT urp.publication_id
  203.     FROM user_rate_publication urp WHERE urp.user_id=1);
  204.     DELETE FROM users WHERE users.id=1;
  205. COMMIT;
  206.  
  207. --find a work by title
  208. SELECT p.id, p.title, p.created_at, p.user_id FROM publications p WHERE p.title = 'New detective by Mr White: Grand Finale';
Advertisement
RAW Paste Data Copied
Advertisement