Last active
May 11, 2021 00:15
-
-
Save ericpkatz/870401ecde8be075f3879dbb6a1a8234 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DROP TABLE IF EXISTS ownership; | |
DROP TABLE IF EXISTS sneakers; | |
DROP TABLE IF EXISTS brands; | |
DROP TABLE IF EXISTS collectors; | |
CREATE TABLE brands( | |
id INTEGER PRIMARY KEY, | |
name VARCHAR(100) | |
); | |
CREATE TABLE sneakers( | |
id INTEGER PRIMARY KEY, | |
name VARCHAR(100), | |
brand_id INTEGER REFERENCES brands(id) | |
); | |
CREATE TABLE collectors( | |
id INTEGER PRIMARY KEY, | |
name VARCHAR(100) | |
); | |
CREATE TABLE ownership( | |
id INTEGER PRIMARY KEY, | |
size INTEGER, | |
condition VARCHAR(10), | |
sneaker_id INTEGER REFERENCES sneakers(id), | |
collector_id INTEGER REFERENCES collectors(id) | |
); | |
INSERT INTO brands(id, name) VALUES (1, 'Nike'); | |
INSERT INTO brands(id, name) VALUES (2, 'Converse'); | |
INSERT INTO sneakers(id, name, brand_id) VALUES (1, 'Air Max', 1); | |
INSERT INTO sneakers(id, name, brand_id) VALUES (2, 'Air Jordan', 1); | |
INSERT INTO sneakers(id, name, brand_id) VALUES (3, 'Stan Smith', 2); | |
INSERT INTO collectors(id, name) VALUES (1, 'moe'); | |
INSERT INTO collectors(id, name) VALUES (2, 'lucy'); | |
INSERT INTO collectors(id, name) VALUES (3, 'larry'); | |
INSERT INTO ownership(id, sneaker_id, collector_id) VALUES (1, 1, 2); | |
INSERT INTO ownership(id, sneaker_id, collector_id) VALUES (2, 1, 2); | |
INSERT INTO ownership(id, sneaker_id, collector_id) VALUES (3, 1, 2); | |
INSERT INTO ownership(id, sneaker_id, collector_id) VALUES (4, 1, 1); | |
INSERT INTO ownership(id, sneaker_id, collector_id) VALUES (5, 3, 1); | |
SELECT brands.name, sneakers.name | |
FROM brands | |
JOIN sneakers | |
ON sneakers.brand_id = brands.id; | |
SELECT collectors.name as collector_name, sneakers.name as sneaker_name | |
FROM ownership | |
JOIN collectors | |
ON collectors.id = ownership.collector_id | |
JOIN sneakers | |
ON sneakers.id = ownership.sneaker_id; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment