SQL Challenges-1: Students achieved 100 percent for the first year of each examination of every subject
SQL Challenges-1: Exercise-31 with Solution
From the following tables write a SQL query to compute total number of students who achieved 100 percent for the first year of each examination of every subject. Return examination ID, subject name, first year, number of students.
Input:
Table: exam_test
Structure:
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
exam_id | int(11) | NO | PRI | ||
subject_id | int(11) | NO | PRI | ||
exam_year | int(11) | NO | PRI | ||
no_of_student | int(11) | YES |
Data:
exam_id | subject_id | exam_year | no_of_student |
---|---|---|---|
71 | 201 | 2017 | 5146 |
71 | 201 | 2018 | 3545 |
71 | 202 | 2017 | 2701 |
71 | 202 | 2018 | 5945 |
71 | 202 | 2019 | 2500 |
71 | 203 | 2017 | 2500 |
72 | 201 | 2018 | 3500 |
72 | 202 | 2017 | 3651 |
73 | 201 | 2018 | 2647 |
73 | 201 | 2019 | 2647 |
73 | 202 | 2018 | 4501 |
Table: subject_test
Structure:
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
subject_id | int(11) | NO | PRI | ||
subject_name | varchar(255) | YES |
Data:
subject_id | subject_name |
---|---|
201 | Mathematics |
202 | Physics |
203 | Chemistry |
exam_id is the primary key of this table.
Sample Solution:
SQL Code(MySQL):
CREATE TABLE exam_test (exam_id int not null, subject_id int not null, exam_year int not null, no_of_student int,
primary key (exam_id,subject_id,exam_year));
INSERT INTO exam_test VALUES (71,201,2017,5146);
INSERT INTO exam_test VALUES (72,202,2017,3651);
INSERT INTO exam_test VALUES (73,202,2018,4501);
INSERT INTO exam_test VALUES (71,202,2018,5945);
INSERT INTO exam_test VALUES (73,201,2018,2647);
INSERT INTO exam_test VALUES (71,201,2018,3545);
INSERT INTO exam_test VALUES (73,201,2019,2647);
INSERT INTO exam_test VALUES (72,201,2018,3500);
INSERT INTO exam_test VALUES (71,203,2017,2500);
INSERT INTO exam_test VALUES (71,202,2019,2500);
INSERT INTO exam_test VALUES (71,202,2017,2701);
INSERT INTO exam_test VALUES (73,201,2017,1000);
CREATE TABLE subject_test (subject_id int not null unique, subject_name varchar(255));
INSERT INTO subject_test VALUES (201,'Mathematics');
INSERT INTO subject_test VALUES (202,'Physics');
INSERT INTO subject_test VALUES (203,'Chemistry');
select s1.exam_id, p.subject_name,s1.exam_year as first_year, s1.no_of_student
from exam_test s1
JOIN subject_test p on s1.subject_id = p.subject_id
join (select subject_id, min(exam_year) min_yr
from exam_test group by subject_id) s2
on s1.subject_id = s2.subject_id
and s1.exam_year = s2.min_yr;
Sample Output:
exam_id|subject_name|first_year|no_of_student| -------|------------|----------|-------------| 71|Mathematics | 2017| 5146| 71|Physics | 2017| 2701| 71|Chemistry | 2017| 2500| 72|Physics | 2017| 3651| 73|Mathematics | 2017| 1000|
SQL Code Editor:
Contribute your code and comments through Disqus.
Previous: Students achieved 100 percent marks in every subject for all the year.
Next: Average experience for each scheme.
SQL: Tips of the Day
SQL Server SELECT into existing table.
INSERT INTO dbo.TABLETWO SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:
INSERT INTO dbo.TABLETWO (col1, col2) SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
Database: SQL Server
Ref: https://bit.ly/3y6tpA3
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework