CREATE DATABASE MUNI01;
USE MUNI01;


CREATE TABLE `funkce` (
  `uzivatele_id` int,
  `jmeno` varchar(60),
  `prijmeni` varchar(60),
  `pohlavi` varchar(1),
  `PLAT1` int,
  `PLAT2` int,
  `Pocet` int,
  `Pocet2` int,
  `datum_narozeni` date,
  `datum_svatek` date,
  `mesto` varchar(20),
  `text` varchar(20),
  `test` DECIMAL(11,3)
);


insert into funkce values ('1', 'jana', 'KRAKTA', 'F', '23500', '10000', '2', '2', '1954-12-22', '1954-12-22', 'Brno', 'A      b', '0');
insert into funkce values ('2', 'Jana', 'NOVAKOVA', 'F', '23500', '10000', '4', '1', '2016-11-11', '2016-11-11', 'Brno', ' Prr T', '1.25');
insert into funkce values ('3', 'Sophia', 'SantiaGO', 'f', '23500', '10000', '5', '3', '1962-11-16', '1962-11-16', 'Brno', 'AA-123-GG', '1.45');
insert into funkce values ('4', 'Jarek', 'BENES', 'M', '52483', '10000', '7', '2', '2017-06-11', '2017-06-11', 'Praha', 'AA-123-GG', '1.5');
insert into funkce values ('5', 'Petr', 'Petr', 'm', '10000', '10000', '8', '3', '2001-09-23', '2001-09-23', 'Praha', 'AA-123-GG', '1.55');
insert into funkce values ('6', 'Honza', 'ADAMEC', 'M', '29990', '1000', '2', '2', '1977-07-27', '1977-07-27', 'Praha', 'BB-222-GG', '-1.4');
insert into funkce values ('7', 'Petra', 'ADAMCOVA', 'F', '23500', '10000', '4', '2', '1982-03-16', '1982-03-16', 'Olomouc', 'AA-123-GG', '-1.5');
insert into funkce values ('8', 'Zuzka', 'Bezva', 'f', '39752', '10000', '5', '2', '2016-02-29', '2016-02-29', 'Ostrava', 'BB-222-GG', '-1.6');
insert into funkce values ('9', 'Ivan', 'Ivan', 'm', '15274', '10000', '7', '2', '1900-02-01', '1900-02-01', 'Pardubice', 'BB-222-GG', '66.2');
insert into funkce values ('10', 'Petr', 'Novak', 'M', '27679', '10000', '6', '2', '2002-05-20', '2002-05-20', 'Zlin', 'B-222-G', '2');
insert into funkce values ('11', NULL, NULL, 'M', '27679', '10000', '5', '5', NULL, '2002-05-21', 'Zlin', '', NULL);




CREATE TABLE `objednavky` (
  `id` int,
  `datum` date,
  `firma` varchar(15),
  `id_vyrobek` int,
  `pocet` int,
  `stav` varchar(15)
);


insert into objednavky values ('1', '1954-12-22', 'AAA', '10', '2', 'Zaplaceno');
insert into objednavky values ('2', '2016-11-11', 'AAA', '22', '5', 'Nezaplaceno');
insert into objednavky values ('3', '1962-11-16', 'AAA', '333', '4', 'Zaplaceno');
insert into objednavky values ('4', '2017-06-11', 'AAA', '40', '7', 'Zaplaceno');
insert into objednavky values ('5', '2002-05-20', 'MUNI', '333', '5', 'Zaplaceno');
insert into objednavky values ('6', '2017-12-30', 'MUNI', '500', '2', 'Zaplaceno');
insert into objednavky values ('7', '2007-06-07', 'MUNI', '40', '4', 'Nezaplaceno');
insert into objednavky values ('8', '2011-06-15', 'XYZ', '500', '3', 'Zaplaceno');
insert into objednavky values ('9', '1994-02-13', 'XYZ', '40', '7', 'Zaplaceno');
insert into objednavky values ('10', '2017-04-03', 'OSVC', '333', '9', 'Zaplaceno');
insert into objednavky values ('11', '2014-03-14', 'OSVC', '500', '7', 'Zaplaceno');
insert into objednavky values ('12', '2014-03-14', 'OSVC', '47', '7', 'Zaplaceno');



CREATE TABLE `vyrobky` (
  `id` int,
  `nazev` varchar(15),
  `cena` int
);

insert into vyrobky values ('10', 'Auto', '200000');
insert into vyrobky values ('22', 'Kniha', '100');
insert into vyrobky values ('333', 'PC', '5000');
insert into vyrobky values ('40', 'Kolo', '10000');
insert into vyrobky values ('500', 'TV', '5000');
insert into vyrobky values ('42', 'Vesmir', '7000');




CREATE TABLE `fakulta` (
  `id` int,
  `Zkratka` varchar(5),
  `Cely nazev` varchar(60),
  `Počet studentu` int
);

CREATE TABLE `studenti` (
  `uzivatele_id` int,
  `jmeno` varchar(60),
  `prijmeni` varchar(60),
  `pohlavi` varchar(1),
  `PLAT` int,
  `datum_narozeni` date,
  `mesto` varchar(20),
  `fakulta` int
);

insert into studenti values ('1', 'jana', 'krátka', 'f', '23500', '1954-12-22', 'Brno', '0');
insert into studenti values ('2', 'Jana', 'Nováková', 'F', '42261', '2016-11-11', 'Brno', '1');
insert into studenti values ('3', 'Sophia', 'Santiago', 'F', '20758', '1962-11-16', 'Brno', '8');
insert into studenti values ('4', 'Jarek', 'Benes', 'M', '52483', '2017-06-11', 'Praha', '8');
insert into studenti values ('5', 'Petr', 'Malý', 'M', '10000', '2001-09-23', 'Praha', '8');
insert into studenti values ('6', 'Honza', 'Adamec', 'M', '29990', '1977-07-27', 'Praha', '7');
insert into studenti values ('7', 'Petra', 'Adamcova', 'F', '23500', '1982-03-16', 'Olomouc', '6');
insert into studenti values ('8', 'Zuzka', 'Bezva', 'F', '39752', '2016-02-29', 'Ostrava', '7');
insert into studenti values ('9', 'Ivan', 'Sobota', 'M', '15274', '1900-02-01', 'Pardubice', '3');
insert into studenti values ('10', 'Petr', 'Novak', 'M', '27679', '2002-05-20', 'Zlin', '2');
insert into studenti values ('11', 'Jirka', 'Plavec', 'M', '23500', '2017-12-30', 'Opava', '1');
insert into studenti values ('12', 'Iva', 'Nováková', 'F', '25371', '2007-06-07', 'Opava', '0');
insert into studenti values ('13', 'Ivan', 'Kurzo', 'M', '10000', '2018-02-22', 'Ostrava', '5');
insert into studenti values ('14', 'Petr', 'Novak', 'M', '21072', '2011-06-15', 'Pardubice', '7');
insert into studenti values ('15', 'Iva', 'Nováková', 'F', '23181', '1994-02-13', 'Brno', '8');
insert into studenti values ('16', 'Ela', 'Nová', 'F', '23500', '1987-12-14', 'Brno', '5');
insert into studenti values ('17', 'Jan', 'Novak', 'M', '49213', '1979-08-19', 'Brno', '2');
insert into studenti values ('18', 'petr', 'novak', 'M', '18638', '1960-11-18', 'Brno', '2');
insert into studenti values ('19', 'Tom', 'Krejza', 'M', '21086', '2017-09-24', 'Brno', '3');
insert into studenti values ('20', 'Ida', 'Krejzova', 'F', '54032', '2017-04-03', 'Brno', '5');
insert into studenti values ('21', 'Pavel', '', 'M', '23500', '2014-03-14', 'Brno', '7');
insert into studenti values ('22', 'Jakub', 'Nedelka', 'M', '50900', '2017-05-16', 'Praha', '3');
insert into studenti values ('23', 'Zuzka', 'Lesní', 'F', '49956', '2001-09-23', 'Brno', '2');
insert into studenti values ('24', 'Zuzana', 'Jarní', 'F', '10000', '2001-09-23', 'Brno', '1');
insert into studenti values ('25', 'Ela', 'Melera', 'F', '10000', '1994-02-13', 'Brno', '47');


insert into fakulta values ('1', 'ESF', 'Ekonomicko správní fakulta', '12000');
insert into fakulta values ('2', 'LAW', 'Právnícká fakulta', '15000');
insert into fakulta values ('3', 'MED', 'Lékařská fakulta', '10000');
insert into fakulta values ('4', 'SCI', 'Přirodovědeská fakulta', '5000');
insert into fakulta values ('5', 'PHIL', 'Filozofická fakulta', '15000');
insert into fakulta values ('6', 'FI', 'Fakulta informatiky', '15000');
insert into fakulta values ('7', 'PED', 'Pedagogická fakulta', '17000');
insert into fakulta values ('8', 'FSS', 'Sociální studia', '8000');
insert into fakulta values ('42', 'AAA', 'čeká na vyplnění', '44444');