Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --U132
- CREATE TABLE products
- AS SELECT id FROM generate_series(1, 5) AS seq(id);
- CREATE TABLE sellers
- (email varchar);
- INSERT INTO sellers
- VALUES ('predajca@gmail.com'),
- ('obchodnik@gmail.com'),
- ('diler@gmail.com');
- CREATE TABLE sales
- (id serial, kedy timestamp, price numeric, product_id integer, seller_id varchar);
- INSERT INTO sales (kedy, price, product_id, seller_id)
- SELECT
- TIMESTAMP '2011-11-11' + floor((random() * 60)) * INTERVAL '1 day',
- floor(random() * 50 + 5)::numeric AS price,
- products.id AS product_id,
- sellers.email AS seller_id
- FROM products, sellers
- WHERE random() > 0.1;
- CREATE VIEW monthly_sales AS
- SELECT date_part('year', s.kedy) AS rok,
- date_part('month', s.kedy) AS mesiac,
- s.seller_id,
- sum(s.price) AS total_price
- FROM sales s
- GROUP BY rok, mesiac, s.seller_id;
- SELECT * FROM monthly_sales ORDER BY seller_id;
- ---
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement