Please help with these SQL questions. Please explain query after. They are based
ID: 3701280 • Letter: P
Question
Please help with these SQL questions. Please explain query after. They are based off of courses-ddl.sql and courses-large.sql
-- 3. For each instructor ID, show the number of courses taught in 2010.
-- Show every instructor, even if no classes taught in 2010.
-- Each line of output should give an instructor ID and a count.
-- Give the output in order of decreasing number of courses taught in 2010,
-- and limit to 10 instructors.
-- 4. 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.
-- 5. Which courses have not been taught at any time?
-- For each such course give the course ID and title.
-- List in order of course title.
-- Limit output to 10 courses.
Explanation / Answer
Hi friend, please post one question per post as per the chegg guideline.
I have answered Q4.
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;