Problem converting relational algebra to SQL

J2015
J2015 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
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
      ) AS x

Open in new window

Tip: use indentation
here are 2 useful sites:
http://www.sql-format.com/ (T-SQL specific but I used it for your question)
http://poorsql.com/ (SQL generic)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial