Using one query's result into another-CodePudding

I have this query which shows the below result, I want to use this MarksObtained and take out min, max and avg of each course and show it with the second query (have provided below).

select 
    CourseName, StdID, MarksObtained 
from 
    stdmarks
inner join 
    course on course.courseid = stdmarks.examid

 -------------------------- ------- --------------- 
|        CourseName        | StdID | MarksObtained |
 -------------------------- ------- --------------- 
| Digital Logic            |     1 |            20 |
| Visual Prog              |     1 |            20 |
| Computer Arch and Design |     1 |            20 |
| Digital Logic            |     2 |            20 |
| Visual Prog              |     2 |            20 |
 -------------------------- ------- ---------------

This is the second query

select 
    distinct CourseName, TeacherName, SemName 
from 
    teacher
inner join 
    stdcourseteacher on teacher.teacherid = stdcourseteacher.teacherid
inner join 
    course on course.courseid = stdcourseteacher.courseid
inner join 
    semester on stdcourseteacher.semid = semester.semid

 ------------------------- ------------- ---------- 
|       CourseName        | TeacherName | SemName  |
 ------------------------- ------------- ---------- 
| Business Communications | Dr. Iman    | Fall2021 |
| Calculus - 1            | Dr. Khalid  | Fall2021 |
| Calculus - 2            | Dr. Khalid  | Fall2020 |
 ------------------------- ------------- ----------

So it will basically show min, max and avg of each course achieved by the students.

What I want:

 ------------------------- ------------- ---------- ----- ----- ----- 
|       CourseName        | TeacherName | SemName  | Min | Max | Avg |
 ------------------------- ------------- ---------- ----- ----- ----- 
| Business Communications | Dr. Iman    | Fall2021 |  80 |  20 |  50 |
| Calculus - 1            | Dr. Khalid  | Fall2021 |  70 |  15 |  45 |
| Calculus - 2            | Dr. Khalid  | Fall2020 |  85 |  15 |  50 |
 ------------------------- ------------- ---------- ----- ----- -----

Sample data:

StdMarks table:

 ------- -------- --------------- 
| StdID | ExamID | MarksObtained |
 ------- -------- --------------- 
|     1 |      9 |            20 |
|     1 |     10 |            20 |
|     1 |     11 |            20 |
 ------- -------- ---------------

StdCourseTeacher Table:

 ------- ---------- ------------ ------- 
| StdID | CourseID | TeacherID  | SemID |
 ------- ---------- ------------ ------- 
|     1 |        9 |          7 |     6 |
|     1 |       10 |          7 |     6 |
|     1 |       11 |          2 |     6 |
|     2 |        9 |          7 |     6 |
|     2 |       10 |          7 |     6 |
 ------- ---------- ------------ -------

Exam Table:

 -------- -------- ---------- ---------- ------- ---------- ----------- 
| ExamID | EvalID |  Topic   | MaxMarks | SemID | CourseID | TeacherID |
 -------- -------- ---------- ---------- ------- ---------- ----------- 
|      1 |      3 | Mid-Term |       20 |     6 |        1 |         3 |
|      2 |      3 | Mid-Term |       20 |     6 |        2 |         4 |
|      3 |      3 | Mid-Term |       20 |     6 |        3 |         7 |
 -------- -------- ---------- ---------- ------- ---------- -----------

Course Table:

 ---------- --------------------------- ---------- 
| CourseID |        CourseName         | Semester |
 ---------- --------------------------- ---------- 
|        1 | Calculus - 1              |        1 |
|        2 | Business Communications   |        1 |
|        3 | Introduction To Computing |        1 |
 ---------- --------------------------- ----------

Semester Table:

 ------- ------------ 
| SemID |  SemName   |
 ------- ------------ 
|     1 | Spring2020 |
|     2 | Summer2020 |
 ------- ------------

Teacher Table:

 ----------- ------------- 
| TeacherID | TeacherName |
 ----------- ------------- 
|         2 | Dr. Ahmed   |
|         3 | Dr. Khalid  |
 ----------- -------------

CodePudding user response：

I think you want to use group by in order to use aggregate functions as follows:

select CourseName, TeacherName, SemName, min(MarksObtained), Max(MarksObtained), avg(MarksObtained)
from teacher T
inner join CT on CT.teacherid = T.teacherid
inner join course C on C.courseid = CT.courseid
inner join semester S on S.semid = CT.semid
inner join stdmarks M on M.examid = C.courseid
group by CourseName, TeacherName, SemName