Back-end Engineering Articles
I write and talk about backend stuff like Ruby, Ruby On Rails, Databases, Testing, Architecture / Infrastructure / System Design, Cloud, DevOps, Backgroud Jobs, and more...
2019-11-15
➜ ~ sudo su - postgres [sudo] password for daniel: postgres@daniel-MS-7A15:~$ psql psql (13.3 (Ubuntu 13.3-1.pgdg16.04+1), server 9.6.22) Type "help" for help. postgres=# CREATE DATABASE newsletters; CREATE DATABASE postgres=# \list
postgres=# \c newsletters psql (13.3 (Ubuntu 13.3-1.pgdg16.04+1), server 9.6.22) You are now connected to database "newsletters" as user "postgres".
newsletters=# CREATE TABLE Customers ( newsletters(# customer_id int NOT NULL UNIQUE PRIMARY KEY, newsletters(# customer_name TEXT NOT NULL, newsletters(# address TEXT NOT NULL); CREATE TABLE newsletters=# \d customers Table "public.customers" Column | Type | Collation | Nullable | Default ---------------+---------+-----------+----------+--------- customer_id | integer | | not null | customer_name | text | | not null | address | text | | not null | Indexes: "customers_pkey" PRIMARY KEY, btree (customer_id)
newsletters=# CREATE TABLE Subscriptions ( newsletters(# subscription_id INT NOT NULL UNIQUE PRIMARY KEY, newsletters(# description TEXT NOT NULL, newsletters(# price_per_month FLOAT NOT NULL DEFAULT '1.99', newsletters(# subscription_length INT NOT NULL DEFAULT '1'); CREATE TABLE newsletters=# \d subscriptions Table "public.subscriptions" Column | Type | Collation | Nullable | Default ---------------------+------------------+-----------+----------+-------------------------- subscription_id | integer | | not null | description | text | | not null | price_per_month | double precision | | not null | '1.99'::double precision subscription_length | integer | | not null | 1 Indexes: "subscriptions_pkey" PRIMARY KEY, btree (subscription_id)
newsletters=# CREATE TABLE Orders ( newsletters(# order_id INT NOT NULL UNIQUE PRIMARY KEY, newsletters(# customer_id INT NOT NULL, newsletters(# subscription_id INT NOT NULL, newsletters(# purchase_date TIMESTAMP); CREATE TABLE
newsletters=# CREATE INDEX idx_customer_id newsletters-# ON Orders (customer_id) newsletters-# ;
newsletters=# CREATE INDEX idx_subscription_id newsletters-# ON Orders (subscription_id); CREATE INDEX newsletters=# \d orders Table "public.orders" Column | Type | Collation | Nullable | Default -----------------+-----------------------------+-----------+----------+--------- order_id | integer | | not null | customer_id | integer | | not null | subscription_id | integer | | not null | purchase_date | timestamp without time zone | | | Indexes: "orders_pkey" PRIMARY KEY, btree (order_id) "idx_customer_id" btree (customer_id) "idx_subscription_id" btree (subscription_id)
INSERT INTO customers (customer_id, customer_name, address) VALUES (1, 'Allie Rahaim', '123 Broadway'), (2, 'Jacquline Diddle', '456 Park Ave.'), (3, 'Lizabeth Letsche', '789 Main St.'), (4, 'Jessia Butman', '1 Columbus Ave.'), (5, 'Inocencia Goyco', '12 Amsterdam Ave.'), (6, 'Bethann Schraub', '29 Monticello'), (7, 'Janay Priolo', '81 Harrisburg'), (8, 'Ophelia Sturdnant', '31 Deerfield Ave.'), (9, 'Eryn Vilar', '56 Morton St.'), (10, 'Jina Farraj', '100 Bryan Ave.');
newsletters=# INSERT INTO customers (customer_id, customer_name, address) newsletters-# VALUES newsletters-# (1,'Allie Rahaim','123 Broadway'), newsletters-# (2,'Jacquline Diddle','456 Park Ave.'), newsletters-# (3,'Lizabeth Letsche','789 Main St.'), newsletters-# (4,'Jessia Butman','1 Columbus Ave.'), newsletters-# (5,'Inocencia Goyco','12 Amsterdam Ave.'), newsletters-# (6,'Bethann Schraub','29 Monticello'), newsletters-# (7,'Janay Priolo','81 Harrisburg'), newsletters-# (8,'Ophelia Sturdnant','31 Deerfield Ave.'), newsletters-# (9,'Eryn Vilar','56 Morton St.'), newsletters-# (10,'Jina Farraj','100 Bryan Ave.'); INSERT 0 10 newsletters=# SELECT * FROM customers; customer_id | customer_name | address -------------+-------------------+------------------- 1 | Allie Rahaim | 123 Broadway 2 | Jacquline Diddle | 456 Park Ave. 3 | Lizabeth Letsche | 789 Main St. 4 | Jessia Butman | 1 Columbus Ave. 5 | Inocencia Goyco | 12 Amsterdam Ave. 6 | Bethann Schraub | 29 Monticello 7 | Janay Priolo | 81 Harrisburg 8 | Ophelia Sturdnant | 31 Deerfield Ave. 9 | Eryn Vilar | 56 Morton St. 10 | Jina Farraj | 100 Bryan Ave. (10 rows)
INSERT INTO subscriptions (subscription_id, description, price_per_month, subscription_length) VALUES (1, 'Politics Magazine', 10, '12 months'), (2, 'Politics Magazine', 11, '6 months'), (3, 'Politics Magazine', 12, '3 months'), (4, 'Fashion Magazine', 15, '12 months'), (5, 'Fashion Magazine', 17, '6 months'), (6, 'Fashion Magazine', 19, '3 months'), (7, 'Sports Magazine', 11, '12 months'), (8, 'Sports Magazine', 12, '6 months'), (9, 'Sports Magazine', 13, '3 months');
newsletters=# INSERT INTO subscriptions (subscription_id, description, price_per_month, subscription_length) newsletters-# VALUES newsletters-# (1,'Politics Magazine',10,'12'), newsletters-# (2,'Politics Magazine',11,'6'), newsletters-# (3,'Politics Magazine',12,'3'), newsletters-# (4,'Fashion Magazine',15,'12'), newsletters-# (5,'Fashion Magazine',17,'6'), newsletters-# (6,'Fashion Magazine',19,'3'), newsletters-# (7,'Sports Magazine',11,'12'), newsletters-# (8,'Sports Magazine',12,'6'), newsletters-# (9,'Sports Magazine',13,'3'); INSERT 0 9 newsletters=# SELECT * FROM subscriptions; subscription_id | description | price_per_month | subscription_length -----------------+-------------------+-----------------+--------------------- 1 | Politics Magazine | 10 | 12 2 | Politics Magazine | 11 | 6 3 | Politics Magazine | 12 | 3 4 | Fashion Magazine | 15 | 12 5 | Fashion Magazine | 17 | 6 6 | Fashion Magazine | 19 | 3 7 | Sports Magazine | 11 | 12 8 | Sports Magazine | 12 | 6 9 | Sports Magazine | 13 | 3 (9 rows)
INSERT INTO orders (order_id, customer_id, subscription_id, purchase_date) VALUES (1, 3, 2, '2021-05-14 14:51:53'), (2, 2, 4, '2021-05-15 17:51:53'), (3, 3, 4, '2021-05-18 13:51:53'), (4, 9, 9, '2021-05-21 17:51:53'), (5, 7, 5, '2021-06-02 12:51:53'), (6, 8, 2, '2021-06-14 13:51:53'), (7, 5, 8, '2021-06-14 17:51:53'), (8, 9, 5, '2021-06-16 19:51:53'), (9, 4, 4, '2021-06-17 20:51:53'), (10, 1, 7, '2021-07-18 22:51:53'), (11, 5, 4, '2021-07-20 17:51:53'), (12, 3, 2, '2021-09-21 17:51:53'), (13, 3, 5, '2021-12-01 18:51:53'), (14, 6, 5, '2021-12-14 16:51:53'), (15, 1, 2, '2021-12-15 15:51:53'), (16, 1, 2, '2021-12-16 14:51:53'), (17, 3, 6, '2021-12-17 13:51:53'), (18, 3, 8, '2021-12-18 12:51:53'), (19, 4, 9, '2021-12-19 11:51:53'), (20, 1, 7, '2021-12-19 10:51:53');
newsletters=# INSERT INTO orders (order_id, customer_id, subscription_id, purchase_date) newsletters-# VALUES newsletters-# (1,3,2,'2021-05-14 14:51:53'), newsletters-# (2,2,4,'2021-05-15 17:51:53'), newsletters-# (3,3,4,'2021-05-18 13:51:53'), newsletters-# (4,9,9,'2021-05-21 17:51:53'), newsletters-# (5,7,5,'2021-06-02 12:51:53'), newsletters-# (6,8,2,'2021-06-14 13:51:53'), newsletters-# (7,5,8,'2021-06-14 17:51:53'), newsletters-# (8,9,5,'2021-06-16 19:51:53'), newsletters-# (9,4,4,'2021-06-17 20:51:53'), newsletters-# (10,1,7,'2021-07-18 22:51:53'), newsletters-# (11,5,4,'2021-07-20 17:51:53'), newsletters-# (12,3,2,'2021-09-21 17:51:53'), newsletters-# (13,3,5,'2021-12-01 18:51:53'), newsletters-# (14,6,5,'2021-12-14 16:51:53'), newsletters-# (15,1,2,'2021-12-15 15:51:53'), newsletters-# (16,1,2,'2021-12-16 14:51:53'), newsletters-# (17,3,6,'2021-12-17 13:51:53'), newsletters-# (18,3,8,'2021-12-18 12:51:53'), newsletters-# (19,4,9,'2021-12-19 11:51:53'), newsletters-# (20,1,7,'2021-12-19 10:51:53'); INSERT 0 20 newsletters=# SELECT * FROM orders; order_id | customer_id | subscription_id | purchase_date ----------+-------------+-----------------+--------------------- 1 | 3 | 2 | 2021-05-14 14:51:53 2 | 2 | 4 | 2021-05-15 17:51:53 3 | 3 | 4 | 2021-05-18 13:51:53 4 | 9 | 9 | 2021-05-21 17:51:53 5 | 7 | 5 | 2021-06-02 12:51:53 6 | 8 | 2 | 2021-06-14 13:51:53 7 | 5 | 8 | 2021-06-14 17:51:53 8 | 9 | 5 | 2021-06-16 19:51:53 9 | 4 | 4 | 2021-06-17 20:51:53 10 | 1 | 7 | 2021-07-18 22:51:53 11 | 5 | 4 | 2021-07-20 17:51:53 12 | 3 | 2 | 2021-09-21 17:51:53 13 | 3 | 5 | 2021-12-01 18:51:53 14 | 6 | 5 | 2021-12-14 16:51:53 15 | 1 | 2 | 2021-12-15 15:51:53 16 | 1 | 2 | 2021-12-16 14:51:53 17 | 3 | 6 | 2021-12-17 13:51:53 18 | 3 | 8 | 2021-12-18 12:51:53 19 | 4 | 9 | 2021-12-19 11:51:53 20 | 1 | 7 | 2021-12-19 10:51:53 (20 rows)