USE OsobaDB2;

INSERT osoba(id,meno,priezvisko,rodne_priezvisko,dat_nar,dat_smrti,pohlavie,vyska,vaha)
SELECT id,meno,priezvisko,rodne_priezvisko,dat_nar,dat_smrti,pohlavie,vyska,vaha
FROM OsobaDB.osoba;

INSERT rodic(id,otec,matka)
SELECT id,otec,matka
FROM OsobaDB.osoba
WHERE otec IS NOT NULL OR matka IS NOT NULL;

INSERT vztah(id,id_on,id_ona,od,do)
SELECT id,id_on,id_ona,od,do
FROM OsobaDB.vztah;

-------------------------

INSERT osoba(id,meno,priezvisko,rodne_priezvisko,dat_nar,dat_smrti,pohlavie,vyska,vaha)
SELECT 24,meno,priezvisko,rodne_priezvisko,dat_nar,dat_smrti,pohlavie,vyska,vaha
FROM osoba
WHERE id = 14;

-------------------------

SET SQL_SAFE_UPDATES = 0;

UPDATE osoba
SET meno = 'Dáša', rodne_priezvisko = priezvisko, priezvisko = 'Dášová'
WHERE id = 24;

-------------------------

SET SQL_SAFE_UPDATES = 0;

UPDATE osoba o
INNER JOIN OsobaDB.osoba o2 ON o2.id = o.id
SET o.rodne_priezvisko = o2.rodne_priezvisko
WHERE o.rodne_priezvisko != o2.rodne_priezvisko;

UPDATE osoba o, OsobaDB.osoba o2
SET o.rodne_priezvisko = o2.rodne_priezvisko
WHERE o.id = o2.id AND o.rodne_priezvisko != o2.rodne_priezvisko;

-------------------------

SET SQL_SAFE_UPDATES = 0;

DELETE FROM osoba
WHERE id = 24;

-------------------------

SET SQL_SAFE_UPDATES = 0;

DELETE osoba
FROM osoba
INNER JOIN OsobaDB.osoba o2 ON o2.id = osoba.id
WHERE osoba.id != o2.id;

-------------------------

ALTER TABLE osoba ADD COLUMN vek INT AFTER dat_nar;

ALTER TABLE osoba ADD vek INT AFTER dat_nar;

-------------------------

UPDATE Osoba
SET vek = TIMESTAMPDIFF(YEAR, dat_nar, CURRENT_DATE())
WHERE dat_smrti NULL;

-------------------------

UPDATE Osoba
SET vek = TIMESTAMPDIFF(YEAR, dat_nar, IFNULL(dat_smrti, CURRENT_DATE()))
WHERE dat_smrti IS NOT NULL;

-------------------------

ALTER TABLE osoba ALTER COLUMN vek SET DEFAULT 0;

ALTER TABLE osoba ALTER vek SET DEFAULT 0;

ALTER TABLE osoba MODIFY COLUMN vek INT NOT NULL DEFAULT 0;

ALTER TABLE osoba MODIFY vek INT NOT NULL DEFAULT 0;

ALTER TABLE osoba CHANGE COLUMN vek vek INT NOT NULL DEFAULT 0;

ALTER TABLE osoba CHANGE vek vek INT NOT NULL DEFAULT 0;

-------------------------

ALTER TABLE osoba ALTER COLUMN vek DROP DEFAULT;

ALTER TABLE osoba ALTER vek DROP DEFAULT;

-------------------------

ALTER TABLE osoba MODIFY COLUMN vek INT NOT NULL FIRST;

ALTER TABLE osoba MODIFY vek INT NOT NULL FIRST;

ALTER TABLE osoba CHANGE COLUMN vek vek INT NOT NULL FIRST;

ALTER TABLE osoba CHANGE vek vek INT NOT NULL FIRST;

-------------------------

ALTER TABLE osoba RENAME COLUMN vek TO pocet_rokov;

ALTER TABLE osoba CHANGE COLUMN vek pocet_rokov INT NOT NULL;

ALTER TABLE osoba CHANGE vek pocet_rokov INT NOT NULL;

-------------------------

ALTER TABLE osoba DROP COLUMN pocet_rokov;

ALTER TABLE osoba DROP pocet_rokov;

-------------------------

SELECT * FROM information_schema.table_constraints
WHERE constraint_schema = 'osobadb2';

SELECT * FROM information_schema.key_column_usage 
WHERE constraint_schema = 'osobadb2';

-------------------------

ALTER TABLE osoba ADD CONSTRAINT osoba_chk_pohlavie CHECK(pohlavie IN('m','z'));

ALTER TABLE osoba ADD CHECK(pohlavie IN('m','z'));

-------------------------

ALTER TABLE osoba DROP CHECK osoba_chk_pohlavie;

-- od verzie MySQL 8.0.19
ALTER TABLE osoba DROP CONSTRAINT osoba_chk_pohlavie;

-----------------------

ALTER TABLE osoba ADD CONSTRAINT osoba_chk_meno_priezvisko CHECK ( meno is not null or priezvisko is not null );

ALTER TABLE osoba ADD CHECK ( meno is not null or priezvisko is not null );

-----------------------

ALTER TABLE osoba DROP CHECK osoba_chk_meno_priezvisko;

ALTER TABLE osoba DROP CONSTRAINT osoba_chk_meno_priezvisko;

-----------------------

ALTER TABLE osoba ADD CONSTRAINT osoba_uq_pvv UNIQUE (pohlavie, vyska, vaha);

ALTER TABLE osoba ADD UNIQUE (pohlavie, vyska, vaha);

-----------------------

ALTER TABLE osoba DROP INDEX osoba_uq_pvv;

-- od verzie MySQL 8.0.19
ALTER TABLE osoba DROP CONSTRAINT osoba_uq_pvv;

-----------------------

ALTER TABLE osoba CHANGE COLUMN id id INT NOT NULL PRIMARY KEY AUTO_INCREMENT;

ALTER TABLE osoba AUTO_INCREMENT = 101;

ALTER TABLE osoba CHANGE id id INT NOT NULL PRIMARY KEY AUTO_INCREMENT;

ALTER TABLE osoba AUTO_INCREMENT = 101;

ALTER TABLE osoba MOFIFY COLUMN id INT NOT NULL PRIMARY KEY AUTO_INCREMENT;

ALTER TABLE osoba AUTO_INCREMENT = 101;

ALTER TABLE osoba MOFIFY id INT NOT NULL PRIMARY KEY AUTO_INCREMENT;

ALTER TABLE osoba AUTO_INCREMENT = 101;

-- len pridanie kluca
ALTER TABLE osoba ADD PRIMARY KEY (id);

-----------------------

ALTER TABLE osoba DROP PRIMARY KEY;

-----------------------

ALTER TABLE osoba ADD CONSTRAINT osoba_fk_otec FOREIGN KEY (otec) REFERENCES osoba (id);

ALTER TABLE osoba ADD COLUMN matka INTEGER, ADD FOREIGN KEY (matka) REFERENCES osoba (id);

-----------------------

ALTER TABLE osoba DROP FOREIGN KEY osoba_fk_otec;

ALTER TABLE osoba DROP CONSTRAINT osoba_fk_matka;

-----------------------

SET FOREIGN_KEY_CHECKS=0;

SET FOREIGN_KEY_CHECKS=1;

ALTER TABLE osoba DISABLE KEYS;

ALTER TABLE osoba ENABLE KEYS;

-----------------------

ALTER TABLE osoba RENAME person;

RENAME TABLE osoba TO person;