SQLize Online / PHPize Online  /  SQLtest Online

CREATE SCHEMA interview; CREATE TABLE interview.stores ( id INTEGER, name TEXT, allowed_alcohol BOOLEAN ); INSERT INTO interview.stores VALUES (1, 'Amazon', TRUE), (2, 'Babylist', FALSE); CREATE TABLE interview.products ( id INTEGER, name TEXT, upc TEXT, created_at DATE ); INSERT INTO interview.products VALUES (1, 'Bottle', '123', '2018-01-01'), (2, 'Diaper', '456', '2018-01-02'), (3, 'Pacifier', '789', '2018-01-03'), (4, 'Baby Toy', '456', '2018-02-04'), (5, 'Soft Blanket', '9213123', '2018-02-05'); CREATE TABLE interview.store_prices ( id INTEGER, product_id INTEGER, store_id INTEGER, price NUMERIC ); INSERT INTO interview.store_prices VALUES (1, 3, 1, 2.59), (2, 2, 1, 3.32), (3, 4, 1, 3.59), (4, 3, 2, 2.34), (5, 1, 2, 1.56); CREATE TABLE interview.order_lines ( id INTEGER, product_id INTEGER, store_id INTEGER, qty INTEGER, line_total NUMERIC, ordered_at DATE ); INSERT INTO interview.order_lines VALUES (1, 1, 2, 3, NULL, '2019-01-15'), (2, 2, 1, 50, NULL, '2019-01-25'), (3, 2, 1, 1, NULL, '2019-03-02'), (4, 1, 2, 12, NULL, '2019-03-04'), (5, 4, 1, 6, NULL, '2019-05-04'), (6, 3, 2, 4, NULL, '2019-05-05'); SELECT * FROM interview.stores; SELECT * FROM interview.products; SELECT * FROM interview.store_prices; SELECT * FROM interview.order_lines; select price, name from interview.store_prices left join interview.products on interview.store_prices.product_id = interview.products.id order by interview.store_prices.price DESC limit 2 ;