Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Food(
"Code" varchar(50) NOT NULL,
"Group" varchar(100) NULL,
"Quantity" int NOT NULL,
"Color" varchar(50) NULL
);
INSERT ALL
   INTO Food ("Code","Group","Quantity","Color") VALUES ('Apple', 'Fruit', 44, 'red')
   INTO Food ("Code","Group","Quantity","Color") VALUES ('Apple', 'Fruit', 1, 'yellow')
   INTO Food ("Code","Group","Quantity","Color") VALUES  ('Pineapple', 'Fruit', 14, 'brown')
   INTO Food ("Code","Group","Quantity","Color") VALUES  ('Apple', 'Fruit', 12, 'red')
   INTO Food ("Code","Group","Quantity","Color") VALUES  ('Banana', 'Fruit', 1, 'yellow')
   INTO Food ("Code","Group","Quantity","Color") VALUES  ('Tomatoes', 'Vegetables', 8, 'red')
   INTO Food ("Code","Group","Quantity","Color") VALUES  ('Mortadella', 'Beef', 1, 'pink')
   INTO Food ("Code","Group","Quantity","Color") VALUES  ('Cucumbers', 'Vegetables', 3, 'green')
   INTO Food ("Code","Group","Quantity","Color") VALUES  ('Cigars vanille', 'Other', 1, 'black')
   INTO Food ("Code","Group","Quantity","Color") VALUES  ('Cigars', 'Other', 2, 'brown')
   
SELECT 1 FROM DUAL;
10 rows affected

SELECT *
FROM Food;


Code
Group
Quantity
Color


Apple
Fruit
44
red

Apple
Fruit
1
yellow

Pineapple
Fruit
14
brown

Apple
Fruit
12
red

Banana
Fruit
1
yellow

Tomatoes
Vegetables
8
red

Mortadella
Beef
1
pink

Cucumbers
Vegetables
3
green

Cigars vanille
Other
1
black

Cigars
Other
2
brown


CREATE TABLE Food1 AS
SELECT *
FROM Food;
10 rows affected

CREATE TABLE Food2 AS
SELECT *
FROM Food;
10 rows affected

CREATE TABLE Groups(
	"ID" varchar(50) NOT NULL,
	"Desc" varchar(100) NOT NULL
);
INSERT ALL
   INTO Groups ("ID","Desc") VALUES ('A001', 'Fruit')
   INTO Groups ("ID","Desc") VALUES ('A002', 'Vegetables')
SELECT 1 FROM DUAL;
2 rows affected

SELECT *
FROM Groups


ID
Desc


A001
Fruit

A002
Vegetables


DELETE Food1
WHERE EXISTS (
   SELECT *
   FROM Groups
   WHERE Groups."Desc"=Food1."Group"
);
7 rows affected

SELECT *
FROM Food1;


Code
Group
Quantity
Color


Mortadella
Beef
1
pink

Cigars vanille
Other
1
black

Cigars
Other
2
brown


DELETE Food2
WHERE EXISTS (
   SELECT *
   FROM Groups
   WHERE Groups."Desc"=Food2."Group"
   AND Food2."Quantity" > 1
);
5 rows affected

SELECT *
FROM Food2;


Code
Group
Quantity
Color


Apple
Fruit
1
yellow

Banana
Fruit
1
yellow

Mortadella
Beef
1
pink

Cigars vanille
Other
1
black

Cigars
Other
2
brown

Code	Group	Quantity	Color
Apple	Fruit	44	red
Apple	Fruit	1	yellow
Pineapple	Fruit	14	brown
Apple	Fruit	12	red
Banana	Fruit	1	yellow
Tomatoes	Vegetables	8	red
Mortadella	Beef	1	pink
Cucumbers	Vegetables	3	green
Cigars vanille	Other	1	black
Cigars	Other	2	brown