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

Вибірку записів створюють за допомогою конструкції SELECT, що має такий загальний вигляд:

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

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

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

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

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

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

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

Предикатом може бути службове слово ALL (показати всі незнай­дені записи), TOP n – показати перші п записів, TOP n PTRCENT – показати перші n % записів, DISTINCT – показати лише різні записи.

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

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

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

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

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

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

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

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

Розв’язок:

SELECT TOP 3 *

FROM Склад1

WHERE Ціна < 100.

5. Пошук даних у базі даних. Команда WHERE є найважливішою в конструкції SELECT. Власне вона призначена для відбору потрібних записів згідно з деяким критерієм (умови пошуку). Умова пошуку – це простий або складений логічний вираз. Простий логічний вираз – це два вирази, з’єднані oneрацією відношення: =, <, >, <=, >=. Відношення “не рівно” позначається <> або!=.

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

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

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

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

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

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

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

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

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

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

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

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

Приклад 3. Із деякої таблиці вибрати всі записи про фірми, назви яких починаються літерою “Д” і які є в містах, назви яких починаються від літери “А” до літери “Ж”, погрупувавши фірми за містами, а міста впорядкувавши за алфавітом.

Розв’язок:

SELECT; *

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

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

GROUP BY Місто;

ORDER BY Місто.

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

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

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

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

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

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

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

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

First:(поле), Last(пoлe) – значення полів у першому й останньому записах таблиці;

Міп(поле), Мах(поле) – мінімальне та максимальне значення в цих полях;

Аvg(поле) – середнє арифметичне значення поля;

Stdev(ПОЛЕ), Stdevp(ПОЛЕ) – середньоквадратичні відхилення в полі.

Приклад 4. Визначити середню ціну товарів, які є на складі:

SELECT Аvg (Ціна) AS t

FROM Склад1;

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

2. Запит із параметром.

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

SELECT *

FROM Склад1

WHERE Ціна <z.

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

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

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

FROM Склад1.

Якщо обчислення містить розгалуження, то використовують функцію IF – “якщо”. Загальний вигляд функції IF такий: ІІF(умова, значення 1, значення 2), де значення 1 надається полю, якщо умова істинна, а значення 2, якщо – хибна. Значення в полі може змінюва­тися залежно від деякої умови. Наприклад, якщо деяке числове поле Кількість у вибірці не містить жодного значення, то його можна продублювати полем Нова Кількість так, щоб на місці порожніх значень поля було число 0:

SELECT Ціна, Кількість, IIF(Кількість IS NULL,O, Кількість) AS

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

FROM Склад1.

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

SELECT список полів таблиці-джерела

INTO нова таблиця {IN зовнішня БД}

FROM таблиця-джерело {WHERE умова}.

5. Запит на додавання записів.

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

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

FROM таблиця2

{WHERE умова}.

Хід роботи

1. Відкрийте MS Access.

2. Створіть таблицю Адреси студентів засобами мови SQL. Скорис­тайтеся командою з головного меню Вигляд / Мова SQL. Таблицю створюйте з ключового слова CREATE TABLEтаблиця (поле 1 тип 1, поле 2 тип 2,...). Дайте назву таблиці Адреси студентів і назви полям Номер (cоunter), Прізвище (char), Місце проживання (char), Вулиця (char), Номер будинку / квартири (integer)/

3. Модифікуйте структуру таблиці. Додайте в кінці створеної таблиці ще два поля Номер телефону та Стан здоров’я. Скористайтеся командою INSERT INTOАдреси студентів (Телефони, [Стан здоров’я]) VALUES (значення 1, значення 2).

4.У полі Номер телефону змініть телефон двох довільно вибраних студентів.Скористайтеся командою ALTER TABLE таблиця дія COLOMN поле тип.

5. Створіть запит на вибірку по полю Місце проживання, який повинен вивести прізвища перших чотирьох студентів, які прожи­вають у м. Луцьку.

Скористайтеся командами:

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

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

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

6. Із деякої таблиці вибрати всі записи про прізвища студентів, які починаються літерою “Д” та які проживають у містах, назви котрих починаються від літери “А” до літери “Ж”. Погрупувати прізвища за місцем проживання, а місця проживання – за алфавітом.

Скористайтеся командами:

SELECT *

FROM [Адреси студентів]

WHERE Прізвище Д* AND [Місце проживання] Like [А-Ж]

GROUP BY Місце проживання;

ORDER BY Місце проживання;

7.Визначити значення полів у першому й останньому записах таблиці Адреси студентів. Скористайтеся такими командами:

для першого запису таблиці

SELECT First (поле) AS t

FROM [Адреси студентів];

для останнього:

SELECT Last(пoлe) AS t

FROM [Адреси студентів].

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

8. Вивести на екран вибірку всіх Прізвищ студентів, які прожи­вають у номерах будинків, що менші ніж десять.

SELECT *

FROM [Адреси студентів];

WHERE [Номер будинку]<10;

9. Створіть запит на створення нового обчислювального поля Сума, попередньо додавши до таблиці Адреси студентів ще два поля з оцінками студентів з математики та інформатики.Скористайтеся командами:

SELECT Математика, Інформатика Математика+Інформатика AS Сума

FROM [Адреси студентів].

10. Створіть нову довільну таблицю, зробивши запит на створення нової таблиці: