001 seconds Next again using the job ID given select the average salary for all

001 seconds next again using the job id given select

This preview shows page 7 - 13 out of 17 pages.

0.01 seconds Next, again using the job ID given, select the average salary for all employees with that job id (use the ROUND function to round this average to 2 decimal places)…. SELECT job_id, ROUND(AVG(salary), 2)
Image of page 7
FROM employees join jobs using (job_id) WHERE job_id = 'ST_CLERK' GROUP BY job_id; JOB_ID ROUND(AVG(SALARY),2) ST_CLERK 2785 1 rows returned in 0.00 seconds Dow DECLARE v_jobid employees.job_id%TYPE; V_rasal employees.salary%TYPE; BEGIN SELECT job_id, ROUND(AVG(salary), 2) into v_jobid, v_rasal FROM employees WHERE job_id = 'ST_CLERK' GROUP BY job_id; DBMS_OUTPUT.PUT_LINE(v_rasal); END; 2785 Statement processed.
Image of page 8
0.01 seconds I was expecting one and I got one. If the salary for the requested employee is less than the average for their job ID, display a message, indicating it’s less than the average, otherwise….. DECLARE v_jobid employees.job_id%TYPE; v_empid employees.employee_id%TYPE; v_empsal employees.salary%TYPE; V_rasal employees.salary%TYPE; BEGIN SELECT job_id, employee_id, salary, ROUND(AVG(salary), 2) into v_jobid, v_empid, v_empsal, v_rasal FROM employees WHERE job_id = 'ST_CLERK' AND employee_id = 125 GROUP BY job_id, employee_id, salary; if v_rasal > v_empsal then DBMS_OUTPUT.PUT_LINE('Employee Nayer has a salary of 3200 which is less than the Stock Clerk average of 2785');
Image of page 9
elsif v_rasal < v_empsal then DBMS_OUTPUT.PUT_LINE('Employee Nayer has a salary of 3200 which is equal to the Stock Clerk average of 2785'); else DBMS_OUTPUT.PUT_LINE('Employee Nayer has a salary of 3200 which is more than the Stock Clerk average of 2785'); END IF; END; Employee Nayer has a salary of 3200 which is more than the Stock Clerk average of 2785 Statement processed. 0.01 seconds Now, change the value of your variable to ST_MAN and re-run your code. Try SH_CLERK DECLARE v_jobid employees.job_id%TYPE; v_empid employees.employee_id%TYPE; v_empsal employees.salary%TYPE; V_rasal employees.salary%TYPE;
Image of page 10
BEGIN SELECT job_id, employee_id, salary, ROUND(AVG(salary), 2) into v_jobid, v_empid, v_empsal, v_rasal FROM employees WHERE job_id = 'ST_MAN' AND employee_id = 120 GROUP BY job_id, employee_id, salary; if v_rasal > v_empsal then DBMS_OUTPUT.PUT_LINE('Employee Weiss has a salary of 8000 which is less than the Stock Clerk average of 2785'); elsif v_rasal = v_empsal then DBMS_OUTPUT.PUT_LINE('Employee Weiss has a salary of 8000 which is equal to the Stock Clerk average of 2785'); else DBMS_OUTPUT.PUT_LINE('Employee Weiss has a salary of 8000 which is more than the Stock Clerk average of 2785'); END IF; END; Employee Weiss has a salary of 8000 which is more than the Stock Clerk average of 2785 Statement processed.
Image of page 11
0.01 seconds DECLARE v_jobid employees.job_id%TYPE; v_empid employees.employee_id%TYPE; V_rasal NUMBER(8,2); v_empsal employees.salary%TYPE; BEGIN SELECT salary into v_empsal FROM employees
Image of page 12
Image of page 13

You've reached the end of your free preview.

Want to read all 17 pages?

  • Spring '16
  • Mary Gable
  • Salary, 3rd millennium, Shipping Clerk, stock clerk

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture