Вывести список сотрудников которые получают зарплату больше чем их начальник sql
Наш вариант теста на знание SQL
У нас, как и во многих других организациях, проводится тестирование соискателей при поступлении их на работу. Основу тестирования составляет устное собеседование, но в некоторых случаях, даются также практические задания. Несколько дней назад, Руководство попросило меня подготовить набор задач на знание SQL.
Разумеется, я постарался сделать задания не слишком сложными. Уровень соискателей различен и задачи, на мой взгляд, должны быть составлены таким образом, чтобы по результатам их решения можно было судить о том, насколько хорошо испытуемый знает предмет.
Также, не имело смысла давать задания на знание каких-либо особенностей тех или иных СУБД. Мы в работе используем Oracle, но это не должно создавать трудностей для соискателей знающих, например, только MS SQL или PostgreSQL. Таким-образом, использование платформо-зависимых решений не возбраняется, но и не является ожидаемым при решении задач.
Для проведения тестирования, в Oracle 11g была развернута схема, содержащая следующие таблицы:
Требовалось составить SQL-запросы, для решения следующих пяти заданий:
Вывести список сотрудников, получающих заработную плату большую чем у непосредственного руководителя
Вывести список сотрудников, получающих максимальную заработную плату в своем отделе
Вывести список ID отделов, количество сотрудников в которых не превышает 3 человек
Вывести список сотрудников, не имеющих назначенного руководителя, работающего в том-же отделе
Найти список ID отделов с максимальной суммарной зарплатой сотрудников
Не требовалось искать в каком-либо смысле оптимальное решение. Единственное требование: запрос должен возвращать правильный ответ на любых входных данных. Задания разрешалось решать в любом порядке, без ограничения времени. При правильном решении всех заданий, предлагалось следующее задание повышенной сложности:
Составить SQL-запрос, вычисляющий произведение вещественных значений, содержащихся в некотором столбце таблицы
SQL. Занимательные задачки
Вот уже более 3-х лет я преподаю SQL в разных тренинг центрах, и одним из моих наблюдений является то, что студенты осваивают и понимают SQL лучше, если ставить перед ними задачу, а не просто рассказывать о возможностях и теоретических основах.
В этой статье я поделюсь с вами своим списком задач, которые я даю студентам в качестве домашнего задания и над которыми мы проводим разного рода брейнстормы, что приводит к глубокому и четкому пониманию SQL.
SQL (ˈɛsˈkjuˈɛl; англ. structured query language — «язык структурированных запросов») — декларативный язык программирования, применяемый для создания, модификации и управления данными в реляционной базе данных, управляемой соответствующей системой управления базами данных. Подробнее…
Почитать об SQL можно из разных источников.
Данная статья не преследует цели обучить вас SQL с нуля.
Будем использовть всем известную схему HR в Oracle с ее таблицами (Подробнее):
Отмечу что мы будем рассматривать только задачи на SELECT. Тут нет задач на DML и DDL.
Задачи
Restricting and Sorting Data
Таблица Employees. Получить список с информацией обо всех сотрудниках
leborchuk
About Oracle RDBMS, Essbase and other big data stuff
Recent Posts
Archives
Categories
Одна задачка на SQL
В феврале 2013 года мне посчастливилось участвовать в жюри региональных финалов олимпиады Oracle ИТ-Планета
Все региональные этапы проходят заочно, в том числе и для жюри. В моем распоряжении были номера участников и их решения. Необходимо было оценить корректность решений и лично удостовериться, что решения, признанные неверными автоматической проверкой, действительно неверны. Впечатлений масса, но я хотел бы рассказать об одной классической задаче и ее решениях. Вернее, об ошибках, которые можно допустить при решении, казалось бы, избитой задачи.
Итак, одно из заданий было сформулировано так:
Одной командой SELECT вывести список сотрудников, которым установлен оклад больший, чем средний оклад по подразделению компании, к которому они приписаны.
Сведения о сотрудниках, для которых неизвестно к какому подразделению они приписаны, выводить не нужно.
В результат вывести 5 (пять) столбцов:
1. Идентификатор сотрудника
2. Фамилию сотрудника
3. Имя сотрудника
4. Оклад, установленный сотруднику
5. Идентификатор подразделения, к которому приписан сотрудник
Результат отсортировать:
1. По окладу, установленный сотруднику (по убыванию)
2. По фамилии сотрудника (по возрастанию)
3. По имени сотрудника (по возрастанию)
4. По идентификатору сотрудника (по возрастанию)
Решением задачи является запрос:
SELECT employee_id, last_name, first_name, salary, department_id
FROM employees E
WHERE salary > (SELECT AVG(salary) FROM employees X
WHERE E.department_id = x.department_id)
ORDER BY salary DESC, last_name, first_name, employee_id;
Вообще говоря, запрос можно сформулировать множеством способов, например, применив преобразование устранение вложенности подзапросов или используя аналитические функции. Однако интересно, сколько и какие ошибки можно допустить в решении этой набившей оскомину задачи? Я, конечно, предполагал, как можно решить эту задачу неправильно, но такого количества различных неверных решений не ожидал. 🙂
1. Один из самых популярных вариантов. Невнимательность при чтении условия задачи
SELECT employee_id, last_name, first_name, salary, department_id
FROM employees
where salary > (select avg(emp.salary)
from employees emp)
order by salary desc, last_name, first_name, employee_id
Решение выглядит весьма правдоподобно, но, к сожалению, оно находит ответ на другую задачу – вывести список сотрудников с зарплатой выше средней по компании, а не по подразделению.
2. Невнимательность при написании запроса
with avg_sal as
(select department_id, avg(salary) avg_salary
from employees
group by department_id
)
select employee_id
, last_name
, first_name
, salary
, e.department_id
from employees e
inner join avg_sal
on e.department_id = avg_sal.department_id
where salary > avg_salary
order by salary, last_name, first_name, employee_id
Аналогично решению выше, это решение также выглядит весьма правдоподобно, и даже выводит верный список работников. Увы, оно неверно, т.к. во фразе ORDER BY перепутан порядок сортировки по полю salary: ASC вместо DESC. На удивление, подобного рода ошибок было очень много: перепутан порядок сортировки, поля выборки, порядок полей в сортировке, при в целом верном решении задачи. Здесь можно дать только один совет: тщательнее читать условие задачи.
3. Использование таблицы с похожими данными
SELECT e.employee_id, e.LAST_name, e.first_name, e.salary, e.department_id
FROM employees e, jobs j
WHERE e.department_id IS NOT NULL
AND j.job_id=e.job_id
AND e.salary>(j.min_salary+j.max_salary)/2
ORDER BY 4 DESC, 2,3,1 ASC;
Очевидно, таблица Jobs никак не связана с окладом сотрудника и условием задачи. Сложно сказать, почему она была использована для решения задачи. Вероятно, на написание этого запроса натолкнула неверная интерпретация схема данных.
4. Неверная группировка
С первого взгляда в этом запросе есть все признаки верного решения: наличие группировки, AVG, Join. Проблема в полях группировки. Их слишком много. Например, присутствует первичный ключ таблицы, а это значит, что фактически никакой группировки выполняться не будет, как и вычисления средней зарплаты по отделу. В результате выбираются все сотрудники, кроме тех, у которых минимальная зарплата по подразделению.
5. Неверный предикат сравнения
Это решение выбирает чуть больше записей, чем требуется. Проблема в предикате сравнения: “>=” вместо “>”, что означает “не меньше средней по отделу”, а должно быть строго больше по условию задачи.
6. Неправильное использование аналитики
SELECT EMPLOYEE_ID,
LAST_NAME,
FIRST_NAME,
SALARY,
DEPARTMENT_ID FROM
(SELECT EMPLOYEE_ID,
LAST_NAME,
FIRST_NAME,
SALARY,
DEPARTMENT_ID,
SUM(salary) OVER (PARTITION BY department_id ORDER BY salary
RANGE UNBOUNDED PRECEDING) lcsum,
COUNT(salary) OVER (PARTITION BY department_id ORDER BY salary
RANGE UNBOUNDED PRECEDING) lcount
FROM employees
ORDER BY manager_id, last_name, salary)
WHERE salary > lcsum/lcount
AND DEPARTMENT_ID IS NOT NULL
ORDER BY SALARY DESC,
LAST_NAME,
FIRST_NAME,
EMPLOYEE_ID;
В этом решении неправильно записано условие вычисления суммы аналитической функцией. При такой записи получается нарастающий итог. Т.е. SUM(salary) OVER (PARTITION BY department_id) возвращает сумму по всему департаменту, а SUM(salary) OVER (PARTITION BY department_id ORDER BY salary ) возвращает нарастающий итог по сумме зарплаты для каждого департамента. В итоге запрос выводит неизвестно что.
7. Неожиданная сортировка
Удивительно, но этот запрос работает 🙂 Т.е. понятно, что запрос написан неправильно и его создатель, скорее всего, слабо знаком с синтаксисом и функциями языка. Однако секция сортировки выглядит оригинально. И в чем-то даже логично. Хотя order by – last_name написать уже нельзя, last_name – строковый тип данных.
Здесь дело в парсере SQL. Официального подтверждения этому нет, но похоже, что парсер Oracle просто выкидывает лидирующие плюсы в выражениях. Так что их можно безболезненно писать 🙂
Было еще много вариантов с опечатками, но их рассматривать не интересно. Они просто не были должным образом протестированы перед отправкой.
В заключение хотелось бы привести пару правильных решений. Которые в нормальной ситуации также являются плохим решением задачи.
8. Плохой стиль
with q1 as (select department_id, avg(salary) s1 from employees where department_id is not null group by department_id)
select e.employee_id, e.last_name, e.first_name, e.salary, e.department_id from employees e, q1 where e.department_id=q1.department_id and e.salary>q1.s1 order by 4 desc, 2 asc, 3 asc, 1 asc
Поддерживать это решение невозможно. Дело не только в том, что очень сложно понять, что же конкретно делает этот запрос. Еще сложнее проверить, что он делает именно то, что нужно. Это очень наглядно видно во время подобного рода соревнований. Были еще несколько похожих, но неправильных вариантов. Этому решению просто повезло. Скорее всего, этот запрос не тестировали и просто случайно удалось написать его с первого раза правильно. Остальным же решениям не повезло. В нескольких похожих запросах были опечатки (это нормально), но стиль оформления запроса не позволил их увидеть перед отправкой на проверку.
Впрочем, это тема для отдельного разговора.
9. Лишние соединения в запросе
with sal as
(select d.department_id did, avg(salary) avgs from
departments d join employees e on d.department_id=e.department_id
group by e.department_id)
select e.employee_id, e.last_name, e.first_name, e.salary, d.department_id
from departments d join employees e on d.department_id=e.department_id
JOIN sal on sal.did=d.department_id
where e.salary>sal.avgs
order by salary desc, last_name, first_name, employee_id
В этом простом запросе умудрились сделать 2 лишних соединения. На эту тему уже было сказано достаточно много и в будущем будет сказано не меньше, но от базы данных требуется не только получить правильный результат, но и сделать это достаточно оперативно. Не стоит нагружать БД бесполезной работой и таким образом писать запросы. Иначе придется покупать Exadata 🙂
P.S. Эталонное решение также не идеально: таблица сотрудников сканируется дважды. Его можно улучшить, используя аналитику. Например, так:
Что позволит добиться плана выполнения с единичным доступом к таблице сотрудников:
———————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————-
| 0 | SELECT STATEMENT | | 107 | 8346 | 5 (40)| 00:00:01 |
| 1 | SORT ORDER BY | | 107 | 8346 | 5 (40)| 00:00:01 |
|* 2 | VIEW | | 107 | 8346 | 4 (25)| 00:00:01 |
| 3 | WINDOW SORT | | 107 | 2782 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMPLOYEES | 107 | 2782 | 3 (0)| 00:00:01 |
———————————————————————————-
Predicate Information (identified by operation id):
—————————————————
Функциональная СУБД
Категории
Свежие записи
Наши услуги
Мир баз данных давно захвачен реляционными СУБД, в которых используется язык SQL. Настолько сильно, что появляющиеся разновидности называют NoSQL. Им удалось отбить себе определенное место на этом рынке, но реляционные СУБД умирать не собираются, и продолжают активно использоваться для своих целей.
В этой статье я хочу описать концепцию функциональной базы данных. Для лучшего понимания, я буду это делать путем сравнения с классической реляционной моделью. В качестве примеров будут использоваться задачи из различных тестов по SQL, найденные в интернете.
Введение
Реляционные базы данных оперируют таблицами и полями. В функциональной базе данных вместо них будут использоваться классы и функции соответственно. Поле в таблице с N ключами будет представлено как функция от N параметров. Вместо связей между таблицами будут использоваться функции, которые возвращают объекты класса, на который идет связь. Вместо JOIN будет использоваться композиция функций.
Прежде чем перейти непосредственно к задачам, опишу задание доменной логики. Для DDL я буду использовать синтаксис PostgreSQL. Для функциональной свой синтаксис.
Таблицы и поля
Простой объект Sku с полями наименование и цена:
CLASS Sku;
name = DATA STRING[100] (Sku);
price = DATA NUMERIC[10,5] (Sku);
Мы объявляем две функции, которые принимают на вход один параметр Sku, и возвращают примитивный тип.
Предполагается, что в функциональной СУБД у каждого объекта будет некий внутренний код, который автоматически генерируется, и к которому при необходимости можно обратиться.
Зададим цену для товара / магазина / поставщика. Она может изменяться со временем, поэтому добавим в таблицу поле время. Объявление таблиц для справочников в реляционной базе данных пропущу, чтобы сократить код:
CLASS Sku;
CLASS Store;
CLASS Supplier;
dateTime = DATA DATETIME (Sku, Store, Supplier);
price = DATA NUMERIC[10,5] (Sku, Store, Supplier);
Индексы
Для последнего примера построим индекс по всем ключам и дате, чтобы можно было быстро находить цену на определенное время.
INDEX Sku sk, Store st, Supplier sp, dateTime(sk, st, sp);
Задачи
Начнем с относительно простых задач, взятых из соответствующей статьи на Хабре.
Сначала объявим доменную логику (для реляционной базы это сделано непосредственно в приведенной статье).
CLASS Department;
name = DATA STRING[100] (Department);
CLASS Employee;
department = DATA Department (Employee);
chief = DATA Employee (Employee);
name = DATA STRING[100] (Employee);
salary = DATA NUMERIC[14,2] (Employee);
Задача 1.1
Вывести список сотрудников, получающих заработную плату большую чем у непосредственного руководителя.
SELECT name(Employee a) WHERE salary(a) > salary(chief(a));
Задача 1.2
Вывести список сотрудников, получающих максимальную заработную плату в своем отделе
maxSalary ‘Максимальная зарплата’ (Department s) =
GROUP MAX salary(Employee e) IF department(e) = s;
SELECT name(Employee a) WHERE salary(a) = maxSalary(department(a));
// или если «заинлайнить»
SELECT name(Employee a) WHERE
salary(a) = maxSalary(GROUP MAX salary(Employee e) IF department(e) = department(a));
Обе реализации эквивалентны. Для первого случая в реляционной базе можно использовать CREATE VIEW, который таким же образом сначала посчитает для конкретного отдела максимальную зарплату в нем. В дальнейшем я для наглядности буду использовать первый случай, так как он лучше отражает решение.
Задача 1.3
Вывести список ID отделов, количество сотрудников в которых не превышает 3 человек.
countEmployees ‘Количество сотрудников’ (Department d) =
GROUP SUM 1 IF department(Employee e) = d;
SELECT Department d WHERE countEmployees(d)
Задача 1.4
Вывести список сотрудников, не имеющих назначенного руководителя, работающего в том-же отделе.
SELECT name(Employee a) WHERE NOT (department(chief(a)) = department(a));
Задача 1.5
Найти список ID отделов с максимальной суммарной зарплатой сотрудников.
salarySum ‘Максимальная зарплата’ (Department d) =
GROUP SUM salary(Employee e) IF department(e) = d;
maxSalarySum ‘Максимальная зарплата отделов’ () =
GROUP MAX salarySum(Department d);
SELECT Department d WHERE salarySum(d) = maxSalarySum();
Задача 2.1
Какие продавцы продали в 1997 году более 30 штук товара №1?
Доменная логика (как и раньше на РСУБД пропускаем объявление):
CLASS Employee ‘Продавец’;
lastName ‘Фамилия’ = DATA STRING[100] (Employee);
CLASS Product ‘Продукт’;
id = DATA INTEGER (Product);
name = DATA STRING[100] (Product);
CLASS Order ‘Заказ’;
date = DATA DATE (Order);
employee = DATA Employee (Order);
CLASS Detail ‘Строка заказа’;
order = DATA Order (Detail);
product = DATA Product (Detail);
quantity = DATA NUMERIC[10,5] (Detail);
sold (Employee e, INTEGER productId, INTEGER year) =
GROUP SUM quantity(OrderDetail d) IF
employee(order(d)) = e AND
id(product(d)) = productId AND
extractYear(date(order(d))) = year;
SELECT lastName(Employee e) WHERE sold(e, 1, 1997) > 30;
Задача 2.2
Для каждого покупателя (имя, фамилия) найти два товара (название), на которые покупатель потратил больше всего денег в 1997-м году.
Расширяем доменную логику из предыдущего примера:
CLASS Customer ‘Клиент’;
contactName ‘ФИО’ = DATA STRING[100] (Customer);
customer = DATA Customer (Order);
unitPrice = DATA NUMERIC[14,2] (Detail);
discount = DATA NUMERIC[6,2] (Detail);
Оператор PARTITION работает по следующему принципу: он суммирует выражение, указанное после SUM (здесь 1), внутри указанных групп (здесь Customer и Year, но может быть любое выражение), сортируя внутри групп по выражениям, указанным в ORDER (здесь bought, а если равны, то по внутреннему коду продукта).
Задача 2.3
Сколько товаров нужно заказать у поставщиков для выполнения текущих заказов.
Опять расширяем доменную логику:
CLASS Supplier ‘Поставщик’;
companyName = DATA STRING[100] (Supplier);
supplier = DATA Supplier (Product);
unitsInStock ‘Остаток на складе’ = DATA NUMERIC[10,3] (Product);
reorderLevel ‘Норма продажи’ = DATA NUMERIC[10,3] (Product);
Задача со звездочкой
И последней пример лично от меня. Есть логика социальной сети. Люди могут дружить друг с другом и нравится друг другу. С точки зрения функциональной базы данных это будет выглядеть следующим образом:
CLASS Person;
likes = DATA BOOLEAN (Person, Person);
friends = DATA BOOLEAN (Person, Person);
Необходимо найти возможных кандидатов на дружбу. Более формализовано нужно найти всех людей A, B, C таких, что A дружит с B, а B дружит с C, A нравится C, но A не дружит с C.
С точки зрения функциональной базы данных запрос будет выглядеть следующим образом:
SELECT Person a, Person b, Person c WHERE
likes(a, c) AND NOT friends(a, c) AND
friends(a, b) AND friends(b, c);
Читателю предлагается самостоятельно решить эту задачу на SQL. Предполагается, что друзей гораздо меньше чем тех, кто нравится. Поэтому они лежат в отдельных таблицах. В случае успешного решения есть также задача с двумя звездочками. В ней дружба не симметрична. На функциональной базе данных это будет выглядеть так:
SELECT Person a, Person b, Person c WHERE
likes(a, c) AND NOT friends(a, c) AND
(friends(a, b) OR friends(b, a)) AND
(friends(b, c) OR friends(c, b));
UPD: решение задачи с первой и второй звездочкой от dss_kalika :
Заключение
Следует отметить, что приведенный синтаксис языка — это всего лишь один из вариантов реализации приведенной концепции. За основу был взят именно SQL, и целью было, чтобы он максимально был похож на него. Конечно, кому-то могут не понравится названия ключевых слов, регистры слов и прочее. Здесь главное — именно сама концепция. При желании можно сделать и C++, и Python подобный синтаксис.
Описанная концепция базы данных, на мой взгляд обладает следующими преимуществами:
CONSTRAINT sold(Employee e, 1, 2019) > 100 IF name(e) = ‘Петя’ MESSAGE ‘Что-то Петя продает слишком много одного товара в 2019 году’;
Добавить комментарий Отменить ответ
Для отправки комментария вам необходимо авторизоваться.
SQL. Занимательные задачки
Категории
Свежие записи
Наши услуги
Вот уже более 3-х лет я преподаю SQL в разных тренинг центрах, и одним из моих наблюдений является то, что студенты осваивают и понимают SQL лучше, если ставить перед ними задачу, а не просто рассказывать о возможностях и теоретических основах.
В этой статье я поделюсь с вами своим списком задач, которые я даю студентам в качестве домашнего задания и над которыми мы проводим разного рода брейнстормы, что приводит к глубокому и четкому пониманию SQL.
SQL (ˈɛsˈkjuˈɛl; англ. structured query language — «язык структурированных запросов») — декларативный язык программирования, применяемый для создания, модификации и управления данными в реляционной базе данных, управляемой соответствующей системой управления базами данных. Подробнее…
Будем использовть всем известную схему HR в Oracle с ее таблицами ( Подробнее ):
Отмечу что мы будем рассматривать только задачи на 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. Получить список всех сотрудников и информацию о наличии бонусов к зарплате (Yes/No)
Решение
Таблица Employees. Получить уровень зарплаты каждого сотрудника: Меньше 5000 считается Low level, Больше или равно 5000 и меньше 10000 считается Normal level, Больше иои равно 10000 считается High level
Решение
Таблица Countries. Для каждой страны показать регион в котором он находится: 1-Europe, 2-America, 3-Asia, 4-Africa (без Join)
Решение
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, пишите в коментариях, добавлю в список.
Добавить комментарий Отменить ответ
Для отправки комментария вам необходимо авторизоваться.