Filtering students by GPA and time enrolled
Question
Suppose you're given the tables below, containing information about various college programs, as well as students currently enrolled in the listed universities. Note that 'college_name_program_id' is a unique identifier for the college/program combination.
Table: college_info
college_name_program_id | college_name | college_program |
---|---|---|
00001 | University A | Engineering |
00002 | University A | Business |
00003 | University A | Mathematics |
00004 | University B | Engineering |
00005 | University B | Business |
00006 | University B | Mathematics |
00007 | University C | Engineering |
00008 | University C | Business |
00009 | University C | Mathematics |
Table: student_info
student_id | college_name_program_id | gpa | year |
---|---|---|---|
31331 | 00001 | 2.8 | 1 |
34583 | 00001 | 3.1 | 3 |
23455 | 00002 | 3.5 | 2 |
23454 | 00005 | 3.8 | 4 |
22337 | 00007 | 2.5 | 3 |
00112 | 00009 | 3.3 | 2 |
12332 | 00004 | 3.4 | 4 |
99123 | 00003 | 2.1 | 3 |
87903 | 00002 | 4 | 1 |
23547 | 00002 | 3.7 | 2 |
66543 | 00003 | 3.6 | 2 |
34554 | 00001 | 3.1 | 1 |
Using the tables above, write a SQL query to return the number of students in each department/program (agnostic of university) that have a GPA of at least 2.8 and have been enrolled for at least 2 years.
You can view/query this table in an interactive SQL fiddle here.