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-22
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)
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)
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)
order_id | customer_id | subscription_id | purchase_date ----------+-------------+-----------------+--------------------- 1 | 3 | 2 | 2021-05-14 14:51:53
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
newsletters=# SELECT * FROM orders newsletters-# JOIN customers newsletters-# ON orders.customer_id = customers.customer_id;
order_id | customer_id | subscription_id | purchase_date | customer_id | customer_name | address ----------+-------------+-----------------+---------------------+-------------+-------------------+------------------- 1 | 3 | 2 | 2021-05-14 14:51:53 | 3 | Lizabeth Letsche | 789 Main St. 2 | 2 | 4 | 2021-05-15 17:51:53 | 2 | Jacquline Diddle | 456 Park Ave. 3 | 3 | 4 | 2021-05-18 13:51:53 | 3 | Lizabeth Letsche | 789 Main St. 4 | 9 | 9 | 2021-05-21 17:51:53 | 9 | Eryn Vilar | 56 Morton St. 5 | 7 | 5 | 2021-06-02 12:51:53 | 7 | Janay Priolo | 81 Harrisburg 6 | 8 | 2 | 2021-06-14 13:51:53 | 8 | Ophelia Sturdnant | 31 Deerfield Ave. 7 | 5 | 8 | 2021-06-14 17:51:53 | 5 | Inocencia Goyco | 12 Amsterdam Ave. 8 | 9 | 5 | 2021-06-16 19:51:53 | 9 | Eryn Vilar | 56 Morton St. 9 | 4 | 4 | 2021-06-17 20:51:53 | 4 | Jessia Butman | 1 Columbus Ave. 10 | 1 | 7 | 2021-07-18 22:51:53 | 1 | Allie Rahaim | 123 Broadway 11 | 5 | 4 | 2021-07-20 17:51:53 | 5 | Inocencia Goyco | 12 Amsterdam Ave. 12 | 3 | 2 | 2021-09-21 17:51:53 | 3 | Lizabeth Letsche | 789 Main St. 13 | 3 | 5 | 2021-12-01 18:51:53 | 3 | Lizabeth Letsche | 789 Main St. 14 | 6 | 5 | 2021-12-14 16:51:53 | 6 | Bethann Schraub | 29 Monticello 15 | 1 | 2 | 2021-12-15 15:51:53 | 1 | Allie Rahaim | 123 Broadway 16 | 1 | 2 | 2021-12-16 14:51:53 | 1 | Allie Rahaim | 123 Broadway 17 | 3 | 6 | 2021-12-17 13:51:53 | 3 | Lizabeth Letsche | 789 Main St. 18 | 3 | 8 | 2021-12-18 12:51:53 | 3 | Lizabeth Letsche | 789 Main St. 19 | 4 | 9 | 2021-12-19 11:51:53 | 4 | Jessia Butman | 1 Columbus Ave. 20 | 1 | 7 | 2021-12-19 10:51:53 | 1 | Allie Rahaim | 123 Broadway (20 rows)
newsletters=# SELECT * FROM orders newsletters-# JOIN customers newsletters-# ON orders.customer_id = customers.customer_id;
newsletters=# SELECT orders.order_id, customers.customer_name newsletters-# FROM orders newsletters-# JOIN customers newsletters-# ON orders.customer_id = customers.customer_id; order_id | customer_name ----------+------------------- 1 | Lizabeth Letsche 2 | Jacquline Diddle 3 | Lizabeth Letsche 4 | Eryn Vilar 5 | Janay Priolo 6 | Ophelia Sturdnant 7 | Inocencia Goyco 8 | Eryn Vilar 9 | Jessia Butman 10 | Allie Rahaim 11 | Inocencia Goyco 12 | Lizabeth Letsche 13 | Lizabeth Letsche 14 | Bethann Schraub 15 | Allie Rahaim 16 | Allie Rahaim 17 | Lizabeth Letsche 18 | Lizabeth Letsche 19 | Jessia Butman 20 | Allie Rahaim (20 rows)
newsletters=# SELECT * newsletters-# FROM orders newsletters-# JOIN subscriptions newsletters-# ON orders.subscription_id = subscriptions.subscription_id; order_id | customer_id | subscription_id | purchase_date | subscription_id | description | price_per_month | subscription_length ----------+-------------+-----------------+---------------------+-----------------+-------------------+-----------------+--------------------- 1 | 3 | 2 | 2021-05-14 14:51:53 | 2 | Politics Magazine | 11 | 6 2 | 2 | 4 | 2021-05-15 17:51:53 | 4 | Fashion Magazine | 15 | 12 3 | 3 | 4 | 2021-05-18 13:51:53 | 4 | Fashion Magazine | 15 | 12 4 | 9 | 9 | 2021-05-21 17:51:53 | 9 | Sports Magazine | 13 | 3 5 | 7 | 5 | 2021-06-02 12:51:53 | 5 | Fashion Magazine | 17 | 6 6 | 8 | 2 | 2021-06-14 13:51:53 | 2 | Politics Magazine | 11 | 6 7 | 5 | 8 | 2021-06-14 17:51:53 | 8 | Sports Magazine | 12 | 6 8 | 9 | 5 | 2021-06-16 19:51:53 | 5 | Fashion Magazine | 17 | 6 9 | 4 | 4 | 2021-06-17 20:51:53 | 4 | Fashion Magazine | 15 | 12 10 | 1 | 7 | 2021-07-18 22:51:53 | 7 | Sports Magazine | 11 | 12 11 | 5 | 4 | 2021-07-20 17:51:53 | 4 | Fashion Magazine | 15 | 12 12 | 3 | 2 | 2021-09-21 17:51:53 | 2 | Politics Magazine | 11 | 6 13 | 3 | 5 | 2021-12-01 18:51:53 | 5 | Fashion Magazine | 17 | 6 14 | 6 | 5 | 2021-12-14 16:51:53 | 5 | Fashion Magazine | 17 | 6 15 | 1 | 2 | 2021-12-15 15:51:53 | 2 | Politics Magazine | 11 | 6 16 | 1 | 2 | 2021-12-16 14:51:53 | 2 | Politics Magazine | 11 | 6 17 | 3 | 6 | 2021-12-17 13:51:53 | 6 | Fashion Magazine | 19 | 3 18 | 3 | 8 | 2021-12-18 12:51:53 | 8 | Sports Magazine | 12 | 6 19 | 4 | 9 | 2021-12-19 11:51:53 | 9 | Sports Magazine | 13 | 3 20 | 1 | 7 | 2021-12-19 10:51:53 | 7 | Sports Magazine | 11 | 12 (20 rows)
newsletters=# SELECT * newsletters-# FROM customers newsletters-# LEFT JOIN orders newsletters-# ON customers.customer_id = orders.customer_id; customer_id | customer_name | address | order_id | customer_id | subscription_id | purchase_date -------------+-------------------+-------------------+----------+-------------+-----------------+--------------------- 3 | Lizabeth Letsche | 789 Main St. | 1 | 3 | 2 | 2021-05-14 14:51:53 2 | Jacquline Diddle | 456 Park Ave. | 2 | 2 | 4 | 2021-05-15 17:51:53 3 | Lizabeth Letsche | 789 Main St. | 3 | 3 | 4 | 2021-05-18 13:51:53 9 | Eryn Vilar | 56 Morton St. | 4 | 9 | 9 | 2021-05-21 17:51:53 7 | Janay Priolo | 81 Harrisburg | 5 | 7 | 5 | 2021-06-02 12:51:53 8 | Ophelia Sturdnant | 31 Deerfield Ave. | 6 | 8 | 2 | 2021-06-14 13:51:53 5 | Inocencia Goyco | 12 Amsterdam Ave. | 7 | 5 | 8 | 2021-06-14 17:51:53 9 | Eryn Vilar | 56 Morton St. | 8 | 9 | 5 | 2021-06-16 19:51:53 4 | Jessia Butman | 1 Columbus Ave. | 9 | 4 | 4 | 2021-06-17 20:51:53 1 | Allie Rahaim | 123 Broadway | 10 | 1 | 7 | 2021-07-18 22:51:53 5 | Inocencia Goyco | 12 Amsterdam Ave. | 11 | 5 | 4 | 2021-07-20 17:51:53 3 | Lizabeth Letsche | 789 Main St. | 12 | 3 | 2 | 2021-09-21 17:51:53 3 | Lizabeth Letsche | 789 Main St. | 13 | 3 | 5 | 2021-12-01 18:51:53 6 | Bethann Schraub | 29 Monticello | 14 | 6 | 5 | 2021-12-14 16:51:53 1 | Allie Rahaim | 123 Broadway | 15 | 1 | 2 | 2021-12-15 15:51:53 1 | Allie Rahaim | 123 Broadway | 16 | 1 | 2 | 2021-12-16 14:51:53 3 | Lizabeth Letsche | 789 Main St. | 17 | 3 | 6 | 2021-12-17 13:51:53 3 | Lizabeth Letsche | 789 Main St. | 18 | 3 | 8 | 2021-12-18 12:51:53 4 | Jessia Butman | 1 Columbus Ave. | 19 | 4 | 9 | 2021-12-19 11:51:53 1 | Allie Rahaim | 123 Broadway | 20 | 1 | 7 | 2021-12-19 10:51:53 10 | Jina Farraj | 100 Bryan Ave. | | | | (21 rows)
newsletters=# SELECT * newsletters-# FROM customers newsletters-# LEFT JOIN orders newsletters-# ON customers.customer_id = orders.customer_id;
newsletters=# SELECT * newsletters-# FROM orders newsletters-# RIGHT JOIN customers newsletters-# ON orders.customer_id = customers.customer_id; order_id | customer_id | subscription_id | purchase_date | customer_id | customer_name | address ----------+-------------+-----------------+---------------------+-------------+-------------------+------------------- 1 | 3 | 2 | 2021-05-14 14:51:53 | 3 | Lizabeth Letsche | 789 Main St. 2 | 2 | 4 | 2021-05-15 17:51:53 | 2 | Jacquline Diddle | 456 Park Ave. 3 | 3 | 4 | 2021-05-18 13:51:53 | 3 | Lizabeth Letsche | 789 Main St. 4 | 9 | 9 | 2021-05-21 17:51:53 | 9 | Eryn Vilar | 56 Morton St. 5 | 7 | 5 | 2021-06-02 12:51:53 | 7 | Janay Priolo | 81 Harrisburg 6 | 8 | 2 | 2021-06-14 13:51:53 | 8 | Ophelia Sturdnant | 31 Deerfield Ave. 7 | 5 | 8 | 2021-06-14 17:51:53 | 5 | Inocencia Goyco | 12 Amsterdam Ave. 8 | 9 | 5 | 2021-06-16 19:51:53 | 9 | Eryn Vilar | 56 Morton St. 9 | 4 | 4 | 2021-06-17 20:51:53 | 4 | Jessia Butman | 1 Columbus Ave. 10 | 1 | 7 | 2021-07-18 22:51:53 | 1 | Allie Rahaim | 123 Broadway 11 | 5 | 4 | 2021-07-20 17:51:53 | 5 | Inocencia Goyco | 12 Amsterdam Ave. 12 | 3 | 2 | 2021-09-21 17:51:53 | 3 | Lizabeth Letsche | 789 Main St. 13 | 3 | 5 | 2021-12-01 18:51:53 | 3 | Lizabeth Letsche | 789 Main St. 14 | 6 | 5 | 2021-12-14 16:51:53 | 6 | Bethann Schraub | 29 Monticello 15 | 1 | 2 | 2021-12-15 15:51:53 | 1 | Allie Rahaim | 123 Broadway 16 | 1 | 2 | 2021-12-16 14:51:53 | 1 | Allie Rahaim | 123 Broadway 17 | 3 | 6 | 2021-12-17 13:51:53 | 3 | Lizabeth Letsche | 789 Main St. 18 | 3 | 8 | 2021-12-18 12:51:53 | 3 | Lizabeth Letsche | 789 Main St. 19 | 4 | 9 | 2021-12-19 11:51:53 | 4 | Jessia Butman | 1 Columbus Ave. 20 | 1 | 7 | 2021-12-19 10:51:53 | 1 | Allie Rahaim | 123 Broadway | | | | 10 | Jina Farraj | 100 Bryan Ave. (21 rows)