Один товарищ рассматривал вариант устроиться на работу в Яндекс на вакансию «Асессор-разработчик».
В тестовом задании была задачка на составление SQL-запроса.
Для всех стран в базе данных посчитать количество компаний со штаб квартирами в этой стране численность сотрудников в которых больше 1000 человек.
В результате должны быть только количество компаний и названия стран с населением более 1 миллиона человек и валовым продуктом более 10 миллиардов долларов, у которых суммарная выручка выбранных компаний составляет более 1 миллиарда долларов.
Скорее всего опытный SQL’щик посмеется на такой реализацией задачи и сможет написать более оптимальный запрос. Если есть идеи по оптимизации — пишите свои варианты в коментариях.
1) Во вложенном запросе получаем список id городов, у которых население более 1 миллиона человек, которые находятся в странах, имеющих валовый доход более 10 миллиардов долларов:
Примечание: не рекомендую использовать в качестве готового ответа для отправки в Яндекс, только для анализа и проверки работоспособности, — чтобы потом написать свой вариант реализации. Так как этот вариант решения был отправлен в Яндекс, и, если вы его скопируте, плагиат может быть легко определен.
Тем кому лень создавать таблицы в БД с нуля, могут использовать мой тестовый дамп:
— Дамп структуры для таблица yandex—sql.Cities
CREATE TABLE IF NOT EXISTS `Cities` (
`id` int(11) NOT NULL DEFAULT ‘0’,
`name` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
`population` int(11) DEFAULT NULL,
`founded` int(11) DEFAULT NULL,
`country_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
— Дамп данных таблицы yandex-sql.Cities:
INSERT INTO `Cities` (`id`, `name`, `population`, `founded`, `country_id`) VALUES
(1, ‘Ульяновск’, 750000, 1648, 1),
(2, ‘Москва’, 3000000, 1420, 1),
(3, ‘Ташкент’, 2500000, 956, 2),
(4, ‘Урумчи’, 900000, 205, 3),
(5, ‘Шанхай’, 3000000, 20, 3);
— Дамп структуры для таблица yandex—sql.Companies
CREATE TABLE IF NOT EXISTS `Companies` (
`id` int(11) NOT NULL,
`name` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT »,
`city_id` int(11) NOT NULL,
`revenue` int(11) NOT NULL,
`labors` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
— Дамп данных таблицы yandex-sql.Companies: ~9 rows (приблизительно)
INSERT INTO `Companies` (`id`, `name`, `city_id`, `revenue`, `labors`) VALUES
(1, ‘Супер-софт’, 1, 900000000, 1500),
(2, ‘Мегасофт’, 1, 500000000, 3000),
(3, ‘Ковер-самолет’, 3, 5000000, 3000),
(4, ‘Трах-Тибидох Development’, 3, 1000000000, 5000),
(5, ‘Ур Ум Чи’ка-1’, 4, 300000, 1001),
(6, ‘Ур Ум Чи’ка-2’, 4, 520000, 999),
(7, ‘Пу До Нг’, 5, 600000000, 1600),
(8, ‘ZBAA Dev’, 5, 520000000, 2500),
(9, ‘IBS’, 2, 500, 1200);
— Дамп структуры для таблица yandex—sql.Countries
CREATE TABLE IF NOT EXISTS `Countries` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
`population` int(11) DEFAULT NULL,
`gdp` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
— Дамп данных таблицы yandex-sql.Countries:
INSERT INTO `Countries` (`id`, `name`, `population`, `gdp`) VALUES
(1, ‘Россия’, 3000000, 500000000000),
(2, ‘Узбекистан’, 1000001, 200000000000),
(3, ‘Китай’, 1000000000, 1000000000000);
Здравствуйте. Решаю задачку:
В реляционной базе данных существуют таблицы:
Cities — список городов
id — первичный ключ
name — название
population — численность населения
founded — год основания
country_id — id страны
Countries — список стран
id — первичный ключ
name — название
population — численность населения
gdp — валовый продукт в долларах
Companies — компании
id — первичный ключ
name — название
city_id — город в котором находится штаб-квартира
revenue — годовая выручка в долларах
labors — численность сотрудников
Постройте таблицу, где для каждой страны посчитано число компаний, удовлетворяющих условиям:
1) штаб квартира компании находится в этой стране
2) число сотрудников компании не менее 1000 человек
Я дошел до такого запроса:
| SQL | ||
|
т.е. он соединяет 2 таблицы.
Я никак не могу присоединить 3-ю таблицу, для того, чтобы сгруппировать не по городам, а по странам, пишет все время что неправильный синтаксис.
Прошу подсказки.
Сама база в архиве
an anonymous user
·
May 06, 2022
SQL
/*
Постройте таблицу, где для каждой страны посчитано число компаний, удволетворяющих условиям:
1) штаб квартира компании находится в этой стране
2) число сотрудников компании не менее 1000 человек */
CREATE TABLE Countries( -- список стран
id INTEGER PRIMARY KEY, -- первичный ключ
name TEXT NOT NULL, -- название
population INTEGER NOT NULL, -- численность населения
gdp INTEGER NOT NULL ); -- валовый продукт в долларах
CREATE TABLE Cities (
id INTEGER PRIMARY KEY, -- первичный ключ
name TEXT NOT NULL, -- название
population INTEGER NOT NULL, -- численность населения
founded INTEGER NOT NULL, -- год основания
country_id foreign Countries-id ); -- id страны
CREATE TABLE Companies( -- компании
id INTEGER PRIMARY KEY, -- первичный ключ
name TEXT NOT NULL, -- название
city_id INTEGER NOT NULL, -- город в котором находится штаб-квартира
revenue INTEGER NOT NULL, -- годовая выручка в долларах
labors INTEGER NOT NULL) -- численность сотрудников
-- insert some values
/*INSERT INTO students VALUES (1, 'Ryan', 'M');
INSERT INTO students VALUES (2, 'Joanna', 'F');
-- fetch some values
SELECT * FROM students WHERE gender = 'F';
Output
(Run the program to view its output)
В реляционной базе данных существуют таблицы:
Cities — список городов
id — первичный ключ
name — название
population — численность населения
founded — год основания
country_id — id страны
Countries — список стран
id — первичный ключ
name — название
population — численность населения
gdp — валовый продукт в долларах
Companies — компании
id — первичный ключ
name — название
city_id — город в котором находится штаб-квартира
revenue — годовая выручка в долларах
labors — численность сотрудников
Составьте запрос, который:
Для всех стран в базе данных посчитать количество компаний со штаб квартирами в этой стране численность сотрудников в которых больше 1000 человек
В результате должны быть только количество компаний и названия стран с населением более 1 миллиона человек и валовым продуктом более 10 миллиардов долларов, у которых суммарная выручка выбранных компаний составляет более 1 миллиарда долларов
Мой вариант:
select *, count(labors),count(revenue),FROM Companies
group by name
HAVING count(labors) >=1000 AND count(revenue) >= 1000000000
( это я пытался выстроить компании с численность сотрудников > 1000 и доходом более 1ккк)
Далее я так полагаю нужно получившийся список сравнить со списком (Countries ) и составить новый список и новый список сравнить со с писком (Cities ) и этот список будет ответом.
П.С. Хотелось бы получить не просто ответ но и логику выполнения такого задания.
В реляционной базе данных существуют таблицы:
Cities — список городов
id — первичный ключ
name — название
population — численность населения
founded — год основания
country_id — id страны
Countries — список стран
id — первичный ключ
name — название
population — численность населения
gdp — валовый продукт в долларах
Companies — компании
id — первичный ключ
name — название
city_id — город в котором находится штаб-квартира
revenue — годовая выручка в долларах
labors — численность сотрудников
Составьте запрос, который:
Для всех стран в базе данных посчитать количество компаний со штаб квартирами в этой стране численность сотрудников в которых больше 1000 человек
В результате должны быть только количество компаний и названия стран с населением более 1 миллиона человек и валовым продуктом более 10 миллиардов долларов, у которых суммарная выручка выбранных компаний составляет более 1 миллиарда долларов
Мой вариант:
select *, count(labors),count(revenue),FROM Companies
group by name
HAVING count(labors) >=1000 AND count(revenue) >= 1000000000
( это я пытался выстроить компании с численность сотрудников > 1000 и доходом более 1ккк)
Далее я так полагаю нужно получившийся список сравнить со списком (Countries ) и составить новый список и новый список сравнить со с писком (Cities ) и этот список будет ответом.
П.С. Хотелось бы получить не просто ответ но и логику выполнения такого задания.
AleksWhite911 / Module_3_SQL_Practice_Netology
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters
| —=============== МОДУЛЬ 3. ОСНОВЫ SQL ======================================= |
| —= ПОМНИТЕ, ЧТО НЕОБХОДИМО УСТАНОВИТЬ ВЕРНОЕ СОЕДИНЕНИЕ И ВЫБРАТЬ СХЕМУ PUBLIC=========== |
| SET search_path TO public; |
| —======== ОСНОВНАЯ ЧАСТЬ ============== |
| —ЗАДАНИЕ №1 |
| —Выведите для каждого покупателя его адрес проживания, |
| —город и страну проживания. |
| select concat(c.last_name,’ ‘, c.first_name) as «Фамилия и имя», a.»address», ci.»city», co.»country» |
| from customer c |
| join address a on a.address_id = c.address_id |
| join city ci on ci.city_id = a.city_id |
| join country co on co.country_id = ci.country_id |
| —ЗАДАНИЕ №2 |
| —С помощью SQL-запроса посчитайте для каждого магазина количество его покупателей. |
| select s.store_id, count(c.customer_id) as «Количество покупателей» |
| from store s |
| join customer c on c.store_id = s.store_id |
| group by s.store_id |
| —Доработайте запрос и выведите только те магазины, |
| —у которых количество покупателей больше 300-от. |
| —Для решения используйте фильтрацию по сгруппированным строкам |
| —с использованием функции агрегации. |
| select s.store_id, count(c.customer_id) as «Количество покупателей» |
| from store s |
| join customer c on c.store_id = s.store_id |
| group by s.store_id |
| having count(c.customer_id) > 300 |
| — Доработайте запрос, добавив в него информацию о городе магазина, |
| —а также фамилию и имя продавца, который работает в этом магазине. |
| select s.store_id, count(c.customer_id) as «Количество покупателей», ci.city, concat(st.last_name, ‘ ‘, st.first_name) as «Фамилия и имя продавца» |
| from store s |
| join customer c on c.store_id = s.store_id |
| join address a on a.address_id = s.address_id |
| join city ci on ci.city_id = a.city_id |
| join staff st on st.store_id = s.store_id |
| group by s.store_id, ci.city_id, st.staff_id |
| having count(c.customer_id) > 300 |
| —ЗАДАНИЕ №3 |
| —Выведите ТОП-5 покупателей, |
| —которые взяли в аренду за всё время наибольшее количество фильмов |
| select concat(c.last_name, ‘ ‘, c.first_name) as «Фамилия и имя», count(r.rental_id) as «Количество фильмов» |
| from customer c |
| join rental r on r.customer_id = c.customer_id |
| group by c.customer_id |
| order by count(rental_id) desc |
| limit 5 |
| —ЗАДАНИЕ №4 |
| —Посчитайте для каждого покупателя 4 аналитических показателя: |
| — 1. количество фильмов, которые он взял в аренду |
| — 2. общую стоимость платежей за аренду всех фильмов (значение округлите до целого числа) |
| — 3. минимальное значение платежа за аренду фильма |
| — 4. максимальное значение платежа за аренду фильма |
| select concat(c.last_name, ‘ ‘, c.first_name) as «Фамилия и имя», count(r.rental_id) as «Количество фильмов», round(sum(p.amount)) as «Общая стоимость платежей», |
| min(p.amount) as «Минимальное значение платежа», max(p.amount) as «Максимальное значение платежа» |
| from customer c |
| join rental r on r.customer_id = c.customer_id |
| join payment p on r.customer_id = p.customer_id and p.rental_id = r.rental_id |
| group by c.customer_id |
| —ЗАДАНИЕ №5 |
| —Используя данные из таблицы городов составьте одним запросом всевозможные пары городов таким образом, |
| —чтобы в результате не было пар с одинаковыми названиями городов. |
| —Для решения необходимо использовать декартово произведение. |
| select c.city, c1.city |
| from city c |
| cross join city c1 |
| where c.city != c1.city |
| —ЗАДАНИЕ №6 |
| —Используя данные из таблицы rental о дате выдачи фильма в аренду (поле rental_date) |
| —и дате возврата фильма (поле return_date), |
| —вычислите для каждого покупателя среднее количество дней, за которые покупатель возвращает фильмы. |
| select r.customer_id as «ID покупателя», round(avg(date_part(‘day’, return_date — rental_date::date))::numeric, 2) as «Среднее количество дней на возврат» |
| from rental r |
| group by r.customer_id |
| order by r.customer_id |
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.
Сколько городов в мире, города-миллионники
Городом принято считать довольно крупный населенный пункт. Трудовая деятельность жителей такого места обыкновенно не связана с сельским хозяйством. Горожане живут на доходы, полученные в иных отраслях.
Исторически термин происходит от наличия вокруг поселения оборонительной ограды — вала или стены. В Древней Руси городом называли любое крупное жилое место, окруженное такой оградой. Они служили центром развития ремесел, искусства и технических достижений.
- Статус города
- Количество городов в мире
- Города-миллионники
- Население от 10 млн человек
- Города с населением 5−10 млн человек
- Другие миллионники
Статус города
Первые города или, иначе говоря, территориальные центры поселений появились еще в эпоху палеолита. С ходом истории понятие «город» менялось. И, соответственно, менялись и сами населенные пункты. В разное время статус города зависел от:
- размера территории одновременного заселения,
- функций центра поселения,
- наличия укреплений,
- численности населения и так далее.
Интересно знать: координаты долготы и широты Москвы.
Последний параметр и по сей день во многих странах является главным критерием для получения городского статуса.
Все населенные пункты соединены друг с другом дорогами. Дороги, проходящие сквозь поселение, становятся улицами. Расположение на перекрестках торговых маршрутов в свое время оказало немалое положительное влияние на развитие Парижа, Венеции, Амстердама, Генуи, Данцига, Великого Новгорода и других городов.
На сегодняшний день территория поселения, имеющего этот статус, обычно наполнена высокими зданиями, построенными из бетона и стали. В большинстве таких мест имеется широкая инфраструктура с обширнейшей сетью разностороннего обслуживания людей.
Это интересно: что является непосредственной причиной возникновения поясов атмосферного давления?
В городскую инфраструктуру входят:
- организации, обеспечивающие население продуктами питания и различными товарами,
- компании, осуществляющие перевозки и обеспечивающие наличие общественного транспорта,
- и многое-многое другое.
Количество городов в мире
Подсчитать общее количество весьма проблематично. Из всех существующих причин, которые затрудняют подсчет, можно выделить две основные.
Причина первая. В различных странах мира поселениям присваивается статус города по неодинаковым критериям.
Как уже указывалось выше, во многих странах статус населенных пунктов зависит от количества жителей. Но минимальный уровень численности населения, необходимый для получения такого статуса, в каждой стране устанавливается отдельно. И эти значения в разных государствах сильно отличаются друг от друга.
Для сравнения можно привести такой пример. В Дании городами становятся все поселения, число жителей которых превышает всего двести пятьдесят человек. В Японии же в населенном пункте должны проживать не менее пятидесяти тысяч человек! То есть один японский город равен двум сотням датских.
Кроме того, некоторые страны не имеют городов вовсе. В них не существует данного понятия. Наиболее яркий пример такого государства — Франция. Французские коммуны, как географические единицы, близки к городам. Но они никак не зависят ни от количества живущих в них людей, ни от сферы деятельности населения.
Это интересно: страны Европы и их столицы, список.
Причина вторая. Количество городов постоянно меняется. Стихийные бедствия порой уничтожают целые населенные пункты. Но катаклизмы — не главная причина исчезновения. Основным орудием как для создания, так и для уничтожения поселений являются люди. Благодаря притоку населения в мире возникают новые города. Из-за оттока населения они стираются с карты. Именно поэтому численность городов мира не может быть константой. А значит, абсолютно точного ответа на главный вопрос не существует. На настоящий момент имеются данные без малого о 2 млн 668 тысячах мест, имеющих статус города!
Все существующие в современном мире города можно разделить на 6 категорий. В зависимости от того, к какой категории относится, он получает дополнительный статус:
- малого,
- среднего,
- большого,
- крупного,
- крупнейшего,
- миллионника.
О последних и пойдет речь ниже.
Города-миллионники
Как понятно из названия, это такие города, количество живущих людей в которых составляет (или превышает) 1 000 000 граждан.
В восьмидесятых годах двадцатого века количество существующих на планете городов-миллионеров достигало примерно двухсот двадцати. Сейчас их уже больше трех сотен. Государством лидером является Китай. В этой стране насчитывается несколько десятков городов с населением от одного миллиона.
Население от 10 млн человек
На настоящий момент семнадцать городов имеют население свыше 10 млн. В четырех из них проживает более 20 млн граждан.
- На первом месте по численности населения находится китайский Чунцин. Самый крупный на планете не только по количеству жителей, но и по площади. Территория Чунцина составляет более 82 тысяч квадратных километров. Проживает в Чунцине свыше 53 млн человек. Но такое большое число населения при такой площади мало заметно. Плотность населения Чунцина составляет шестьсот человек на квадратный километр — это самое маленькое значение из шести десятков самых населенных мест мира.
- Второе место по числу жителей занимает опять же представитель Китая — Шанхай. Население Шанхая — свыше 24 млн граждан.
- На почетном третьем месте расположился Карачи — самый большой город Пакистана. Его население — 23,5 млн жителей.
- Закрывает четверку Пекин. В столице Китайской Республики проживает более 21 млн граждан.
- Затем следует Дели. По своей площади это второй город Индии. Число его жителей превышает 16 млн.
- На шестом месте — нигерийский Лагос, в котором проживает около 15 млн человек.
- Следующими по численности населения являются турецкий Стамбул и японский Токио. Количество жителей обоих приближается к 14 млн.
- Девятое место занимает еще один китайский представитель — Гуанчжоу. Его население — 13 млн.
- Количество жителей свыше 12 млн имеет индийский Мумбаи, а также Москва и Дакка — столицы России и Бангладеша соответственно.
- В столице Египта — Каире, бразильском Сан-Паулу и пакистанском Лахоре живет более 11 млн человек.
- Завершают список городов, население которых превышает 10 млн граждан, китайский Шеньчжень и корейский Сеул.
Города с населением 5−10 млн человек
Более 9 млн граждан живут в индонезийской Джакарте, столице Демократической Республики Конго — Киншасе и китайском Тяньцзине.
В девяти городах мира число жителей превышает 8 млн — в Мехико, Лиме, Бангалоре, Лондоне, Нью-Йорке, Бангкоке, Дунгуане, Тегеране и Ахмадабаде.
Имеют население свыше 7 млн Богота, Хошимин, Гонконг и Багдад.
В шести населенных пунктах количество жителей переваливает за 6 млн — в Ухане, Хайдарабаде, Ханое, Луанде, Рио-де-Жанейро и Фошане.
Семь городов с населением более 5 млн человек.
Другие миллионники
Пятнадцать городов на планете имеют население, превышающее 4 млн человек. А с населением свыше трех миллионов граждан и того больше. Что уж говорить про просто миллионники. Список городов по численности населения, их площадь и другие интересные сведения можно найти в популярной интернет-энциклопедии Википедии.
Решение модуля 1.6 из курса «Интерактивный тренажер по SQL»
Разобрали ответы и решения урока 1.6(Таблица «Командировки», запросы на выборку) под курс «Интерактивный тренажер по SQL».
Предыдущий разбор модуля 1.5 следующий разбор модуль 1.7
Вывести из таблицы trip информацию о командировках тех сотрудников, фамилия которых заканчивается на букву «а», в отсортированном по убыванию даты последнего дня командировки виде. В результат включить столбцы name, city, per_diem, date_first, date_last.
Вывести в алфавитном порядке фамилии и инициалы тех сотрудников, которые были в командировке в Москве.
Для каждого города посчитать, сколько раз сотрудники в нем были. Информацию вывести в отсортированном в алфавитном порядке по названию городов. Вычисляемый столбец назвать Количество.
Вывести два города, в которых чаще всего были в командировках сотрудники. Вычисляемый столбец назвать Количество.
Вывести информацию о командировках во все города кроме Москвы и Санкт-Петербурга (фамилии и инициалы сотрудников, город , длительность командировки в днях, при этом первый и последний день относится к периоду командировки). Последний столбец назвать Длительность . Информацию вывести в упорядоченном по убыванию длительности поездки, а потом по убыванию названий городов (в обратном алфавитном порядке).
Вывести информацию о командировках сотрудника(ов), которые были самыми короткими по времени. В результат включить столбцы name , city , date_first , date_last .
Вывести информацию о командировках, начало и конец которых относятся к одному месяцу (год может быть любой). В результат включить столбцы name , city , date_first , date_last . Строки отсортировать сначала в алфавитном порядке по названию города, а затем по фамилии сотрудника .
Вывести название месяца и количество командировок для каждого месяца. Считаем, что командировка относится к некоторому месяцу, если она началась в этом месяце. Информацию вывести сначала в отсортированном по убыванию количества, а потом в алфавитном порядке по названию месяца виде. Название столбцов – Месяц и Количество.
Вывести сумму суточных (произведение количества дней командировки и размера суточных) для командировок, первый день которых пришелся на февраль или март 2020 года. Значение суточных для каждой командировки занесено в столбец per_diem . Вывести фамилию и инициалы сотрудника, город, первый день командировки и сумму суточных. Последний столбец назвать Сумма . Информацию отсортировать сначала в алфавитном порядке по фамилиям сотрудников, а затем по убыванию суммы суточных.
Вывести фамилию с инициалами и общую сумму суточных, полученных за все командировки для тех сотрудников, которые были в командировках больше чем 3 раза, в отсортированном по убыванию сумм суточных виде. Последний столбец назвать Сумма .
б) (Материк= ‘С.Америка‘ ИЛИ Материк = ‘Ю. Америка‘
2) Дайте ответы на следующие вопросы:
а) Какую строку будет занимать запись, содержащая данные о России, после сортировки по убыванию значения поля «Площадь»?
б) Какую строку будет занимать запись, содержащая данные о России, после сортировки по убыванию значения поля «Население»?
в) Какую строку будет занимать запись, содержащая данные о России, после сортировки по убыванию значения поля «Процент»?
г) Какую строку будет занимать запись, содержащая данные о России, после сортировки сначала по убыванию значения поля «Дата», а затем по убыванию значения поля «Процент»?
д) Какая запись будет занимать первую строку после сортировки сначала по убыванию значения поля «Дата», а затем по возрастанию значения поля «Материк»?
Ответы:
1) Укажите количество и номера записей, удовлетворяющих условию:
а) Количество: 7. Номера: 1, 2, 7, 10, 11, 12, 13
б) Количество: 3. Номера: 2, 9, 13
в) Количество: 2. Номера: 8, 12
г) Количество: 7. Номера: 1, 6, 7, 8, 10, 11, 12
д) Количество: 7. Номера: 2, 3, 4, 7, 9, 14, 16
2) Дайте ответы на следующие вопросы:
а) 1
б) 7
в) 8
г) 8
д) 5, Египет