Sql : plz tell the querry


#1

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 student is interviewed by a company. The OFFER relation gives the salary offered to a student who is successful in a company’s interview.
Write a relational algebra expressions (using only the operator σ,π,∪,−σ,π,∪,− ) for the following queries:
List the rollnumbers and names of students who went for interviews and received job offers from every company with which they interviewed


#2

π<srollno,sname> ( σ<OFFER.cname=INTERVIEW.cname> (STUDENT ⋈ INTERVIEW ⋈ OFFER) )


#3

wrong querry… as it return all the students name and rollno who are plzced


#4

X-> π<srollno,cname> (STUDENT X INTERVIEW)

Y-> π<srollno,cname> ( σ<OFFER.cname=INTERVIEW.cname> (STUDENT ⋈ INTERVIEW ⋈ OFFER) )

Z-> π ( X -Y)

Ans-> π<srollno,sname>( σ<STUDENT.srollno != Z.srollno> Z)

will this work?


#5

well this is the same querry of ruturaj and no that not work… as Y contains students placed, x contain student who interviewed so z contain student not placed… and in and again u r finding all students placed… :smiley:


#6

A-> π<srollno,cname> (INTERVIEW) −π<srollno,cname> (OFFER)

B-> π<srollno,cname> (INTERVIEW ) − A

X-> π<srollno,cname> (STUDENT X B)

Y-> π<srollno,cname> ( σ<OFFER.cname=INTERVIEW.cname> (STUDENT ⋈ INTERVIEW ⋈ OFFER) )

Z-> π ( X -Y)

Ans-> π<srollno,sname>( σ<STUDENT.srollno != Z.srollno> (Z⋈S))

I have eliminated that problem. See now.


#7

no… :frowning: what is x fetching???


#8

I am explaining what i am trying to do.

 Names  Companies
     A   Google
     B    Yahoo
     A   Facebook
     B    Google
     A    Yahoo

Suppose we have the Student A and B , & Google, Yahoo, Facebook as the companies.

 Names  Companies
     A   Google
     A    Yahoo
     A   Facebook
     B    Yahoo
     B   Facebook
     B    Google

So the above table is basically my ‘X’. Now if i subtract the previous table from it,
i will get

B Facebook

Now try executing the rest of the queries. It’s really difficult to test and explain. SQL queries are easier to write. In gate writing queries won’t be asked. We can see the options and then select from it.


#9

the question is “List the roll numbers and names of students who went for interviews and received job offers from every company with which they interviewed” In this it is not compulsory that student have to sit for every company… for eg: 1 student sit in company A,B,C and 2nd student sit in company B and D so our answer should be both 1st and 2nd student


#10

Yeah you are right…I didn’t read the question properly before.

It would be really difficult then…