--skalarni vyraz typu integer

SELECT - (1 + 1) AS res;


-- skalarní výraz typu boolean

SELECT 5 > 4 + 1 OR 5 < 4 + 1 AS res;


-- volani funkce jako skalarni vyraz

SELECT abs(-5) AS res;

SELECT factorial(abs(-5)) AS res;

SELECT random() AS res;




-- data

DROP TABLE IF EXISTS accounts CASCADE;

CREATE TABLE accounts
 ( name VARCHAR(10),
 amount integer,
 UNIQUE (name));

INSERT INTO accounts
VALUES
	('Tom', 1000000),
	('Jirka', 25),
	('Pepa', 123456),
	('Lada', 123456);

TABLE accounts;


-- usporadani n-tic

SELECT * FROM accounts ORDER BY amount ASC, name DESC;

SELECT * FROM accounts ORDER BY amount ASC, name ASC;

-- LIMIT, OFFSET

SELECT * FROM accounts ORDER BY amount ASC, name DESC LIMIT 1; -- nejchudsi (pri danem usporadani jmen)

SELECT * FROM accounts ORDER BY amount ASC, name DESC OFFSET 1 LIMIT 1; -- druhy nejchudsi (pri danem usporadani jmen)

SELECT * FROM accounts OFFSET 1 LIMIT 1; -- ???


-- rozsireni

SELECT *, amount * 1.05 AS with_interest FROM accounts;

SELECT *, amount * 1.05 FROM accounts;  --pokud nepojmenujete, tak to projde, ale....


-- singletony, operator IN


SELECT * FROM accounts WHERE amount=(SELECT 123456 as minimum);  -- lide majici 123456 na ucte (singleton jako hodnota)

SELECT * FROM accounts WHERE amount IN (SELECT 123456 as minimum);  -- totez ale s IN


SELECT * FROM accounts WHERE amount IN (SELECT amount FROM accounts ORDER BY amount DESC limit 2); -- lide majici prvni dve nejvyssi sumy na ucte



-- agregace

DROP TABLE IF EXISTS lide;
CREATE TABLE lide
       (jmeno TEXT,
        vek INT,
	vyska INT,
	vaha INT);

INSERT INTO lide VALUES
       ('Jan', 30, 185, 85),
       ('Ema', 30, 170, 60),
       ('Tom', 85, 160, 60),
       ('Iva', 25, 190, 85),
       ('Pepa', 30, 190, 95),
       ('Ivo', 25, 180, 80),
       ('Eda', 30, 150, 50),
       ('Jiri', 85, 160, 60),
       ('Kuba', 85, 160, 60),
       ('Eva', 30, 175, 70);

-- kolik lidi je v DB?
SELECT count(*) AS pocet FROM lide;

--prumerny vek, vaha a vyska
SELECT  avg(vek) AS prumerny_vek, avg(vaha) AS prumer_vaha, avg(vyska) AS prumer_vyska FROM lide;

-- prumerna vaha a vyska tricatniku
SELECT  avg(vaha) AS prumer_vaha, avg(vyska) AS prumer_vyska FROM lide WHERE vek = 30;

--proc ne takto?
SELECT  vek, avg(vaha) AS prumer_vaha, avg(vyska) AS prumer_vyska FROM lide WHERE vek = 30;

-- prumerna vaha a vyska pro jednotlive veky
SELECT vek, avg(vaha) AS prumer_vaha, avg(vyska) AS prumer_vyska FROM lide GROUP BY vek;

-- prumerna vaha a vyska tricatniku pres filtrovani pomoci HAVING
SELECT vek, avg(vaha) AS prumer_vaha, avg(vyska) AS prumer_vyska FROM lide GROUP BY vek HAVING vek=30;


-- prumerna vaha a vyska pro jednotlive veky, kde mam alespon 3 respondenty
SELECT vek, avg(vaha) AS prumer_vaha, avg(vyska) AS prumer_vyska FROM lide GROUP BY vek HAVING count(*) >= 3;


-- jak je to s NULL hodnotami?

SELECT  count(*) AS pocet_lidi, sum(vek) AS suma_vek, avg(vek) AS prumerny_vek, sum(vaha) AS suma_vaha, avg(vaha) AS prumer_vaha, sum(vyska) AS suma_vyska, avg(vyska) AS prumer_vyska FROM lide;

INSERT INTO lide VALUES
       (null, 30, null, null),
       (null, null, null, null);


SELECT  count(*) AS pocet_lidi, sum(vek) AS suma_vek, avg(vek) AS prumerny_vek, sum(vaha) AS suma_vaha, avg(vaha) AS prumer_vaha, sum(vyska) AS suma_vyska, avg(vyska) AS prumer_vyska FROM lide;



