Witam,

proszę o pomoc lub radę w napisaniu instrukcji SELECT.

1. Znajdź liczbę pracowników zarabiających więcej niż wynosi średnia pensja w ich dziale oraz średnią pensję takich pracowników w każdym dziale.
Ponadto zapytanie ma wyświetlić numer i nazwę działu oraz liczbę pracowników i średnią pensję w każdym dziale, a jego wynik ma być posortowany malejąco wg liczby
pracowników zarabiających więcej niż wynosi średnia pensja w ich dziale.

2. Znajdź liczbę pracowników zarabiających więcej niż wynosi średnia pensja w ich dziale oraz średnią pensję takich pracowników w każdym dziale, w którym minimalna pensja
jest wyższa od minimalnej pensji w dziale nr 20. Ponadto zapytanie ma wyświetlić numer i nazwę działu oraz liczbę pracowników i średnią pensję w każdym dziale, a jego wynik ma być posortowany malejąco według liczby pracowników zarabiających więcej niż wynosi średnia pensja w ich dziale.

Struktura Tabel:

departments
DEPARTMENT_ID : DEPARTMENT_NAME : MANAGER_ID : LOCATION_ID
10 : Administr : 200 : 1700
20 : Buyers : 201 : 1800
... : ... : ... : ...

employees
EMPLOYEE_ID : FIRST_NAME : LAST_NAME : SALARY : DEPARTMENT_ID : DEPARTMENT_NAME
1000 : Mark : Win : 4000 : 20 : Sale
1001 : John : Beck : 3490 : 50 : Marketing
... : ... : ... : ... : ... : ...

Proszę o sugestię, z góry dzięki.

Nie odrabiamy prac domowych. Pokaż co już masz i z czym masz problem.

Poza tym w sieci jest pełno podobnych zadań. Poszukaj a oświeci Cię.

Co już mam:

SELECT e1.last_name, e1.department_id, e1.salary, avg(e.salary), from employees e1, employees e
WHERE e1.department_id=e.department_id GROUP BY e1.last_name, e1.salary, e1.department_id, e.department_id
ORDER BY e1.department_id;

Daje mi to imiona, id departamentu pensje danego pracownika i średnią pensję w jego dziale.

Teraz próbuje przekształcić tego selecta by zliczył last_name i pokazał ilu takich pracowników zarabia więcej niż średnia w jego dziale,
nietety bez powodzenia.

Musisz tam wpleść jeszcze COUNT i HAVING

Musisz użyć podzapytania.
Kolokwium dziś?

Są wakacje więc kolokwium brak, ale te zadania dostałem do przerobienia na sesję poprawkową, nie byłem pilnym uczniem więc siedzę i czytam 2-gi dzień, w końcu na to wpadnę

SELECT count(distinct e1.last_name), e1.department_id, e1.salary, avg(e.salary), from employees e1, employees e
WHERE e1.department_id=e.department_id GROUP BY e1.last_name, e1.salary, e1.department_id, e.department_id
ORDER BY e1.department_id;

Zlicza w taki sposób:

count(distinctel.last_name) : department_id : salary : avg(e.salary)
1 : 90 : 2400 : 1900
1 : 100 : 7700 : 8600
1 : 100 : 7800 : 8600

Więc teraz próba przekształcenia tak, by department_id się nie dublowało za to zliczyło te jedynki ;-)

Grupowanie po danej kolumnie i jednocześnie użycie na niej funkcji agregującej jest nielogiczne.

SELECT e1.last_name, e1.department_id, e1.salary, avg(e.salary), from employees e1, employees e
WHERE e1.department_id=e.department_id GROUP BY e1.last_name, e1.salary, e1.department_id, e.department_id HAVING e1.salary>avg(e.salary)
ORDER BY e1.department_id;

Ok są pracownicy którzy zarabiają więcej niż średnia w ich działach, jak to zliczyć ? Jakieś sugestie ?

DEPARTMENT_ID : SALARY : AVG(SALARY)
80 : 12400 : 9000
80 : 15000 : 9000
90 : 24000 : 19000
100 : 16000 : 8600
100 : 16000 : 8600

Jeszcze raz:
1. Podzapytanie oblicza średnią dla każdego działu.
2. Zapytanie główne wybiera pracownika z danego działu z warunkiem na pensję większą od średniej w tymże dziale.

SELECT department_id, COUNT (department_id), srednia from (
SELECT e1.last_name, e1.department_id, e1.salary, avg(e.salary) as srednia, from employees e1, employees e
WHERE e1.department_id=e.department_id GROUP BY e1.last_name, e1.salary, e1.department_id, e.department_id HAVING e1.salary>avg(e.salary)
ORDER BY e1.department_id)
GROUP BY department_id, srednia;

Takie zapytanie jest chyba poprawne, chodziło Ci o to że jest zbyt skomplikowane ?

Podzapytanie w środku ma liczyć tylko średnią per dział.

Mozesz rownież użyć funkcji analitycznych.
SELECT imie, nazwisko, placa, dzial, avg(placa) over(partition by dzial) from ...

Okej dzięki, 3 zadanie spróbuje wykonać zgodnie z waszymi radami, może nieelegancko ale zadanie 2 gotowe.

SELECT department_id, department_name, pracownicy, count(department_id) as pracow, avg(salary), srednia from (
SELECT e1.department_name, count(e1.last_name) as pracownicy, e1.department_id, e1.salary, avg(e.salary) as srednia FROM employees e1. employees e WHERE e1.department_id=e.department_id GROUP BY e1.last_name, e1.salary, e1.department_name, e1.department_id, e.department_id HAVING e1.salary>avg(e.salary) ORDER BY e1.department_id) GROUP BY department_id, department_name, srednia, pracownicy ORDER BY pracow;

Mile widziane konkretne pomysły na uproszczenie, najlepiej okraszone stosownym kawałkiem kodu

Podzapytanie ma obliczać tylko i wyłącznie średnią na dział. Na jednej tablicy. Czyli wynikiem ma być id_dzial i średnia pensji.

[SQL] pobierz, plaintext 
SELECT department_id, department_name, ast_name, salary, avg(salary) over(partition BY department_id) srednia FROM employee
[SQL] pobierz, plaintext 

I z tego wybierasz co chcesz...

Tak z czystej ciekawości, moje zapytanie pokazuje poprawne wyniki i spełnia wszystkie warunki zadania.
Możliwe, że Twoje rozwiązanie jest pod pewnymi względami lepsze ale prosiłbym o jakieś konkrety ?

W podzapytaniu nie jest potrzebna informacja o pracowniku. To proste zapytanie grupujące i obliczające średnią per dział.
Potem takie podzapytanie spinasz z głównym zapytanie poprzez id_dzial z warunkiem na płaca>średnia_płaca. Masz wszystkich pracowników, którzy zarabiają powyżej średniej w dziale. Jeśli chcesz obliczyć ile jest takich pracowników w danym dziale, to grupujesz per dział i zliczasz id_pracownik.

A macie jakieś pomysły jak poradzić sobie z 3 zadaniem ?


Nikt nic nie poradzi ? Może jakaś sugestia ?

W każdym dziale znajdę pracowników, którzy zarabiają więcej niż wynosi średnia pensja w ich dziale więc dla każdego działu wyświetli mi się co najmniej jedna osoba. Ale jak do tego dodać średnią pensję pracowników w których działach minimalna pensja jest wyższa od tej w dziale 20 ?
Wtedy dla reszty pracowników, którzy nie spełniają warunków wyświetli się w wierszu wartość 0 ?

SELECT department_id, department_name, ilosc_prac, count(department_id) as zarabiajacy_wiecej, avg(salary) as srednia_zar_wiecej, rednia_dzial from (SELECT el.department_name, count(el.last_name) as ilosc_prac, el.department_id, el.salary, avg(e.salary) as srednia_dzial from employees el, employees e where el.department_id=e.department_id group by el.last_name, el.salary, el.department_name, el.department_id, e.department_id having el.salary>avg(e.salary) order by el.department_id) group by department_id, department_name, srednia_dzial, ilosc_prac order by zarabiajacy_wiecej;

Należy wykorzystać CASE ? Może UNION ?