

--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;




-- rozsireni


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

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

SELECT * FROM accounts;

SELECT *, amount * 1.05 AS with_interest FROM accounts;

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


-- rozsireni a aktualizovatelnsot

DROP VIEW IF EXISTS accounts_with_interest;

CREATE VIEW accounts_with_interest AS
SELECT *, amount * 1.05 AS with_interest FROM accounts;

SELECT * from accounts_with_interest;

UPDATE accounts_with_interest SET amount=2000000 WHERE name='Tom';		-- OK

SELECT * from accounts_with_interest;

UPDATE accounts_with_interest SET with_interest=2000000 WHERE name='Tom';	-- KO

SELECT * from accounts_with_interest;


-- singletony, operator IN, agregace

DROP VIEW IF EXISTS wealthy;

CREATE VIEW wealthy AS SELECT name FROM accounts WHERE amount > 100000; -- "bohaci"

SELECT count(*) from wealthy; -- agregace - pocet radku relace

TABLE wealthy;

UPDATE accounts SET amount=amount/2 WHERE name IN (table wealthy);  -- okrademe je

SELECT count(*) from wealthy;

TABLE wealthy;


UPDATE accounts SET amount=amount/2 WHERE name IN (table wealthy);  -- a znova

SELECT count(*) from wealthy;

TABLE wealthy;


UPDATE accounts SET amount=amount/2 WHERE name IN (table wealthy);  -- a znova

SELECT count(*) from wealthy;

TABLE wealthy;  --uz nam ziskal jenom Tom - relace je singleton, pouzijeme ji jako hodnotu

UPDATE accounts SET amount=(SELECT sum(amount) FROM accounts) WHERE name=(TABLE wealthy);  -- sigelton jako hodnota, agregace, boolean skalarni vyraz se singeltonem jako hodnotou

TABLE accounts;




