Useful queries

psql tutordb

createdb tutordb

dropdb tutordb

\q quit

tables

\d view

view answer table info

\d answers

Useful queries

add entries

insert into answers (user_id, question_id, quiz_id, answer_date, option) values (614,2,4,'2019-03-03 00:00:00', 2)

view unique questions

select count(*) from questions where new_id is null;

view if all questions have 4 options

select * from (select count(*), question_id from options group by question_id) as a where count <> 4;

copy to file list of quizzes

\copy (select title, count from quizzes, (select quiz_id, count(question_id) from quiz_has_question group by quiz_id) as q where id = quiz_id) To 'test.csv' With CSV

view most repeated questions

select count, content from questions, (select question_id, count(*) as count from quiz_has_question group by question_id having count(*)>5) as t where id = question_id;

view most unanswered questions

select * from (select question_id, count(*) as c from answers where option is null group by question_id) as a where c > 40;

add column

alter table students add column username varchar(255);

rename column

ALTER TABLE students RENAME COLUMN type TO role;

set value

update students set role = 'student' where true;

delete record

DELETE FROM table WHERE condition;

Order students by percentage of correct questions

select u.id, u.year, SUM(CASE WHEN o.correct = true THEN 1 ELSE 0 END) * 100 / count(*) as percentage, count(*) as total, SUM(CASE WHEN o.correct = true THEN 1 ELSE 0 END) as corrects from users as u, answers as a, options as o where o.option = a.option and a.question_id = o.question_id and u.id = a.user_id group by u.id order by percentage DESC;

Like

select * from questions where content like '%Consider the following view%Builder case%';