Как разобраться с sql: SQL за 20 минут

5 вопросов по SQL, которые часто задают дата-сайентистам на собеседованиях

Хотя составление SQL-запросов — это не самое интересное в работе дата-сайентистов, хорошее понимание SQL чрезвычайно важно для того, кто хочет преуспеть в любом занятии, связанном с обработкой данных. Дело тут в том, что SQL — это не только SELECT, FROM и WHERE. Чем больше SQL-конструкций знает специалист — тем легче ему будет создавать запросы на получение из баз данных всего, что ему может понадобиться.

Автор статьи, перевод которой мы сегодня публикуем, говорит, что она направлена на решение двух задач:

1. Изучение механизмов, которые выходят за пределы базового знания SQL.
2. Рассмотрение нескольких практических задач по работе с SQL.

В статье рассмотрено 5 вопросов по SQL, взятых с Leetcode. Они представляют собой практические задачи, которые часто встречаются на собеседованиях.

Вопрос №1: второе место по зарплате

Напишите SQL-запрос для получения из таблицы со сведениями о заработной плате сотрудников (Employee) записи, содержащей вторую по размеру заработную плату.

Например, такой запрос, выполненный для таблицы, представленной ниже, должен вернуть 200. Если в таблице нет значения, меньшего, чем самая высокая зарплата — запрос должен вернуть null.

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

▍Решение А: использование IFNULL и OFFSET

Вот основные механизмы, которые будут использованы в данном варианте решения задачи:

• IFNULL(expression, alt): эта функция возвращает свой аргумент expression в том случае, если он не равен null. В противном случае возвращается аргумент alt. Мы воспользуемся этой функцией для того чтобы возвратить null в том случае, если в таблице не окажется искомого значения.
• OFFSET: этот оператор используется с выражением ORDER BY для того чтобы отбросить первые n строк. Это нам пригодится по той причине, что нас интересует вторая строка результата (то есть — вторая по величине зарплата, данные о которой есть в таблице).

Вот — готовый запрос:

SELECT
    IFNULL(
        (SELECT DISTINCT Salary
        FROM Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1
        ), null) as SecondHighestSalary
FROM Employee
LIMIT 1

▍Решение B: использование MAX

В запросе, представленном ниже, используется функция MAX. Здесь выбирается самое большое значение заработной платы, не равное максимальной заработной плате, полученной по всей таблице. В результате мы и получаем то, что нам нужно — вторую по величине заработную плату.

SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary != (SELECT MAX(salary) FROM Employee)

Вопрос №2: дублирующиеся адреса электронной почты

Напишите SQL-запрос, который обнаружит в таблице Person все дублирующиеся адреса электронной почты.

+----+---------+
| Id | Email   |
+----+---------+
| 1  | [email protected] |
| 2  | [email protected] |
| 3  | [email protected] |
+----+---------+

▍Решение А: COUNT в подзапросе

Сначала мы создаём подзапрос, в котором выясняется частота появления каждого адреса в таблице. Затем результат, возвращаемый подзапросом, фильтруется с использованием инструкции WHERE count > 1. Запрос вернёт сведения об адресах, встречающихся в исходной таблице больше одного раза.

SELECT Email
FROM (
    SELECT Email, count(Email) AS count
    FROM Person
    GROUP BY Email
) as email_count
WHERE count > 1

▍Решение B: выражение HAVING

• HAVING: это выражение, которое позволяет использовать инструкцию WHERE вместе с выражением GROUP BY.

SELECT Email
FROM Person
GROUP BY Email
HAVING count(Email) > 1

Вопрос №3: растущая температура

Напишите SQL-запрос, который находит в таблице Weather все даты (идентификаторы дат), когда температура была бы выше температуры на предшествующие им даты. То есть, нас интересуют даты, в которые «сегодняшняя» температура выше «вчерашней».

+---------+------------------+------------------+
| Id(INT) | RecordDate(DATE) | Temperature(INT) |
+---------+------------------+------------------+
|       1 |       2015-01-01 |               10 |
|       2 |       2015-01-02 |               25 |
|       3 |       2015-01-03 |               20 |
|       4 |       2015-01-04 |               30 |
+---------+------------------+------------------+

▍Решение: DATEDIFF

• DATEDIFF: эта функция вычисляет разницу между двумя датами. Она используется для того, чтобы обеспечить сравнение именно «сегодняшних» и «вчерашних» температур.

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

SELECT DISTINCT a.Id
FROM Weather a, Weather b
WHERE a.Temperature > b.Temperature
AND DATEDIFF(a.Recorddate, b.Recorddate) = 1

Вопрос №4: самая высокая зарплата в подразделении

В таблице Employee хранятся сведения о сотрудниках компании. В каждой записи этой таблицы содержатся сведения об идентификаторе (Id) сотрудника, о его имени (Name), о зарплате (Salary) и о подразделении компании, где он работает (Department).

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Jim   | 90000  | 1            |
| 3  | Henry | 80000  | 2            |
| 4  | Sam   | 60000  | 2            |
| 5  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

В таблице Department содержатся сведения о подразделениях компании.

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

Напишите SQL-запрос, который находит в каждом из подразделений сотрудников с максимальной заработной платой. Например, для вышеприведённых таблиц подобный запрос должен возвращать результаты, представленные следующей таблицей (при этом порядок строк в таблице значения не имеет):

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Jim      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

▍Решение: команда IN

Команда IN позволяет задавать в инструкции WHERE условия, соответствующие использованию нескольких команд OR. Например, две следующие конструкции идентичны:

WHERE country = ‘Canada’ OR country = ‘USA’
WHERE country IN (‘Canada’, ’USA’).

Здесь мы хотим получить таблицу, содержащую название подразделения (Department), имя сотрудника (Employee) и его заработную плату (Salary). Для этого мы формируем таблицу, в которой содержатся сведения об идентификаторе подразделения (DepartmentID) и о максимальной зарплате по этому подразделению. Далее мы объединяем две таблицы по условию, в соответствии с которым записи в результирующую таблицу попадают только в том случае, если DepartmentID и Salary есть в ранее сформированной таблице.

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM Employee
INNER JOIN Department ON Employee.DepartmentId = Department.Id
WHERE (DepartmentId , Salary) 
    IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
 )

Вопрос №5: пересаживание учеников

Мэри — учительница в средней школе. У неё есть таблица seat, хранящая имена учеников и сведениях об их местах в классе. Значение id в этой таблице постоянно возрастает. Мэри хочет поменять местами соседних учеников.

Вот таблица исходного размещения учеников:

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Abbot   |
|    2    | Doris   |
|    3    | Emerson |
|    4    | Green   |
|    5    | Jeames  |
+---------+---------+

Вот что должно получиться после пересаживания соседних учеников:

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Doris   |
|    2    | Abbot   |
|    3    | Green   |
|    4    | Emerson |
|    5    | Jeames  |
+---------+---------+

Напишите запрос, который позволит учительнице решить вышеописанную задачу.

Обратите внимание на то, что если количество учеников является нечётным — последнего ученика никуда пересаживать не надо.

▍Решение: использование оператора WHEN

SQL-конструкцию CASE WHEN THEN можно рассматривать как оператор if в программировании.

В нашем случае первый оператор WHEN используется для проверки того, назначен ли последней строке в таблице нечётный идентификатор. Если это так — строка не подвергается изменениям. Второй оператор WHEN отвечает за добавление 1 к каждому нечётному идентификатору (например — 1, 3, 5 превращается в 2, 4, 6) и за вычитание 1 из каждого чётного идентификатора (2, 4, 6 превращаются в 1, 3, 5).

SELECT 
    CASE 
        WHEN((SELECT MAX(id) FROM seat)%2 = 1) AND id = (SELECT MAX(id) FROM seat) THEN id
        WHEN id%2 = 1 THEN id + 1
        ELSE id - 1
    END AS id, student
FROM seat
ORDER BY id

Итоги

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

P.S. В нашем маркетплейсе есть Docker-образ с SQL Server Express, который устанавливается в один клик. Вы можете проверить работу контейнеров на VPS. Всем новым клиентам бесплатно предоставляются 3 дня для тестирования.

Уважаемые читатели! Что вы можете посоветовать тем, кто хочет освоить искусство создания SQL-запросов?

Как объяснить своей бабушке разницу между SQL и NoSQL / Хабр

Одно из наиболее важных решений, которые принимает разработчик, заключается в том, какую базу данных использовать. В течение многих лет опции были ограничены различными вариантами реляционных баз данных, которые поддерживали язык структурированных запросов (SQL). К ним относятся MS SQL Server, Oracle, MySQL, PostgreSQL, DB2 и многие другие.

За последние 15 лет на рынке появилось много новых баз данных в рамках подхода No-SQL. К ним относятся хранилища ключей-значений, такие как Redis и Amazon DynamoDB, широкие колоночные базы, такие как Cassandra и HBase, хранилища документов, такие как MongoDB и Couchbase, а также графовые базы данных и поисковые системы, такие как Elasticsearch и Solr.

В этой статье мы попробуем разобраться в SQL и NoSQL, не влезая в их функционал.

Кроме того, мы немного повеселимся в процессе.

Объясняем бабушке SQL

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

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

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

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

Cписок внуков

Через некоторое время ты во всем разбираешься и мы почти закончили со списком! Однако ты поворачиваешься ко мне и говоришь: «Мы забыли добавить место для супругов, хобби, внуков!» Но нет, мы не забыли! Это следует дальше и требует нового листа бумаги.

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

Список супругов

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

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

Я возвращаюсь через несколько часов. А ты крута, бабуля! Все выглядит отлично, за исключением списка хобби. В списке около 1000 хобби. Большинство из них повторяются; что случилось?

Извини, совсем забыл сказать! Используя один список, можно отслеживать только хобби. Затем в другом списке нам нужно отследить внуков, которые занимаются этим хобби. Мы собираемся назвать это «Общий список». Видя, что тебе это не нравится, я начинаю переживать и возвращаюсь в режим списка.
Список хобби

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

Общий список внуки хобби

После всей этой работы у бабушки теперь есть крутая система запоминания для слежки за всей ее удивительно большой семьей. А потом — чтобы задержать меня подольше — она задает волшебный вопрос: «Где ты научился все это делать?»

Реляционные базы данных

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

Отличительная черта наиболее популярных реляционных баз данных — язык запросов SQL (Structured Query Language). Благодаря нему, если бабушка перенесет свою систему запоминания в компьютер, она сможет быстро получить ответ на такие вопросы, как: «Кто не посещал меня в прошлом году, женат и не имеет никаких увлечений?»

Одна из наиболее популярных систем управления базами данных SQL это MySQL с открытым исходным кодом. Она реализована в первую очередь как система управления реляционными базами данных (RDBMS) для программных приложений на базе веб-технологий.

Некоторые ключевые особенности MySQL:

• Она довольно известна, широко используется и тщательно протестирована.
• Есть много квалифицированных разработчиков, имеющих опыт работы с SQL и реляционными базами данных.
• Данные хранятся в различных таблицах, что позволяет легко устанавливать связь с использованием первичных и внешних ключей (идентификаторов).
• Он прост в использовании и эффективен, что делает его идеальным для больших и малых предприятий.
• Исходный код находится на условиях GNU General Public License.

Теперь забудь ВСЕ.

Объясняем бабушке NoSQL

Бабушка, у нас огромная семья. В ней 150 внуков! Многие из них женаты, имеют детей, чем-то увлекаются и прочее. В твоем возрасте невозможно помнить все обо всех нас. Что тебе нужно, так это это система запоминания!

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

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

{ 
  "_id":"dkdigiye82gd87gd99dg87gd",
  "name":"Cody",
  "birthday":"09-12-2006",
  "last_visit":"09-02-2019",
  "clothing_size":"XL",
  "favorite_ice_cream":"Fudge caramel",
  "adopted":false,
  "hobbies":[ 
     "video games",
     "computers",
     "cooking"
  ],
  "spouse":null,
  "kids":[ 

  ],
  "favorite_picture":"file://scrapbook-103/christmas-2010.jpg",
  "misc_notes":"Prefers ice-cream cake on birthday instead of chocolate cake!"
}

Я: “Кажется все готово!”
Бабушка: “Подожди, а как же остальные внуки?”
Я: “Да, точно. Тогда выделяем по странице на каждого.”
Бабушка: “А мне нужно будет записывать всю ту же самую информация для всех, как я делала для тебя?”
Я: “Нет, только если ты хочешь. Давай покажу.”
Забрав у бабушки ручку, я перелистываю страницу и быстро записываю информацию о моем самом нелюбимом двоюродном брате.

{ 
  "_id":"dh97dhs9b39397ss001",
  "name":"Tanner",
  "birthday":"09-12-2008",
  "clothing_size":"S",
  "friend_count":0,
  "favorite_picture":null,
  "remember":"Born on same day as Cody but not as important"
}

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

Когда все уже сделано, она задает волшебный вопрос: «Где ты научился все это делать?»

Базы данных NoSQL

Существует множество баз данных NoSQL (“не только SQL”). В наших примерах мы показали базу данных документов. Базы данных NoSQL моделируют данные способами, исключающими табличные отношения, используемые в реляционных базах данных. Эти базы данных стали популярными в начале 2000-х годов среди компаний, которым требовалась облачная кластеризация баз данных из-за их явных требований к масштабированию (например, Facebook). В таких приложениях согласованность данных была намного менее важной, чем производительность и масштабируемость.

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

Ключевые особенности NoSQL:

• Очень гибкий способ хранения данных
• Горизонтальное масштабирование до кластеров
• Возможная последовательность на постоянство / распространение
• Документы, которые идентифицируются с использованием уникальных ключей

Детальное сравнение

MySQL требует определенной и структурированной схемы.
NoSQL позволяет сохранять любые данные в «документе».

MySQL поддерживает огромное сообщество.

У NoSQL есть небольшое и быстро растущее сообщество.

NoSQL отличается простотой масштабирования.

MySQL нуждается в большей управляемости.

MySQL использует SQL, который применяется во множестве типов баз данных.

NoSQL — это база данных на основе дизайна с популярными реализациями.

MySQL использует стандартный язык запросов (SQL).

NoSQL не использует стандартный язык запросов.

MySQL имеет много отличных инструментов отчетности.

В NoSQL есть несколько инструментов отчетности, которые сложно стандартизировать.

MySQL может выдать проблемы с производительностью для больших данных.

NoSQL обеспечивает отличную производительность на больших данных.

Мысли 8base

В компании 8base, в которой я работаю, мы обеспечиваем рабочую область каждого проекта реляционной базой данных Aurora MySQL, которая размещается на AWS. Хотя NoSQL является логичным выбором, когда требования вашего приложения требуют высокой производительности и масштабируемости, мы считаем, что строгая согласованность данных, обеспечиваемая СУБД, необходима при создании SaaS-приложений и другого программного обеспечения для бизнеса.

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

Перевод выполнен в компании 8base

8base – это готовый к использованию GraphQL backend-as-a-service, который постепенно превращается в полноценную low code платформу разработки. Наша цель – дать возможность разработчикам, обладающим навыками front-end или мобильной разработки, создавать масштабируемые бизнес-приложения.

Узнайте больше о разработке с Aurora, Serverless и GraphQL с помощью 8base.com здесь.

Конструкция WHERE в SQL

Вы здесь:
Главная — MySQL — SQL — Конструкция WHERE в SQL

При выборке записей из таблицы практически всегда требуется задавать определённые условия, по которым мы определяем, какие записи нам нужны, а какие — нет. И именно эти условия можно задавать с помощью конструкции WHERE в SQL. Я уже знакомил Вас с ней, но в этой статье решил познакомить с WHERE немного поближе.

Начну с простого примера выборки с использованием конструции WHERE в SQL:

SELECT * FROM table WHERE count=5

Вернутся записи, в которых поле «count» имеет значение 5. Теперь усложним запрос:

SELECT * FROM table WHERE count=5 AND id < 100

Таким образом, вернутся записи, у которых поле «count» имеет значение 5 И поле «id» имеет значение меньше 100.

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

• ! (отрицание)
• AND (И)
• OR (ИЛИ)
• XOR (ИСКЛЮЧАЮЩЕЕ ИЛИ, иногда ещё называют МОНТАЖНОЕ ИЛИ, но такое название встречается в основном в микропроцессорной литературе)

Пример с использованием нескольких логических операторов:

SELECT * FROM table WHERE !(id <= 120 AND (count=10 OR date > "10/11/1980"))

Вот такой, на первый взгляд, сложный SQL-запрос. Постарайтесь в нём разобраться самостоятельно.

Также конструкция WHERE в SQL может содержать LIKE. LIKE позволяет определить, совпадает ли указанная строка с определённым шаблоном. Чтобы стало немного понятнее, приведу пример:

SELECT * FROM table WHERE text LIKE "%some text%"

Данный SQL-запрос вернёт result_set, содержащий записи, в которых поле «text» имеет такой текст: «some text«. Обратите внимание, что это не проверка на равенство. Текст может быть огромным, но если в нём содержитася строка: «some text«, то LIKE вернёт true.

Давайте напишу, как задаётся шаблон для LIKE:

• % — это то, что мы с Вами использовали. Используется он чаще всего и означает он любую строку любой длины. Фактически, строкой «%some text%» мы говорим, что сначала идёт любая строка любой длины, затем «some text«, а затем вновь любая строка любой длины. Если текст удовлетворяет этому шаблону, то вернуть true, иначе false.
• [ ] — это одиночный символ. Чтобы использовать этот шаблон необходимо задавать диапазоны, например, так: «[a-z]some%«. Данный шаблон будет означать, что сначала идёт 1 символ (любой символ от a до z), далее «some» и потом любая строка любой длины.
• _ — это любой одиночный символ.
• [^] — это противоположность [ ]. Например, можно привести такой пример: «[^az]some_«. Данный шаблон означает, что вначале идёт любой символ, но только НЕ «a» и НЕ «z«. Далее должна идти строка «some«, а после только один одиночный символ.

Знание и умение использования LIKE очень важно, поверьте моему опыту. Самый простой пример использования LIKE — это поиск по сайту. Ведь контент находится в базе данных, и необходимо вытащить только те записи, в которых содержится строка, заданная в строке поиска. И тут приходит на помощь LIKE. Именно так реализован поиск мною на этом сайте.

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

Полный курс по PHP и MySQL: http://srs.myrusakov.ru/php

• 
  Создано 22.01.2011 16:41:50

• 
  
  Михаил Русаков

Предыдущая статья Следующая статья

Копирование материалов разрешается только с указанием автора (Михаил Русаков) и индексируемой прямой ссылкой на сайт (http://myrusakov.ru)!

Добавляйтесь ко мне в друзья ВКонтакте: http://vk.com/myrusakov.
Если Вы хотите дать оценку мне и моей работе, то напишите её в моей группе: http://vk.com/rusakovmy.

Если Вы не хотите пропустить новые материалы на сайте,
то Вы можете подписаться на обновления: Подписаться на обновления

Если у Вас остались какие-либо вопросы, либо у Вас есть желание высказаться по поводу этой статьи, то Вы можете оставить свой комментарий внизу страницы.

Порекомендуйте эту статью друзьям:

Если Вам понравился сайт, то разместите ссылку на него (у себя на сайте, на форуме, в контакте):

1. 
   Кнопка:
   
   <a href=»https://myrusakov.ru» target=»_blank»><img src=»https://myrusakov.ru/images/button.gif» alt=»Как создать свой сайт» /></a>

   Она выглядит вот так:

2. 
   Текстовая ссылка:
   <a href=»https://myrusakov.ru» target=»_blank»>Как создать свой сайт</a>

   Она выглядит вот так: Как создать свой сайт

3. BB-код ссылки для форумов (например, можете поставить её в подписи):
   
   [URL=»https://myrusakov.ru»]Как создать свой сайт[/URL]

Наш вариант теста на знание SQL / Хабр

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

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

Также, не имело смысла давать задания на знание каких-либо особенностей тех или иных СУБД. Мы в работе используем Oracle, но это не должно создавать трудностей для соискателей знающих, например, только MS SQL или PostgreSQL. Таким-образом, использование платформо-зависимых решений не возбраняется, но и не является ожидаемым при решении задач.

Для проведения тестирования, в Oracle 11g была развернута схема, содержащая следующие таблицы:

Требовалось составить SQL-запросы, для решения следующих пяти заданий:

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

select a.*
from   employee a, employee b
where  b.id = a.chief_id
and    a.salary > b.salary

Задание 2Вывести список сотрудников, получающих максимальную заработную плату в своем отделеВариант ответа

select a.*
from   employee a
where  a.salary = ( select max(salary) from employee b
                    where  b.department_id = a.department_id )

Задание 3Вывести список ID отделов, количество сотрудников в которых не превышает 3 человекВариант ответа

select department_id
from   employee
group  by department_id
having count(*) <= 3

Задание 4Вывести список сотрудников, не имеющих назначенного руководителя, работающего в том-же отделеВариант ответа

select a.*
from   employee a
left   join employee b on (b.id = a.chief_id and b.department_id = a.department_id)
where  b.id is null

Задание 5Найти список ID отделов с максимальной суммарной зарплатой сотрудниковВариант ответа

with sum_salary as
  ( select department_id, sum(salary) salary
    from   employee
    group  by department_id )
select department_id
from   sum_salary a       
where  a.salary = ( select max(salary) from sum_salary ) 

Не требовалось искать в каком-либо смысле оптимальное решение. Единственное требование: запрос должен возвращать правильный ответ на любых входных данных. Задания разрешалось решать в любом порядке, без ограничения времени. При правильном решении всех заданий, предлагалось следующее задание повышенной сложности:Дополнительное заданиеСоставить SQL-запрос, вычисляющий произведение вещественных значений, содержащихся в некотором столбце таблицыВариант ответа

select
  exp(sum(ln(decode(sign(salary),0,1,-1,-salary,salary))))
 *decode(mod(sum(decode(sign(salary),-1,1,0)),2),1,-1,1)
 *sign(min(abs(salary)))
from employee

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

SQL или NoSQL — вот в чём вопрос / Блог компании RUVDS.com / Хабр

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

Реляционные БД хранят структурированные данные, которые обычно представляют объекты реального мира. Скажем, это могут быть сведения о человеке, или о содержимом корзины для товаров в магазине, сгруппированные в таблицах, формат которых задан на этапе проектирования хранилища.

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

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

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

О выборе SQL-баз данных

Не существует баз данных, которые подойдут абсолютно всем. Именно поэтому многие компании используют и реляционные, и нереляционные БД для решения различных задач. Хотя NoSQL-базы стали популярными благодаря быстродействию и хорошей масштабируемости, в некоторых ситуациях предпочтительными могут оказаться структурированные SQL-хранилища. Вот две причины, которые могут послужить поводом для выбора SQL-базы:

1. Необходимость соответствия базы данных требованиям ACID (Atomicity, Consistency, Isolation, Durability — атомарность, непротиворечивость, изолированность, долговечность). Это позволяет уменьшить вероятность неожиданного поведения системы и обеспечить целостность базы данных. Достигается подобное путём жёсткого определения того, как именно транзакции взаимодействуют с базой данных. Это отличается от подхода, используемого в NoSQL-базах, которые ставят во главу угла гибкость и скорость, а не 100% целостность данных.
2. Данные, с которыми вы работаете, структурированы, при этом структура не подвержена частым изменением. Если ваша организация не находится в стадии экспоненциального роста, вероятно, не найдётся убедительных причин использовать БД, которая позволяет достаточно вольно обращаться с типами данных и нацелена на обработку огромных объёмов информации.

О выборе NoSQL-баз данных

Если есть подозрения, что база данных может стать узким местом некоего проекта, основанного на работе с большими объёмами информации, стоит посмотреть в сторону NoSQL-баз, которые позволяют то, чего не умеют реляционные БД.

Вот возможности, которые стали причиной популярности таких NoSQL баз данных, как MongoDB, CouchDB, Cassandra, HBase:

1. Хранение больших объёмов неструктурированной информации. База данных NoSQL не накладывает ограничений на типы хранимых данных. Более того, при необходимости в процессе работы можно добавлять новые типы данных.
2. Использование облачных вычислений и хранилищ. Облачные хранилища — отличное решение, но они требуют, чтобы данные можно было легко распределить между несколькими серверами для обеспечения масштабирования. Использование, для тестирования и разработки, локального оборудования, а затем перенос системы в облако, где она и работает — это именно то, для чего созданы NoSQL базы данных.
3. Быстрая разработка. Если вы разрабатываете систему, используя agile-методы, применение реляционной БД способно замедлить работу. NoSQL базы данных не нуждаются в том же объёме подготовительных действий, которые обычно нужны для реляционных баз.

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

SQL и NoSQL

Начнём с некоторых ключевых концепций реляционных и нереляционных баз данных. Ниже показана база данных, содержащая сведения о взаимоотношениях людей. Вариант a — это бессхемная структура, построенная в виде графа, характерная для NoSQL-решений. Вариант b показывает, как те же данные можно представить в структурированном виде, типичном для SQL.

Два варианта представления данных

Бессхемность означает, что два документа в структуре данных NoSQL не должны иметь одинаковые поля и могут хранить данные разных типов. Вот, например, массив объектов, набор полей которых не совпадает.

var cars = [
{ Model: "BMW", Color: "Red", Manufactured: 2016 },
{ Model: "Mercedes", Type: "Coupe", Color: "Black", Manufactured: "1-1-2017" }
];

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

SELECT Orders.OrderID, Customers.Name, Orders.Date
FROM Orders
INNER JOIN Customers
ON Orders.CustID = Customers.CustID

Как более продвинутый пример, для демонстрации того, когда SQL предпочтительнее NoSQL, рассмотрим особенности применения в NoSQL-базах алгоритмов уплотнения. Проблема заключается в том, что в некоторых NoSQL-базах (например, в CouchDB и HBase) постоянно приходится формировать так называемые sstables — строковые таблицы в формате ключ-значение, отсортированные по ключу. В такие таблицы, которые сохраняются на диск, данные попадают из таблиц, хранящихся в памяти, при их переполнении и в других ситуациях. При интенсивной работе с базой создание таблиц, со временем, приводит к тому, что подсистема ввода-вывода устройства хранения данных становится узким местом для операций чтения данных. Как результат, чтение в NoSQL-базе происходит медленнее, чем запись, что сводит на нет одно из главных преимуществ нереляционных баз данных. Именно для того, чтобы уменьшить этот эффект, системы NoSQL используют, в фоновом режиме, алгоритмы уплотнения данных, пытаясь объединить множество таблиц в одну. Но и сама по себе эта операция весьма ресурсоёмка, система работает под повышенной нагрузкой.

Масштабируемость

Одно из основных различий рассматриваемых технологий заключается в том, что NoSQL-базы лучше поддаются масштабированию. Например, в MongoDB имеется встроенная поддержка репликации и шардинга (горизонтального разделения данных) для обеспечения масштабируемости. Хотя масштабирование поддерживается и в SQL-базах, это требует гораздо больших затрат человеческих и аппаратных ресурсов.

Более подробное сравнение SQL и NoSQL можно найти в этом материале. Вот его основные положения. А именно, были проведены испытания трёх основных характеристик систем: параллельная обработка данных, работа с хранилищами информации, репликация данных. Возможности параллельной обработки оценивались путём анализа механизмов блокировки, управления параллельным доступом на основе многоверсионности, и ACID. Тестирование хранилищ охватывало и физические носители, и хранилища использующие оперативную память. Репликацию испытывали в синхронном и асинхронном режимах.

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

Индексация

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

И в SQL, и в NoSQL-базах индексы служат одной и той же цели — ускорить и оптимизировать извлечение данных. Но то, как именно они работают — различается из-за разных архитектур баз данных и особенностей хранения информации в базе. В то время, как SQL-индексы представлены в виде B-деревьев, которые отражают иерархическую структуру реляционных данных, в NoSQL базах данных они указывают на документы, или на части документов, между которыми, в основном, нет никаких отношений. Вот подробный материал на эту тему.

CRM-системы

CRM-приложения — это один из лучших примеров систем, для которых характерны огромные объёмы ежедневно обрабатываемых данных и очень большое количество транзакций. Все разработчики таких приложений используют и SQL, и NoSQL базы данных. И, хотя большая часть данных транзакций всё ещё хранится в SQL-базах, применение находят общедоступные системы класса DBaaS (data-base-as-a-service, база данных как сервис), наподобие AWS DynamoDB и Azure DocumentDB, в результате, серьёзная нагрузка по обработке данных может быть перенесена в облачные NoSQL-базы.

В то время, как использование подобных служб освобождает разработчика от решения задач по обслуживанию хранилищ, это, кроме того, область, где NoSQL базы применяются для того, для чего они, в основном, и были созданы, например, для глубинного анализа данных. Объёмы информации, хранимой в огромных CRM-системах финансовых и телекоммуникационных компаний, было бы практически невозможно проанализировать, используя инструменты вроде SAS или R. Это потребовало бы огромных аппаратных ресурсов.

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

Итоги

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

Вот признаки проектов, для которых идеально подойдут SQL-базы:

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

А вот свойства проектов, для которых подойдёт что-то из сферы NoSQL:

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

В итоге хочется сказать, что в современном мире нет противостояния между реляционными и нереляционными базами данных. Вместо этого стоит говорить об их совместном использовании для решения задач, на которых та или иная технология показывает себя лучше всего. Кроме того, всё сильнее наблюдается интеграция этих технологий друг в друга. Например, Microsoft, Oracle и Teradata сейчас предлагают некоторые формы интеграции с Hadoop для подключения аналитических инструментов, основанных на SQL, к миру неструктурированных больших данных.

Уважаемые читатели, а вам приходилось выбирать системы управления базами данных для собственных проектов? Если да — поделитесь пожалуйста опытом, расскажите, что и почему вы в итоге выбрали.

Как бороться с аномалиями модификации SQL и нормальными формами

2. Программирование
3. SQL
4. Как бороться с аномалиями модификации SQL и нормальными формами

Аллен Г. Тейлор

Множество проблем, называемых модификацией аномалии — могут испортить базу данных, если вы неправильно структурируете базу данных SQL. Чтобы предотвратить эти проблемы, вы можете нормализовать структуру базы данных. Нормализация обычно влечет за собой разделение одной таблицы базы данных на две более простые таблицы.

Аномалии модификации названы так, потому что они генерируются при добавлении, изменении или удалении данных из таблицы базы данных.

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

Однако если вы сделаете это, вы не потеряете тот факт, что покупатель 1001 купил стиральный порошок; вы также теряете тот факт, что стиральный порошок стоит 12 долларов. Эта ситуация называется аномалией делеции . При удалении одного факта (что покупатель 1001 купил стиральный порошок) вы случайно удаляете другой факт (этот стиральный порошок стоит 12 долларов).

Вы можете использовать ту же таблицу для иллюстрации аномалии вставки. Например, предположим, что вы хотите добавить дезодорант-стик в свою линейку продуктов по цене 2 доллара.Вы не можете добавить эти данные в таблицу SALES, пока клиент не купит дезодорант-стик.

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

• CUST_PURCH, который имеет дело с единственной идеей покупок клиентов.

• PROD_PRICE, который касается единой идеи ценообразования продукта.

Теперь вы можете удалить строку для клиента 1001 из CUST_PURCH, не теряя того факта, что стиральный порошок стоит 12 долларов. (Стоимость стирального порошка теперь хранится в PROD_PRICE.) Вы также можете добавить дезодорант-стик в PROD_PRICE независимо от того, купил ли кто-нибудь продукт. Информация о покупках хранится в другом месте в таблице CUST_PURCH.

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

Каждая таблица базы данных должна иметь дело с одной и только одной главной темой. Иногда (как вы, наверное, догадались) определить, что таблица действительно имеет дело с двумя или более темами, может быть сложно.

Таблицы можно классифицировать по типам аномалий модификации, которым они подвержены.В статье 1970 года Э. Ф. Кодд идентифицировал три источника аномалий модификации и определил первую, вторую и третью нормальные формы (1NF, 2NF, 3NF) как средства лечения этих типов аномалий. В последующие годы Кодд и другие открыли дополнительные типы аномалий и определили новые нормальные формы для борьбы с ними.

Нормальная форма Бойса-Кодда (BCNF), четвертая нормальная форма (4NF) и пятая нормальная форма (5NF), каждая, обеспечивала более высокую степень защиты от аномалий модификации.Однако только в 1981 году в статье, написанной Рональдом Феджином, описывалась нормальная форма доменного ключа или DK / NF. Использование этой последней нормальной формы позволяет гарантировать , что таблица не содержит аномалий модификации.

Нормальные формы — это вложенные в том смысле, что таблица, которая находится в 2NF, автоматически становится также в 1NF. Точно так же таблица в 3NF автоматически попадает в 2NF и так далее. Для большинства практических приложений помещения базы данных в 3NF достаточно для обеспечения высокой степени целостности.Чтобы быть абсолютно уверенным в ее целостности, необходимо поместить базу данных в DK / NF.

После того, как вы максимально нормализуете базу данных, вы можете захотеть сделать выбранные денормализации для повышения производительности. Если да, то помните о типах аномалий, которые теперь могут стать возможными.

Об авторе книги

Аллен Г. Тейлор — 30-летний ветеран компьютерной индустрии и автор более 40 книг, в том числе SQL для чайников, и Crystal Reports для чайников. Он читает лекции на национальном уровне по базам данных, инновациям и предпринимательству. Он также преподает разработку баз данных на международном уровне через ведущего провайдера онлайн-образования.

.

Как работать с повторяющимися записями в базе данных SQL Server

Переполнение стека

1. Около

2. Продукты

3. Для команд

1. Переполнение стека
   Общественные вопросы и ответы

2. Переполнение стека для команд
   Где разработчики и технологи делятся частными знаниями с коллегами

3. Вакансии
   Программирование и связанные с ним технические возможности карьерного роста

4. Талант
   Нанимайте технических специалистов и создавайте свой бренд работодателя

5. Реклама
   Обратитесь к разработчикам и технологам со всего мира

6. О компании

.

node.js — Как бороться с SQL-инъекцией в OrientDB с использованием nodejs?

Переполнение стека

1. Около

2. Продукты

3. Для команд

1. Переполнение стека
   Общественные вопросы и ответы

2. Переполнение стека для команд
   Где разработчики и технологи делятся частными знаниями с коллегами

3. Вакансии
   Программирование и связанные с ним технические возможности карьерного роста

4. Талант
   Нанимайте технических специалистов и создавайте свой бренд работодателя

5. Реклама
   Обратитесь к разработчикам и технологам со всего мира

6. О компании

.

Что такое база данных? Что такое SQL?

• На главную

• Тестирование

  • • Назад
    • Гибкое тестирование
    • BugZilla
    • Cucumber
    • Тестирование базы данных
    • Тестирование ETL
    • 0003
    • Jmeter
    • Загрузка Jmeter

    Jmeter Backing

    • Ручное тестирование
    • Мобильное тестирование
    • Mantis
    • Почтальон
    • QTP
    • Назад
    • Центр качества (ALM)
    • RPA
    • SAP Testing
    • Selenium

  • SAP

    • • Назад
      • ABAP
      • APO
      • Начало er
      • Basis
      • BODS
      • BI
      • BPC
      • CO
      • Назад
      • CRM
      • Crystal Reports
      • FICO
      • Pay4
      • HR

      Назад

      • PI / PO
      • PP
      • SD
      • SAPUI5
      • Безопасность
      • Менеджер решений
      • Successfactors
      • SAP Tutorials
  • Назад

    Web

  • Angular

    Web

    • • ASP.Net
      • C
      • C #
      • C ++
      • CodeIgniter
      • СУБД
      • JavaScript
      • Назад
      • Java
      • JSP
      • Kotlin
      • Linux
      • Linux
      • Kotlin
      • Linux

      js

      • Perl
      • Назад
      • PHP
      • PL / SQL
      • PostgreSQL
      • Python
      • ReactJS
      • Ruby & Rails
      • Scala
      • SQL

      000

      • SQL

      000

      0003 SQL

      000

      0003 SQL

      000

      • UML
      • VB.Net
      • VBScript
      • Веб-службы
      • WPF

    • Обязательно учите!

      • • Назад
        • Бухгалтерский учет
        • Алгоритмы
        • Android
        • Блокчейн
        • Business Analyst
        • Создание веб-сайта
        • CCNA
        • Облачные вычисления
        • 0003 COBOL
        • 000 Compiler
          9000 Встроенный
          • 000 9000 Compiler
          • Ethical Hacking
          • Учебники по Excel
          • Программирование на Go
          • IoT
          • ITIL
          • Jenkins
          • MIS
          • Сети
          • Операционная система
          • 0003
          • Назад
          • Управление проектами Обзоры
          • Salesforce
          • SEO
          • Разработка программного обеспечения
          • VB A

      • Big Data

        • • Назад
          • AWS
          • BigData
          • Cassandra
          • Cognos
          • Хранилище данных

          0003

          • HBOps

          0003

          • HBOps

          0003

          • MicroStrategy

      .

      No related posts.