Бази даних і SQL: тренувальні питання з поясненнями

Безкоштовні тестові питання з теми Бази даних і SQL з підказками й поясненнями, трьох рівнів: ключові терміни й факти, застосування понять, кейси та аналіз.

Безкоштовна задачаЛегка

Яке обмеження гарантує, що стовпець ніколи не залишиться порожнім (NULL)?

Наступна задача →
Тренуйтеся безкоштовно: Бази даних і SQLТренуйтеся зараз: Бази даних і SQLБезкоштовних задач щодня: 5. Без картки.

Що ви тренуєте

  1. Легкаключові терміни й факти
  2. Середнязастосування понять
  3. Складнакейси та аналіз

Вивчайте тему «Бази даних і SQL» з нуля

1Що таке реляційна база даних

Реляційна база даних зберігає дані в таблицях. Таблиця схожа на електронну таблицю: кожен рядок — це один запис, кожен стовпець — одна властивість.

Магазин може мати таблицю customers зі стовпцями id, name і city та таблицю orders зі стовпцями id, customer_id і total.

Первинний ключ — це стовпець або набір стовпців, що однозначно ідентифікує кожен рядок. Він ніколи не повторюється й ніколи не буває порожнім. Тут це id кожної таблиці.

Зовнішній ключ — це стовпець, що посилається на первинний ключ іншої таблиці. В orders customer_id вказує, який клієнт зробив замовлення.

Так таблиці пов’язані: один клієнт може мати багато замовлень, але кожне замовлення належить одному клієнту. Це зв’язок «один до багатьох».

SQL (Structured Query Language) — це мова, якою створюють таблиці, змінюють дані та ставлять до них запитання.

Самоперевірка Що таке первинний ключ?
  1. Індекс, що прискорює пошук
  2. Перший стовпець кожної таблиці
  3. Стовпець або набір стовпців, що однозначно ідентифікує кожен рядок
  4. Стовпець, що посилається на рядок в іншій таблиці

Відповідь: Стовпець або набір стовпців, що однозначно ідентифікує кожен рядок. Первинний ключ унікальний і ніколи не NULL, тож вибирає рівно один рядок. Стовпець, що посилається на іншу таблицю, — це зовнішній ключ.

2Читання даних за допомогою SELECT

Більшість запитань стосується оператора SELECT. Він складається з речень, які завжди записують у такому порядку:

SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY …
  • SELECT перелічує потрібні стовпці. DISTINCT усуває дубльовані рядки.
  • FROM називає таблицю або таблиці, об’єднані разом.
  • WHERE залишає лише рядки, що відповідають умові, наприклад WHERE total > 20.
  • GROUP BY збирає рядки з однаковим значенням в одну групу, щоб рахувати чи додавати за групами.
  • HAVING залишає лише групи, що відповідають умові. Він може використовувати агрегати, чого WHERE не може.
  • ORDER BY сортує результат, за замовчуванням за зростанням; додайте DESC, щоб найбільше було першим.

Агрегатні функції зводять багато рядків в одне значення: COUNT, SUM, AVG, MIN і MAX.

База даних не виконує речення в порядку, в якому ви їх пишете. Вона спершу виконує FROM, потім WHERE, GROUP BY, HAVING, SELECT і нарешті ORDER BY.

Інші оператори змінюють дані: INSERT додає рядки, UPDATE змінює рядки, а DELETE видаляє рядки. CREATE, ALTER і DROP змінюють самі таблиці.

Самоперевірка Вам потрібні лише клієнти з більш ніж 5 замовленнями. Яке речення фільтрує за COUNT(*) > 5?
  1. DISTINCT
  2. HAVING
  3. ORDER BY
  4. WHERE

Відповідь: HAVING. Кількість існує лише після групування рядків, а HAVING фільтрує групи. WHERE виконується до групування, тож не може використовувати агрегат, як-от COUNT.

3З’єднання, нормалізація та транзакції

З’єднання (join) поєднує рядки двох таблиць за умовою, зазвичай зовнішній ключ = первинний ключ:

FROM customers c JOIN orders o ON o.customer_id = c.id
  • INNER JOIN (або JOIN) залишає лише рядки, що мають відповідник в обох таблицях.
  • LEFT JOIN залишає кожен рядок лівої таблиці. Де відповідника немає, стовпці правої таблиці дорівнюють NULL.
  • NULL означає, що значення відсутнє або невідоме. Перевіряйте його через IS NULL, ніколи через = NULL.

Нормалізація розбиває таблиці так, щоб кожен факт зберігався один раз. Це дозволяє уникнути оновлення того самого факту в кількох місцях.

  • 1NF: кожна клітинка містить одне атомарне значення, без повторюваних груп.
  • 2NF: 1NF, і кожен неключовий стовпець залежить від усього ключа, а не від його частини в складеному ключі.
  • 3NF: 2NF, і жоден неключовий стовпець не залежить від іншого неключового стовпця.

Транзакція об’єднує оператори в одну одиницю. COMMIT робить її остаточною, а ROLLBACK скасовує. Транзакції підпорядковуються ACID: атомарність (усе або нічого), узгодженість, ізольованість і довговічність.

Самоперевірка Клієнт не зробив жодного замовлення. Що відбувається з цим клієнтом у customers LEFT JOIN orders?
  1. Запит завершується помилкою
  2. Клієнт з’являється один раз для кожного замовлення в таблиці
  3. Клієнт з’являється один раз, з NULL у стовпцях замовлення
  4. Клієнта вилучено з результату

Відповідь: Клієнт з’являється один раз, з NULL у стовпцях замовлення. LEFT JOIN залишає кожен рядок лівої таблиці та заповнює відсутню праву сторону значенням NULL. Вилучення клієнта — це те, що робить INNER JOIN.

4Метод крок за кроком

Щоб написати запит або з’ясувати, що повертає заданий запит:

  1. Знайдіть таблиці, що містять дані, і як вони пов’язані ключами.
  2. Напишіть FROM із з’єднаннями. Оберіть LEFT JOIN, коли рядки без відповідника мають залишитися.
  3. Додайте WHERE для умов на окремі рядки.
  4. Якщо запитання каже «на» чи «для кожного», додайте GROUP BY та агрегат.
  5. Додайте HAVING для умов на агрегат, потім ORDER BY, якщо порядок важливий.
  6. Перевірте результат на малому прикладі: перелічіть рядки після кожного речення, в порядку, в якому їх виконує база даних.

Для теоретичного запитання спершу назвіть поняття (ключ, тип з’єднання, нормальна форма, властивість ACID), потім виберіть варіант, що відповідає його означенню.

5Розв’язаний приклад

Простежте кожен крок. Спробуйте передбачити наступний, перш ніж його відкрити.

Розв’язаний приклад

Таблиця customers має рядки (1, Ana), (2, Ben) і (3, Cas). Таблиця orders має рядки (id, customer_id) = (10, 1), (11, 1), (12, 2) і (13, 1). Що повертає цей запит? SELECT c.name, COUNT(o.id) FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.name

1

Виконайте FROM і з’єднання

Кожного клієнта поєднано із замовленнями, що мають його id. Ana відповідають замовлення 10, 11 і 13. Ben відповідає замовлення 12.

Cas не має замовлень. Оскільки це LEFT JOIN, Cas залишається в результаті, з NULL для o.id.

2

Перелічіть з’єднані рядки

  • Ana, 10
  • Ana, 11
  • Ana, 13
  • Ben, 12
  • Cas, NULL

Це 5 рядків.

3

Групування за name

GROUP BY c.name утворює три групи: Ana з 3 рядками, Ben з 1 рядком і Cas з 1 рядком.

4

Підрахунок у кожній групі

COUNT(o.id) рахує лише значення, що не NULL. Ana отримує 3, а Ben отримує 1.

Cas має один рядок, але його o.id дорівнює NULL, тож Cas отримує 0.

5

Перевірте відповідь

Ana, Ben і Cas разом мають 3 + 1 + 0 = 4 замовлення, стільки ж, скільки рядків в orders. З COUNT(*) натомість Cas помилково отримав би 1, бо COUNT(*) рахує сам рядок.

Відповідь

Ana 3, Ben 1, Cas 0.

6Типові помилки

  • Використовувати агрегат у WHERE. Умови на COUNT, SUM чи AVG належать до HAVING.
  • Писати = NULL. Будь-яке порівняння з NULL невизначене; використовуйте IS NULL або IS NOT NULL.
  • Використовувати INNER JOIN, коли рядки без відповідника мають залишитися. Використовуйте LEFT JOIN.
  • Забувати умову з’єднання. Без ON кожен рядок поєднується з кожним рядком (перехресне з’єднання).
  • Вибирати стовпець, якого немає ні в GROUP BY, ні всередині агрегату.
  • Плутати DELETE (видаляє рядки) і DROP TABLE (видаляє всю таблицю).
  • Виконувати UPDATE або DELETE без WHERE. Це змінює кожен рядок таблиці.
Самоперевірка Таблиця має 10 рядків, а стовпець col дорівнює NULL у кожному рядку. Що повертає SELECT COUNT(col)?
  1. 0
  2. 10
  3. 1
  4. NULL

Відповідь: 0. COUNT(col) рахує лише значення, що не NULL, тож повертає 0. COUNT(*) рахує рядки й повернув би 10.

7Як виглядають екзаменаційні запитання

  • Легкий: ключові терміни та факти. Що роблять SELECT, WHERE, ORDER BY чи DELETE, первинні й зовнішні ключі, що означає NULL.
  • Середній: застосування понять. Правильний запит для завдання, WHERE проти HAVING, внутрішні проти зовнішніх з’єднань, нормальні форми, ACID.
  • Складний: випадки й аналіз. Підзапити, рівні ізоляції та їхні аномалії, BCNF, віконні функції, індекси та продуктивність запитів, SQL-ін’єкція.

Коли запит задано, простежте його на кількох рядках у порядку FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. Більшість пасток видно в межах трьох рядків.

Вивчайте крок за кроком у застосункуВивчайте крок за кроком у застосунку

Приклади питань

Приклад 1Легка

Що робить UNION з результатами двох запитів SELECT?

  1. AЗ’єднує їх поруч за ключем
  2. BЗалишає лише рядки, що є в обох
  3. CОб’єднує їх в один результат і видаляє дублікати рядків
  4. DЗалишає лише рядки, які є в першому запиті, але не в другому
Показати підказки
  1. Обидва запити мають мати однакову кількість сумісних стовпців.
  2. Дві неправильні відповіді тепер закреслено. Одна з двох решти правильна.
Показати відповідь і пояснення

Чому це відповідь

UNION ставить рядки обох запитів один під одним і видаляє дублікати; UNION ALL зберігає дублікати.

Відповідь

C · Об’єднує їх в один результат і видаляє дублікати рядків
Приклад 2Середня

Що повертає SELECT SUM(amount) FROM orders, коли речення WHERE не відповідає жодному рядку?

  1. AПомилку
  2. B0
  3. CNULL
  4. DПорожній рядок
Показати підказки
  1. Тут SUM відрізняється від COUNT.
  2. Дві неправильні відповіді тепер закреслено. Одна з двох решти правильна.
Показати відповідь і пояснення

Чому це відповідь

SUM, AVG, MIN і MAX повертають NULL для порожньої множини, а COUNT повертає 0; COALESCE(SUM(amount), 0) натомість дає 0.

Відповідь

C · NULL
Приклад 3Складна

Що повертає SELECT name, dept, AVG(salary) OVER (PARTITION BY dept) FROM emp?

  1. AЛише працівників, які заробляють більше за середнє свого відділу
  2. BКожен рядок працівника разом із середньою зарплатою його відділу
  3. CПомилку, бо name немає в GROUP BY
  4. DОдин рядок на відділ із його середньою зарплатою
Показати підказки
  1. Віконні функції не згортають рядки.
  2. Дві неправильні відповіді тепер закреслено. Одна з двох решти правильна.
Показати відповідь і пояснення

Чому це відповідь

На відміну від GROUP BY, віконна функція зберігає кожен вхідний рядок і додає значення, обчислене для його розділу, тут — відділу.

Відповідь

B · Кожен рядок працівника разом із середньою зарплатою його відділу

Запитання й відповіді

Бази даних і SQL: що це?

Магазин може мати таблицю customers зі стовпцями id, name і city та таблицю orders зі стовпцями id, customer_id і total.

Так таблиці пов’язані: один клієнт може мати багато замовлень, але кожне замовлення належить одному клієнту. Це зв’язок «один до багатьох».

Бази даних і SQL: як вивчити крок за кроком?
  1. Що таке реляційна база даних
  2. Читання даних за допомогою SELECT
  3. З’єднання, нормалізація та транзакції
  4. Метод крок за кроком
  5. Розв’язаний приклад
  6. Типові помилки
  7. Як виглядають екзаменаційні запитання
Бази даних і SQL: що ви тренуєте?
  • Легка: ключові терміни й факти
  • Середня: застосування понять
  • Складна: кейси та аналіз

Кожне питання має чотири варіанти відповіді, і лише один правильний. Застрягли? Спершу відкрийте невелику підказку, а повне пояснення — лише коли воно знадобиться.

Як проходить тренування

  • Кожне питання має чотири варіанти відповіді, і лише один правильний.
  • Застрягли? Спершу відкрийте невелику підказку, а повне пояснення — лише коли воно знадобиться.
  • Ваш прогрес, помилки та збережені задачі зберігаються, тож перед іспитом ви зможете все повторити.

Курси з цією темою

Основи інформатики

Інші теми: Інформатика

Системи численняПідмережі IPv4Комп'ютерні мережіАлгоритми та складністьСтруктури даних

Робочий аркуш для друку

Робочий аркуш «Бази даних і SQL» з відповідями
Тренуйтеся безкоштовно: Бази даних і SQLТренуйтеся зараз: Бази даних і SQLБезкоштовних задач щодня: 5. Без картки.