CREATE DOMAIN nazev AS VARCHAR(50);

CREATE TABLE hra {
    idr int(10) PRIMARY KEY,
    nazev nazev UNIQUE KEY,
    vyrobce varchar(250),
    cena int(10)
};

UPDATE HRA SET cena*1.25 WHERE vyrobce='XYZ';

SELECT hra.* FROM hra
    JOIN skore ON(skore.hra=hra.idr)
    WHERE skore is not null
    ORDER BY vyrobce, nazev;

CREATE TABLE skore {
    hrac int(10),
    hra int(10),
    skore int(15),
    PRIMARY KEY (hrac, hra),
};

ALTER TABLE `skore`
 ADD CONSTRAINT `klic_1` FOREIGN KEY (`hra`) REFERENCES `hra` (`idr`) ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE `skore`
 ADD CONSTRAINT `klic_2` FOREIGN KEY (`hrac`) REFERENCES `hrac` (`idh`) ON DELETE SET NULL ON UPDATE CASCADE;

SET TERM //;

CREATE PROCEDURE skore_pridej (id_hrace int(10), id_hry int(10), skore int(10))
DECLARE VARIABLE
    pocet_radku int(1);
AS
begin
    SELECT count(*) FROM skore WHERE hrac=:id_hrace AND hra=:id_hry INTO :pocet_radku;
    if(:pocet_radku=0) then begin
        INSERT INTO skore VALUES(:id_hrace, :id_hry, :skore);
    end;
    else begin
        UPDATE skore set skore = :skore WHERE skore<:skore;
    end;
end;//

SELECT idr, nazev, AVG(skore) as prum_skore FROM hra JOIN skore ON(skore.hra=hra.idr) ORDER BY nazev;

SELECT idh, jmeno, prijmeni, COUNT(skore) as pocet_her FROM hrac JOIN skore ON(skore.hrac=hrac.idh) WHERE pocet_her>=5 ORDER BY pocet_her DESC;

SELECT neco_jineho FROM hrac JOIN skore ON(skore.hrac=hrac.idh) WHERE SUM(skore)>1000 AND COUNT(skore)>=5 ORDER BY prijmeni;

SELECT neco_jineho FROM hrac JOIN skore ON(skore.hrac=hrac.idh) WHERE skore>400 AND COUNT(skore)>=10 ORDER BY prijmeni;

CREATE SEQUENCE gen_idr;

CREATE PROCEDURE hra_nova(nazev nazev, vyrobce varchar(250))
RETURNS (nove_id INTEGER)
AS
BEGIN
    nove_id = GEN_ID(gen_idr, 1);
    INSERT INTO hra VALUES(:nove_id, :nazev, :vyrobce, NULL);
END;//


CREATE VIEW hry_nehrane AS SELECT idr, nazev, vyrobce, cena FROM hra JOIN skore ON(skore.hra=hra.idr) WHERE COUNT(skore.skore)=0;

CREATE PROCEDURE skore_od_do(a integer, b integer)
RETURNS(idh, jmeno, prijmeni, idr, nazev, skore)
AS
BEGIN
    SELECT idh, jmeno, prijmeni, idr, nazev, skore FROM hrac
    JOIN skore ON(skore.hrac=hrac.idh)
    JOIN hra ON(skore.hra=hra.idr)
    WHERE skore>:a AND skore<:b
    ORDER BY skore, nazev
    INTO :idh, :jmeno, :prijmeni, :idr, :nazev, :skore;
end//