I need help converting this query in english into SQL form and write run them on
ID: 3929436 • Letter: I
Question
I need help converting this query in english into SQL form and write run them on access. Underneath is the data that i am using for conversion.
Query
For each student, find the number of courses taken by this student. Output a table consisting of three columns: FirstName, LastName, Number of Courses.
Students
StudentId
FirstName
LastName
Degree
001
Linus
Torvalds
BS
002
Jeff
Bezos
MS
003
Pierre
Omidyar
BS
004
Sergey
Brin
MS
005
Larry
Page
MS
006
Mark
Zuckerberg
BS
Transcripts
StudentId
Term
CourseId
Grade
001
Fall 2014
Math 200
C
001
Spring 2015
CS 300
B
002
Fall 2014
CS 200
A
002
Spring 2015
CS 300
B
002
Fall 2015
CS 400
003
Fall 2014
Math 200
A
003
Spring 2015
CS 300
B
003
Fall 2015
CS 200
004
Fall 2014
CS 400
A
004
Spring 2015
Phys 400
B
005
Spring 2015
Phys 400
B
005
Fall 2015
CS 400
006
Spring 2015
Math 200
C
006
Fall 2015
CS 200
Students
StudentId
FirstName
LastName
Degree
001
Linus
Torvalds
BS
002
Jeff
Bezos
MS
003
Pierre
Omidyar
BS
004
Sergey
Brin
MS
005
Larry
Page
MS
006
Mark
Zuckerberg
BS
Explanation / Answer
we can use this query which works for me:
select s1.firstName, s1.LastName, count(*) as numberofcourse from students s1, transcripts t1 where s1.studentid = t1.studentid group by t1.studentid
Output:
FirstName LastName numberofcourse Linus Torvalds 2 Jeff Bezos 3 Pierre Omidyar 3 Sergey Brin 2 Larry Page 2 Mark Zuckerberg 2Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.