SHARE
TWEET

Untitled

a guest Apr 24th, 2019 62 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DROP DATABASE IF EXISTS youtube_db;
  2. CREATE DATABASE youtube_db;
  3. USE youtube_db;
  4.  
  5. CREATE TABLE User (
  6.     id INTEGER AUTO_INCREMENT NOT NULL,
  7.     name VARCHAR(255) NOT NULL,
  8.     username varchar(255) NOT NULL UNIQUE KEY,
  9.     views_num int NOT NULL DEFAULT 0,
  10.  
  11.     PRIMARY KEY(id)
  12. );
  13.  
  14. CREATE TABLE Channel (
  15.     id INTEGER AUTO_INCREMENT NOT NULL,
  16.     name VARCHAR(100) NOT NULL,
  17.     user_id int,
  18.     PRIMARY KEY(id),
  19.     FOREIGN KEY(user_id) REFERENCES User(id)
  20.     ON DELETE CASCADE
  21.     ON UPDATE CASCADE
  22. );
  23.  
  24. CREATE TABLE Video (
  25.     id INTEGER AUTO_INCREMENT NOT NULL,
  26.     name VARCHAR(50) NOT NULL,
  27.     channel_id int NOT NULL,
  28.     views INTEGER DEFAULT 0,
  29.    
  30.     PRIMARY KEY(id),
  31.     FOREIGN KEY(channel_id) REFERENCES Channel(id)
  32.     ON DELETE CASCADE
  33.     ON UPDATE CASCADE
  34. );
  35.  
  36. CREATE TRIGGER VideosInsert
  37. AFTER INSERT
  38. ON Video
  39. FOR EACH ROW
  40.     UPDATE user
  41.     SET views_num = ( select sum(views) from Video left join Channel on Video.channel_id = Channel.id)
  42.     WHERE New.id = id;
  43.    
  44.  
  45. CREATE TRIGGER VideosUpdate
  46. AFTER UPDATE
  47. ON Video
  48. FOR EACH ROW
  49.     UPDATE user
  50.     SET views_num = ( select sum(views) from Video left join Channel on Video.channel_id = Channel.id)
  51.     WHERE New.id = id;
  52.    
  53.  
  54. CREATE TRIGGER VideosDelete
  55. AFTER Delete
  56. ON Video
  57. FOR EACH ROW
  58.     UPDATE user
  59.     SET views_num = 0
  60.     WHERE OLD.id = id;
  61.    
  62. INSERT INTO User(name, username) VALUES ('Pesho Programista', 'pe60');
  63. INSERT INTO User(name, username) VALUES ('Gosho', 'go60');
  64. INSERT INTO User(name, username) VALUES ('Tosho', 'to60');
  65.  
  66. INSERT INTO Channel(name, user_id) VALUES ('Channel1', 1);
  67. INSERT INTO Channel(name, user_id) VALUES ('Channel2', 2);
  68. INSERT INTO Channel(name, user_id) VALUES ('Channel3', 3);
  69.  
  70. INSERT INTO Video(name, channel_id) VALUES ('Video1', 1);
  71. INSERT INTO Video(name, channel_id, views) VALUES ('Video2', 2, 2);
  72. INSERT INTO Video(name, channel_id) VALUES ('Video0', 3);
  73.  
  74. Select v.name, v.views, u.name, ch.name from Video v
  75. LEFT JOIN Channel ch ON ch.id = v.channel_id
  76. LEFT JOIN  User u ON u.id = ch.user_id;
  77.  
  78. Select ch.name from Channel ch
  79. Inner join Video v on v.channel_id = ch.id
  80. group by ch.name;
  81.  
  82. Select u.name, u.username, u.views_num from User u
  83. Inner join Channel ch on ch.user_id = u.id
  84. Inner join Video v on v.channel_id
  85. group by u.username;
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