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.