Выполнение запросов.
Запишем запрос, выведем данные из таблицы “prodav”, относящиеся к городам Бабушкин и Луга.
mysql> SELECT * FROM prodav WHERE city_prodav=»Бабушкин» OR city_prodav=»Луга»;
В таблице prodav, выведем строки, для которых 0.10<comiss<0.12
mysql> SELECT * FROM prodav WHERE comiss BETWEEN 0.1 AND 0.12;
Выведем записи из таблицы продавцов, для которых фамилия находится от А до Н.
mysql> SELECT * FROM prodav WHERE name_prodav BETWEEN ‘А%’ AND ‘Н%’;
Подсчитаем количество продавцов
mysql> SELECT COUNT(DISTINCT cod_prodav) FROM sale;
Определим стоимость продаж
mysql> SELECT SUM(cost_s) FROM sale;
Определим стоимость продаж у продавца Птицына
mysql> SELECT SUM(cost_s) FROM sale WHERE (cod_prodav=(SELECT cod_prodav FROM prodav WHERE name_prodav=»Птицын»));
Выведем коды сделок, где стоимость сделки превысила среднее значение.
mysql> SELECT cod_s FROM sale WHERE cost_s>(SELECT AVG(cost_s) FROM sale);
Выведем записи в таблице сделок, для которых стоимость сделок превышает среднее значение за 2005 и последующие годы.
mysql> SELECT * FROM sale WHERE cost_s>(SELECT AVG(cost_s) FROM sale WHERE YEAR(date_s)>2004);
Выведем для каждого продавца, какие покупатели живут в одном городе с продавцом.
mysql> SELECT prodav.name_prodav, pokup.name_pokup, pokup.city_pokup FROM prodav
, pokup WHERE prodav.city_prodav=pokup.city_pokup;
Выведем у каждого продавца, какие покупатели в каждом городе у него покупают.
Посчитайте количество городов в которых нет продавцов
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.
Помогите решить задачку по sql
Для всех стран в базе данных посчитать количество компаний со штаб квартирами в этой стране численность сотрудников в которых больше 1000 человек В результате должны быть только количество компаний и названия стран с населением более 1 миллиона человек и валовым продуктом более 10 миллиардов долларов, у которых суммарная выручка выбранных компаний составляет более 1 миллиарда долларов
Мой вариант:
( это я пытался выстроить компании с численность сотрудников > 1000 и доходом более 1ккк) Далее я так полагаю нужно получившийся список сравнить со списком (Countries ) и составить новый список и новый список сравнить со с писком (Cities ) и этот список будет ответом.
П.С. Хотелось бы получить не просто ответ но и логику выполнения такого задания.
Посчитайте количество городов в которых нет продавцов
Задания по Northwind
Упражнения по SQL: подсчитать количество различных значений города, отличных от NULL, для продавцов
- фактический – общее количество человек, которым нужен товар, и объем закупаемого товара;
- доступный – то количество человек, которые закупаются именно у этого продавца;
- потенциальный – те люди, которые закупаются именно у этого продавца, а также те, кому в ближайшей перспективе может также потребоваться этот продукт. Относительно таких потенциальных покупателей пока нет ясности, где именно они будут закупаться. Возможно, они приобретут товар у конкурентов.
- число потребителей, которым интересен продукт;
- количество единиц, которое один клиент покупает за один раз;
- частота, регулярность покупок;
- стоимость одной единицы товара.
- количество покупателей зависит от территории, где работает компания. За основу расчетов берется ключевой критерий ЦА. Например, компания производит комплектующие для автомобилей. Следовательно, клиентами могут стать только те, у кого есть авто. Необходимо узнать, сколько машин зарегистрировано или куплено в регионе. Однако численность ЦА зависит и от типа товара. Товары индивидуального пользования каждый клиент покупает для себя. Некоторые продукты можно использовать коллективно, например, холодильник покупается для всей семьи, хотя в семье может быть несколько заинтересованных людей;
- частота потребления товара определяется с помощью опросов. Есть товары, срок использования которых определяется их техническими характеристиками. Например, любой смартфон рано или поздно перестает получать обновления и его функциональность падает. Из-за этого пользователи вынуждены менять модель телефона. Также срок использования вещи определяется общепринятыми стандартами. Например, зубные щетки рекомендуется менять раз в 2–4 месяца;
- средняя стоимость покупки определяется, исходя из собственного прайс-листа и цен конкурентов. Если разброс цен на рынке слишком большой, можно также провести опрос целевой аудитории;
- объем приобретаемого продукта. Если товар уже запущен в продажу, можно проверить все чеки, в которых он фигурирует. Например, лимонад продается в разных объемах – 0,5; 1; 1,5 и 2 литра. По чекам будет ясно, какой объем предпочитают покупатели и сколько бутылок они берут одновременно.
- открытые официальные источники, например, данные органов госвласти, Таможенная служба, служба государственной статистики;
- готовые маркетинговые исследования;
- исследования, сделанные на заказ;
- данные из отраслевых журналов, СМИ, из докладов экспертов.
- время – период, в течение которого нужно отслеживать активность потребителей. Оптимально выбирать год или полгода, но если продукт сезонный, то и рассчитывать потенциальный объем следует именно на сезон;
- география – объем рынка будет отличаться в конкретном регионе или по всей стране;
- аудитория – если не сегментировать аудиторию на отдельные подгруппы, то результат расчетов будет необъективным. Правильнее рассчитывать объем рынка для каждой группы потребителей;
- единица измерения – если товар будет продаваться примерно по той же цене, что у конкурентов, оптимально вести расчеты в рублях. Если на рынке большие колебания цен, то и объем рынка лучше считать в единицах продукции.
- необходимо найти данные обо всех крупных конкурентах. В идеале выбранные компании покрывают спрос хотя бы на 80 %;
- взять за основу рыночные розничные цены;
- суммировать продажи – это будет потенциальный объем в единицах;
- умножить количество проданного товара на его стоимость и получить потенциальный объем рынка в рублях.
- сезонные товары. Сезонность зависит от региона, ведь в разных областях сезон длится по-разному;
- узкоспециализированные товары. Например, горнолыжное оборудование будет иметь разный спрос в горных районах и в равнинных районах;
- товары для конечных потребителей разной возрастной категории. Регионы отличаются по количеству детей, пенсионеров, мужчин или женщин;
- товары для В2В-сегмента. В разных городах находится разное количество потенциальных клиентов, да и специфика бизнес-процессов у них может отличаться даже от соседних областей.
- техническое усовершенствование товаров. Например, так произошло с объемом рынка кнопочных телефонов. Когда появились сенсорные модели, большая часть пользователей перешла на них и спрос на кнопочные телефоны упал;
- насыщенность рынка. Если товары можно использовать долго (например, как мебель или крупную бытовую технику), то есть риск быстро охватить всех потребителей и наблюдать резкое снижение спроса;
- появление более бюджетных аналогов;
- экономические кризисы, в результате которых у потребителей снижаются доходы.
mysql> SELECT * FROM prodav WHERE comiss BETWEEN 0.1 AND 0.12;
mysql> SELECT * FROM prodav WHERE name_prodav BETWEEN ‘А%’ AND ‘Н%’;
mysql> SELECT COUNT(DISTINCT cod_prodav) FROM sale;
mysql> SELECT SUM(cost_s) FROM sale;
mysql> SELECT SUM(cost_s) FROM sale WHERE (cod_prodav=(SELECT cod_prodav FROM prodav WHERE name_prodav=»Птицын»));
mysql> SELECT cod_s FROM sale WHERE cost_s>(SELECT AVG(cost_s) FROM sale);
mysql> SELECT * FROM sale WHERE cost_s>(SELECT AVG(cost_s) FROM sale WHERE YEAR(date_s)>2004);