veri

1-Prosedür ile enleri bulma

BEGIN

    DECLARE ders_idsi INT;
    DECLARE yuksek_isim VARCHAR(100);
    DECLARE dusuk_isim VARCHAR(100);
    DECLARE yuksek_not FLOAT;
    DECLARE dusuk_not FLOAT;
    SET ders_idsi=1;
   
    SELECT MAX(od.not) AS max_not, MIN(od.not) AS min_not
    INTO yuksek_not, dusuk_not
    FROM ogrenci o
    JOIN ogrenci_ders od ON o.ono=od.ono
    WHERE od.did=ders_idsi;
   
    SELECT o.adi, o.soyadi, od.not
    FROM ogrenci o
    JOIN ogrenci_ders od ON o.ono=od.ono
    WHERE od.did=ders_idsi AND od.not=yuksek_not
    UNION
    SELECT o.adi, o.soyadi, od.not
    FROM ogrenci o
    JOIN ogrenci_ders od ON o.ono=od.ono
    WHERE od.did=ders_idsi AND od.not=dusuk_not;
 
END

2- Prosedür ile enler2 bulma

BEGIN
    DECLARE yuksek_isim VARCHAR(100);
    DECLARE dusuk_isim VARCHAR(100);
    DECLARE yuksek_not FLOAT;
    DECLARE dusuk_not FLOAT;
   
    SELECT MAX(od.not) AS max_not, MIN(od.not) AS min_not
    INTO yuksek_not, dusuk_not
    FROM ogrenci o
    JOIN ogrenci_ders od ON o.ono=od.ono
    WHERE od.did=ders_idsi;
   
    SELECT o.adi, o.soyadi, od.not
    FROM ogrenci o
    JOIN ogrenci_ders od ON o.ono=od.ono
    WHERE od.did=ders_idsi AND od.not=yuksek_not
    UNION
    SELECT o.adi, o.soyadi, od.not
    FROM ogrenci o
    JOIN ogrenci_ders od ON o.ono=od.ono
    WHERE od.did=ders_idsi AND od.not=dusuk_not;

END

3-Hesaplama

BEGIN
DECLARE toplam INT DEFAULT 0;
    DECLARE i INT;
    SET i=basla;
    WHILE i<=bitis DO
    SET toplam=toplam+i;
        SET i=i+artis;
    END WHILE;
    SELECT toplam AS mesaj;
END

4- Ortalama

BEGIN
DECLARE i INT DEFAULT 0;
    DECLARE toplam FLOAT DEFAULT 0;
    DECLARE ort FLOAT;
    DECLARE notu FLOAT;
    DECLARE bulunamadi TINYINT DEFAULT FALSE;
   
    DECLARE ogrenci_dongu CURSOR FOR
    SELECT `not` FROM ogrenci_ders WHERE ono=o_no;
   
    DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET bulunamadi=TRUE;
       
    OPEN ogrenci_dongu;
        WHILE bulunamadı=FALSE DO
    FETCH ogrenci_dongu INTO notu;
        SET i=i+1;
        SET toplam=+toplam+notu;
    END WHILE;
    SET ort=toplam/i;
    SELECT ort as mesaj;
 
END

5- yerleştir

BEGIN
DECLARE adayId INT;
    DECLARE programId INT;
    DECLARE kontenjanToplam INT;
    DECLARE kontenjanYerlesen INT;
    DECLARE bulunamadi TINYINT DEFAULT FALSE;
    DECLARE bulunamadiTercih TINYINT DEFAULT FALSE;
   
   
    DECLARE aday_cursor CURSOR FOR SELECT id
    FROM osym_aday ORDER BY puan DESC;
   
    DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET bulunamadi = TRUE;
       
    DELETE FROM osym_yerlestir WHERE 1;
   
    OPEN aday_cursor;
    WHILE bulunamadi=FALSE DO
    FETCH aday_cursor INTO adayId;
       
        SET bulunamadiTercih=FALSE;
        blok2: BEGIN
    DECLARE tercih_cursor CURSOR FOR SELECT program_id
        FROM osym_tercih
        WHERE aday_id=adayId ORDER BY sira;
       
            DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET bulunamadiTercih = TRUE;
            OPEN tercih_cursor;
            WHILE bulunamadiTercih=FALSE DO
            FETCH tercih_cursor INTO programId;
               
                SELECT kontenjan INTO kontenjanToplam FROM osym_program
                WHERE id=programId;
                   
                SELECT COUNT(*) INTO kontenjanYerlesen FROM osym_yerlestir
                WHERE program_id=programId;
               
                IF kontenjanYerlesen<kontenjanToplam THEN
                INSERT INTO osym_yerlestir (aday_id, program_id)
                    VALUES(adayId,programId);
                    CLOSE tercih_cursor;
                    LEAVE blok2;
                END IF;
            END WHILE;
            CLOSE tercih_cursor;
        END blok2;
       
    END WHILE;
    CLOSE aday_cursor;
   
END

Alt sorgu

select *
from ogrenci o
where ono IN(select DISTINCT ono
                       from ogrenci_ders od
                       inner join ders d on od.did=d.did
                       where `not` between 2 and 3.5)