I want to convert a sql query to relational algebra containing exist keyword-CodePudding

select fname,lname,salary from employee where exists
(select essn from dependent where employee.sex = dependent.sex);

this is the query

CodePudding user response：

Nicer SQL, avoiding exists:

select unique fname,lname,salary from employee
                                 join dependent on employee.sex = dependent.sex;

Nicer nicer SQL, but only works if sex is the only column in common between tables employee, dependent

select unique fname,lname,salary from employee
                                 natural join dependent;

Relational Algebra Appendix A style (with the same proviso)

(employee JOIN dependent) {fname,lname,salary}

Relational Algebra Codd 1972 style (with the same proviso)

π<fname,lname,salary>(employee ⋈ dependent)

(putting the attribute names in < >, because subscripting doesn't work in SO's code.)

CodePudding user response：

/*EXISTS in the Where Clause (by example)*/

SELECT S.movieTitle, M.studioName
FROM StarsIn S, Movie M
WHERE S.movieYear >= 2000
AND S.movieTitle = M.title
AND EXISTS (
    SELECT name
    FROM MovieStar
    WHERE birthdate = 1960 AND name = S.starName)