Konspekt_lektsiy_BD_amp_amp_SD
.pdfМ. Г. Глава. Технологія проектування і адміністрування баз даних та сховищ даних |
81 |
|
|
Задача: потрібно вивести поточний рейтинг всіх студентів по всіх дисциплінах з таблиці Rating. Однак користувача не цікавить їхній код — йому необхідні прізвища й імена, що описані в таблиці Student.
Для розв’язання цієї задачі необхідно виконати проекцію на деяку підмножину схеми відношення, отриманого в результаті з’єднання двох таблиць. При виконанні запиту (одного) до декількох таблиць і знадобиться уточнення імен атрибутів. Це уточнення здійснюється шляхом додавання до імені поля префікса у вигляді імені таблиці, відокремленого крапкою. Наприклад: Student.Patronymic, Rating.Mark, причому цей префікс додається до кожного атрибута з підмножини, що проеціюється. Отут саме і можна скористатися аліасами таблиць.
Наприклад (розв’язання задачі):
SELECT S.SecondName, S.FirstName, R.DKod, R.Mark FROM Student S, Rating R
WHERE S.Kod = R.Kod ORDER BY S.SecondName, R.DKod;
Правда, необхідно відзначити, що в питанні застосування префіксів цей приклад не абсолютно наочний. І от чому: якщо серед атрибутів, на які з’єднання виконує проекцію, немає однакових імен, то префікси можна опустити.
SELECT SecondName, FirstName, DKod, Mark
FROM Student S INNER JOIN Rating R ON S.Kod = R.Kod ORDER BY SecondName, DKod;
Тут наведено конструкцію (INNER JOIN), декілька відмінну від тої, що розглянуто у розділі „Реляційна алгебра“. Це пов’язано з тим, що в будь-яких діалектах SQL команду з’єднання значно розширено. У всіх СУБД замість „чистої“ команди JOIN використовуються команди:
INNER JOIN — внутрішнє з’єднання
та
LEFT | RIGHT | FULL OUTER JOIN — ліве, праве чи повне зовнішнє з’єднання.
Для розуміння різниці між цими типами з’єднань розглянемо приклад, в якому дещо порушимо правила формалізації біумовного зв’язку 1:1 та схему DWExec даних, наведену в Додатках А-В, а саме: до таблиці DWExec до-
дамо кортежі, що містять ідентифікатори студентів, які не ма- |
SKod |
DpKod |
||
ють тем дипломних проектів. Завдяки цьому в полі DpKod |
1 |
9 |
||
з’являться значення NULL, а таблиця DWExec прийме наступ- |
2 |
1 |
||
ний, не зовсім коректний вигляд: |
|
|
||
3 |
3 |
|||
Тепер вирішимо задачу формування переліку керівників |
|
|
||
4 |
2 |
|||
дипломних проектів з відображенням студентів, якими вони ке- |
||||
5 |
5 |
|||
рують, та ідентифікаторів тем дипломних проектів, що викону- |
||||
6 |
10 |
|||
ють ці студенти. Для вирішення цієї задачі виконаємо внутрі- |
||||
шнє з’єднання двох наведених таблиць: |
7 |
NULL |
||
SELECT LKod, SKod, L.DpKod |
8 |
NULL |
||
FROM |
DWList L INNER JOIN DWExec E ON |
L.DpKod = E.DpKod; |
Отримаємо такий результат:
Як можна бачити, у результуючій таблиці відсутні викладачі, що не керують дипломними проектами, та студенти, які ще не отримали тем дипломних проектів. Для того, щоб додати всю таку інформацію треба виконати зовнішнє з’єднання (точніше, повне зовнішнє з’єднання):
LKod |
SKod |
DpKod |
1 |
1 |
9 |
4 |
2 |
1 |
4 |
3 |
3 |
4 |
4 |
2 |
5 |
5 |
5 |
1 |
6 |
10 |
Кафедра економічної кібернетики та інформаційних технологій. Одеський національний політехнічний університет
82 |
М. Г. Глава. Технологія проектування і адміністрування баз даних та сховищ даних |
|
|
SELECT LKod, L.DpKod, SKod, E.DpKod |
|
|
|
|
||||
FROM DWList L |
FULL OUTER JOIN |
DWExec E |
||||||
|
|
|
|
|
|
ON |
L.DpKod = E.DpKod; |
|
Результуюча таблиця буде така: |
|
|
|
|
||||
|
|
|
|
|
|
|
|
|
|
LKod |
|
DpKod |
|
SKod |
|
DpKod |
|
|
4 |
|
1 |
|
2 |
|
1 |
|
|
4 |
|
2 |
|
4 |
|
2 |
|
|
4 |
|
3 |
|
3 |
|
3 |
|
|
5 |
|
5 |
|
5 |
|
5 |
|
|
1 |
|
9 |
|
1 |
|
9 |
|
|
1 |
|
10 |
|
6 |
|
10 |
|
|
5 |
|
4 |
|
NULL |
|
NULL |
|
|
2 |
|
6 |
|
NULL |
|
NULL |
|
|
2 |
|
7 |
|
NULL |
|
NULL |
|
|
3 |
|
8 |
|
NULL |
|
NULL |
|
|
NULL |
|
NULL |
|
7 |
|
NULL |
|
|
NULL |
|
NULL |
|
8 |
|
NULL |
|
З цієї таблиці видно, що „додаткові“ кортежі доповнюються NULL-значеннями у відповідних полях, отриманих з іншої таблиці.
Але такий результат може бути корисним на етапі призначення студентам тем дипломних проектів.
Адже після цього завідувачу кафедри, крім „зв’язку“ між керівниками дипломних проектів та студентами, потрібно знати тільки, хто з викладачів має дипломників, та які теми ще не видані. Для вирішення цієї більш обмеженої задачі треба виконати ліве зовнішнє з’єднання, яке додасть до результату внутрішнього з’єднання кортежі першої (що розташована до оператора JOIN) таблиці, які „не мають пари“:
SELECT LKod, L.DpKod, SKod, E.DpKod |
|
|
|
|
||||
FROM DWList L |
LEFT OUTER JOIN |
DWExec E |
||||||
|
|
|
|
|
|
ON |
L.DpKod = E.DpKod; |
|
Результат запиту наступний: |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
LKod |
|
DpKod |
|
SKod |
|
DpKod |
|
|
4 |
|
1 |
|
2 |
|
1 |
|
|
4 |
|
2 |
|
4 |
|
2 |
|
|
4 |
|
3 |
|
3 |
|
3 |
|
|
5 |
|
5 |
|
5 |
|
5 |
|
|
1 |
|
9 |
|
1 |
|
9 |
|
|
1 |
|
10 |
|
6 |
|
10 |
|
|
5 |
|
4 |
|
NULL |
|
NULL |
|
|
2 |
|
6 |
|
NULL |
|
NULL |
|
|
2 |
|
7 |
|
NULL |
|
NULL |
|
|
3 |
|
8 |
|
NULL |
|
NULL |
|
Деканату ж навпаки потрібно додатково знати тільки, хто з дипломників ще не наван-
Кафедра економічної кібернетики та інформаційних технологій. Одеський національний політехнічний університет
М. Г. Глава. Технологія проектування і адміністрування баз даних та сховищ даних |
83 |
|
|
тажений. Отут допоможе праве зовнішнє з’єднання, яке додасть до результату внутрішнього з’єднання „зайві“ кортежі другої таблиці, що знаходиться після оператора JOIN:
SELECT LKod, L.DpKod, SKod, E.DpKod |
|
|
|
|
||||
FROM DWList L |
RIGHT OUTER JOIN |
DWExec E |
||||||
|
|
|
|
|
|
ON |
L.DpKod = E.DpKod; |
|
Результуюча таблиця буде така: |
|
|
|
|
||||
|
|
|
|
|
|
|
|
|
|
LKod |
|
DpKod |
|
SKod |
|
DpKod |
|
|
4 |
|
1 |
|
2 |
|
1 |
|
|
4 |
|
2 |
|
4 |
|
2 |
|
|
4 |
|
3 |
|
3 |
|
3 |
|
|
5 |
|
5 |
|
5 |
|
5 |
|
|
1 |
|
9 |
|
1 |
|
9 |
|
|
1 |
|
10 |
|
6 |
|
10 |
|
|
NULL |
|
NULL |
|
7 |
|
NULL |
|
|
NULL |
|
NULL |
|
8 |
|
NULL |
|
Запит, з якого було розпочато знайомство з варіантами з’єднань виконує внутрішнє з’єднання, тому не відобразить ні студентів, що не мають коду, ні оцінки, які виставлені невідомо кому. Зрозуміло, що жодного з подібних кортежів в наведених таблицях бути не може.
В одному запиті можна виконувати з’єднання і більшого, ніж два, числа таблиць. Наприклад, додамо до запиту, що вирішує задачу відображення прізвищ студентів та їхнього рейтингу, назву спеціальності:
SELECT SPName, SecondName, FirstName, Mark
FROM |
Student S, Rating R, Speciality SP |
WHERE |
S.Kod = R.Kod AND S.Spec = SP.Spec |
ORDER BY S.Spec, SecondName;
І, нарешті, з’єднання можна виконати над однією таблицею (з’єднання таблиці самої із собою). Префікси та аліаси таблиці для цього просто необхідні. Наприклад, задача: з кожної підмножини викладачів, що мають однакову сумарну надбавку, необхідно вибрати тих, у кого надбавка за стаж перевищує 100 грн. (див. Додаток В).
Спочатку виконаємо такий запит:
SELECT LKod,
LongevityInc + DegreeInc + TitleInc + SpecialInc Summary_Increment, LongevityInc
FROM SalaryIncrements |
|
|||
ORDER BY |
2 DESC, LKod; |
|
||
який дасть наступний результат: |
|
|||
|
|
|
|
|
|
LKOD |
SUMMARY_INCREMENT |
LONGEVITYINC |
|
|
1 |
|
1845 |
300 |
|
5 |
|
1534 |
260 |
|
2 |
|
990 |
220 |
|
4 |
|
990 |
200 |
|
3 |
|
876 |
250 |
Вибірку викладачів з однаковою сумарною надбавкою можна виконати таким чином: SELECT SI2.LKod, SI2.LongevityInc + SI2.DegreeInc + SI2.TitleInc + SI2.SpecialInc
Кафедра економічної кібернетики та інформаційних технологій. Одеський національний політехнічний університет
84 |
М. Г. Глава. Технологія проектування і адміністрування баз даних та сховищ даних |
|
|
Summary_Increment, SI2.LongevityInc
FROM |
SalaryIncrements SI1, SalaryIncrements SI2 |
WHERE |
SI2.LongevityInc + SI2.DegreeInc + SI2.TitleInc + SI2.SpecialInc = |
|
SI1.LongevityInc + SI1.DegreeInc + SI1.TitleInc + SI1.SpecialInc |
ORDER BY 2 DESC, SI2.LKod;
Дана інструкція фактично виконує з’єднання двох копій таблиці SalaryIncrements. Залишається додати тільки до вислову WHERE другу частину умови:
… AND SI2. LongevityInc > 100 …
Однак у цьому прикладі результуюча таблиця буде включати й одиничні підгрупи, тому що сумарна надбавка кожного викладача дорівнює сумарній надбавці цього ж викладача з другої копії таблиці:
LKOD |
SUMMARY_INCREMENT |
LONGEVITYINC |
1 |
1845 |
300 |
5 |
1534 |
260 |
2 |
990 |
220 |
2 |
990 |
220 |
4 |
990 |
200 |
4 |
990 |
200 |
3 |
876 |
250 |
Розпізнати такі кортежі можна за збігом первинних ключів і, відповідно, виключити їх. Для цього до вислову WHERE додамо ще одну умову:
… AND SI2.LKod <> SI1.LKod …
Ця умова забезпечить видалення з результату не тільки одиничних підгруп, але і зайвих кортежів з підгруп, що містять по два рядка, тому що один з них також є з’єднанням кортежу самого із собою. Але якщо в підгрупі було три і більш рядків, то їхнє число зменшується на одиницю. Відповідно залишається по два і більше однакових кортежів. Позбутися від них можна за допомогою оператора DISTINCT:
SELECT DISTINCT …
І, нарешті, над результатом з’єднання копій однієї таблиці можна виконати з’єднання з іншим відношенням. Наприклад, замінивши LKod на прізвище й ім’я з таблиці Lecturer. Кінцевий варіант запиту буде виглядати таким чином:
|
SELECT DISTINCT SecondName, FirstName, |
|
|
|
|
|||
|
|
|
SI2.LongevityInc + SI2.DegreeInc + SI2.TitleInc + SI2.SpecialInc |
|||||
|
|
|
Summary_Increment, SI2.LongevityInc |
|
|
|||
|
FROM |
SalaryIncrements SI1, SalaryIncrements SI2, Lecturer L |
|
|
||||
|
WHERE |
SI2.LongevityInc + SI2.DegreeInc + SI2.TitleInc + SI2.SpecialInc = |
||||||
|
|
SI1.LongevityInc + SI1.DegreeInc + SI1.TitleInc + SI1.SpecialInc |
||||||
|
|
AND SI2. LongevityInc > 100 |
|
|
|
|
||
|
|
AND SI2.LKod <> SI1.LKod |
|
|
|
|
||
|
|
AND L.Kod = SI2.LKod |
|
|
|
|
||
|
ORDER BY 3 DESC, SI2.LKod; |
|
|
|
|
|||
|
В результаті отримаємо: |
|
|
|
|
|||
|
|
|
|
|
|
|||
|
SECOND_NAME |
FIRST_NAME |
SUMMARY_INCREMENT |
LONGEVITYINC |
|
|||
|
Погорецька |
|
Валентина |
|
990 |
|
200 |
|
|
Востров |
|
Георгій |
|
990 |
|
220 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Кафедра економічної кібернетики та інформаційних технологій. Одеський національний політехнічний університет
М. Г. Глава. Технологія проектування і адміністрування баз даних та сховищ даних |
85 |
|
|
До цього запиту необхідно зробити зауваження про те, що версії СУБД PostgreSQL до 8.3 вимагають для визначення аліасу стовпця використання оператора AS.
Проте СУБД PostgreSQL і останніх версій на такий запит поверне помилку через те, що, ця СУБД, як і деякі інші, при використанні оператора DISTINCT дозволяє впорядковувати результуючі таблиці тільки за тими полями, на які виконано проекцію. Тому або треба по-
збавитись оператора DISTINCT, або додати в проекцію поле LKod з другої копії таблиці
SalaryIncrements:
SELECT DISTINCT SecondName, FirstName, SI2.LKod,
SI2.LongevityInc + SI2.DegreeInc + SI2.TitleInc + SI2.SpecialInc AS Summary_Increment, …
Як компенсацію за цей невеличкий недолік СУБД PostgreSQL має ще більш розширені можливості стосовно реалізації операції з’єднання.
Зокрема, в цьому діалекті з’єднана таблиця — це таблиця, отримана із двох інших не тільки реальних, але й також з’єднаних таблиць відповідно до правил певного типу з’єднання. Тут, окрім внутрішнього та зовнішнього з’єднання, існує ще й перехресне:
… R CROSS JOIN S …
Цей тип з’єднання, насправді, це ще одна реалізація декартового добутку. Таким чином, вираз FROM R CROSS JOIN S еквівалентний виразу FROM R, S або виразу FROM R
INNER JOIN S ON TRUE (див. нижче).
Обмеженні з’єднання в PostgreSQL:
…R {[INNER] | {LEFT | RIGHT | FULL} [OUTER]} JOIN S ON булевий вираз …
…R {[INNER] | {LEFT | RIGHT | FULL} [OUTER]} JOIN S USING список стовпців …
…R NATURAL {[INNER] | {LEFT | RIGHT | FULL} [OUTER]} JOIN S …
Оператори INNER і OUTER необов’язкові у всіх формах: INNER використовується за замовчуванням, а LEFT, RIGHT і FULL мають на увазі зовнішнє з’єднання.
Умова з’єднання вказується в операторі ON або USING, або, неявно, оператором NATURAL. Умова з’єднання визначає, які рядки двох вихідних таблиць „відповідають“ один одному.
ON — найбільш загальний вид умови з’єднання: вона приймає значення логічного виразу того ж типу, що використовується в пропозиції WHERE. Пари рядків з R і S з’єднуються, якщо ON вираз є для них істинним.
USING — скорочена нотація ON: вона містить список розділених комами імен стовпців, які в таблицях, що з’єднуються, повинні бути однаковими, і формує умову з’єднання шляхом порівняння кожної із цих пар стовпців. Крім того, результатом JOIN USING буде один стовпець для кожної з порівнюваних пар вхідних стовпців плюс всі інші стовпці кожної таблиці. Таким чином,
USING (a, b, c)
еквівалентно
ON (R.a = S.a AND R.b = S.b AND R.c = S.c)
за винятком того, що якщо використовується ON у результаті буде по два стовпці a, b, і c, у той час як з USING залишиться тільки один з кожної пари.
NATURAL — коротка форма USING: вона утворює список USING, що складається з тих і тільки тих імен стовпців, які присутні в обох вхідних таблицях. Як і в USING, ці стовпці відображаються у вихідній таблиці тільки один раз.
І нарешті, дуже важливою особливістю діалекту PostgreSQL є можливість створення „багатоповерхових“ конструкцій з операторів JOIN. Тобто з’єднання будь-якого типу можуть
Кафедра економічної кібернетики та інформаційних технологій. Одеський національний політехнічний університет
86 |
М. Г. Глава. Технологія проектування і адміністрування баз даних та сховищ даних |
|
|
бути об’єднані в ланцюжок або вкладені один до одного: одна або обидві таблиці R і S можуть бути результуючими таблицями операції з’єднання (JOIN-виразами). Для контролю порядку з’єднань можна використовувати круглі дужки навколо JOIN-виразів. При відсутності дужок, вкладена множина операторів JOIN розглядається зліва направо.
Наступний приклад ранжує студентів по їх рейтингу. Для цього використовуються віконні функції в з’єднанні. Набір рядків розбивається на фрагменти з урахуванням значення стовпця DKod і сортується по стовпцю Mark. При цьому ключове слово ORDER BY у реченні OVER впорядковує результат RANK, а ORDER BY в інструкції SELECT впорядковує результуючий набір:
SELECT S.SecondName, D.DName, R.Mark, |
|
|
|
|
|||
|
RANK() |
OVER (PARTITION BY R.Dkod |
|
|
|
|
|
|
ORDER BY R.Mark DESC) AS "RANK" |
|
|||||
|
FROM Student S INNER JOIN |
Rating R ON S.Kod = R.Kod |
|
||||
|
|
INNER JOIN |
Discipline D |
ON R.DKod = D.Dkod |
|||
|
ORDER BY |
S.SecondName; |
|
|
|
|
|
Результат цього запиту буде такий: |
|
|
|
|
|
||
|
|
|
|
|
|
|
|
|
SecondName |
DName |
|
Mark |
|
RANK |
|
|
Арсірій |
Дослідження операцій |
|
20 |
|
4 |
|
|
Арсірій |
Мережеві технології |
|
76 |
|
3 |
|
|
Арсірій |
Технології проектування БД |
|
90 |
|
1 |
|
|
Блажко |
Економіка |
|
|
97 |
|
1 |
|
Бровков |
Технології проектування БД |
|
82 |
|
3 |
|
|
Бровков |
Мережеві технології |
|
10 |
|
4 |
|
|
Бровков |
Дослідження операцій |
|
75 |
|
2 |
|
|
Журан |
Технології проектування БД |
|
86 |
|
2 |
|
|
Любченко |
Мережеві технології |
|
85 |
|
1 |
|
|
Любченко |
Технології проектування БД |
|
46 |
|
5 |
|
|
Любченко |
Дослідження операцій |
|
100 |
|
1 |
|
|
Мікулінська |
Економічна кібернетика |
|
30 |
|
1 |
|
|
Філатова |
Мережеві технології |
|
85 |
|
1 |
|
|
Філатова |
Технології проектування БД |
|
54 |
|
4 |
|
|
Філатова |
Дослідження операцій |
|
65 |
|
3 |
|
|
Філиппова |
Технології проектування БД |
|
0 |
|
6 |
У наступному прикладі повертається значення DENSE_RANK рейтингу студентів для різних дисциплін, при цьому аргумент ORDER BY у реченні OVER упорядковує результат DENSE_RANK, а ORDER BY в інструкції SELECT упорядковує результуючий набір:
SELECT S.SecondName, D.DName, R.Mark,
DENSE_RANK() |
OVER (PARTITION BY R.Dkod |
|
ORDER BY |
R.Mark DESC) AS "DENSE_RANK" |
|
FROM Student S INNER JOIN |
Rating R ON S.Kod = R.Kod |
|
INNER JOIN |
Discipline D ON R.DKod = D.Dkod |
|
ORDER BY S.SecondName; |
|
Цей приклад є, на перший погляд, не зовсім виразним через те, що результуюча таблиця майже не відрізняється від попередньої окрім деяких значень стовпця DENSE_RANK:
|
SecondName |
DName |
Mark |
DENSE_RANK |
|
|
Арсірій |
Дослідження операцій |
20 |
4 |
|
|
Арсірій |
Мережеві технології |
76 |
2 |
|
|
|
|
|
|
|
|
|
|
|
|
|
Кафедра економічної кібернетики та інформаційних технологій. Одеський національний політехнічний університет
|
М. Г. Глава. Технологія проектування і адміністрування баз даних та сховищ даних |
87 |
|||
|
|
|
|
|
|
|
|
|
|
|
|
|
SecondName |
DName |
Mark |
DENSE_RANK |
|
|
Арсірій |
Технології проектування БД |
90 |
1 |
|
|
Блажко |
Економіка |
97 |
1 |
|
|
Бровков |
Технології проектування БД |
82 |
3 |
|
|
Бровков |
Мережеві технології |
10 |
3 |
|
|
Бровков |
Дослідження операцій |
75 |
2 |
|
|
Журан |
Технології проектування БД |
86 |
2 |
|
|
Любченко |
Мережеві технології |
85 |
1 |
|
|
Любченко |
Технології проектування БД |
46 |
5 |
|
|
Любченко |
Дослідження операцій |
100 |
1 |
|
|
Мікулінська |
Економічна кібернетика |
30 |
1 |
|
|
Філатова |
Мережеві технології |
85 |
1 |
|
|
Філатова |
Технології проектування БД |
54 |
4 |
|
|
Філатова |
Дослідження операцій |
65 |
3 |
|
|
Філиппова |
Технології проектування БД |
0 |
6 |
|
У наступному прикладі рядки поділяються на три групи
SELECT S.SecondName, D.DName, R.Mark,
NTILE(3) OVER (ORDER BY R.Mark DESC) AS "GR_RANK"
|
FROM Student S INNER JOIN |
Rating R ON S.Kod = R.Kod |
|||
|
|
INNER JOIN |
Discipline D |
ON R.DKod = D.DKod; |
|
в результаті чого отримаємо таку таблицю: |
|
|
|
||
|
|
|
|
|
|
|
SecondName |
DName |
Mark |
GR_RANK |
|
|
Любченко |
Дослідження операцій |
100 |
1 |
|
|
Блажко |
Економіка |
|
97 |
1 |
|
Арсірій |
Технології проектування БД |
90 |
1 |
|
|
Журан |
Технології проектування БД |
86 |
1 |
|
|
Любченко |
Мережеві технології |
85 |
1 |
|
|
Філатова |
Мережеві технології |
85 |
1 |
|
|
Бровков |
Технології проектування БД |
82 |
2 |
|
|
Арсірій |
Мережеві технології |
76 |
2 |
|
|
Бровков |
Дослідження операцій |
75 |
2 |
|
|
Філатова |
Дослідження операцій |
65 |
2 |
|
|
Філатова |
Технології проектування БД |
54 |
2 |
|
|
Любченко |
Технології проектування БД |
46 |
3 |
|
|
Мікулінська |
Економічна кібернетика |
30 |
3 |
|
|
Арсірій |
Дослідження операцій |
20 |
3 |
|
|
Бровков |
Мережеві технології |
10 |
3 |
|
|
Філиппова |
Технології проектування БД |
0 |
3 |
Змінюючи попередній приклад запиту, додамо аргумент PARTITION BY. Рядки спочатку поділяються по атрибуту DKod, а потім поділяються на три групи для кожного значення Mark. При цьому ORDER BY у реченні OVER упорядковує результат NTILE, а ORDER BY в інструкції SELECT — результуючий набір:
SELECT S.SecondName, D.DName, R.Mark,
NTILE(3) OVER (PARTITION BY R.Dkod
ORDER BY |
R.Mark DESC) AS "GR_RANK" FROM |
Student S INNER JOIN |
Rating R ON S.Kod = R.Kod |
INNER JOIN |
Discipline D ON R.DKod = D.DKod; |
Кафедра економічної кібернетики та інформаційних технологій. Одеський національний політехнічний університет
88 |
М. Г. Глава. Технологія проектування і адміністрування баз даних та сховищ даних |
|
|
Це, у свою чергу, утворить наступни зміни у результуючій таблиці:
SecondName |
DName |
Mark |
GR_RANK |
Арсірій |
Технології проектування БД |
90 |
1 |
Журан |
Технології проектування БД |
86 |
1 |
Бровков |
Технології проектування БД |
82 |
2 |
Філатова |
Технології проектування БД |
54 |
2 |
Любченко |
Технології проектування БД |
46 |
3 |
Філиппова |
Технології проектування БД |
0 |
3 |
Любченко |
Мережеві технології |
85 |
1 |
Філатова |
Мережеві технології |
85 |
1 |
Арсірій |
Мережеві технології |
76 |
2 |
Бровков |
Мережеві технології |
10 |
3 |
Мікулінська |
Економічна кібернетика |
30 |
1 |
Блажко |
Економіка |
97 |
1 |
Любченко |
Дослідження операцій |
100 |
1 |
Бровков |
Дослідження операцій |
75 |
1 |
Філатова |
Дослідження операцій |
65 |
2 |
Арсірій |
Дослідження операцій |
20 |
3 |
Демонстраційний приклад
Запишіть SQL-запити для маніпулювання даними з таблиць, що створені у завданні „Демонстраційний приклад“ розділу „Мова визначення даних (МВД, DDL) SQL. Частина 1“.
П.1. Складіть SQL-запити щодо введення нової інформації до таблиць бази даних. При цьому визначте та відстежте послідовність наповнення таблиць для зберігання посилальної цілісності.
Розв’язання.
Згідно схемі даних таблиці aircompany, passenger та employee містять батьківські ключі для таблиць voyage та ticket і не мають зовнішніх ключів. Тому вони створювались першими, і до них в першу чергу повинні вноситись нові кортежі. Таблиця voyage містить батьківський ключ для таблиці class, і до неї новий кортеж буде внесений наступним. Потім — у таблицю class з батьківським ключем для таблиці ticket, яку і створено останньою, і нові дані до неї можна буде внести тільки при наявності відповідних значень у таблицях class, passenger та employee.
INSERT INTO aircompany
VALUES (NEXTVAL(‘s_aircompany’), ‘Lufthansa’, ‘Німеччина’);
Для цього прикладу можна далі заповнити повністю кожну з таблиць за означеною чергою, а можна, як в реальній системі: до відповідної таблиці(-ць) внести кортеж(і) з батьківським(и) ключем(-ами), а потім — всі кортежі з залежними від нього(них) зовнішніми ключами.
INSERT INTO |
voyage |
VALUES (NEXTVAL(‘s_voyage’), ‘RK 4578’, ‘Одеса – Нью-Йорк’, |
|
|
‘2012-08-09’, ‘12:30:00’, ‘2012-08-10’, ‘03:40:00’, ‘Boeing 747’, 1); |
INSERT INTO |
class VALUES (NEXTVAL(‘s_class’), ‘бізнес’, 1, 1000.00, 50); |
INSERT INTO |
passenger |
VALUES (NEXTVAL(‘s_passenger’), ‘Чугунов А.А.’, ‘м’, ‘КН 123456’);
Кафедра економічної кібернетики та інформаційних технологій. Одеський національний політехнічний університет
М. Г. Глава. Технологія проектування і адміністрування баз даних та сховищ даних |
89 |
|
|
|
|
INSERT INTO |
employee |
|
VALUES (NEXTVAL(‘s_employee’), ‘Арсірій О.О.’, ‘касир’, ‘1966-03-06’, |
|
|
2345678); |
‘КМ 555555’, 56788765349876, ‘м. Одеса, вул. Блакитна, б. 6, кв. 35’, |
|
|
|
|
INSERT INTO |
ticket VALUES (NEXTVAL(‘s_ticket’), 4, 6, 10, 2, ‘покупка’); |
|
Проконтролювати введені кортежі можна за допомогою відповідних команд
SELECT * FROM таблиця;
Наприклад.
SELECT * FROM aircompany;
|
|
id_aircompany |
|
|
name |
|
country |
|
||
|
1 |
|
|
Lufthansa |
|
Німеччина |
|
|||
|
2 |
|
|
Аеросвіт |
|
Україна |
|
|||
|
3 |
|
|
Трансаэро |
|
Україна |
|
|||
|
4 |
|
|
Днеправіа |
|
Україна |
|
|||
|
5 |
|
Turkish Airlines |
|
Турція |
|
||||
SELECT * FROM class; |
|
|
|
|
|
|
|
|||
|
|
|
|
|
|
|
|
|||
id_class |
|
Name |
|
|
voyage |
price |
quantity_place |
|||
1 |
|
Бізнес |
|
|
1 |
1000.00 |
50 |
|||
2 |
|
економ покращений |
|
1 |
800.00 |
50 |
||||
3 |
|
Економ |
|
|
1 |
500.00 |
100 |
|||
4 |
|
Бізнес |
|
|
2 |
1200.00 |
50 |
|||
5 |
|
економ покращений |
|
2 |
600.00 |
50 |
||||
6 |
|
Економ |
|
|
2 |
300.00 |
100 |
|||
7 |
|
бізнес |
|
|
3 |
1500.00 |
50 |
|||
8 |
|
економ покращений |
|
3 |
1400.00 |
50 |
||||
9 |
|
економ |
|
|
3 |
1100.00 |
100 |
|||
10 |
|
бізнес |
|
|
4 |
2000.00 |
50 |
|||
11 |
|
економ |
|
|
4 |
1800.00 |
50 |
|||
12 |
|
економ |
|
|
4 |
1500.00 |
100 |
SELECT * FROM passenger;
id_passenger |
full_name |
sex |
passport |
1 |
Малахов Є.В. |
м |
КМ 123456 |
2 |
Чугунов А.А. |
м |
КН 123456 |
3 |
Альохін О.Б. |
м |
КМ 654321 |
4 |
Філиппова С.В. |
ж |
КЕ 123456 |
5 |
Журан О.А. |
ж |
КМ 234567 |
6 |
Лінгур Л.М. |
ж |
КМ 345678 |
7 |
Андрієнко В.М. |
ж |
КМ 456789 |
SELECT * FROM voyage;
Кафедра економічної кібернетики та інформаційних технологій. Одеський національний політехнічний університет
|
90 |
|
М. Г. Глава. Технологія проектування і адміністрування баз даних та сховищ даних |
|
|
|
|
|
|
|||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
|
id_ |
number |
destination |
date_ |
time_ |
|
date_ |
time_ |
|
type_ |
|
|
|
aircom- |
|
|||||||||||
|
voyage |
departure |
departure |
arrival |
arrival |
|
aircraft |
|
|
pany |
|
|||||||||||||||
|
1 |
RK |
Одеса – |
2012-08-09 |
|
12:30:00 |
|
2012-08-10 |
03:40:00 |
|
Boeing |
|
|
1 |
|
|
||||||||||
|
|
4578 |
|
Нью-Йорк |
|
|
|
|
|
|
|
|
|
|
|
|
747 |
|
|
|
|
|
|
|
||
|
2 |
RK |
Одеса – |
2009-06-11 |
|
18:40:00 |
|
2009-06-12 |
02:50:00 |
А310 |
|
|
1 |
|
|
|||||||||||
|
3467 |
|
Лондон |
|
|
|
|
|
|
|||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||
|
3 |
RK |
Одеса – |
2009-03-19 |
|
17:40:00 |
|
2009-03-19 |
17:20:00 |
АН 158 |
|
|
2 |
|
|
|||||||||||
|
3478 |
|
Бухарест |
|
|
|
|
|
|
|||||||||||||||||
|
4 |
RK |
Одеса – |
2009-05-05 |
|
12:10:00 |
|
2009-05-06 |
15:50:00 |
|
Boeing |
|
|
2 |
|
|
||||||||||
|
|
2398 |
|
Пекін |
|
|
|
|
|
|
|
|
|
|
|
|
747 |
|
|
|
|
|
|
|
||
|
5 |
RK |
Одеса – |
2009-06-12 |
|
18:45:00 |
|
2009-06-13 |
16:20:00 |
ИЛ 76 |
|
|
2 |
|
|
|||||||||||
|
3490 |
|
Рим |
|
|
|
|
|
|
|
||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||
|
SELECT * FROM employee; |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||
|
id_ |
|
full_name |
posi- |
date_birth |
passport |
identification_ |
|
adress |
|
|
|
telefon |
|
||||||||||||
|
employee |
tion |
code |
|
|
|
|
|
|
|||||||||||||||||
|
1 |
|
Шевченко |
Касир |
1956-10- |
|
КМ |
|
|
|
|
|
м. Одеса, |
|
|
|
|
|
|
|||||||
|
|
|
23458765349876 |
вул. Незнайкіна, |
1234567 |
|
||||||||||||||||||||
|
|
|
Г.В. |
|
|
04 |
|
777777 |
|
|
|
|
|
б. 56, кв. 45 |
|
|
|
|
|
|
||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||
|
2 |
|
Арсірій |
Касир |
1966-03- |
КМ |
|
|
|
|
|
м. Одеса, |
|
|
|
|
|
|
||||||||
|
|
56788765349876 |
вул. Блакитна, |
|
2345678 |
|
||||||||||||||||||||
|
|
О.О. |
|
06 |
|
555555 |
|
|
||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
б. 6, кв. 35 |
|
|
|
|
|
|
|||
|
3 |
|
Погорецька |
Касир |
1974-09- |
КМ |
|
|
|
|
|
м. Одеса, |
|
|
|
|
|
|
||||||||
|
|
23458456349876 |
пл. Рожева, б. 2, |
|
3456789 |
|
||||||||||||||||||||
|
|
В.Я. |
|
12 |
|
444111 |
|
|
||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
кв. 32 |
|
|
|
|
|
|
|
|
|
|
|
|
Івченко |
|
1984-05- |
КМ |
|
|
|
|
|
м. Одеса, |
|
|
|
|
|
|
||||||||
|
4 |
|
Касир |
85474776534987 |
вул. Біла, |
|
|
|
7654321 |
|
||||||||||||||||
|
|
І.Ю. |
|
10 |
|
222333 |
|
|
|
|
||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
б. 23, кв. 4 |
|
|
|
|
|
|
|||
|
SELECT * FROM ticket; |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||||
|
id_ticket |
|
passenger |
|
class |
|
|
|
place |
|
|
employee |
|
operation |
|
|||||||||||
|
1 |
|
|
|
|
4 |
|
6 |
|
|
|
10 |
|
|
2 |
|
|
|
|
покупка |
|
|||||
|
2 |
|
|
|
|
1 |
|
5 |
|
|
|
12 |
|
|
2 |
|
|
|
|
бронь |
|
|||||
|
3 |
|
|
|
|
3 |
|
4 |
|
|
|
11 |
|
|
4 |
|
|
|
|
покупка |
|
|||||
|
4 |
|
|
|
|
5 |
|
2 |
|
|
|
14 |
|
|
4 |
|
|
|
|
покупка |
|
|||||
|
5 |
|
|
|
|
2 |
|
2 |
|
|
|
21 |
|
|
4 |
|
|
|
|
покупка |
|
|||||
|
6 |
|
|
|
|
7 |
|
2 |
|
|
|
6 |
|
|
4 |
|
|
|
|
покупка |
|
|||||
|
7 |
|
|
|
|
6 |
|
2 |
|
|
|
4 |
|
|
4 |
|
|
|
|
бронь |
|
П.2. Виберіть напрямки польотів певного типу літака (за вибором студента).
Розв’язання.
SELECT destination FROM voyage WHERE type_aircraft=‘Boeing 747’;
Результуюча таблиця.
destination
Одеса – Нью Йорк
Одеса – Пекін
П.3. Створіть представлення (view), що містить інформацію про напрямки рейсів, кількість та загальну суму проданих квитків на кожен напрямок. Результат відсортуйте за напря-
Кафедра економічної кібернетики та інформаційних технологій. Одеський національний політехнічний університет