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

• Projection
• Selection
• Oracle SQL functions

Queries to work on:

1. list all the information of all employees;

   SQL:
   select *
     from hr.employees;
   
   Relational Algebra (-- a table by itself is an algebra expression):
   Employees 
   
   Datalog (Employees table has 11 columns):
   Result(c1, c2, c3, c4, c5, c6, c7, c8, c9, cA, cB) ::=
         Employees(c1, c2, c3, c4, c5, c6, c7, c8, c9, cA, cB)
   

2. list the full name of each employee;

   SQL:
   select first_name || ' ' || last_name as full_name
     from hr.employees;
   
   Relational Algebra:
   P_{first_name, last_name} (Employees)
   
   Datalog:
   Result(fn, ln) ::= Employees(_, fn, ln, _, _, _, _, _, _, _, _)
   

3. list the personal information of all employees whose last name is 'King';

   SQL:
   select *
     from hr.employees
    where last_name = 'King';
   
   Relational Algebra:
   S_(last_name = 'King') Employees
   
   Datalog:
   Result(c1, c2, c3, c4, c5, c6, c7, c8, c9, cA, cB) ::=
         Employees(c1, c2, c3, c4, c5, c6, c7, c8, c9, cA, cB)
         And c3 = 'King'
   

4. list unique last names of the employees.

   SQL (uses bag/multi-set semantics):
   select distinct last_name
     from hr.employees;
   
   Relational Algebra (because it uses set semantics):
   P_{last_name} (Employees)
   
   Datalog (because it uses set semantics):
   Result(ln) ::= Employees(_, _, ln, _, _, _, _, _, _, _, _)
   

5. list the full name of each employee and the date they started to work for the company using the presentation format "yyyy Month dd";

   SQL:
   select first_name || ' ' || last_name as full_name,
          to_char(hire_date, 'yyyy Month dd') as hire_date
   from hr.employees;
   
   Relational Algebra (we don't care about data presentation in RA):
   P_{first_name, last_name, hire_date} (Employees)
   
   Datalog (we don't care about data presentation in Datalog):
   Result(fn, ln, hd) ::= Employees(_, fn, ln, _, _, hd, _, _, _, _, _)