Lab schema (HR schema) is listed as below (primary keys are underlined):

Regions(region_id, region_name);
Countries(country_id, country_name, region_id);
Locations(location_id, street_address, postal_code, city, state_province, country_id);
Departments(department_id, department_name, manager_id, location_id);
Jobs(job_id, job_title, min_salary, max_salary);
Employees(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id);
Job_History(employee_id, start_date, end_date, job_id, department_id);

• PSJ queries and existential queries

Write equivalent SQL, Relational Algebra and Datalog query for each of the following questions.

Note that (Rou_T(*) Relation) means to rename the table Relation to T
  and keep the names of all the columns in the original table Relation.

set your session to use HR schema, so that we don't prefix each table with hr.

1. For some of the employees, they do not belong to any department (or we do not know in which department they work). For each such employee, list his/her full name.

   select first_name, last_name
   from   employees
   where  department_id is null;
   
   Pi_{first_name, last_name} (Sigma_(department_id is null) employees)
   
   Result(fn, ln) ::= Employees(_, fn, ln, _, _, _, _, _, _, _, did)
                   and (did is null)
   

2. List the name of each employee who once had the title of "Public Accountant".

   select first_name, last_name
   from   employees
   where  employee_id IN (select employee_id
                          from   job_history natural join jobs
                          where  job_title = 'Public Accountant');
   
   Pi_{first_name, last_name} ((Rou_E(*) Employees) Join_(E.employee_id = JH.employee_id)
                              (Rou_JH(*) Job_History) Join_(JH.job_id = Jobs.job_id)
                              (Sigma_(job_title = 'Public Accountant') Jobs))
   
   Result(fn, ln) ::= Employees(eid, fn, ln, _, _, _, _, _, _, _, _)
                  and Job_History(eid, _, _, jid, _)
                  and Jobs(jid, 'Public Accountant', _, _)
   
   

3. List the name of each employee who once had a title that's not "Public Accountant".

   select first_name, last_name
   from   employees
   where  employee_id IN (select employee_id
                          from   job_history natural join jobs
                          where  job_title <> 'Public Accountant');
   
   Pi_{first_name, last_name} ((Rou_E(*) Employees) Join_(E.employee_id = JH.employee_id)
                              (Rou_JH(*) Job_History) Join_(JH.job_id = Jobs.job_id)
                              (Sigma_(job_title <> 'Public Accountant') Jobs))
   
   Result(fn, ln) ::= Employees(eid, fn, ln, _, _, _, _, _, _, _, _)
                  and Job_History(eid, _, _, jid, _)
                  and Jobs(jid, title, _, _)
                  and title <> 'Public Accountant'
   

4. List the name of each employee who never had the title of "Public Accountant".

   select first_name, last_name
   from   employees
   where  employee_id NOT IN (select employee_id
                              from   job_history natural join jobs
                              where  job_title = 'Public Accountant');
   
   (Pi_{first_name, last_name} Employees)
   -
   (Pi_{first_name, last_name} (Rou_E(Employees) Join_(E.employee_id = JH.employee_id)
                              Rou_JH(Job_History) Join_(JH.job_id = Jobs.job_id)
                              (Sigma_(job_title = 'Public Accountant') Jobs)))
   
   Result(fn, ln) ::= Employees(eid, fn, ln, _, _, _, _, _, _, _, _)
                  and Jobs(jid, 'Public Accountant', _, _)
                  and NOT Job_History(eid, _, _, jid, _)
   

5. List the unique country id and country name of each country in which at least one department locates.
   • Relational Algebra and Datalog

     Pi_{country_id, country_name} (Countries J Locations J Departments)
     
     Result(cid, cname) ::= Countries(cid, cname, _)
                        and Locations(lid, _, _, _, _, cid)
                        and Departments(_, _, _, lid)
     

   • Using only joins (and distinct)

     select distinct country_id, country_name
     from   Countries natural join Locations natural join Departments;
     

   • Using operator IN

     select country_id, country_name
     from   Countries
     where  country_id IN (select country_id
                           from   Departments natural join Locations);
     

   • Using predicate EXISTS

     select country_id, country_name
     from   Countries C
     where  exists (select *
                    from   Departments natural join Locations L
                    where  L.country_id = C.country_id);
     

6. List the id and name of each department in which no employee works.
   • Relational Algebra and Datalog

     (Pi_{department_id, department_name} Departments)
     -
     (Pi_{department_id, department_name} ((Rou_D(*) Departments)
                           Join_(D.department_id = E.department_id)
                           (Rou_E(*) Employees)))
     
     Result(did, dname) ::= Departments(did, dname, _, _, _)
                        and Not Employees(_, _, _, _, _, _, _, _, _, _, did)
     

   • Using operator IN (beware the null value in the department_id column in the Employees table)

     select department_id, department_name
     from   Departments
     where  department_id Not IN (select department_id
                                  from   Employees
                                  where  department_id is not null);
     

   • Using predicate EXISTS

     select department_id, department_name
     from   Departments D
     where  not exists (select *
                        from Employees E
                        where D.department_id = E.department_id);
     

   • Using set difference and join only

     select department_id, department_name
     from   Departments
     minus
     select department_id, department_name
     from   Departments D join Employees E
            on D.department_id = E.department_id;
     
     

   • Using outer join and null value detection

     select department_id, department_name
     from   Departments D left join Employees E
            on D.department_id = E.department_id
     where  E.employee_id is null;