Question

Perform SQL queries in the given data structure.

1. write an SQL query that gives the number of courses taken for every student in the student table.
2. For each instructor, show the instructor name and the number of sections that have been taught by that instructor. You do not need to include instructors who have never taught a section. List in order of decreasing the number of sections taught.
3. Give the number of semester/year combinations in which sections have been offered.

create table classroom

      (building         varchar(15),

      room_number           varchar(7),

      capacity         numeric(4,0),

      primary key (building, room_number)

      );

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

      );

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

      );

create table takes

      (ID               varchar(5),

      course_id        varchar(8),

      sec_id                 varchar(8),

      semester         varchar(6),

      year             numeric(4,0),

      grade                    varchar(2),

      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 student

            on delete cascade

      );

create table advisor

      (s_ID             varchar(5),

      i_ID             varchar(5),

      primary key (s_ID),

      foreign key (i_ID) references instructor (ID)

            on delete set null,

      foreign key (s_ID) references student (ID)

            on delete cascade

      );

create table time_slot

      (time_slot_id          varchar(4),

      day              varchar(1),

      start_hr         numeric(2) check (start_hr >= 0 and start_hr < 24),

      start_min        numeric(2) check (start_min >= 0 and start_min < 60),

      end_hr                 numeric(2) check (end_hr >= 0 and end_hr < 24),

      end_min          numeric(2) check (end_min >= 0 and end_min < 60),

      primary key (time_slot_id, day, start_hr, start_min)

      );

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

      );

Answer 1

1. write an SQL query that gives the number of courses taken for every student in the student table.

select student.ID, count(course.course_id) from student, takes, course where student.ID = takes.ID and course.course_id = takes.course_id group by student.ID;

2. For each instructor, show the instructor name and the number of sections that have been taught by that instructor. You do not need to include instructors who have never taught a section. List in order of decreasing the number of sections taught.

select instructor.name, count(section.sec_id) from instructor, teaches, section where instructor.ID = teaches.ID and teaches.sec_id and section.sec_id and instructor.ID IN (select teaches.ID from teaches);

3. Give the number of semester/year combinations in which sections have been offered.

The column names and table names are case sensitive. Please be careful on that.