TEXT   22

DB

Guest on 22nd August 2022 01:26:03 PM

  1. CREATE DOMAIN nazev AS VARCHAR(50);
  2.  
  3. CREATE TABLE hra {
  4.     idr int(10) PRIMARY KEY,
  5.     nazev nazev UNIQUE KEY,
  6.     vyrobce varchar(250),
  7.     cena int(10)
  8. };
  9.  
  10. UPDATE HRA SET cena*1.25 WHERE vyrobce='XYZ';
  11.  
  12. SELECT hra.* FROM hra
  13.     JOIN skore ON(skore.hra=hra.idr)
  14.     WHERE skore is not null
  15.     ORDER BY vyrobce, nazev;
  16.  
  17. CREATE TABLE skore {
  18.     hrac int(10),
  19.     hra int(10),
  20.     skore int(15),
  21.     PRIMARY KEY (hrac, hra),
  22. };
  23.  
  24. ALTER TABLE `skore`
  25.  ADD CONSTRAINT `klic_1` FOREIGN KEY (`hra`) REFERENCES `hra` (`idr`) ON DELETE NO ACTION ON UPDATE CASCADE;
  26. ALTER TABLE `skore`
  27.  ADD CONSTRAINT `klic_2` FOREIGN KEY (`hrac`) REFERENCES `hrac` (`idh`) ON DELETE SET NULL ON UPDATE CASCADE;
  28.  
  29. SET TERM //;
  30.  
  31. CREATE PROCEDURE skore_pridej (id_hrace int(10), id_hry int(10), skore int(10))
  32. DECLARE VARIABLE
  33.     pocet_radku int(1);
  34. AS
  35. begin
  36.     SELECT count(*) FROM skore WHERE hrac=:id_hrace AND hra=:id_hry INTO :pocet_radku;
  37.     if(:pocet_radku=0) then begin
  38.         INSERT INTO skore VALUES(:id_hrace, :id_hry, :skore);
  39.     end;
  40.     else begin
  41.         UPDATE skore set skore = :skore WHERE skore<:skore;
  42.     end;
  43. end;//
  44.  
  45. SELECT idr, nazev, AVG(skore) as prum_skore FROM hra JOIN skore ON(skore.hra=hra.idr) ORDER BY nazev;
  46.  
  47. 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;
  48.  
  49. SELECT neco_jineho FROM hrac JOIN skore ON(skore.hrac=hrac.idh) WHERE SUM(skore)>1000 AND COUNT(skore)>=5 ORDER BY prijmeni;
  50.  
  51. SELECT neco_jineho FROM hrac JOIN skore ON(skore.hrac=hrac.idh) WHERE skore>400 AND COUNT(skore)>=10 ORDER BY prijmeni;
  52.  
  53. CREATE SEQUENCE gen_idr;
  54.  
  55. CREATE PROCEDURE hra_nova(nazev nazev, vyrobce varchar(250))
  56. RETURNS (nove_id INTEGER)
  57. AS
  58. BEGIN
  59.     nove_id = GEN_ID(gen_idr, 1);
  60.     INSERT INTO hra VALUES(:nove_id, :nazev, :vyrobce, NULL);
  61. END;//
  62.  
  63.  
  64. CREATE VIEW hry_nehrane AS SELECT idr, nazev, vyrobce, cena FROM hra JOIN skore ON(skore.hra=hra.idr) WHERE COUNT(skore.skore)=0;
  65.  
  66. CREATE PROCEDURE skore_od_do(a integer, b integer)
  67. RETURNS(idh, jmeno, prijmeni, idr, nazev, skore)
  68. AS
  69. BEGIN
  70.     SELECT idh, jmeno, prijmeni, idr, nazev, skore FROM hrac
  71.     JOIN skore ON(skore.hrac=hrac.idh)
  72.     JOIN hra ON(skore.hra=hra.idr)
  73.     WHERE skore>:a AND skore<:b
  74.     ORDER BY skore, nazev
  75.     INTO :idh, :jmeno, :prijmeni, :idr, :nazev, :skore;
  76. end//

Raw Paste


Login or Register to edit or fork this paste. It's free.