Лабораторна робота № 5

Тема роботи: Створення запитів мовою SQL.

Мета роботи: вивчити деякі команди мови SQL та навчитися за їх допомогою створювати запити в Access.

5.1 Інструкція SELECT мови SQL

При створенні запитів в режимі конструктора автоматично ство­рюється інструкція на мові SQL. Її можна переглянути за допомогою меню і внести зміни. Оптимізатор запитів (Query Optimizer) обробляє запит після запуску і будує найефективніший план його виконання. Оптимізатор може навіть змінити структуру запиту.

Інструкція Select має такі розділи:

SELECT перелік полів або виразів

INTO ім’я нової таблиці

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

WHERE умови відбору записів

GROUP BY поля або вирази для угруповання даних

HAVING умови відбору записів після угруповання

UNION

ORDER BY перелік полів для сортування

Обов’язковими є тільки розділи SELECT та FROM. Інші розділи краще використовувати в тому порядку, в якому вони тут записані.

5.1.1 Розділ SELECT

Він визначає потрібні для запиту поля та вирази. Полю можна надати нове ім’я – псевдонім (alias). Можна створювати обчислю­вальні поля. Синтаксис розділу:

SELECT [ ALL | DISTINCT | DISTINCTROW] < список полів >;

1 ALL – використовується за замовчанням і дозволяє включати до запиту однакові рядки;

2 DISTINCT – аналізується результат вибірки, і з однакових значень виводиться лише одне, тобто всі рядки вибірки різні.

3 DISTINCTROW – кожен рядок вибірки буде створений з уні­кальної комбінації записів базовихтаблиць, але в залежності від того, які поля вибрані, може статися так, що вибірка матиме однакові ряд­ки. Параметр DISTINCTROW впливає на результат вибірки тільки тоді, коли використовувалась операція JOIN та до вибірки були вклю­чені не всі стовпці таблиць.

Задача 5.1. Створити запит на вибірку даних, до якого увійдуть усі відомості про робітника з таблиці tvidom.

SELECT * FROM tvidom;

Такий вигляд матиме інструкція SQL. Результати роботи запиту матимуть майже такий самий вигляд, як таблиця 1.6, тільки вибірка даних буде упорядкована за табельним номером. Access за замовчан­ням упорядковує дані за ключовим полем, якщо не вказаний якийсь інший варіант. Символ * використовується, якщо вибираються всі поля таблиці. Ім’я таблиці tvidom вказується у розділі FROM.

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

SELECT DISTINCT tzarobitok.tn

FROM tzarobitok;

Така інструкція створює вибірку табельних номерів з табли­ці tzarobitok. Але замість них виводяться прізвища, тому що в конструк­торі таблиці tzarobitok була використана підстанов­ка. Кожне прізвище виводиться лише один раз завдяки використанню ключового слова DISTINCT. Якщо його НЕ писати, то кожне прізвище буде виведено стільки разів, скільки воно зустрічається в таблиці.

5.1.2 Розділ FROM

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

Якщо до запиту входить декілька таблиць, то треба вказувати типи їх з'єднання. Синтаксис:

Головна таблиця JOIN TYPE дочірня таблиця ON умови зв’язку

таблиць

ON умови зв’язку таблиць вказує які поля таблиць треба порів­нювати і яким чином. Найчастіше використовується оператор =. Інші оператори порівняння <, >, <=, >=, <> можна використовувати тільки в інструкції SQL, в режимі конструктора запитів це неможливо.

JOIN TYPE – тип з'єднання таблиць. Можливі такі типи:

1 INNER JOIN – встановлюється за замовчанням. До запиту включаються лише ті записи з обох таблиць, в яких співпадають зна­чення відповідних полів.

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

SELECT tvidom.tn, tvidom.fio, tzarobitok.dat, tzarobitok.zar

FROM tvidom INNER JOIN tzarobitok

ON tvidom.tn = tzarobitok.tn

ORDER BY tzarobitok.dat;

З обох таблиць відбираються тільки ті записи, у яких співпада­ють значення табельних номерів. Розділ ORDER BY забезпечує сорту­вання вибірки за датою. Інструкція відповідає запиту для задачі 2.1.

2 LEFT [OUTER] JOIN – до запиту включаються всі записи головної (лівої) таблиці, незалежно від того чи існують для них відпо­відні записи в дочірній (правій) таблиці. Якщо в правій таблиці немає даних для вибірки, в стовпці запиту їхнє місце вільне. Якщо в попередньому прикладі змінити тип зв’язку, то виведуться й прізвища тих робітників, які ще не працювали.

Задача 5.4. Знайти відомості про робітників, які ще не працю­вали, тобто їхні заробітки відсутні в таблиці tzarobitok.

SELECT tvidom.pidrozdil, tvidom.fio

FROM tvidom LEFT JOIN tzarobitok ON tvidom.tn = tzarobitok.tn

WHERE (((tzarobitok.tn) Is Null));

Ця інструкція відповідає запиту для задачі 2.6. З таблиці tvidom спочатку вибираються всі записи, а потім розділ WHERE уточнює умову відбору.Умова Is Null залишає у виборці тільки ті записи з таб­лиці tvidom, які не мають зв’язаних з ними записів у таблиці tzarobitok.

3 RIGHT [OUTER] JOIN – до запиту включаються всі записи правої таблиці, незалежно від того чи існують для них відповідні за­писи в лівій таблиці.

4 FULL [OUTER] JOIN – в MS Access відсутній. В SQL Server до запиту ввійдуть усі записи з двох таблиць: ті, що задовольняють умові з’єднання, і ті, що не задовольняють.

5.1.3 Розділ WHERE

Тут вказується логічна умова, від виконання якої залежить чи ввійде запис до вибірки. Можна вказати декілька умов, об’єднаних логічними операторами AND, OR або іншими. WHERE в невеликих запитах може замінити конструкцію INNER JOIN, але це суттєво зменшує швидкість виконання запиту.

Задача 5.5. Вивести перелік заробітків, які знаходяться в межах від 30 грн. до 50 грн. (див. задачу 2.2)

SELECT tvidom.tn, tvidom.fio, tzarobitok.dat, tzarobitok.zar

FROM tvidom INNER JOIN tzarobitok

ON tvidom.tn = tzarobitok.tn

WHERE (((tzarobitok.zar)>=30 And (tzarobitok.zar)<=50));

Задача 5.6. Створити запит про заробітки, які були нараховані в певному місяці. Номер місяця вводити як параметр.

PARAMETERS [Введіть номер місяця] Short;

SELECT tvidom.tn, tvidom.fio, tzarobitok.dat, tzarobitok.zar

FROM tvidom INNER JOIN tzarobitok

ON tvidom.tn = tzarobitok.tn

WHERE (((Month([tzarobitok]![dat]))=[Введіть номер місяця]))

ORDER BY tzarobitok.dat;

Тип параметру вказують після службового слова PARAMETERS.

5.1.4 Розділ GROUP BY

Він використовується для підсумкових запитів В списку полів розділу SELECT без змін дозволяється використовувати імена тільки тих полів, які входять до розділу GROUP BY. Усі інші поля повинні бути використані як аргументи підсумкових функцій AVG(), MAX(), MIN(), SUM(), COUNT() або інших.

Задача 5.7. Створити запит про заробітки всіх робітників. Ця інструкція відповідає запиту для задачі 3.1.

SELECT tvidom.tn, tvidom.fio, Sum(tzarobitok.zar) AS [Усього],

Avg(tzarobitok.zar) AS [Середнє за день]

FROM tvidom LEFT JOIN tzarobitok ON tvidom.tn = tzarobitok.tn

GROUP BY tvidom.tn, tvidom.fio;

5.1.5 Розділ HAVING

Він дуже схожий на розділ WHERE. В них однакові правила за­пису логічних умов. Але умови, задані в розділі WHERE, перевіряють­ся для окремих записів перед угрупованням, а умови розділу HAVING перевіряються для груп записів після угруповання стовпців, вказаних в розділі GROUP BY і звичайно містять підсумкові функції.

Задача 5.8. Створити запит про заробітки тих робітників, які заробили більше 200 грн. (див. задачу 3.2)

SELECT tvidom.tn, tvidom.fio, Sum(tzarobitok.zar) AS [Усього]

FROM tvidom INNER JOIN tzarobitok

ON tvidom.tn = tzarobitok.tn

GROUP BY tvidom.tn, tvidom.fio

HAVING (((Sum(tzarobitok.zar))>200))

ORDER BY tvidom.fio;

Задача 5.9. Для кожного робітника підрахувати кількість днів, коли їхні заробітки становили більше 40 грн. (див. задачу 3.3)

SELECT tvidom.fio, Count(tzarobitok.zar) as [Кількість днів]

FROM tvidom INNER JOIN tzarobitok

ON tvidom.tn = tzarobitok.tn

WHERE (((tzarobitok.zar)>40))

GROUP BY tvidom.fio

ORDER BY tvidom.fio;

5.1.6 Розділ ORDER BY

В цьому розділі вказуються імена полів або їх номери в списку полів даного запиту (нумерація починається з 1). За замовчанням вста­новлений порядок сортування за зростанням (ASC). Для сортування за зменшенням використовується службове слово DESC.