+-----------------------------------------------------------+
| Domaci ukol - priklady na procviceni SQL k predmetu it360 |
+-----------------------------------------------------------+

4. select jmeno, fce
	from zam;

5. select nazev
	from oddel;

6. select popis
	from ukoly;

7. select distinct fce
	from zam;

8. select distinct titul
	from zam;

9. select plat, plat*12
	from zam;

10. select plat+6000
	from zam;

11. select plat, plat*1.2
	from zam;

12. 'pan/pani', jmeno, 'pracuje...'
	kdyz chci HNED za text -> 'pan/pani' || jmeno

    select 'Pan', titul, jmeno, 'pracuje v oddeleni', cis_odd
	from zam;

13. select plat/22
	from zam;

14. select (plat+1000)*12
	from zam;
    
    select plat*12+12000
    	from zam; 

15. select plat*12+5000
	from zam;

16. select distinct cis_odd, plat
	from zam;

17. select jmeno, oddel.nazev
	from zam, oddel
	where (zam.cis_odd = oddel.cis_odd);

18. select popis, zam.jmeno, zam.cis_odd
	from ukoly, zam
	where (ukoly.os_cis = zam.os_cis);

19. select oddel.nazev, zam.jmeno
	from oddel, zam
	where (oddel.sef = zam.os_cis);

20. select ukoly.popis, oddel.nazev
	from ukoly, oddel, zam
	where (ukoly.os_cis = zam.os_cis and zam.cis_odd = oddel.cis_odd);

21. select zam.jmeno, ukoly.popis
	from zam, ukoly
	where (zam.os_cis = ukoly.os_cis);

    OUTER JOIN:
    select zam.os_cis, zam.jmeno, ukoly.popis
	from zam, ukoly
	where (zam.os_cis = ukoly.os_cis (+));

22. select oddel.nazev, ukoly.popis
	from zam, ukoly, oddel
	where (ukoly.os_cis = zam.os_cis and zam.cis_odd = oddel.cis_odd);

	(vsechny oddeleni - slozitejsi - vnorene dotazy + outer joiny)
	
23. select ukoly.popis, zam.jmeno, oddel.nazev
	from zam, ukoly, oddel
	where (ukoly.os_cis = zam.os_cis and zam.cis_odd = oddel.cis_odd);

24. select distinct zam.jmeno
	from zam, ukoly
	where (ukoly.os_cis = zam.os_cis);

25. select distinct oddel.nazev
	from zam, ukoly, oddel
	where (ukoly.os_cis = zam.os_cis and zam.cis_odd = oddel.cis_odd);

26. select popis from ukoly where os_cis = 1;

27. select cis_odd, fce from zam where jmeno like 'STRNAD';

28. select jmeno
	from zam
	where (fce like 'BOSS' or fce like 'REDITEL');

29. select os_cis, jmeno
	from zam
	where (cis_odd = 2 or cis_odd = 4);

    select os_cis, jmeno
	from zam
	where (cis_odd in (2, 4));

30. select jmeno
	from zam
	where fce not like 'BOSS';

31. select jmeno
	from zam
	where (cis_odd = 3 and plat > 6000);

32. select jmeno
	from zam
	where (titul like 'ING' and (cis_odd = 2 or cis_odd = 6));

    select jmeno
    	from zam
    	where (titul like 'ING' and (cis_odd in (2, 6)));

33. select os_cis, jmeno
	from zam
	where (plat < 7200 or (fce like 'BOSS' and plat < 9000));

34. select jmeno, fce
	from zam
	where (plat > 9000 and plat < 12000);

35. select jmeno
	from zam
	where (cis_odd = 2 or cis_odd = 3 or cis_odd = 4 or cis_odd = 5 or cis_odd = 6 or cis_odd = 7);

    select jmeno
	from zam
	where (cis_odd in (2, 3, 4, 5, 6, 7));

    select jmeno
	from zam
	where (cis_odd between 2 and 7);


36. select jmeno
	from zam
	where (cis_odd = 2 or cis_odd = 4 or cis_odd = 6 or cis_odd = 8 or cis_odd = 10);

    select jmeno
	from zam
	where (cis_odd in (2, 4, 6, 8, 10));


37. select jmeno
	from zam
	where (fce like 'MATEMATIK' or fce like 'TECHNIK' or fce like 'PROJEKTANT' or fce like 'SEF TECHNIK');

    select jmeno
	from zam
	where (fce in ('MATEMATIK', 'TECHNIK', 'PROJEKTANT', 'SEF TECHNIK'));


38. select jmeno, plat
	from zam
	where (titul is not null);

39. select fce
	from zam
	where (jmeno like '%OVA');

40. select distinct os_cis
	from ukoly
	where (popis like '%PROGRAM%');

41. select jmeno
	from zam
	where (plat*1.1*12 > 90000); 

42. select jmeno
	from zam
	where (cis_odd = (select cis_odd
				from oddel
				where (nazev like 'PROJEKCE')));

     select jmeno
	from zam, oddel
	where (oddel.nazev like 'PROJEKCE' and zam.cis_odd = oddel.cis_odd);


43. select ukoly.popis
	from zam, ukoly
	where (zam.titul is not null and ukoly.os_cis = zam.os_cis);


44. select distinct zam.jmeno
	from zam, oddel, ukoly
	where (oddel.nazev like 'PROGRAMOVANI' and zam.cis_odd = oddel.cis_odd and ukoly.os_cis = zam.os_cis);

45. select zam.jmeno
	from zam, oddel
	where (oddel.nazev like 'PROVOZ POCITACE' and oddel.sef = zam.os_cis);


46. select jmeno
	from zam
	order by jmeno;
	
47. select os_cis, jmeno, titul
	from zam
	order by titul;

48. select cis_odd, plat, jmeno
	from zam
	order by cis_odd asc, plat desc;

49. select jmeno, plat*1.1
	from zam
	where (cis_odd = 3)
	order by plat*1.1 desc;

50. select ukoly.cis_uk, ukoly.popis, zam.jmeno
	from zam, ukoly, oddel
	where (ukoly.os_cis = zam.os_cis and zam.cis_odd = oddel.cis_odd)
	order by oddel.nazev;

51. select max(distinct plat), min(distinct plat)
	from zam;
	
52. select count(distinct os_cis), sum(plat), sum(plat)*12
	from zam;

53. select avg(plat), avg(plat*12)
	from zam;

54. select count(cis_odd)
	from oddel;
	(= z tabulky oddel)

    select count(distinct cis_odd)
	from zam;
	(= z tabulky zam)	

55. select avg(plat)
	from zam
	where (cis_odd = 3);

56. select count(distinct os_cis), sum(plat*1.1*12)
	from zam
	where (cis_odd = 2);


57. select count(os_cis)
	from zam
	where (plat > 12000);

58. select max(plat)
	from zam
	where (cis_odd = 3);

59. select count(os_cis)
	from zam
	where (cis_odd = 2 and titul like 'ING');

60. select count(os_cis)
	from zam
	where (jmeno like 'K%');

61. select count(os_cis), min(plat)
	from zam
	where (fce like 'BOSS');

62. select count(os_cis)
	from zam
	where (titul is not null);

63. select count(os_cis)
	from zam
	where (cis_odd = 3 and fce like 'PROGRAMATOR');

64. select min(plat)
	from zam, (select cis_odd from oddel where nazev like 'PROJEKCE') oddel
	where (zam.cis_odd = oddel.cis_odd);
	
    select min(plat)
	from zam, oddel
	where (oddel.nazev like 'PROJEKCE' and zam.cis_odd = oddel.cis_odd);

65. select count(ukoly.cis_uk)
	from zam, ukoly
	where (zam.jmeno like 'CERVENY' and zam.os_cis = ukoly.os_cis);

66. select cis_odd, count(os_cis)
	from zam
	group by cis_odd;
    (*** nevybere vsechny oddeleni ***)

    select oddel.cis_odd, count(zam.os_cis)
	from zam, oddel
	where (zam.cis_odd (+) = oddel.cis_odd)
	group by oddel.cis_odd;

    select oddel.cis_odd, count(zam.os_cis)
	from zam join oddel on (zam.cis_odd (+) = oddel.cis_odd)
	group by oddel.cis_odd;

    select oddel.cis_odd, count(zam.os_cis)
	from zam right join oddel on (zam.cis_odd = oddel.cis_odd)
	group by oddel.cis_odd;

67. select cis_odd, avg(plat), avg(plat*12)
	from zam
	group by cis_odd;
    (*** nevybere vsechny oddeleni ***)

    select oddel.cis_odd, avg(plat), avg(plat*12)
	from zam, oddel
	where (zam.cis_odd (+) = oddel.cis_odd)
	group by oddel.cis_odd;

68. select cis_odd, count(os_cis)
	from zam
	where (titul like 'ING')
	group by cis_odd;
    (*** nevybere vsechny oddeleni ***)

    select oddel.cis_odd, count(zam.os_cis)
	from zam, oddel
	where (titul (+) like 'ING' and oddel.cis_odd = zam.cis_odd (+))
	group by oddel.cis_odd;

69. select cis_odd, count(os_cis)
	from zam
	where (titul is null)
	group by cis_odd;
    (*** nevybere vsechny oddeleni ***)

    select oddel.cis_odd, count(zam.os_cis)
	from oddel, zam
	where (zam.cis_odd (+) = oddel.cis_odd
		and titul (+) is null)
	group by oddel.cis_odd;

    select oddel.cis_odd, nazev, count(zam.os_cis)
	from oddel, zam
	where (zam.cis_odd (+) = oddel.cis_odd
		and titul (+) is null)
	group by oddel.cis_odd, nazev;

70. select cis_odd, fce, count(os_cis)
	from zam
	group by cis_odd, fce;
    (*** nevybere vsechny oddeleni ***)

    select oddel.cis_odd, fce, count(zam.os_cis)
	from zam, oddel
	where (oddel.cis_odd = zam.cis_odd (+))
	group by oddel.cis_odd, fce;

71. select cis_odd, fce, max(plat)
	from zam
	group by cis_odd, fce;

72. select cis_odd, max(plat)
	from zam
	group by cis_odd
	order by max(plat) desc;
    (*** nevybere vsechny oddeleni ***)

    select oddel.cis_odd, max(plat)
	from zam, oddel
	where (oddel.cis_odd = zam.cis_odd (+))
	group by oddel.cis_odd
	order by max(plat) desc;

73. select fce, avg(plat)
	from zam
	group by fce
	order by avg(plat) desc;

74. select cis_odd, count(os_cis) as pocet
	from zam
	where (plat > 9000)
	group by cis_odd
	order by pocet desc;
    (*** nevybere vsechny oddeleni ***)

    select oddel.cis_odd, count(zam.os_cis) as pocet
	from zam, oddel
	where (plat (+) > 9000 and oddel.cis_odd = zam.cis_odd (+))
	group by oddel.cis_odd
	order by pocet desc;
	
75. select cis_odd, count(os_cis) as pocet
	from zam
	where (titul is not null and plat < 12000)
	group by cis_odd
	order by pocet desc;
    (*** nevybere vsechny oddeleni ***)

    select oddel.cis_odd, count(zam.os_cis) as pocet
	from zam, oddel
	where (titul (+) is not null and plat (+) < 12000 and oddel.cis_odd = zam.cis_odd (+))
	group by oddel.cis_odd
	order by pocet desc;

76. select zam.jmeno, oddel.nazev, nadriz.jmeno
	from zam, oddel, zam nadriz
	where (zam.cis_odd = oddel.cis_odd and oddel.sef = nadriz.os_cis);
	
77. select zam.jmeno, nadriz.jmeno
	from zam, zam nadriz
	where (zam.nadr = nadriz.os_cis);

78. select jmeno
	from zam
	where (plat = (select plat from zam where jmeno like 'DLOUHY'));

79. select zam.jmeno
	from zam, zam nadriz
	where (zam.nadr = nadriz.os_cis and zam.plat > nadriz.plat);

80. select zam.jmeno, nadriz.jmeno, ved.jmeno
	from zam, zam nadriz, zam ved, oddel
	where (zam.nadr = nadriz.os_cis and zam.cis_odd = oddel.cis_odd and oddel.sef = ved.os_cis);

81. select oddel.cis_odd, nazev, avg(plat) as prumer
	from oddel, zam
	where (oddel.cis_odd = zam.cis_odd (+))
	group by oddel.cis_odd, nazev
	order by prumer desc;

82. select oddel.nazev, count(zam.titul) as pocet
	from oddel, zam
	where (oddel.cis_odd = zam.cis_odd (+) and titul (+) is not null and plat (+) < 12000)
	group by oddel.nazev
	order by pocet desc;

83. select oddel.cis_odd, oddel.nazev, zam.fce, avg(plat) as prumer
	from oddel, zam
	where (oddel.cis_odd = zam.cis_odd (+))
	group by oddel.cis_odd, oddel.nazev, zam.fce
	order by oddel.nazev;

84. select zam.jmeno, count(ukoly.cis_uk) as pocet
	from zam, ukoly
	where (zam.os_cis = ukoly.os_cis (+))
	group by ukoly.os_cis, zam.jmeno
	order by zam.jmeno;

85. select oddel.cis_odd, oddel.nazev, zam.fce, count(zam.fce)
	from oddel, zam
	where (oddel.cis_odd = zam.cis_odd (+))
	group by oddel.cis_odd, oddel.nazev, zam.fce;

86. select oddel.nazev, count(ukoly.cis_uk)
	from zam, oddel, ukoly
	where (oddel.cis_odd = zam.cis_odd (+) and ukoly.os_cis (+) = zam.os_cis)
	group by oddel.nazev;

87. select nadr.jmeno, count(zam.os_cis) as pocet
	from zam, zam nadr
	where (zam.nadr (+) = nadr.os_cis)
	group by nadr.jmeno
	order by nadr.jmeno;

88. select oddel.nazev, sef.jmeno, count(zam.os_cis) as pocet
	from oddel, zam, zam sef
	where (oddel.sef = sef.os_cis (+) and oddel.cis_odd = zam.cis_odd (+))
	group by oddel.nazev, sef.jmeno;

89. select oddel.nazev, sef.jmeno as sef, count(zam.os_cis) as pocet
	from oddel, zam sef, zam
	where (oddel.sef = sef.os_cis (+) and oddel.cis_odd = zam.cis_odd (+) and zam.titul (+) like 'ING')
	group by oddel.nazev, sef.jmeno;

90a. select count(zam.os_cis)
	from zam, zam sef
	where (zam.nadr = sef.os_cis and sef.jmeno like 'ZLATUSKA');

90b. select zam.os_cis, zam.jmeno
	from zam, zam sef
	where (zam.nadr = sef.os_cis and sef.jmeno like 'KOCOUR' and zam.titul like 'ING');
	(*** trochu podivne zadani - jsou tam dva KOCOURove ***)

91. select count(zam.os_cis)
	from oddel, zam
	where (oddel.nazev like 'BUFET' and oddel.cis_odd = zam.cis_odd);

92. select cis_odd
	from zam
	group by cis_odd
	having count(os_cis) > 5;

93. select cis_odd, sum(plat)
	from zam
	group by cis_odd
	having sum(plat) > 36000;

94. select fce, avg(plat)
	from zam
	group by fce
	having avg(plat) > 9000;

95. select oddel.nazev, max(zam.plat)
	from zam, oddel
	where (oddel.cis_odd = zam.cis_odd)
	group by oddel.nazev
	having max(zam.plat) < 12000;

96. select zam.jmeno
	from zam, ukoly
	where (ukoly.os_cis = zam.os_cis)
	group by zam.os_cis, zam.jmeno
	having count(ukoly.os_cis) > 1;

97. select fce
	from zam
	group by fce
	having count(zam.os_cis) > 3;

98. select sef.jmeno, sef.fce
	from zam sef, zam
	where (zam.nadr = sef.os_cis)
	group by sef.os_cis, sef.jmeno, sef.fce
	having count(zam.os_cis) > 2;

99. select oddel.nazev
	from zam, oddel
	where (oddel.cis_odd = zam.cis_odd and zam.fce like 'BOSS')
	group by oddel.nazev
	having count(zam.fce) > 1;

100. select max(plat)
	from oddel, zam
	where (oddel.cis_odd = zam.cis_odd and oddel.nazev like 'PROJEKCE');

101. select oddel.nazev
	from zam, oddel
	where (oddel.cis_odd = zam.cis_odd and titul is not null)
	group by oddel.nazev
	having count(zam.titul) > 1;

102. select oddel.nazev
	from zam, oddel
	where (oddel.cis_odd = zam.cis_odd and zam.plat < 9000)
	group by oddel.nazev
	having count(zam.os_cis) > 1;

103. select oddel.nazev
	from zam, oddel
	where (oddel.cis_odd = zam.cis_odd)
	group by oddel.nazev
	having sum(zam.plat*12) > 600000;

104. select fce
	from zam
	where (titul like 'ING')
	group by fce
	having count(fce) > 0;

105. select zam.cis_odd, sef.jmeno
	from zam, zam sef, oddel
	where (oddel.sef = sef.os_cis and oddel.cis_odd = zam.cis_odd and zam.titul like 'ING')
	group by zam.cis_odd, sef.jmeno
	having count(zam.titul) > 0;

106. select fce, avg(plat)
	from zam
	where (titul is not null)
	group by fce
	having avg(plat) < 9900;

107. select distinct zam.fce
	from zam, ukoly
	where (ukoly.os_cis = zam.os_cis)
	group by ukoly.os_cis, zam.fce
	having count(zam.os_cis) > 1;

108. select distinct sef.jmeno
	from zam, oddel, ukoly, zam sef
	where (oddel.sef = sef.os_cis and oddel.cis_odd = zam.cis_odd and ukoly.os_cis = zam.os_cis)
	group by oddel.cis_odd, sef.jmeno
	having count(ukoly.os_cis) > 1;

     select distinct sef.jmeno
	from zam, oddel, ukoly, zam sef
	where (oddel.sef = sef.os_cis and oddel.cis_odd = zam.cis_odd and ukoly.os_cis = zam.os_cis)
	group by ukoly.os_cis, sef.jmeno
	having count(ukoly.os_cis) > 1;
	(*** vraci jmena vedoucich, kteri maji v oddeleni zamestnance, kteri maji pridelen vice jak 1 ukol ***)

109. select sef.jmeno
	from oddel, zam, zam sef
	where (oddel.cis_odd = zam.cis_odd and oddel.sef = sef.os_cis)
	group by oddel.cis_odd, sef.jmeno
	having count(zam.os_cis) > 3;

110. select sef.jmeno as nadrizeny
	from zam, zam sef
	where (zam.nadr = sef.os_cis and zam.titul like 'ING')
	group by sef.jmeno
	having count(zam.titul) > 0;

111. select sef.jmeno as nadrizeny
	from zam, zam sef
	where (zam.nadr = sef.os_cis and sef.titul is null)
	group by sef.jmeno
	having count(zam.os_cis) > 2;

113. select zam.os_cis, zam.jmeno
	from zam, oddel
	where (oddel.cis_odd = zam.cis_odd and oddel.nazev like 'PROGRAMOVANI');

114. select oddel.nazev
	from oddel, zam
	where (oddel.cis_odd = zam.cis_odd and zam.jmeno like 'OBROVSKY');

115. select zam.os_cis, zam.jmeno
	from zam, zam sef
	where (zam.nadr = sef.os_cis and sef.jmeno = 'CECH');

116. select zam.os_cis, zam.jmeno
	from zam, zam osoba
	where (zam.cis_odd = 4 and zam.plat > osoba.plat and osoba.jmeno like 'KADRNOZKOVA');

117. select os_cis, jmeno
	from zam
	where (plat < (select avg(plat) from zam));
	
118. select jmeno
	from zam
	where (plat = (select min(plat) from zam));

119. select oddel.nazev
	from zam, oddel
	where (oddel.cis_odd = zam.cis_odd)
	group by oddel.nazev
	having (count(zam.os_cis) = (select count(zam.os_cis)
					from oddel, zam
					where (oddel.cis_odd = zam.cis_odd and oddel.nazev like 'PROJEKCE')));
	(*** vybere i projekci ***)

	select oddel.nazev
	from zam, oddel
	where (oddel.cis_odd = zam.cis_odd and oddel.nazev not like 'PROJEKCE')
	group by oddel.nazev
	having (count(zam.os_cis) = (select count(zam.os_cis)
					from oddel, zam
					where (oddel.cis_odd = zam.cis_odd and oddel.nazev like 'PROJEKCE')));
	(*** bez projekce ***)	

120. select count(os_cis)
	from zam
	where (plat < (select avg(plat) from zam));

121. select count(os_cis)
	from zam
	where (titul like 'ING' and plat < (select avg(plat) from zam where (titul like 'ING')));

122. select os_cis, jmeno, plat, titul
	from zam, oddel
	where (oddel.cis_odd = zam.cis_odd and (titul is null or oddel.nazev like 'BUFET'));

123. select os_cis, jmeno, plat, titul
	from zam
	where (titul is not null or plat > (select avg(plat) from zam))
	order by os_cis;

124. select oddel.cis_odd, oddel.nazev, count(zam.os_cis)
	from zam, oddel
	where (oddel.cis_odd = zam.cis_odd (+))
	group by oddel.cis_odd, oddel.nazev;

125. select zam.os_cis, jmeno, count(ukoly.os_cis)
	from ukoly, zam
	where (ukoly.os_cis (+) = zam.os_cis)
	group by zam.os_cis, jmeno;

126. select nadr, os_cis, jmeno, level-1
	from zam
	start with nadr = 14
	connect by prior os_cis = nadr
	order by nadr desc;



*****************************************************************************************************
-*** od 26. do 45 se ma pouzivat IN, LIKE, BETWEEN ***-

46-50 -> ORDER BY

51-85 -> agregacni fce (AVG, SUM, MAX, MIN, COUNT...) + GROUP BY

-> 111 kombinace vseho + kombinace vyber. podm s vyb. podm, vztahujicich se k jednotlivym radkum (=having)

92: vypis jen odd, kde poc. zam je vetsi nez 5
select cis_odd
from zam
group by cis_odd
having count(os_cis) > 5;

select cis_odd, count(os_cis)
from zam
where plat > 5000
group by cis_odd
having count(os_cis) > 5;
->nejdriv vybere 1. podm (5000), ty se daj do grupy a na ty grupy se da podminka cetnosti (having)

tj. rozdil mezi where a having je ten, ze where se vyhodnoti pred tim, nez se pouzije group by a having se pak pouzije na ty vytvorene skupiny


****
Vyber cislo a nazev vsech oddeleni ve kterych NEpracuje docent:
select cis_odd, nazev
	from oddel
	where cis_odd not in (select cis_odd
				from zam
				where titul like 'DOC');
****
pri pouzivani (+) plati pravidlo, ze (+) se da na stranu, kde je MENE ***DISTINCT*** HODNOT pri vyhodnoceny vyrazu

pouziva-li se group by, musi byt vsechny sloupce, ktere se vypisuji (za selectem) uvedeny take v group by

***
zjistete cisla a jmena zamestnancu, kteri nemaji prideleny zadny ukol a maji plat vetsi nez vedouci jejich oddeleni

select zam.os_cis, zam.jmeno
	from oddel, ukoly, zam, zam sef
	where (oddel.sef = sef.os_cis and zam.cis_odd = oddel.cis_odd and ukoly.os_cis (+) = zam.os_cis and zam.plat > sef.plat)
	group by ukoly.os_cis, zam.os_cis, zam.jmeno
	having count(ukoly.os_cis) = 0;

select distinct zam.os_cis, zam.jmeno
	from zam, oddel, zam ved
	where (zam.cis_odd = oddel.cis_odd and sef = ved.os_cis and zam.plat > ved.plat and zam.os_cis not in (select distinct os_cis from ukoly));
	(*** ofic. reseni ***)

126.
select nadr, os_cis, jmeno, level-1
	from zam
	start with nadr = 14
	connect by prior os_cis = nadr
	order by nadr desc;


127.
select *
from user_tables;
from user_views;
from all_views;
from all_tables;

-------
vypiste cisla a jmena zamestnancu, kteri maji plat > nez je prumerny plat v jejich oddeleni

reseni:
(*** zavisly subdotaz - ve vnorenem subdotazu se odkazuji na vnejsi tabulku -> zpracovava se tak,
	ze pro kazdy vnejsi radek se provede vnoreny subdotaz a za vnejsi.cis_odd se dosadi AKTUALNI
	hodnota - toho zpracovavaneho radku ***)
select os_cis, jmeno
	from zam vnejsi
	where plat > (select avg(zam.plat)
			from zam
			where zam.cis_odd = vnejsi.cis_odd);

select os_cis, jmeno
	from zam, (select cis_odd, avg(plat) prumplat
			from zam
			group by cis_odd) prum
	where (zam.cis_odd = prum.cis_odd and zam.plat > prum.prumplat);

select os_cis, jmeno
	from zam join (select cis_odd, avg(plat) prumplat
			from zam
			group by cis_odd) prum
		on (zam.cis_odd = prum.cis_odd)
	where zam.plat > prum.prumplat;



*********
kap. 4

1. insert into zam values(90, 'Martin', 'vedouci', 20000, 'ing', 2, 1);
2. update zam set jmeno = 'Honza' where os_cis = 90;
3. delete from zam where os_cis = 90;
4. create or replace view xdammsklad as
	select os_cis, jmeno, plat
	from zam
	where cis_odd = 6;
	(*** to OR REPLACE je tu, aby byl aktualizovatelny - tj. kdyz se do puvodni tab. neco prida/smaze, tak se to projevi i ve view ***)
5. create or replace view xdammpetka as
	select zam.os_cis, jmeno, oddel.nazev, ukoly.popis
	from zam, oddel, ukoly
	where (oddel.cis_odd = zam.cis_odd and ukoly.os_cis (+) = zam.os_cis);

6. - prava:
vytv. view bufet
grant ... bufet studentovi
a student to pouziva sel from student1.bufet -> PLNE jmeno

a) grant select, update
	on xdammsklad
	to student;

b) pod studentem:
   update student1.xdammsklad
	set os_cis = 89
	where os_cis = 90;

7. drop view xdammsklad;

---
kdyz se identifikace sloupcu apod. pise bez uvozovek, Oracle to bere CASE-INSENSITIVE, ale kdyz se to da do uvozovek "", je to CASE SENSITIVE