Решение задач 1. 📝 Аналитик в компании “Рога и Ко” за день написал некот в Острове

Решение задач 1. 📝 Аналитик в компании “Рога и Ко” за день написал некот в Острове Аналитика

Sql. занимательные задачки

Здравствуй, Хабр!

Вот уже более 3-х лет я преподаю SQL в разных тренинг центрах, и одним из моих наблюдений является то, что студенты осваивают и понимают SQL лучше, если ставить перед ними задачу, а не просто рассказывать о возможностях и теоретических основах.

В этой статье я поделюсь с вами своим списком задач, которые я даю студентам в качестве домашнего задания и над которыми мы проводим разного рода брейнстормы, что приводит к глубокому и четкому пониманию SQL.

Решение задач 1. 📝 Аналитик в компании “Рога и Ко” за день написал некот в Острове

SQL (ˈɛsˈkjuˈɛl; англ. structured query language — «язык структурированных запросов») — декларативный язык программирования, применяемый для создания, модификации и управления данными в реляционной базе данных, управляемой соответствующей системой управления базами данных. Подробнее…

Почитать об SQL можно из разных источников.
Данная статья не преследует цели обучить вас SQL с нуля.

Итак, поехали.

Будем использовть всем известную схему HR в Oracle с ее таблицами (Подробнее):

Решение задач 1. 📝 Аналитик в компании “Рога и Ко” за день написал некот в Острове
Отмечу что мы будем рассматривать только задачи на SELECT. Тут нет задач на DML и DDL.

Restricting and Sorting Data

Таблица Employees. Получить список с информацией обо всех сотрудниках

Таблица Employees. Получить список всех сотрудников с именем ‘David’

Таблица Employees. Получить список всех сотрудников с job_id равным ‘IT_PROG’

Таблица Employees. Получить список всех сотрудников из 50го отдела (department_id) с зарплатой(salary), большей 4000

Таблица Employees. Получить список всех сотрудников из 20го и из 30го отдела (department_id)

Таблица Employees. Получить список всех сотрудников у которых последняя буква в имени равна ‘a’

Таблица Employees. Получить список всех сотрудников из 50го и из 80го отдела (department_id) у которых есть бонус (значение в колонке commission_pct не пустое)

Таблица Employees. Получить список всех сотрудников у которых в имени содержатся минимум 2 буквы ‘n’

Таблица Employees. Получить список всех сотрудников у которых длина имени больше 4 букв

Таблица Employees. Получить список всех сотрудников у которых зарплата находится в промежутке от 8000 до 9000 (включительно)

Таблица Employees. Получить список всех сотрудников у которых в имени содержится символ ‘%’

Таблица Employees. Получить список всех ID менеджеров

Таблица Employees. Получить список работников с их позициями в формате: Donald(sh_clerk)

Using Single-Row Functions to Customize Output

Таблица Employees. Получить список всех сотрудников у которых длина имени больше 10 букв

Таблица Employees. Получить список всех сотрудников у которых в имени есть буква ‘b’ (без учета регистра)

Таблица Employees. Получить список всех сотрудников у которых в имени содержатся минимум 2 буквы ‘a’

Таблица Employees. Получить список всех сотрудников зарплата которых кратна 1000

Таблица Employees. Получить первое 3х значное число телефонного номера сотрудника если его номер в формате ХХХ.ХХХ.ХХХХ

Таблица Departments. Получить первое слово из имени департамента для тех у кого в названии больше одного слова

Таблица Employees. Получить имена сотрудников без первой и последней буквы в имени

Таблица Employees. Получить список всех сотрудников у которых последняя буква в имени равна ‘m’ и длинной имени большей 5ти

Таблица Dual. Получить дату следующей пятницы

Таблица Employees. Получить список всех сотрудников которые работают в компании больше 17 лет

Таблица Employees. Получить список всех сотрудников у которых последня цифра телефонного номера нечетная и состоит из 3ех чисел разделенных точкой

Таблица Employees. Получить список всех сотрудников у которых в значении job_id после знака ‘_’ как минимум 3 символа но при этом это значение после ‘_’ не равно ‘CLERK’

Таблица Employees. Получить список всех сотрудников заменив в значении PHONE_NUMBER все ‘.’ на ‘-‘

Using Conversion Functions and Conditional Expressions

Таблица Employees. Получить список всех сотрудников которые пришли на работу в первый день месяца (любого)

Таблица Employees. Получить список всех сотрудников которые пришли на работу в 2008ом году

Таблица DUAL. Показать завтрашнюю дату в формате: Tomorrow is Second day of January

Таблица Employees. Получить список всех сотрудников и дату прихода на работу каждого в формате: 21st of June, 2007

Таблица Employees. Получить список работников с увеличенными зарплатами на 20%. Зарплату показать со знаком доллара

Таблица Employees. Получить список всех сотрудников которые приши на работу в феврале 2007го года.

Таблица DUAL. Вывезти актуальную дату, секунда, минута, час, день, месяц, год

Таблица Employees. Получить список всех сотрудников с полными зарплатами (salary commission_pct(%)) в формате: $24,000.00

Таблица Employees. Получить список всех сотрудников и информацию о наличии бонусов к зарплате (Yes/No)

Таблица Employees. Получить уровень зарплаты каждого сотрудника: Меньше 5000 считается Low level, Больше или равно 5000 и меньше 10000 считается Normal level, Больше иои равно 10000 считается High level

Таблица Countries. Для каждой страны показать регион в котором он находится: 1-Europe, 2-America, 3-Asia, 4-Africa (без Join)

Решение
SELECT country_name country,
       DECODE (region_id,
               1, 'Europe',
               2, 'America',
               3, 'Asia',
               4, 'Africa',
               'Unknown')
           region
  FROM countries;

SELECT country_name
           country,
       CASE region_id
           WHEN 1 THEN 'Europe'
           WHEN 2 THEN 'America'
           WHEN 3 THEN 'Asia'
           WHEN 4 THEN 'Africa'
           ELSE 'Unknown'
       END
           region
  FROM countries;

Reporting Aggregated Data Using the Group Functions

Таблица Employees. Получить репорт по department_id с минимальной и максимальной зарплатой, с ранней и поздней датой прихода на работу и с количествов сотрудников. Сорировать по количеству сотрудников (по убыванию)

Таблица Employees. Сколько сотрудников имена которых начинается с одной и той же буквы? Сортировать по количеству. Показывать только те где количество больше 1

Таблица Employees. Сколько сотрудников которые работают в одном и тоже отделе и получают одинаковую зарплату?

Таблица Employees. Получить репорт сколько сотрудников приняли на работу в каждый день недели. Сортировать по количеству

Таблица Employees. Получить репорт сколько сотрудников приняли на работу по годам. Сортировать по количеству

Таблица Employees. Получить количество департаментов в котором есть сотрудники

Таблица Employees. Получить список department_id в котором работают больше 30 сотрудников

Таблица Employees. Получить список department_id и округленную среднюю зарплату работников в каждом департаменте.

Таблица Countries. Получить список region_id сумма всех букв всех country_name в котором больше 60ти

Таблица Employees. Получить список department_id в котором работают работники нескольких (>1) job_id

Таблица Employees. Получить список manager_id у которых количество подчиненных больше 5 и сумма всех зарплат его подчиненных больше 50000

Таблица Employees. Получить список manager_id у которых средняя зарплата всех его подчиненных находится в промежутке от 6000 до 9000 которые не получают бонусы (commission_pct пустой)

Таблица Employees. Получить максимальную зарплату из всех сотрудников job_id которыз заканчивается на слово ‘CLERK’

Таблица Employees. Получить максимальную зарплату среди всех средних зарплат по департаменту

Таблица Employees. Получить количество сотрудников с одинаковым количеством букв в имени. При этом показать только тех у кого длина имени больше 5 и количество сотрудников с таким именем больше 20. Сортировать по длинне имени

Displaying Data from Multiple Tables Using Joins

Таблица Employees, Departaments, Locations, Countries, Regions. Получить список регионов и количество сотрудников в каждом регионе

Таблица Employees, Departaments, Locations, Countries, Regions. Получить детальную информацию о каждом сотруднике:

First_name, Last_name, Departament, Job, Street, Country, Region

Таблица Employees. Показать всех менеджеров которые имеют в подчинении больше 6ти сотрудников

Таблица Employees. Показать всех сотрудников которые ни кому не подчиняются

Таблица Employees, Job_history. В таблице Employee хранятся все сотрудники. В таблице Job_history хранятся сотрудники которые покинули компанию. Получить репорт о всех сотрудниках и о его статусе в компании (Работает или покинул компанию с датой ухода)

Пример:

first_name | status

Jennifer | Left the company at 31 of December, 2006

Clara | Currently Working

Таблица Employees, Departaments, Locations, Countries, Regions. Получить список сотрудников которые живут в Europe (region_name)

Таблица Employees, Departaments. Показать все департаменты в которых работают больше 30ти сотрудников

Таблица Employees, Departaments. Показать всех сотрудников которые не состоят ни в одном департаменте

Таблица Employees, Departaments. Показать все департаменты в которых нет ни одного сотрудника

Таблица Employees. Показать всех сотрудников у которых нет ни кого в подчинении

Таблица Employees, Jobs, Departaments. Показать сотрудников в формате: First_name, Job_title, Department_name.

Пример:

First_name | Job_title | Department_name

Donald | Shipping | Clerk Shipping

Таблица Employees. Получить список сотрудников менеджеры которых устроились на работу в 2005ом году но при это сами эти работники устроились на работу до 2005 года

Таблица Employees. Получить список сотрудников менеджеры которых устроились на работу в январе месяце любого года и длина job_title этих сотрудников больше 15ти символов

Using Subqueries to Solve Queries

Таблица Employees. Получить список сотрудников с самым длинным именем.

Таблица Employees. Получить список сотрудников с зарплатой большей средней зарплаты всех сотрудников.

Таблица Employees, Departments, Locations. Получить город в котором сотрудники в сумме зарабатывают меньше всех.

Таблица Employees. Получить список сотрудников у которых менеджер получает зарплату больше 15000.

Таблица Employees, Departaments. Показать все департаменты в которых нет ни одного сотрудника

Таблица Employees. Показать всех сотрудников которые не являются менеджерами

Таблица Employees. Показать всех менеджеров которые имеют в подчинении больше 6ти сотрудников

Таблица Employees, Departaments. Показать сотрудников которые работают в департаменте IT

Таблица Employees, Jobs, Departaments. Показать сотрудников в формате: First_name, Job_title, Department_name.

Пример:

First_name | Job_title | Department_name

Donald | Shipping | Clerk Shipping

Таблица Employees. Получить список сотрудников менеджеры которых устроились на работу в 2005ом году но при это сами эти работники устроились на работу до 2005 года

Таблица Employees. Получить список сотрудников менеджеры которых устроились на работу в январе месяце любого года и длина job_title этих сотрудников больше 15ти символов

На этом пока всё.

Надеюсь, задачи были интересными и увлекательными.
Буду по возможности дополнять этот список задач.
Также буду рад любым замечаниям и предложениям.

P.S.: Если кому то в голову придет интересная задача на SELECT, пишите в коментариях, добавлю в список.

Спасибо.

Задача

Задача. Выберите из таблицы workers все записи так, чтобы вместо id было workersId,
вместо login – workersLogin, вместо salary – workersSalary.

Решение:

Задачи для решения

Во всех задачах ниже
считайте, что таблица workers имеет поля id, login, salary, age, date, description
(и другие при необходимости).

На in


Выберите из таблицы workers записи с id равным 1, 2, 3, 7, 9,
и логином, равным ‘user’, ‘admin’, ‘ivan’ и зарплатой больше 300.

На работу с частью даты


Выберите из таблицы workers все записи за
следующие дни любого месяца: 1, 7, 11, 12, 15, 19, 21, 29.

Решение задач аналитик 📝 в компании “рога и ко” за день написал некоторо в зеленокумске

Решение задач решить 📝 4 задачи математическая статистика в кяхте

Аналитик в компании “Рога и Ко” за день написал некоторое количество SQL-запросов.
Все запросы можно охарактеризовать количественной характеристикой сложности
выполнения – условная “память”.
Три “тяжелейших” (7/20 общей затраченной памяти) запроса превысили максимальное
время выполнения и были отключены системой.
Три “легчайших” (5/13 затраченной памяти всех оставшихся запросов) выполнялись менее
10 минут каждый. Остальные запросы выполнялись менее получаса каждый.
1.1 Найдите общее количество запросов, которое написал аналитик
1.2 Дайте оценку на среднее время успешно выполненного запроса
2 В команде есть два стажера-аналитика. Правильный ответ каждый из стажеров
получает в 14 из 17 случаев. Чтобы быть более уверенным в важном решении, менеджер
решил дать одну и ту же задачу сразу обоим аналитикам: если оба получают одинаковый
ответ, то менеджер его использует, а если ответы разные – выберет один из них наугад.
Насколько такой способ повышает шансы менеджера принять верное решение?
3 В мешке лежат три кубика: 6-гранный, 12-гранный, 20-гранный. Мы достали один кубик
на удачу, подкинули его и на нем выпало 12.
1) Какова вероятность, что если мы так же достанем и подкинем один из
оставшихся в мешке кубиков, на нем выпадет меньше?
2) Как изменится ответ, если на первом кубике выпало 4 вместо 12?
4 Чтобы между пользователями Авито было больше доверия, а жизнь мошенников
стала сложнее, мы решили попробовать ввести систему отзывов: покупатель может
оставить отзыв на продавца. Отзыв может быть просто рейтингом (1-5 звездочек), а может
содержать дополнительно какой-то произвольный текст.
4.1 Предложите метрики, по которым можно будет следить за прогрессом такого
проекта и определять его успешность
4.2 Поскольку Авито не магазин, а площадка для связи покупателя и продавца, мы
в большинстве случаев не знаем, произошла ли в действительности сделка и
на каких условиях. Для большинства сделок последнее, что нам известно –
покупатель нажал кнопку просмотра телефона продавца или связался с ним в
чате. Также мы всегда знаем логин (привязанный к email и телефону) продавца,
но покупатель может быть незалогиненным. В связи с этим кажется, что есть
большой риск накрутки отзывов и недобросовестного использования системы:
например, профессиональные участники будут пытаться оставлять негативные
отзывы на своих конкурентов и хвалебные на себя. Предположим, что система
некоторое время уже работает и у нас есть данные по отзывам и всей
активности клиентов: что продавал, что искал, на каких объявлениях смотрел
телефоны и т.п. Как оценить масштабы накрутки, т.е. долю фальшивых
отзывов?

Дополнительный анализ:  Глава 11. НАЛОГОВОЕ СУДЕБНОЕ РАЗБИРАТЕЛЬСТВО И ЗАЩИТА ПРАВ НАЛОГОПЛАТЕЛЬЩИКОВ
Оцените статью
Аналитик-эксперт
Добавить комментарий