List all active students in June by name. (make up names and other data if you a

Question

List all active students in June by name. (make up names and other data if you are actually building a prototype database). Include the number of hours students received tutoring and how many lessons they completed. NEED SQL QUERY.

TutorD, CartDate, Status) MATCH HISTORY MatchD, TutonD, Studer D, TutorD CertDate Status StarDate, EndDatel 100 1/05/2008 Active 101 1/05/2008 | Temp Stop 102 1/06/2008 | Dropped 103 5/22/2008 Active 104 6/22/2008 | Active 105 6/22/2008 Temp Slop 1065/2212008 Activo 100 3000 1n0/2008 101 102 106 3001 1/15/2008 5/15/2008 3002 2/10/20083/01/2008 3003 5/28/2008 103 104 104 3004 6/01/20086/15/2008 3005 8/01/2008 6/28/2000 3006 8/01/2008 STUDENT (StudentiD, Road) StudentID Read 3000 23 3001 5.6 3002 1.3 3003 3.3 3004 2. 3005 48 300678 30015 TUTOR REPORT (MatchID, Month, Hours, Lessons) 1 6708 46106 5 6106 4 7/00 17/08 10

Explanation / Answer

In the database schema Student table does not contain any Name field, so I am assuming Student contains Name attribute as well. So the new schema of Student table would be-

Student (StudentID, Name, Read)

In order to get the hours attended by each student, will use the aggragate function SUM() on hour as well as on Lessons. As aggregate function is used in the select clause, will use group by at the end. Where clause is used to get the detail of month June.

Query

SELECT s.StudentID, s.Name, SUM(tr.Hours), SUM(tr.Lessons)

FROM STUDENT AS s

INNER JOIN MATCHHISTORY AS m

ON m.StudentID = s.StudentID

INNER JOIN TUTORREPORT AS tr

ON tr.MatchID = m.MatchID

WHERE tr.Month = '6/08'

GROUP BY s.StudentID, s.Name;

Related Questions

Navigate

• Browse (All)
• Browse L
• Subjects

---

---

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