Useful queries

Drop, Create, Load dump, Enter db, View Tables, View schema, Quit

dropdb tutordb
createdb tutordb
psql tutordb < demo.sql
psql tutordb
\dt
\d quizzes
\q

Create dump, Execute sql file

pg_dump tutordb > ./demo.sql
psql tutordb -f ./migration.sql

Useful queries

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

-- Deletes
delete from topic_conjunctions where assessment_id is null;

-- Update keys
update assessments set key = id;
update questions set key = id;
update quizzes set key = id;
update users set key = id;


-- Alter Tables
alter table users drop column number_of_correct_answers;
alter table courses add column type character varying(255);
alter table quiz_answers add column used_in_statistics boolean;
alter table students rename column type to role;

-- updates
update quizzes set available_date = creation_date where available_date is null;
update quizzes set type = 'GENERATED' where type = 'STUDENT';

-- Reset statistics
update questions set number_of_answers = 0;
update questions set number_of_correct = 0;
update users set number_of_student_quizzes = 0;
update users set number_of_teacher_quizzes = 0;
update users set number_of_in_class_quizzes = 0;
update users set number_of_correct_teacher_answers = 0;
update users set number_of_correct_in_class_answers = 0;
update users set number_of_correct_student_answers = 0;
update users set number_of_teacher_answers = 0;
update users set number_of_in_class_answers = 0;
update users set number_of_student_answers = 0;
update quiz_answers set used_in_statistics = false;

-- Change options
update options set correct = true where id = 8125;
update options set correct = false where id = 8127;

-- Inserts
insert into users_course_executions (users_id, course_executions_id) values (615, 9);
insert into users (name, role, username) values ('Demo Admin', 'DEMO_ADMIN', 'Demo-Admin');
insert into answers (user_id, question_id, quiz_id, answer_date, option) values (614,2,4,'2019-03-03 00:00:00', 2):

-- Insert into without conflicts
insert into question_answers
    (id, time_taken, option_id, quiz_answer_id, quiz_question_id, sequence)
select * from question_answers_2
where quiz_answer_id in (select id from quiz_answers)
and quiz_question_id in (select id from quiz_questions)
ON CONFLICT DO NOTHING;

-- Create Table
CREATE TABLE question_answers_2 (
    id integer NOT NULL,
    time_taken integer,
    option_id integer,
    quiz_answer_id integer,
    quiz_question_id integer,
    sequence integer
);

-- Drop table
drop table question_answers_2;

-- Copy to csv
\copy (select * from questions) to '/home/pedro/tutor-private/scripts/teachers.csv' with csv

-- Copy from csv to table
\copy question_answers_2 FROM '/home/ubuntu/tutor/answer.csv' DELIMITER ',' CSV


-- Get Teachers and courses
select a.name as teacher, b.name as course
from 
	(select * from users where role = 'TEACHER') a
left join
	(
		select *
		from users_course_executions uc, course_executions ce, courses c
		where ce.course_id = c.id and uc.course_executions_id = ce.id
	) b 
on a.id = b.users_id
order by course, teacher

-- View if all questions have 4 options
select * from (select count(*), question_id from options group by question_id) as a where count <> 4;

-- View last 5 users
select id, name, role, last_access from users where last_access is not null order by last_access desc limit 5;

-- Count users per execution
select acronym, count from course_executions, (select course_executions_id, count(*) from users_course_executions group by course_executions_id) as a where id = a.course_executions_id order by count desc;

-- Count questions per course
select name, count from courses, (select course_id, count(*) from questions group by course_id) as a where id = a.course_id order by count desc;

-- Count quizzes per execution
select acronym, count from course_executions, (select course_execution_id, count(*) from quizzes group by course_execution_id) as a where id = a.course_execution_id order by count desc;

-- Regexp_replace
update questions set content = regexp_replace(title, '(AAAtesting)<script>alert\(1\)</script>()', '\1\2', 'g');

-- Anonymize
update users set name = 'anonymous';
update courses set name = 'anonymous';
update course_executions set acronym = 'anonymous';
delete from question_answer_items where username like 'Demo-Stu%';
update question_answer_items set username = users.id from users where question_answer_items.username = users.username;
update users set username = 'anonymous';
update questions set content = 'content';
update questions set title = 'title';
update options set content = 'option';