create table course (course_id varchar(8), title varchar(50), dept_name varchar(
ID: 3701249 • Letter: C
Question
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 instructor
(ID varchar(5),
name varchar(20) not null,
dept_name varchar(20),
salary numeric(8,2) check (salary > 29000),
primary key (ID),
foreign key (dept_name) references department
on delete set null
);
create table teaches
(ID varchar(5),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
primary key (ID, course_id, sec_id, semester, year),
foreign key (course_id,sec_id, semester, year) references section
on delete cascade,
foreign key (ID) references instructor
on delete cascade
);
SQL
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
according to given qurey, there are three table course, instructor, teaches.
Now we have to find list of those course title that have not been taught any time. Means that course that has not any record in teaches table.
so, we will find the list of that course that course_id not match in teaches Table.
below is the query:-
Select course_id, title from course where course_id NOT IN (select course_id from teaches) order by title limit 10;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.