SHARE
TWEET

[BP] 2. KOLOKVIJ - 16.01.2018. (A)

eniodordan Jan 15th, 2020 (edited) 227 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- A grupa
  2.  
  3. -- 1. zadatak
  4. -- a)
  5. ALTER TABLE Glumac ADD Spol CHAR(1);
  6. -- b)
  7. ALTER TABLE Glumac ADD CONSTRAINT glumac_chk_spol CHECK(Spol IN ('M', 'Z'));
  8. -- c)
  9. ALTER TABLE Filmovi ALTER COLUMN Naslov VARCHAR(100);
  10. -- d)
  11. ALTER TABLE IzdavackaKuca DROP COLUMN Drzava;
  12.  
  13. -- 2. zadatak
  14. -- a)
  15. INSERT INTO Glumac VALUES ('15', 'Enio', 'Dordan');
  16. -- b)
  17. INSERT INTO Filmovi (IDFilma, Naslov, Trajanje) VALUES ('5', 'Parasite', '132');
  18. -- c)
  19. UPDATE IzdavackaKuca SET NazivKuce = 'Twentieth Century Fox' WHERE NazivKuce = '20th Century Fox';
  20. -- d)
  21. DELETE FROM Filmovi WHERE (DatumIzdavanja BETWEEN '01/07/2007' AND '31/12/2007') AND (Trajanje < 120);
  22.  
  23. -- 3. zadatak
  24. -- a)
  25. SELECT COUNT(*) FROM Glumac WHERE Ime LIKE 'P%' AND Prezime LIKE 'C%';
  26. -- b)
  27. SELECT Naslov, DatumIzdavanja, Trajanje FROM Filmovi WHERE Trajanje = (SELECT MIN(Trajanje) FROM Filmovi);
  28. -- c)
  29. SELECT YEAR(DatumIzdavanja), AVG(Trajanje) FROM Filmovi GROUP BY YEAR(DatumIzdavanja) ORDER BY 1 DESC;
  30. --d)
  31. CREATE VIEW FilmInfo AS SELECT Naslov, Trajanje, NazivKuce, SUBSTRING(Drzava, 1, 3) FROM Filmovi, IzdavackaKuca;
  32.  
  33. -- 4. zadatak
  34. CREATE FUNCTION Funkcija (@n INT) RETURNS INT
  35. AS
  36. BEGIN
  37.     DECLARE @rez = INT;
  38.     SET @rez = 0;
  39.     IF @n<0
  40.     BEGIN
  41.         SET @rez = ABS(@n);
  42.     END
  43.     ELSE
  44.     BEGIN
  45.         DECLARE @brojac = 0
  46.         WHILE @brojac < @n
  47.         BEGIN
  48.             IF @n%2=0
  49.             BEGIN
  50.             SET @rez = @rez + @brojac;
  51.             END
  52.         END
  53.     END
  54.     RETURN @rez;
  55. END
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