70 lines
2.7 KiB
SQL
70 lines
2.7 KiB
SQL
DROP TABLE IF EXISTS user;
|
|
DROP TABLE IF EXISTS question;
|
|
DROP TABLE IF EXISTS course;
|
|
DROP TABLE IF EXISTS lecture;
|
|
DROP TABLE IF EXISTS upvote;
|
|
|
|
CREATE TABLE user (
|
|
user_id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username TEXT UNIQUE NOT NULL,
|
|
password TEXT NOT NULL,
|
|
lecturer INTEGER NOT NULL
|
|
);
|
|
|
|
CREATE TABLE question (
|
|
question_id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
lecture_id INTEGER NOT NULL,
|
|
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
question_text TEXT NOT NULL,
|
|
upvotes INTEGER NOT NULL,
|
|
has_answer BOOLEAN DEFAULT 0,
|
|
answer TEXT,
|
|
user_id INTEGER,
|
|
FOREIGN KEY (lecture_id) REFERENCES lecture (lecture_id),
|
|
FOREIGN KEY (user_id) REFERENCES user (user_id)
|
|
);
|
|
|
|
CREATE TABLE course (
|
|
course_id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
course_name TEXT UNIQUE NOT NULL
|
|
/*user_id INTEGER NOT NULL,
|
|
FOREIGN KEY (user_id) REFERENCES user (user_id)*/
|
|
);
|
|
|
|
CREATE TABLE lecture (
|
|
lecture_id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
lecture_date DATE,
|
|
course_id INTEGER,
|
|
topic TEXT NOT NULL,
|
|
FOREIGN KEY (course_id) REFERENCES course(course_id)
|
|
);
|
|
|
|
CREATE TABLE upvote (
|
|
upvote_id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
question_id INTEGER,
|
|
user_login TEXT
|
|
);
|
|
|
|
INSERT INTO course (course_name) VALUES ('Tworzenie systemów informatycznych');
|
|
INSERT INTO course (course_name) VALUES ('Optymalizacja kombinatoryczna');
|
|
INSERT INTO course (course_name) VALUES ('Podstawy JavaScriptu dla programistów C#');
|
|
|
|
INSERT INTO question (lecture_id, question_text, upvotes) VALUES (2, 'Nudne pytanie',0);
|
|
INSERT INTO question (lecture_id, question_text, upvotes) VALUES (2, 'Czy pytania będą proste?',78);
|
|
INSERT INTO question (lecture_id, question_text, upvotes) VALUES (2, 'Czy to będzie na egzaminie?',111);
|
|
INSERT INTO question (lecture_id, question_text, upvotes) VALUES (2, 'Nic nie rozumiem.',6);
|
|
INSERT INTO question (lecture_id, question_text, upvotes) VALUES (2, 'Chcę zdaaaaać',9);
|
|
|
|
INSERT INTO lecture (lecture_date,course_id,topic) VALUES('2019-01-30',1,'Jak przetrwać chwilę prawdy?');
|
|
INSERT INTO lecture (lecture_date,course_id,topic) VALUES('2019-01-16',1,'Peopleware');
|
|
INSERT INTO lecture (lecture_date,course_id,topic) VALUES('2019-01-09',1,'Jak zostać komputerowym geniuszem?');
|
|
INSERT INTO lecture (lecture_date,course_id,topic) VALUES('2018-12-01',1,'Jak zaplanować projekt informatyczny?');
|
|
|
|
INSERT INTO lecture (lecture_date,course_id,topic) VALUES('2019-01-18',2,'Zaawansowane algorytmy znajdujące skojarzenia w grafach');
|
|
INSERT INTO lecture (lecture_date,course_id,topic) VALUES('2019-01-07',3,'Wprowadzenie do Ajaxa');
|
|
INSERT INTO lecture (lecture_date,course_id,topic) VALUES('2019-01-14',3,'Wysyłanie i ściąganie danych przy pomocy Ajaxa');
|
|
|
|
|
|
|
|
select * from user;
|
|
select * from question; |