Premier League database issue

[JSwaT693]

If i wanted to count average salary of PL players that i have in database how should I do it?

[SkipperToKozak]

Okay so the problem you mentioned can be solved by those soultions:

Number 1

--zad2

--DECLARE min_wyplata NUMBER; max_wyplata NUMBER; avg_wyplata NUMBER;

--

--BEGIN

--

-- SELECT MIN(salary),MAX(salary)

--

-- INTO min_wyplata, max_wyplata

--

-- FROM employees;

--

-- avg_wyplata := (min_wyplata + max_wyplata)/2;

--

-- dbms_output.put_line('Minimalna wyplata to: ' || min_wyplata);

--

-- dbms_output.put_line('Maksymalnawyplata to: ' || max_wyplata);

--

-- dbms_output.put_line('Ich srednia arytmetyczna to: ' || avg_wyplata);

--

--END;

And Number 2:

--zad3

--DECLARE min_wyplata NUMBER; max_wyplata NUMBER; harm_wyplata NUMBER;

--

--

--

-- FUNCTION srednia_harmoniczna(a NUMBER, b NUMBER) RETURN NUMBER IS

--

-- BEGIN

--

-- RETURN FLOOR(2/((1/a)+(1/b)));

--

-- END srednia_harmoniczna;

--

--

--

--BEGIN

--

-- SELECT MIN(salary), MAX(salary)

--

-- INTO min_wyplata, max_wyplata

--

-- FROM employees;

--

--

--

-- harm_wyplata := srednia_harmoniczna(min_wyplata, max_wyplata);

--

-- dbms_output.put_line('Minimalna wyplata to: ' || min_wyplata);

--

-- dbms_output.put_line('Maksymalna wyplata to: ' || max_wyplata);

--

-- dbms_output.put_line('Ich srednia harmoniczna to: ' || harm_wyplata);

--

--END;

[JSwaT693]

Okay thanks that's helpful. But what if i wanted to count the average of only the London teams how to do it?

[SkipperToKozak]

Remember you need to uncomment those functions to properly check whether they are working.

I FOUND OUT WHAT MIGHT BE THE PROBLEM!!!

Check it out here:

First try (example 4)

--DECLARE min_wyplata NUMBER; max_wyplata NUMBER; harm_wyplata NUMBER; department_id1 NUMBER; department_name VARCHAR2(50);

--

--BEGIN

--

-- department_id1 := &enter_department_id; -- wprowadź ID działu

--

-- SELECT department_name

--

-- INTO department_name

--

-- FROM departments

--

-- WHERE department_id = department_id1 AND ROWNUM = 1;

--

--

--

-- SELECT MIN(salary), MAX(salary)

--

-- INTO min_wyplata, max_wyplata

--

-- FROM employees

--

-- WHERE department_id = department_id1;

--

--

--

-- harm_wyplata := floor(2/((1/min_wyplata)+(1/max_wyplata)));

--

--

--

-- dbms_output.put_line('Wybrany dzial to: ' || department_name);

--

-- dbms_output.put_line('Minimalna wyplata to: ' || min_wyplata);

--

-- dbms_output.put_line('Maksymalna wyplata to: ' || max_wyplata);

--

-- dbms_output.put_line('Ich średnia harmoniczna to: ' || harm_wyplata);

--

--END;

Second try (example 5)

--DECLARE min_wyplata NUMBER; max_wyplata NUMBER; harm_wyplata NUMBER; department_id1 NUMBER; department_name VARCHAR2(50);

--

--BEGIN

--BEGIN

--

-- department_id1 := &enter_department_id; -- wprowadź ID działu

--

--

-- SELECT department_name

--

-- INTO department_name

--

-- FROM departments

--

-- WHERE department_id = department_id1 AND ROWNUM = 1;

--

-- EXCEPTION

-- WHEN NO_DATA_FOUND THEN dbms_output.put_line('Nie ma takiego dzialu!');

-- END;

--

-- SELECT MIN(salary), MAX(salary)

--

-- INTO min_wyplata, max_wyplata

--

-- FROM employees

--

-- WHERE department_id = department_id1;

--

--

--

-- harm_wyplata := floor(2/((1/min_wyplata)+(1/max_wyplata)));

--

--

--

-- dbms_output.put_line('Wybrany dzial to: ' || department_name);

--

-- dbms_output.put_line('Minimalna wyplata to: ' || min_wyplata);

--

-- dbms_output.put_line('Maksymalna wyplata to: ' || max_wyplata);

--

-- dbms_output.put_line('Ich średnia harmoniczna to: ' || harm_wyplata);

--

--END;

[SkipperToKozak]

Cursos might be found in the tutorial