feb2019

CREATE TABLE student_generacije(
id_smera INTEGER,
godina INTEGER,
indeks INTEGER NOT NULL,
prosek DOUBLE,
duzina_studiranja INTEGER,
PRIMARY KEY(indeks));

ALTER TABLE student_generacije
ADD CONSTRAINT haha
FOREIGN KEY(indeks)
REFERENCES dosije(indeks);

ALTER TABLE student_generacije
ADD CONSTRAINT hah
CHECK(prosek>8.5);

INSERT INTO student_generacije
VALUES (123,333,20060020,3,123);

DELETE
FROM student_generacije s
WHERE s.indeks = 20060020;




INSERT INTO student_generacije(id_smera, godina, indeks)
WITH prosek AS(SELECT i.indeks, s.id_smera, avg(ocena+0.0) AS prosek, st.datum
FROM ispit i JOIN dosije d
ON d.indeks = i.indeks JOIN smer s ON s.id_smera = d.id_smera
JOIN STATUS st ON st.indeks = d.indeks
WHERE st.STATUS = 'diplomirao' AND ocena > 5 AND status_prijave = 'o'
GROUP BY i.indeks,s.id_smera,st.datum
HAVING avg(ocena+0.0)>8.5),
najbolji AS(SELECT p.id_smera, p.indeks, p.prosek, p.datum datum
FROM prosek p  JOIN dosije d ON d.indeks = p.indeks
JOIN smer s ON d.id_smera = s.id_smera
WHERE (p.prosek*(s.semestara/2)*365)/(days(p.datum)-days(d.datum_upisa)) >= ALL(SELECT p1.prosek*s.semestara/2*365/(days(p1.datum)-days(d.datum_upisa))
                                                                                FROM prosek p1  JOIN dosije d ON d.indeks = p1.indeks
                                                                                WHERE p.id_smera = p1.id_smera
                                                                                ))

SELECT ID_SMERA, CASE WHEN MONTH(DATUM) IN (10,11,12) THEN YEAR(DATUM)
                    ELSE YEAR(datum) -1 END, indeks
FROM NAJBOLJI;


UPDATE student_generacije AS sg
SET prosek = (SELECT avg(ocena+0.0)
                FROM ispit i
                WHERE sg.indeks=i.indeks AND ocena > 5 AND status_prijave = 'o'
                GROUP BY i.indeks);

CREATE VIEW prosek AS(SELECT i.indeks, s.id_smera, avg(ocena+0.0) AS prosek, st.datum
                            FROM ispit i JOIN dosije d
                            ON d.indeks = i.indeks JOIN smer s ON s.id_smera = d.id_smera
                            JOIN STATUS st ON st.indeks = d.indeks
                            WHERE st.STATUS = 'diplomirao' AND ocena > 5 AND status_prijave = 'o'
                            GROUP BY i.indeks,s.id_smera,st.datum
                            HAVING avg(ocena+0.0)>8.5);

CREATE VIEW                         najbolji AS(SELECT p.id_smera, p.indeks, p.prosek, p.datum datum
                            FROM prosek p  JOIN dosije d ON d.indeks = p.indeks
                            JOIN smer s ON d.id_smera = s.id_smera
                            WHERE (p.prosek*(s.semestara/2)*365)/(days(p.datum)-days(d.datum_upisa)) >= ALL(SELECT p1.prosek*s.semestara/2*365/(days(p1.datum)-days(d.datum_upisa))
                                                                                FROM prosek p1  JOIN dosije d ON d.indeks = p1.indeks
                                                                                WHERE p.id_smera = p1.id_smera
                                                                                ))  ;

UPDATE student_generacije AS sg
SET duzina_studiranja = (
                        SELECT days(n.datum) - days(d.datum_upisa)
                        FROM najbolji n JOIN dosije d ON n.indeks = d.indeks
                        WHERE d.indeks = sg.indeks
);








DROP VIEW najbolji;
DROP VIEW prosek;



SELECT *
FROM student_generacije