Answer Happy

Below you are given queries expressed both in plain English and in SQL. Indicate which ones match, even if the SQL query could be expressed simpler. All queries use the following schema: Person (v_number, name, favourite_colour) Student (v_number, major) Instructor (v_number, specialisation) Class (crn, name) Teaches (instructor, crn, semester) EnrolledIn (student, crn, semester, grade) Find the student who has taken the same course the most number of times SELECT student FROM `EnrolledIn GROUP BY `crn` ORDER BY COUNT(*) LIMIT 1; Create a view that shows all students, together with the highest grade they have received in any class so far. CREATE VIEW top_grades` AS ( SELECT `Student`.*, MAX(grade`) AS `top_grade FROM `Student ); JOIN `Class ON (v_number GROUP BY `student student')
Provide the name of every unique person who is enrolled in at least three courses SELECT Name* FROM `Person JOIN `EnrolledIn` ON (student` = v_number) GROUP BY `v_number HAVING COUNT(*) >= 3; Retrieve all students whose favourite colour is a shade of blue SELECT * FROM `Person WHERE `favourite_colour` = 'a shade of blue'; Indicate the names of all instructors who are teaching the same course in the same semester as somebody else (i.e., co-teaching) SELECT `name` FROM `Person JOIN `Teaches` AS `T1` ON (v_number` = `T1`.`instructor`) JOIN `Teaches` AS `T2 ON (T1.`crn` = `T2`.`crn` AND `T1.semester = `T2`.`semester`) WHERE `T2`.`instructor <> `v_number";