MySQL Query Provide the SQL statements for each query. The table schema has been
ID: 3754042 • Letter: M
Question
MySQL Query
Provide the SQL statements for each query. The table schema has been given:
Problem 3:
a. The average score for each course in ascending order by CNO.
b. For each student who took more than two courses, list the name of the student and the number of the courses the student took.
c. The names of students who performed better than average in each course.
CNAME 21 Computer Science 17 Chemist 15 PhysicS 12 Mathematics l 13 Mathematics III RESULT CNO CNO STUDENT SNO SNAME ADDRESS SEX SCORE 31 L. Gatlin Holcomb F 11 N. Park Whitney M 22 S. Conner: Holcomb F 24 D. Davissc E. Quad M 19 1. Blake Holcomb M 4 T. Smith Cambridg F SNO 2 2 2 2 2 2 31 14 2 63 4 Histo 3 Geography 2 Biology 72 1 N. J. Sloan Whitney F 14 D. E. Knutl Whitney M 6 J. Brown Whitney 2 M. Robert Cambridg M 14 33 U. Smith Cambridg F 14 37 2 13 13 13 13 97 72 11 NULL 14 61 52 63Explanation / Answer
a.
Select CNO,AVG(SCORE) from RESULT group by CNO order by CNO;
b.
Select SNAME ,COUNT(COURSE.CNO) from STUDENT inner join RESULT on STUDENT.SNO = RESULT.SNO inner join COURSE on RESULT.CNO = COURSE.CNO group by STUDENT.SNO
having COUNT(COURSE.CNO) > 2;
c.
Select SNAME ,COURSE.CNO ,SCORE from STUDENT inner join RESULT on STUDENT.SNO = RESULT.SNO inner join COURSE on RESULT.CNO = COURSE.CNO group by COURSE.CNO having SCORE > AVG(SCORE);
Do ask if any doubt. Please upvote.
c.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.