Команда SQL-SELECT

Размещено на http://www.allbest.ru/

Реферат

На тему “Команда SQL - SELECT”

Львів 2012

Зміст

1 Алгебраїчні оператори в запитах

2 Запити, які залучають кілька таблиць (об'єднання таблиць)

3 Підсумкові запити

4 Групування запитів

5 Підзапити

6 Зовнішні об'єднання

7 Висновки

1 Алгебраїчні оператори в запитах

алгебраїчний оператор запит

Для визначення даних, котрі ми хочемо відшукати з бази даних можна використати кілька запитів пов'язаних між собою алгебраїчними операторами відповідно до синтаксису:SELECT_instruction operator SELECT_instruction

Є три таких оператора:

UNION, UNION ALL- об'єднання результатів запиту (з вилученням рядків що повторюються чи ні відповідно).

INTERSECT- перетин результатів запиту.

EXCEPT- різниця результатів запиту (в Oracle також MINUS).

Приклад

Показати кількість відділів в яких немає найнятих працівників на даний час.

Щоб вирішити цю проблему, по-перше знайти кількість відділів, де на даний час працюють декілька працівників і потім використати оператор MINUS:

SELECT Dept.Deptno FROM Dept

MINUS

SELECT Emp.Deptno FROM Emp;

DEPTNO

----------

40

Приклад

Покажіть імена працівників додаючи позначення * до працівників, які працюють в департаменті 10.

Для вирішення цієї проблеми використовують оператор UNION, який розглядає окремо два випадки - працівників, які працюють в департаменті 10 і які не працюють.

SELECT Emp.Ename||'*' FROM Emp

WHERE Emp.Deptno = 10

UNION

SELECT Emp.Ename FROM Emp

WHERE Emp.Deptno <> 10

ORDER BY 1;

EMP.ENAME||'*'

--------------

ADAMS

ALLEN

BLAKE

CLARK*

FORD

JAMES

JONES

KING*

MARTIN

MILLER*

SCOTT

SMITH

TURNER

WARD

Для того, щоб використовувати алгебраїчні оператори, кількість і типи даних стовпців повинен бути такі самі.

Згідно Standard, результуюча таблиця не містить імен стовпців. Однак Oracle використовує вирази з першого запиту SELECT як мітки стовпців коли відображають вміст результуючої таблиці.

Оператор ORDER BY може бути тільки в кінці цілого запиту. В операторі ORDER BY ми можемо посилатися до результуючих стовпців, використовуючи їх послідовні номери 1,2,...

2 Запити, які залучають кілька таблиць (об'єднання таблиць)

Дані котрі, отримують з бази даних можуть міститися в кількох таблицях. Дані з кількох таблиць пов'язуються між собою зв'язками між рядками зв'язком зовнішній ключ -> первинний ключ. В таких випадках і первинний і зовнішній ключ мають те саме ім'я.. Щоб їх розрізнити використовується конструкція, яка йде попереду імені стовпця таблиці наприклад Emp.Deptno. Кажуть, що уточнюють ім'я стовпця іменем таблиці.

Приклад

Список усіх працівників і для кожного вказати назву департаменту.

SELECT Emp.Empno, Emp.Ename, Dept.Dname

FROM Emp, Dept

WHERE Emp.Deptno = Dept.Deptno;

EMPNO ENAME DNAME

---------- ---------- --------------

7369 SMITH RESEARCH

7499 ALLEN SALES

7521 WARD SALES

7566 JONES RESEARCH

7654 MARTIN SALES

7698 BLAKE SALES

7782 CLARK ACCOUNTING

7788 SCOTT RESEARCH

7839 KING ACCOUNTING

7844 TURNER SALES

7876 ADAMS RESEARCH

7900 JAMES SALES

7902 FORD RESEARCH

7934 MILLER ACCOUNTING

Кожен рядок працівника посилається на рядок з таблиці Dept - це узгоджується через значення Deptno value.

Твердження Emp.Deptno=Dept.Deptno в операторі SELECT поданому вище називають об'єднуючим твердженням (join predicate), на відміну від інших тверджень, які називають обмежувальним твердженнями (restricting predicates таким як:

Sal>1000

або

Loc='Warszawa'

Давайте підкреслимо важливість об'єднуючої умови. Якщо ми не включили її в запиті, ми повинні отримати множину всіх можливих комбінацій рядків від зв'язаних таблиць - не тільки тих, який пов'язаний одна з одною загальною характеристикою , як наприклад номер департаменту від згаданого вище запиту. Результат такого повного об'єднання таблиць називають Декартовим добуток цих таблиць.

Оператори об'єдання

В Standard і Oracle від версії 9i, об'єднуючу умову також можна написати в операторі FROM як частину об'єднуючих операторів. Є кілька об'єднуючих операторів. Для запитів можливі наступні варіанти:

SELECT Emp.Empno, Emp.Ename, Dept.Dname

FROM Emp JOIN Dept ON Emp.Deptno = Dept.Deptno;

(ця форма включає оператор JOIN) або коли об'єднуючий стовпчик іменується так само.

SELECT Emp.Empno, Emp.Ename, Dname

FROM Emp JOIN Dept USING (Deptno);

або скорочено

SELECT Empno, Ename, Dname

FROM Emp NATURAL JOIN Dept;

В останньому випадку об'єднуючий стовпчик має те саме ім'я в обидвох таблицях.

Само- об'єдання

Використовуючи зв'язокзовнішній ключ -> первинний ключ можемо здійснити об'єднати таблицю з нею ж . В цьому випадку одна таблиця виступає у двох ролях, відмічаючи псевдонімом ім'я таблиці в операторі FROM.

Приклад

Список усіх працівників і імен їх менеджерів.

SELECT Empl.Ename, Man.Ename AS Mgr

FROM Emp Empl JOIN Emp Man ON Empl.Mgr = Man.Empno;

ENAME MGR

---------- ----------

SMITH FORD

ALLEN BLAKE

WARD BLAKE

JONES KING

MARTIN BLAKE

BLAKE KING

CLARK KING

SCOTT JONES

TURNER BLAKE

ADAMS SCOTT

JAMES BLAKE

FORD JONES

MILLER CLARK

Псевдонім Empl представляє тут рядок працівника, а псевдонім Man представляє рядок його менеджера забезпечуючи зв'язок між ними умовою у формі рівності:

Empl.Mgr = Man.Empno

Рядок з іменем працівника пов'язаний з точно одним рядком тієї ж таблиці - рядок з іменем менеджера визначений через ідентифікатор Empl.Mgr. Більш звичним і часто використовуваним об'єднання такого типу, яке ґрунтується на зв'язку зовнішній ключ -> первинний ключ . В загальному об'єднуюча умова двох (або більше таблиць) може бути довільна. У випадку нашого прикладу бази даних є одне об'єднання, яке має різні властивості. Продемонструємо це в наступному прикладі:

Приклад

Список всіх працівників з їх рангом окладу.

SELECT e.Empno, e.Ename, s.Grade

FROM Emp e, Salgrade s

WHERE e.Sal BETWEEN s.Losal AND s.Hisal;

EMPNO ENAME GRADE

---------- ---------- ----------

7369 SMITH 1

7876 ADAMS 1

7900 JAMES 1

7521 WARD 2

7654 MARTIN 2

7934 MILLER 2

7499 ALLEN 3

7844 TURNER 3

7566 JONES 4

7698 BLAKE 4

7782 CLARK 4

7788 SCOTT 4

7902 FORD 4

7839 KING 5

Зауважимо, що це є стовпчик Sal з таблиці Emp і стовпчики Losal і Hisal. Ми допускаємо, що закритий інтервал значень [Losal, Hisal] покриває діапазон окладів працівників. Рядок працівника є пов'язаний одним рядком таблиці Salgrade.

Правила обчислення оператора SELECT з об'єднанням:

Якщо запит містить алгебраїчні оператори UNION, INTERSECT і MINUS, повторити кроки 2-7 для їх аргументів.

Обчислити таблицю описану в операторі FROM. Застосувати оператор JOIN щоб їх показати. Розглянути всі результуючі рядки.

Застосувати умову WHERE для цих рядків. Зберегти тільки ті, які True для умови, вилучити ті рядки для яких умова є False або Null.

Для кожного збереженого рядка, обчислюєтться вираз з оператора SELECT.

Якщо є DISTINCT після SELECT, тоді вилучаться всі копії з результуючих рядків.

Виконаються алгебраїчні оператори, якщо вони є.

виконається оператор ORDER BY (сортування рядків), якщо він є.

3 Підсумкові запити

Дані з однієї або більше таблиць можуть бути просумовані з використанням функцій агрегування. Як результат, ми отримуємо один рядок. Семантика запису така:

COUNT()

Кількість значень в стовпці.

MAX()

Максимальне значення стовпця.

MIN()

Мінімальне значення стовпця.

SUM()

Сума значень стовпця.

AVG()

Середнє значення стовпця.

В Oracle є також додаткові функції: STDDEV - стандартне відхилення і VARIANCE - варіація.

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

У випадку функції COUNT, може бути використаний символ * як аргумент. COUNT(*) означає: підрахунок усіх рядків, які визначає умова в операторі WHERE.

Якщо значення виразу Null тоді конкретне значення не буде включене при обчисленні значення функції.

Приклад

Показати кількість працівників у всій компанії.

SELECT COUNT(*) AS "Number of company employees"

FROM Emp;

Number of company employees

----------------------

14

Приклад

Показати статистику по окладах всіх працівників, які працюють в департаменті Sales

SELECT MIN(e.Sal) AS "Min salary",

MAX(e.Sal) AS "Max salary",

MAX(e.Sal)- MIN(e.Sal) AS "Diff in salaries",

AVG(e.Sal) AS "Avg salary"

FROM Emp e JOIN Dept d ON e.Deptno = d.Deptno

WHERE d.Dname = 'SALES';

Min salary Max salary Diff in salaries Avg salary

----------- ----------- ------------- ------------

950 2850 1900 1566.66667

Якщо ми хочемо включити псевдо-значення NULL, коли обчислюються агреговані значення, ми використовуємо функцію NVL. Наступний запит обчилює середнє комісійне значення, який інтерпретує NULL як 0.

SELECT AVG(NVL(e.comm,0)) "Average provision"

FROM Emp e;

Average provision

----------------

157.142857

4 Групування запитів

Оператор GROUP BY розділяє рядки на групи і обчислює підсумкові функції в середині цих груп.

SELECT … FROM … WHERE …

GROUP BY expression,...

[HAVING condition]

Приклад

Поділіть працівників на групи згідно номера департаменту і тоді для кожної групи обчислити кількість і сумарний оклад.

Версія 1 (без виводу назв департаментів)

SELECT e.Deptno Id, COUNT(*) Number, SUM(e.Sal) Sum

FROM Emp e

GROUP BY e.Deptno;

ID NUMBER SUM

---------- ---------- ----------

10 3 8750

20 5 10875

30 6 9400

Версія 2 (з виводом назв департаментів)

SELECT d.Deptno Id, d.Dname Dep_name, COUNT(*) Number, SUM(e.Sal) Sum

FROM Dept d JOIN Emp e ON d.Deptno = e.Deptno

GROUP BY d.Deptno, d.Dname;

ID DEP_NAME NUMBER SUM

---------- -------------- ---------- ----------

10 ACCOUNTING 3 8750

20 RESEARCH 5 10875

30 SALES 6 9400

В другому випадку здійснюється групування після об'єднання двох таблиць Dept і Emp.

Стовчики з яких створюються групи, називають згруповані стовпці( grouping columns). В першому випадку це є Dept.Deptno і в другому випадку це Dept.Deptno і Dept.Dname.

В наступному прикладі є два стовпця e.Deptno і e.Job в операторі GROUP BY. Кожна група містить значення e.Deptno і e.Job, ми сумуємо оклади працівників в середині цих груп.

SELECT e.Deptno, e.Job, SUM(e.Sal)

FROM Emp e

GROUP BY e.Deptno, e.Job;

DEPTNO JOB SUM(SAL)

---------- --------- ----------

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

20 ANALYST 6000

20 CLERK 1900

20 MANAGER 2975

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 5600

Зауважте, що протягом процесу групування, дані сортуються за значенням групуючого стовпця. Ми не можемо припустити, що так буде завжди. Тому що групування може відбуватися іншим шляхом, наприклад, з використанням хешуванням замість сортування.

Ми можемо обмежити показ груп створенням умов для груп. Наприклад, обмежити, показ груп департаментів в яких менше 5 працівників, додаванням наступної умови HAVING:

HAVING COUNT(*)>=5

Отримуємо оператор:

SELECT e.Deptno Id, COUNT(*) Number, SUM(e.Sal) Sum

FROM Emp e

GROUP BY e.Deptno

HAVING COUNT(*)>=5;

ID NUMBER SUM

---------- ---------- ----------

20 5 10875

30 6 9400

В порівнянні з попереднім запитом без оператора HAVING , результат не містив одного рядка з NUMBER = 3:

ID NUMBER SUMA

---------- ---------- ----------

10 3 8750

Ми маємо пам'ятати, що протягом виконання групуючого запиту, спочатку умова WHERE використовується для обмеження. Наступним обмежені рядки групуються і кінцева умова HAVING використвоуєтьмя для обмеження рядків при виводі груп.

Ми маємо пам'ятати про обмеження в операторах SELECT з операторами GROUP BY і HAVING.

Обмеження для запитів групування

Оператор GROUP BY list може містити ім'я тільки одного стовпця( в Oracle будь-якого виразу).

Елементи SELECT , HAVING і ORDER BY можуть бути:

константами,

підсумковими функціями,

групуючим стовпцем (в операторі GROUP BY ),

виразами, які включають елементи (1)-(3)

SQL робить виняток при обробці псевдо-значення Null : два рядки які містять ті самі значення в згрупованих стовпцях, включають Null, при попаданні в ту саму групу.

Семантика запитів з операторами GROUP BY і HAVING є наступною:

Правила для виконання групування запитів

Якщо запит містить виклик операторів UNION, INTERSECT і EXCEPT, повторити кроки 2-7 для їх аргументів.

Обчислити таблиці в FROM. Застосувати оператор JOIN. Розглянути всі результуючі рядки.

Застосувати умову WHERE до всіх цих рядків. Зберегти ті рядки, які є True для цієї умови. Вилучити всі інші, тобто які є False або Null.

Поділити збережені рядки по групах згідно специфікації заданої в GROUP BY.

Застосувати умову HAVING до всіх цих груп. Зберегти ті рядки, які є True для цієї умови. Вилучити всі інші, тобто які є False або Null.

Для кожної збереженої групи, обчислити вираз поданий в SELECT.

Якщо є DISTINCT після SELECT, тоді вилучаться всі копії з результуючих рядків.

Виконаються алгебраїчні оператори, якщо вони є.

Виконується ORDER BY(сортування рядків), якщо цей оператор присутній.

5 Підзапити

Ми не будемо обговорювати важливу властивість, яка є в мовах програмування, тобто композицію операторів. Є звичним в структурному підході застосовувати композицію операторів для вирішення проблеми. Якщо ми використовуємо цей метод, ми поділяємо проблему на менші частини, вирішуємо тоді рішення їх відповідно компонуємо, щоб вирішити основну проблему.

SQL означає Мова Структурованих Запитів ( Structured Query Language). В середині операторів WHERE і HAVING, але тільки в SELECT і FROM ми можемо використовувати підзапити, які мають таку саму форму як запити (але тільки закриті в круглі дужки). В під запиті можуть бути аргументами твердження з: =, <, <=, >, >=, <>, IN, NOT IN. Це є обмеження для результату підзапиту. У випадку тверждень: =, <, <=, >, >=, <> і лівого аргументу IN і NOT IN під запит може повернути тільки одне значення. У випадку правих аргументів твержень IN і NOT IN під запит може повернути список значень. (В попередній версії Standard і Oracle були додаткові обмеження для під запиту на входження тільки правого аргумента для реляційних тверджень).

В Standard не можна використовувати оператори ORDER BY і UNION в підзапитах. В Oracle оператори UNION, MINUS і INTERSECT можуть бути в підзапитах.

Ім'я стовпця, який є в основному запиті, можна використати в підзапиті. Такий під запит називають корельованим (correlated). Інші називають некорельовані ( uncorrelated).

Результат некорельованих під запитів не залежить від рядків з основного запиту. Результат корельованого під запиту може залежати від рядків з основного запиту- може бути різний для різних рядків.

Приклад

Список працівників, які отримують більше серед всіх решта.

Щоб виконати цей запит, спочатку знайдемо найбільше значення Sal, використовуючи запит:

SELECT Max(e.Sal) FROM Emp e;

Цей запит можна використати як підзапит в операторі WHERE, де будуть порівнюватися оклади працівників з найбільшим окладом. Як результат ми готові знайти тих працівників, чий оклад є рівний максимальному.

SELECT e.Ename, e.Sal

FROM Emp e

WHERE e.Sal = (SELECT Max(f.Sal) FROM Emp f);

ENAME SAL

---------- ----------

KING 5000

Може бути більше ніж один підзапит в операторі WHERE.

Приклад

Список всіх працівників, хто працює на тій самій посаді як працівник 7369 і оклад якого більший ніж працівника 7876.

Проблема вирішується наступним запитом:

SELECT e.Ename, e.Job

FROM Emp e

WHERE e.Job = (SELECT f.Job FROM Emp f WHERE f.Empno = 7369)

AND e.Sal > (SELECT g.Sal FROM Emp g WHERE g.Empno = 7876);

ENAME JOB

---------- ---------

MILLER CLERK

Коли підзапит повертає більше ніж одне значення, ми можемо використати оператор IN замість =.

Приклад

Список департаментів, де працюють CLERK.

Вирішують проблему наступним запитом.

SELECT d.Dname

FROM Dept d

WHERE d.Deptno IN (SELECT e.Deptno FROM Emp e WHERE e.Job = 'CLERK');

DNAME

--------------

ACCOUNTING

RESEARCH

SALES

Ми маємо досвід з деяких задач, коли використовують оператор NOT IN якщо результат під запиту містив NULL, тому що не було значень для яких можна встановити щось інше ніж NULL. Наступний запит обчислює працівників які не є менеджерами інших.:

SELECT Empl.Ename

FROM Emp Empl

WHERE Empl.Empno NOT IN (SELECT Sub.Mgr FROM Emp Sub);

І отримали пусту таблицю як результат. Це легше компенсувати, тяжче забезпечити щоб Null не зустрічався в результат підзапиту. Є достатнім додати додаткову умову в WHERE підзапиту.

SELECT Empl.Ename

FROM Emp Empl

WHERE Empl.Empno NOT IN (SELECT Sub.Mgr FROM Emp Sub

WHERE Sub.Mgr IS NOT NULL);

Як ви могли бачити, результат корельованого під запиту може залежати від значень, які є в рядках залучених під час викоання основного запиту.

Приклад

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

В основному запиті ми використовуємо під запит, який шукає максимальний оклад працівників в цьому департаменті:

SELECT Max(e.Sal)

FROM Emp e

WHERE e.Deptno= <Deptno specified in the main query>

і повний запит:

SELECT e.Deptno, e.Ename, e.Sal

FROM Emp e

WHERE e.Sal = (SELECT Max(f.Sal)FROM Emp f WHERE f.Deptno= a.Deptno);

DEPTNO ENAME SAL

---------- ---------- ----------

30 BLAKE 2850

20 SCOTT 3000

10 KING 5000

20 FORD 3000

Оператори SOME, ANY і ALL

Порівняння тверджень можна комбінувати з ключовими словами : SOME (ANY) - читають "для деяких" і ALL - читають "для всіх" або " для кожного", отримуючи композицію, де правий аргумент може бути списком виразів або підзапитом в круглих дужках. Синтаксис є наступним:

expression comparison_operator [ANY|SOME|ALL][expression_list|(subquery)]

Приклад

10000 >= ALL (SELECT Sal FROM Emp)

який може прочитати так: "10000 більше або рівне окладам кожного працівника"

1000 >= SOME (SELECT Sal FROM Emp)

який може прочитати так: "10000 більше або рівне окладу деяких працівників"

Оператори EXISTS і NOT EXISTS

Є два твердження EXISTS і NOT EXISTS, які контролюють чи підзапит містить непустий результат або один пустий. Наприклад, наступна умова перевіряє чи останній працівник працює в департаменті 10:

EXISTS(SELECT 'x' FROM Emp WHERE Deptno= 10)

Істинність цієї умови не залежить від вмісту оператора SELECT.

Приклад

Список працівників, які не мають працівників.

Використовуємо твердження NOT EXISTS і корельований підзапит:

SELECT DISTINCT d.Dname

FROM Dept d

WHERE NOT EXISTS (SELECT 'x' FROM Emp e WHERE e.Deptno = d.Deptno);

DNAME

--------------

OPERATIONS

Використовуючи твердження NOT EXISTS, ми можемо вирішити задачу знаходження працівників, які не є менеджерами інших - тут не буде проблеми з псевдо-значенням NULL в результаті під запиту.

SELECT e.Empno, e.Ename, e.Job, e.Deptno

FROM Emp e

WHERE NOT EXISTS (SELECT 'x'

FROM Emp f

WHERE f.Mgr = e.Empno);

EMPNO ENAME JOB DEPTNO

---------- ---------- --------- ----------

7369 SMITH CLERK 20

7499 ALLEN SALESMAN 30

7521 WARD SALESMAN 30

7654 MARTIN SALESMAN 30

7844 TURNER SALESMAN 30

7876 ADAMS CLERK 20

7900 JAMES CLERK 30

7934 MILLER CLERK 10

Твердження EXISTS і NOT EXISTS predicates узгоджуються прямо до квантора існування (existential quantifier) і квантора всезагальності (universal quantifier) з запереченням у вкладеній формулі, відповідно - змінна рядка підзапиту буде обмежувати квантор. Вони забезпечують потужний інструментарій для вирішення складних задач через використання запитів SQL. В Standard є також твердження:

UNIQUE (subquery)

котре перевіряє чи не повторюються рядки в результаті під запиту.

Приклад

Твердження

UNIQUE (SELECT Ename FROM Emp)

дає значення True, коли ім'я працівника в таблиці Emp повторюється, інакше дає значення False.

Підзапити в командах UPDATE і DELETE

Підзапити зазвичай є компонентами умов команд WHERE і HAVING . Нагадуємо, що WHERE може бути також в командах UPDATE і DELETE.

Приклад

Збільшити оклад всіх працівників. які працюють в Даласі.

UPDATE Emp e

SET e.Sal = e.Sal * 1.1

WHERE 'DALLAS' = (SELECT d.Loc

FROM Dept d

WHERE e.Deptno = d.Deptno);

Підзапити можуть бути також в інших місцях ніж умова. Нижче розглянемо такі ситуації.

Підзапити в розділі FROM команди SELECT

Example

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

SELECT a.Deptno "Department",

Trunc(100*a.Number_Empl/b.Number_Empl,1)AS "%Employees",

Trunc(100*a.Salary_Sum/b.Salary_Sum,1) AS "%Salaries"

FROM (SELECT Deptno, COUNT(*) AS Number_Empl, SUM(Sal) AS Salary_Sum

FROM Emp

GROUP BY Deptno) a,

(SELECT COUNT(*) AS Number_Empl, SUM(Sal) AS Salary_Sum

FROM Emp) b;

Department %Employees %Salaries

---------- ------------ ----------

10 21.4 30.1

20 35.7 37.4

30 42.8 32.3

Підзапити в розділі SELECT команди SELECT

Приклад

Для кожного департаменту обчислити скільки працівників у ньому працює.

SELECT d.Deptno "Department",

(SELECT COUNT(*) FROM Emp e WHERE e.Deptno=d.Deptno) AS "#Employees"

FROM Dept d;

Department #Employees

---------- ------------

10 3

20 5

30 6

40 0

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

Підзапити в частині AS команди CREATE TABLE (тільки в Oracle)

Приклад

Скопіювати інформацію про клерків і їх розташування в нову таблицю Clerks.

CREATE TABLE Clerks (Empno, Ename, Sal)

AS SELECT e.Empno, e.Ename, e.Sal

FROM Emp e

WHERE e.Job = 'CLERK';

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

Підзапити в команді INSERT

Приклад

Вставити в тимчасову таблицю усіх працівників, які працюють протягом останніх 10 днів.

INSERT INTO Emp_new

SELECT *

FROM Emp

WHERE Sysdate - Hiredate < 10;

В Standard підзапити записують завжди в круглих дужках (в Oracle не завжди).

6 Зовнішні об'єднання

Зовнішнє об'єдання розширює результат одиночного об'єдання ( inner join) з рядками одної таблиці, які не відповідають рядкам з іншої таблиці.

Приклад

Список всіх працівників з назвою департаменту, де вони працюють.

Допускаємо, що стовпчик Emp.Deptno може містити значення NULL і тоді вставимо в таблицю Emp нового працівника Kowalski без пов'язування з будь-яким відділом.

Запит:

SELECT e.Empno, e.Ename, d.Dname

FROM Emp e LEFT JOIN Dept d ON e.Deptno = d.Deptno;

повертає інформацію про працівників і їх департаменти, у цьому випадку працівник, який не відноситься ні до якого департаменту отримають значення Null value в стовпці d.Dname - в Oracle, при друці буде відображатися пустим символьним рядком.

EMPNO ENAME DNAME

---------- ---------- --------------

7369 SMITH RESEARCH

7499 ALLEN SALES

7521 WARD SALES

7566 JONES RESEARCH

7654 MARTIN SALES

7698 BLAKE SALES

7782 CLARK ACCOUNTING

7788 SCOTT RESEARCH

7839 KING ACCOUNTING

7844 TURNER SALES

7876 ADAMS RESEARCH

7900 JAMES SALES

7902 FORD RESEARCH

7934 MILLER ACCOUNTING

7799 KOWALSKI

Приклад

Список всіх працівників з просумованими окладами.

Можемо використати зовнішнє об'єднання RIGHT JOIN, який є симетричний до LEFT JOIN.

SELECT d.Deptno, d.Dname, SUM(e.Sal)

FROM Emp e RIGHT JOIN Dept d ON e.Deptno = d.Deptno

GROUP BY d.Deptno, d.Dname;

DEPTNO DNAME SUM(SAL)

------ -------------- ----------

10 ACCOUNTING 8750

20 RESEARCH 1087

30 SALES 9400

40 OPERATIONS

Якщо департамент не має працівників, то просумується і отримає значення Null тільки. Згідно правил результат Null, відобразиться пустим рядком. Якщо ми хочемо отримати значення нуль, ми маємо замінити вираз SUM(Sal) як аргумент функції NVL.

NVL(Sum(Sal),0)

Зауважмо, що подібний але не такий ефект буде, коли використати в Oracle оператор UNION:

SELECT d.Deptno, d.Dname, TO_CHAR(SUM(e.Sal))

FROM Emp e JOIN Dept d ON e.Deptno = d.Deptno

GROUP BY d.Deptno, d.Dname

UNION

SELECT d.Deptno, d.Dname, NULL

FROM Dept d

WHERE NOT EXISTS (SELECT 'x' FROM Emp f WHERE f.Deptno = d.Deptno);

DEPTNO DNAME TO_CHAR(SUM(SAL))

------ ------------- --------------------

10 ACCOUNTING 8750

20 RESEARCH 10875

30 SALES 9400

40 OPERATIONS

Інші оператори об'єднання

Одиничні об'єдання таблиць будемо позначати (T, U ):

Перехресне об'єдання (Cross join) T CROSS JOIN U - Декартовий добуток(Cartesian product) - комбінація усіх рядків:

SELECT * FROM T CROSS JOIN U;

is the same as:

SELECT * FROM T,U;

Повне зовнішнє обєдання (Full outer join) T FULL JOIN U - результат об'єдання правого і лівого зовнішніх об'єданань (без повтору рядків). Приклад запиту:

SELECT e.Ename, d.Dname

FROM Emp e FULL JOIN Dept d ON e.Deptno=d.Deptno;

Поєднує ім'я працівника з їхнім департаментом, показує також працівників які не відносяться до будь-якого департаменту і також дпартаменти, які не містять працівників.

В наступній лекції ми продовжимо обговорення конструкцій мови SQL.

7 Висновки

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

Размещено на Allbest.ru