Hi, l need help with a database management task, if you can help with querying l

Question

Hi, l need help with a database management task, if you can help with querying languages for me to implement ths

Project Description

1.      The college has some different majors, for example, computer science, software engineering, digital media, network engineering, etc. Each major has several classes and each class has about 30 students.

The information about a student involves student ID, name, sex, age, hometown, province, total credits, etc.

The information about the course involves course ID, course name, major, credits, etc. One course often has several sections, and one section is taught by one teacher in a semester. The information about the section involves course ID, section ID, semester, year, teaching location, etc.

The information about the teacher involves teacher ID, name, sex, age, title, contact phone, office, email, etc.

The information about a student’s marks involves student ID, course ID, semester, course marks, etc.

Note that: One major has more than one class. One course in a semester needs more than one teacher. And one teacher can take more than one course in a semester

Functions and querying to be done:

To list one student’s marks of all courses grouping by semester;

To list all students’ marks about some course in the ascending or decreasing order. And each querying only lists the marks of students in one class;

To compute the average marks of each course. And the average marks can be grouped by class;

To count the number of courses that one student has learned, and compute his total credits;

To query marks of one student about some course;

To query courses that are taught by some teacher in one semester;

To query courses that are taken by some class in one semester;

Other possible functions and querying that you can

Explanation / Answer

1.To list one student’s marks of all courses grouping by semester;

SELECT Student.StudentID Student.CourseID, Student.courseMarks,
FROM Student INNER JOIN
(SELECT CourseID, courseMarks FROM Student
GROUP BY Semister) S1
ON S1.CourseID = Student.CourseID AND
S1.courseMarks = Student.courseMarks
AND S1.StudentID = Student.StudentID
ORDER BY Student.CourseID

2.To list all students’ marks about some course in the ascending or
decreasing order.
And each querying only lists the marks of students in one class;

SELECT Student.StudentID Student.CourseID, Student.courseMarks,
FROM Student INNER JOIN
(SELECT CourseID, courseMarks FROM Student
GROUP BY Semister) S1
ON S1.CourseID = Student.CourseID AND
S1.courseMarks = Student.courseMarks
ORDER BY Student.CourseID

3.To compute the average marks of each course. And the average marks
can be grouped by class;

SELECT StudentID,courseMarks, avg( marks ) as avg_mark FROM `student`
GROUP BY course_Name


4.To count the number of courses that one student has learned, and
compute his total credits;

SELECT COUNT(courseID) FROM Student GROUP BY student_name;

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

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