Guest User

Untitled

a guest
Dec 8th, 2019
89
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2. --1 Zadatak
  3. select b.naziv,m.ime,m.prezime
  4. from sastav_benda s
  5. join bendovi b on s.idBenda = b.idBenda
  6. join muzicari m on m.idMuzicara = s.idMuzicara
  7. where s.datum2 is null
  8.  
  9.  
  10. --2 Zadatak
  11. select p.ime,SUM(k.cena)
  12. from karte k
  13. join posetioci p on p.idPosetioca = k.idPosetioca
  14. group by k.idPosetioca,p.ime
  15.  
  16.  
  17. --3 Zadatak
  18. select k.idFestivala,MAX(k.cena)
  19. from karte k
  20. where k.cena not in (
  21. select MAX(k1.cena)
  22. from karte k1
  23. group by k1.idFestivala
  24. )
  25. group by k.idFestivala
  26.  
  27.  
  28. --4 Zadatak
  29. create view GODINE_SVIRANJA_ZA_MUZICARE as
  30. select *,case
  31. when datum2 is null then DATEDIFF(year,datum1,getdate())
  32. else DATEDIFF(year,datum1,datum2)
  33. end as br_godina
  34. from sastav_benda
  35.  
  36.  
  37. select idMuzicara,idBenda,case
  38. when br_godina < 5 then 'Guster'
  39. when br_godina between 5 and 10 then 'Dzomba'
  40. else 'Stara kajla'
  41. end as Status
  42. from GODINE_SVIRANJA_ZA_MUZICARE
  43.  
  44. --5 Zadatak
  45. create view zarada_i_br as
  46. select l.idFestivala,l.godina,sum(k.cena) as zarada,count(k.idPosetioca) as br_posetilaca
  47. from lineup_festivala l
  48. join karte k on l.idFestivala = k.idFestivala and l.godina = k.godina
  49. where l.dan >= 2
  50. group by l.idFestivala,l.godina
  51.  
  52.  
  53.  
  54. select z.idFestivala,z.godina,(select count(*)+1
  55. from zarada_i_br z1
  56. where z.godina = z1.godina
  57. and z.zarada < z1.zarada
  58. or(z.zarada = z1.zarada and z.br_posetilaca < z1.br_posetilaca)
  59.  
  60. ) as rang
  61. from zarada_i_br z
  62. order by z.godina desc
  63.  
  64. --6 Zadatak
  65. create view tabelica as
  66. select distinct sb1.idBenda,count(*)+1 as br
  67. from sastav_benda sb1 join sastav_benda sb2 on
  68. sb1.idBenda = sb2.idBenda and sb1.idMuzicara <> sb2.idMuzicara
  69. and sb1.datum1 >= sb2.datum1 and (
  70. sb1.datum1 <= sb2.datum2 or sb2.datum2 is null)
  71. group by sb1.idBenda,sb1.datum1
  72.  
  73.  
  74. select idBenda
  75. from tabelica
  76. where br in (select max(br) from tabelica)
  77.  
  78. --7 Zadatak
RAW Paste Data