SQL Server 2022

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

CREATE TABLE Item (
  "item_id" INTEGER,
  "item_name" VARCHAR(30),
  "date_purchased" date
);

INSERT INTO Item
  ("item_id", "item_name", "date_purchased")
VALUES
  ('101', 'A101', '2012-01-01'),
  ('101', 'A101', '2012-01-02'),
  ('101', 'A101', '2012-01-03'),
  ('101', 'A101', '2012-01-04'),
  ('150', 'B150', '2012-01-05'),
  ('202', 'C202', '2012-01-06'),
  ('202', 'C202', '2012-01-07'),
  ('202', 'C202', '2012-01-08'),
  ('221', 'D221', '2012-01-09'),
  ('303', 'E303', '2012-01-10'),
  ('303', 'E303', '2012-01-11'),
  ('303', 'E303', '2012-01-12'),
  ('351', 'F351', '2012-01-13'),
  ('404', 'G404', '2012-01-14'),
  ('404', 'G404', '2012-01-15'),
  ('414', 'H414', '2012-01-16'),
  ('505', 'I505', '2012-01-17'),
  ('505', 'I505', '2012-01-18'),
  ('516', 'J516', '2012-01-19'),
  ('606', 'K606', '2012-01-20'),
  ('606', 'K606', '2012-01-21'),
  ('606', 'K606', '2012-01-22'),
  ('707', 'L707', '2012-01-23'),
  ('707', 'L707', '2012-01-24'),
  ('707', 'L707', '2012-01-25'),
  ('707', 'L707', '2012-01-26'),
  ('707', 'L707', '2012-01-27'),
  ('707', 'L707', '2012-01-28'),
  ('707', 'L707', '2012-01-29'),
  ('808', 'M808', '2012-01-30'),
  ('808', 'M808', '2012-01-31'),
  ('808', 'M808', '2012-02-01'),
  ('808', 'M808', '2012-02-02'),
  ('909', 'N909', '2012-02-03'),
  ('909', 'N909', '2012-02-04'),
  ('909', 'N909', '2012-02-05'),
  ('909', 'N909', '2012-02-05'),
  ('909', 'N909', '2012-02-07'),
  ('950', 'O950', '2012-02-08'),
  ('950', 'O950', '2012-02-09'),
  ('970', 'P970', '2012-02-10'),
  ('970', 'P970', '2012-02-11'),
  ('970', 'P970', '2012-02-12'),
  ('970', 'P970', '2012-02-13'),
  ('970', 'P970', '2012-02-14'),
  ('999', 'Q999', '2012-02-15');

CREATE TABLE DepartmentItem (
  "item_id" INTEGER,
  "department_id" VARCHAR(30)
);

INSERT INTO DepartmentItem
  ("item_id", "department_id")
VALUES
  ('101', 'Eng200'),
  ('101', 'IT059'),
  ('101', 'IT059L'),
  ('101', 'IT069'),
  ('150', 'Eng200'),
  ('202', 'Eng209'),
  ('202', 'IT059'),
  ('202', 'IT060L'),
  ('221', 'IT060L'),
  ('303', 'Eng209'),
  ('303', 'IT06.9'),
  ('303', 'IT069'),
  ('351', 'Eng20-'),
  ('404', 'Eng209'),
  ('404', 'IT064'),
  ('414', 'Eng20.1'),
  ('505', 'Eng209'),
  ('505', 'IT060'),
  ('516', 'IT05..'),
  ('606', 'Eng209'),
  ('606', 'IT060M'),
  ('606', 'IT069'),
  ('707', 'Eng209'),
  ('707', 'IT058H'),
  ('707', 'IT059'),
  ('707', 'IT06.0'),
  ('707', 'IT06.9'),
  ('707', 'IT060'),
  ('707', 'IT069'),
  ('808', 'Eng209'),
  ('808', 'IT059'),
  ('808', 'IT059M'),
  ('808', 'IT069'),
  ('909', 'Eng209'),
  ('909', 'IT059'),
  ('909', 'IT060'),
  ('909', 'IT060M'),
  ('909', 'IT069G'),
  ('950', 'Eng20-'),
  ('950', 'IT069'),
  ('970', 'Eng20-'),
  ('970', 'IT058L'),
  ('970', 'IT059'),
  ('970', 'IT059L'),
  ('970', 'IT069'),
  ('999', 'Eng20?');

92 rows affected

item_id
101
202
303
404
505
606
707
808
909
950
970