Hello, can anybody answer all these questions for me?. I don\'t know how to make

Question

Hello, can anybody answer all these questions for me?. I don't know how to make queries and it's great if you can answer everything and if not at least many so I will have a guide to follow and answer the rest. thank you

University Schema:

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
   )

For the following, use the University schema from the SQL dass exercise. Write SQL queries (a) Using takes, the enrollment in each course. Display the course ID and the enrollment. (b) Display the course ID and enrollment of those courses with an enrollment of two or fewer (e) Using asub query, display the course ID and enrollment of the course with the maximum (d) Using asub query, display the course IDand enrollment of the counses with the minimum (e) The enrollment in each section of each course. Display the course ID, the section ID and The course IDs of those counses in teaches with a course ID that begins with BIO Each course ID should be displayed only once. (g) Using a sub-query and IN, the names of those instructors who have taught one or more Each name should be displayed only once. of those courses. (h) The names of those instructors who have taught all of those courses. Each name should be displayed only once. This time, you may not assume that SQL has a DIV operator. Instead, implement division using the "counting technique described on slides 29 and 30 of the On Making Relational Division Comprehensible presentation available on the course web site on Note: If you're not careful, you'll be countingsectious of courses rather than courses. You queries should be written in such a way that they would work correctly with any instance of the database.

Explanation / Answer

Multiple Questions : Answering 1st four.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

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