sneaker_world.sql

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;