SHARE
TWEET

Untitled

a guest Dec 8th, 2019 79 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
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top