I need help writing the query for the following schema in SQLite. CREATE TABLE d
ID: 3764369 • Letter: I
Question
I need help writing the query for the following schema in SQLite.
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
);
Which departments have the most prereqs per course, on average? Give only 10 rows of output, showing the 10 departments with the most average prereqs. Your output should look like this:
Athletics|2.0
Comp. Sci.|1.5
History|1.5
Mech. Eng.|1.5
Civil Eng.|1.4
English|1.33
Statistics|1.33
Psychology|1.29
Biology|1.25
Cybernetics|1.25
Explanation / Answer
select dept_name, count(prereq_id as no_of_prereq) from department d, course c, prereq p where c.course_id=p.course_id and c.dept_name=d.dept_name group by(d.dept_name) order by (no_of_prereq) desc
LIMIT 10;