4.1 Wyświetl minimalne i maksymalne wynagrodzenie w poszczególnych grupach etatowych.
SELECT [etat], Min([placa_pod]) AS placa_min, Max([placa_pod]) AS placa_max
FROM zatrudnienie
GROUP BY [etat];
4.2 Utwórz zapytanie zwracające liczbę pracowników zatrudnionych na poszczególnych etatach.
SELECT [etat], count(*) AS liczba_pracownikow
FROM zatrudnienie
GROUP BY [etat]
ORDER BY count(*) DESC;
4.3 Wyświetl przeciętną, miesięczną płacę oraz przeciętne roczne zarobki każdej grupy etatowej.
SELECT [etat], avg([placa_pod]) AS srednia_mies, avg([placa_pod]*12) AS srednia_rok
FROM zatrudnienie
GROUP BY [etat];
4.4 Policz ilu pracowników posiada tytuł naukowy prof. dr hab. i dr.
SELECT tytul, count(*) AS tytuly
FROM pracownicy
GROUP BY tytul
HAVING tytul in( "prof. dr hab.", "dr");
4.5 Policz ilu pracowników ma przyznaną premię
SELECT IIf(nz([premia]),'przyznana','brak') AS ja, count(*) AS losob
FROM zatrudnienie
GROUP BY IIf(nz([premia]),'przyznana','brak');
4.6 Oblicz jaką kwotę należy przeznaczyć każdego miesiąca na wypłaty dla prorektorów i dziekanów. Uwzględnij również premie
SELECT nazwa
FROM jednostki AS j LEFT JOIN zatrudnienie AS z ON j.id_jedn=z.id_jedn
WHERE z.id_jedn is null;
4.7 Wypisz grupy etatowe, których średnie miesięczne zarobki są mniejsze od 2500 zł.
SELECT zatrudnienie.etat, Avg(zatrudnienie.placa_pod) AS srednie_mies
FROM zatrudnienie
GROUP BY zatrudnienie.etat
HAVING (((Avg(zatrudnienie.placa_pod))<2500));
4.8 Wyszukaj grupy etatowe, w których maksymalne wynagrodzenia zawierają się w przedziale <2300, 3000>.
SELECT [etat], max([placa_pod]) AS max_mies
FROM zatrudnienie
GROUP BY [etat]
HAVING max(placa_pod)<3001 AND max(placa_pod)>2299;
4.9 Znajdź rok w którym zatrudniono więcej niż 1 pracownika
SELECT YEAR(data_zatr) AS rok, count(*) AS liczba_zatr
FROM zatrudnienie
GROUP BY YEAR(data_zatr)
HAVING count(*)>1;
4.10 Utwórz zapytanie zwracające nazwisko, imię, płacę podstawową i premię wszystkich pracowników
SELECT pracownicy.nazwisko, pracownicy.imie, zatrudnienie.placa_pod, zatrudnienie.premia
FROM pracownicy, zatrudnienie
WHERE (((pracownicy.id_prac)=zatrudnienie.id_prac))
ORDER BY pracownicy.nazwisko;
5.1 Utworzyć kwerendę ze sprzężeniem, która wyświetli wszystkie nazwy jednostek nie występujących w tabeli zatrudnienie
SELECT nowa_nazwa.nazwa
FROM jednostki AS nowa_nazwa LEFT JOIN zatrudnienie AS nowa_nazwa2 ON nowa_nazwa.id_jedn=nowa_nazwa2.id_jedn
WHERE (((nowa_nazwa2.id_jedn) Is Null));
5.2 Utworzyć kwerendę ze sprzężeniem, która wyświetli wszystkie nazwiska pracowników nigdzie nie zatrudnionych.
SELECT reference_pracownicy.nazwisko
FROM pracownicy AS reference_pracownicy LEFT JOIN zatrudnienie AS reference_zatrudnienie ON reference_pracownicy.id_prac=reference_zatrudnienie.id_prac
WHERE (((reference_zatrudnienie.id_prac) Is Null));
5.3 Zmodyfikować kwerendę z zad. 2, tak, by w dodatkowej kolumnie pojawił się napis „pracownik nigdzie nie zatrudniony”
SELECT J.nazwa, [dodatkowa] AS [Informacja o zatrudnieniu]
FROM jednostki AS J LEFT JOIN zatrudnienie AS Z ON J.id_jedn=Z.id_jedn
WHERE (((Z.id_jedn) Is Null));
5.4 Utworzyć kwerendę ze sprzężeniem, która zliczy pracowników pracujących w danych jednostkach. Wynik posortować malejąco
SELECT J.nazwa, Count(*) AS ile_ludziow
FROM jednostki AS J INNER JOIN (pracownicy AS P INNER JOIN zatrudnienie ON P.id_prac=zatrudnienie.id_prac) ON J.id_jedn=zatrudnienie.id_jedn
GROUP BY J.nazwa
ORDER BY Count(*) DESC;
5.5 Wyświetlić osoby pracujące w danych jednostkach. Posortować rosnąco
SELECT P.nazwisko, J.nazwa
FROM jednostki AS J INNER JOIN (pracownicy AS P INNER JOIN zatrudnienie ON P.id_prac=zatrudnienie.id_prac) ON J.id_jedn=zatrudnienie.id_jedn
ORDER BY nazwisko;
5.6 Wyświetl listę jednostek, w których nikt nie pracuje
SELECT nazwa
FROM jednostki AS J LEFT JOIN zatrudnienie AS Z ON j.id_jedn=z.id_jedn
WHERE z.id_jedn IS NULL;
5.7 Utworzyć kwerendę, która wypisze nazwy jednostek nie mających jednostek podrzędnych. jednostki.id_jedn_nad > MAX(jednostki.id_jedn);
SELECT jednostki.nazwa
FROM jednostki
WHERE jednostki.id_jedn>6;
5.8 Utworzyć kwerendę, która zliczy liczbę jednostek podrzędnych dla jednostek będących jednostkami nadrzędnymi
SELECT jednostki.id_jedn_nad, Count(*) AS liczba_jednostek_podzednych
FROM jednostki
GROUP BY jednostki.id_jedn_nad
ORDER BY Count(*);
5.9 Utworzyć kwerendę ze sprzężeniem, która obliczy liczbę osób zatrudnionych
SELECT Count(*) AS liczba_pracujacych
FROM jednostki AS J RIGHT JOIN (zatrudnienie AS Z RIGHT JOIN pracownicy ON Z.id_prac=pracownicy.id_prac) ON J.id_jedn=Z.id_jedn;
5.10 Utworzyć kwerendę zliczającą osoby z danymi tytułami naukowymi
SELECT T.tytul, Count(*) AS liczba_osób
FROM tytuly AS T RIGHT JOIN pracownicy AS P ON T.tytul=P.tytul
GROUP BY T.tytul
ORDER BY Count(*);
5.11 Utworzyć kwerendę wypisującą kwotę, jaką trzeba wypłacić osobom zatrudnionym, z danym tytułem naukowym.
SELECT T.tytul AS tytu, Count(*) AS liczba_zatrudnionych, Z.placa_pod
FROM zatrudnienie AS Z RIGHT JOIN (pracownicy AS P RIGHT JOIN tytuly AS T ON P.tytul=T.tytul) ON Z.id_prac=P.id_prac
GROUP BY T.tytul, Z.placa_pod;
6.1 Utworzyć kwerendę wypisującą, jaką kwotę co miesiąc przeznaczają poszczególne jednostki na płacę i premię dla swoich pracowników
SELECT J.nazwa, Sum(Z.placa_pod) AS płaca, Sum(Z.premia) AS Premia
FROM jednostki AS J LEFT JOIN zatrudnienie AS Z ON J.id_jedn=Z.id_jedn
GROUP BY J.nazwa
HAVING (((Sum(Z.placa_pod)) Is Not Null));
6.2 Utworzyć kwerendę wypisującą największą i najmniejszą premię przyznawaną pracownikom w poszczególnych jednostkach
SELECT J.nazwa, Max(Z.premia) AS Najwieksza_Premia, Min(Z.premia) AS Najmniejsza_Premia
FROM jednostki AS J LEFT JOIN zatrudnienie AS Z ON J.id_jedn=Z.id_jedn
GROUP BY J.nazwa
HAVING Sum(Z.premia) Is Not Null;
6.3 kwerendę wypisującą średnią pensję w poszczególnych jednostkach uczelni
SELECT J.nazwa, Avg(Z.placa_pod) AS Srednia_placa
FROM jednostki AS J LEFT JOIN zatrudnienie AS Z ON J.id_jedn = Z.id_jedn
GROUP BY J.nazwa
HAVING (((Avg(Z.placa_pod)) Is Not Null));
6.4 Wypisz różnicę między płacą maksymalną a płacą minimalną dla etatów, na których nie pracują zatrudnieni pracownicy. Wyniki uporządkuj przez sortowanie
SELECT E.etat, ([E.placa_max]-[E.placa_min]) AS roznica
FROM etaty AS E RIGHT JOIN zatrudnienie AS Z ON E.etat<>Z.etat
GROUP BY E.etat, ([E.placa_max]-[E.placa_min])
ORDER BY ([E.placa_max]-[E.placa_min]);
6.5 Utworzyć kwerendę, która zwróci największe i najmniejsze zarobki w każdej jednostce i poda liczbę pracowników w danej jednostce
SELECT J.nazwa, Max(Z.placa_pod) AS Najwieksza_Premia, Min(Z.placa_pod) AS Najmniejsza_Premia, Count(*) AS liczba_zatrudnionych
FROM jednostki AS J LEFT JOIN zatrudnienie AS Z ON J.id_jedn = Z.id_jedn
GROUP BY J.nazwa
HAVING (((Sum(Z.placa_pod)) Is Not Null));
6.6 Wypisz ile i jakie tytuły mają zatrudnieni pracownicy
SELECT T.tytul AS Tytul, Count(*) AS ile_zatrudnionych
FROM (pracownicy AS P LEFT JOIN tytuly AS T ON P.tytul = T.tytul) RIGHT JOIN zatrudnienie AS Z ON P.id_prac = Z.id_prac
GROUP BY T.tytul;
6.7 Znajdź najstarszego i najmłodszego pracownika
SELECT Min(Z.data_zatr) AS Najmlodszy, Max(Z.data_zatr) AS Najstarszy, P.nazwisko
FROM zatrudnienie AS Z LEFT JOIN pracownicy AS P ON Z.id_prac=P.id_prac
GROUP BY P.nazwisko;
6.8 Wypisz, ile osób z jednostek, które zatrudniają pracowników, nie ma przyznanej premii. Sortuj wyniki wg nazwy jednostki
SELECT J.nazwa, Count(*) AS ile_bez_premi
FROM jednostki AS J RIGHT JOIN zatrudnienie AS Z ON J.id_jedn=Z.id_jedn
GROUP BY J.nazwa, Z.premia
HAVING (((Z.premia) Is Null));
6.9 Oblicz, ile osób zostało zatrudnionych między 2001-08-01 a 2001-09-30
SELECT Count(*) AS ile_zatrudniono
FROM zatrudnienie
WHERE (((zatrudnienie.data_zatr)<#9/29/2001# And (zatrudnienie.data_zatr)>#8/1/2001#));
6.10 Wypisz imiona i nazwiska profesorów, których nikt nie zatrudnia
SELECT P.imie, P.nazwisko
FROM pracownicy AS P LEFT JOIN zatrudnienie AS Z ON P.id_prac = Z.id_prac
GROUP BY P.imie, P.nazwisko, P.tytul
HAVING (((P.tytul) Like 'prof*'));
7.1 Utworzyć kwerendę aktualizującą tabelę pracownicy, wpisującą w pole nr_dowodu dane odpowiadające kombinacji liter AA, cyfr 5432234 i id_prac
UPDATE pracownicy SET pracownicy.nr_dowodu = 'AA1234' & pracownicy.id_prac;
7.2 Utworzyć kwerendę aktualizującą pole tytuł naukowy dla pracowników o imionach zaczynających się od liter A i B. Tytuł naukowy po aktualizacji = "prof. zwyczajny" (należy pamiętać o relacjach bazy danych…
UPDATE pracownicy SET tytul = "prof.zwyczajny"
WHERE imie like "A*" OR imie like "B*";
7.3 Utworzyć kwerendę aktualizującą pole data_ur, wstawiając tu dzisiejszą datę, pomniejszoną o datę o 10,5 roku i id_prac*10.
UPDATE pracownicy SET pracownicy.data_ur = Date()-3738-pracownicy.id_prac*10;
7.4 Skopiować tabelę etaty i zapisać ją pod nazwą etaty2. Dla tej tabeli utworzyć kwerendę aktualizującą jej pola, dodającą do nazwy etatu cyfrę 2, a także obniżającą płacę minimalną i maksymalną o 200 złotych
UPDATE etaty2 SET etat = etat+"2", placa_min = placa_min-200, placa_max = placa_max-200;
7.5 Utworzyć kwerendę dołączającą dane z tabeli etaty do etaty2
INSERT INTO etaty2
SELECT etat AS etat, placa_min AS placa_min, placa_max AS placa_max
FROM etaty;
7.6 Utworzyć kwerendę aktualizującą tabelę etaty2: pole etat do postaci "nowy" + nazwa etatu i płaca maksymalna mniejsza o 200 zł dla płacy minimalnej > 1000 zł
UPDATE etaty2 SET etat = "nowy"+etat, placa_max = placa_max-200
WHERE placa_min>1000;
7.7 Zmodyfikować tabelę etaty2 dołączając do niej kolumnę typu tekstowego o nazwie "profil". Dla tak zmodyfikowanej tabeli utworzyć kwerendę aktualizującą, która dla wszystkich etatów typu "nazwa_etatu" (czyli z wyłączeniem "nazwa_etatu2") doda profil = "stażysta" a dla pozostałych "etatowy".
UPDATE etaty2 SET profil = "stażysta"
WHERE etat not LIKE "*2";
7.8 Utworzyć kwerendę wybierającą z tabeli etaty2 tylko te wiersze, które nie występują w tabeli zatrudnienie i płaca maksymalna jest mniejsza od 1500 zł. Następnie korzystając z tej kwerendy utworzyć kwerendę aktualizującą tabelę etaty2, która do nazwy profilu doda tekst "niższy"
SELECT E.etat, E.placa_min, E.placa_max
FROM etaty2 AS E LEFT JOIN zatrudnienie AS Z ON E.etat=Z.etat
WHERE E.placa_max<1500;
UPDATE [SELECT E.etat, E.placa_min, E.placa_max, profil
FROM etaty2 AS E LEFT JOIN zatrudnienie AS Z ON E.etat = Z.etat
WHERE E.placa_max < 1500]. AS [%$##@_Alias] SET profil = profil+" niższy";
7.9 Utworzyć kwerendę usuwającą z tabeli etaty2 wszystkie rekordy, dla których spełniony jest warunek: płaca minimalna < 1800 lub profil ze słowem "niższy".
DELETE *
FROM etaty2
WHERE placa_min<1800 Or profil Like "*niższy";
7.10 Utworzyć kwerendę aktualizującą wszystkie rekordy z tabeli etaty2, dopisując do płacy minimalnej i płacy maksymalnej różnicę między płacą minimalną i maksymalną dla danego etatu. Zmienić profil na "najlepsi " + dzisiejsza data
UPDATE etaty2 SET placa_min = (placa_max-placa_min)+placa_min, placa_max = (placa_max-placa_min)+placa_max, profil = "najlepsi "+[Date()];
8.1 Utwórz kwerendę tworzącą nową tabelę o nazwie Archiwum zatrudnienia. Dodaj do tabeli rekordy z tabeli zatrudnienie, gdzie data zatrudnienia > 2001-08-01 i < 2001-09-30
SELECT zatrudnienie.id_zatr, zatrudnienie.id_prac, zatrudnienie.id_jedn, zatrudnienie.etat, zatrudnienie.placa_pod, zatrudnienie.premia, zatrudnienie.data_zatr, zatrudnienie.data_zwol INTO [Archwium zatrudnienie]
FROM zatrudnienie
WHERE (((zatrudnienie.data_zatr)>#8/1/2001# And (zatrudnienie.data_zatr)<#9/30/2001#));
8.2 W tabeli Archiwum zatrudnienia uzupełnij pole data_zwol dzisiejszą datą. Użyj do tego celu kwerendy aktualizującej
UPDATE [Archiwum zatrudnienie] SET data_zwol = date();
8.3 Korzystając z widoku projektu stwórz kwerendę krzyżową, która wyświetli liczbę osób zatrudnionych na poszczególnych stanowiskach. (Należy skorzystać z tabeli Archiwum zatrudnienia i pracownicy)
TRANSFORM Count(*) AS Wyr1
SELECT pracownicy.tytul
FROM [Archiwum zatrudnienie] INNER JOIN pracownicy ON [Archiwum zatrudnienie].id_prac = pracownicy.id_prac
GROUP BY pracownicy.tytul
PIVOT [Archiwum zatrudnienie].etat;