Как правильно решать задачи по sql

Как правильно решать задачи по sql

Сегодня у нас 223 участников (18 новых).
Решено задач на рейтинговом этапе: 11
(3 по SELECT и 8 по DML),
на обучающем этапе — 250

Практическое владение языком SQL

Сайт поможет каждому, кто хочет приобрести или повысить свои навыки в написании операторов манипуляции данными языка SQL. Суть обучения состоит в том, что вы сами пишете операторы, которые должны вернуть или изменить данные, требуемые заданием. При этом в случае неправильного ответа вы сможете узнать, какие данные возвращает правильный запрос, а также увидеть, что вернул ваш запрос. Кроме того, есть возможность выполнять любые операторы DML к имеющимся базам данных, отключив опцию проверки. Упражнения имеют разный уровень сложности (от 1 до 5), который проставлен во втором столбце списка упражнений. Предлагаются упражнения на выборку данных (оператор SELECT) и упражнения на модификацию данных (операторы INSERT, UPDATE, DELETE и MERGE). По результатам решения задач на сайте ведется рейтинг участников. При этом упражнения на выборку разбиты на три этапа: первый (4 упражнений) без контроля времени на выполнение отдельного задания, второй (начиная с 5 упражнения) — с контролем времени на выполнение каждого задания. На третьем этапе, который называется оптимизационным и начинается с задачи 139, требуется не только правильно решить задачу, но и время выполнения запроса должно быть соизмеримым с временем выполнения авторского решения.
Упражнения первого этапа доступны без регистрации, причем задания можно решать в любом порядке. Для выполнения остальных упражнений требуется регистрация. Регистрация бесплатна, как и все остальные сервисы сайта. В третьем столбце списка упражнений будут отмечаться («OK») номера правильно выполненных упражнений для зарегистрировавшихся посетителей. Посетив наш сайт впоследствии, вам не нужно будет вспоминать, какие упражнения вами уже выполнены, а какие — нет. Исключение составляет обучающий этап. Участие в рейтинге обучающего этапа (включая отметки «ОК» решенных задач) является платным. Однажды зарегистрировавшись, вы впоследствии вводите логин и пароль, указанные при регистрации. При входе без авторизации система не будет отслеживать ваши успехи. Для авторизованных пользователей доступен форум, на котором можно обсудить решения предложенных упражнений.

Убедительно просим вас соблюдать правила сайта.

ЗАМЕЧАНИЕ: неправильно сформулированный запрос может вернуть «правильные» данные на текущем состоянии базы данных. Поэтому не стоит удивляться, если результаты неверного запроса совпадут с правильными результатами, но при этом запрос будет оценен системой проверки как неверный.

ВНИМАНИЕ: для корректной работы сайта браузер должен разрешать использование Cookie и JavaScript.
Поскольку страницы справки открываются в подчиненном окне, ваш Web-фильтр, если он используется, должен разрешать открытие подчиненных окон.

Сертификация

По результатам тестирования на сайте можно заказать сертификат «SQL Data Manipulation Language Specialist», подтверждающий вашу квалификацию. Качество сертификата мы поддерживаем периодической заменой задач и повышением сертификационных требований.

Используемый синтаксис SQL

Запросы посетителей фактически исполняются сервером SQL, что накладывает ограничения на допустимый синтаксис операторов. В настоящее время на рейтинговых этапах мы используем Microsoft SQL Server 2019 (15.0), а на обучающем этапе — дополнительно MariaDB-10.4 (совместима с MySQL 8), PostgreSQL 12.3 и Oracle Database 11g. Поэтому пользователю нужно придерживаться синтаксиса данных реализаций при написании своих запросов. Отметим, что синтаксис языка SQL, реализованный в Microsoft SQL Server, достаточно близок к стандарту SQL-92. Однако есть ряд отклонений, среди которых можно отметить отсутствие естественного соединения таблиц (NATURAL JOIN). Имеющаяся на сайте справка по языку манипуляции данными SQL, выдержанная в соответствии со стандартом, содержит необходимую информацию для изучения языка и выполнения упражнений. Там же можно найти особенности используемой реализации (SQL Server).

5 заданий по SQL с реальных собеседований

SQL — один из самых востребованных навыков в IT. Разбираем несколько задач с собеседований, разбитых по уровням junior, middle и senior.

SQL — один из самых востребованных навыков в современной IT индустрии (на 3 месте по популярности согласно StackOverflow Developer Survey 2020, даже Python идет на 4 месте).

Само собой, конкуренция в этой сфере огромна и собеседования порой превращаются в сущую пытку — кандидатам дают огромные задачи, задают десятки каверзных вопросов, устраивают дизайн-интервью и все это на позицию Junior аналитика…

Сегодня Елизавета, аналитик и эксперт IT Resume, делится 5 задачами и вопросами, которые входят в программу подготовки к собеседованию SQL Interview. Это задачи с реальных собеседований в крупные IT-компании, и они разбиты по уровням — Junior, Middle и Senior. Попробуйте и вы свои силы — сможете их решить без подсказки ?

Елизавета Рузова
аналитик и эксперт в IT Resume

Вводные данные

Есть таблица анализов Analysis:

• an_id — ID анализа;
• an_name — название анализа;
• an_cost — себестоимость анализа;
• an_price — розничная цена анализа;
• an_group — группа анализов.

Есть таблица групп анализов Groups:

• gr_id — ID группы;
• gr_name — название группы;
• gr_temp — температурный режим хранения.

Есть таблица заказов Orders:

• ord_id — ID заказа;
• ord_datetime — дата и время заказа;
• ord_an — ID анализа.

Далее мы будем работать с этими таблицами.

Задача 1. Уровень: Junior

Формулировка: вывести название и цену для всех анализов, которые продавались 5 февраля 2020 и всю следующую неделю.

Это задача для начинающих специалистов. В ней проверяется базовое знание SELECT-запросов и умение работать с датой-временем.

Примечание На собеседованиях редко придираются к специфике диалекта — если Вы привыкли работать в PostgreSQL, то используйте привычные функции. Главное — правильно решить задачу.

Задача 2. Уровень: Middle

Формулировка: нарастающим итогом рассчитать, как увеличивалось количество проданных тестов каждый месяц каждого года с разбивкой по группе.

Эта задача уже более высокого уровня: ее можно давать как Middle, так и Junior специалистам. Здесь проверяется базовое понимание оконных функций, джоинов и группировок.

Примечание После того, как вы написали первую версию своего запроса, попробуйте его оптимизировать. Например, в данном примере мы используем CTE — обобщенные табличные выражения.

Задача 3: Уровень Senior

В этой задаче мы будем работать с другой таблицей (да, она будет всего одна). Сам запрос в этой задаче не сложный, но для его написания необходимо как бы уметь «мыслить на SQL».

Рассмотрим таблицу балансов клиентов:

ClientBalance(client_id, client_name, client_balance_date, client_balance_value)

• client_id — идентификатор клиента;
• client_name — ФИО клиента;
• client_balance_date — дата баланса клиента;
• client_balance_value — значение баланса клиента.

Формулировка: в данной таблице в какой-то момент времени появились полные дубли. Предложите способ для избавления от них без создания новой таблицы.

Вопрос 1. Уровень: Junior

Есть категория «хитрых» вопросов, которые особенно любят задавать Junior-специалистам. Хотя чего уж там, любым специалистам.

Один из таких видов — вопросы по темам, на которые в повседневной жизни не обращаешь внимания и о которых не задумываешься. Просто делаешь на автомате, а на собеседовании это стреляет. Ну или на проекте, когда код начинает работать неправильно. Но это другая история…

Вопрос: Как оператор GROUP BY обрабатывает поля с NULL?

Если Вы не знаете ответ на этот вопрос, то после прочтения ответа обязательно проверьте свои проекты — может у вас где-то закралась ошибка? ?

Учитывая, что NULL в SQL — просто отсутствие значения, то все значения NULL при группировке попадают в одну группу. Например, пусть есть таблица:

name | score ------|------- Vasya | 5 Petya | 10 Petya | 3 Vasya | 4 NULL | 3 NULL | 8 

Тогда запрос select sum(score) from table group by name даст:

name | score ------|------- Vasya | 9 Petya | 13 NULL | 11 

Вопрос 2. Уровень Middle

Этот вопрос не такой хитрый, как предыдущий, а вполне себе конкретный. Однако, он требует знания оконных функций и их тонкостей, а это исконное Middle требование.

Вопрос: В чем отличие функции RANK() от DENSE_RANK() ?

Примечание Кстати говоря, по мотивам этого вопроса очень любят давать задачи на собеседованиях в разных вариациях: пронумеровать строки с одинаковыми значениями без разрывов, с разрывами и так далее. Так что потренируйтесь на досуге ?

По аналогии с функцией ROW_NUMBER , оконные функции RANK и DENSE_RANK служат для нумерации строк. Однако, делают они это немного иначе: строки с одинаковым значениям получают одинаковый ранг. Для ряда задач это логично: если у двух сотрудников одинаковая зарплата, мы не можем сказать, что кто-то из них первый, а кто-то второй. Они одинаковы. Но при таком подходе возникает проблема: а какой ранг должен получить следующий сотрудник? Например, если первые два были одинаковые и у них ранги 1, то сотрудник со второй зарплатой в компании должен иметь ранг 2 или 3?

Эпилог

Мы разобрали с вами всего 5 возможных заданий, которые судьба и рекрутеры могут подкинуть вам на собеседовании. Однако, вариантов, на самом деле, масса. И чтобы реально подготовиться к собеседованию, нужно нарешать как можно больше задач, понять и прочувствовать теоретические аспекты и буквально научиться «мыслить на SQL». Именно на это и нацелена программа SQL Interview — так что будем рады помочь вам получить работу мечты!

И, помимо этого, не забывайте: SQL — чисто прикладной инструмент. Чтобы его освоить, нужно практиковаться, практиковаться и практиковаться. Выучить его можно буквально за несколько недель интенсивных занятий, а вот освоить на уровне Senior… порой на это уходят годы.

Как правильно решать задачи по sql

МЕРОПРИЯТИЯ

YADRO X MOSCOW USER GROUP C++ MEETUP

Комментарии

Популярные По порядку
Не удалось загрузить комментарии.

ЛУЧШИЕ СТАТЬИ ПО ТЕМЕ

11 типов современных баз данных: краткие описания, схемы и примеры БД

Любые данные где-то хранятся. Будь это интернет вещей или пароли в *nix. Показываем схемы основных типов баз данных, чтобы наглядно представить различия между ними.

Подборка материалов для изучения баз данных и SQL

Подборка книг, видеокурсов и онлайн-ресурсов для изучения баз данных, основ реляционной теории и языка SQL.

SQL за 20 минут

Предлагаем вашему вниманию статью с кричащим названием «SQL за 20 минут». Конечно, весь SQL за 20 минут вы не освоите, но хороший старт получите.

Когда SQL решает, и задачки для прокачки

Интервью, или как у нас их чаще называют, «собесы», проходят особой сюжетной линией вдоль карьерной лестницы айтишников.

Естественный путь профессионального становления — это когда ты сначала являешься кандидатом, претендующим на вакансию, и только проходишь интервью, ну или пытаешься, а потом, со временем, еще и сам проводишь интервью для других соискателей.

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

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

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

Например, в описании требований к вакансиям Manual QA, знание SQL проскакивает настолько часто, что его можно считать частью «джентльменского» набора. И даже в тех случаях, когда в описании вакансии SQL не упоминается, это отнюдь не означает, что на интервью о нем не вспомнят.

Дело в том, что с точки зрения отбора кандидатов, знание SQL, является довольно удобным фильтром, т.к. обладает хорошей «предъявимостью», при чем не только теоретической, но и практической. Описать структура табличек, и поставить задачу перед кандидатом, можно хоть на салфетке, хоть в Codeshare. И в случаях, когда вакансия, может не предполагать знаний языков программирования, SQL, по прежнему остается кросс-профильным инструментом тестирования кандидатов разных специальностей.

Не стоит также забывать и о том, что хоть какая-то база данных, присутствует практически в любом продукте. Конечно же, это может быть далеко не только SQL-ная БД, но даже NoSQL запросы, гораздо легче изучать, когда в голове уже есть паттерны «классических» запросов SQL.

На интервью, практические задачки зачастую идут после теоретического блока, ближе к завершению процесса тестирования кандидата. И эта «вишенка на торте», порой, может стать решающей. И в этом есть своя логика. Ведь вызубрить и предъявить знание теории, грубо говоря, может каждый, но тот, кто здесь и сейчас, в условиях «экзамена», способен решить практическую задачу, находясь, так или иначе, под воздействием стресса, который является неотъемлемым спутником интервью, и продемонстрировать структурное мышление и знание синтаксиса, имеет все шансы завладеть конкурентным преимуществом, по сравнению с другими кандидатами.

Можно ли научиться решать задачи по SQL, на таком уровне, чтобы очередной вызов, с которым пришлось столкнуться на собесе, вызывал бы скорее азарт, чем страх, да конечно можно! Ибо, как известно, practice makes perfect.

Другой вопрос, что для этого, нужно нечто большее, чем самоуспокоение, путем решения самых простеньких задач, под условным названием «селект со звездочкой».

По моим субъективным наблюдениям, некоторые ресурсы, предлагают решать слишком уж тривиальные задачи, примерно на таком уровне сложности:

SELECT ProductName FROM Products WHERE Price > 100;

К сожалению, при подготовке к интервью, подобные иллюзии, разбившись о твердь реальности, могут дорого обойтись, упущенными возможностями.

Далее будут примеры разминочных задач для тренировки.

Ну а кому интересна регулярная практика, встречаемся в Телеграм-канале SQL решает, там мы тренируемся решать задачи с реальных интервью, и те задачи, которые дают хорошую почву для тренировки мозгов.

P.S. Зайдя в ТГ канал, рекомендуется кликнуть по хештегу #задача, и порешать, то что там есть, а тут нет:)

Будем использовать схему популярной БД NorthWind

Чтобы не заморачиваться с настройкой окружения на локальной машине, можно воспользоваться площадкой w3schools нажав на любую из кнопок ‘Try it yourself‘, вы попадете в редактор запросов.

Задачи

Задача 1

Таблица: Customers;

Выбрать имена клиентов (CustomerName), являющихся выходцами из городов, названия которых, начинаются на букву S, но не заканчиваются на букву n. А также, вывести и сами названия городов (City).

Подсказка: если правильный запрос выполнить в дефолтной базе w3schools, должно вернуться 11 записей.

SELECT CustomerName, City FROM Customers WHERE City LIKE "S%" AND City NOT LIKE "%n";

Задача 2

Таблицы: Customers, Orders;

Вывести список имен клиентов (CustomerName), которые не сделали ни одного заказа.

SELECT c.CustomerName FROM Customers c LEFT JOIN Orders o ON c.CustomerID=o.CustomerID WHERE o.OrderID is NULL;

Задача 3

Таблица: Products;

Вывести наименование (ProductName) и цену (Price) продукта, цена которого, предшествует минимальной.

Например: Представим, что в таблице представлены продукты по следующим ценам: 0.5$, 2.8$, 6$, 22$. Из данного перечня, нам необходимо было бы получить запись о продукте, цена которого составляет 2.8$.

SELECT ProductName, Price FROM Products WHERE Price > (SELECT MIN(Price) FROM Products) ORDER BY Price ASC LIMIT 1;

Задача 4

Таблицы: Customers, Orders;

Вывести список имен клиентов (CustomerName), даты заказов (OrderDate), и кол-во заказов, для клиентов, которые сделали более одного заказа, за январь 1997-го года.
Упорядочить список по кол-ву заказов, в обратном порядке.

SELECT c.CustomerName, o.OrderDate, Count(c.CustomerName) AS LoyalClient FROM Customers c JOIN Orders o ON c.CustomerID=o.CustomerID WHERE o.OrderDate LIKE "%1997-01%" GROUP BY c.CustomerName HAVING LoyalClient > 1 ORDER BY LoyalClient DESC;

Задача 5

Таблицы: Customers, Orders;

Вывести список имен клиентов (CutomerName), а также кол-во осуществленных ими заказов, для всех клиентов, кто сделал более 6-ти заказов.
Упорядочить записи, по количеству сделанных заказов, в обратном порядке.

SELECT c.CustomerName, COUNT(c.CustomerName) AS OrderRate FROM Customers c JOIN Orders o ON c.CustomerID=o.CustomerID GROUP BY c.CustomerName HAVING OrderRate > 6 ORDER BY OrderRate DESC

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

Если вспомнятся интересные задачи, добро пожаловать в комментарии здесь, или в ТГ канале SQL решает

Успехов в развитии!