-- Vyber všechny státy, v nichž operuje alespoň jedna buňka organizace "K.L.M.N.".
-- {country[id›country_id] * {cell[organization_id›id, country_id] * {organization[id](name = "K.L.M.N.")}}}[name]
SELECT
   country.name
FROM
   country, cell, organization
WHERE
   organization.name LIKE "K.L.M.N." AND cell.organization_id = organization.id AND cell.country_id = country.id
GROUP BY
   country.id
ORDER BY
   country.name;
--------------------------------------------------------------------------------
-- Které organizace mají více než pět členů?
-- {agent[id] * {cell[id › cell_id] * {organizace[id › organization_id]}}}(count(agent[id]) >= 5)[name › "Organizace", count(agent[id]) › "Pocet agentu"]
SELECT
   o.name AS "Organizace", count(a.id) AS "Pocet agentu"
FROM
   cell c
JOIN
   organization o ON o.id = c.organization_id
JOIN
   agent a ON a.cell_id = c.id
GROUP BY
   o.id
HAVING
   count(a.id) > 5
ORDER BY
   o.name;
--------------------------------------------------------------------------------
--Zbraně, které zabily alespoň jednoho člověka.
SELECT
   w.id AS "ID zbrane", wt.name AS "Typ", count(m.victim_id) AS "Pocet zabitych"
FROM
   murders m
JOIN
   weapon w ON w.id = m.weapon_id
JOIN
   weapon_types wt ON wt.id = w.type_id
GROUP BY
   w.id
HAVING
   count(m.victim_id) >= 1;
--------------------------------------------------------------------------------
-- Osoby které vlastnily více než jednu zbraň a přitom nejsou obchodníci se zbraněmi.
SELECT
   h.id AS "ID cloveka", concat(h.name," ",h.surename) AS "Jmeno", count(w.weapon_id) AS "Pocet zbrani"
FROM
   human h
JOIN
   human_has_weapon w ON w.human_id = h.id
WHERE
   h.id NOT IN (SELECT human_id FROM trader)
GROUP BY
   h.id
HAVING
   count(w.weapon_id) > 1;
--------------------------------------------------------------------------------
-- Člověk vlastnící nějakou zbraň v době kdy byl zabit
-- {human[id › victim_id] * murders[occurs] * human_has_weapon[human_id › victim_id, since, onto](since ? occurs ? (onto ? occurs ? onto = NULL))}[name]
SELECT DISTINCT
   h.id AS "ID cloveka", concat(h.name, " ", h.surename) AS "Jmeno"
FROM
   human h
JOIN
   murders m ON m.victim_id = h.id
JOIN
   human_has_weapon w ON w.human_id = h.id AND w.since <= m.occurs AND (w.onto >= m.occurs OR w.onto IS NULL)
ORDER BY
   h.id;
--------------------------------------------------------------------------------
-- Typy zbraní vlastněných vojáky organizací s ideologií "teletubbies", seřazené podle počtu jejich výskytů.
SELECT
  wt.name AS "Typ zbrane", count(osoby.human_id) AS "Pocet majitelu"
FROM
  weapon_types wt
RIGHT JOIN
  weapon w ON w.type_id = wt.id
RIGHT JOIN
  human_has_weapon hhw ON hhw.weapon_id = w.id
RIGHT JOIN(
  SELECT
   a.human_id
  FROM
   agent a
  RIGHT JOIN (
   SELECT
   ahf.agent_id
   FROM
   agent_has_function ahf
   RIGHT JOIN
   specification spec ON spec.id = ahf.specification_id
   WHERE
   spec.name LIKE "Vojak"
  ) vojaci ON vojaci.agent_id = a.human_id
  RIGHT JOIN
   cell c ON a.cell_id = c.id
  RIGHT JOIN
   organization o ON o.id = c.organization_id
  RIGHT JOIN
   ideology i ON i.id = o.ideology_id
  WHERE
   i.name = "teletubbies"
  ORDER BY
   human_id
) osoby ON osoby.human_id = hhw.human_id
GROUP BY
  wt.id
ORDER BY
  count(osoby.human_id);
-- Malé upozornění - při tomto dotazu se může ve výpisu objevit více zbraní daného typu než kolik jich je celkem k dispozici. Je to proto, že zbraně v průběhu času mohou měnit majitele a pro ukázku jejich obliby je nejlepší vidět, kolikrát byly dané zbraně koupeny.
--------------------------------------------------------------------------------
-- Vyber všechny osoby, které nejsou agentem
-- {human[id] \ human[id] * {agent[human_id › id]}}
SELECT
  *
FROM
  human
WHERE
  human.id NOT IN (SELECT human.id FROM human, agent WHERE human.id = agent.human_id GROUP BY human.id)
ORDER BY
  id;
--------------------------------------------------------------------------------
-- Vyber všechny agenty organizace "K.L.M.N." kteří někdy měli v držení zbraň typu glock
-- {{agent * cell[id › cell_id] * organization(name = "K.L.M.N.")[id › organization_id]}[human_id] * {human[id › human_id] * human_has_weapon * weapon[id ›weapon_id] * weapon_types(name = "glock")[id › type_id]}}
SELECT
  Q.*
FROM (
  SELECT
    a.*
  FROM
    agent a
  JOIN
    cell c ON c.id = a.cell_id
  JOIN
    organization o ON o.id = c.organization_id WHERE o.name LIKE "K.L.M.N."
  ) Q
JOIN (
  SELECT
    a.*
  FROM
    agent a
  JOIN
    human h ON h.id = a.human_id
  JOIN
    human_has_weapon hhw ON hhw.human_id = h.id
  JOIN
    weapon w ON w.id = hhw.weapon_id
  JOIN
    weapon_types wt ON wt.id = w.type_id WHERE wt.name LIKE "glock"
  ) g ON g.id = Q.id
GROUP BY
  Q.id;
--------------------------------------------------------------------------------
-- Všichni lidé, kteří znají nějakého alespoň dvojitého agenta, který byl (bude) 21.5.2007 ještě naživu
SELECT
  h.*
FROM
  human h
JOIN
  human_knows_human hkh ON hkh.who = h.id
JOIN (
  SELECT
    h.*
  FROM
    human h
  JOIN
    agent a ON a.human_id = h.id
  WHERE
    h.id NOT IN (SELECT m.victim_id AS id FROM murders m WHERE occurs < "2007-05-21")
  GROUP BY
    h.id
  HAVING
    count(h.id) > 1
) dvojti ON dvojti.id = h.id
GROUP BY
  h.id
ORDER BY
  h.id;
--------------------------------------------------------------------------------
-- Osoby které pracují jako agent pouze v organizaci "Oz-tubbies"
-- {human[id › human_id] * agent * cell[id › cell_id] * organization(name = "Oz-tubbies")[id › organization_id]}\{human[id › human_id] * agent * cell[id › cell_id] * organization(name ? "Oz-tubbies")[id › organization_id]}
SELECT
  verniAgenti.*
FROM (
  SELECT
    h.*
  FROM
    human h
  JOIN
    agent a ON a.human_id = h.id
  GROUP BY
    a.human_id
  HAVING
    count(a.id) = 1
  ) verniAgenti
JOIN
  agent a ON a.human_id = verniAgenti.id
JOIN
  cell c ON c.id = a.cell_id
JOIN
  organization o ON o.id = c.organization_id WHERE o.name LIKE "Oz-tubbies";
--------------------------------------------------------------------------------
-- Civilní jména a kódová označení hlavních bossů všech organizací
-- {human[id › human_id, name, surename] * agent[id › agent_id, human_id, cell_id, codename] * agent_has_function * specification(name = "boss")[id › specification_id]}*{cell(id = superior_cell)[id › cell_id]}[name, surename, codename]
SELECT
  concat(h.name, " ", h.surename) AS "Jmeno bosse", a.codename AS "Nickname bosse", o.name AS "Organizace"
FROM
  human h
JOIN
  agent a ON a.human_id = h.id
JOIN
  agent_has_function ahf ON ahf.agent_id = a.id
JOIN
  specification s ON s.id = ahf.specification_id
JOIN
  cell c ON c.id = a.cell_id
JOIN
  organization o ON o.id = c.organization_id
WHERE
  s.name LIKE "boss" AND c.id = c.superior_cell;
--------------------------------------------------------------------------------
-- Vyber všechny osoby které někdy koupily novou zbraň od člověka se jménem "Evzen Onegin".
-- {{human[id › human_id] * human_has_weapon * weapon[id › weapon_id] * trader[human_id › trader_id]}[human_id, trader_id] * {human(name = "Evzen" ? surename = "Onegin")[id›trader_id]}[trader_id]}[human_id]
SELECT
  h.*, count(h.id) AS "Pocet nakupu"
FROM
  human h
JOIN
  human_has_weapon hhw ON hhw.human_id = h.id
JOIN
  weapon w ON hhw.weapon_id = w.id
JOIN
  trader t ON w.trader_id = t.human_id
JOIN
  human h2 ON h2.id = t.human_id
WHERE
  h2.name LIKE "Evzen" AND h2.surename LIKE "Onegin" AND h.id != h2.id
GROUP BY
  h.id;
--------------------------------------------------------------------------------
-- Vypiš kdo byl kdy kým a čím zabit
SELECT
  concat(h.name," ",h.surename) AS "Jmeno zabiteho", m.occurs AS "Kdy byl zabit", concat(h2.name," ",h2.surename) AS "Jmeno vraha", wt.name as "Zbran"
FROM
  human h
JOIN
  murders m ON m.victim_id = h.id
JOIN
  weapon w ON w.id = m.weapon_id
JOIN
  weapon_types wt ON wt.id = w.type_id
JOIN
  human_has_weapon hhw ON hhw.weapon_id = w.id
JOIN
  human h2 ON h2.id = hhw.human_id
WHERE
  hhw.since <= m.occurs AND (hhw.onto >= m.occurs OR hhw.onto IS NULL);
--------------------------------------------------------------------------------
-- Všechny osoby které pracují v zemi ze které pocházejí ale pro cizí agenturu / organizaci
SELECT
  h.*
FROM
  human h
JOIN
  agent a ON a.human_id = h.id
JOIN
  cell c ON c.id = a.cell_id
JOIN
  country cn1 ON cn1.id = c.country_id
JOIN
  organization o ON o.id = c.organization_id
JOIN
  country cn2 ON cn2.id = o.work_for
JOIN
  country cn3 ON cn3.id = h.country_id
WHERE
  cn1.id = cn3.id AND cn1.id != cn2.id;
--------------------------------------------------------------------------------
-- Typy zbraní, jimiž ještě nebyla spáchána žádná vražda (žádnou zbraní toho typu).
-- {{weapon_types[name, id › type_id]}\{murders * weapons[id › weapon_id] * weapon_types[name, id › type_id]}}[name]
SELECT
  wt.name
FROM
  weapon_types wt
JOIN
  weapon w ON w.type_id = wt.id
WHERE
  wt.id NOT IN (
    SELECT
      wt.id
    FROM
      weapon_types wt
    JOIN
      weapon w ON w.type_id = wt.id
    JOIN
      murders m ON m.weapon_id = w.id
    GROUP BY
      wt.id
  )
GROUP BY
  wt.id
ORDER BY
  wt.id;
--------------------------------------------------------------------------------
-- Obchodníci se zbraněmi, kteří nejsou agenty
-- {{human[id › human_id] * trader[human_id]}\{human[id ›human_id] * agent[human_id]}}[human_id]
SELECT
  h.*
FROM
  human h
JOIN
  trader t ON t.human_id = h.id
WHERE
  h.id NOT IN (SELECT h.id FROM human h JOIN agent a ON a.human_id = h.id GROUP BY h.id);
--------------------------------------------------------------------------------
-- Špehové kteří někdy měli v držení zbraň typu luk
-- {specification(name = "speh")[id › specification_id] * agent_has_function * agent[id › agent_id, codename] * human_has_weapon * weapon[id › weapon_id] * weapon_types(name = "luk")[id › type_id]}[codename]
SELECT
  a.*
FROM
  agent a
NATURAL JOIN
  human_has_weapon hhw
JOIN
  weapon w ON hhw.weapon_id = w.id
JOIN
  weapon_types wt ON wt.id = w.type_id
JOIN
  agent_has_function ahf ON ahf.agent_id = a.id
JOIN
  specification s ON s.id = ahf.specification_id
WHERE
  wt.name LIKE "luk" AND s.name LIKE "speh";
--------------------------------------------------------------------------------
-- Vojáci kteří znají alespoň dva obchodníky
SELECT
  a.*, count(a.id) AS "Kolik zna obchodniku"
FROM
  agent a
JOIN
  agent_has_function ahf ON ahf.agent_id = a.id
JOIN
  specification s ON s.id = ahf.specification_id
JOIN
  human_knows_human hkh ON a.human_id = hkh.who
JOIN
  trader t ON t.human_id = hkh.whom
GROUP BY
  a.id
HAVING
  count(a.id) >= 2;
--------------------------------------------------------------------------------
-- Lidé kteří měli cokoli společného s "Carlem Lindtem"
SELECT
  concat(h.name," ", h.surename) AS "Osoba", "jeho vrah" AS "Druh znamosti"
FROM
  human h
JOIN
  human_has_weapon hhw ON hhw.human_id = h.id
NATURAL JOIN
  murders m
JOIN
  human h2 ON h2.id = m.victim_id
WHERE
  h2.name LIKE "Carl" AND h2.surename LIKE "Lindt" AND m.occurs >= hhw.since AND (m.occurs <= hhw.onto OR hhw.onto IS NULL)
UNION
SELECT
  concat(h.name," ",h.surename) AS "Osoba", "jeho obet" AS "Druh znamosti"
FROM
  human h
JOIN
  murders m ON m.victim_id = h.id
NATURAL JOIN
  human_has_weapon hhw
JOIN
  human h2 ON h2.id = hhw.human_id
WHERE
  h2.name LIKE "Carl" AND h2.surename LIKE "Lindt" AND m.occurs >= hhw.since AND (m.occurs <= hhw.onto OR
hhw.onto IS NULL)
UNION
SELECT DISTINCT
  concat(h.name," ",h.surename) AS "Osoba", "Carl ho znal" AS "Druh znamosti"
FROM
  human h
JOIN
  human_knows_human hkh ON h.id = hkh.whom
JOIN
  human h2 ON h2.id = hkh.who
WHERE
  h2.name LIKE "Carl" AND h2.surename LIKE "Lindt"
UNION
SELECT DISTINCT
  concat(h.name," ",h.surename) AS "Osoba", "znal Carla" AS "Druh znamosti"
FROM
  human h
JOIN
  human_knows_human hkh ON h.id = hkh.who
JOIN
  human h2 ON h2.id = hkh.whom
WHERE
  h2.name LIKE "Carl" AND h2.surename LIKE "Lindt";
--------------------------------------------------------------------------------
-- Obchodníci, kteří jsou zároveň dvojitými agenty
SELECT
  t.*
FROM
  trader t
NATURAL JOIN
  agent a
GROUP BY
  t.human_id
HAVING
  count(t.human_id) >= 2;