再試験課題2023

create table genders (

    id int AUTO_INCREMENT PRIMARY KEY,

    title varchar(10)

    );

create table students (

    id int AUTO_INCREMENT PRIMARY KEY,

    name varchar(20) not null,

    gender_id int,

    FOREIGN key (gender_id) REFERENCES genders(id),

    birth_date date

    );

create table questions (

    id int AUTO_INCREMENT PRIMARY KEY,

    title text not null    

    );

create table questions_students (

    id int AUTO_INCREMENT PRIMARY KEY,

    question_id int not null,

    FOREIGN key (question_id) REFERENCES questions(id),

    student_id int not null,

    FOREIGN key (student_id) REFERENCES students(id)

    );

create table text_answers (

    id int AUTO_INCREMENT PRIMARY KEY,

    questions_student_id int not null,

    foreign key (questions_student_id) references questions_students(id),

    answer text

    );

create table int_answers (

    id int AUTO_INCREMENT PRIMARY KEY,

    questions_student_id int not null,

    foreign key (questions_student_id) references questions_students(id),

    answer int

    );

create table yes_no_answers (

    id int AUTO_INCREMENT PRIMARY KEY,

    questions_student_id int not null,

    foreign key (questions_student_id) references questions_students(id),

    answer boolean

    );

create table options (

    id int primary key AUTO_INCREMENT,

    title varchar(30)

    );

create table options_questions_students (

    option_id int not null,

    foreign key (option_id) references options(id),

    questions_student_id int not null,

    foreign key (questions_student_id) references questions_students(id),

    primary key (option_id,questions_student_id)

    );

CREATE VIEW all_lists (a, b, c, d, e, f, g) AS

SELECT `questions`.`title`, `students`.`name`, `genders`.`title`, `text_answers`.`answer`, `int_answers`.`answer`, `yes_no_answers`.`answer`, `options`.`title`

FROM `questions_students` 

LEFT JOIN `students` ON `questions_students`.`student_id` = `students`.`id` 

LEFT JOIN `genders` ON `students`.`gender_id` = `genders`.`id` 

LEFT JOIN `questions` ON `questions_students`.`question_id` = `questions`.`id` 

LEFT JOIN `text_answers` ON `text_answers`.`questions_student_id` = `questions_students`.`id` 

LEFT JOIN `int_answers` ON `int_answers`.`questions_student_id` = `questions_students`.`id` 

LEFT JOIN `yes_no_answers` ON `yes_no_answers`.`questions_student_id` = `questions_students`.`id` 

LEFT JOIN `options_questions_students` ON `options_questions_students`.`questions_student_id` = `questions_students`.`id` 

LEFT JOIN `options` ON `options_questions_students`.`option_id` = `options`.`id`;

CREATE VIEW count_dep (a, b) AS

SELECT count(*), `options`.`title`

FROM `questions_students` 

LEFT JOIN `students` ON `questions_students`.`student_id` = `students`.`id` 

LEFT JOIN `genders` ON `students`.`gender_id` = `genders`.`id` 

LEFT JOIN `questions` ON `questions_students`.`question_id` = `questions`.`id` 

LEFT JOIN `text_answers` ON `text_answers`.`questions_student_id` = `questions_students`.`id` 

LEFT JOIN `int_answers` ON `int_answers`.`questions_student_id` = `questions_students`.`id` 

LEFT JOIN `yes_no_answers` ON `yes_no_answers`.`questions_student_id` = `questions_students`.`id` 

LEFT JOIN `options_questions_students` ON `options_questions_students`.`questions_student_id` = `questions_students`.`id` 

LEFT JOIN `options` ON `options_questions_students`.`option_id` = `options`.`id`

group by `options`.`id`;