Na 7 laboratorium zajmiemy się złożonymi zapytaniami w SQL. Wykorzystamy kilka przydatnych funkcji, z których dotychczas nie korzystaliśmy. Tak jak w poprzednich tygodniach pracujemy na bazie employees.
Uwaga do zadań z poprzedniego tygodnia. Poniższa kwerenda da jeden wiersz, ponieważ UNION jest domyślnie sumą zbiorów (bez powtórzeń)
SELECT max(to_date) AS maxData FROM salaries
UNION
SELECT max(to_date) AS maxData FROM titles;
Aby mieć powtarzające się wiersze używamy UNION ALL
SELECT max(to_date) AS maxData FROM salaries
UNION ALL
SELECT max(to_date) AS maxData FROM titles;
Wystarczy, aby wiersze nie były identyczne, aby zadziałał nam zwykły UNION
SELECT max(to_date) AS maxData, 'salaries' FROM salaries
UNION
SELECT max(to_date) AS maxData, 'titles' FROM titles;
Ćwiczenie
Znajdź procent pracowników, którzy wykonują pracę inżynierską i nie wykonują pracy inżynierskiej. Wyniki zaokrąglij do drugiego miejsca po przecinku i wypisz w następującym formacie ‘45.65%’
Podpowiedź: Przypisz do każdego pracownika czy wykonuje on pracę inżynierską czy nie
Ćwiczenie
Znajdź liczbę pracowników aktualnie pracujących, pracujących do 31 grudnia 1990, i pozostałych
Zadanie pożyczone od dr Giniewicza
Spośród pracowników zatrudnionych w momencie zbierania danych, znajdź dziesięć osób, które otrzymały najwyższy awans (podwyżkę) od momentu zatrudnienia. Jeśli kilka osób otrzymało taki sam awans, wybierz pracownika zatrudnionego później (szybciej awansował). Nie używaj tabel tymczasowych.
Rozwiązanie naiwne
SELECT max(salary) - min(salary) as podwyzka, emp_no FROM salaries
GROUP BY emp_no
HAVING max(year(to_date))=9999
ORDER BY podwyzka DESC, min(to_date) DESC
LIMIT 10;
Dlaczego może ono być niepoprawne?
Zadanie pomocnicze
Znajdź liczbę pracowników, którzy obecnie zarabiają mniej niż w momencie zatrudniania
Podpowiedź: mogą przydać się funkcje concat, group_concat, substring_index, min, cast. Uwaga: zapytanie może zająć 30 sekund lub więcej!
SET @liczba=23;
SELECT @liczba;
Zmiennym możemy przypisać wartości pochodzące z zapytań
SELECT @liczba:=COUNT(*) FROM salaries;
Możemy je następnie wykorzystywać w kolejnych zapytaniach
SELECT @minus_one_sigma:=AVG(salary)-STDDEV(salary),@plus_one_sigma:=AVG(salary)+STDDEV(salary) FROM salaries;
SELECT COUNT(*)/@liczba FROM salaries WHERE salary>@minus_one_sigma AND salary<@plus_one_sigma;
Pytanie bonusowe
Co możesz, na tej podstawie, powiedzieć o zgodności pensji z rozkładem normalnym? W jaki sposób, jakim zapytaniem, próbował(a)byś weryfikować taką hipotezę?
Ćwiczenie
Znajdź 95 kwantyl pensji
Naiwne rozwiązanie
Łączymy dwa razy tabele, dla każdej pensji zliczamy liczbę pensji od niej mniejszych. Jaka jest złożoność obliczeniowa takiego rozwiązania?
Poniżej podpowiedzi, do dwóch możliwych sposób rozwiązania tego problemu
Numer wiersza w mysql
MySQL nie ma bezpośredniego wsparcia dla numeru wiersza. Można go natomiast emulować, za pomocą zmiennej (szczegóły).
Limit
Klauzulę LIMIT stosowaliśmy dotychczas do wybieranie pierwszych n wyników zapytania. Daje ono jednak więcej możliwości, o czym można przeczytać tutaj.