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);

Legend:

In Relational Algebra queries,

Pi_{columns} represents the projection operator,
Sigma_(condition) represents the selection operator,
Join_(condition) represents the join operator,
and Join alone represents the natural join operator.

Write a SQL query for each of the following questions. Also, if possible, write the equivalent Relational Algebra and Datalog queries.

1. Find the first and last name of each employee whose area code of his/her phone number is 515;

   SQL:
   select first_name, last_name
   from   hr.employees E
   where  phone_number like '515.%';
   
   Relational Algebra:
   Pi_{first_name, last_name}
     (Sigma_(phone_number like '515.%') Employees)
   
   Datalog:
   Result(first_name, last_name) ::=
          Employees(_, first_name, last_name, _, pn, _, _, _, _, _, _)
      and pn like '515.%'
   

2. For each employee, list his/her name and the name of the department he/she works in;

   SQL:
   select first_name, last_name, department_name
   from hr.Employees E, hr.Departments D
   where E.department_id = D.department_id;
   
   select first_name, last_name, department_name
   from hr.Employees E join hr.Departments D
        on E.department_id = D.department_id;
   
   Relational Algebra:
   Pi_{first_name, last_name, department_name}
         (Employees Join_(department_id) Departments)
   
   Datalog:
   Result(fn, ln, dn) ::= Employees(_, fn, ln, _, _, _, _, _, _, _, did)
                 And Departments(did, dn, _, _)
   
   

3. List the first and last name of each employee who works in the department named "Sales" and earns more than $5000.

   SQL:
   select first_name, last_name
   from hr.Employees E, hr.Departments D
   where E.department_id = D.department_id and E.salary >= 5000
     and D.department_name = 'Sales';
   
   select first_name, last_name
   from hr.Employees E join hr.Departments D
        on E.department_id = D.department_id 
   where E.salary >= 5000
     and D.department_name = 'Sales';
   
   Relational Algebra:
   Pi_{first_name, last_name} ((Sigma_(salary >= max_salary) Employees)
          Join_(department_id) (Sigma_(department_name = 'Sales') Departments))
   
   Datalog:
   Result(fn, ln) ::= Employees(_, fn, ln, _, _, _, _, sal, _, _, did)
                 And Departments(did, 'Sales', _, _) And sal >= 5000
   

4. List the first and last name of each employee who earns the same as or more than the max salary allowed by his/her job title.

   SQL:
   select first_name, last_name
   from hr.Employees E, hr.Jobs J
   where E.job_id = J.job_id and E.salary >= J.max_salary;
   
   Relational Algebra:
   Pi_{first_name, last_name} (Sigma_(salary >= max_salary) (Employees Join Jobs))
   
   Datalog:
   Result(fn, ln) ::= Employees(_, fn, ln, _, _, _, jid, sal, _, _, _)
                 And Jobs(jid, _, _, maxsal) And sal >= maxsal
   

5. For each employee, list his/her name and the name of the employee he/she directly reports to.

   SQL:
   select E1.first_name || ' ' || E1.last_name as emp_name
          E2.first_name || ' ' || E2.last_name as manager_name
   from hr.employees E1, hr.employees E2
   where E1.manager_id = E2.employee_id;
   
   Relational Algebra:
   Pi_{emp_fn, emp_ln, m_fn, m_ln)
   ((Rou_(Emp1(manager_id, emp_fn, emp_ln)(Pi_{manager_id, first_name, last_name}Employees))
   Join_(manager_id = employee_id)
   (Rou_(Emp2(m_fn, m_ln, employee_id)(Pi_{employee_id, first_name, last_name}Employees)))
   
   Datalog:
   Result(efn, eln, mfn, mln) ::= Employees(mid, mfn, mln, _, _, _, _, _, _, _, _)
                             And Employees(_, efn, eln, _, _, _, _, _, _, mid, _)