LOCATIONS location id street address postal_code HR DEPARTMENTS department id lo
ID: 3909634 • Letter: L
Question
LOCATIONS location id street address postal_code HR DEPARTMENTS department id location_id JOB HISTORY state province country id employee id start date end date job id department id EMPLOYEES employee id first name COUNTRIES country id country name region id last name phone_number hire date job id salary JOBS job id job title min_salary max salary REGIONS region id manager id Figure 3: HR Schema a) Write a procedure to display the outcome of report given in figure 4. The report represents the employee's full name and Salary. Process the outcome using employee id-102. (13 marks) Sala 20000 e Name Max Luther Figure 4: Report/Outcom e of Procedure b) Wnte a query to display employee last_name, department_name, location id, and city of al employees who does not earn commissions (commission pct) 8 marks) c) Write a query to display the last name, job_id, and salary for all employees whose job is either that of a Sales Representative' or a Stock Clerk, and whose salary is not equal to $2,500, $3,500, or $7,000 (7 marks) d) Write a query to create a report for the human resources department that displays employee last names, department id, and all the employees who work in the same department as a given employee (7 marks) e) Write a query to display the last_ name, job _id, and hire date for the employees whose last name are "Matos' and Taylor (5 marks)Explanation / Answer
If you have any doubts, please give me comment...
-- a)
SELECT CONCAT(first_name, ' ', last_name) AS 'Employee Name', salary AS Salary WHERE employee_id = 102;
--b)
SELECT last_name, department_name, D.location_id, city
FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L
WHERE E.department_id = D.department_id AND D.location_id = L.location_id AND commission_pct = 0;
--c)
SELECT last_name, E.job_id, salary
FROM EMPLOYEE E, JOBS J
WHERE E.job_id = J.job_id AND (J.job_title='Sales Representative' OR J.job_title ='Stock Clerk') AND salary NOT IN(2500, 3500, 7000);
--d)
SELECT E.last_name, E.department_id, E1.last_name
FROM EMPLOYEE E, EMPLOYEE E1
WHERE E.department_id = E1.department_id;
--e)
SELECT last_name, E.job_id, hire_date
FROM EMPLOYEES E
WHERE last_name = 'Matos'
UNION
SELECT last_name, E.job_id, hire_date
FROM EMPLOYEES E
WHERE last_name = 'Taylor';
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.