Facebook
From Aleksy Ruszala, 4 Months ago, written in SQL.
Embed
Download Paste or View Raw
Hits: 66
  1. CREATE TABLE USER(
  2.     id INT AUTO_INCREMENT PRIMARY KEY,
  3.     username VARCHAR(50) NOT NULL,
  4.     password VARCHAR(50) NOT NULL,
  5.     display_name VARCHAR(40)
  6. );
  7.  
  8. CREATE TABLE category(
  9.     id INT AUTO_INCREMENT PRIMARY KEY,
  10.     name VARCHAR(50) NOT NULL
  11. );
  12.  
  13. CREATE TABLE post(
  14.     id INT AUTO_INCREMENT PRIMARY KEY,
  15.     user_id INT NOT NULL,
  16.     category_id INT NOT NULL,
  17.     title VARCHAR(100) NOT NULL,
  18.     content text NOT NULL,
  19.     created DATE,
  20.     CONSTRAINT post_user_fk_user_id
  21.         FOREIGN KEY (user_id) REFERENCES USER(id),
  22.     CONSTRAINT post_category_fk_category_id
  23.         FOREIGN KEY (category_id) REFERENCES category(id)
  24. );
  25.  
  26. CREATE TABLE menu(
  27.     id INT AUTO_INCREMENT PRIMARY KEY,
  28.     name VARCHAR(50) NOT NULL,
  29.     url VARCHAR(255) NOT NULL
  30. );
  31.  
  32. CREATE INDEX user_id ON post(user_id);
  33. CREATE INDEX category_id ON post(category_id);
  34.  
  35. INSERT INTO USER (username, password, display_name) VALUES ('admin', 'value2', 'God');
  36. INSERT INTO USER (username, password, display_name) VALUES ('marian222', 'value2', 'Mariusz');
  37. INSERT INTO USER (username, password, display_name) VALUES ('stefan93', 'value2', 'Stefan');
  38. INSERT INTO USER (username, password, display_name) VALUES ('kowal22', 'value2', 'Kamil');
  39.  
  40. INSERT INTO category (name) VALUES ('Prono');
  41. INSERT INTO category (name) VALUES ('Gry');
  42. INSERT INTO category (name) VALUES ('News');
  43. INSERT INTO category (name) VALUES ('Story');
  44.  
  45. INSERT INTO post (content, title, user_id, category_id)
  46. VALUES ('So perhaps, you''ve generated some fancy text, and you''re content that you can now copy and paste your fancy text in the comments section of funny cat videos, but perhaps you''re wondering how it''s even possible to change the font of your text? Is it some sort of hack? Are you copying and pasting an actual font?','Hello world',1,1);
  47.  
  48. INSERT INTO post (title, content, user_id, category_id)
  49. VALUES ('Unicode text','So perhaps, you''ve generated some fancy text, and you''re content that you can now copy and paste your fancy text in the comments section of funny cat videos, but perhaps you''re wondering how it''s even possible to change the font of your text? Is it some sort of hack? Are you copying and pasting an actual font?',2,2);
  50.  
  51. CREATE TABLE country(
  52.     id INT AUTO_INCREMENT PRIMARY KEY,
  53.     code VARCHAR(3) NOT NULL ,
  54.     name VARCHAR(50)
  55. );
  56.  
  57. ALTER TABLE USER
  58. ADD country_id INT,
  59. ADD CONSTRAINT user_country_fk_country_id
  60. FOREIGN KEY (country_id) REFERENCES country(id);
  61.  
  62. CREATE INDEX country_id ON USER(country_id);
  63.  
  64.  
  65. SELECT * FROM USER;
  66.  
  67. SELECT *
  68. FROM USER u
  69. LEFT JOIN country c ON u.country_id = c.id;
  70.  
  71. CREATE VIEW users_with_country AS
  72. SELECT u.username, c.name
  73. FROM USER u
  74. LEFT JOIN country c ON u.country_id = c.id;
  75.  
  76. SELECT * FROM users_with_country;
  77.  
  78. UPDATE country
  79. SET code = 'XX';
  80.  
  81. UPDATE country SET code = 'zS' WHERE id = 4;
  82.  
  83.  
  84. SELECT u.username, c.name
  85. FROM USER u
  86. LEFT JOIN country c ON u.country_id = c.id;
  87.  
  88.  
  89. CREATE TABLE usercountry(
  90.     id INT PRIMARY KEY,
  91.     username VARCHAR(50) NOT NULL,
  92.         name VARCHAR(50)
  93. );
  94.  
  95. INSERT INTO usercountry
  96. SELECT u.id, u.username, c.name
  97. FROM USER u
  98. LEFT JOIN country c ON u.country_id = c.id;
  99.  
  100. INSERT INTO users_with_country (username, name) VALUE ('sds','sds')
  101.  
  102.