sql-oracle/group_function.sql at master · qaletka/sql-oracle · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
/* Język SQL. Rozdział 4. Funkcje grupowe – zadania  */

-- 1.
SELECT
    MIN(placa_pod) AS minimum,
    MAX(placa_pod) AS maksimum,
    MAX(placa_pod) - MIN(placa_pod) AS różnica
FROM
    pracownicy;

-- 2.
SELECT
    etat,
    AVG(placa_pod) AS srednia
FROM
    pracownicy
GROUP BY
    etat
ORDER BY
    srednia DESC;

-- 3.
SELECT
    COUNT(*) AS profesorowie
FROM
    pracownicy
WHERE
    etat = 'PROFESOR';

-- 4.
SELECT
    id_zesp,
    SUM(placa_pod + nvl(placa_dod, 0)) AS sumaryczne_place
FROM
    pracownicy
GROUP BY
    id_zesp
ORDER BY
    id_zesp;

-- 5.
SELECT
    MAX(SUM(placa_pod + nvl(placa_dod, 0))) AS max_sum_plac
FROM
    pracownicy
GROUP BY
    id_zesp;

-- 6.
SELECT
    id_szefa AS id_szefa,
    MIN(placa_pod) AS minimalna
FROM
    pracownicy
WHERE
    id_szefa IS NOT NULL
GROUP BY
    id_szefa
ORDER BY
    minimalna DESC;

-- 7.
SELECT
    id_zesp,
    COUNT(*) AS ilu_pracuje
FROM
    pracownicy
GROUP BY
    id_zesp
ORDER BY
    ilu_pracuje DESC;

-- 8.
SELECT
    id_zesp,
    COUNT(*) AS ilu_pracuje
FROM
    pracownicy
GROUP BY
    id_zesp
HAVING
    COUNT(*) > 3
ORDER BY
    ilu_pracuje DESC;

-- 9.
SELECT
    id_prac
FROM
    pracownicy
WHERE
    id_prac NOT IN (
        SELECT
            id_prac
        FROM
            pracownicy
    );

-- 10.
SELECT
    etat,
    AVG(placa_pod) AS srednia,
    COUNT(*) AS liczba
FROM
    pracownicy
WHERE
    zatrudniony > DATE '1990-01-01'
GROUP BY
    etat;

-- 11.
SELECT
    id_zesp,
    etat,
    round(AVG(placa_pod + nvl(placa_dod, 0))) AS srednia,
    round(MAX(placa_pod + nvl(placa_dod, 0))) AS maksymalna
FROM
    pracownicy
WHERE
    etat IN (
        'PROFESOR',
        'ASYSTENT'
    )
GROUP BY
    id_zesp,
    etat
ORDER BY
    id_zesp,
    etat;

-- 12.
SELECT
    EXTRACT(YEAR FROM zatrudniony) AS rok,
    COUNT(*) AS ilu_pracownikow
FROM
    pracownicy
GROUP BY
    EXTRACT(YEAR FROM zatrudniony)
ORDER BY
    rok;

-- 13.
SELECT
    length(nazwisko) AS "Ile liter",
    COUNT(*) AS "W ilu nazwiskach"
FROM
    pracownicy
GROUP BY
    length(nazwisko)
ORDER BY
    "Ile liter";

-- 14.
SELECT
    SUM(sign(instr(nazwisko, 'A') + instr(nazwisko, 'a'))) AS "Ile nazwisk z A"
FROM
    pracownicy;

-- 15.
SELECT
    SUM(sign(instr(nazwisko, 'A') + instr(nazwisko, 'a'))) AS "Ile nazwisk z A",
    SUM(sign(instr(nazwisko, 'E') + instr(nazwisko, 'e'))) AS "Ile nazwisk z E"
FROM
    pracownicy;