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

Learning Objectives:

• ER modelling
• Simple Queries
  • Projection
  • Selection
• Oracle SQL functions - manipulating string/date data

Your tasks:

Reverse translate the above relational database schema to its equivalent ER diagram.

Write SQL queries for the following tasks and test run them on Oracle:

1. list all the data of all employees; (In database terminology, this means to retrieve all the data in the table employees.)
2. list the full name of each employee; (hint: use string concatenation in the select clause.)
3. list the personal information of all employees whose last name is 'King';
4. list unique last names of the employees; (Hint: using keyword distinct.)
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";