I need help with the following schema in SQLite: Which courses are prereqs for t
ID: 3764371 • Letter: I
Question
I need help with the following schema in SQLite:
Which courses are prereqs for the most other courses? Give only 10 rows of output, showing the titles of the courses with the most prereqs, along with the number of courses for which they are prereqs. Order by decreasing count and then increasing title. Your output should look like this:
Differential Geometry|3
World History|3
Antidisestablishmentarianism in Modern America|2
Bankruptcy|2
Compiler Design|2
Data Mining|2
Differential Equations|2
Environmental Law|2
Game Programming|2
Greek Tragedy|2
The Schema is the following:
CREATE TABLE department
(dept_name varchar(20),
building varchar(15),
budget numeric(12,2) check (budget > 0),
primary key (dept_name)
);
CREATE TABLE course
(course_id varchar(8),
title varchar(50),
dept_name varchar(20),
credits numeric(2,0) check (credits > 0),
primary key (course_id),
foreign key (dept_name) references department
on delete set null
);
CREATE TABLE prereq
(course_id varchar(8),
prereq_id varchar(8),
primary key (course_id, prereq_id),
foreign key (course_id) references course
on delete cascade,
foreign key (prereq_id) references course
);
Explanation / Answer
SELECT course.title, count(*)
FROM prereq
INNER JOIN course
ON prereq.prereq_id=course.course_id
GROUP BY prereq_id
ORDER BY count(*) desc, course.title asc
LIMIT 10