Урок 15                                                                        Інформатика 11(ІКТ)

Основи мови SQL.

Мета.

Навчальна. Ознайомити з основами мови SQL, структура SQL-запиту.

Розвиваюча. Розвивати логічне та алгоритмічне мислення.

Виховна. Виховувати культуру мовлення.

Тип уроку. Урок засвоєння нових знань.

Учні повинні знати:

Учні повинні уміти:

Матеріали для роботи з учнями:

• Основи баз даних: Навч. посіб. / І.О.Завадський. - К.: Видавець І.О. Завадський, 2013.
• Мультимедійне обладнання;
• Презентація “Поглиблене семантичне моделювання”.
• База даних (Вікіпедія).

План

1. Організація початку уроку
2. Актуалізація опорних знань.
3. Мотивація навчальної діяльності.
4. Вивчення нового матеріалу.
5. Запитання до уроку.
6. Домашнє завдання.

Хід уроку

1. Організація початку уроку.

3. Актуалізація опорних знань.

1. Що таке мова маніпулювання даними?
2. Яка мова маніпулювання даними най­більш поширена в реляційній моделі?
3. Який вигляд має вікно конструктора запитів?
4. Що таке віртуальна таблиця?

Тестування “Основи баз даних”

4. Мотивація навчальної діяльності.

Як ми вже зазначали, практично в усіх реляційних СКБД для маніпулювання даними застосовують мову SQL (англ. Structu­red Query Language — мова структурованих запитів), а такі автоматизовані засоби створення запитів, як ми розглядали в попередньому розділі, представлені лише в поодиноких систе­мах керування базами даних. Тобто основний спосіб конструю­вання запитів до баз даних — це їх запис у текстовому вигляді, чимось подібний до написання невеличких програм. Однак мова SQL не є мовою програмування, вона декларативна, тобто дозволяє користувачу описати, що він хоче отримати, не опи­суючи, як саме комп’ютер має обчислити потрібний результат. Тому писати запити мовою SQL значно легше, ніж програми будь-якою мовою програмування. Загалом є кілька різновидів запитів: на вибирання даних, їх додавання, видалення, онов­лення та деякі інші. Сьогодні навчимося описувати мовою SQL нескладні запити на вибирання даних.

5. Вивчення нового матеріалу.

Загальна структура SQL-запиту

Ви вже вмієте створювати запити на вибирання даних, або вибіркові запити за допомогою конструктора запитів MS Access.

Відмінною рисою цього типу запитів є те, що вони не змінюють дані в базі, а лише вибирають їх з таблиць за певними умова­ми. Результатом виконання будь-якого вибіркового запиту, нагадаємо, є віртуальна таблиця, що існує нетривалий час, поки ви її не закриєте.

Зрозуміти структуру вибіркового SQL-запиту найлегше на кон­кретному прикладі, розглянутому у вправі 9.1.

Вправа 9.1

Запишіть мовою SQL і виконайте запит визначити прізвища та імена учнів хлопців.

1. Відкрийте конструктор запитів. У вікні Добавление табли­цы виберіть таблицю Учні, оскільки запит стосується уч­нів, після чого клацніть кнопку Добавить і закрийте це вікно.
2. У Access 2003 клацніть правою кнопкою миші будь-яке місце вікна конструктора, крім бланка запиту, й виберіть з контекстного меню команду Режим SQL, а в Access 2007/2010 з меню кнопки Режим на стрічці Конструктор ви­беріть команду Режим SQL. Буде відображено заготовку SQL-коду запиту:

SELECT

FROM Учні;

У цій заготовці ви бачите два ключових слова: SELECT і FROM. Після слова FROM вказують назви таблиць, з яких вибиратимуться дані. У нашому випадку це буде таблиця Учні; її назву вже введено автоматично.

3. Після слова SELECT через кому запишіть назви полів, зна­чення яких відображатимуться. У даному запиті це поля прізвище та ім'я. Оскільки назва поля ім'я містить сим­вол апострофа, її потрібно взяти у квадратні дужки:

SELECT прізвище, [ім'я]

FROM Учні;

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

4. В кінці запиту (але до символу ;) запишіть ключове слово WHERE, а після нього — умову відбору записів (серед усіх учнів нам потрібно відібрати хлопців). Остаточний вигляд запиту буде таким:

SELECT прізвище, [ім'я]

FROM Учні

WHERE стать="ч";

5. Запустіть запит на виконання кнопкою ! (Виконати) та переконайтеся, що в таблиці результатів є відомості тільки про хлопців.
6. Закрийте вікно запиту, зберігши його під іменем учні-хлопці.

Загалом вибірковий SQL-запит має такий формат:

SELECT список полів, значення яких потрібно отримати

FROM список таблиць, з яких вибираються дані

WHERE умова, яку мають задовольняти записи, що вибираються

Зазначимо, що вирази, які починаються з ключових слів, часто називають фразами, наприклад фраза SELECT. Регістр ключо­вих слів неважливий, але їх прийнято записувати великими літерами.

Зауважимо також, що мова SQL англізована, тобто її вирази дещо нагадують англійські речення. Слово SELECT переклада­ється як «вибрати», слово FROM — «з», a WHERE — «де», або «для яких». У цілому простий SQL-запит потрібно читати за такою схемою.

Запит SELECT х FROM у WHERE z слід інтерпретувати так: «вибрати поля х тих записів таблиці у, які задовольняють умову z».

Для допитливих.

Якщо запит із вправи 9.1 скласти в конс­трукторі, а потім перейти в режим SQL, то ми побачимо та­кий текст, автоматично сконструйований СКБД:

SELECT Учні.прізвище, Учні.[ім'я]

FROM Учні                                                                                                             І

WHERE (((Учні.стать)="ч"));

За змістом він нічим не відрізняється від тексту, записано­го нами в SQL-редакторі, але виглядає складнішим через те, що під час автоматичної побудови SQL-виразів MS Ac­cess вводить деякі зайві позначення. Так, після слова WHERE поставлено непотрібні дужки, а перед назвами атри­бутів записано назву таблиці з символом крапки. Вираз Учні. прізвище можна читати як «поле прізвище таблиці Учні». Проте якщо у запиті всього одна таблиця, то поле прізвище ніякій іншій таблиці не може належати і тому специфікатор Учні . можна опустити.

Завдання 9.2

Запишіть мовою SQL запит визначити прізвища та номери паспортів учителів-математиків. Як перейти в режим уве­дення SQL-коду, описано у вправі 9.1.

З’єднання таблиць

Розглянемо запит визначити прізвища та імена вчителів, що викладають в НА класіу який ми вже реалізовували за допо­могою конструктора запитів (див. рис. 8.7). Якщо створити цей запит у конструкторі, а потім відобразити його SQL-код та ви­далити зайві дужки і специфікатори, результат буде таким:

SELECT прізвище,[ім'я]

FROM Учителі INNER JOIN Викладання ON

Учителі.паспорт = Викладання.учитель

WHERE клас="11А"

У фразі FROM ми бачимо не назву таблиці і не список назв, а оператор INNER JOIN (у перекладі з англійської — внутрішнє з’єднання), призначений для з’єднання таблиць. Загальний синтаксис цього оператора такий:

Таблиця1 INNER JOIN Таблиця2 ON умова

Умова зазвичай стосується обох таблиць, вказаних до і після слів INNER JOIN (у прикладі вище — таблиць Учителі та Ви­кладання). Оператор «зчіплює» ті записи таблиці 1 і таблиці 2, які, разом узяті, відповідають умові. У результаті з таких зчі­пок формується нова таблиця, до якої вже застосовується від­бір рядків згідно з фразою WHERE, а потім — відбір стовпців згідно з фразою SELECT.

Опишемо детальніше алгоритм, за яким СКБД виконує запит.

1. Виконується оператор INNER JOIN у фразі FROM. А саме, зчіплюються всі такі пари записів з таблиць Учителі та Викладання, для яких виконується рівність Учителі. паспорт = Викладання.учитель, тобто значення поля паспорт у таблиці Учителі збігається зі значенням поля учитель у таблиці Викладання (рис. 9.1, а). У результа­ті отримуємо таблицю, що містить усі такі зчіпки (рис. 9.1, б).

Оскільки у зв’язку між таблицями Учителі та Викладання таблиця Учителі є головною, а Викладання — підлеглою, то оператор Учителі INNER JOIN Викладання можна ін­терпретувати так: «до кожного вчителя дописати інформа­цію про його викладання».

2. З таблиці, зображеної на рис. 9.1, б відбираються записи за умовою клас="11А", тобто ті записи, які в полі клас містять значення НА. У результаті отримуємо таблицю, зображену на рис. 9.2.
3. У таблиці, зображеній на рис. 9.2, залишаються тільки два поля, вказані після слова SELECT: прізвище та ім'я. Отримуємо таблицю, зображену на рис. 9.3, — це і є оста­точний результат виконання запиту.

У загальному випадку SQL-запит виконується так:

1. Виконується операція з’єднання таблиць із фрази FROM.
2. До отриманої таблиці застосовується операція вибірки: від­бираються ті записи, що задовольняють умову, вказану у фразі WHERE.
3. Таблиця, отримана на попередньому кроці, проектується на поля, вказані у фразі SELECT, тобто ці поля залишають­ся, а всі інші відкидаються.

Більшість вибіркових запитів до реляційних баз даних (але не всі) виконуються саме за цією схемою: з’єднання таблиць — вибірка записів — проекція на поля.

Для допитливих.

З’єднання, вибірка і проекція — три з во­сьми операцій реляційноі алгебри Кодда. Як уже згадувалося в розділі 1, реляційна алгебра — це мова маніпулювання даними, запропонована автором реляційної моделі Е. Код- дом. Багато її рис увібрала в себе мова SQL.

Завдання 9.2

Запишіть оператори INNER JOIN, які дозволять отримати в базі даних школа такі з’єднання таблиць, як показано на рис. 9.4, а-б

Завдання 9.3

Реалізуйте мовою SQL запит визначити, у якому класі клас ним керівником є Сошко Катерина Миколаївна.

Для допитливих.

З’єднувати таблиці можна і без оператора INNER JOIN. Для цього слід перелічити їхні назви через кому у фразі FROM, а у фразі WHERE записати умову з’єднан­ня записів, долучивши її до інших умов сполучником AND. Наприклад, запит визначити прізвища та імена вчителів, що викладають в 11А класі можна реалізувати так:

SELECT прізвище,[ім'я]

FROM Учителі, Викладання

WHERE клас="11А" AND Учителі.паспорт =

Викладання.учитель

Підзапити

У попередньому розділі ми згадували про властивість реляцій- ної замкненості мови SQL. Вона полягає в тому, що результа­том будь-якого запиту є таблиця, а отже, одні запити можна підставляти в інші замість таблиць. Загалом є два способи під­становки одного запиту в інший:

• замість імені таблиці в запиті вказують ім’я іншого запиту;
• в одному запиті записують повний текст іншого запиту, взятий у круглі дужки.

Підстановку можна здійснювати у фразах FROM та WHERE. Особ­ливо важливий і цікавий той випадок, коли у фразі WHERE од­ного запиту записують повний текст іншого запиту. Розгляне­мо цей випадок детально, реалізувавши запит визначити пріз­вища та імена вчителів, що викладають в 11А класі, з вико­ристанням підзапиту. SQL-текст запиту буде таким:

SELECT прізвище,[ім'я]

FROM Учителі

WHERE паспорт IN (SELECT учитель FROM Викладання WHERE клас="11А")

У фразі WHERE цього запиту вжито оператор IN (англ. «в», «належить»). Його загальний формат такий:

значення IN (підзапит)

Результатом підзапиту має бути таблиця з одним стовпцем. Як­що вказане перед словом IN значення в цьому стовпці є, то весь вираз значення IN (підзапит) вважається істинним, інак­ше — хибним. Щоб краще зрозуміти принцип дії оператора IN, розглянемо крок за кроком виконання запиту визначити пріз­вища та імена вчителів, що викладають в 11А класі з точки зору СКБД.

1. Виконуємо фразу FROM. Оскільки в ній вказано лише одну таблицю Учителі, то на першому кроці ми просто «бере­мо» цю таблицю для подальших операцій.
2. Перебираємо всі записи таблиці Учителі, перевіряючи для кожного, чи виконується умова, вказана після слова WHERE. Ця перевірка здійснюється в два етапи.

2а. Виконуємо підзапит. Його результат — це набір номе­рів паспортів учителів НА класу (згадайте структуру таблиці Викладання).

2б. Перевіряємо, чи належить значення поля паспорт з поточного запису таблиці Учителі набору значень, отриманому на кроці 2а. Якщо належить, то запис у таблиці Учителі залишаємо, інакше — відкидаємо.

3. Проектуємо отриману на кроці 2 таблицю за полями прі­звище та ім'я, тобто ці поля залишаємо, а всі інші — від­кидаємо.

Таким чином, оператор IN дає змогу перевірити, чи належить значення множині результатів підзапиту. Зазначимо, що у фразу WHERE підзапит можна вставляти лише за допомогою логічного оператора, формуючи вираз, значення якого істинне або хибне. Однак це не обов’язково має бути оператор IN; є ще оператори EXISTS (англ. «існує»), ANY (англ. «будь-який»), ALL (усі). Так, значення виразу EXISTS (підзапит) буде істинним, якщо результат підзапиту містить хоча б один запис, і хибним, якщо результат підзапиту порожній.

Завдання 9.4

Використовуючи підзапит в операторі IN, реалізуйте мовою SQL запит визначити класи, у яких викладають вчителі на ім’я Петро.

Віднімання множин записів

Можливості мови SQL значно ширші за можливості конструк­тора запитів MS Access і тому далеко не кожен SQL-запит може бути створений у вікні конструктора. Зараз ми розглянемо найпростіший різновид запитів, які не можуть бути створені за допомогою конструктора. Це запити з запереченням, наприк­лад визначити прізвища та імена вчителів, які не виклада­ють в 11А класі. Цей запит відрізняється від того, який ми розглядали раніше, наявністю частки «не». Якби її не було, ми би просто з’єднали таблицю вчителів із таблицею викладання, відібрали ті записи, які відповідають 10А класу, та спроекту­вали результат на поля прізвище та ім'я. Але в запиті із час­ткою «не» нам потрібні не ці, а якраз всі інші вчителі. Тобто від множини всіх учителів нам потрібно відняти тих, які ви­кладають у 10А класі. Найлегше це зробити за допомогою опе­ратора NOT IN (англ. «не належить»):

SELECT прізвище,[ім'я]

FROM Учителі

WHERE паспорт NOT IN (SELECT учитель FROM Викладання WHERE клас=”11A")

Буквально запит читається так: «вибрати прізвища та імена тих учителів, які не належать множині вчителів, що виклада­ють в 11А класі».

Завдання 9.5

Реалізуйте мовою SQL запит визначити назви класів, у яких не вчиться жодної дівчини.

6. Висновки.

• Вибірковий запит у мові SQL має такий формат:

SELECT список полів, значення яких потрібно отримати

FROM список таблиць, з яких вибираються дані

WHERE умова, яку мають задовольняти записи, що вибираються

• Запит SELECT х FROM у WHERE z слід інтерпретувати так: «вибрати поля х тих записів таблиці у, які задовольняють умову 2».
• Оператор a INNER JOIN b ON с використовується у фразі FROM і виконує з’єднання таблиць. Він «зчіплює» ті записи таблиць а і b, які, разом узяті, відповідають умові с.
• Оператор IN дає змогу сконструювати у фразі WHERE умову з підзапитом. Вираз значення IN (підзапит) вважається істинним, якщо значення належить множині значень, отриманій у результаті виконання підзапиту, та хибним в іншому випадку.
• Запити, у яких перед дієсловом розташовано частку «не», реалізують за допомогою оператора NOT IN у фразі WHERE. Вираз значення NOT IN (підзапит) вважається істин­ним, якщо значення не належить множині значень, отри­маній у результаті виконання підзапиту, та хибним в ін­шому випадку.

7. Завдання для самостійного виконання

Реалізуйте мовою SQL такі запити.

1. Відобразити всю інформацію про учнів, яких навчають у чител і-чо ловіки.
2. Для кожного вчителя відобразити всю інформацію про уч­нів, яких він навчає.
3. Відобразити прізвища та імена всіх учнів, крім тих, які вчаться в 11 класах.
4. Визначити прізвища та імена учнів, яких не навчає Ва­силь Петрович Корбут.
5. Визначити прізвища вчителів, які викладають не тільки в 11 класах (зауважте, що вони можуть не викладати в 11 класах взагалі).
6. Для кожного вчителя відобразити всю інформацію про уч­нів, яких він не навчає.
7. Визначити прізвища вчителів, які викладають принаймні в одному тому ж класі, що і Сошко Катерина Миколаївна.
8. Визначити пари прізвищ учителів, які викладають тільки в різних класах (тобто вчитель 1 не викладає в жодному з тих класій, де викладає вчитель 2, і навпаки).

8. Питання для роздумів

1. Нижче наведено формулювання та реалізації чотирьох за­питів. Визначте, яка реалізація якому запиту відповідає.

Формулювання

1. Визначити прізвища вчителів, які викладають при­наймні в одному 11 класі.
2. Визначити прізвища вчителів, які викладають в усіх 11 класах.
3. Визначити прізвища вчителів, які викладають не в усіх 11 класах.
4. Визначити прізвища вчителів, які не викладають принаймні в одному 11 класі.

Реалізації

SELECT прізвище                                                                                          --А

FROM Учителі

WHERE NOT EXISTS (SELECT * FROM Викладання

WHERE клас Like "*11" AND клас NOT IN (SELECT клас FROM Викладання WHERE учитель=Учителі.паспорт))

SELECT прізвище                                                                                         --Б

FROM Учителі

WHERE EXISTS (SELECT * FROM Викладання

WHERE клас Like "*11" AND клас NOT IN (SELECT клас

FROM Викладання WHERE учитель=Учителі.паспорт))

SELECT прізвище                                                                                         - -В

FROM Учителі

WHERE EXISTS (SELECT * FROM Викладання

WHERE клас Like "*11" AND клас IN (SELECT клас

FROM Викладання WHERE учитель=

Учителі.паспорт))

SELECT прізвище                                                                                              --Г

FROM Учителі

WHERE NOT EXISTS (SELECT * FROM Викладання

WHERE клас Like "*11" AND клас IN (SELECT клас

FROM Викладання WHERE учитель=Учителі.паспорт))

2. Припустимо, що предметною областю бази даних є механіч­ні деталі. База містить лише одну таблицю, у якій є два поля: № деталі та № складової. У цій таблиці зберіга­ються відомості про те, які деталі з яких складаються. На­приклад, з поданої таблиці видно, що деталь 1 складається з деталей 2 і 3, а деталь 3 — з деталей 4 і 5.

Сформулюйте для цієї бази даних запити, які: 

а) неможливо реалізувати за допомогою конструктора за­питів;

б*) неможливо реалізувати засобами мови SQL.

9. Завдання для досліджень.

1. Реалізуйте завдання 9.3 та завдання для самостійного ви­конання 1—2 мовою SQL, але без оператора INNER JOIN.
2. З’ясуйте, який синтаксис має та як використовується опе­ратор EXISTS. Реалізуйте за допомогою операторів EXISTS і NOT IN запит визначити назви класів, у яких принаймні один вчитель не викладає.
3. Реалізуйте за допомогою операторів NOT EXISTS і NOT IN запит визначити назви класів, у яких викладають усі вчителі.

10. Домашнє завдання.

1. Опрацювати теоретичний матеріал.
2. Виконати завдання для самостійного виконання 3, 6, 8.