create table student (ID varchar(5), name varchar(20) not null, dept_name varcha
ID: 3700898 • Letter: C
Question
create table student
(ID varchar(5),
name varchar(20) not null,
dept_name varchar(20),
tot_cred numeric(3,0) check (tot_cred >= 0),
primary key (ID),
foreign key (dept_name) references department
on delete set null
);
create table takes
(ID varchar(5),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
grade varchar(2),
primary key (ID, course_id, sec_id, semester, year),
foreign key (course_id,sec_id, semester, year) references section
on delete cascade,
foreign key (ID) references student
on delete cascade
);
SQL
Which student (by ID) has received the most grades of "A" or "A-"?
-- Give a single output line with the student's ID, name, and the count.
Explanation / Answer
Here is your Query:
select id,name,cnt from(
select s.id,s.name,t.grade,count(*) cnt,maxcnt FROM student s , takes T
inner join (select grade,Max(cnt) maxcnt from ( select id,grade,count(*) cnt from takes where grade in ('A','A-') group by id , grade ) a group by grade) a1
on a1.grade=t.grade
where s.id= t.id and t.grade in ("A","A-") group by s.id , t.grade having cnt=maxcnt) a;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.