DB2 | 4

-- <ScriptOptions statementTerminator=";" />
CREATE TABLE PON.OCENY (
    ID_OCENY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    ID_STUDENTA INTEGER NOT NULL,
    OCENA DB2ADMIN.OCENA NOT NULL,
    PRIMARY KEY ( ID_OCENY )
   );

   ALTER TABLE PON.STUDENCI ADD PRIMARY KEY (ID_STUDENTA);
   ALTER TABLE PON.OCENY ADD CONSTRAINT FK_OCENY_STUD FOREIGN KEY (ID_STUDENTA) REFERENCES PON.STUDENCI(ID_STUDENTA);

   ALTER TABLE PON.OCENY ADD CONSTRAINT SPR_OCENE CHECK(CAST(OCENA AS DECIMAL(5,1)) BETWEEN 2 AND 5);
---ALTER TABLE APN.OCENY ADD CONSTRAINT SPR_OCENY CHECK(CAST(OCENA AS DECIMAL(5,1)) IN (2.0, 2.5, 3.0, 3.5, 4.0, 4.5, 5.0));

   INSERT INTO PON.STUDENCI(NAZWISKO) VALUES ('Kowalski');
   INSERT INTO PON.STUDENCI(NAZWISKO) VALUES ('Nowak');
   INSERT INTO PON.STUDENCI(NAZWISKO) VALUES ('Dgbioc');

SELECT * FROM PON.STUDENCI;
UPDATE PON.STUDENCI SET IMIE='Jan';

INSERT INTO PON.OCENY (ID_STUDENTA, OCENA) VALUES(3, 5.0);
INSERT INTO PON.OCENY (ID_STUDENTA, OCENA) VALUES(2, 4.0);
INSERT INTO PON.OCENY (ID_STUDENTA, OCENA) VALUES(1, 3.0);

CREATE VIEW STUD_OCENA AS
SELECT S.NAZWISKO, S.IMIE, AVG(O.OCENA) FROM PON.STUDENCI s INNER JOIN PON.OCENY O
ON O.ID_STUDENTA=s.ID_STUDENTA;






TO samo ale po raz drugi XDDD








-- <ScriptOptions statementTerminator=";" />
ALTER TABLE APN.OCENY ADD CONSTRAINT FK_OCENY_STUD
FOREIGN KEY (ID_STUDENTA) REFERENCES APN.STUDENCI(ID_STUDENTA);

ALTER TABLE APN.OCENY DROP CONSTRAINT SPR_OCENY;
ALTER TABLE APN.OCENY ADD CONSTRAINT SPR_OCENY
CHECK(CAST(OCENA AS DECIMAL(5,1)) IN (2.0, 2.5, 3.0, 3.5, 4.0, 4.5, 5.0));

INSERT INTO APN.STUDENCI(NAZWISKO, IMIE) VALUES ('KOWALSKI', 'JAN');
INSERT INTO APN.STUDENCI(NAZWISKO, IMIE) VALUES ('MARAS', 'SARAM');

INSERT INTO APN.OCENY (ID_STUDENTA, OCENA) VALUES (1, 2.0);
INSERT INTO APN.OCENY (ID_STUDENTA, OCENA) VALUES (1, 4.5);
INSERT INTO APN.OCENY (ID_STUDENTA, OCENA) VALUES (1, 3.5);

INSERT INTO APN.OCENY (ID_STUDENTA, OCENA) VALUES (2, 3.0);
INSERT INTO APN.OCENY (ID_STUDENTA, OCENA) VALUES (2, 5.0);
INSERT INTO APN.OCENY (ID_STUDENTA, OCENA) VALUES (2, 5.0);

SELECT S.NAZWISKO, O.OCENA FROM APN.STUDENCI S INNER JOIN APN.OCENY O
ON S.ID_STUDENTA = O.ID_STUDENTA;

-- ZA POMOCĄ WIDOKU OBLICZYC SREDNIA OCENE DLA STUDENTA
SELECT S.NAZWISKO, AVG(CAST(O.OCENA AS DECIMAL(5,1)))
FROM APN.STUDENCI S
INNER JOIN APN.OCENY O
ON S.ID_STUDENTA = O.ID_STUDENTA
GROUP BY S.NAZWISKO;

CREATE OR REPLACE VIEW SREDNIE AS
SELECT S.NAZWISKO,
    CAST
    (
        ROUND
        (
            AVG
            (
                CAST
                (
                    O.OCENA AS DECIMAL(5,1)
                )
            )*2, 0
        )/2 AS DECIMAL(5,1)
    ) AS SREDNIA
FROM APN.STUDENCI S
INNER JOIN APN.OCENY O
ON S.ID_STUDENTA = O.ID_STUDENTA
GROUP BY S.NAZWISKO;

SELECT * FROM SREDNIE;