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 |