Лабораторна робота №3.
Тема. Оптимізація запитів.
Мета. Навчитися використовувати оптимізатор запитів.
Теоретичні матеріали. Оптимізація запитів, індекси, план виконання запитів.
Завдання для лабораторної роботи
Написати скрипт, який виконує завдання за варіантом.
Проаналізувати скрипт у оптимізторі запитів.
Провести оптимізацію та верифікацію скрипта у відповідній базі даних.
Варіанти завдання до лабораторної роботи №3.
Варіант 1. Знайдіть всі записи таблиці Printer для кольорових принтерів.
Варіант 2. Знайдіть номер моделі, швидкість і розмір жорсткого диска ПК, що мають 12x або 24x CD і ціну менше 600 дол.
Варіант 3. Вкажіть виробника та швидкість для тих ПК-блокнотів, які мають жорсткий диск об'ємом не менше 10 Гбайт.
Варіант 4. Знайдіть середню швидкість ПК.
Варіант 5. Знайдіть середню швидкість ПК-блокнотів, ціна яких перевищує 1000 дол.
Варіант 6. Знайдіть середню швидкість ПК, випущених виробником A.
Варіант 7. Для кожного значення швидкості знайдіть середню вартість ПК з такою ж швидкістю процесора. Вивести: швидкість, середня ціна.
Варіант 8*. Знайдіть розміри жорстких дисків, що збігаються у двох і більше PC. Вивести: HD
Варіант 9*. Знайдіть пари моделей PC, що мають однакові швидкість і RAM. У результаті кожна пара вказується тільки один раз, тобто (i, j), але не (j, i), Порядок виводу: модель з великим номером, модель з меншим номером, швидкість і RAM.
Варіант 10. Для класів кораблів, калібр гармат яких не менше 16 дюймів, вкажіть клас і країну.
Варіант 11. Вкажіть кораблі, потоплені в боях у Північній Атлантиці (North Atlantic). Висновок: ship.
Варіант 12. За Вашингтонського міжнародного договору від початку 1922 заборонялося будувати лінійні кораблі водотоннажністю понад 35 тис. тонн. Вкажіть кораблі, які порушили цей договір (враховувати тільки кораблі c відомим роком спуску на воду). Вивести назви кораблів.
Варіант 13*. Знайдіть країни, що мали будь-коли класи звичайних бойових кораблів ( 'bb') і мали коли-небудь класи крейсерів ( 'bc').
Варіант 14. Знайдіть клас, ім'я та країну для кораблів з таблиці Ships, що мають не менше 10 гармат.
Варіант 15. Для ПК з максимальним кодом з таблиці PC вивести всі його характеристики (крім коду) у дві колонки:
? Назва характеристики (ім'я відповідного стовпця в таблиці PC);
? Значення характеристики
Варіант 16. Знайдіть назви кораблів, потоплених у боях, і назва бою, в якому вони були потоплені.
Варіант 17. Знайдіть назви всіх кораблів в базі даних, що починаються з літери R.
Варіант 18*. Знайдіть назви кораблів з гарматами калібру 16 дюймів (врахувати кораблі з таблиці Outcomes).
Варіант 19. Знайдіть битви, в яких брали участь кораблі класу Kongo.
Варіант 20. Визначте середнє число знарядь для класів лінійних кораблів. Отримати результат з точністю до 2-х десяткових знаків.
Варіант 21*. З точністю до 2-х десяткових знаків визначте середнє число знарядь всіх лінійних кораблів (врахувати кораблі з таблиці Outcomes).
Варіант 22. Для кожного класу визначте рік, коли було спущено на воду перший корабель цього класу. Якщо рік спуску на воду головного корабля невідомий, визначте мінімальний рік спуску на воду кораблів цього класу. Вивести: клас, рік.
Варіант 23*. Порахувати залишок грошових коштів на кожному пункті прийому для бази даних зі звітністю не частіше одного разу на день. Висновок: пункт, залишок.
Варіант 24*. Порахувати залишок грошових коштів на початок дня 15/04/01 на кожному пункті прийому для бази даних зі звітністю не частіше одного разу на день. Висновок: пункт, залишок.
Варіант 25. Порахувати залишок грошових коштів на всіх пунктах прийому для бази даних зі звітністю не частіше одного разу на день.
Варіант 26. Порахувати залишок грошових коштів на всіх пунктах прийому на початок дня 15/04/01 для бази даних зі звітністю не частіше одного разу на день.
Варіант 27. Обрати всі білі квадрати, що забарвлювалися тільки з балончиків, порожніх до теперішнього часу. Вивести ім'я квадрата
Додатки.
А. Схеми баз даних
Комп’ютерна фірма
Схема БД складається з чотирьох таблиць:
Product (maker, model, type)PC (code, model, speed, ram, hd, cd, price)Laptop (code, model, speed, ram, hd, screen, price)Printer (code, model, color, type, price)
Таблиця Product представляє виробника (maker), номер моделі (model) і тип ('PC' – ПК, 'Laptop' – ПК-блокнот або 'Printer' – принтер). Припускається, що номера моделей в таблиці Product унікальні для всіх виробників та типів продуктів.
В таблиці PC для кожного ПК, який однозначно визначається унікальним кодом – code, вказані модель – model (зовнішній ключ до таблиці Product), швидкість – speed (процесора в мегагерцах), об’єм памяті – ram (в мегабайтах), розмір диска – hd (в гігабайтах), швидкість зчитуючого пристрою – cd (наприклад, '4x') та ціна – price.
Таблица Laptop аналогічна таблиці РС за винятком того, що замість швидкості CD містить розмір екрана – screen (в дюймах).
В таблиці Printer для кожної моделі принтера вказується, чи є він кольоровим – color ('y', якщо кольоровий), тип принтера – type (лазерний – 'Laser', струменевий – 'Jet' або матричний – 'Matrix') і ціна – price.
INCLUDEPICTURE "http://www.sql-ex.ru/images/computers.gif" \* MERGEFORMATINET
Фірма прийому сировини
Фірма має декілька пунктів прийому вторсировини. Кожний пункт отримує гроші для видачі здавачам вторсировити. Відомості про отримання грошей на пунктах прийому записуються в таблицю:
Income_o(point, date, inc)
Первинним ключем є (point, date). При цьому в стовпець date записується тільки дата (без часу), тобто прийом грошей (inc) на кожному пункті проводиться не частіше одного разу на день. Відомості про видачу грошей здавачам вторсировини записуються в таблицю:
Outcome_o(point, date, out)
У цій таблиці також первинний ключ (point, date) гарантує звітність кожного пункту про видані грошах (out) не частіше одного разу на день.
У випадку, коли прихід і витрата грошей може фіксуватися кілька разів на день, використовується інша схема з таблицями, що мають первинний ключ code:
Income(code, point, date, inc)
Outcome(code, point, date, out)
Тут також значення стовпця date не містять часу.
INCLUDEPICTURE "http://www.sql-ex.ru/images/income.gif" \* MERGEFORMATINET
Кораблі
Розглядається БД кораблів, що брали участь у другій світовій війні. Є наступні відношення:
Classes (class, type, country, numGuns, bore, displacement)
Ships (name, class, launched)
Battles (name, date)
Outcomes (ship, battle, result)
Кораблі в «класах» побудовані за одним і тим же проектом, і класу привласнюється або ім’я першого корабля, побудованого за даним проектом, або назві класу дається ім’я проекту, яке не збігається ні з одним з кораблів. Корабель, що дав назву класу, називається головним.
Відношення Classes містить ім’я класу, тип (bb для бойового (лінійного) корабля або bc для бойового крейсера), країну, в якій побудований корабель, число головних знарядь, калібр гармат (діаметр стовбура знаряддя в дюймах) і водотоннажність (вага в тоннах). У відношенні Ships записані назва корабля, ім’я його класу і рік спуску на воду. У відношенні Battles включені назва і дата битви, в якій брали участь кораблі, а у відношенні Outcomes ? результат участі даного корабля в битві (потоплений ? sunk, пошкоджений ? damaged або неушкоджений ? OK).
Зауваження. У відношення Outcomes можуть входити кораблі, які відсутні у відношенні Ships.
INCLUDEPICTURE "http://www.sql-ex.ru/images/ships.gif" \* MERGEFORMATINET
Аерофлот
Схема БД складається з чотирьох відношень:
Company (ID_comp, name)
Trip (trip_no, ID_comp, plane, town_from, town_to, time_out, time_in) Passenger (ID_psg, name)
Pass_in_trip (trip_no, date, ID_psg, place)
Таблиця Company містить ідентифікатор і назву компанії, яка здійснює перевезення пасажирів. Таблиця Trip містить інформацію про рейси: номер рейсу, ідентифікатор компанії, тип літака, місто відправлення, місто прибуття, час відправлення та час прибуття. Таблиця Passenger містить ідентифікатор та ім’я пасажира. Таблиця Pass_in_trip містить інформацію про польоти: номер рейсу, дата вильоту (день), ідентифікатор пасажира і місце, на якому він сидів під час польоту. При цьому слід мати на увазі, що
рейси виконуються щодня, а тривалість польоту будь-якого рейсу менше доби;
час і дата враховується щодо одного часового поясу;
час відправлення та прибуття вказується з точністю до хвилини;
серед пасажирів можуть бути однофамільці (однакові значення поля name, наприклад, Bruce Willis);
номер місця в салоні ? це число з буквою; число визначає номер ряду, літера (a ? d) ? місце в ряду зліва направо в алфавітному порядку;
зв’язки і обмеження показані на схемі даних.
INCLUDEPICTURE "http://www.sql-ex.ru/images/aero.gif" \* MERGEFORMATINET
Фарбування
Схема бази даних складається з трьох відношень:
utQ (Q_ID int,Q_NAME varchar(35)) utV (V_ID int,V_NAME varchar(35),V_COLOR char(1)) utB (B_Q_ID int,B_V_ID int,B_VOL tinyint, B_DATETIME datetime)
Таблиця utQ містить ідентифікатор і назву квадрата, колір якого спочатку чорний.
Таблиця utV містить ідентифікатор, назву та колір балончика з фарбою.
Таблиця utB містить інформацію про зафарбування квадрата балончиком: ідентифікатор квадрата, ідентифікатор балончика, кількість фарби і час фарбування.
При цьому слід мати на увазі, що:
Балончики з фарбою можуть бути трьох кольорів: червоний V_COLOR = 'R', зелений V_COLOR = 'G', блакитний V_COLOR = 'B' (латинські літери).
Обсяг балончика дорівнює 255 і спочатку він повний;
Колір квадрата визначається за правилом RGB, тобто R = 0, G = 0, B = 0 ? чорний, R = 255, G = 255, B = 255 ? білий;
Запис у таблиці зафарбувань utB зменшує кількість фарби в балончику на величину B_VOL і відповідно збільшує кількість фарби в квадраті на цю ж величину;
Значення 0 < B_VOL <= 255
Кількість фарби одного кольору в квадраті не перевищує 255, а кількість фарби в балончику не може бути менше нуля.
INCLUDEPICTURE "http://www.sql-ex.ru/images/painting.gif" \* MERGEFORMATINET