GATEverse — GATE CSE previous year questions

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:

\begin{array}{|ll|}\hline \text{COMPANY(}\underline{\text{cname}}\text{, clocation)} & \text{STUDENT(}\underline{\text{srollno}}\text{, sname, sdegree)} \\ \text{INTERVIEW(}\underline{\text{cname, srollno}}, \text{idate)} & \text{OFFER(}\underline{\text{cname, srollno}}\text{, osalary)} \\\hline \end{array}

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 student 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 a relational algebra expressions (using only the operators $\bowtie, \sigma, \pi, \cup, -$ ) for the following queries. List the $rollnumbers$ and $names$ of students who attended at least one interview but did not receive $any$ job offer. 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.