SET ECHO ON

-- vytvorim sloupec potrebny pro funkcionalitu

EXECUTE pridejCelkovaCastka();

anonymous block completed
-- p�id�m n�kup komponentu s nulovou cenou a zkontroluji korektn� napln�n� nov�ho sloupce

EXECUTE pridejPlast('Maxxis', 'Ignitor', 2008, 26, 2.5);

anonymous block completed
INSERT INTO nakup (cena, datum, ob_nazev, je_id_jezdce, ko_vyrobce, ko_model, ko_rok_vyroby)
  VALUES (0, '30-05-09', 'Dookie', 1, 'Maxxis', 'Ignitor', 2008)

1 rows inserted
SELECT celkova_castka FROM jezdec WHERE id_jezdce=1

CELKOVA_CASTKA         
---------------------- 
11149                  

1 rows selected

-- vyma�u p�edchoz� n�kup

DELETE FROM nakup
  WHERE datum='30-05-09' AND ob_nazev='Dookie' AND je_id_jezdce=1 AND ko_vyrobce='Maxxis' AND ko_model='Ignitor' AND ko_rok_vyroby=2008

1 rows deleted
SELECT celkova_castka FROM jezdec WHERE id_jezdce=1

CELKOVA_CASTKA         
---------------------- 
11149                  

1 rows selected

-- p�id�m n�kup komponentu s nenulovou cenou  

INSERT INTO nakup (cena, datum, ob_nazev, je_id_jezdce, ko_vyrobce, ko_model, ko_rok_vyroby)
  VALUES (100, '30-05-09', 'Dookie', 1, 'Maxxis', 'Ignitor', 2008)

1 rows inserted
SELECT celkova_castka FROM jezdec WHERE id_jezdce=1

CELKOVA_CASTKA         
---------------------- 
11249                  

1 rows selected

-- uprav�m n�kup zv�en�m ceny

UPDATE nakup
  SET cena = 200
  WHERE datum='30-05-09' AND ob_nazev='Dookie' AND je_id_jezdce=1 AND ko_vyrobce='Maxxis' AND ko_model='Ignitor' AND ko_rok_vyroby=2008

1 rows updated
SELECT celkova_castka FROM jezdec WHERE id_jezdce=1

CELKOVA_CASTKA         
---------------------- 
11349                  

1 rows selected

-- vyma�u p�edchoz� n�kup

DELETE FROM nakup
  WHERE datum='30-05-09' AND ob_nazev='Dookie' AND je_id_jezdce=1 AND ko_vyrobce='Maxxis' AND ko_model='Ignitor' AND ko_rok_vyroby=2008

1 rows deleted
SELECT celkova_castka FROM jezdec WHERE id_jezdce=1

CELKOVA_CASTKA         
---------------------- 
11149                  

1 rows selected

ROLLBACK

ROLLBACK succeeded.
-- odstranim vytvoreny sloupec - rollback nestaci, protoze DDL dela autocommit

ALTER TABLE jezdec drop column celkova_castka

ALTER TABLE jezdec succeeded.
