Databases for Marat

creation.sql

CREATE TABLE `Collection` (
  `Id` int(11) NOT NULL,
  `Name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `Item` (
  `Id` int(11) NOT NULL,
  `CollectionId` int(11) NOT NULL,
  `Value` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `Collection` (`Id`, `Name`) VALUES
(3, 'Овощи'),
(4, 'Фрукты'),
(5, 'Крема');
INSERT INTO `Item` (`Id`, `CollectionId`, `Value`) VALUES
(1, 3, 'Помидоры'),
(2, 3, 'Огурцы'),
(3, 4, 'Бананы'),
(4, 4, 'Яблоки'),
(5, 5, 'Для кожи'),
(6, 5, 'Для морды'),
(7, 5, 'Для жопы'),
(8, 1, 'Хуйня какая-то');

ALTER TABLE `Collection`
  ADD PRIMARY KEY (`Id`);
ALTER TABLE `Item`
  ADD PRIMARY KEY (`Id`);
ALTER TABLE `Collection`
  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
ALTER TABLE `Item`
  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

query.sql

SELECT Name FROM Collection cn JOIN  Item im ON cn.ID = im.CollectionID ORDER BY COUNT(DISTINCT(im.CollectionID)) DESC;