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.
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.%'
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, _, _)
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
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
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, _)