-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathZ5 SQL.sql
More file actions
145 lines (116 loc) · 5 KB
/
Z5 SQL.sql
File metadata and controls
145 lines (116 loc) · 5 KB
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
/*Z5 Karol Sekściński GR4 319093 */
/*
Z5.1 - Pokazać miasta wraz ze średnią aktualna
pensją w nich z firm tam się mieszczących
Używając UNION, rozważyć opcję ALL
jak nie ma etatów to 0 pokazujemy
(czyli musimy obsłużyć miasta bez etatów AKT firm)
*/
SELECT m.id_miasta, m.nazwa, X.suma_zarobkow AS [srednia zarobkow]
FROM MIASTA m
join (SELECT mW.id_miasta, AVG(ew.pensja) AS suma_zarobkow
FROM etaty eW
join FIRMY f ON (eW.id_firmy = f.nazwa_skr)
join MIASTA mW ON (mW.id_miasta = f.id_miasta)
WHERE eW.DO IS NULL
GROUP BY mW.id_miasta
) X ON (X.id_miasta = m.id_miasta)
UNION ALL
SELECT m.id_miasta, m.nazwa, CONVERT(money, 0) AS XX
FROM MIASTA m
WHERE NOT EXISTS (SELECT 1 FROM etaty eW
join FIRMY f ON (eW.id_firmy = f.nazwa_skr)
join MIASTA mW ON (mW.id_miasta = f.id_miasta)
WHERE mW.id_miasta = m.id_miasta AND eW.do is null)
ORDER BY 1, 2
/*
id_miasta nazwa srednia zarobkow
----------- -------------------------------------------------- ---------------------
1 WARSZAWA 6000,00
2 PLOCK 0,00
3 RADOM 9500,00
4 SIERPC 7416,6666
5 OSTROLEKA 7500,00
6 WYSZKOW 3600,00
7 BIALYSTOK 4250,00
8 SUWALKI 0,00
9 SOKOLKA 5500,00
10 KRYNKI 10000,00
11 SUPRASL 0,00
12 BIALOWIEZA 0,00
(12 row(s) affected)
*/
/*
Z5.2 - to samo co w Z5.1
Ale z wykorzystaniem LEFT OUTER
pokazuje kazdy osobe oddzielnie
musi chyba pokazywac kazda osobe oddzielnie
*/
SELECT m.id_miasta, m.nazwa, ISNULL(X.suma_zarobkow, 0) AS [srednia zarobkow]
FROM MIASTA m
left outer
join (SELECT mW.id_miasta, AVG(ew.pensja) AS suma_zarobkow
FROM etaty eW
join FIRMY f ON (eW.id_firmy = f.nazwa_skr)
join MIASTA mW ON (mW.id_miasta = f.id_miasta)
WHERE EW.DO IS NULL
GROUP BY mW.id_miasta
) X ON (X.id_miasta = m.id_miasta)
/*
id_miasta nazwa srednia zarobkow
----------- -------------------------------------------------- ---------------------
1 WARSZAWA 6000,00
2 PLOCK 0,00
3 RADOM 9500,00
4 SIERPC 7416,6666
5 OSTROLEKA 7500,00
6 WYSZKOW 3600,00
7 BIALYSTOK 4250,00
8 SUWALKI 0,00
9 SOKOLKA 5500,00
10 KRYNKI 10000,00
11 SUPRASL 0,00
12 BIALOWIEZA 0,00
(12 row(s) affected)
*/
/*
Z5.3 Napisać procedurę pokazującą średnią pensję w
osób z miasta - parametr procedure @id_miasta
*/
GO
CREATE PROCEDURE dbo.P2 (@id_miasta int )
AS
SELECT m.id_miasta, m.nazwa, ISNULL(X.suma_zarobkow, 0) AS [srednia zarobkow]
FROM MIASTA m
left outer
join (SELECT mW.id_miasta, AVG(ew.pensja) AS suma_zarobkow
FROM etaty eW
join FIRMY f ON (eW.id_firmy = f.nazwa_skr)
join MIASTA mW ON (mW.id_miasta = f.id_miasta)
WHERE EW.DO IS NULL
GROUP BY mW.id_miasta
) X ON (X.id_miasta = m.id_miasta)
WHERE m.id_miasta = @id_miasta
ORDER BY m.id_miasta, m.nazwa
GO
EXEC P2 @id_miasta = 3
/*
Command(s) completed successfully.
*/
/*
EXEC P2 @id_miasta = 1
id_miasta nazwa srednia zarobkow
----------- -------------------------------------------------- ---------------------
1 WARSZAWA 6000,00
(1 row(s) affected)
EXEC P2 @id_miasta = 2
id_miasta nazwa srednia zarobkow
----------- -------------------------------------------------- ---------------------
2 PLOCK 0,00
(1 row(s) affected)
EXEC P2 @id_miasta = 3
id_miasta nazwa srednia zarobkow
----------- -------------------------------------------------- ---------------------
3 RADOM 9500,00
(1 row(s) affected)
*/