-
Notifications
You must be signed in to change notification settings - Fork 2
/
tabele.txt
131 lines (98 loc) · 4.23 KB
/
tabele.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
GRANT ALL ON DATABASE sem2023_masao TO pavlan WITH GRANT OPTION;
GRANT ALL ON SCHEMA public TO pavlan WITH GRANT OPTION;
GRANT ALL ON DATABASE sem2023_masao TO ianl WITH GRANT OPTION;
GRANT ALL ON SCHEMA public TO ianl WITH GRANT OPTION;
GRANT ALL ON DATABASE sem2023_masao TO gasperdr WITH GRANT OPTION;
GRANT ALL ON DATABASE sem2023_masao TO majg WITH GRANT OPTION;
GRANT ALL ON SCHEMA public TO majg WITH GRANT OPTION;
GRANT ALL ON SCHEMA public TO gasperdr WITH GRANT OPTION;
GRANT ALL ON ALL TABLES IN SCHEMA public TO gasperdr WITH GRANT OPTION;
GRANT ALL ON ALL TABLES IN SCHEMA public TO ianl WITH GRANT OPTION;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO gasperdr WITH GRANT OPTION;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO ianl WITH GRANT OPTION;
GRANT ALL ON ALL TABLES IN SCHEMA public TO pavlan WITH GRANT OPTION;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO pavlan WITH GRANT OPTION;
GRANT ALL ON ALL TABLES IN SCHEMA public TO majg WITH GRANT OPTION;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO majg WITH GRANT OPTION;
CREATE TABLE house(
"id" SERIAL PRIMARY KEY,
"name" TEXT NOT NULL
);
CREATE TABLE student(
"id" SERIAL PRIMARY KEY,
"name" TEXT NOT NULL,
"username" TEXT NOT NULL UNIQUE,
"password" TEXT NOT NULL,
"patronus" TEXT,
"house_id" INT REFERENCES house("id")
);
#CREATE TABLE professor(
# "id" SERIAL PRIMARY KEY,
# "name" TEXT NOT NULL,
# "house" TEXT
#);
CREATE TABLE subject(
"id" SERIAL PRIMARY KEY,
"name" TEXT NOT NULL,
"professor_id" INT REFERENCES professor("id") NOT NULL
);
CREATE TABLE student_subject(
"id" SERIAL PRIMARY KEY,
"student_id" INT REFERENCES student("id"),
"subject_id" INT REFERENCES subject("id")
);
CREATE TABLE forum(
"id" SERIAL PRIMARY KEY,
"name" TEXT NOT NULL
);
CREATE TABLE post(
"id" SERIAL PRIMARY KEY,
"text" TEXT,
"likes" INT,
"student_id" INT REFERENCES student("id"),
"forum_id" INT REFERENCES forum("id")
);
CREATE TABLE student_post(
"id" SERIAL PRIMARY KEY,
"student_id" INT REFERENCES student("id"),
"post_id" INT REFERENCES post("id")
);
CREATE TABLE comment(
"id" SERIAL PRIMARY KEY,
"text" TEXT,
"student_id" INT REFERENCES student("id"),
"post_id" INT REFERENCES post("id")
);
CREATE TABLE student_forum(
"id" SERIAL PRIMARY KEY,
"student_id" INT REFERENCES student("id"),
"forum_id" INT REFERENCES forum("id")
);
INSERT INTO house ("id", "name")
VALUES (1, 'Gryffindor');
INSERT INTO house ("id","name")
VALUES (2,'Hufflepuff');
INSERT INTO house ("id","name")
VALUES (3,'Ravenclaw');
INSERT INTO house ("id","name")
VALUES (4,'Slytherin');
update student
set "House" = (select id from house where student."House"= house.name);
alter table student alter column "House" TYPE integer using ("House"::integer);
alter table student rename column "House" to "House_id";
alter table student add foreign key ("House_id") REFERENCES house(id);
insert into forum(name) values('The Daily Prophet');
insert into forum(name) values('The Slytherin Dungeon');
insert into forum(name) values('The Ravenclaw Tower');
insert into forum(name) values('The Gryffindor Common Room');
insert into forum(name) values('The Hufflepuff Lounge');
insert into forum(name) values('The Quidditch Pitch');
insert into forum(name) values('The Hogwarts School of Witchcraft and Wizardry');
insert into forum(name) values('The Room of Requirement');
insert into forum(name) values('The Hogwarts meme page');
INSERT INTO subject(name, professor_id)
VALUES ('Astronomy', 20), ('Charms', 5), ('Defence Against the Dark Arts', 8), ('Herbology',16), ('History of magic',10), ('Potions',7), ('Transfiguration',4), ('Arithmancy',21), ('Care of magicial creatures',2), ('Tarot',13), ('The Dark Lord Ideology', 14), ('Study of Ancient Runes', 11), ('Alchemy',9), ('Apparition', 3), ('Occlumency and Legilimency', 1), ('Divination', 6), ('Muggle Studies', 12), ('Use of Unforgivable Curses',15), ('The Practice of Dark Rituals',17), ('Flying Instructor',18), ('Library Science',19);
update professor
set "House" = (select id from house where professor."House"= house.name);
alter table professor alter column "House" TYPE integer using ("House"::integer);
alter table professor rename column "House" to "House_id";