(I will write only useful attributes in relation which are required)
Ex: INTERVIEW
company name |
student roll |
A |
1 |
B |
1 |
C |
1 |
A |
2 |
B |
2 |
A |
3 |
OFFER
company name |
student roll |
A |
1 |
B |
1 |
C |
1 |
A |
2 |
So the student with rolls 1,2,3 interviewed. Student 1 did sit for all companies, got the job in all companies A,B,C.
Student 2 sat for A,B, got job in A only. Student 3 sat for A, did not get.
a) Part i) :
minus
equals to
∏scrollno (Interview) - ∏scrollno( Offer)
You got the required student's roll numbers but to print their names, store that in Temp and join with Student table.
∏ scrollno,sname ( Temp ⋈ Student)
a) Part ii) : Those who got interviewed (includes those who got jobs in all,some,none)
Now interviewed - offer = those who did not get jobs or got in some.
Now again subtract whatever you got from all students of the interview again
minus
equals to
But note that it is not an intersection. You may think.... A-(A-B) so intersection.
But it is not... We are doing A-B on all tuples.
But the next subtraction is done on a particular attribute. (It became distinct since we focused on it only)
∏scrollno (Interview) - ∏scrollno( Interview - Offer)
You got the required student's roll numbers but to print their names, store that in Temp and join with Student table.
∏ scrollno,sname ( Temp ⋈ Student)
b) select s.sdegree,AVG(o.osalary) from Student s,Offer o where s.srollno=o.srollno having count(distinct s.srollno)>5 group by s.sdegree;