SQLize | PHPize | SQLtest

-- SELECT * -- FROM test -- ORDER BY id DESC -- OFFSET 1 LIMIT 1; 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 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, avg_price NUMERIC, low_price 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', 130, 1, 1, 1, 1); INSERT INTO coins values ('2021-01-02', 'bitcoin', 120, 1, 1, 1, 1); INSERT INTO coins values ('2021-01-02', 'bitcoin', 120, 1, 1, 1, 1); INSERT INTO coins values ('2021-01-02', 'bitcoin', 120, 1, 1, 1, 1); INSERT INTO coins values ('2021-01-02', 'bitcoin', 120, 1, 1, 1, 1); INSERT INTO coins values ('2021-01-02', 'bitcoin', 120, 1, 1, 1, 1); INSERT INTO coins values ('2021-01-02', 'bitcoin', 120, 1, 1, 1, 1); INSERT INTO coins values ('2021-01-02', 'bitcoin', 120, 1, 3, 1, 1); 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; SELECT * FROM public.coins; SELECT full_nm as full_name, avg(avg_price) as avg_price, max(high_price) as max_price, min(low_price) as min_price FROM coins GROUP BY full_nm; CREATE SCHEMA cd; CREATE TABLE cd.members( memid INTEGER NOT NULL, surname CHARACTER VARYING(200) NOT NULL, firstname CHARACTER VARYING(200) NOT NULL, address CHARACTER VARYING(300) NOT NULL, zipcode INTEGER NOT NULL, telephone CHARACTER VARYING(20) NOT NULL, recommendedby INTEGER, joindate TIMESTAMP NOT NULL, CONSTRAINT members_pk PRIMARY KEY (memid), CONSTRAINT fk_members_recommendedby FOREIGN KEY (recommendedby) REFERENCES cd.members(memid) ON DELETE SET NULL ); INSERT INTO cd.members values (1, 'a', 'a', 'addr1', 111, '+7 111', NULL, '2021-01-01'); INSERT INTO cd.members values (2, 'b', 'b', 'addr2', 222, '+7 222', 1, '2021-01-02'); SELECT * FROM cd.members; SELECT recommendedby, COUNT(memid) as count FROM cd.members WHERE recommendedby IS NOT NULL GROUP BY recommendedby ORDER BY recommendedby; CREATE TABLE cd.facilities( facid INTEGER NOT NULL, name CHARACTER VARYING(100) NOT NULL, membercost NUMERIC NOT NULL, guestcost NUMERIC NOT NULL, initialoutlay NUMERIC NOT NULL, monthlymaintenance NUMERIC NOT NULL, CONSTRAINT facilities_pk PRIMARY KEY (facid) ); INSERT INTO cd.facilities values (1, 'a', 1, 1, 1, 1); INSERT INTO cd.facilities values (2, 'b', 2, 2, 2, 2); CREATE TABLE cd.bookings( bookid INTEGER NOT NULL, facid INTEGER NOT NULL, memid INTEGER NOT NULL, starttime TIMESTAMP NOT NULL, slots INTEGER NOT NULL, CONSTRAINT bookings_pk PRIMARY KEY (bookid), CONSTRAINT fk_bookings_facid FOREIGN KEY (facid) REFERENCES cd.facilities(facid), CONSTRAINT fk_bookings_memid FOREIGN KEY (memid) REFERENCES cd.members(memid) ); INSERT INTO cd.bookings values (1, 1, 1, '2013-01-01', 1); INSERT INTO cd.bookings values (2, 2, 2, '2013-02-02', 3); SELECT DISTINCT ON (member, recommender) (m.firstname || ' ' || m.surname) AS member, CASE WHEN m.recommendedby IS NULL THEN NULL ELSE (r.firstname || ' ' || r.surname) END AS recommender FROM cd.members AS m, cd.members AS r WHERE (m.recommendedby = r.memid AND m.recommendedby IS NOT NULL) OR m.recommendedby IS NULL ORDER BY member, recommender ; SELECT RANK() OVER (ORDER BY sum(vol) DESC) as rank, dt, sum(vol) as vol FROM coins GROUP BY dt LIMIT 10; SELECT COUNT(*) OVER () as count, firstname, surname FROM cd.members ORDER BY joindate; SELECT m.firstname as firstname, m.surname as surname, ROUND(0.5 * sum(b.slots)) as hours, RANK() OVER (ORDER BY ROUND(0.5 * sum(b.slots)) DESC) as rank FROM cd.members as m, cd.bookings as b WHERE m.memid = b.memid GROUP BY m.memid ORDER BY rank, surname, firstname; WITH costs AS ( SELECT f.name AS facility, CASE WHEN b.memid = 0 THEN b.slots * f.guestcost ELSE b.slots * f.membercost END AS cost FROM cd.bookings AS b, cd.facilities AS f WHERE b.facid = f.facid ) SELECT facility AS name, RANK() OVER (ORDER BY SUM(cost) DESC) FROM costs GROUP BY facility ORDER BY rank, name LIMIT 3 ; WITH RECURSIVE t(memid, firstname, surname) AS ( VALUES (1, 'GUEST', 'GUEST') UNION ALL SELECT m.memid, m.firstname, m.surname FROM cd.members as m, t WHERE m.recommendedby = t.memid ) SELECT memid, firstname, surname FROM t WHERE memid > 1 ORDER BY memid; CREATE FUNCTION count_non_volatile_days(full_name TEXT) RETURNS integer AS $$ << block >> DECLARE tbl RECORD; tbl2 RECORD; BEGIN SELECT *, lag(vol) over win INTO tbl FROM coins WHERE coins.full_nm = full_name WINDOW win as (partition by full_nm order by dt) ; IF COUNT(tbl) = 0 THEN RAISE 'Crypto currency with name % is absent in database!', full_name USING ERRCODE = '02000'; ELSE SELECT * INTO tbl2 FROM tbl WHERE lag = vol; RETURN COUNT(tbl2); END IF; END; $$ LANGUAGE plpgsql; SELECT count_non_volatile_days('bitcoin'); SELECT *, lag(vol) over win INTO tbl FROM coins WHERE coins.full_nm = 'bitcoin' WINDOW win as (partition by full_nm order by dt) ; COUNT(SELECT * FROM tbl);