MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE customers (
    birth_day date,
    first_name VARCHAR(20),
    last_name VARCHAR(20),
    c_id int,
    CONSTRAINT PK_Customers PRIMARY KEY (c_id));
INSERT INTO customers (birth_day, first_name, last_name, c_id) VALUES ('1993-07-11','Victor','Davis',1);
INSERT INTO customers (birth_day, first_name, last_name, c_id) VALUES ('2001-03-28','Katarina','Williams',2);
INSERT INTO customers (birth_day, first_name, last_name, c_id) VALUES ('1965-12-11','David','Jones',3);
INSERT INTO customers (birth_day, first_name, last_name, c_id) VALUES ('1980-10-10','Evelyn','Lee',4);


CREATE TABLE employees (
    birth_day date,
    first_name VARCHAR(20),
    last_name VARCHAR(20),
    e_id int,
    CONSTRAINT PK_Employees PRIMARY KEY (e_id)
);
INSERT INTO employees (birth_day, first_name, last_name, e_id) VALUES ('1983-09-02','David','Smith',1);
INSERT INTO employees (birth_day, first_name, last_name, e_id) VALUES ('1990-07-23','Olivia','Brown',2);
INSERT INTO employees (birth_day, first_name, last_name, e_id) VALUES ('1973-05-11','David','Johnson',3);
INSERT INTO employees (birth_day, first_name, last_name, e_id) VALUES ('1999-11-21','Mia','Taylor',4);




CREATE TABLE transactions (
    e_id int,
    c_id int,
    date date,
    t_id int,
    CONSTRAINT PK_transactions PRIMARY KEY (t_id),
    FOREIGN KEY (e_id) REFERENCES employees(e_id),
    FOREIGN KEY (c_id) REFERENCES customers(c_id)
);
INSERT INTO transactions (e_id, c_id, date, t_id) VALUES (1,1,'2020-8-11',1);
INSERT INTO transactions (e_id, c_id, date, t_id) VALUES (3,1,'2020-8-15',2);
INSERT INTO transactions (e_id, c_id, date, t_id) VALUES (1,4,'2020-9-01',3);
INSERT INTO transactions (e_id, c_id, date, t_id) VALUES (2,2,'2020-9-07',4);
INSERT INTO transactions (e_id, c_id, date, t_id) VALUES (4,3,'2020-9-07',5);
CREATE TABLE items (
    amount int,
    price_for_each int,
    name VARCHAR(20),
    CONSTRAINT PK_items PRIMARY KEY (name)
);
INSERT INTO items (price_for_each, amount, name) VALUES (110,22,'2lof milk');
INSERT INTO items (price_for_each, amount, name) VALUES (99,30,'6 cans of lemonade');
INSERT INTO items (price_for_each, amount, name) VALUES (150,20,'Pack of butter');
INSERT INTO items (price_for_each, amount, name) VALUES (450,13,'Roast chicken');
INSERT INTO items (price_for_each, amount, name) VALUES (99,30,'Pack of rice');
INSERT INTO items (price_for_each, amount, name) VALUES (20,50,'Banana');
INSERT INTO items (price_for_each, amount, name) VALUES (200,30,'3kg sugar');
INSERT INTO items (price_for_each, amount, name) VALUES (150,15,'Toast bread');
INSERT INTO items (price_for_each, amount, name) VALUES (150,18,'Earl Grey tea');

CREATE TABLE promotions (
    discount int,
    p_id int,
    CONSTRAINT PK_promotions PRIMARY KEY (p_id)
);
INSERT INTO promotions (discount, p_id) VALUES (99,1);
INSERT INTO promotions (discount, p_id) VALUES (200,2);
INSERT INTO promotions (discount, p_id) VALUES (150,3);
INSERT INTO promotions (discount, p_id) VALUES (150,4);

CREATE TABLE itemsinpromotions (
    name VARCHAR(20),
    p_id int,
    amount int,
    FOREIGN KEY (name) REFERENCES items(name),
    FOREIGN KEY (p_id) REFERENCES promotions(p_id)
);
INSERT INTO itemsinpromotions (name, p_id, amount) VALUES ('6 cans of lemonade',1,2);
INSERT INTO itemsinpromotions (name, p_id, amount) VALUES ('Roast chicken',2,1);
INSERT INTO itemsinpromotions (name, p_id, amount) VALUES ('Pack of rice',2,1);
INSERT INTO itemsinpromotions (name, p_id, amount) VALUES ('Pack of butter',3,1);
INSERT INTO itemsinpromotions (name, p_id, amount) VALUES ('Toast bread',3,2);
INSERT INTO itemsinpromotions (name, p_id, amount) VALUES ('2lof milk',4,2);
INSERT INTO itemsinpromotions (name, p_id, amount) VALUES ('Banana',4,3);
INSERT INTO itemsinpromotions (name, p_id, amount) VALUES ('3kg sugar',4,2);

CREATE TABLE itemsintransactions (
    name VARCHAR(20),
    t_id int,
    amount int,
    FOREIGN KEY (name) REFERENCES items(name),
    FOREIGN KEY (t_id) REFERENCES transactions(t_id)
);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES('6 cans of lemonade',1,1);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Roast chicken',1,1);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Pack of butter',1,1);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Toast bread',1,1);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('2lof milk',1,2);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Banana',1,3);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('3kg sugar',1,1);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('6 cans of lemonade',2,5);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Pack of rice',2,1);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('6 cans of lemonade',3,3);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Roast chicken',3,2);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Pack of rice',3,1);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Pack of butter',3,1);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('2lof milk',4,5);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Banana',4,20);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('3kg sugar',4,8);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('6 cans of lemonade',5,10);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Roast chicken',5,10);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Pack of rice',5,10);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Pack of butter',5,10);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Toast bread',5,10);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('2lof milk',5,10);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Banana',5,10);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('3kg sugar',5,10);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Earl Grey tea',5,10);


CREATE VIEW DavidSoldTo AS 
    SELECT birth_day, first_name, last_name
    FROM customers
    WHERE EXISTS (SELECT 1 FROM transactions WHERE e_id=1)
    ORDER BY birth_day
;

CREATE VIEW PeopleInShop AS 
    SELECT DISTINCT birth_day, first_name, last_name
    FROM employees
    UNION
    SELECT DISTINCT birth_day, first_name, last_name
    FROM customers
    WHERE EXISTS (SELECT 1 FROM transactions WHERE date=2020-9-07)
    ORDER BY birth_day
;
select * from DavidSoldTo


birth_day
first_name
last_name


1965-12-11
David
Jones

1980-10-10
Evelyn
Lee

1993-07-11
Victor
Davis

2001-03-28
Katarina
Williams


select * from PeopleInShop


birth_day
first_name
last_name


1973-05-11
David
Johnson

1983-09-02
David
Smith

1990-07-23
Olivia
Brown

1999-11-21
Mia
Taylor
birth_day	first_name	last_name
1965-12-11	David	Jones
1980-10-10	Evelyn	Lee
1993-07-11	Victor	Davis
2001-03-28	Katarina	Williams
birth_day	first_name	last_name
1973-05-11	David	Johnson
1983-09-02	David	Smith
1990-07-23	Olivia	Brown
1999-11-21	Mia	Taylor