5道较难的数据库查询习题(Intermediate SQL)

习题之前，我们先建立了11张表：

create table classroom
    (building     varchar(15),
     room_number      varchar(7),
     capacity     numeric(4,0),
     primarykey (building, room_number)
    );
 
create table department
    (dept_name    varchar(20),
     building     varchar(15),
     budget           numeric(12,2) check (budget > 0),
     primarykey (dept_name)
    );
 
create table course
    (course_id    varchar(8),
     title        varchar(50),
     dept_name    varchar(20),
     credits      numeric(2,0) check (credits > 0),
     primarykey (course_id),
     foreignkey (dept_name) references department
       on deleteset null
    );
 
create table instructor
    (ID        varchar(5),
     name         varchar(20) not null,
     dept_name    varchar(20),
     salary       numeric(8,2) check (salary > 29000),
     primarykey (ID),
     foreignkey (dept_name) references department
       on deleteset 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),
     primarykey (course_id, sec_id, semester, year),
     foreignkey (course_id) references course
       on deletecascade,
     foreignkey (building, room_number) references classroom
       on deleteset null
    );
 
create table teaches
    (ID        varchar(5),
     course_id    varchar(8),
     sec_id       varchar(8),
     semester     varchar(6),
     year         numeric(4,0),
     primarykey (ID, course_id, sec_id, semester, year),
     foreignkey (course_id,sec_id, semester, year) references section
       on deletecascade,
     foreignkey (ID) references instructor
       on deletecascade
    );
 
create table student
    (ID        varchar(5),
     name         varchar(20) not null,
     dept_name    varchar(20),
     tot_cred     numeric(3,0) check (tot_cred >= 0),
     primarykey (ID),
     foreignkey (dept_name) references department
       on deleteset null
    );
 
create table takes
    (ID        varchar(5),
     course_id    varchar(8),
     sec_id       varchar(8),
     semester     varchar(6),
     year         numeric(4,0),
     grade            varchar(2),
     primarykey (ID, course_id, sec_id, semester, year),
     foreignkey (course_id,sec_id, semester, year) references section
       on deletecascade,
     foreignkey (ID) references student
       on deletecascade
    );
 
create table advisor
    (s_ID         varchar(5),
     i_ID         varchar(5),
     primarykey (s_ID),
     foreignkey (i_ID) references instructor(ID)
       on deleteset null,
     foreignkey (s_ID) references student(ID)
       on deletecascade
    );

接下来我们再往各张表里插入数据(代码略)后，我们来看看11张表的信息：

classroom:                                                                 department:

                            

course:                                                                   instructor: