SQLize Online / PHPize Online  /  SQLtest Online

DROP TABLE IF EXISTS `company`; CREATE TABLE IF NOT EXISTS `company` ( `companyId` int(100) NOT NULL, `companyName` varchar(100) NOT NULL, `companyCountry` varchar(100) NOT NULL, PRIMARY KEY (`companyId`), UNIQUE KEY `companyID` (`companyId`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Дамп данных таблицы `company` -- INSERT INTO `company` (`companyId`, `companyName`, `companyCountry`) VALUES (1, 'nokia', 'finland'), (2, 'samsung', 'korea'), (3, 'apple', 'usa'), (4, 'Alcatel', 'italia'), (5, 'sony', 'japan'), (6, 'sony-ericsson', 'japan'), (7, 'philips', 'holland'), (8, ' xiaomi', 'china'), (9, 'lenovo', 'china'); -- -------------------------------------------------------- -- -- Структура таблицы `phone` -- DROP TABLE IF EXISTS `phone`; CREATE TABLE IF NOT EXISTS `phone` ( `phoneId` int(100) NOT NULL, `phoneModel` varchar(100) NOT NULL, `companyId` int(100) NOT NULL, `price` int(100) NOT NULL, PRIMARY KEY (`phoneId`), KEY `companyId` (`companyId`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Дамп данных таблицы `phone` -- INSERT INTO `phone` (`phoneId`, `phoneModel`, `companyId`, `price`) VALUES (1, 'G50', 1, 38), (2, '8 V 5G UW ', 1, 76), (3, 'Galaxy M22', 2, 54), (4, 'Galaxy M52 5G', 2, 93), (5, 'iPhone 13 Mini', 3, 87), (6, 'iPhone 11', 3, 99), (7, '1L', 4, 43), (8, '1SE', 4, 87), (9, 'Xperia Ace 2', 5, 75), (10, 'Xperia 5 III', 5, 92), (11, 'fds', 7, 44); SELECT companyName, PriceAvg FROM ( SELECT `companyId`, AVG(`price`) AS PriceAvg FROM phone GROUP BY `companyId` ) AS reporttable INNER JOIN company ON reporttable.companyId = company.companyId ORDER BY PriceAvg DESC LIMIT 1;