CREATE TABLE Student(
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50),
    email VARCHAR(50),
    mob_no VARCHAR(50),
    dept VARCHAR(50)
);
CREATE TABLE Teacher(
    teacher_id INT PRIMARY KEY,
    teacher_name VARCHAR(50),
    specialization VARCHAR(50),
    email VARCHAR(50)
);
CREATE TABLE question(
    teacher_id INT,
    question_id INT PRIMARY KEY,
    question_text VARCHAR(50),
    marks INT,
    FOREIGN KEY(teacher_id) REFERENCES Teacher(teacher_id)
);
CREATE TABLE responses(
    student_id INT,
    question_id INT,
    responses_id INT PRIMARY KEY,
    responses_text TEXT,
    is_correct BOOLEAN,
    FOREIGN KEY(student_id) REFERENCES Student(student_id),
    FOREIGN KEY(question_id) REFERENCES question(question_id)
);
CREATE TABLE results(
    student_id INT,
    question_id INT,
    marks_obtained INT,
    FOREIGN KEY(student_id) REFERENCES Student(student_id),
    FOREIGN KEY(question_id) REFERENCES question(question_id)
);
INSERT INTO Student VALUES
(101,'Rahul Sharma','rahulsharma16@gmail.com',67889992,'CSE'),
(102,'Jacob ','jacob216@gmail.com',923456798,'ECE'),
(103,'Mark Jacobs','markjacobs16@gmail.com',943445924,'Civil'),
(104,'Sneha Sen','snehasen456@gmail.com',783452198,'CSE');

INSERT INTO Teacher VALUES
(201,'Anil Verma','Database management','anilverma678@gmail.com'),
(202,'Tony Singh','OOPS programming','tonysingh78@gmail.com'),
(203,'Rakesh Bhatt','Computer Networks','rakeshbhatt34@gmail.com');

INSERT INTO question VALUES
(201,301,"What is a primary key?",5),
(202,302,"Object creation syntax in C++?",3),
(203,303,"What is computer network?",4),
(202,304,"What is a role of a variable?",8);

INSERT INTO responses VALUES
(101, 301, 401, 'A primary key uniquely identifies each record in a table.',TRUE),
(102, 301, 411, 'A primary key is a identifier for a record.',FALSE),
(102, 302, 412, 'MyClass obj',TRUE),
(103, 302, 413, 'obj',FALSE),
(103, 304, 421, 'A variable stores a value in a program.',TRUE),    
(104,303,431, 'A computer network connects computers to share data.',TRUE);

INSERT INTO results VALUES
(101,302,65),
(102,303,83),
(102,301,24),
(103,301,98);

SELECT r.responses_text
FROM responses as r
WHERE question_id=301
AND is_correct=TRUE;
    
SELECT s.student_id,s.student_name   #Name and Id 
FROM Student as s                    #of student who has 
JOIN responses as r                  #given correct answer 
ON s.student_id=r.student_id         #for question no 301
WHERE question_id=301
AND is_correct=TRUE;

SELECT SUM(marks_obtained) as final_score  #Calculate final score 
FROM results                               #of student id 102
WHERE student_id=102;                      #using results table

SELECT r.is_correct             #Find if jacob has 
FROM responses as r             #answered question 
JOIN Student as s               #no 301 correctly
ON r.student_id=s.student_id
WHERE s.student_name="Jacob "
AND r.question_id=301;

SELECT q.question_id,r.responses_text,r.is_correct   #Display response against all 
FROM question as q                                   #questions 
JOIN responses as r                                  #and their correctness against 
ON r.question_id=q.question_id                       #each qs for student 102
WHERE r.student_id=102;