SQLize Online / PHPize Online  /  SQLtest Online

-- CREATE TABLE test ( -- id serial, -- name varchar -- ); -- INSERT INTO test (name) values ('one'),('two'),('three'),('four'),('five'); -- SELECT * -- FROM test -- ORDER BY id DESC -- OFFSET 1 LIMIT 1; -- CREATE SCHEMA public; CREATE TABLE IF NOT EXISTS public.hw( id INTEGER, height FLOAT4, weight FLOAT4 ); SELECT min(height) as min_height, max(height) as max_height, min(weight) as min_weight, max(weight) as max_weight FROM public.hw; SELECT (weight * 0.453592)/POWER(height * 0.0254, 2) AS bmi FROM public.hw; INSERT INTO public.hw values (1, 1.0, 0.0); INSERT INTO public.hw values (2, 1.0, 100.0); INSERT INTO public.hw values (3, 1.0, 100.0); SELECT COUNT(id) AS underweight_count FROM public.hw WHERE (weight / 2.2046) / POWER(height * 0.0254, 2) < 18.5; SELECT id, (weight * 0.453592)/POWER(height * 0.0254, 2) AS bmi, CASE WHEN (weight * 0.453592)/POWER(height * 0.0254, 2) < 18.5 THEN 'underweight' WHEN (weight * 0.453592)/POWER(height * 0.0254, 2) < 25 THEN 'normal' WHEN (weight * 0.453592)/POWER(height * 0.0254, 2) < 30 THEN 'overweight' WHEN (weight * 0.453592)/POWER(height * 0.0254, 2) < 35 THEN 'obese' ELSE 'extremely obese' END AS type FROM public.hw ORDER BY bmi DESC, id DESC; CREATE TABLE IF NOT EXISTS coins( dt VARCHAR(16), full_nm VARCHAR(128), high_price NUMERIC, tx_cnt NUMERIC, vol NUMERIC ); -- INSERT INTO coins values (1, 101, 1, 1, 1, 1, 'BiTCoin'; -- INSERT INTO coins values (1, 1, 1, 1, 1, 1, 'BiTCOin'); -- SELECT * FROM coins WHERE full_nm = 'BTC' and avg_price < 100; -- SELECT dt, high_price, vol FROM public.coins WHERE symbol = 'DOGE' AND avg_price > 0.001 AND dt LIKE '2018%'; SELECT DISTINCT UPPER(full_nm) AS full_name FROM public.coins ORDER BY full_name; INSERT INTO coins values ('2021-01-01', 'bitcoin', 120, 1, 1); INSERT INTO coins values ('2021-01-02', 'bitcoin', 120); WITH best_price AS ( SELECT full_nm, MAX(high_price) as price FROM public.coins GROUP BY full_nm ) SELECT UPPER(p.full_nm) AS full_name, MIN(dt) AS dt, p.price FROM public.coins AS c RIGHT JOIN best_price AS p ON c.full_nm = p.full_nm AND c.high_price = p.price GROUP BY p.full_nm, p.price ORDER BY price DESC, full_name ASC; -- SELECT DISTINCT UPPER(full_nm), MIN(dt), high_price AS price FROM public.coins RIGHT JOIN best_price ON public.coins.full_nm = best_price.full_nm GROUP BY full_nm, high_price; -- SELECT DISTINCT UPPER(full_nm) AS full_name, MIN(dt), high_price AS price FROM public.coins; DELETE FROM public.coins WHERE tx_cnt IS NULL OR vol IS NULL;