This is supposed to develop a database to manage a grade book. Your product shou

Question

This is supposed to develop a database to manage a grade book. Your product should contain information about courses, students, grades for homework, quizzes, midterm, ?nal, and various grades. Each course has id, title, credit hour. You should store an id, name, email for each student. Each homework has number, submission date and time, due date, marks. Each quiz has number, marks, date. You have to store also number and marks for the exams (midterm and final). Students enroll to courses and you have to store their final grade. Database should also store the various activity grades for each student. SQL Queries: 1. Find all students who are taking “math” course along with their grades in mid and final exam. 2. Find the students who submitted a homework after the deadline in “biology” course. 3. Find the quizzes of “math” course along with their dates and marks. 4. Show the total grades of each course that a student has taken. 5. Show the students who obtained grades less than 60 in “physics” course.

Explanation / Answer

Course

C_Id, c_title, credit_hour

SQL: Create table Course (c_id varchar(10) primary key, c_title varchar(20), credit_hour date not null);

Student

S_Id, s_name, s_email

SQL: create table student (s_id varchar(10) primary key, s_name not null varchar(20), s_email varchar not null (10));

Homework_Grade

H_Id, h_submission_datetime, h_due date, h_marks

SQL: Create table homework_grade (H_id varchar (10) primary key, h_submission_datetime not null date, h_due_date not null date , h_marks not null int, s_id varchar(10) foreign key references student (s_id) , c_id varchar(10) forign key references course (c_id));

quizz,

q_id, q_marks,q_date

SQL: create table quiz (q_id varchar (10) primary key, q_marks not null int, q_date not null date, s_id varchar (10) foreign key references student (s_id), c_id varchar (10) foreign key references course (c_id));

Grade

Id, type, marks

SQL: create tabl grade (g_Id varchar (10) primary key, g_type not null varchar(10), g_marks not null int, s_id varchar (10) foreign key references student (s_id), c_id varchar (10) foreign key references course (c_id));

Answer 1:

Select s_name from student, course, grade where grade.sid= student.s_id and grade.c_id=course.c_id and c_title= “math” and g_type any (“mid”, “final“);

Answer 2:

Select s_name from student, homework_grade, course where homework_grade.sid= student.sid and homework_grade.cid=course.cid and c_title= “Biology” and h_due_date < SYSDATETIME();

Answer 3:

Select q_id, q_marks,q_date from quiz,course where quiz.c_id=course.c_id and c_tile=”math”;

Answer 4:

Select sum (g_marks), c_id,c_name,s_name course,grade,student where course.s_id=student.s_id and course.c_id= grade.c_id group by c_id,s_name;

Answer 5:

Select s_name from student, grade ,course where grade.s_id=student.s_id and course.g_id=grade.g_id and g_marks < 60;

I have answered your question. Please do not forget to give a positive feedback to the answer. Thank you.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.