I need to translate this into relational algebra, then translate the relational algebra in SQL for 1) SQL Server and 2) Access: "List the names of departments in which ALL jobs, except PRESIDENT, are held by at least one employee."
We have two tables:
DEPARTMENT (dptname, dptID)
EMPLOYEE (empID, job, dptID)
Here is the relational algebra:
R1 = Π job (∑ job <> ‘PRESIDENT’ (EMPLOYEE))
R2 (e2, dptID) =Π job, dptID (R1 X DEPARTMENT)
R3 = Π job, dptID (EMPLOYEE)
R4 (e2, d2, e, d) = Π e2, R2.dptID, R3.job, R3.dptID (R2 =* R3)
R5 = Π dptname (∑ e <> NULL (R4 X DEPARTMENT))
Here is my R1 statement converted to SQL. It works well:
SELECT job FROM (SELECT DISTINCT job FROM EMPLOYEE WHERE job <> 'PRESIDENT') AS R1
However, my converted R2 statement fails. Here is the preliminary version, where the embedded R1 is still temporarily represented by 'R1' showing its embedded position inside the logic:
SELECT e2, nodpt FROM (SELECT DISTINCT job AS e2, dptID FROM DEPARTMENT, (R1)) AS R2
And here is the final version, where 'R1' has been replaced with its actual SQL text:
SELECT e2, dptID FROM (SELECT DISTINCT job AS e2, dptID FROM DEPARTMENT, (
SELECT job FROM (SELECT DISTINCT job FROM EMPLOYEE WHERE job <> 'PRESIDENT') AS R1
)) AS R2
My SQL fails in SQL Server with error message "Msg 102, Level 15, State 1, Line 15 Incorrect syntax near ')'.".
What am I doing wrong? I guess if you can help determine why it fails, then I should be able to convert statements R3, R4, and R5 without any problems.
Thanks