Смекни!
smekni.com

Організація баз даних та знань (стр. 11 из 26)

SELECT TOVAR...TNAME, TOVAR.KOL,

TOVAR.PRICE, "="AS EQU,

TOVAR.KOL*TOVAR.PRICE AS SUMMA

FROM TOVAR;

У результаті одержимо таблицю з колонками, яких не було у вихідній таблиці TOVAR:

TNAME KOL PRICE EQU SUMMA

Болт 10 100 = 1000

Гайка 20 200 = 4000

Гвинт 30 300 = 9000

Приклад. Упорядкування результатів запиту (ключове слово ORDER BY…):

SELECT PD...PNUM, PD.DNUM, PD.VOLUME

FROM PD

ORDER BY DNUM; В результаті одержимо наступну таблицю, впорядковану по полю DNUM:

PNUM DNUM VOLUME

1 1 100

2 1 150

3 1 1000

1 2 200

2 2 250

1 3 300

Приклад. Упорядкування результатів запиту по декількох полях зі зростанням або убуванням (ключові слова ASC, DESC):

SELECT PD.PNUM, PD.DNUM, PD.VOLUME

FROM PD

ORDER BY DNUM ASC, VOLUME DESC;

У результаті одержимо таблицю, у якій рядки йдуть у порядку зростання значення поля DNUM, а рядки з однаковим значенням DNUM ідуть у порядку убування значення поля

VOLUME:

PNUM DNUM VOLUME

3 1 1000 2 1 150

1 1 100

2 2 250

1 2 200

1 3 300

Якщо явно не зазначені ключові слова ASC або DESC, то за замовчуванням береться впорядкування по зростанню (ASC).

Відбір даних із декількох таблиць

Приклад. Природне з'єднання таблиць (спосіб 1 - явна вказівка умов з'єднання):

SELECT P.PNUM, P.PNAME, PD.DNUM, PD.VOLUME

FROM P, PD

WHERE P.PNUM = PD.PNUM;

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

PNUM PNAME DNUM VOLUME

1 Іванов 1 100

1 Іванов 2 200

1 Іванов 3 300

2 Петров 1 150

2 Петров 2 250

3 Сидоров 1 1000

Таблиці, що з'єднуються, перелічені у розділі FROM оператора, умова з'єднання наведена у розділі WHERE. Розділ WHERE, крім умови з'єднання таблиць, може також містити й умови відбору рядків.

Приклад. Природне з'єднання таблиць (спосіб 2 - ключові слова JOIN…USING…):

SELECT PNUM, P.PNAME, PD.DNUM,

PD.VOLUME

FROM P JOIN PD USING PNUM;

Ключове слово USING дозволяє явно вказати, за якими із загальних колонок таблиць буде вироблятися з'єднання.

Приклад. Природне з'єднання таблиць (спосіб 3 - ключове слово NATURAL JOIN):

SELECT P.PNUM, P.PNAME, PD.DNUM,

PD.VOLUME

FROM P NATURAL JOIN PD;

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

SELECT P.PNAME, D.DNAME, PD.VOLUME FROM P NATURAL JOIN PD NATURAL JOIN D; У результаті одержимо таку таблицю:

PNAME DNAME VOLUME
Іванов Болт 100
Іванов Гайка 200
Іванов Гвинт 300
Петров Болт 150
Петров Гайка 250
Сидоров Болт 1000

Приклад. Прямий добуток таблиць:

SELECT P.PNUM, P.PNAME, D.DNUM,

D.DNAME

FROM P, D;

У результаті одержимо таку таблицю:

PNUM PNAME DNUM DNAME

1 Іванов 1 Болт

1 Іванов 2 Гайка 1 Іванов 3 Гвинт

2 Петров 1 Болт

2 Петров 2 Гайка 2 Петров 3 Гвинт

3 Сидоров 1 Болт

3 Сидоров 2 Гайка

3 Сидоров 3 Гвинт

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

Приклад. З'єднання таблиць за довільною умовою. Розглянемо таблиці постачальників і деталей, яким присвоєний деякий статус.

Таблиця 2.1 – Відношення P (Постачальники)

PNUM PNAME PSTATUS
1 Іванов 4
2 Петров 1

3 Сидоров

Таблиця 2.2 – Відношення D (Деталі)

2
DNUM DNAME DSTATUS
1 Болт 3
2 Гайка 2
3 Гвинт 1

Відповідь на запитання "які постачальники мають право поставляти певні деталі?" дає такий запит:

SELECT P.PNUM, P.PNAME,

P.PSTATUS,

D.DNUM, D.DNAME, D.DSTATUS

FROM P, D

WHERE P.PSTATUS >= D.DSTATUS; У результаті одержимо таку таблицю:

PNUM PNAME PSTATUS DNUM DNAME DSTATUS

1 Іванов 4 1 Болт 3

1 Іванов 4 2 Гайка 2

1 Іванов 4 3 Гвинт 1

2 Петров 1 3 Гвинт 1 3 Сидоров 2 2 Гайка 2

3 Сидоров 2 3 Гвинт 1

2.4 Використання імен кореляції (аліасів, псевдонімів)

Іноді доводиться виконувати запити, у яких таблиця з'єднується сама із собою, або одна таблиця з'єднується двічі з іншою таблицею. При цьому використовуються імена кореляції (аліаси, псевдоніми), які дозволяють розрізняти копії та таблиці-оригінали. Імена кореляції вводяться у розділі FROM і йдуть через пробіл після імені таблиці. Імена кореляції повинні використовуватися як префікс перед ім'ям стовпця й відокремлюються від імені стовпця крапкою. Якщо у запиті вказуються ті самі поля з різних екземплярів однієї таблиці, вони повинні бути перейменовані для усунення неоднозначності в іменуваннях колонок результуючої таблиці. Визначення імені кореляції діє тільки під час виконання запиту.

Приклад. Відібрати всі пари постачальників таким чином, щоб перший постачальник у парі мав статус, більший од статусу другого постачальника:

SELECT P1.PNAME AS PNAME1,

P1.PSTATUS AS PSTATUS1,

P2.PNAME AS PNAME2,

P2.PSTATUS AS PSTATUS2

FROM P P1, P P2

WHERE P1.PSTATUS1 > P2.PSTATUS2;

В результаті одержимо наступну таблицю:

PNAME1 PSTATUS1 PNAME2 PSTATUS2

Іванов 4 Петров 1

Іванов 4 Сидоров 2

Сидоров 2 Петров 1

Приклад. Розглянемо ситуацію, коли деякі постачальники (назвемо їх контрагенти) можуть виступати як постачальники деталей, так як і одержувачі. Таблиці, що зберігають дані можуть мати такий вигляд:

Таблиця 2.3 – Відношення CONTRAGENTS

Номер контрагента Ім’я контрагента NUM NAME

1 Іванов

2 Петров

3 Сидоров

Таблиця 2.4 – Відношення DETAILS

Номер деталі Найменування деталі

DNUM DNAME
1 Болт
2 Гайка

3

Таблиця 2.5 – Відношення CD (Поставки)

Гвинт

Номер Номер

постачальника одержувача

PNUM CNUM

Номер деталі

DNUM

Кількість, що поставляється,

VOLUME

1 2 1 100
1 3 2 200
1 3 3 300
2 3 1 150
2 3 2 250
3 1 1 1000

У таблиці CD поля PNUM й CNUM є зовнішніми ключами, що посилаються на потенційний ключ NUM у таблиці CONTRAGENTS.

Відповідь на запитання "хто, кому, що, в якій кількості поставляє" дається таким запитом:

SELECT P.NAME AS PNAME,

C.NAME AS CNAME,

DETAILS.DNAME, CD.VOLUME

FROM CONTRAGENTS P, CONTRAGENTS C, DETAILS, CD

WHERE P.NUM = CD.PNUM AND

C.NUM = CD.CNUM AND

D.DNUM = CD.DNUM;

У результаті одержимо таку таблицю:

PNAME CNAME DNAME VOLUME

Іванов Петров Болт 100

Іванов Сидоров Гайка 200
Іванов Сидоров Гвинт 300
Петров Сидоров Болт 150
Петров Сидоров Гайка 250
Сидоров Іванов Болт 1000

Цей самий запит може бути виражений дуже великою кількістю способів, наприклад, так:

SELECT P.NAME AS PNAME,

C.NAME AS CNAME,

DETAILS.DNAME, CD.VOLUME

FROM CONTRAGENTS P, CONTRAGENTS C, DETAILS NATURAL JOIN CD

WHERE P.NUM = CD.PNUM AND C.NUM = CD.CNUM;

2.5 Вбудовані функції

Арифметичні функції

SQL підтримує повний набір арифметичних операцій і математичних функцій для побудови арифметичних виражень над колонками БД (+, -, *, /, ABS, LN, SQRT і т.д.). Перелік основних вбудованих математичних функцій поданий нижче:

ABS(X) Повертає абсолютне значення числа Х;
ACOS(X) Повертає арккосинус числа Х;
ASIN(X) Повертає арксинус числа Х;
ATAN(X) Повертає арктангенс числа Х;
COS(X) Повертає косинус числа Х;
EXP(X) Повертає експоненту числа Х;
SIGN(X) Повертає -1, якщо Х<0,0, якщо Х=0, +1, якщо Х>0;
LN(X) Повертає натуральний логарифм числа Х;

MOD(X,Y) Повертає залишок від розподілу Х на Y;

CEIL(X) Повертає найменше ціле, більше або рівне Х;

ROUND(X, n) Округляє число Х до числа з n знаками після крапки;

SIN(X) Повертає синус числа Х;
SQRT(X) Повертає квадратний корінь числа Х;
TAN(X) Повертає тангенс числа Х;
FLOOR(X) Повертає найбільше ціле менше або рівне Х;
LOG(А, X) Повертає логарифм числа Х по основі А;
SINH(X) Повертає гіперболічний синус числа Х;

COSH(X) Повертає гіперболічний косинус числа Х;

TANH(X) Повертає гіперболічний тангенс числа Х;

TRANC(X,n) Скорочує число Х до числа з n знаками після десяткової крапки; POWER(A,X) Повертає значення А, зведене в ступінь Х.

Арифметичні вираження необхідні для одержання даних, які безпосередньо не зберігаються в колонках таблиць БД, але значення яких необхідні користувачеві. Припустимо, що вам необхідний список службовців, що показує виплату, що одержав кожен службовець із урахуванням премій і штрафів

Функції для обробки дати

У діалекті SQL є невеликий набір функцій для маніпулювання колонками з типом date. Список основних функцій обробки дати й часу наведений нижче:

SYSDATE Повертає поточну дату й час;

ROUND(D[,F]) - Округлює значення дати D відповідно до заданого шаблона;

TRANC(D[,F]) - Скорочує значення дати D відповідно до заданого шаблона; NEXT_DAY(D,S) - Повертає дату дня, що є першим днем, більше пізнім, ніж поточна дата з назвою S.