×

Техническое собеседование: 10 каверзных вопросов по SQL

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

1. Что вернет условие 2 <> NULL?

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

2 <> NULL

возвращает ложь (FALSE), как впрочем и условие

2 = NULL

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

Возвращаясь к сути вопроса, мы не можем сказать «Два не равно NULL» потому, что мы не знаем значения справа от знака неравенства, а там как раз может оказаться двойка.

2. Что вернет условие 3 NOT IN (1, 2, NULL)?

Здесь та же история, что и в предыдущем случае. Условие

3 NOT IN (1, 2, NULL)

возвращает ложь (FALSE), как и условие

3 IN (1, 2, NULL)

Причина этого заключается в особенностях работы оператора IN. Проверяя, что определенное значение входит в коллекцию, оператор IN просто сравнивает это значение с каждым элементом коллекции.

Другими словами:

3 IN (1, 2, NULL)

это то же самое, что и

(3 = 1) OR (3 = 2) OR (3 = NULL)

В случае с NOT IN условие:

3 NOT IN (1, 2, NULL)

это то же самое, что и

(3 <> 1) AND (3 <> 2) AND (3 <> NULL)

Как мы знаем из предыдущего примера, 3 <> NULL возвращает ложь, а значит и все условие
(3 <> 1) AND (3 <> 2) AND (3 <> NULL)

тоже будет ложным.

3. Выполнится ли этот запрос?

SELECT 
	order_id,
	order_code,
	SUM(order_value)
FROM 
	orders
GROUP BY
	order_id

Едино правильного ответа на этот вопрос нет — все зависит от базы данных. Проблема этого запроса заключается в том, что колонка order_code не указана в выражении GROUP BY и при этом для нее не определена агрегатная функция. То есть по отношению к колонке order_code мы не знаем группировать ее или группировать по ней.

Если этот запрос будет выполняться в MySQL, то колонка order_code добавится в выражение GROUP BY автоматически и запрос выполнится нормально. Если же этот запрос будет выполняться MS SQL Server, то по умолчанию будет сгенерирована ошибка. Впрочем, это поведение настраивается.

4. Почему не выполнится этот запрос?

SELECT 
	user_name,
	YEAR(user_birth_date) AS year_of_birth
FROM 
	users
WHERE
	year_of_birth = 2000

Запрос не выполнится из-за обращения к псевдониму year_of_birth в выражении WHERE. Дело в том, что псевдонимы полей в SQL используются для форматирования данных уже полученных из базы. Поэтому их можно использовать только в выражениях, которые отвечают за оформление результата, таких как GROUP BY, ORDER BY и HAVING. В выражениях, отвечающих за получение данных, таких как WHERE, нужно использовать оригинальные имена полей.

WHERE
	YEAR(user_birth_date) = 2000

5. Имеет ли значение порядок колонок в составном индексе?

Да.

CREATE NONCLUSTERED INDEX MyInd on users (user_name, user_birth_date);

это не то же самое, что

CREATE NONCLUSTERED INDEX MyInd on users (user_birth_date, user_name);

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

Другими словами, колонки, по которым поиск выполняется чаще всего, должны стоять в составном индексе первыми.

6. Какая разница между TRUNCATE TABLE table_name и DELETE FROM table_name?

Фактически обе эти команды вызовут удаление всех строк из таблицы под названием table_name, но вот произойдет это совсем по-разному:

  1. При вызове команды TRUNCATE таблица полностью сбрасывается и создается снова, в то время как команда DELETE удаляет каждую строку таблицы по отдельности. Из-за этого TRUNCATE отрабатывает значительно быстрее.
  2. Как следствие первого пункта, команда TRUNCATE не вызывает срабатывание триггеров и правил внешних ключей, то есть, очищая таблицу таким способом, можно не бояться каскадного удаления или изменения данных в других таблицах.
  3. В отличие от DELETE команда TRUNCATE не транзакционная. То есть, если в момент ее вызова, таблица table_name будет заблокирована какой-либо транзакцией — может возникнуть ошибка.

7. Какая разница между типами CHAR и VARCHAR?

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

  1. Тип CHAR хранит значение фиксированной длины. Если строка, помещаемая в колонку данного типа, имеет меньшую длину, чем длина типа — строка будет дополнена пробелами. Например, если в колонку типа CHAR(10) записать строку SQL, то она сохранится как SQL       .

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

  2. Для типа CHAR используется статическое распределение памяти, из-за чего операции с ним быстрее, чем с VARCHAR.

Таким образом, тип CHAR подходит для хранения строковых данных фиксированной длины (например, инвентарных номеров, хешей), а для остальных строк больше подойдут VARCHAR или NVARCHAR.

8. Какая разница между типами VARCHAR и NVARCHAR?

Тип NVARCHAR, пожалуй, самый универсальный из строчных типов данных в БД. Он позволяет хранить строки переменной длины в формате Unicode. В этом формате каждый символ занимает 2 байта, а сама кодировка содержит 65 536 символов и включает в себя все языки мира, в том числе иероглифы.

Тип VARCHAR хранит данные в формате SACII. В этом формате каждый символ занимает 1 байт, но отельная кодировка содержит всего 256 символов. Из-за этого для каждого мирового языка выделяется своя кодировка.

Таким образом, в формате VARCHAR стоит хранить строчные данные, которые точно не придется переводить (например, адреса электронной почты). Для других случаев больше подойдет NVARCHAR.

9. Какая разница между UNION и UNION ALL?

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

10. Какая разница между выражениями WHERE и HAVING?

Ну и наконец, вопрос, который задают практически на каждом собеседовании по базам данных: про HAVING.
Выражения WHERE и HAVING используются для фильтрации результата запроса и ожидают после себя некоторое условие, по которому нужно отфильтровать данные. Но, если WHERE работает само по себе и фильтрует данные каждой строки результата по отдельности, то выражение HAVING имеет смысл только в сочетании с выражением GROUP BY и фильтрует уже сгруппированные значения.


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

Все про українське ІТ в телеграмі — підписуйтеся на канал DOU

👍ПодобаєтьсяСподобалось5
До обраногоВ обраному7
LinkedIn

Схожі статті

  • Співбесіда з Go. 200+ запитань для Junior, Middle, SeniorСпівбесіда з Go. 200+ запитань для Junior, Middle, Senior

    Редакція DOU

    Цього разу героєм рубрики, що присвячена технічним співбесідам, є Golang (або просто Go). Як завжди, ми з’ясували у реальних СТО та інших спеціалістів, що проводять технічні інтерв’ю, які питання вони ставлять кандидатам. Результатом ділимось з читачами DOU. 71

  • Співбесіда з iOS. 250 запитань для Junior, Middle, SeniorСпівбесіда з iOS. 250 запитань для Junior, Middle, Senior

    Редакція DOU

    Пропонуємо перелік технічних питань, що стануть у пригоді на співбесіді на посаду iOS Developer. Для зручності ми розділили їх за рівнями та тематикою. Усі питання — від практиків, що проводять технічні інтерв’ю. 37

  • Співбесіда з Ruby. 500+ запитань для Junior, Middle, SeniorСпівбесіда з Ruby. 500+ запитань для Junior, Middle, Senior

    Редакція DOU

    Що потрібно знати, коли проходиш технічну співбесіду з Ruby? Звісно, до всіх запитань готовим не будеш, але ми попросили інтерв’юерів-практиків надіслати свої списки запитань, а потім узагальнили їх в одному матеріалі. 14




Найкращі коментарі пропустити

«Каким вы видите свой запрос через 5 лет?»

Автору спасибі, хоч якась цікава технічна тема, бо все про трактори і ’бест практіси’ від новоспечених сеньйорів.

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

а где каверзные вопросы?

Он позволяет хранить строки переменной длины в формате Unicode. В этом формате каждый символ занимает 2 байта, а сама кодировка содержит 65 536 символов и включает в себя все языки мира, в том числе иероглифы.

Удачный повод порекомендовать еще раз всем прочитать знаменитую статью Джоеля Спольски „The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets (No Excuses!)”

В ней написано, в частности:

Some people are under the misconception that Unicode is simply a 16-bit code where each character takes 16 bits and therefore there are 65,536 possible characters. This is not, actually, correct. It is the single most common myth about Unicode, so if you thought that, don’t feel bad.

Вот есть русский перевод, если не осиливаете оригинал.

181 коментар

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

любопытно. можешь выложить?

не, ну, в UNION из одной части в другую данные не кочуют. Что с подзапросами, что без.

а ну-ка пример субд в которой так алиасы работают? или это лишь ваши фантазии?

Претензия в том, что эта субд ТРЕБУЕТ назначать алисы сабквери

та ну, exists, any, all нормально работают без алиасов. требуется только для derived table.

даже если ты их не юзаешь нигде больше

та ладно. dev.mysql.com/...​an/5.7/en/subqueries.html
почти везде как раз не надо алиас.

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

Помечтайте. Varchar займёт в аккурат на 2 байта больше того же типа CHAR. Всё та же строка, дополненная пробелами. Она просто усекается при выдаче. Но уж точно не двигает всю таблицу при изменении значения в строке.
Хотите переменного значения — BLOBы ваше всё, естественно с последствием в виде ну очень проблемного их восстановления в случае если посыпался индекс.

По этой причине любое сравнение с NULL возвращает ложь.

Это жестоко по отношению к новичкам. И что ещё хуже, этой статьёй могут воспользоваться ХРюши и манагеры с целью тестирования.
Любая логика над NULL вернёт NULL.

3 IN (1, 2, NULL)

А вот это уже скотство от создателей SQL (Оракла то бишь). Операция IN не является логической, это предикат над множеством. Почему множество, поданное списком, принципиально отличается от того же множества, поданного таблицей — а null его знает.

Я к примеру об этой гомосятине узнал только что из статьи, хотя программирую уж почти пару десятков лет. И тому есть простое объяснение:
Использовать IN противопоказано в боевом программировании. Просто забудьте о его существовании, равно как и про BETWEEN. Обе эти операции гарантируют вам построчное сканирование таблицы, то есть заведомый промах по всем индексам. Даже если в условии задано выражение, на индекс попадающее, нередки случаи когда оптимизатор принимает решение о полном сканировании. Да, это тупизм от создателей оптимизаторов (Майкрософт в этом особо отличиться умеет), но для оптимизатора наличие в запросе колонки из индекса — говорит что в индекс он попал, а потом операция не поддающаяся индексации — и жопа.

Почему BETWEEN не применяет индекса — великое ХЗ, но тот же запрос с «больше равно» и «меньше равно» в индексы попадёт гарантированно. Но с IN — не попадёт, даже если будет пустое множество в скобках, всё равно будут просмотрены все строки.

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

index range scan уже отменили?

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

Не знаю как в других ДБ, но Оракл перепишет BETWEEN как >= <= и будет использовать индекс, если он конечно есть.

Вообще задавать такие вопросы без привязки к конкретной ДБ глупо, как и задавать такие вопросы web developerу.
Сначала в погоне за фулстеком web developerы понаписывают запросы, а потом удивляются почему в продакшене все тормозит.
Кесарю кесарево в общем.

Помечтайте. Varchar займёт в аккурат на 2 байта больше того же типа CHAR. Всё та же строка, дополненная пробелами. Она просто усекается при выдаче.

вы не уточнили СУБД, но для SQL Server написанное выше — ахинея
docs.microsoft.com/...​-sql?view=sql-server-2017

char [ ( n ) ] Fixed-length, non-Unicode string data. n defines the string length and must be a value from 1 through 8,000. The storage size is n bytes. The ISO synonym for char is character.

varchar [ ( n | max ) ] Variable-length, non-Unicode string data. n defines the string length and can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size is the actual length of the data entered + 2 bytes.

для MySQL судя по доке тоже
docs.oracle.com/...​01/mysql-5.1-en/char.html

In contrast to CHAR, VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.

для оракла честно говоря не нашел в документации как он раельно хранит varchar

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

ну опять таки, для SQL Server написанное выше полнейший бред (как в смысле написанного так и в стиле изложения) — индекс может не использоваться запросом в том случае если оптимизатор решит что его дешевле просканировать. почему он так решит причин может быть множество — селективность индекса низкая, статистика устаревшая, какой джойн используется (nested loop, merge, hash). единственно сделаю оговорку — с фильтрованными индексами ситуация может быть несколько иной, там действительно были проблемы когда индекс не использовался хотя должен был бы. но вроде как минимум часть этих проблем порешали

Список в худших традициях украинских собеседований.
Первая половина — нафуй не нужные вопросы, не встречаются в реале. Нужны для того, чтобы потешить ЧСВ собеседюущего. Он то знает на них ответы, а заваленные им кандидаты — нет.
Вторая половина — трюизмы, которые знает любой SQL-Developer уровня Junior.

Если этот запрос будет выполняться в MySQL, то колонка order_code добавится в выражение GROUP BY автоматически и запрос выполнится нормально.

Параметр order_code автоматически НЕ добавляется к GROUP BY. Иными словами MySQL НЕ переписывает запрос

SELECT A, B, COUNT(*) FROM T GROUP BY A

как вы написали в запрос:

SELECT A, B, COUNT(*) FROM T GROUP BY A, B

Можете потестировать взяв в качастве A/B primary key — тогда б вам COUNT(*) всегда выдавал, но нет :)

Тут скорее интересен вопрос, что вообще MySQL это допускает и что в итоге выдаст в колонке B.

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

Почитать тут и тоже самое глянуть в версиях !=5.7: dev.mysql.com/...​en/group-by-handling.html

А как у ДОУ с модерацией технических статей? В конкретно этой приводится в корне не верное объяснение причин поведения null в логических выражениях, смысл TRUNCATE, смысл алиаса колонки, описание WHERE и HAVING. Как минимум.

А как у ДОУ с модерацией технических статей?

Анархия!!!

ЗЫ: а почему только технических? #trollface

))) список источников почти везде уместен, а в технической статье имхо необходим

а на доу есть технические статьи?

Яка крайина таки и статти ))

Вопрос очень правильный, единственно, хотел бы акцентировать Ваше внимание но один момент.

Автор описфвает что сие есть для собеседования.
Вот тут вот большой кейс.

Часто испытуемых можно разделить на две групы.

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

Вторая — это, если обобщенно, математики.
Они знают, или понимают, или чувствуют, что математика — наука архисложная. А програмирование так или иначе — прикладная математика.
И тут новый кейс, еще две группы:
—"академики" — люди с глубокими академическими знаниями, которые «не терпят» «приближенных наименований» и «не четких определений, описаний». Для них рабочий код может «работать не правильно», потому что по сути «работать не должен». И они, имея глубочайшие познания" «всегда правы», какой бы ценой это выражалось...
—"энтузиасты" — фанаты, для которых маиематика — это религия («возлюбленная Богиня»), красота которой в ее строгости, гармоничности и закончености логических линий. Рефакторинг кода они будут делать до тех пор, пока весь код не будет лаконичен как украинское «Будьмо!».

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

Если мы проанализируем любой холивар — вот это скорее всего разные стороны — представители этих групп.

Я лично столкнулся с «проблемой этих груп», когда, работая в SoftBistro, написал статью про noSQL Cassandra, одним из тезисов которой было то, что Cassandra ближе к Релятивной модели баз данных, чем SQL...

Для людей «первой группы», «не копающих глубоко», SQL тождественно Релятивной модели баз данных.
Для людей «второй группы», «копаюших строго и глубоко», SQL всего лишь simple (и ПОКА единственная) реализация Релятивной модели баз данных.

(только давайте не поднимать холивар — все по своему правы!)

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

подскажите пожалуйста где и как настроить такое поведение?

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

«Каким вы видите свой запрос через 5 лет?»

Забытым в коде, но всё ещё исправно работающим.

по троичной логике проще всего спросить «что вернет SELECT null = null ?».
если ответит «True, конечно ж», спросить «а почему это выражение может вернуть NULL?». если человек включается и «ааааа, точно» — значит, понимает, просто редко сталкивается.

если всё еще не врубается — ну, тут хз.

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

Спасибо за статью. Если бы не она я бы не почерпнул столько интересного из комментариев!

Не все верно даже в ответах самого автора, что ж говорить за остальных, кого он интервьюировал ?

1. Что вернет условие 2 <> NULL?
Вопреки привычной логике условие
2 <> NULL
возвращает ложь (FALSE), как впрочем и условие
2 = NULL

Неправильный ответ.
Результат определен ANSI SQL стандартом и зависит от настроек сессии.
Для MSSQL по дефолту ANSI NULL ON, результат будет UNKNOWN en.wikipedia.org/wiki/Three-valued_logic

для полного понимания UNKNOW != FALSE & UNKNOWN != TRUE

2. Что вернет условие 3 NOT IN (1, 2, NULL)?
Здесь та же история, что и в предыдущем случае. Условие
3 NOT IN (1, 2, NULL)
возвращает ложь (FALSE), как и условие
3 IN (1, 2, NULL)
Другими словами:
3 IN (1, 2, NULL)
это то же самое, что и
(3 = 1) OR (3 = 2) OR (3 = NULL)
Как мы знаем из предыдущего примера, 3 <> NULL возвращает ложь, а значит и все условие
(3 <> 1) AND (3 <> 2) AND (3 <> NULL)
тоже будет ложным.

Таже история — неправильный ответ.
Если мы примем за истину
что 3 IN (1, 2, NULL). это эквивалент (3 = 1) OR (3 = 2) OR (3 = NULL)

то почему следующий запрос вернет 0 строк ?
SELECT val
from (VALUES (1),(2),(3)) AS T(val)
where

3 IN (1, 2, NULL)

ведь для первой строки Val=1 будет эквивалент (1= 1) OR (1 = 2) OR (1 = NULL) и похоже что выражение будет эвалюировано в TRUE.
Ответ в UNKNOWN стате

3-4 Даже сказать нечего ;)

5. Имеет ли значение порядок колонок в составном индексе?
Да.

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

Я не уверен о какой СУБД идет речь и о каких индексах (HASH, COLUMNSTORED, BTREE/RANGE)

Так вот
Для
HASH/COLUMNSTORED — не имеет значение
BTREE/RANGE — имеет

Похоже автор ведет речь о BTREE/RANGE. Так вот никаких индексов по первой а потом по второй колонке не строится. Что имеет значение так это
— Предикат(ы) поиска в запросах
— Селективность атрибутов в ключе индекса

6. Какая разница между TRUNCATE TABLE table_name и DELETE FROM table_name?
Первая — DDL вторая DML остальное это следствие этого различия.
В отличие от DELETE команда TRUNCATE не транзакционная

Типичная ошибка. Все DDL команды транзакционные (за исключением пары )

7-10 детский сад.

В целом такой уровень вопросов — Trainee, за исключением вопроса 5.
MSSQL (включая ANSI) далеко ушел в развитии, есть и поинтереснее вопросы для интервью.

Результат определен ANSI SQL стандартом и зависит от настроек сессии.

Стандартизаторы SQL (типа ISO/IEC, или когда-то ANSI) вообще не предусматривают нестандартных режимов, это дело вендоров.
Давайте корректнее в формулировках, раз уж взялись исправлять постера.

Похоже автор ведет речь о BTREE/RANGE. Так вот никаких индексов по первой а потом по второй колонке не строится.

Обычно таки строится индекс по суммарному выражению из первой и второй колонки. Именно это наверняка и имелось в виду.
И по такому индексу можно искать по любому из его «подвыражений» от начала и до некоторой точки обрезки. То есть в случае индекса из двух колонок он не даст возможность эффективно искать по второй колонке — для этого нужен другой индекс.
Можно попросить переформулировать, но мне (и многим) было достаточно.

Все DDL команды транзакционные (за исключением пары )

MS SQL — возможно. В других очень долго это было не так.

раз уж тут много умных . понравилось из последнего . mysql . есть таблица где собраны , дата , сумма1 . с одной датой могут быть несколько строк . задача выдать таблицу по каждой дате нарастающим итогом в один запрос. т.е. если есть запись от 1/04 , 1/04 , 2/04, 03/04 , то в текущем месяце результат за 1/04 как сумма первых двух . а за 2/04 это нарастающим , т.е. 1/04 + 1/04 + 2/04 ... за 3/04 будут суммы всех предыдущих и значения за 3/04 ... и т.д.

PS. это возможно .

Это Вы о оконных функциях или рекурсивных запросах? Можно и так и так. И то и то есть в mysql.

один запрос . без окон . SELECT и поехали .

нет , что-то не так . могу сказать что проще . результаты .

www.screencast.com/t/mq1iluLLF

Сбила с толку фраза

текущем месяце

. В сумму шли только данные из того-же месяца, что и дата.

Убираем «and DATE_FORMAT(dates.created_at, ’%Y-%m-01′) = DATE_FORMAT(data_table.created_at , ’%Y-%m-01’)» из 4-й строки

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

SELECT ym
, @a1 := @a1 + a1 amount1
FROM
( SELECT DATE_FORMAT(date,’%Y-%m’) ym
, SUM(amount1) a1
FROM my_table
GROUP
BY ym
) x
, (SELECT @a1:=0) vars
ORDER
BY ym;

Хорошее решение. Спасибо. Многое почерпнул для себя. Сказывается, то на MySQL я написал всего десятка два запросов.

1) Если прицельно смотреть на решение, сразу бросается в глаза, что оно работает до тех пор пока GROUP BY ym, кроме группировки выполняет еще и сортировку. Это характерно только для MySQL и может поменяться в любой момент.
2) Как только мы захотим, чтобы в списке дат не было пропусков решение нужно менять полностью.
3) В моем, действительно боле неуклюжем, запросе:
а) сомневаюсь, что будет индекс включающий только дату и сумму. Хотя-бы потому что MySQL сразу засунет туда и первичный ключ, и мы получим ту же самую таблицу повторенную еще раз.
в) DATE_FORMAT, вероятно, и так убьет попытки оптимизатора применить индекс, если индекс не по вычислимой колонке с DATE_FORMAT.
г) вероятно, на одну дату приходится много записей, потому сомнительно, чтобы index sacn + row fetch давал бы выигрыш. Но, тут действительно нужно смотреть на план запроса.
д) соглашусь, рост сета, но запрос легко исправить:
select start.day, sum(records.amount)
from (select DATE_FORMAT(created_at, ’%Y-%m-%d’) day from data_table group by 1) start,
(select DATE_FORMAT(created_at, ’%Y-%m-%d’) day, sum(amount) amount from data_table group by 1) records
where start.day >= records.day
group by start.day;

В этом случае nested loop будет гоняться по двум небольшим таблицам в памяти. Первый запрос легко заменить на другой эмиттер дат, например stackoverflow.com/...​eries-equivalent-in-mysql

5) Давай согласимся, что как раз для этого и были придуманы оконные функции. И, слава Богу, они появились и в MySQL

Это не просто возможно я решается 5 способами
MSSQL
— Дедовский Курсором
— До 2012
1. OUTER EQUI + GROUP BY
2. Subquqry as expresion
— 2012 — SUM() OVER ( ORDER BY ). | SUM() OVER ( ) ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
— Quirky Update through reusable variable

В вашем кейсе нужно сделать grouping by date а потом running total считать

sqlperformance.com/...​es/grouped-running-totals

Сказать честно ? Вот если кто мне задаст такие вопросы когда нибудь , я просто встану и уйду , просто будет не о чем говорить . Если вы сомневаетесь что ответит база данных — попробуйте . Конечно разумно чтоб не запустить full scan по огромной таблице ( если у кого есть такие таблицы ) . Не знаете — есть гугль — подскажет . Задача современного программиста решать проблемы .

в данной статье отличие where и having описаны НЕПРАВИЛЬНО . В принципе неправильно . Помогло вам ? тут надо начинать с вычисляемых функций , алиасов , индексов .При этом по разному будет для разных баз . Но спрашивать это — глупо . правильный вопрос на собеседовании должен звучать так . как быстро найти дупликаты записей ( id ) у которых значение полей xx одинаковое . Вы сразу увидите понимает как решить задачу или нет . При этом конечно для профи должен быть правильный ответ , для джуниора правильное направление ( допустимы небольшие ошибки в синтаксисе , но конечно не where/having ). Что вам пользы от человека который учил и заучил . А первое что лучше проверить IQ и посмотреть что человек делал для этого , дурной код сразу в глаза бросается .

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

Не знаете — есть гугль — подскажет

Такой подход не годится, когда нужно оценивать результат своей работы. А то я знаю людей, у которых сортировка была с n^3 сложностью и они не догадывались, что можно сделать несколько эффективней.

В MySQL запрос с группировкой может выполняться, а может нет — зависит от настроек и версии. В 5.1 выполнится по дефолту (а может и всегда — не помню когда настройку ввели), в 5.7 по дефолту не выполнится, но можно включить совместимость со старыми версиями.

4. Почему не выполнится этот запрос?

Тут уместнее было бы в целом расписать «порядок выполнения запроса».

При вызове команды TRUNCATE таблица полностью сбрасывается и создается снова

Реализация и особенности truncate варьируются для каждой СУБД, там только по отличиям можно статью сопоставимых размеров накатать.

Какая разница между выражениями WHERE и HAVING

Каверзность вопроса зашкаливает.

Я когда то в прошлом веке перепутал и вместо HAVING написал WHERE — Interbase 4 убил НТ сервер.

Цель вопросов с NULL — проверить память кандидата или что-то другое?

и на собеседовании это как таблица умножения nyukers.blogspot.com/2018/03/null.html
или зачем это помнить если там логики нет. Великое Ничто.

NVL(value, new_value) поможет?

Проектирование баз данных. КПИ. Второй семестр второго курса.

В Postgres CHAR, VARCHAR, и text будут храниться абсолютно одинаково (начиная с версии 8.1, раньше не проверял), и значения в колонке char(10) НЕ будут дополняться справа пробелами.

В Postgres TRUNCATE вполне себе будет хотеть эксклюзивный лок на таблицу, будет ждать, пока ее можно будет залочить, и его вполне себе можно вызвать в транзакции (и соответственно откатить).

Почему не выполнится 4й запрос — можно было бы дать и более подробное объяснение — потому что порядок выполнения запроса:

FROM.
ON.
JOIN.
where.
GROUP BY.
WITH CUBE или WITH ROLLUP.
HAVING.
SELECT.

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

Если не знать ничего о троичной логике и не понимать что вернет сравнение, то получается что-то в стиле «дети, это [что попадет в выборку] понять невозможно, это нужно заучить»... Не уверен, что это правильный посыл в статье, нацеленной на новичков.

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

И соотв. если мы (здесь обобщение) ищем таки их то и писать in null или там is not null (с коррекцией на правильный синтаксис) просто зная что такая концепция есть и решается вот так.

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

Опять же ж возвращаясь к уровню экспертизы вот в таком «вопросе на собеседовании» видимо будет упомянута «троичная логика» а вы уверены что это вообще она? вы способны рассуждать на уровне эксперта? а почему вы уверены что это вообще «логика»? а что вообще такое «логика» применительно к данному контексту? (всё риторические вопросы как обобщение)

А так да, технически сравнение с null возвращает unknown либо null а может и еще что-то в зависимоти от базы данных.

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

Ящитаю, что задавать вопросы типа «а как моя любимая БД обработает вот такой хитрый случай?» есть смысл только тогда, когда вы ищете ДБА с уровнем «гуру по этой конкретной БД». В остальных случаях(а их большинство), стоит проверять знания по ISOшному сиквелу с универсальными вопросами.

Потому, например, вопросы 6, 9, 10, в принципе, норм, остальные — так себе.

Интересно, по сиквелу вообще можно написать статью чтобы ни у кого не пригорело? :)

вот вот...
..вопрос... если на проєкте не используется абстрактная модель баз данных — есть ли смысл идти на этот проэкт? А вопросы по сыквел могут как раз говорить про это.. (Если, конечно, не ищется именно спец по БД)...
..уже правильнее были бы вопросы хотябы по Spring Hibernate JPA или Active Record...

..хотя после того, как я увидел позицию Сениор програмист Вродпресс, я уже ничему не удивляюсь!

..хотя после того, как я увидел позицию Сениор програмист Вродпресс, я уже ничему не удивляюсь!

Справедливости ради первый по величине (в абсолютных цифрах инстансов) рынок веба тут не подкопаешься и потом а оно действительно им «среднему клиенту» нннада?

ЗЫ: свежий вопрос реальных клиентов по теме «хостер прислал маляву всё пропало с 1-го числа гугл считает только https хостер предлагает продать сертификат что делать!?» а на деле история за то что гугло хром с какого-то там числа будет «помечать в браузере сайты без https» чем собственно уже какое-то время с год занимается тот же ж файрфокс вы сирёзно считаете что всем этим людям нужный «Spring Hibernate JPA или Active Record...»?

ЗЫ: другая классика «типа эмбеддеры» запилили «бек» на скале а «фронт» на русте а как ставить его реальные клиенты ни в зуб ногой даже тот же ж руст как оказалось собрать на таргет платформах строго через Ж каждый первый реальный клиент сичас пешут на сях обсуждают в кодревю использовать override для «си++ консистентности» ))

PostgreSQL вернет ничего на

2 <> NULL

и

3 NOT IN (1, 2, NULL)

, но не „ложь”


leo=# SELECT 2 <> NULL;
 ?column?
----------

(1 row)

SELECT 3 NOT IN (1, 2, NULL);
 ?column?
----------

(1 row)

# как выглядить "ложь"

leo=# SELECT 2 <> 2;
 ?column?
----------
 f
(1 row)

Из документации:

Some applications might expect that expression = NULL returns true if expression evaluates to the null value. It is highly recommended that these applications be modified to comply with the SQL standard.

С ходу как-то так
mysql> SELECT VERSION(), 2 <> NULL;
±----------±----------+
| VERSION() | 2 <> NULL |
±----------±----------+
| 5.7.21 | NULL |
±----------±----------+
1 row in set (0.00 sec)

Як для початківця в SQL — цінна стаття. Дякую!

Как раз недавно была проблема с truncate / delete: нужно было перезаписывать полностью данные в таблицу, из которой тянула данные вьюха, из которой тянуло данные конечное приложение. Пришлось потратить некоторое время, чтобы понять, что проблема в truncate :) После замены на delete все работает отлично.

ни один дев в здравом уме не будет писать запросы с налами

Да, у него там вместо налла будет имя nullable колонки, oooops.

Но, все равно, вопросы про наллы так себе, так как дб-специфичны.

Если в С-подобных языках NULL значит отсутствие какого-то значения

Полагаю по остальным вопросам включая сам SQL экспертиза того же ж уровня ))

..хм... автор четко и чесно написал свой уровень — web developer
..какие еще тут могут біть вопросы?

Каюсь бы не прав ))

ЗЫ: и признаю это в последнее время лично общая проблема «сперва не разбираясь подтягивать к своему уровню и выставлять претензии что нет так» пока что делать как бороть не придумал ((

Интересно, спасибо.

Каверзных вопросов, конечно, можно накидать ещё много. Из того, что с ходу вспоминается и весьма полезно для анализа на интервью:
1. Специфика квотинга строк и имён полей/таблиц в разных движках.
2. Правила передачи произвольных аргументов через агенты/коннекторы, как делать правильный квотинг (хотя она больше зависит от коннекторов), чтобы не было слишком мрачно.
3. Обращение агрегирующих функций с NULLʼами.
4. Подзапросы со ссылкой на корневой запрос.
5. [сеньорский уровень 1] Оконные функции.
6. [сеньорский уровень 2] Рекурсивные запросы с WITH.
7. Особенности обращения транзакций с уровнями изоляций (сеньорское — чем отличается serialized от snapshot?) Специфика разных движков (чем MVCC постгреса отличается в последствиях от полу-MVCC оракла).
8. LIMIT и OFFSET (очень частая штука в веб-движках!), как где пишутся, чем плохо и как заставлять работать за адекватное время.
9. Автоконверсии типов (почему select 2 = ’2′ даёт true).
Фуух, можно продолжать много дальше, но устал. Расписывать ответы тоже нет вдохновения :)

Дійсно питання не такі вже й «каверзні» і в залежності від типу СУБД відповіді будуть різними на деякі запитання. Але мені більше подобається читати технічні дискусії в коментаріях подібних статтей, де гуру з досвідом 10+ років можуть розповісти більше цікавих речей ніж сама стаття (:

Тому побільше б технічних статтей на доу.

1. Что вернет условие 2 <> NULL

?
такое условие возвращает UNDEFINED. Выглядит оно как False, только потому-что условия where и having пропускают дальше только истинные значения.

3. Выполнится ли этот запрос?

Как ни странно, тут поведение MySQL вполне правильное, из-за опциональной фичи из срандрата SQL T301, Functional dependencies. Слава Богу, другие производители не спешат ее внедрять. Хотя в том же MS SQL можно ее включить.

4. Почему не выполнится этот запрос?

Запрос выполнится в MySQL. Потому-что у него парсер лох. Этот же парсер проигнорирует inline объявление foreign key и разрешит on в cross join.
Другие БД — молодцы.

5. Имеет ли значение порядок колонок в составном индексе?

Объяснение — ересь. Всех по этому поводу нужно отправлять на use-the-index-luke.com
Если говорить о каверзных вопросах и MySQL, то нужно спрашивать про его дурную реализацию, когда в индекс включается не адрес строки, а целиком первичный ключ.

6. Какая разница между TRUNCATE TABLE table_name и DELETE FROM table_name?
Как следствие первого пункта, команда TRUNCATE не вызывает срабатывание триггеров и правил внешних ключей, то есть, очищая таблицу таким способом, можно не бояться каскадного удаления или изменения данных в других таблицах.

— то есть данные в БД станут не консистентными?
Тот же Постгре и Оракл не даст сделать TRUNCATE если есть данные связанные по внешним ключам

В отличие от DELETE команда TRUNCATE не транзакционная. То есть, если в момент ее вызова, таблица table_name будет заблокирована какой-либо транзакцией — может возникнуть ошибка.

А тут кандидата нужно попросить привести примеры DDL и DML команд. TRUNCATE, действительно DDL команда. А транзакции это про DML, куда, внезапно, относится и SELECT. Если вдаваться в деали — в Постгре DDL тоже транзакционен и TRUNCATE можно откатить.

7. Какая разница между типами CHAR и VARCHAR?
Для типа CHAR используется статическое распределение памяти, из-за чего операции с ним быстрее, чем с VARCHAR.

из за страничной организации таблиц это далеко не всегда так и сильно зависит от длины данных. В большинстве случаев хранить данные комплектнее более выгодно, чем выравнивать их. Потому что стоимость чтения с диска больше стоимости разбора блока данных процессором.
Боле того, в том же Постгре все, что длиннее N символов автоматом хранится в TOAST таблице, а все, что длиннее 2000 автоматом архивируется.

8. Какая разница между типами VARCHAR и NVARCHAR?

Давай еще спросим:
1) Почему («AAA» = «aaa») может быть true
2) Почему (some_long_string = some_other_long_string ) может быть true, даже если строки разные
3) Почему column_name = ’STRING’ и column_name like ’STRING’ могут давать разные результаты

9. Какая разница между UNION и UNION ALL?

Забыл, что UNION делает сортировку данных.

10. Какая разница между выражениями WHERE и HAVING?

страх и ужас.

Если задавать «каверзные» вопросы, тогда:
сколько будет
select 1 from any_table where 1!=1 having count(*)=0;

Хотя в том же MS SQL можно ее включить.

Плиз ткните в BOL. Интересно для себя.

2 <> NULL вернет NULL. Да и вообще в MySQL нет true и false. Сравнивать с NULL надо через IS

ой мамочки... А можно спросить — что такое охватывающий индекс? И почему я так офигел от «ответа» на вопрос по составному индексу?
TRUNCATE и DELETE тоже красота...

Статтю треба назвати «10 кавєрзних вопросов по SQL для начінающего веб девелопера». Люди ведуться на заголовок, а тут — пєчаль-тоска...

я прошу прощения: а с каких пор сикель стал исключительно территорией веб девелоперов?

фор хум хау %)

хинт: это было намеренно. тяпница все таки

облом

error: ‘materialize’ was not declared in this scope
—+materialize —+parallel(gilrs,8), parallel(SiQeL, 8)
^~~~~~~~~~~
: note: suggested alternative: ‘glMaterialiv’
—+materialize —+parallel(gilrs,8), parallel(SiQeL, 8)
^~~~~~~~~~~
glMaterialiv
error: ‘gilrs’ was not declared in this scope
—+materialize —+parallel(gilrs,8), parallel(SiQeL, 8)
^~~~~
error: ‘parallel’ was not declared in this scope
—+materialize —+parallel(gilrs,8), parallel(SiQeL, 8)
^~~~~~~~
error: ‘SiQeL’ was not declared in this scope
—+materialize —+parallel(gilrs,8), parallel(SiQeL, 8)

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

Стаття написана веб-девелопером (принаймні це вказане в профілі ТС’а). Тому «кавєрзность» цих питань — це з точки зору автора. Для досвідченого фахівця в цій статті немає нічого корисного. Тому я і написав

для начінающего веб девелопера

Тогда не сбивайте новичков некорректными ответами на свои вопросы :). С композитным индексом ой.

Другими словами, колонки, по которым поиск выполняется чаще всего, должны стоять в составном индексе первыми.

По результатам анализа запросов я вижу что чаще всего у меня вылняются запросы с поиском по полю Name ИЛИ по полю Birthday. Исходя из логики Вашего ответа нужен композитный индекс на эти два поля. Так ведь? Но там будет беда...
На самом деле порядок полей в композитном индексе должен совпадать с порядком полей в where а еще лучше охватывающий индекс.
Еще другими словами — выражение «колонки долдны стоять первыми» некорректно, потому что возникает вопрос — а какая первее?
PS
MS SQL и Interbase:). MySQL запросы гдубоко не оптимизировал. Но по логике должно быть так же.

Воот. Так подправьте что бы не было неоднозначности.

А, понял. Смотрите, я имел в виду, если уж мы делаем составной индекс, то имеет ли в нем значение порядок колонок. Например, если мы создадим только один составной индекс
CREATE NONCLUSTERED INDEX MyInd on users (Name,Birthday);
и больше никаких, то он ускорит поиск по сочетанию Name+Birthday и по колонке Name, а по колонке Birthday не ускорит.

Внезапно, и поиск по колонке Birthday такой индекс может ускорить! По крайней мере MsSQL старых версий вполне себе ускорял (можно было проверить по плану выполнения).

Например, запрос SELECT COUNT(*) FROM USERS WHERE Birthday=@Birthday будет идти по индексу, а не по таблице.

Разгадка одна. В саму таблицу/кластерный индекс записывается вся строка. В обычный индекс записываются только выбранные поля, следовательно, вес каждой строчки в индексе будет меньше, следовательно, больше строк вмещается на одну страницу.
И хоть там будет делаться не index seek а index scan, все равно по индексу он будет идти быстрее чем по самой таблице — просканить нужно будет меньшее кол-во страниц.

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

Взагалі кажучи, це залежить від реалізації індексу на фізичному рівні у конкретної СУБД. Тобто в екзотичних випадках (не просте b-tree) швидкість пошуку по окремій колонці, що входить в індекс, може не залежати від порядку колонок (щось типу GIN індексу в Postgers).

Он позволяет хранить строки переменной длины в формате Unicode. В этом формате каждый символ занимает 2 байта, а сама кодировка содержит 65 536 символов и включает в себя все языки мира, в том числе иероглифы.

Удачный повод порекомендовать еще раз всем прочитать знаменитую статью Джоеля Спольски „The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets (No Excuses!)”

В ней написано, в частности:

Some people are under the misconception that Unicode is simply a 16-bit code where each character takes 16 bits and therefore there are 65,536 possible characters. This is not, actually, correct. It is the single most common myth about Unicode, so if you thought that, don’t feel bad.

Вот есть русский перевод, если не осиливаете оригинал.

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

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

В дополнение к этой знаменитой статье. Если кто-то решил, что он мазохист, ну то есть, что юникод — это просто, пусть вначале прочтет секцию Assume Brokenness этого ответа stackoverflow.com/a/6163129. Ответ в целом касается Perl, но эта секция — универсальна и несёт боль. Много боли.
примеры:

Code that assumes every lowercase code point has a distinct uppercase one, or vice versa, is broken. For example, „ª” is a lowercase letter with no uppercase; whereas both „ᵃ” and „ᴬ” are letters, but they are not lowercase letters; however, they are both lowercase code points without corresponding uppercase versions. Got that? They are not \p{Lowercase_Letter}, despite being both \p{Letter} and \p{Lowercase}.

Code that assumes only letters have case is broken. Beyond just letters, it turns out that numbers, symbols, and even marks have case. In fact, changing the case can even make something change its main general category, like a \p{Mark} turning into a \p{Letter}. It can also make it switch from one script to another.

Code that believes once you successfully create a file by a given name, that when you run ls or readdir on its enclosing directory, you’ll actually find that file with the name you created it under is buggy, broken, and wrong. Stop being surprised by this!

Code that believes that stuff like /s/i can only match „S” or „s” is broken and wrong. You’d be surprised.

Спасибо за ссылку. Эти кейсы ускользнули даже от меня :-(

Статья примечательна тем, что еще лет 10-15 назад имела аттрибут «мастрид» практически для любого программиста, и в первую очередь в вебе, а на сегодня большинство веб-девелоперов абсолютно безболезненно обходятся без этих знаний. Потому что технологии уже поднялись на пару уровней абстракции, и больше беспокоиться о подобном нет необходимости. Так выпьем же за уровни абстракции! :)

Как мы видим прямо из этой статьи — рано радоваться. Уникод СИЛЬНО отличается от просто строк, достаточно сильно чтобы охреневать каждый день — почему к апперкейсу не приводится, сколько места занимает, а уж что с регэкспами делается — вообще песня — ты ж читал Assume Brokenness из камента выше?

Короче, я думаю, что актуально еще огого как...

Леша, конечно актуально. Но ведь правда же, 10 лет назад любой сайт смотрелся кракозяблами если программист не знал ничего кроме ASCII, а сейчас уже выросло поколение веб-девелоперов, которым ни разу не приходилось задумываться на эту тему. Безусловно на все случаи асбтракции не распространяются и как мы знаем от того же самого автора — они склонны «протекать»

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

насчет ФК для MS SQL это неправда, если на таблицу ссылаются внешние ключи транкейт просто не пройдет.

You cannot use TRUNCATE TABLE on tables that:
•Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)

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

і справді цікавіше ніж більшість статей про переїзди, недоїзди і т.д.
мене ще на співбесідах часто цікавить не тільки знання функцій, а вміння їх застосувати
типу знаю count, group by і having. а дублікати в колонці знайти не можу

Какая разница между типами VARCHAR и NVARCHAR

В Postgres хранят в varchar обычно.

В Postgres обычно хранят в text, все равно в TOAST уедет. varchar нужен, только чтобы ограничить длину строки.

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

отвечают за оформление результата, таких как GROUP BY, ORDER BY и HAVING

Жесть (как и 10-ый вопрос).

1 и 2 тоже зависят от BD.
По крайней мере в ms sql есть set ansi_nulls on|off

А вообще для кого эти вопросы-то? Тому, кто пишет код и базу видит только ̶в̶ ̶б̶и̶н̶о̶к̶л̶ь̶ через ORM это и не надо, там автоматом ’where X in not null’ сгенерируется. А суровые базовики такие «каверзные» вопросы среди ночи в пьяном состоянии помнят.

Автору спасибі, хоч якась цікава технічна тема, бо все про трактори і ’бест практіси’ від новоспечених сеньйорів.

По правде тут с Вами согласен. Хоть и чуть включил критику, но то что мало техстатей это верно подмечено. Приятно порадовало в пятницу :)

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

2 <> NULL вернет UNKNOWN, а не FALSE. Поскольку SQL Server оперирует троичной логикой при сравнении операторов.

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

Что-то я такой настройки в SQL Server не встречал. По правде не знаю, как в MySQL, но в SQL Server такой запрос работать не будет:

SELECT a, b
FROM (
    SELECT a = 1, b = 1
) t
GROUP BY a

Column ’t.b’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

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

Суть порядка столбцов в оптимальном использовании predicate pushdown на этапе физического чтения из индекса. То есть не вычитывать весь индекс, а потом по нему фильтровать, а выгребать только то что нужно (именно потому SQL Server и старается пропихивать предикаты до операций соединения):

IF OBJECT_ID('tempdb.dbo.#t') IS NOT NULL
    DROP TABLE #t
GO

SELECT TOP(1000) a = 0, b = 'A'
INTO #t
FROM [master].dbo.spt_values

INSERT INTO #t VALUES (1, 'B')
GO

CREATE NONCLUSTERED INDEX ix1 ON #t (a, b)
GO

SELECT COUNT(1)
FROM #t
WHERE b = 'B'
    AND a = 1

SELECT COUNT(1)
FROM #t
WHERE b = 'B'

Table '#t'. Scan count 1, logical reads 2, ....
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Table '#t'. Scan count 1, logical reads 5, ....
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

Execution plan

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

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

DECLARE @a TABLE (a INT)

BEGIN TRAN
INSERT INTO @a VALUES (1)
ROLLBACK

SELECT * FROM @a
Для типа CHAR используется статическое распределение памяти, из-за чего операции с ним быстрее, чем с VARCHAR

Смотря как проверять и что делать. Зачастую CHAR менее производительный (не забываем что пробелы добавляются автоматом), чем VARCHAR (точно также как и VARCHAR быстрее NVARCHAR)

DECLARE @a CHAR(8000) = '1'
      , @b VARCHAR(8000) = '1'

DECLARE @s DATETIME = GETDATE()

WHILE LEN(@a) != 8000 BEGIN
    SET @a = '1' + @a
END

SELECT DATEDIFF(MICROSECOND, @s, GETDATE()) / 1000.

SET @s = GETDATE()

WHILE LEN(@b) != 8000 BEGIN
    SET @b = '1' + @b
END

SELECT DATEDIFF(MICROSECOND, @s, GETDATE()) / 1000.
разница между UNION и UNION ALL

Разница в том, что данные операторы на физическом уровне работают по разному. UNION ALL последовательный, а UNION — параллельно выполняется. Пример тут.

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

SELECT COUNT(1)
WHERE 1=0

SELECT COUNT(1)
WHERE 1=0
GROUP BY ()

Или почему тут такой результат:

SELECT N'уйти в туман как ежику'
WHERE 'reason' = 'no'
HAVING COUNT(*) = 0

Или такой:

SELECT MAX(N'вроде и смешно а причины нет как и результата')
WHERE 1=0

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

Ну я бы так не сказал. Есть ситуации где данные нюансы имеют смысл и часто в коде могут встречаться. Например вот такой пример:

DECLARE @a INT = 1
      , @b INT = 1

SELECT @a = 2
WHERE 1=0

SET @b = (SELECT 2 WHERE 1=0)

SELECT @a, @b

Другое дело... стоит ли такое спрашивать на собесах уровня jun / mid. Я лично не спрашивал.

забавный у вас код если такие перлы там встречаются, тем более часто. ни разу за 13 лет не встречал такого в продуктовом коде. ИМХО такой код не должен пройти через любое адекватное код ревью. у меня сильное подозрение что тот кто такое писал перешел на SQL Server с какой-то другой СУБД, например с оракла.

Код и вправду забавный, но пишет народ крайне разные. Есть и откровенная индусня. Есть и толковые ребята, которые так пишут. Когда-то ревьювал код написанный MVP, то вот такое встречалось в цикле:

DECLARE @a INT = 1
      , @b INT

SELECT @a = 2
     , @b = @a

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

Либо вариант с использованием меток:

lbl:

DELETE TOP(1000) FROM ...
WHERE ...

IF @@rowcount > 0
    GOTO lbl

вместо использования цикла или курсора. Как говориться на вкус и цвет фломастеры разные.

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

Truncate можно откатить в MS SQL, в других СУБД придется восстанавливать, либо использовать специальные возможности.

О чем должна сказать ссылка на документацию?

sdb=# create table t1 as select * from newfile;
SELECT 61
sdb=# truncate table t1;
TRUNCATE TABLE
sdb=# rollback;
WARNING:  there is no transaction in progress
ROLLBACK

Я даже не знаю, как Вам ответить. Из двух мест

  • Документация: truncate откатывается если текущая транзакция не будет закоммичена
  • Интерпретатор: нет активной транзакции
Вы не можете сделать выводов, что не так?..

Да, был неправ. Почему-то был уверен, что транкейт точно так же делает неявную фиксацию в процессе как в оракле(

забавная штука
в доке в рестрикшинах по транкейту написанно (docs.microsoft.com/...​uncate-table-transact-sql)
>TRUNCATE TABLE cannot be ran inside of a transaction.

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

In Azure SQL Data Warehouse and Parallel Data Warehouse:

TRUNCATE TABLE is not allowed within the EXPLAIN statement.

TRUNCATE TABLE cannot be ran inside of a transaction.

TRUNCATE можно откатить, а значит эта команда поддерживает транзакции.

Не знаю, як в SQL Server, а в Oracle все навпаки: TRUNCATE сам по собі фіксує транзакцію і його неможливо відкотити. Хоча в реду лог теж пишеться деякий мінімум інформації по HWM.

Плюс еще и в том, что TRUNCATE можно откатить, а значит эта команда поддерживает транзакции.

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

Какое отношение удалённые таблицы имеют к TRUNCATE в Oracle?

Для того что-бы выполнить, TRUNCATE нужно иметь права DROP ANY TABLE. Потому всегда думал, что в этом случае старая таблица отправляется в корзину, а на его месте создается такая же.
Перечитал документацию. Действительно, там другой механизм удаления. В результате ни Rollback ни Flashback не работает. «You cannot roll back a TRUNCATE TABLE statement, nor can you use a FLASHBACK TABLE statement to retrieve the contents of a table that has been truncated.»

Насколько я понимаю, если включен flashback data archive — то и после truncate можно получить данные. Но это уже такое... нечастое.

Для меня проблема в другом:
Вы пишите, что вопросы по языку структурированых вопросов (SQL), когда на самом деле ответы на эти вопросы правильные только в реализации несколькими конкретными СУБД, — таким образом в других СУБД это попросту неправда (1, 2, 3, 6, 8) конкретно для Postgres. Сами вопросы я считаю очень даже правильными. Проблема в ответах.

Я с удовольствием почитал о реализации SQL в Ms/My SQL, но не хочу чтобы это воспринималось как будто в любой базе, реализующий стандарт, это работает так, а не иначе.

Ну 1 и 2 просто неточно описаны, поскольку нулл является неопределенным значением, то сравнение с нуллом(ами) или not in (something, null) возвращает такой же неопределенный результат, что автор автоматически приписал к ложному результату. А так-то все правильно)

5,6,9,10 — вопросы норм, остальное все «ну я тут пришел показать, что почитал мануал перед тем как тебя спрашивать»

а где каверзные вопросы?

Подобного рода комментарии никак не способствуют появлению бОльшего количества технических статей на ДОУ. Если у вас есть какая-то критика по существу, напишите об этом, как это сделал выше Sergey Syrovatchenko. Если вы с чем-то не согласны, напишите с чем именно, автор сделает выводы или вступит с вами в дискуссию.

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

Комент абсолютно по делу(все, кто надо — поняли что хотели сказать), а вот коментарии «сперва добейся, начни с себя» и т.п. действительно не способствуют.

Для того чтобы написать подобную статью не обязательно обращаться к «большому количеству ИТ-специалистов». Просто возьмите книжку «как выучить _имя_технологии_ за 12 часов» и скопируйте оттуда что-нибудь. Для вас не пишут качественный контент не потому что боятся коментариев, достаточно посмотреть на то что публикует DOU чтобы понять что технические статьи не являются основным профилем этого ресурса, куда интереснее штамповать ерунду в рубриках «как я работаю» и прочих не технических категориях.

Если я правильно понял, то Сергей есть MS SQL Server DBA. В этом случае его ответы не полные и не совсем точные, в отдельных случаях просто не правильные.

Пожалуйста пните в каких местах и что неверно. Разумную критику всегда воспринимаю на ура. А вот пустословие штука опасная :)

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

SET ANSI_NULLS OFF
SELECT 1
WHERE 1 != NULL

А так меня уже прямо заинтриговало )))

1) declare @i int=1
set ANSI_NULLS off
if @i<>NULL select 1 else select 2
1
set ANSI_NULLS on
if @i<>NULL select 1 else select 2
2
Где здесь ошибка и есть ли такая настройка.

2)

Плюс еще и в том, что TRUNCATE можно откатить, а значит эта команда поддерживает транзакции.

После commit можно откатить результат выполнения TRUNCATE?
Не полный ответ на вопрос — чем отличается TRUNCATE и DELETE

3) Индексы — как насчет включить в индекс первым полем, если тип поля Varchar(max)
и как такое поле добавить в индекс? Такое поле можно добавить, знаете как?

4) UNION и UNION ALL основное отличие в реализации UNION есть сортировка, а также и UNION и UNION ALL могут выполняться параллельно, это зависит от схемы базы и установок MS SQL SERVER.

1) Спорить не буду. Не написал, хотя штука такая есть (за все время ни разу к слову не включали).

2) Откатить можно:

SELECT a = 1
INTO #t

BEGIN TRAN
TRUNCATE TABLE #t
ROLLBACK

SELECT * FROM #t

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

В плане различий BOL можно почитать. Тем более выше народ тоже комментарии оставлял.

3) Если по такому полю нужно фильтровать, то почему не FTS? Если не вариант то сделать вычисляемое поле и усечь его чтобы все влезало в любимые 900 байт (начиная с 2016го увеличили до 1700 байт).

SELECT a = CAST(NULL AS VARCHAR(MAX))
INTO #t

ALTER TABLE #t ADD b AS CAST(a AS VARCHAR(8000))

CREATE NONCLUSTERED INDEX ix ON #t (b)

4) Хотите сказать что UNION всегда будет приводить к сортировке?

DECLARE @a TABLE (a INT PRIMARY KEY)
DECLARE @b TABLE (b INT PRIMARY KEY)

SELECT *, b = 1 FROM @a
UNION
SELECT *, b = 2 FROM @b

тут оптимизатор вообще решил конкатенацию входящих потоков делать.

В плане «параллельности» речь не о паралелльных планах и не о настройках. А о том как эти операторы работают когда идет row goal через TOP(N). Вычитываются ли данные последующим оператором если текущий вернул нужное число строк:

SELECT TOP(1) AddressID
FROM (
    SELECT TOP(1) AddressID
    FROM Person.[Address]
    WHERE AddressLine1 = @AddressLine

    UNION ALL

    SELECT TOP(1) AddressID
    FROM Person.[Address]
    WHERE AddressLine2 = @AddressLine
) t

т.е. если первый запрос вернет строку, второй физически данные не вычитает в отличии от UNION.

К слову мне Ваши замечания понравились :) Спасибо.

SELECT *, b = 1 FROM @a
UNION
SELECT *, b = 2 FROM @b

Это о чем, если есть явное указание?
Насчет TOP(n) — оптимизатор тоже может ошибаться. Нужен seek хинт.
У вас были претензии и вам ответили.

А насчет

ALTER TABLE #t ADD b AS CAST(a AS
VARCHAR(8000))

так это уже к вашему руководству. И очень хочется узнать реакцию?

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

Хочется ответочку то давайте :) вариант такой:

DECLARE @a INT
SELECT @A

всегда будет работать? И от каких настроек зависит.

Улыбнуло, все будет зависит от collation

От COLLATE на какой базе? Истина как известно кроется в деталях :)

подозреваю что в tempDB.. но вот только не получается чтоб проверить—>
«Cannot alter the database ’tempdb’ because it is a system database». надо инстанс переустанавливать?

Раз я тоже ответа не дождался. Что ж тогда я скажу.

COLLATE на уровне базы влияет на то как будут интерпретироваться имена объектов на этапе построения плана выполнения (binding). То есть если у нас имя таблицы написано строчными, а в метаданных хранится заглавными и на базе регистрозависимый коллейшен, то будет ошибка. И это распространяется на все пользовательские объекты.

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

ок, но как можно поменять колейшн базы мастер?

Претензий не было. Думал будет более хардкорно :) хотя как для пятницы отлично все. Спасибо.

DOU все таки более общий ресурс. Все детали лучше обсуждать на sql.ru

Спасибо что не прошли мимо и проявили свой уровень опыта, вы наверно и по сырам эксперт? Технические статьи без вас никак не обойдутся.

2 <> NULL

возвращает ложь (FALSE)

Т.е. not (2 <> NULL) возвращает TRUE? ;)

Вернёт FALSE, т.к. ХЗ что в результате, такой себе код Шрёдингера
Собственно для сравнения с NULL следует использовать IFNULL, тогда не будет проблем

Гм, но ведь not (FALSE) = FALSE это как-то странно, нет?

логика троичная, Null возвращает «ни да, ни нет» для всех сравнений, и даже null = null вернет False

Если

null = null вернет False

то not (null = null) вернет True?)
(запустим обсуждение по второму кругу)

дабы круги не наматывать, достаточно внимательно прочитать: логика троичная. То есть, если не будет явного приведения к boolean, то все логические конструкции будут возвращать False («ни да ни нет» при переводе в bool == False)

Null («unknown») такой же результат логических операций, как true/false.
Если речь идет о типе boolean в контексте sql, то там 3 доступынх значения, а не 2: true/false/null("unknown").
Вот вам несколько абсолютно валидных запросов:
select null::boolean; select true AND null; select false AND null;
Вы либо это знаете и не поняли троллинга, либо не знаете.

Ну, в общем да, это был намек на троичную логику, в свете которой «null = null вернет False» опять-таки неверно :)

sdb=# select (2<> null) ;
 ?column?
----------

(1 row)


sdb=# select not (2<> null) ;
 ?column?
----------

(1 row)


sdb=# select not (2<> null) is unknown;
 ?column?
----------
 f

В постгресе хотя бы пустая строка — это таки строка.

postgres=# select ('' is not null);
 ?column?
----------
 t
(1 row)


postgres=# select (''='');
 ?column?
----------
 t
(1 row)

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

mysql> select 2 <> null;
+-----------+
| 2 <> null |
+-----------+
|      NULL |
+-----------+
1 row in set (0,00 sec)

mysql> select not (2 <> null);
+-----------------+
| not (2 <> null) |
+-----------------+
|            NULL |
+-----------------+
1 row in set (0,00 sec)

MySQL пишет NULL там, где по стандарту требуется unknown, но суть именно такова. По стандарту булевская логика трёхзначная, и третье состояние «неизвестно» (null или unknown) обладает своей спецификой. Например, true or unknown равно true, а вот true and unknown равно unknown.

Автор статьи, похоже, то ли вспоминал режим с выключенным ansi_nulls, то ли вообще как-то «слева» анализировал.

Тема этих NULL вообще очень сложная, копья активно ломаются на тему, полезны они или нет, можно ли их в базах допускать или нет, есть очень влиятельные противники (например, C.J.Date, один из отцов-основателей РСУБД, резко против них). Я смотрю на это относительно спокойно после того, как мы в мониторинге заводили два разных NULL (назывались иначе, по сути было not available — датчика вообще нет в конкретной железяке, и sense failure, снятие не удалось в конкретный момент); по сравнению с этим проблематика SQL громоздка и неудобна, но не удивляет :)

(UPD: но крайне не хватает упрощённых операторов сравнения, которые бы выдавали false или true при попадании в них null, в зависимости от оператора.
IFNULL/COALESCE помогает, но выглядит временами странно, и нет гарантии правильного «продвижения» их внутрь оптимизатора запросов.)

Для расширенного понимания темы и методов обхода можно глянуть ещё тут и вообще по словам ifnull, coalesce, nvl, генерацию строк с NULL с разными (left/right/full) outer join...

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

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

Підписатись на коментарі