Где взять образец базы данных для практики SQL

1. Почему одного CSV недостаточно

Прежде чем объяснять: подумайте о последней таблице, с которой вы работали. На какой вопрос вы не могли бы ответить, не открыв рядом с ней второй файл?

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

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

2. Возьмите Чинук, тот, с которого стоит начать.

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

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

https://github.com/lerocha/chinook-database/releases/download/v1.4.5/Chinook_Sqlite.sqlite

Это около 1 МБ. Щелкните ссылку правой кнопкой мыши и выберите «Сохранить ссылку как» или вставьте ее в адресную строку браузера. Расширение файла .sqlite, и вы также можете увидеть .db или .sqlite3 используется в другом месте для файла того же типа. SQLite не заботится о том, какой из трех вы используете; они все просто имя.

Игнорируйте другой файл в этом выпуске, Chinook_Sqlite.sql. Это сценарий инструкций по созданию базы данных, а не сама база данных, и его запуск сегодня вам не нужен.

3. Откройте его и загрузите запрос, подтверждающий его.

Перед объяснением: вы собираетесь выполнить запрос, который возвращает 8 строк. Что может показать вам этот результат, чего не может показать простой просмотр файла на диске?

Открыть Браузер БД для SQLite, выберите «Открыть базу данных» и выберите только что загруженный файл. Слева вы должны увидеть список таблиц: «Альбом», «Исполнитель», «Клиент», «Сотрудник», «Жанр», «Счет», «Счет-фактура», «Медиатип», «Плейлист», «ПлейлистТрек», «отслеживать». Одиннадцать из них.

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

SELECT 'Album' AS tbl, COUNT(*) AS rows FROM Album
UNION ALL SELECT 'Artist',      COUNT(*) FROM Artist
UNION ALL SELECT 'Customer',    COUNT(*) FROM Customer
UNION ALL SELECT 'Employee',    COUNT(*) FROM Employee
UNION ALL SELECT 'Genre',       COUNT(*) FROM Genre
UNION ALL SELECT 'Invoice',     COUNT(*) FROM Invoice
UNION ALL SELECT 'InvoiceLine', COUNT(*) FROM InvoiceLine
UNION ALL SELECT 'Track',       COUNT(*) FROM Track;

tbl          rows
-----------  ----
Album        347
Artist       275
Customer     59
Employee     8
Genre        25
Invoice      412
InvoiceLine  2240
Track        3503

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

Два числа, за которые нужно держаться, потому что они используются в остальной части страницы: 412 счетов и 2240 строк счета-фактуры.

4. Ваше первое объединение двух таблиц

Перед пояснением: в таблице «Счета-фактуры» записана стоимость каждой продажи, а в таблице «Клиенты» указано, где живет каждый покупатель. Для какого из двух вопросов «что мы продали» и «где живут наши покупатели» нужны обе таблицы сразу?

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

SELECT c.Country,
       COUNT(*)            AS invoices,
       ROUND(SUM(i.Total), 2) AS revenue
FROM Invoice i
JOIN Customer c ON c.CustomerId = i.CustomerId
GROUP BY c.Country
ORDER BY revenue DESC
LIMIT 5;

Country  invoices  revenue
-------  --------  -------
USA      91        523.06
Canada   56        303.96
France   35        195.1
Brazil   35        190.1
Germany  28        156.48

Линия, выполняющая работу, ON c.CustomerId = i.CustomerId. В нем говорится: строка счета-фактуры и строка клиента принадлежат друг другу, если идентификатор клиента, указанный в счете, совпадает с собственным идентификатором клиента. Этот общий столбец является ключевым, и это линия, нарисованная на рисунке вверху. Если вам нужен полный отчет о том, как соединения влияют на количество строк, SQL-СОЕДИНЕНИЯ является руководством для этого.

Обратите внимание, что 91 + 56 + 35 + 35 + 28 = 245, а не 412, потому что LIMIT 5 сократили список до пяти стран. Несогласованная сумма обычно представляет собой фильтр, который вы забыли написать, и LIMIT считается за один.

5. Нортвинд, Сакила и собственные данные комплекта: когда каждый прав

Еще три имени всплывают постоянно. Вот честное сравнение с цифрами, которые я измерил, а не с теми, которые повторяются в Интернете.

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

6. Дело в дырах в данных

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

SELECT COUNT(*)                    AS total,
       COUNT(Composer)             AS has_composer,
       COUNT(*) - COUNT(Composer)  AS missing
FROM Track;

total  has_composer  missing
-----  ------------  -------
3503   2525          978

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

COUNT(*) считает строки. COUNT(Composer) подсчитывает строки, в которых этот столбец действительно содержит значение. 978 треков не имеют записи композитора, что составляет 28% каталога. Ничто не содержало ошибок, ничто вас не предупреждало, и любой отчет, который вы создаете, сгруппированный по композитору, молча описывает остальные 72%. Этот разрыв и есть то, что NULL в SQL вот-вот, и встретить его здесь, в игрушечной базе данных, гораздо дешевле, чем встретиться с ним перед клиентом.

Таблица «Клиент» имеет такую ​​же форму отверстия. Из 59 клиентов только у 10 зарегистрирована компания и только у 30 — государство. Пробел — это факт сбора данных, а не ошибка, которую нужно скрыть.

7. Посмотрите, как 412 строк превращаются в 2240.

Самая дорогостоящая ошибка SQL — это соединение, которое незаметно умножает строки, и эта база данных покажет вам это в одной строке.

SELECT COUNT(*) AS rows_after_join
FROM Invoice i
JOIN InvoiceLine l ON l.InvoiceId = i.InvoiceId;

rows_after_join
---------------
2240

Вы начали с таблицы, содержащей 412 счетов-фактур, и получили 2240 строк. Ничего не сломано. В каждом счете-фактуре есть несколько строк, по одной на каждую купленную дорожку, поэтому объединение строк в счета-фактуры повторяет счет-фактуру один раз для каждой строки. Среднее значение: 2240 ÷ 412 = 5,4 трека на продажу.

Опасность заключается в том, что произойдет дальше. Писать SUM(i.Total) против этого объединенного результата, и общая сумма каждого счета добавляется один раз в строке, поэтому счет из 5,4 строк учитывается 5,4 раза, и ваш показатель дохода оказывается в несколько раз завышенным. Это будет похоже на правдоподобное число. Ничто вас не предупредит.

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

Что пошло не так и как это исправить

Пять вещей, которые останавливают людей именно на этой странице.

Загрузка сохранена в виде текстового файла. Некоторые браузеры переименовывают Chinook_Sqlite.sqlite к .txtили откройте его на экране в виде стены символов. Щелкните ссылку правой кнопкой мыши и используйте «Сохранить ссылку», а не щелкайте ее левой кнопкой мыши. Если он уже сохранен неправильно, переименуйте файл обратно, и он откроется нормально, потому что расширение никогда не делало его базой данных.

«Файл не является базой данных». Почти всегда это означает, что вы загрузили веб-страницу, а не файл, и преимуществом является размер: реальный файл Чинук имеет размер около 1 МБ, а страница с ошибкой HTML — несколько килобайт. Сначала проверьте размер файла, а затем загрузите его еще раз.

Имена таблиц не будут соответствовать вашему запросу. «Чинук» использует имена в единственном числе, Invoice и не Invoices. Нортвинд использует множественное число, Orders и Customers. Учебники постоянно смешивают эти два понятия. Если вы получили сообщение «нет такой таблицы», прочитайте список на боковой панели, а не руководство.

Пробелы в имени таблицы. У Нортвинд есть стол, который буквально называется Order Details. Запишите это в двойных кавычках, SELECT * FROM "Order Details"или SQL считывает пробел как конец имени.

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