create table student (s_no int primary key, name varchar(20), class varchar(20))
engine=InnoDB;
create table competition (c_no int primary key, name varchar(20), type varchar(20))
engine=InnoDB;
create table s_c (s_no int, c_no int, rank int, year int, foreign key (s_no) references student (s_no),
foreign key (c_no) references competition (c_no))
engine=InnoDB;
insert into student values (1, "BBB", "7th");
insert into student values (2, "AAA", "9th");
insert into student values (3, "DDD", "8th");
insert into student values (4, "CCC", "10th");
insert into student values (5, "FFF", "8th");
insert into student values (6, "EEE", "10th");
insert into competition values (101, "Running", "Sports");
insert into competition values (102, "Chess", "Edu");
insert into s_c values (2, 102, 1, 2000);
insert into s_c values (1, 102, 3, 2000);
insert into s_c values (3, 102, 2, 2000);
insert into s_c values (5, 102, 1, 2000);
insert into s_c values (4, 102, 3, 2000);
insert into s_c values (2, 102, 2, 2000);
insert into s_c values (1, 102, 1, 2000);
insert into s_c values (3, 102, 2, 2000);
insert into s_c values (4, 101, 1, 2000);
insert into s_c values (3, 101, 2, 2000);
insert into s_c values (5, 101, 1, 2000);
insert into s_c values (3, 101, 3, 2000);
insert into s_c values (6, 101, 2, 2000);
insert into s_c values (1, 101, 1, 2000);
insert into s_c values (4, 101, 2, 2000);
insert into s_c values (3, 101, 3, 2000);
select * from student;
select * from competition;
select * from s_c;
Ok so that's the code I've written till now.. As you can see s_c table represents the many to many relation between student and competition.. The query I'm attempting to do is find the student who has won (i.e. 1st rank) most number of times and am not able to get any solution
select name from student where s_no in (select s_no from s_c where rank=1 ORDER BY rank);
I know the code I'm entering to get the required output isn't exactly what is expected in the question so I'll be glad if anyone would help
Question
SVG Veteran
Ok so that's the code I've written till now.. As you can see s_c table represents the many to many relation between student and competition.. The query I'm attempting to do is find the student who has won (i.e. 1st rank) most number of times and am not able to get any solution
I know the code I'm entering to get the required output isn't exactly what is expected in the question so I'll be glad if anyone would help
Link to comment
Share on other sites
2 answers to this question
Recommended Posts