[LV5] Baze podataka

//1. zadatak
CREATE TABLE korisnik (
    ime VARCHAR(20),
    naopako_ime VARCHAR(20)
);

SELECT *FROM korisnik;

INSERT INTO korisnik (ime, naopako_ime) VALUES ('Dominik', NULL);

CREATE FUNCTION funkcija_rev(@ime VARCHAR(20))
RETURNS VARCHAR(20)
BEGIN
    DECLARE @naopako VARCHAR(20);
    SET @naopako = REVERSE(@ime);
    RETURN @naopako;
END

SELECT ime, dbo.funkcija_rev(ime) AS "Naopako ime" FROM korisnik;

CREATE PROCEDURE procedura_rev(@ime VARCHAR(20))
AS
BEGIN
    DECLARE @naopako VARCHAR(20);
    SET @naopako = REVERSE(@ime);
    PRINT @naopako;
END

EXEC procedura_rev "Ivan";

CREATE TRIGGER okidac_rev ON korisnik
INSTEAD OF INSERT
AS
BEGIN
    DECLARE @ime_kor VARCHAR(20);
    SELECT @ime_kor = ime FROM inserted;
    INSERT INTO korisnik VALUES(@ime_kor, REVERSE(@ime_kor));
END

DROP TRIGGER okidac_rev

INSERT INTO korisnik (ime, naopako_ime) VALUES ('Dominik', NULL);

//2 zadatak
ALTER TABLE korisnik ADD datum SMALLDATETIME;

CREATE PROCEDURE procedura_horoskop(@datum SMALLDATETIME)
AS
BEGIN
    DECLARE @mjesec SMALLDATETIME, @dan SMALLDATETIME;
    SET @mjesec = MONTH(@datum);
    SET @dan = DAY(@datum);
    IF(@mjesec = 3 AND @dan >= 21) OR (@mjesec = 4 AND @dan <= 20)
    BEGIN
        PRINT 'OVAN';
    END
    IF(@mjesec = 4 AND @dan >= 20) OR (@mjesec = 5 AND @dan <= 21)
    BEGIN
        PRINT 'BIK';
    END
    IF(@mjesec = 5 AND @dan >= 22) OR (@mjesec = 6 AND @dan <= 21)
    BEGIN
        PRINT 'BLIZANCI';
    END
    IF(@mjesec = 6 AND @dan >= 22) OR (@mjesec = 7 AND @dan <= 23)
    BEGIN
        PRINT 'RAK';
    END
    IF(@mjesec = 7 AND @dan >= 24) OR (@mjesec = 8 AND @dan <= 22)
    BEGIN
        PRINT 'LAV';
    END
    IF(@mjesec = 8 AND @dan >= 23) OR (@mjesec = 9 AND @dan <= 22)
    BEGIN
        PRINT 'DJEVICA';
    END
    IF(@mjesec = 9 AND @dan >= 23) OR (@mjesec = 10 AND @dan <= 23)
    BEGIN
        PRINT 'VAGA';
    END
    IF(@mjesec = 10 AND @dan >= 24) OR (@mjesec = 11 AND @dan <= 21)
    BEGIN
        PRINT 'ŠKORPION';
    END
    IF(@mjesec = 11 AND @dan >= 22) OR (@mjesec = 12 AND @dan <= 21)
    BEGIN
        PRINT 'STRIJELAC';
    END
    IF(@mjesec = 12 AND @dan >= 22) OR (@mjesec = 1 AND @dan <= 20)
    BEGIN
        PRINT 'JARAC';
    END
    IF(@mjesec = 1 AND @dan >= 21) OR (@mjesec = 2 AND @dan <= 19)
    BEGIN
        PRINT 'VODENJAK';
    END
    IF(@mjesec = 2 AND @dan >= 20) OR (@mjesec = 3 AND @dan <= 20)
    BEGIN
        PRINT 'RIBE';
    END
END