Завдання для самостійної роботи

1. Напишіть запит на вивід номерів предметів навчання з 30 до 100 , що знаходяться в таблиці EXAM_MARKS.

2. Напишіть запит, що вибирає дані про всі предмети навчання, екзамени з яких здані студентами, що мають ідентифікатори 12 і 32.

3. Напишіть запит на вивід назв предметів навчання, що починаються на букву «Ф».

4. Напишіть запит, що вибирає відомості про студентів, у яких імена починаються на букви «А» або «В».

5. Напишіть запит для вибору з таблиці EXAM_MARKS записів, у яких відсутні значення оцінок (поле MARK).

6. Напишіть запит на вивід з таблиці EXAM_MARKS записів, що мають у поле MARK значення оцінок.

3.3. Лабораторна робота №3

Тема: Перетворення виводу й вбудовані функції

Мета роботи: набути практичні навички використання операторів перетворення даних і вбудованих функцій при роботі з реляційною базою даних.

Порядок виконання роботи

1.Ознайомитися з теоретичними положеннями до лабораторної роботи.

2. Виконати всі наведені приклади в середовище СУБД ACCESS.Для цього необхідно:

• завантажити СУБД ACCESS;

• виконати режим відкриття бази даних «ВНЗ SQL»;

• у вікні БД «ВНЗ SQL» вибрати вкладку «Запити»;

• в діалоговому вікні вказати режим «Додати таблицю» і вибрати необхідні дані;

• на панелі інструментів вибрати пункт меню «Вид» і вказати режим роботи SQL;

• в поточному діалоговому вікні ввести текст запиту в форматі SQL;

• виконати введений запит, для чого натиснути кнопку «Запуск» (!) на панелі інструментів;

• отриманий результат виконання запиту занести в звіт та перевірити на відповідність умовам прикладу;

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

3. Виконати індивідуальне завдання (дивись завдання для самостійної роботи). Номери індивідуальних завдань видаються викладачем.

4. Результати виконання індивідуальних завдань занести у звіт.

5. Захистити звіт.

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

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

Числові, символьні й строкові константи

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

Якщо в запиті замість специфікації стовпця SQL виявляє число, то воно інтерпретується як числова константа.

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

Приклад 1. Результатом виконання запиту

SELECT 'Прізвище', SURNAME, 'Місто', CITY, 100

FROM STUDENT;

є таблиця наступного виду:

Expr1000	SURNAME	Expr1002	CITY	Expr1004
Прізвище	ІВАНЕНКО	Місто	ОДЕСА	100
Прізвище	ПЕТРЕНКО	Місто	КИЇВ	100
Прізвище	СИДОРЕНКО	Місто	МИКОЛАЇВ	100
Прізвище	КОВАЛІВ	Місто	БЕРДЯНСЬК	100
Прізвище	ЗАЙЦЕВА	Місто	ЛУЦЬК	100
Прізвище	КОТІВ	Місто	БЕЛГОРОД	100
Прізвище	БУЙНОВ	Місто	ВІННИЦЯ	100
Прізвище	ПАВЛОВ	Місто	ВІННИЦЯ	100
Прізвище	ПЕТРЕНКО	Місто		100
Прізвище	ЛУКІН	Місто	ВІННИЦЯ	100

Арифметичні операції для перетворення числових даних

• Унарний (одиночний) оператор «—» (знак мінус) змінює знак числового значення, перед яким він зазначений, на протилежний.

• Бінарні оператори «+», «-», «*» і «/» надають можливість виконання арифметичних операцій додавання, віднімання, множення й ділення.

Приклад 2.

Результат запиту

SELECT SURNAME, NAME, STIPEND,

- (STIPEND*KURS)/2

FROM STUDENT

WHERE KURS = 4 AND STIPEND > 0 ;

виглядає таким чином:

SURNAME	NAME	STIPEND	KURS
Сидоренко	Вадим	150	4	-300
Петренко	Антон	200	4	-400

Операція конкатенації рядків

Операція конкатенації «&» дозволяє з'єднувати («склеювати») значення двох або більше стовпців символьного типу або символьних констант в один рядок.

Ця операція має синтаксис

<значимий символьний вираз > {&} <значимий символьний вираз >.

Приклад 3:

SELECT SURNAME & “ “ & NAME, STIPEND

FROM STUDENT

WHERE KURS = 4 AND STIPEND > 0;

Результат запиту буде виглядати таким чином:

	STIPEND
Сидоренко Вадим	150
Петренко Антон	200

Функції перетворення символів у рядку

• LOWER - перетворення у строкові символи (нижній регістр)

LOWER (<рядок>)

• UPPER - перетворення у прописні (заголовні) символи (верхній регістр)

UPPER (<рядок>)

•INITCAP — перетворення першої букви кожного слова рядка в прописну (заголовну)

INITCAP (<рядок>)

Приклад 4:

SELECT LOWER (SURNAME) , UPPER (NAME)

FROM STUDENT

WHERE KURS = 4 AND STIPEND > 0;

Результат запиту буде виглядати таким чином:

SURNAME	NAME
Сидоренко	ВАДИМ
Петренко	АНТОН

Строкові функції

• LPAD — доповнення рядка ліворуч

LPAD (<рядок>,<довжина>[,<субрядок>])

• <рядок> доповнюється ліворуч заданою в <субрядку> послідовністю символів до зазначеної <довжини > (можливо, з повторенням послідовності);

• якщо <субрядок> не зазначений, то за замовчуванням <рядок> доповнюється пробілами;

• якщо <довжина> менше довжини <рядка>, то вихідний <рядок> обмежується ліворуч до заданої <довжини>.

• RPAD — доповнення рядка праворуч

RPAD (<рядок>,<довжина>[,<субрядок>])

• <рядок> доповнюється праворуч заданою в <субрядку> послідовністю символів до зазначеної <довжини> (можливо, з повторенням послідовності);

• якщо <субрядок> не зазначений, то за замовчуванням <рядок> доповнюється пробілами;

• якщо <довжина> менше довжини <рядка>, то вихідний <рядок> обмежується праворуч до заданої <довжини >.

• LTRIM — видалення лівих граничних символів

LTRIM (<рядок>[,<субрядок>])

• з <рядка> видаляються ліворуч символи, зазначені в <субрядку>;

• якщо < субрядок > не зазначений, за замовчуванням видаляються пробіли;

• в <рядок> праворуч додається стільки пробілів, скільки символів ліворуч було вилучено, тобто довжина <рядка> залишається незмінною.

• RTRIM — видалення правих граничних символів

RTRIM (<рядок>[,<субрядок>])

• з <рядка> видаляються праворуч символи, зазначені в <субрядку>;

• якщо <субрядок> не зазначений, за замовчуванням видаляються пробіли;

• в <рядок> ліворуч додається стільки пробілів, скільки символів праворуч було вилучено, тобто довжина <рядка> залишається незмінною.

Функції LTRIM і RTRIM пропонується використовувати при написанні умовних виразів, у яких порівнюються текстові рядки. Справа в тому, що наявність початкових або кінцевих пробілів у порівнюваних операндах може спотворити результат порівняння.

Приклад 5. Константи ' ААА' і 'ААА ' не рівні один одному.

•SUBSTR — виділення субрядка

SUBSTR (<рядок>, <початок> [, <кількість>])

• з <рядка> вибирається задана <кількість> символів, починаючи із зазначеної параметром <початок> позиції в рядку;

• якщо <кількість> не задана, символи вибираються з <початку> і до кінця <рядка>;

• повертається субрядок, що містить число символів, заданих параметром <кількість>, або число символів від позиції, заданої параметром <початок> до кінця рядка;

• якщо зазначений <початок> перевищує довжину <рядка>, то повертається рядок, що складається із пробілів. Довжина цього рядка буде дорівнювати заданій <кількості> або вихідній довжині <рядка> (при не заданій <кількості>).

• INSTR — пошук субрядка

INSTR (<рядок>,<субрядок>[,<початок пошуку>

[,<номер входження>]])

• < початок пошуку> задає початкову позицію в рядку для пошуку <субрядка>. Якщо не заданий, то за замовчуванням приймається значення 1;

• <номер входження> задає порядковий номер шуканого субрядка. Якщо не заданий, то за замовчуванням приймається значення 1;

• значимі вирази в <початку пошуку> або в <номері входження > повинні мати беззнаковий цілий тип або приводитися до цього типу;

• тип значення, що повертається - INT;

функція повертає позицію знайденого субядка.

• LENGTH — визначення довжини рядка

LENGTH (<рядок>)

• довжина <рядка>, тип значення, що повертається - INT;

• функція повертає NULL, якщо <рядок> має NULL-значення.

Приклад 6. Запити, що використовують строкові функції.

SELECT LPAD (SURNAME, 10, ‘@’, RPAD (NAME, 10, '$')

FROM STUDENT

WHERE KURS = 3 AND STIPEND > 0 ;

Результат запиту буде виглядати таким чином:

@@Петренко	Петро$$$$$
@@@@Павлов	Андрій$$$$
@@@@@Лукін	Артем$$$$$

Приклад 7. Запити, що використовують строкові функції.

SELECT SUBSTR(NAME, 1, 1) ||'.'|| SURNAME, CITY, LENGTH (CITY,)

FROM STUDENT

WHERE KURS IN(2, 3, 4) AND STIPEND > 0;

Запит видає результат:

	CITY
П. Петренко	Київ	4
С. Сидоренко	Миколаїв	8
О. Зайцева	Луцьк	5
А. Лукін	Вінниця	7
А. Петренко	NULL	NULL