Відкрийте і перегляньте таблицю Рейтинг.

Виконайте запит на відшукання в таблиці Відмінники записів, що повторюються.

Вікно бази даних => Закладка Запити (але нічого не вибирати) ■=> Створити (кнопка) <> Виберіть режим Записи, що повторю­ються ■=> ОК "=> Виберіть таблицю Відмінники ■=> Далі <> Задайте поля, де можна виявити небажані повторення, наприклад, Номер, перевівши цю назву в поле пошуку повторень ■=> ГОТОВО. Перепишіть номери записів, що повторюються, у звіт. Закрийте запит як Запитб.

10* Вилучіть з таблиці Відмінники записи, що повторюють­ ся, за допомогою запиту на вилучення і доповнення.

Розв'язування задачі потребує виконання такого алгоритму: 1) скопіюйте таблицю Відмінники у буфер обміну і вставте її (лише структуру) як таблицю Відмінники2; 2) зробіть у структурі поле Номер ключовим; 3) на базі старої таблиці створіть запит на доповнення нової таблиці всіма записами; 4) виконайте цей запит, незважаючи на конфліктну ситуацію — одержете записи без повторень. Розглянемо ще один спосіб. Відкрийте Запиті у режимі конструктора. Доповніть запит таб­лицею Відмінники. Вилучіть з конструктора таблицю Оцінки, клацнувши в ній і застосувавши команду Вилучити. В умові по­шуку по полю Номер зазначте один під одним номери записів, які треба вилучити. Запустіть на виконання запит на вилучення. Увага! Будуть вилучені всі копії записів, що повторюються. Тепер потрібно доповнити цю таблицю втраченими записами. Відшукайте їх за допомогою простого запиту в іншій таблиці, де вони не повторюються, за записаними у звіт номерами.

Збережіть таблицю Відмінники чи Відмінники2 як ново- створену без записів, що повторюються.

12. Створіть нову таблицю Оцінки2, відобразивши у ній три перші поля з таблиці Оцінки тих студентів, які мають хоча би одну двійку (чи трійку, якщо двійок немає) з будь-якого предмета.

Створіть новий Запит7, у якому скасуйте режим виведення на екран усіх полів, окрім перших трьох, а умову (=2) зазначте в різних рядках у полях оцінок. Змініть тип запиту на Створення НОВОЇ таблиці. Таблицю назвіть Оцінки2. Запустіть запит.

Відкрийте таблицю Оцінки2 і перегляньте її.

14. Сконструюйте Запитв для виявлення записів у таблиці Оцінки, які не мають підпорядкованих записів у таблиці Оцінки2, тобто з'ясуйте, хто зі студентів не має заборго­ ваностей.

На закладці Запити натисніть на кнопку Створити і виберіть останній тип запиту "Записи без підпорядкованих" => ОК. Вико­найте вказівки майстра. Виберіть таблицю Оцінки як основну о Далі, а Оцінки2 — як підпорядковану о Далі. У наступному вікні налагодьте зв'язок між полями НОМЕР в обох таблицях. Між таблицями буде налагоджено зв'язок у цьому випадку один до одного о Далі. Занесіть усі поля таблиці Оцінки для відображення в запиті ■=> Далі. Змініть назву запиту на Запитв ^> Готово.

15. Сформулюйте і реалізуйте три будь-які запити до бази даних "Склад".

Закрийте базу даних. Закінчіть роботу. Здайте звіти.

 

208

14 Практикум

ЛЛ

Контрольні запитання

1. Яке призначення баз даних?

2. Як у базі даних відшукати потрібні записи?

3. Яке призначення фільтрів?

4. Для чого призначена команда Застосувати фільтр?

5. Які є типи запитів?

6. Як створити запит?

7. Як виконати пошук по текстовому полю?

8. Чи можна з відфільтрованих записів створити нову таблицю?

9. Що таке запит?

 

10. Яка відмінність між використанням фільтра і запиту?

11. Яке призначення команди Скасувати (Відмінити) фільтр?

12. Що таке простий запит на вибірку?

13. Яке призначення конструктора запитів?

14. Як створити нову таблицю за допомогою запитів?

15. Як побудувати умову зі сполучником "або" у запиті?

16. Що таке запит на доповнення?

17. Які є способи створення запитів?

18. Як додати таблицю у вікно конструктора запитів?

19. Як перемістити поля з таблиці у бланк запиту?

20. Як упорядкувати записи, використовуючи запит?

21. Як змінити тип запиту?

22. Як побудувати умову зі сполучником "і" у запиті?

23. Що таке запит з параметром?

24. Як модифікувати запит?

25. Як скопіювати частину структури таблиці в нову таблицю?

26. Як створити таблицю з обчислювальним полем?

27. Як відшукати і вилучити з таблиці записи, що повторюються?

28. Опишіть умову для відшукання записів про студентів, які народилися від 1 квітня до ЗО травня.

29. Опишіть умову відшукання записів про студентів, номери телефонів яких починаються на 69.

30. Опишіть умову вибірки записів про студентів, які живуть на вулиці Науковій.

 

31. Опишіть умову вибірки записів про студентів, прізвища яких починаються на букви "С" або "П".

32. Опишіть умову вибірки записів про студентів, які живуть на вулиці Науковій або Садовій.

33. Опишіть умову вибірки записів про студентів, які мають з усіх предметів "4" або "5".

34. Як визначити середню оцінку групи з математики?

35. Як визначити середні оцінки групи з усіх предметів?

Практична робота № 31 Тема

MS Access. Мова SQL.

Мета

Уміти створювати запити мовою SQL.

План

1. Призначення мови SQL.

2. Керування таблицями.

3. Створення запитів на вибірку записів.

4. Пошук даних у базі даних.

5. Застосування команди SELECT.

Теоретичні відомості

1. Призначення мови SQL. Мова SQL призначена для побу­дови баз даних і керування ними, головно для створення різноманітних запитів з метою відшукання потрібних даних. Мова SQL має більші можливості, ніж технологія QBE, є універ-сальнішою і дає змогу створити нестандартні запити. Мова стандартизована під назвою SQL ANSI 92 і реалізована в різних програмних середовищах та на різних комп'ютерних платфор­мах. У програмі MS Access мова використовується в дещо видо­зміненому і не цілком сумісному зі стандартом варіанті, що має назву SQL Microsoft Jet.

За допомогою команд мови можна створити об'єкти бази даних, наприклад, таблиці, модифікувати структуру раніше створених об'єктів, побудувати запити до бази даних на вибірку записів, в тому числі параметричні, перехресні, на створення нової таблиці, на обчислення в таблицях тощо.

2. Керування таблицями. Щоб перейти в режим SQL, потріб­но відкрити будь-яку базу даних або під час створення нової бази даних перейти на закладку Запити і виконати команду Ство­рити запит за допомогою конструктора. Після цього пере­ходимо в режим SQL за допомогою команд з головного меню Вигляд О Мова SQL. Отримаємо вікно для побудови запиту засобами мови, куди вводять речення мови.

Речення може починатися зі слів CREATE, SELECT, IN­SERT, UPDATE, DELETE, ALTER, DROP тощо. Повне речення називають конструкцією SQL (або запитом SQL). Воно скла­дається з одної чи декількох команд і закінчується символом «;». Виконують конструкцію командою Виконати або натис­кають на кнопку запуску запиту (!) на панелі інструментів.

210

Розглянемо головні команди і конструкції мови.

Для створення нової таблиці слід ввести команду

CREATE TABLE таблиця (полеї типі, поле2тип2,...);

Назву таблиці і полям дає користувач. Типи полів такі: integer — цілі числові дані; char або text(n) — текстові дані, де п — довжина даного; real — короткі дійсні числові дані; float — довгі дійсні числові дані; binary(n) — логічні дані зі значеннями true, false із зазначенням довжини даного; bit — логічні дані зі значеннями 1, 0; counter — лічильник; datetime — дата і час; money — грошові дані; image — картинка.

Одне з полів можна зробити ключовим, дописавши після назви його типу службові слова PRIMARY KEY.

Якщо назва таблиці чи поля містить пропуск чи інший розділовий символ, то її беруть у квадратні дужки, наприклад,

CREATE TABLE Складі ([Номер товару] integer, [Назва

товару] text(30), Ціна real, Кількість integer, Імпорт bit,

Картинка image);

Модифікувати структуру таблиці можна такою командою:

ALTER TABLE таблиця дія COLOMN поле тип;

Над полем визначені такі дії:

ADD—додати, DROP—вилучити, ALTER—змінити тип поля.

Щоб вилучити всю таблицю з БД, виконують команду

DROP TABLE таблиця;

Додавати запис у кінець створеної таблиці можна так:

INSERT INTO таблиця(поле1,поле2,...)

VALUES (значенняі, значення2,...);

Порожню таблицю заповнюють даними у візуальному режи­мі, перейшовши на закладку Таблиці і відкривши потрібну таблицю для введення даних. Для заміни даних у полях існуючої таблиці є команда

UPDATE таблиця SET поле=вираз WHERE умова;

Приклад 1. Внести зміни в таблицю Складі, якщо ціна телевізорів зменшилася на 10%. Розв'язок:

UPDATE Складі SET Ціна = Ціна *0.9

WHERE Назва = "телевізор";

Записи вилучають з таблиці за допомогою команди

DELETE FROM таблиця WHERE умова;

Значення поля можна вилучати шляхом створення запиту на оновлення поля значенням Null.

3. Створення запитів на вибірку записів. Вибірка — це динамічний набір даних з бази даних, який відображається на екрані комп'ютера. Вибірку записів створюють за допомогою конструкції SELECT, що має такий загальний вигляд:

SELECT предикат список полів

FROM таблиця {IN адреса зовнішньої БД на диску}

{WHERE умова пошуку}

{GROUP BY поле для групування записів}

{HAVING додаткова умова пошуку в групі}

{ORDER BY список полів для впорядкування);

Команди, записані у фігурних дужках, є необов'язковими.

Предикатом може бути службове слово ALL (показати всі знайдені записи), ТОР п — показати перші п записів, TOP n PERCENT — показати перші п% записів, DISTINCT — показати лише різні записи.

Список полів містить назви полів, які відображатимуться у вибірці. Елементи списків відокремлюються комами. Усі поля таблиці позначаються так: таблиця.* або *, якщо розглядається одна таблиця.

Джерелом даних може бути одна або декілька таблиць з поточної чи зовнішньої бази даних.

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

Якщо таблиць є декілька, то їх оголошують списком у команді FROM. Якщо між двома таблицями є внутрішній зв'я­зок по деякому полю, наприклад, [Номер товару], то команду FROM треба писати так:

FROM таблиця 1 INNER JOIN таблиця2

ON таблиця 1.[Номер товару]=таблиця2. [Номер товару].

Команда ORDER BY призначена для впорядкування отрима­них записів в алфавітному чи зворотному порядку. Відповідний напрямок упорядкування задається предикатами ASC чи DESC, що записуються після назви поля.

Приклад 2. З таблиці Складі вибрати перші три записи з усіма полями про товари, ціна яких менша від 100 грн. Розв'язок:

SELECT TOP 3 *

FROM Складі

WHERE Ціна < 100;

4. Пошук даних в базі даних. Команда WHERE є найваж­ливішою у конструкції SELECT. Власне вона призначена для відбору потрібних записів згідно з деяким критерієм (умови

212

пошуку). Умова пошуку — це простий або складений логічний вираз. Простий логічний вираз — це два вирази, з'єднані опе­рацією відношення: =, <, >, <=, >=. Відношення «не рівно» позначається <> або!=.

Вирази можуть містити назви полів і сталі величини, з'єд­нані допустимими операціями. Складені логічні вирази утворю­ють з простих за допомогою логічних операцій NOT, AND, OR, XOR та інших так, як це прийнято у мовах програмування Visual Basic чи Qbasic. Розглянемо приклади умов пошуку:

1) [Назва товару]="телефон";

2) [Дата виготовлення]=#5/21/05#;

3) NOT Продавець="Афоня" або Продавець!= "Афоня";

4) Мат > З AND Фіз > З AND Інформ > 3;

5) [Ціна товару] > 20.5 AND [Ціна товару] < 85.

Для останнього прикладу є зручніша конструкція, компакт­ніше, якщо назва поля довга:

[Ціна товару] BETWEEN 20.5 AND 85.

Усі дати за останні 30 днів:

Дата BETWEEN day() - 30 AND day().

Дати слід писати в американському стилі місяць/день/рік.

Для пошуку даних у текстових полях корисною є операція порівняння Like.

Приклад 3. З деякої таблиці вибрати всі записи про фірми, назви яких починаються літерою «Д» і які є в містах, назви яких починаються від літери «А» до літери «JK», погрупувавши фірми по містах, а міста впорядкувавши за алфавітом. Розв'язування:

SELECT *

FROM [Якась таблиця]

WHERE Фірма Like "Д*" AND Місто Like [А-Ж]

GROUP BY Місто

ORDER BY Місто;

Умови конструюють так само, як і в QBE, з тією відмінністю, що в логічних виразах SQL є назви полів.

Наведена вище конструкція SELECT призначена для ство­рення запитів на вибірку записів з БД.

5. Застосування команди SELECT. Розглянемо такі засто­сування команди SELECT: 1) для статистичних обчислень у ви­бірці; 2) для побудови параметричних запитів; 3) для створення нового обчислювального поля; 4) запиту на створення нової таблиці; 5) запиту на додавання записів.

1. Статистичні функції. Команду SELECT використовують для обчислення і виведення на екран значень статистичних

функцій по полях вибірки. Перше речення у конструкції повинно бути таким:

SELECT список функцій.

Розглянемо деякі статистичні функції:

Соип^поле) дає кількість записів, де це поле не порожнє (тобто є NOT Null, де Null — ознака порожнього поля);

Зит(поле) обчислює суму всіх числових значень поля;

Рігв^ПОле), Last(пoлe) — значення полів у першому і останньому записах таблиці;

МІП(ПОЛЄ), Мах(поле) — мінімальне і максимальне значення у цих полях;

А\/д(поле) — середнє арифметичне значення поля; Stdev(пoлe)₎ Stdevp(r^e) — середньоквадратичні відхи­лення у полі.

Приклад 4. Визначити середню ціну товарів, що є на складі: SELECT Ауд(Ціна) AS t FROM Складі;

Результат t потрібно переписати з екрана.

2. Запит з параметром. Щоб вивести на екран вибірку всіх товарів, ціна яких менша за середню, потрібно виконати такий параметричний запит:

SELECT * FROM Складі WHERE Ціна <z;

Якщо деяка назва у команді WHERE не збігається з назвою поля поточної бази даних, то вона розглядається як назва параметра в параметричному запиті. Відкриється вікно, куди слід ввести значення параметра (у нашому випадку це значення беремо з попереднього прикладу — t). Зверніть увагу: у команді WHERE застосовувати статистичні функції не можна.

3. Запит на створення нового обчислювальног о поля. Нас­тупне застосування команди SELECT — це створення нового обчислювального поля, наприклад Сума, так:

SELECT Ціна, Кількість, Ціна*Кількість AS Сума FROM Складі;

Якщо обчислення містить розгалуження, то використовують функцію IIF —«якщо». Загальний вигляд функції IIF такий: ІЩумова, значенняі, значення2), де значенняі надається полю, якщо умова істинна, а значення2, якщо — хибна. Значення в полі може змінюватися залежно від деякої умови. Наприклад, якщо деяке числове поле Кількість у вибірці не містить жодного

214

 

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

SELECT Ціна, Кількість, ^(Кількість IS NULL.0, Кількість) AS

НоваКількість

FROM Складі;

4. Запит на створення нової таблиці. Нову таблицю можна
створити за допомогою такого запиту:

SELECT список полів таблиці-джерела INTO нова таблиця {IN зовнішня БД} FROM таблиця-джерело {WHERE умова};

5. Запит на додавання записів. Запит на додавання записів
у таблицюі з іншої таблиці2 має такий вигляд:

INSERT INTO таблиця 1 (полеї, поле2,...)

SELECT поле1таблиці2, поле2таблиці2,...

FROM таблиця2

{WHERE умова};

Методику створення деяких інших запитів мовою SQL мож­на вивчати шляхом конструювання відповідного запиту QBE і перетворення його в запит SQL. Інформацію про нові службові слова мови SQL можна отримати з файлів довідок.

Хід роботи

1. Перетворіть п'ять запитів, створених у попередній роботі,
в SQL -запити.

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