RelationalDesign: PolnenjeTabeli.sql

truncate table administrator restart identity cascade; 
truncate table attempt restart identity cascade; 
truncate table badge restart identity cascade; 
truncate table badge_is_awarded_to_quiztaker restart identity cascade;
truncate table category restart identity cascade;
truncate table choice restart identity cascade; 
truncate table hint restart identity cascade; 
truncate table user_table restart identity cascade; 
truncate table medal restart identity cascade; 
truncate table medal_is_awarded_to_quiztaker restart identity cascade; 
truncate table moderator restart identity cascade; 
truncate table question restart identity cascade; 
truncate table question_is_part_of_quiz restart identity cascade; 
truncate table quiz restart identity cascade; 
truncate table quiz_is_part_of_tournament restart identity cascade; 
truncate table quiztaker restart identity cascade; 
truncate table quiztaker_participates_in_tournament restart identity cascade; 
truncate table quiztaker_participates_in_tournamentphase restart identity cascade; 
truncate table result_table restart identity cascade;
truncate table selectedchoice restart identity cascade; 
truncate table selectionquestion restart identity cascade; 
truncate table subject restart identity cascade; 
truncate table textquestion restart identity cascade; 
truncate table tournament restart identity cascade; 
truncate table tournamentorganizer restart identity cascade; 
truncate table tournamentorganizer_hosts_tournament restart identity cascade; 
truncate table tournamentphase restart identity cascade; 
truncate table quiz_is_part_of_tournamentphase restart identity cascade;

insert into user_table(fullname, username, password_attr) values
('Kliment Shukanov', 'shukan123', 'User321!'),
('Petar Pucoski', 'perikus123', 'najsecurelozinka'),
('Stefan Risteski', 'fankyoste', 'password_attr123'),
('Petre Petreski', 'mitre123', 'pass123'),
('Kiril Lazarov', 'kirilLazarov', 'pass321'),
('Quiz Tester', 'quizTester', 'pass321123!'),
('Stevo Stevoski', 'stefs', 'asdjkdas'),
('Stole Dimitrieski', 'stoleGK', 'gk123321'),
('Quiz Tester 2', 'quizTester2', 'pass32112332!');

insert into administrator (userID) values
        (1), (2), (3);

insert into moderator (userID) values
        (4), (5), (6);

insert into quiztaker(userid) values
        (7), (8), (9);

insert into tournament(tournamentname) values
('History Quiz Tournament'),
('General Sport Knowledge Test'),
('Geography Tournament');

insert into tournamentphase(tournamentid, tournamentphasename) values
        (1, 'First phase'), (1, 'Semi-finals'), (1, 'Finals'), (2, 'Phase 1'),  (2, 'Phase 2'),  (2, 'Phase 3'),  (2, 'Phase 4'),  (2, 'Grand final'), (3, 'First phase'), (3, 'Second phase');

insert into tournamentorganizer(userid) values
        (4), (5), (6);

insert into subject(subjectname) values
        ('History'), ('Sport'), ('Geography'), ('Physics');

insert into category(subjectid, categoryname) values
        (1, 'World War 1'), (1, 'World War 2'), (1, 'Balkan Wars'), 
        (2, 'Football'), (2, 'Basketball'), (3, 'Europe'), (3, 'Asia');
        
insert into quiz(categoryid, quizname, tournamentphaseid) values
        (2, 'World War 2 Trivia'),
        (4, 'Football Legends'),
        (5, 'Basketball legends'),
        (6, 'European Landmarks'),
        (4, 'Champions league');
        

insert into medal(tournamentid, medalname, description) values
        (2, 'Sports-wiz', 'Your knowledge of sports is impeccable'),
        (1, 'History nerd', 'You are a true historian'),
        (3, 'Geography geek', 'Master of geography');

insert into question(questiontext) values
        ('Who got the Rookie of the Year award in 2021'),
        ('Give your opinion on who will win the 2022 season and why?'),
        ('Guess this flag?'),
        ('What is the capital of Germany?'),
        ('Who won the 2010 World Cup?'),
        ('What is the most populated nation in Asia?');

insert into hint(hinttext, questionid) values
        ('This player plays in Charlotte Hornets', 1),
        ('It is a country in the north of Europe', 3),
        ('Their nickname is the Red Furia', 5);

insert into textquestion (questionid) values
        (2), (4), (6);

insert into selectionquestion (questionid) values
        (1), (3), (5);

insert into choice(questionid, iscorrect, choicetext) values
        (1, true, 'LaMelo Ball'),
        (1, false, 'Luka Doncic'),
        (1, false, 'Nikola Jokic');

insert into attempt(userid, attemptdate, quizid) values
        (7, '12/10/2021', 3),
        (8, '11/10/2021', 3),
        (9, '10/10/2021', 3);

insert into selectedchoice(choiceid, attemptid) values
        (2, 1), (3, 2), (1, 3);

insert into result_table(attemptid, points) values
        (1, 0), (2, 0), (3, 100);

insert into badge(resultid, badgename , description) values
        (1, 'High performance', 'You scored a score above 90 points'),
        (2, 'Participation badge', 'At least you tried'),
        (3, 'Basketball maniac', 'Score above 90 points on a quiz about basketball');

insert into quiz_is_part_of_tournament(quizid, tournamentid) values
        (5, 2), (1, 1), (3,2);

insert into quiz_is_part_of_tournamentphase (quizid, tournamentphaseid) values
        (5, 8), (1, 1), (3, 7);

insert into tournamentorganizer_hosts_tournament (userid , tournamentid) values
        (1,2), (2,1), (3,3);

insert into question_is_part_of_quiz(questionid, quizid) values
        (1,3), (2, 3), (9, 4);

insert into quiztaker_participates_in_tournamentphase (userid, tournamentphaseid) values
        (7, 8), (8, 2), (9, 3);
        
insert into quiztaker_participates_in_tournament (userid, tournamentid) values
        (7, 2), (8, 1), (9,1);

insert into medal_is_awarded_to_quiztaker (medalid, userid) values
        (2, 7), (2, 8), (1, 9);

insert into badge_is_awarded_to_quiztaker (badgeid, userid) values
        (2,7), (2, 8), (3, 9);