00_tables.sql

DROP TABLE IF EXISTS sender;
CREATE TABLE sender (
        id INTEGER PRIMARY KEY,
        name,
        city
);

DROP TABLE IF EXISTS product;
CREATE TABLE product (
        id INTEGER PRIMARY KEY,
        name,
        color,
        city
);

DROP TABLE IF EXISTS delivery;
CREATE TABLE delivery (
        sender_id INTEGER,
        product_id INTEGER,
        quantity INTEGER,
        FOREIGN KEY(sender_id) REFERENCES sender(id),
        FOREIGN KEY(product_id) REFERENCES product(id)
);

01_data.sql

INSERT INTO sender (id, name, city) VALUES (7, "Lowen", "Berlin");
INSERT INTO sender (id, name, city) VALUES (8, "Jeter", "N.Y.");
INSERT INTO sender (id, name, city) VALUES (9, "Kling", "Berlin");
INSERT INTO sender (id, name, city) VALUES (10, "Greer", "Paris");
INSERT INTO sender (id, name, city) VALUES (11, "Wong", "S.F.");

INSERT INTO product (id, name, color, city) VALUES (1, "Bolt", "Blue", "Berlin");
INSERT INTO product (id, name, color, city) VALUES (2, "Nut", "Red", "Paris");
INSERT INTO product (id, name, color, city) VALUES (3, "Cog", "Red", "Rome");
INSERT INTO product (id, name, color, city) VALUES (4, "Screw", "Green", "Paris");
INSERT INTO product (id, name, color, city) VALUES (5, "Nail", "Red", "N.Y.");

INSERT INTO delivery (sender_id, product_id, quantity) VALUES (7, 1, 300);
INSERT INTO delivery (sender_id, product_id, quantity) VALUES (7, 3, 400);
INSERT INTO delivery (sender_id, product_id, quantity) VALUES (8, 2, 300);
INSERT INTO delivery (sender_id, product_id, quantity) VALUES (8, 4, 500);
INSERT INTO delivery (sender_id, product_id, quantity) VALUES (9, 3, 300);
INSERT INTO delivery (sender_id, product_id, quantity) VALUES (11, 2, 600);

02_queries.sql

-- Senders that do not deliver anything:
SELECT DISTINCT id FROM sender WHERE id NOT IN (SELECT sender_id FROM delivery);
-- Returns: 10

-- Senders that deliver products from Paris:
SELECT DISTINCT sender_id FROM delivery WHERE product_id IN (
        SELECT id FROM product WHERE city="Paris"
);
-- Returns: 8, 11