Вивчення мови запитів, засобів сортування і групування даних.

ТЕОРЕТИЧНІ ВІДОМОСТІ

Призначення команди SELECT:для вибірки даних з однієї чи декількох таблиць, створення представлень або процедур.

Команда SELECT мови DМL призначена для вибірки даних, що задовольняють заданій умові, з одного або декількох зв'язаних об'єктів бази. Такими об'єктами є таблиці БД, представлення і збережені процедури, причому в будь-яких поєднаннях. Вибрані дані можуть бути агреговані, відсортовані, з ними можна робити обчислення. Команда може бути вкладеною. Структура команди:

SELECT [STRAIGHT_JOIN]

[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]

[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY]

[DISTINCT | DISTINCTROW | ALL]

select_expression,...

[INTO {OUTFILE | DUMPFILE} 'file_name' export_options]

[FROM table_references

[WHERE where_definition]

[GROUP BY {unsigned_integer | col_name | formula} [ASC | DESC],...]

[HAVING where_definition]

[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC],...]

[LIMIT [offset,] rows | rows OFFSET offset]

[PROCEDURE procedure_name(argument_list)]

[FOR UPDATE | LOCK IN SHARE MODE]];

Більшість речень необов’язкові, але важлива наведена послідовність.

Якщо у вас в БД стоїть порівняння «utf8_general_ci», то запит на вибірку користувачів і сортування їх по імені можна робити так:

1 SELECT *

2 FROM `users`

3 ORDER BY `name` COLLATE utf8_unicode_ci;

Одноразово змінити порівняння для певного поля, наприклад `name`, можна запитом:

1 ALTER TABLE `users`

2 CHANGE `name`

3 `name` VARCHAR(50)

4 CHARACTER SET utf8

5 COLLATE utf8_unicode_ci

6 NOT NULL;

 

3.3 ЗАВДАННЯ НА ЛАБОРАТОРНУ РОБОТУ

1. Виконати запити, що повертають всі рядки і всі стовпчики кожної із створених в лабораторній роботі №2 таблиць.

2. Виконати запити, вказані у Вашому варіанті (кожен пункт завдання реалізується одним оператором SELECT).

3. Перевірити правильність роботи створених запитів.

4. Створити запити з сортуванням та групуванням.

Варіант 1

1. Вивести алфавітний список деталей.

2. Вивести упорядкований список бригад.

3. Вивести кількість деталей одного типу.

4. Вивести деталі і їх кількість, якщо вона більше, ніж середній показник.

Варіант 2

1. Вивести алфавітний список вкладників.

2. Вивести кількість виданих грошей.

3. Вивести кількість вкладів кожного типу

4. Вивести вклади, що в 2 рази більше, ніж середній показник.

Варіант 3

1. Вивести алфавітний список моделей.

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

3. Вивести кількість матеріалів кожного типу, з яких пошито моделі.

4. Вивести список матеріалів, що потрібні більше, ніж середній показник.

Варіант 4

1. Вивести список екіпажів рейсу №1.

2. Вивести алфавітний список пунктів прибуття із зазначенням загальної суми рейсів (сума рейсів = рейс однієї компанії*кількість компаній).

3. Вивести назви всіх погодних чинників.

4. Вивести прибуття останніх рейсів, вказавши дату.

Варіант 5

1. Вивести алфавітні списки прізвищ та ініціалів (перших букв імен і по-батькові з крапкою).

2. Вивести прізвища абонентів, що мали переговори з іноземцями.

3. Вивести кількість абонентів в кожному з відділень зв’язку.

4. Вивести список прізвищ абонентів, що наговорили більше ніж на 50 гр.

5. Вивести номера відділень зв’язку та суми наданих ними послуг.

Варіант 6

1. Вивести алфавітні списки прізвищ та ініціалів (перших букв імен і по-батькові з крапкою) передплатників.

2. Вивести прізвища передплатників, що мешкають на вул. Героїв Дніпра.

3. Вивести номера відділень пошти з зазначенням кількості листонош в кожному з них.

4. Вивести прізвища передплатників, що передплатили більш ніж 5 видань.

5. Вивести номера відділень і прізвища кращих листонош.

Варіант 7

1. Вивести назви предметів і ступінь їх забезпечення в лабораторії №3.

2. Вивести перших 5 студентів за абеткою.

3. Вивести список лабораторій, що забезпечують мову С++.

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

5. Вивести назви предметів, що викладаються на протязі тижня.

Варіант 8

1. Вивести алфавітний список вантажів.

2. Вивести список вантажів та вагу вантажів, упорядкований за зменшенням ваги.

3. Вивести дані про самий важкий вантаж.

4. Вивести вантаж, що важить в 2 рази більше, ніж середній показник.

5. Вивести список замовлень, що очікують виконання більше тижня.

Варіант 9

1. Вивести алфавітний список наявних будматеріалів.

2. Вивести список будматеріалів та сумарний об’єм кожного з будматеріалів.

3. Вивести середню вагу будматеріалів кожного типу.

4. Вивести норму витрат будматеріалу на тиждень.

5. Вивести 5 останніх використаних будматеріалів.

Варіант 10

1. Вивести алфавітний (з ініціалами) список доярок.

2. Вивести список доярок з кількістю корів, що вони обслуговують.

3. Вивести середній надій Зірочки.

4. Вивести фураж, калорійність, вагу, постачальника за спаданням наявної ваги.

5. Вивести сумарний надій за тиждень.

Варіант 11

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

2. Вивести середній, максимальний та мінімальний оклад.

3. Вивести кількість робітників по посадах, які відробили без лікарняних.

4. Вивести заробітну плату, що в 2 рази більше, ніж середній показник.

5. Вивести список робітників, що хворіли більше тижня.

Варіант 12

1. Вивести список держав, упорядкований за площею, кількістю населення.

2. Вивести ВВП, демографічний стан, сумарну вагу промислових викидів.

3. Вивести кількість ресурсів кожного типу.

4. Вивести список енергоресурсів, початок їх використання, якість.

5. Вивести список держав, що знаходяться у стані економічної кризи.

Варіант 13

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

2. Вивести впорядкований за зменшенням сумарний бал за всі іспити для кожного абітурієнта, якщо не приймав участі в іспитах, то 0.

3. Вивести кількість місць за ліцензіями, за держбюджетом кожного факультету.

4. Вивести список факультетів, що мають конкурс в 1,5 рази більше, ніж середній показник.

5. Вивести список абітурієнтів, що подали заяви за два тижні до початку іспитів.

Варіант 14

1. Вивести список шахістів, що мають незавершені партії, впорядкований за зменшенням кількості незавершених партій.

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

3. Вивести кількість шахістів кожного класу.

4. Вивести розклад змагання на один день.

5. Вивести гонорари, що більше середніх.

Варіант 15

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

2. Вивести алфавітний список покупців, дату одержання картки покупця, адресу кожного, якщо покупці мешкають на вул. Хрещатик.

3. Надати відомості про вагу товару, що проданий за день.

4. Вивести список продавців, які робили в 1,2 рази більше за середній показник. Вивести також стать, коли отримав санітарну книжку, дату народження

5. Вивести список товару, строк вживання якого закінчився.

Варіант 16

1. Вивести список риб улову за місяць.

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

3. Навести дані за ресурсами вилову (ділянка на площі водосховища, коли було зариблення, кількість днів дозволеного промислу). Упорядкувати за збільшенням дати зариблення.

4. Вивести список бригад, які виловили в 1,3 рази більше, ніж середній показник.

5. Вивести список риби, що є в наявності, ціну, строк придатності.

Варіант 17

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

2. Вивести список мов програмування, якими потрібно володіти на 3.

3. Вивести упорядкований за збільшенням стажу список службовців. Потрібні також освіта, посада, сімейний стан, час, коли навчався в останнє.

4. Вивести кількість службовців, стаж роботи яких менше двох років.

5. Вивести назви мов програмування, потрібне володіння якими перевищує середній показник.

Варіант 18

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

2. Вивести кількість навчальних посібників.

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

4. Вивести список читачів, вік яких менший, ніж середній показник.

5. Вивести видання, якими можна користуватися тільки в читальному залі.

Варіант 19

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

2. Вивести три перших прізвища студентів-заочників зі спеціальності «Програмна інженерія».

3. Вивести кількість студентів денної форми навчання.

4. Вивести дисципліну, де середня оцінка вище.

5. Вивести оцінки в балах з відомої національної оцінки.

Варіант 20

1. Вивести п’ять прізвищ та ініціалів (перших букв імен і по-батькові з крапкою) авторів книги, адресу, телефон, наявність державних премій.

2. Вивести упорядкований за збільшенням року видання та накладом список назв книг видавництва Хвиля-М. Також надати кількість сторінок, ціну.

3. Для кожного видавництва вивести максимальну і мінімальну ціну його книги.

4. Вивести кількість авторів кожної книги; якщо книга не має авторів, то 0.

5. Згрупувати список книг за видавництвами.

Варіант 21

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

2. Вивести списки викладачів, які ведуть більше ніж 2 предмети.

3. Вивести список предметів, які вивчаються на 1 курсі, у першому семестрі.

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

5. Вивести предмети, в назві яких є слово «база», курс і семестр вивчення.

Варіант 22

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

2. Вивести середню складність предметів у семестрі.

3. Вивести список предметів із кількістю груп, що їх вивчає (якщо предмет не вивчається жодною з груп, то 0).

4. Вивести список предметів (без повторень), які вивчаються студентами-програмістами денної форми навчання на 3 курсі.

5. Вивести список груп, старост груп, якщо у групі менше 10 студентів.

Варіант 23

1. Вивести алфавітний список покупців.

2. Вивести упорядкований за сумою замовлення у прямому порядку, за датою замовлення у зворотному порядку список замовлень за тиждень.

3. Вивести назви та ціни акційних товарів, вказати і стару ціну.

4. Вивести кількість і суму замовлень за день.

5. Вивести назву, ціну, дату виготовлення, товару, ціна якого найбільша.

Варіант 24

1. Вивести алфавітний список прізвищ студентів, що живуть в гуртожитку №1.

2. Вивести кількість ліжок, список кімнат в гуртожитку №1, в яких є вільні місця.

3. Вивести список кімнат, в яких жив Петров.

4. Вивести список прізвищ студентів, які за рік переїжджали більше одного разу.

5. Вивести максимальну вартість кімнати у гуртожитку за рік.

Варіант 25

1. Вивести алфавітні списки прізвищ та ініціалів (перших букв імен і по-батькові з крапкою) студентів, що займались 26.06.2012 в 508 аудиторії.

2. Вивести список комп’ютерів, на яких займався Петров за останній тиждень.

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

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

5. Вивести кількість незайнятих комп’ютерів.

Варіант 26

1. Вивести алфавітні списки прізвищ і ініціалів хворих із діагнозом №1.

2. Визначити середній вік хворих, що мають діагноз №2.

3. Вивести списки назв діагнозів із зазначенням району, де наявний діагноз.

4. Для кожного хворого вивести кількість його діагнозів (якщо немає, то 0).

5. Вивести останній на даний час діагноз для кожного хворого.

Варіант 27

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

2. Визначити середній вік хворих, що є на дільниці.

3. Вивести списки кабінетів, де приймають терапевти.

4. Для кожного лікаря вивести кількість звернень за допомогою.

5. Вивести лікарів, які приймали хворих більше ніж середній час.

Варіант 28

1. Вивести алфавітні списки прізвищ та ініціалів співробітників, посади, освіта.

2. Вивести список курсів, що пропонуються, термін проведення, кількість годин з упорядкуванням за зростанням по кількості годин.

3. Вивести номери курсів і прізвища співробітників, які їх прослухали останніми.

4. Вивести список відділів, співробітників, які зараз навчаються (поле закінчення перепідготовки не заповнене).

5. Вивести список курсів, які будуть проводитися у березні.

 

3.4 МЕТОДИКА ВИКОНАННЯ

Проходимо сесію, обираємо вкладку Query. У вікні, що з’явиться, і потрібно створювати запит мовою SQL. Назви таблиць і стовпців можна писати, перетаскувати. Якщо клікнути на назві таблиці products, то справа у вкладці Query profil зокрема з’явиться Columns in products. Тиснемо на +, отримаємо назви полів, які можна перемістити для формування скрипта. Також є типи даних. Зліва можна перемістити назви таблиць.

Наприклад, створили запит

1 select name, prodid2 from products3 order by name;Для виконання запиту тиснемо правою кнопкою по запиту, потім – RUN. Або тиснемо лівою кнопкою на .

У переліку SQL Functions за абеткою наведені функції, що виконуються у запиті, наприклад, AVG(), BETWEEN, COUNT(), CURRENT_DATE.

3.5 ЗМІСТ ЗВІТУ

Розділ звіту “Результати роботи” повинен містити скрипти і результати запитів.

3.6 КОНТРОЛЬНІ ПИТАННЯ

1. Чи будуть працювати наступні оператори SELECT? Якщо ні, то що в них слід виправити?

а) SELECT SUM(SALARY), EMP_ID FROM EMPLOYEE_PAY_TBL GROUP BY 1 AND 2;

б) SELECT EMP_ID, MAX(SALARY) FROM EMPLOYEE_PAY_TBL GROUP BY SALARY, EMP_ID;

в) SELECT EMP_ID, COUNT(SALARY) FROM EMPLOYEE_PAY_TBL ORDER BY EMP_ID GROUP BY SALARY;

2. Чи вірне наступне твердження: "При використанні ключового слова HAVING необхідно використати також і ключове слово GROUP BY?"

3. Чи вірне наступне твердження: "Наступний оператор SQL повертає суми зарплат по групам?"

SELECT SUM(SALARY)

FROM EMPLOYEE_PAY_TBL;

4. Чи вірне наступне твердження: "Вибранні у запиті стовпці повинні бути присутні в списку ключового слова GROUP BY у тому ж порядку?"

5. Чи вірне наступне твердження: "Вираз ключового слова HAVING говорить GROUP BY про те, які групи слід включити у виведення?"

6. Напишіть скрипт за вказівкою викладача.

7. Синтаксис SELECT.

8.Як зберегти результати запиту в таблиці?

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

10. Функції для роботи с датами і часом

ЛАБОРАТОРНА РОБОТА 4

БАГАТОТАБЛИЧНІ ЗАПИТИ

МЕТА РОБОТИ