Question

This is database system concept.

1.Find the ids of instructors who are also students using a set operation. Assume that a person is identified by her or his id. So, if the same id appears in both instructor and student, then that person is both an instructor and a student. Remember: set operation means union, intersect or set difference.

2.Find the ids of instructors who are also students using the set membership operator.

3.Find the ids of instructors who are also students using a set comparison operator.

4. Find the ids of instructors who are also students using the exists construct.

5.Find the names and ids of the students who have taken all the courses that are offered by their departments. Notice, the table course contains information about courses offered by departments.

6.Find the names and ids of the students who have taken exactly one course in the Spring 2010 semester.

7.Find the names and ids of the students who have taken at most one course in the Spring 2010 semester.

8.Write a query that uses a derived relation to find the student(s) who have taken at least two courses in the Spring 2010 semester. Remember: derived relation means a subquery in the from clause.

9.Write a query that uses a scalar query in the select clause to find the number of distinct courses that have been taught by each instructor. Schema of the output should be (name, id, number_courses).

​10.Use an outer join to find names of instructors who did not teach any course in the Spring 2010 semester.

11.Write a query that uses the with clause or a derived relation to find the id and number of courses that have been taken by student(s) who have taken the most number of courses.

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.SQL> select id from instructor intersect select id from student;

OUTPUT

ID

-----

501

502

503

2.SQL> select id from instructor where id in(select id from student);

OUTPUT

ID

-----

503

501

502

3.SQL> select id from instructor where id= some (select id from student);

OUTPUT

ID

-----

503

501

502

4.SQL> select id from student where exists (select id from instructor);

OUTPUT

ID

-----

503

501

502

5.SQL> select name,id from student s,course c where s.dept_name=c.dept_name;

OUTPUT

NAME                 ID

-------------------- -----

madhu                501

madhu                501

smith                502

john                 503

john                 503

smith                502

6 rows selected.