Relational-Algebra

A university placement center maintains a relational database of companies that interview students on campus and make job offers to those successful in the interview. The schema of the database is given below:

  COMPANY (cname, clocation)     STUDENT (scrollno, sname, sdegree)
   INTERVIEW (cname, srollno, idate)  OFFER (cname,srollno, osalary) 

The COMPANY relation gives the name and location of the company. The STUDENT relation gives the student’s roll number, name and the degree program for which the student is registered in the university. The INTERVIEW relation gives the date on which a students is interviewed by a company. The OFFER relation gives the salary offered to a student who is successful in a company’s interview. The key for each relation is indicated by the underlined attributes.

(a) Write relational algebra expressions (using only the operator ⨝,σ,π,∪,−) for the following queries:

(i) List the rollnumbers and names of those students who attended at least one interview but did not receive any job offer.
(ii) List the rollnumbers and names of students who went for interviews and received job offers from every company with which they interviewed.

(b) Write an SQL query to list, for each degree program in which more than five students were offered jobs, the name of the degree and the average offered salary of students in this degree program.