Question

A major multi-national corporation as employed you to research and implement SQL statements to support their data management operations. The company consists of several departments located in the cities all over the world. The company's database also contains information about the present and past jobs of its employees and about the present managerial structure. A conceptual schema of the database is given below. REGION region-name ID Belongs-to 1.. COUNTRY street-address country-name ID postal-code city Is-in 0..1 LOCATION state-province [0..1] Involved-in Belonged-to JOBHISTORY end-date Is-located-at A 0..1 Has-past 1 1 start-date 1 ID JOB job-title min-salary max-salary * EMPLOYEE DEPARTMENT Has-current Belongs-to employee_id ID 0..1 department-name ID first-name last-name 0..1 email [0..1] Has-manager phone-number [0..1] 0..1 hire-date 0..1 salary [ 0..1] commission-pct [0..1 Has-supervisor

Please refer to the following business scenarios:

(i) Using SELECT statements of SQL, find the employee id, first name, last name, job title and email of all employees working in Asia (i.e. all countries coming from the region
    ‘Asia’). This query must be implemented as a nested query!

(ii) Using SELECT statements of SQL, find the employee id, first name, last name, job title and supervisor id of employees who had worked for more than 3 years and completed their jobs in the information technology department.

(iii) Using SELECT statements of SQL, find the employee id, first name, last name, hire-date, salary of supervisors, and the total number of employees directly supervised by each one
     of them. Display only those records of supervisors who supervised 8 or more employees.

Answer 1

1)SELECT employee_id,first_name,last_name,job_title,email,

(SELECT job_title FROM job

WHERE job.id=employee.job_id as job_title) FROM employee

WHERE(employee e

JOIN department d

ON e.department_id=d.department_id

JOIN location 1

ON d.location_id=1.location_id)

AND

WHERE country_name IN

(SELECT region_name

FROM region TABLE

WHERE region_name='ASIA');

2) SELECT employee_id,first_name,last_name,job_title,supervisor_id

FROM employees

WHERE EXTRACT((YEAR

FROM age(CURRENT_DATE,HIRE_DATE))>3 and department_id=IT);

3) SELECT employee_id,first_name,last_name,hire_date,

count(employee.supervisor_id)employee_count

FROM employee

WHERE(SELECT supervisor.salary,count(*) FROM supervisor WHERE having count>7);