SQLize Online / PHPize Online  /  SQLtest Online

-- 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); INSERT INTO coins values ('2021-01-02', 'bitcoin', 1, 1, 3, 1, 1); INSERT INTO coins values ('2021-01-02', 'bitcoin', 1, 1, 3, 1, 1); INSERT INTO coins values ('2021-01-02', 'bitcoin', 1, 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 OR REPLACE FUNCTION count_non_volatile_days(full_nm TEXT) RETURNS integer AS $$ DECLARE cnt INTEGER := 0; BEGIN SELECT COUNT(*) INTO cnt FROM coins WHERE coins.full_nm = count_non_volatile_days.full_nm ; IF cnt = 0 THEN RAISE 'Crypto currency with name "%s" is absent in database!', full_nm USING ERRCODE = '02000'; END IF; SELECT COUNT(*) INTO cnt FROM coins WHERE count_non_volatile_days.full_nm = coins.full_nm AND low_price = high_price; RETURN cnt; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION serial_generator(start_val_inc INTEGER, last_val_ex INTEGER) RETURNS TABLE (num INTEGER) AS $$ BEGIN FOR result IN start_val_inc..last_val_ex-1 LOOP RETURN NEXT; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; SELECT * FROM serial_generator(1, 3);