JLHR93JA

1set search_path = seminar_4, public;
2create schema seminar_4;
3
4create table if not exists department (
5    department_nm varchar(127),
6    location_txt varchar(127)
7);
8
9INSERT INTO seminar_4.department VALUES('HR', 'Murom');
10INSERT INTO seminar_4.department VALUES('SUPPORT', 'Saratov');
11INSERT INTO seminar_4.department VALUES('MANAGEMENT', 'Samara');
12INSERT INTO seminar_4.department VALUES('HEAD', 'Moscow');
13INSERT INTO seminar_4.department VALUES('SALE', 'Moscow');
14INSERT INTO seminar_4.department VALUES('R&D', 'Novosibirsk');
15
16select *
17from department;
18--5
19select
20    s.employee_nm
21from
22    seminar_3.salary s
23inner join
24        seminar_4.department d
25            on s.department_nm = d.department_nm
26            and d.location_txt = 'Moscow';
27--6
28select
29    s.employee_nm
30from
31    seminar_3.salary s
32left join
33    seminar_4.department d
34        on s.department_nm = d.department_nm
35where
36    d.location_txt is null;
37
38--7
39select
40    location_txt,
41    max(salary)
42from
43    seminar_3.salary s
44inner join
45    seminar_4.department d
46        on s.department_nm = d.department_nm
47group by
48    d.location_txt;
49
50--8
51select
52    d.location_txt,
53    count(*)
54from
55    seminar_3.salary s
56inner join
57    seminar_4.department d
58        on s.department_nm = d.department_nm
59group by
60    d.location_txt;
61
62--9
63select
64    location_txt,
65    min(salary)
66from
67    seminar_3.salary s
68inner join
69    seminar_4.department d
70        on s.department_nm = d.department_nm
71group by
72    d.location_txt
73having
74    min(s.salary) > 500;
75
76--10
77select
78    s.employee_nm,
79    d.location_txt,
80    s.salary
81from
82    seminar_3.salary s
83left join
84    seminar_4.department d
85        on s.department_nm = d.department_nm
86order by
87    d.location_txt asc,
88    s.salary asc;
89
90--12
91select
92    location_txt,
93    avg(salary)
94from
95    seminar_3.salary s
96inner join
97    seminar_4.department d
98        on s.department_nm = d.department_nm
99group by
100    1
101order by
102    2 desc;
103
104--13
105select
106    location_txt,
107    count(*) as employee_cnt,
108    max(s.salary) as max_salary,
109    min(s.salary) as min_salary,
110    avg(s.salary) as avg_salary
111from
112    seminar_3.salary as s
113inner join
114    seminar_4.department as d
115        on s.department_nm = d.department_nm
116group by
117    d.location_txt;
118
119--13
120select
121    location_txt,
122    count(*)
123from
124    seminar_3.salary s
125inner join
126    seminar_4.department d
127        on s.department_nm = d.department_nm
128group by
129    d.location_txt
130having
131    avg(s.salary) > 100;
132
133--14
134--Если в таблицу department записать отдел, которого нет в salary, то при джоине ему, очевидно, ничего не будет
135--соответствовать, т.е. при inner для него не будет выбрано ни одной строки, а при outer - значения NULL
136
137--15
138select employee_nm, s.department_nm as department, location_txt, sum(s.salary)
139from seminar_3.salary s
140left join seminar_4.department d
141    on s.department_nm = d.department_nm
142group by employee_nm, department, location_txt
143
144--16
145--CROSS: N*M
146--INNER: min - 0, max - M*N
147--LEFT: min - M, max = M*N
148--FULL: min = max(M, N), max = M*N
149
150
151--17
152--CROSS
153select *
154from seminar_3.salary s
155inner join seminar_4.department d
156    on true;
157
158--FULL
159select *
160from seminar_3.salary s
161inner join seminar_4.department d
162    on (s.department_nm = d.department_nm) or (d.location_txt is null) or (s.salary is null);