Лабораторна робота № 2
sql-запити

Мета – ознайомитися з синтаксисом SQL-запитів. Навчитися використовувати конструкцію SELECT для вибірки даних із таблиці.

Засоби. Виділений сервер MySql, клієнт для адміністрування сервера (MySQL-Front або PHPMyAdmin), навчальна база даних.

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

Запит – команда, яку дають СУБД для виведення певних даних з таблиць у пам’ять. Ці дані, звичайно, виводяться безпосередньо на екран комп’ютера або терміналу, хоча у більшості випадків, їх можна також послати принтеру, зберегти у файлі (як об’єкт у пам’яті комп’ютера), або представити як вхідну інформацію для іншої команди чи процесу.

Запити розглядають як частину мови DML (data manipulation language). Усі запити в SQL складаються з однієї команди. Структура цієї команди проста, але її можна розширювати, щоб виконати досить складні оцінки й обробку даних.

Команда SELECT

У найпростішій формі команда SELECT інструктує базу даних про те, як витягти інформацію з таблиці. Наприклад, для виведення таблиці телефонів Phone слід вказати:

SELECT Abonent, Street, House, Flat, Phone

FROM Phone

Результат виконання цієї команди буде приблизно таким:

Abonent	Street	House	Flat	Phone
ЛНТУ	Львівська	75	000	746101
Гуртожиток ЛНТУ	Даньшина	8	000	60915
Іванов А.Г.	Кравчука	10	015	61115
...	...	...	...	...

Тобто, ця команда виведе всі дані з таблиці.

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

 

SELECT	Ключове слово, яке повідомляє базі даних, що ця команда –запит. Усі запити починаються цим словом, після нього ставиться пробіл.
Abonent, Street, House, Flat, Phone	Список стовпців з таблиці, які вибирають шляхом виконання запиту. Будь-які стовпці, які не перераховані тут, не будуть включені в результат команди. Слід відмітити, що цей запит лише показує дані, і якщо якісь дані не відображені, то це не означає, що їх немає в самій таблиці.
FROM Phone	Ключове слово, яке слід використовувати в кожному запиті. Воно супроводжується пробілом і потім вказується ім’я таблиці, що є джерелом даних. В нашому випадку – таблиця телефонів (Phone).
;	Крапка з комою використовується у всіх інтерактивних командах SQL, як символ закінчення команди. У деяких системах похила риска вліво (\) у рядку є індикатором кінця команди.

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

Якщо потрібно вивести вміст всієї таблиці, то можна використовувати наступну команду:

SELECT * FROM Phone

Це призведе до того ж результату, що й попередня команда.

Фільтрування результату

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

WHERE — конструкція команди SELECT, що дозволяє встановлювати предикати, умова яких може бути або істинною, або хибною для будь-якого рядка таблиці. Команда витягує тільки ті рядки з таблиці, для яких таке твердження істинне. Наприклад, припустимо, потрібно відшукати список тільки тих фільмів з БД, які були випущені після 2003-го року:

SELECT * from film_list where year>2003

Коли задіяна конструкція WHERE, програма бази даних переглядає всю таблицю по одному рядку й досліджує кожен рядок, щоб визначити, чи істинне твердження. Отже, для запису з фільмом "9 рота" програма розгляне поточне значення стовпця year, визначить, що воно більше 2003, і включить цей рядок у виведення. Запис для "Bimmer/Бумер" не буде включений тощо.

Реляційні оператори порівняння, які розпізнає SQL:

Логічні оператори

AND	Логічне і
NOT	Логічне заперечення
OR	Логічне або

Оператор IN

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

SELECT * from film_list where year IN (2003,2004)

Цей запит еквівалентний такому:

SELECT * from film.list where (year=2003) OR (year=2004)

Оператор LIKE

LIKE застосовують тільки до текстових полів, наприклад, CHAR чи VARCHAR, з якими він використовується, щоб знаходити підстрічки. Тобто він шукає у текстовому полі, чи співпадає з умовою його вміст. Як умову оператор використовує групові символи (wildkards) — спеціальні символи, які можуть відповідати чому-небудь. Є два типи групових символів, що використовуються з LIKE:

•      символ підкреслення (_) замінює будь-який один символ. Наприклад, 'b_t' буде відповідати словам 'bat' або 'bit', але не буде відповідати 'brat'.

•      знак відсотка (%) замінює послідовність будь-якого числа символів (включаючи символи нуля). Наприклад, '%p%t' буде відповідати словам 'put', 'posit', 'opt', але не 'spite'.

Наприклад, нам потрібно вивести всіх абонентів, прізвище яких починається на "Гавр":

SELECT * from Phone where Abonent Like 'Гавр%'

Агрегатні функції

Запити можуть видавати узагальнене групове значення полів і значення одного поля. Це робиться за допомогою агрегатних функцій. Агрегатні функції видають одне значення для всієї групи таблиці. Список цих функцій:

•       COUNT — видає кількість не порожніх (значення не NULL) рядків поля, які вибрав запит.

•       SUM — видає арифметичну суму всіх вибраних значень поля.

•       AVG — видає середнє арифметичне всіх вибраних значень поля.

•       МАХ — видає найбільше зі всіх вибраних значень поля.

•       MIN — видає найменше зі всіх вибраних значень поля.

Всі агрегатні функції ігнорують порожні (NULL) значення полів. Виняток становить лише функція COUNT. При вказуванні як аргументу символа "*" (зірочка) вона видасть кількість рядків незалежно від того, які значення в них містяться.

Наприклад, потрібно визначити кількість фільмів в таблиці film_list:

SELECT COUNT(Name) FROM film_list

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

SELECT MIN(Year)

FROM film_list

where year<>0

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

SELECT (Year(‘AddDate’)-'уеаґ)

FROM film_list

where 'year'<>0

В останньому запиті використано функцію Year(), яка виділяє з дати рік. Оскільки в таблиці є поле  з аналогічним іменем, то ім'я поля взято у зворотні лапки.

Конструкція GROUP BY

Конструкція GROUP BY дозволяє визначати підмножину значень і застосовувати агрегатну функцію до цієї підмножини. Це дає можливість поєднувати поля й агрегатні функції в єдиній конструкції SELECT.

Наприклад, потрібно визначити з таблиці film_list скільки фільмів вийшло кожного року. Для цього можна, звичайно, виконати окремий запит для кожного року, вибравши COUNT (Name) з таблиці. Проте GROUP BY дозволяє помістити все в один запит:

SELECT Year,   COUNT(Name)

FROM film_list

GROUP BY Year

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

Можна використовувати конструкцію GROUP BY одразу з кількома полями. Припустимо, потрібно дізнатися для кожної країни, скільки фільмів вона випускала щороку.

SELECT Country,  Year,  COUNT(Name) FROM film_list

GROUP BY Country,  Year

Звичайно порожні групи (роки, коли певна країна не випустила жодного фільму) не будуть показані в результаті.

Конструкція HAVING

Припустимо, з таблиці фільмів потрібно вибрати тільки ті роки, в які було випущено більше 50-ти фільмів. Не можна використати WHERE, наприклад, так:

SELECT Year, COUNT(Name) FROM film_list

WHERE C0UNT(Name)>50

GROUP BY Year

Це буде суперечити строгій інтерпретації ANSI. Щоб побачити список усіх років, кількість фільмів в яких перевищує 50, слід використати конструкцію HAVING. Конструкція HAVING визначає критерії, що використовуються для видалення певних груп з виведення, так само, як конструкція WHERE робить це для окремих рядків. Правильний розв'язок:

SELECT Year,   COUNT(Name) FROM film_list

GROUP BY Year

HAVING C0UNT(Name)>50

Результат виконання запиту:

Форматування

Таблиці — це невпорядковані набори даних, і дані, які містяться в них, не обов'язково з'являються в якійсь певній послідовності. SQL використовує команду ORDER BY, щоб впорядковувати результат виконання запиту. Ця команда впорядковує виведення запиту відповідно до значень у тій або іншій кількості обраних стовпців. Кілька стовпців впорядковуються один усередині іншого. Можна визначати зростаючий (ASC) або спадний (DESC) порядок для кожного стовпця. За замовчуванням встановлено — зростаючий.

Наприклад, такий запит впорядковує таблицю фільмів за полем країна. За умови, що назви країн співпадають, рядки розміщуються у спадному порядку за роком випуску:

SELECT * FROM film_list

ORDER BY Country ASC,  Year Desc

Завдання

Написати SQL-запити, які дозволяють вивести таку інформацію з БД phones:

1. Вивести список абонентів, які проживають на вулиці Львівській. В отриманому результаті має бути два поля: абонент (abonent) і сторона (side). Вважати, що абонент проживає на правій стороні, якщо номер будинку парний і на лівій — якщо непарний. Вказівка: використати оператор CASE.

2.      Написати запит, що виводить телефони сусідів пана Федорова, який проживає по вулиці Ломоносова (приватний сектор).

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

4.      Вивести список прізвищ усіх абонентів-однофамільців, які проживають по вулиці Кравчука. Список повинен складатися з двох полів: прізвища і кількості людей, які носять це прізвище.

5.      Написати запит, який би видавав звіт, що являє собою список вулиць, де в одному будинку мешкають хоча б двоє родичів (однофамільців) даної особи (наприклад, Ковальчука). Звіт має складатися з двох полів: назва вулиці (street) та кількість будинків (house_cnt) на цій вулиці, які відповідають вказаній умові.

6.      Вивести перший десяток абонентів, на яких зареєстровано найбільше телефонних номерів.

7.      Скільки абонентів обслуговує 72-га АТС (перші дві цифри номера співпадають з номером АТС).

8.      Скільки абонентів обслуговує цифрова станція UTEL (номери починаються на 43).

9.      Вивести номери телефонів і адреси усіх аптек Луцька.

10.Вивести список телефонів ЛНТУ.

11.Вивести звіт про те, скільки абонентів обслуговує кожна АТС.

12.Вивести список квартир (поле квартира не дорівнює нулю), у яких зареєстровано більше одного номера телефону.

13.   Вивести власників симетричних номерів. (Перша цифра така ж як остання, друга — така ж, як передостання тощо.)

14.   Вивести власників "щасливих" номерів (сума перших трьох цифр дорівнює сумі останніх трьох).

15.   На якій вулиці найбільше телефонізованих будинків.

16.   Вивести звіт за густотою телефонного покриття на вулицях Луцька. Звіт повинен складатися з двох полів: назва вулиці (street) та середня кількість телефонів в одному будинку.

17. На яку букву алфавіту припадає найбільше (найменше) телефонних номерів.

18.   Вивести список вулиць Луцька, на яких є хоча б один телефон.

19.   Вивести кількість вулиць Луцька, які починаються на одну й ту ж літеру. Результат має. містити два поля: буква (letter) та кількість вулиць (street_cnt).

20. Знайти абонентів, прізвища яких закінчуються на „нко”, „всьий”

21. Вивести власників непарних номерів телефонів на вулиці Гнідавській.

22. Знайти кількість квартир з телефонами у будинку №9 на проспекті Волі.

23. Вивести список усіх людей, які проживаю в квартирі № 13 і число 13 є в номері їхнього телефону.

24. Вивести усіх абонентів, номер будинку і квартири яких присутній в номері їхнього телефону.

25. Створити в своїй БД таблицю з такою ж структурою, як таблиця Phones, і скопіювати туди всі записи з таблиці Phones, що відповідають таким умовам:

1) Прізвища абонентів закінчуються на „ко”

2) В телефонах є число 75

3) Сума цифр номера телефону ділиться на 15

27. Вивести список абонентів, в яких номер будинку і номер квартири. становлять сьогоднішню дату. Наприклад, сьогодні 26 лютого (26.02), отже, до списку повинні входити ті абоненти, номер квартири яких 26, а будинку 2.) Сьогоднішню дату запит повинен "дізнатися самостійно" і виділити з неї число та місяць, використовуючи функції роботи з датами.

28. Скільки абонентів у Луцьку мають телефонні номери, що закінчуються на дату вашого народження (не враховуючи рік).