using SQLite Which courses have no corresponding sections? Query for all such co

Question

using SQLite

Which courses have no corresponding sections? Query for all such courses, with output ordered by course_id, and no more than 10 rows of output produced. (Hint 1: to test if a field 'a' is null, you can write 'a is null'. Hint 2: do't forget your outer joins.)

I have tables:

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 section
(course_id varchar(8),
sec_id varchar(8),
semester varchar(6)
check (semester in ('Fall', 'Winter', 'Spring', 'Summer')),
year numeric(4,0) check (year > 1701 and year < 2100)
,
building varchar(15),
room_number varchar(7),
time_slot_id varchar(4),
primary key (course_id, sec_id, semester, year),
foreign key (course_id) references course
on delete cascade,
foreign key (building, room_number) references classroom
on delete set null
);

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
);

Explanation / Answer

select top 10 distinct o.course_id from

(select c.course_id, s.course_id as flag from course c

left outer join section s

on c.course_id = s.course_id

where flag is null) o

order by o.course_id

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.