Table Name : students
| Column Name | Type |
|---|---|
| roll_no | varchar(20) |
| name | varchar(30) |
- roll_no is the primary key for this table.
- This table gives roll_no and name of the students.
Table Name : student_marks
| Column Name | Type |
|---|---|
| student_id | varchar(20) |
| subject1 | int |
| subject2 | int |
| subject3 | int |
| subject4 | int |
| subject5 | int |
| subject6 | int |
- student_id is the primary key for this table. It is also a foreign key to the students table referencing to roll_no.
- This table gives student_id and the marks of the students in 6 subjects.
Table Name : subjects
| Column Name | Type |
|---|---|
| id | varchar(20) |
| name | varchar(30) |
| pass_marks | int |
- id is the primary key for this table.
- This table gives id of the subjects (same as of mentioned in student_marks), name of the subjects and pass_marks.
Given tables represent the marks scored by engineering students.
Write a SQL query display the following results for each student.
- Student_id, Student name
- Total Percentage of all marks
- Failed subjects (must be comma seperated values in case of multiple failed subjects)
- Result (if percentage >= 70% then 'First Class', if >= 50% & <70% then 'Second class', if <50% then 'Third class'. The result should be 'Fail' if a students fails in any subject irrespective of the percentage marks).
Students have the option to choose either 4 or 5 subjects only.
Please refer to the sample below for a better idea.
The query result format is in the following example:
Input
Table Name: students
| roll_no | name |
|---|---|
| 2GR5CS011 | Maryam |
| 2GR5CS012 | Rose |
| 2GR5CS013 | Alice |
| 2GR5CS014 | Lilly |
| 2GR5CS015 | Anna |
| 2GR5CS016 | Zoya |
Table Name: student_marks
| student_id | S1 | S2 | S3 | S4 | S5 | S6 |
|---|---|---|---|---|---|---|
| 2GR5CS011 | 75 | null | 56 | 69 | 82 | null |
| 2GR5CS012 | 57 | 46 | 32 | 30 | null | null |
| 2GR5CS013 | 40 | 52 | 56 | null | 31 | 40 |
| 2GR5CS014 | 65 | 73 | null | 81 | 33 | 41 |
| 2GR5CS015 | 98 | null | 94 | null | 90 | 20 |
| 2GR5CS016 | null | 98 | 98 | 81 | 84 | 89 |
Table Name: subjects
| id | name | pass_marks |
|---|---|---|
| S1 | Mathematics | 40 |
| S2 | Algorithms | 35 |
| S3 | Computer Networks | 35 |
| S4 | Data Structure | 40 |
| S5 | Artificial Intelligence | 30 |
| S6 | Object Oriented Programming | 35 |
Output
| student_id | student_name | percentage_marks | failed_subjects | result |
|---|---|---|---|---|
| 2GR5CS011 | Maryam | 70.50 | null | First Class |
| 2GR5CS012 | Rose | 41.25 | Data Structure,Computer Networks | Fail |
| 2GR5CS013 | Alice | 43.80 | null | Third Class |
| 2GR5CS014 | Lilly | 58.60 | null | Second Class |
| 2GR5CS015 | Anna | 75.50 | Object Oriented Programming | Fail |
| 2GR5CS016 | Zoya | 90.00 | null | First Class |