Приклад 2. Визначити, є чи спільні співробітники в двох різних організаціях. Для цього видамо список співпадаючих прізвищ: SELECT * FROM PERS WHERE FAM IN (SELECT FAM FROM PERS1).
При роботі з вкладеними запитами можна використовувати ключові слова:
1)ANY,ALL;
EXISTS,SINGULAR.
ALL означає, що умова виконується для всіх записів, ANY – хоча б для однієї.
Приклад 3. Список співробітників підприємства, що мають максимальну зарплату:
SELECT FAM FROM PERS WHERE ZARPL>= ALL (SELECT ZARPL FROM PERS).
Приклад 4.
SELECT FAM FROM PERS WHERE DEP=’Бібліотека’ AND ZARPL>= ANY (SELECT ZARPL FROM PERS WHERE DEP LIKE ’Цех%’)
Виділяє співробітників бібліотеки з зарплатою не нижче ніж у самих низькооплачуваних працівників будь-якого цеху.
Ключове слово EXISTS (квантор існування) означає добір тільки тих значень, для яких вкладений запит поверне одне чи більше значень, а ключове слово SINGULAR - коли вкладений запит повертає одне і тільки одне значення.
Приклад 5. Сформувати список співробітників, що мають однолітків:
SELECT TABNUM, FAM, YEAR_B FROM PERS P1
WHERE EXISTS(SELECT TABNUM, FAM,YEAR_B FROM PERS P2
WHERE P1.YEAR_B=P2.YEAR_B AND P1.TABNUM<>P2.TABNUM) .
Приклад 6.Сформувати список список співробітників, що не мають однолітків:
SELECT TABNUM, FAM, YEAR_B FROM PERS P1
WHERE SINGULAR(SELECT TABNUM, FAM, YEAR_B FROM PERS P2 WHERE P1.YEAR_B=P2.YEAR_B AND P1.TABNUM=P2.TABNUM).
Тут P1 і P2 – псевдоніми таблиці PERS, вони дозволяють звертатися з зовнішнього і вкладеного запиту до одній і тій же таблиці як до різних таблиць. Псевдоніми мають значення тільки до кінця речення SQL, далі вони стають невизначеними.
Операції з таблицями в мові SQL
Оператор створення бази даних(БД) актуальний тільки для серверних додатків. У ньому визначається розташування БД, права доступу до її елементів і так далі. У локальних додатках роль БД грає каталог(папка). Тому методи створення БД для локальних баз даних не розглядаються.
Створення таблиць бази даних. Таблиці бази даних є основними будівельними блоками для БД. Щоб створити таблицю необхідно:
1)задати ім'я таблиці (не повинне збігатися з ім'ям існуючих таблиць даної БД);
2)задати імена стовпців, типи складових її стовпців і їхньої довжини(для строкових полів)
3) індекси.
4)звязки між таблицями.
Можливі типи даних(Paradox):
1)Char(n) –строкове поле, довжиною n символів, 0<n<256. Може задаватися вид кодування. Наприклад, Name Char(20) CHARACTER SET.
WIN1251 задає російське кодування для поля Name довжиною 20 байт.
2) Int – відповідає типу integer мови Delphi.
3)Smallint - відповідає типу smallint мови Delphi.
4)BLOB –ланцюжок байт невизначеної довжини.
5)FLOAT - відповідає типу Real мови Delphi.
6)Date – поле для збереження дат
7)BOOLEAN - відповідає типу boolean мови Delphi.
8)AUTOINC – автоинкроементное поле
Перерахування стовпців повинне бути в круглих дужках, опис стовпців відокремлюються один від одного комами.
CREATE TABLE ім'я_таблиці
({визначення стовпця1,…,
визначення стовця2,… } )
Після оголошення деяких полів можна вказувати PRIMARY KEY, що вказує, що ці поля складають первинний індекс(ключ). Таких полів може бути кілька. Порядок входження полів у первинний індекс задається порядком полів в операторі створення таблиці. Крім того, можна вказувати NOT NULL після оголошення деяких стовпців. Це означає, що значення полів обов'язково повинне бути задане в кожному записі. Порушення цієї умови викликає виняткову ситуацію. Також можна для поля вказувати ключове слово UNIQUE. Це означає, що значення цього поля повинне бути унікальним. Порушення цієї умови викликає виняткову ситуацію. Після оголошення поля можна вказувати DEFAULT(значення за замовчуванням).Наприклад, Pol char(1) DEFAULT 'М' .
У таблиці можуть бути поля, що обчислюються. Вони позначаються ключовими словами COMPUTED BY замість типу даних.
Наприклад, Year_b smallint DEFAULT 1950,Age COMPUTED BY (2000-Year_b).
Поле Age це поле, що обчислюється. Можна встановлювати обмеження на значення полів за допомогою ключового слова CHECK.
дозволяють відкинути невірні дані при введенні дати народження при вступі на роботу - не приймаємо занадто старих і занадто молодих. Проте весь досвід свідчить, що немає непорушних обмежень – рано чи пізно від них змушують відмовлятися. Краще видавати попередження, ніж категоричне обмеження.
Створювану таблицю можна об'єднати (тільки як дочірню) з раніше створеною таблицею відношенням ссилочної цілісності. При цьому задається інформація про індекси таблиці і список ключів цієї таблиці, що використовуються для зв'язку з батьківської (ключові слова FOREGIN KEY). Також задається інформація про ім'я батьківської таблиці, списку ключових полів батьківської таблиці, по яких відбувається об'єднання з боку батьківської таблиці(ключове слово REFERENCES). Ці поля можуть бути відсутніми, якщо це первинний індекс в батьківскій таблиці. Крім того, можуть задаватися не обов'язкові елементи ON DELETE та ON UPDATE, що визначають, які дії повинні виконуватися при видаленні чи модифікації ключових полів з запису батьківської таблиці, якщо є зв'язані записи в підлеглій таблиці:
1) NO ACTION – видалення чи зміна ключових полів заборонена.
2) CADCADE – при видаленні запису в батьківській таблиці відбувається видалення всіх підлеглих записів у дочірніх таблицях. При зміні ключових полів відбувається синхронна зміна підлеглих записів у дочірніх таблицях.
3) SET DEFAULT при видаленні запису чи зміні ключових полів запису у батьківській таблиці відбувається присвоєння ключовим полям дочірньої таблиці в підлеглих записах значень за замовчуванням.
4) SET NULL при видаленні запису чи зміні ключових полів у батьківській таблиці відбувається присвоєння ключовим полям дочірньої таблиці в підлеглих записах нульових значень.
Ці ключові слова мови SQL задаються наприкінці опису таблиці. Наприклад, нехай зв'язується вже існуюча таблиця KADR зі створюваною таблицею ZARPL по коду KODRAB(код працівника), причому по KODRAB у таблиці KADR створений первинний индекс (первинний ключ), а в таблиці ZARPL буде також створений індекс (за допомогою ключових слів CREATE INDEX).
Тоді зв'язати таблиці KADR і ZARPL можна за наступним чином:
FOREGIN KEY(KODRAB) REFERENCES KADR KODRAB
Для того, щоб запрограмувати реакцію на видалення з головної таблиці запису необхідно вказати наприкінці опису таблиці ZARPL:ON DELETE CASCADE. Це буде озаначати, що якщо віддаляється запис з таблиці KADR, то автоматично видаляються всі відповідні записи і з таблиці ZARPL. Визначати
ON UPDATE ... у нашому випадку безглуздо, тому що в добре спланованому додатку в користувача не повинне бути доступу до внутрішніх полів типу KODRAB! Зв'язувати ці таблиці по декількох полях теж досить необачно.
Можливості оператора Create Table (в основному) дублюються програмою DataBase DeskTop. Тому доцільніше використовувати DataBase DeskTop на етапі проектування додатка.
Приклад. Потрібно створити таблицю з назвою TablNew. Вона повинна мати поля з іменами Name, Zarpl, Age. Перше поле містить російські букви. Речення SQL Create Table помістимо в рядок s. Потім послідовно викличемо методи Close() - закрити компонент Query1,Clear() –очистити список речень SQL. Потім додамо нове речення SQL у список за допомогою методу Add(s). Після цього виконується метод EXECSQL(), що власне і виконує оператори SQL. Варто помітити, що такий метод характерний для всіх операторів SQL, крім оператора Select.
s:=' Create Table TableNew(Name CHAR(20) CHARACTER SET WIN1251,zarpl FLOAT, age INT)';
