Сдам Сам

ПОЛЕЗНОЕ


КАТЕГОРИИ







Приклади використання оператора SELECT





Приклад 1. Створити запит з БД відділу збуту молокозаводу, заданої відношенням ЗАМОВЛЕННЯ (код замовлення, назва замовника, дата замовлення, назва продукції, одиниці виміру, кількість, ціна). Вибрати продукцію, яку замовили 12.01.06 р. та вказати кількість замовленої продукції.

Вихідні дані представлені у таблиці Замовлення.

Оператор вибору має сформувати результуючу таблицю, яка за умовою, вміщує поля [Назва продукції ] та [Кількість]. Такі дані можна використати для планування випуску продукції, тому можна сказати, що задача має практичний сенс, хоча значно спрощена відносно реальної для полегшення сприйняття матеріалу.

Таблиця ЗАМОВЛЕННЯ

Код замовлення Замовник Дата Назва продукції Од_ виміру Кількість Ціна за од., грн.
  “Фуршет” 12.01.06 Молоко 1л., жирн. 2,5% Уп.   2,7
  “Фуршет” 12.01.06 Кефір 0,5 л., жирн. 2,5% Уп   1,2
  “Фуршет” 13.01.06 Кефір 1 л., жирн. 2,5% Уп   3,2
  “Фуршет” 12.01.06 Сметана 0,5л.,жирн. 20% Уп   3,9
  “Сільпо” 12.01.06 Молоко 1л., жирн. 2,5% Уп   2,7
  “Сільпо” 13.01.06 Кефір 0,5 л., жирн. 2,5% Уп   1,2
  “Сільпо” 12.01.06 Сметана 0,2л.,жирн. 15% Уп   2,2
  “Біла” 13.01.06 Кефір 0,5 л., жирн. 2,5% Уп   1,2
  “Біла” 13.01.06 Кефір 1 л., жирн. 2,5% Уп   3,2
  “Біла” 13.01.06 Сметана 0,2л.,жирн. 15% Уп   2,2
  “Біла” 13.01.06 Молоко 1л., жирн. 2,5% Уп   2,7

Отже запит має вигляд:

SELECT [Назва продукції ], [Од_виміру], [Кількість]

FROM ЗАМОВЛЕННЯ

WHERE [Дата] = 12.01.06

В результаті отримаємо таблицю:

Назва продукції Од_виміру Кількість
Молоко 1л., жирн. 2,5% Уп.  
Кефір 0,5 л., жирн. 2,5% Уп  
Сметана 0,5л.,жирн. 20% Уп  
Молоко 1л., жирн. 2,5% Уп  
Сметана 0,2л.,жирн. 15% Уп  

Приклад 2. Використовуючи дані з прикладу 1 вибрати продукцію, жирність якої складає 2,5%.

Текст запиту:

SELECT DISTINCT [Назва продукції ]

FROM ЗАМОВЛЕННЯ

WHERE [Назва продукції ] LAKE %жирність2,5%.

Результат:

Назва продукції
Молоко 1л., жирн. 2,5%
Кефір 0,5 л., жирн. 2,5%
Кефір 1 л., жирн. 2,5%

 

Завдяки слову DISTINCT вилучено повторення рядків результуючої таблиці.

Приклад 3. Використовуючи дані з прикладу1, підрахувати кількість продукції кожного виду, замовленої 12.01.06.

Текст запиту:

SELECT [Назва продукції ], [Од_виміру], Sum[Кількість] As [Загальна кількість]

FROM ЗАМОВЛЕННЯ

WHERE [Дата] = 12.01.06

GROUP BY [Назва продукції ]

Результат запиту має наступний вигляд:

Назва продукції Од_виміру Загальна кількість
Молоко 1л., жирн. 2,5% Уп.  
Кефір 0,5 л., жирн. 2,5% Уп  
Сметана 0,5л.,жирн. 20% Уп  
Сметана 0,2л.,жирн. 15% Уп  

 

Для формування результуючого запиту використали агрегативну функцію Sum, а для заголовок поля сформували використовуючи псевдонім після слова AS.

Приклад 4. Використовуючи дані з прикладу1, підрахувати кількість продукції кожного виду, замовленої на кожну вказану дату.

Текст запиту:

SELECT [Назва продукції ], [Од_виміру], [Дата], Sum[Кількість], As [Загальна кількість]

FROM ЗАМОВЛЕННЯ

GROUP BY [Дата], [Назва продукції ]

Результат запиту має наступний вигляд:

 

Назва продукції Од_виміру Дата Загальна кількість
Молоко 1л., жирн. 2,5% Уп. 12.01.06  
Кефір 0,5 л., жирн. 2,5% Уп 12.01.06  
Сметана 0,5л.,жирн. 20% Уп 12.01.06  
Сметана 0,2л.,жирн. 15% Уп 12.01.06  
Кефір 0,5 л., жирн. 2,5% Уп 13.01.06  
Кефір 1 л., жирн. 2,5% Уп 13.01.06 5 00
Сметана 0,2л.,жирн. 15% Уп 13.01.06  
Молоко 1л., жирн. 2,5% Уп 13.01.06  

Приклад 5. Використовуючи дані з прикладу1, підрахувати на яку суму реалізовано продукції кожного виду на кожну вказану дату.

Текст запиту:

SELECT [Назва продукції ], [Од_виміру], [Дата], Sum([Кількість]*[Ціна]) As [Сума]

FROM ЗАМОВЛЕННЯ

GROUP BY [Дата], [Назва продукції ]

Результат запиту

Назва продукції Од_виміру Дата Сума
Молоко 1л., жирн. 2,5% Уп. 12.01.06  
Кефір 0,5 л., жирн. 2,5% Уп 12.01.06  
Сметана 0,5л.,жирн. 20% Уп 12.01.06  
Сметана 0,2л.,жирн. 15% Уп 12.01.06  
Кефір 0,5 л., жирн. 2,5% Уп 13.01.06  
Кефір 1 л., жирн. 2,5% Уп 13.01.06  
Сметана 0,2л.,жирн. 15% Уп 13.01.06  
Молоко 1л., жирн. 2,5% Уп 13.01.06  

Приклад 6. Використовуючи дані з прикладу 1, визначити яка продукція дала максимальну виручку на кожну вказану дату.

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

Запит1

SELECT [Назва продукції], [Од_виміру], [Дата], Sum([Кількість]*[Ціна]) As [Сума]

FROM ЗАМОВЛЕННЯ

GROUP BY [Дата], [Назва продукції ]

Другий запит допоможе нам з підрахованих сум вибрати найбільші по кожному виду продукції:

SELEKT Max([Сума])

FROM Запит1

Результат виконання запитів

 

Назва продукції Од_виміру Дата Сума
Молоко 1л., жирн. 2,5% Уп. 12.01.06  
Кефір 1 л., жирн. 2,5% Уп 13.01.06  

 

Лекція 13

13.1. Внутрішнє та зовнішнє об‘єднання таблиць

В попередньому параграфі ми розглядали приклади вибірки даних з однієї таблиці. Однак, в реальній базі даних кожна таблиця зберігає дані про об‘єкти одного класу і для наведення відповідності між цими об‘єктами, в межах однієї предметної області, створюють зв‘язки між таблицями. Яким же чином умова зв‘язку між таблицями відображена в мові SQL? В стандарті SQL1 умову зв‘язку кортежів з різних відношень задавали в реченні WHERE, шляхом порівняння значень ключів. Наприклад: З бази даних заданої відношеннями ЗАМОВНИК (код замовника, назва замовника, адреса, реквізити) та ЗАМОВЛЕННЯ (код замовлення, код замовника, дата замовлення, назва продукції, кількість, ціна, сума) вибрати замовників, що замовляли продукцію не пізніше 31.12.2005 р. Текст оператора має наступний вигляд:

SELECT DISTINCT ЗАМОВНИК.назва замовника

FROM ЗАМОВНИК, ЗАМОВЛЕННЯ

WHERE ЗАМОВНИК. Код замовника = ЗАМОВЛЕННЯ.код замовника AND ЗАМОВЛЕННЯ.дата < 31.12.2005

Зв‘язок між таблицями вказано в реченні WHERE ЗАМОВНИК. Код замовника = ЗАМОВЛЕННЯ.код замовника. В цьому випадку до результуючої таблиці потрапляють кортежі, які відповідають умові зчеплення. Для наведеного прикладу такої умови досить. Таке об‘єднання відношень називається внутрішнім. Інакше внутрішнє об‘єднання можна записати наступним чином:

SELECT DISTINCT ЗАМОВНИК.назва замовника

FROM ЗАМОВНИК INNER JOIN ЗАМОВЛЕННЯ ON ЗАМОВНИК. Код замовника = ЗАМОВЛЕННЯ.код замовника

WHERE ЗАМОВЛЕННЯ.дата < 31.12.2005

Тобто, в реченні FROM використовують оператор JOIN з службовим словом INNER. Це зроблено для того, щоб уніфікувати операцію об‘єднання відношень. В такому випадку вид об‘єднання визначається службовими словами. В SQL2 передбачені стандарти, що описують різні варіанти об‘єднання відношень, які на відміну від внутрішнього об‘єднання, називаються зовнішніми. Загалом, в SQL2 синтаксис речення FROM має наступний вигляд:

FROM <перелік вихідних таблиць> | <вираз природного об‘єднання> | <вираз об‘єднання> | <вираз перехресного об‘єднання > | <вираз запита на об‘єднання>,

де <перелік вихідних таблиць>::= <назва_таблиці1> [<синонім таблиці1>] […] [, <назва_таблиціN> [<синонім таблиціN>] ];

<вираз природного об‘єднання>::= <назва_таблиці1> NATURAL { INNER | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER] } JOIN <назва_таблиці2>;

<вираз перехресного об‘єднання >::= <назва_таблиці1> CROSS JOIN <назва_таблиці2>;

<вираз запита на об‘єднання>::= <назва_таблиці1> UNION JOIN

<назва_таблиці2>;

<вираз об‘єднання>::= <назва_таблиці1> {INNER | FULL [OUTER]

| LEFT [OUTER] | RIGHT [OUTER]} JOIN {ON <умова об‘єднання> | [USING (список стовпчиків)]} <назва_таблиці2>;

Слова LEFT/RIGHT використовують у випадках, коли необхідно мати всі записи з однієї таблиці і показати зв‘язані з нею записи іншої таблиці.

Для виділення головної таблиці оператор JOIN доповнюється службовими словами RIGHT або LEFT, які вказують праворуч чи ліворуч від оператора JOIN знаходиться головна таблиця. Наприклад, не всі замовники вже зробили замовлення, хоча, на основі угоди в таблицю ЗАМОВНИК дані про них занесено. Виникає наступна задача – з таблиці ЗАМОВНИК вибрати замовників, які замовляли продукцію у 2005 році, але ще не зробили замовлення у 2006 році.

В мові SQL2, використовуючи оператор JOIN запит має вигляд:

SELECT DISTINCT ЗАМОВНИК.назва замовника

FROM ЗАМОВНИК LEFT JOIN ЗАМОВЛЕННЯ ON

ЗАМОВНИК. Код замовника = ЗАМОВЛЕННЯ. Код замовника

WHERE Замовлення.дата < 31.12.2005)

Слово CROSS використовують, коли необхідно мати у вибірці всі варіанти об‘єднання записів з обох таблиць, тобто кожний рядок з однієї таблиці об‘єднується з кожним рядком другої таблиці. Таке об‘єднання відповідає операції розширеного декартового добутку з реляційної алгебри.

Операція запиту на об‘єднання еквівалентна операції теоретико-множинного об‘єднання реляційної алгебри. При цьому необхідно підтримувати еквівалентність схем вихідних відношень. Запит на об‘єднання виконують за наступною схемою:

SELECT - запит

UNION

SELECT - запит

UNION

SELECT - запит

Всі запити, використовувані в запиті на об‘єднання не повинні містити виразів, тобто, обчислюваних полів.

Вкладені запити

В SQL існує можливість вкладання запитів один в одного. Як правило, внутрішній запит, який називають SubQuery, генерує значення, яке перевіряється в предикаті умови зовнішнього запита (в реченнях WHERE або HAVING). В частині FROM оператора SELECT дозволено примінять синоніми до назви таблиці, якщо при виконанні запита з підзапитом маємо використовувати два екземпляра однієї таблиці – один у запиті, другий у підзапиті. Синоніми створюють з використанням ключового слова AS, яке можна опустити. В якості синонімів використовують літери, літери з цифрами, або сукупність літер коротшу ніж назва таблиці.

FROM ЗАМОВНИК AS R1, ЗАМОВЛЕННЯ AS R2 або

FROM ЗАМОВНИК R1, ЗАМОВЛЕННЯ R2

Розглянемо вкладені запити на прикладі БД відділу збуту молокозаводу.

Приклад 1. В базі даних заданій відношенням Замовлення визначити дату максимального замовлення за кількістю.

SELECT дата, кількість

FROM ЗАМОВЛЕННЯ

WHERE кількість = (SELECT MAX(кількість) FROM ЗАМОВЛЕННЯ)

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

SELECT дата, кількість, кількість - (SELECT AVG(кількість) FROM ЗАМОВЛЕННЯ) AS перевищення

FROM ЗАМОВЛЕННЯ

WHERE кількість > (SELECT AVG(кількість) FROM ЗАМОВЛЕННЯ)

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

SELECT ЗАМОВНИК. назва

FROM ЗАМОВНИК NATURAL INNER JOIN ЗАМОВЛЕННЯ

WHERE ЗАМОВЛЕННЯ.кількість = (SELECT MAX (ЗАМОВЛЕННЯ. кількість) FROM ЗАМОВЛЕННЯ)

За рахунок використання слова NATURAL ми можемо не використовувати вираз порівняння для кодів зв‘язку між таблицями, тому що це слово передбачає наявність у різних таблицях полів для зв‘язку з однаковими назвами.

Разом з підзапитом часто використовують предикат EXISTS або NOT EXISTS. Предикат EXISTS має значення ІСТИНА, коли результат виконання підзапиту не порожній і навпаки, NOT EXISTS має значення ІСТИНА, коли результат виконання підзапиту порожній.

Приклад 4. Вбазі даних, заданій відношеннями ЗАМОВНИК та ЗАМОВЛЕННЯ, вибрати замовників, що не брали на нашому підприємстві молоко у листопаді 2006 року та підрахувати сумарну виручку від реалізації продукції цим замовникам. У вибірку включити тільки тих замовників, для кого сума реалізації перевищує 5000 грн.

SELECT ЗАМОВНИК. Назва замовника, SUM (ЗАМОВЛЕННЯ. Кількість * ЗАМОВЛЕННЯ.Ціна) as Сума

FROM ЗАМОВНИК NATURAL INNER JOIN ЗАМОВЛЕННЯ

WHERE ЗАМОВЛЕННЯ.дата LIKE “%11.2006”

NOT EXISTS

(SELECT ЗАМОВНИК. Назва замовника

FROM ЗАМОВНИК NATURAL INNER JOIN ЗАМОВЛЕННЯ WHERE ЗАМОВЛЕННЯ.Дата LIKE “%11.2006” AND ЗАМОВЛЕННЯ. Назва продукції LAKE “молоко%”)

GROUP BY (Назва замовника)

HAVING Сума > 5000

В стандарті SQL2 оператори порівняння розширено до багатократних порівнянь з використанням ключових слів ANY та ALL. Таке розширення використовують для порівняння вибраного стовпчика таблиці зі стовпчиком даних поверненим підзапитом.

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

Приклад 5. У БД молокозаводу заданій відношеннями ЗАМОВНИК та ЗАМОВЛЕННЯ вибрати назви замовників, які хоча б один раз у 2006 р. взяли будь-яку продукцію більше, ніж на 1000 грн.

SELECT R1.Назва замовника

FROM ЗАМОВНИК R1, ЗАМОВЛЕННЯ R2

WHERE YEAR(R2.Дата) =”2006” AND 1000< ANY

(SELECT (Кількість*Ціна) AS Вартість

FROM ЗАМОВЛЕННЯ R3

WHERE R2.Код замовника =

R3. Код замовника)

Ключове слово ALL вимагає, щоб предикат порівняння мав би значення “Істина” під час порівняння з усіма рядками підзапиту.

Приклад 6. У БД молокозаводу заданій відношеннями ЗАМОВНИК ЗАМОВЛЕННЯ та ПРОДУКЦІЯ вибрати назви замовників, які замовляли весь асортимент продукції у 2006 р.

SELECT DISTINCT R1. назва замовника

FROM ЗАМОВНИК AS R1 NATURAL INNER JOIN ЗАМОВЛЕННЯ AS R2

WHERE YEAR(R2.Дата) =”2006” AND R2. код продукції =

ALL (SELECT R3. код продукції FROM ЗАМОВЛЕННЯ AS R3 NATURAL RIGHT JOIN ПРОДУКЦІЯ WHERE R2. код замовника = R3. код замовника)

В даному запиті відбувається подвійне використання таблиці ЗАМОВЛЕННЯ: в основному запиті для вибірки замовників (синонім R2), які замовляли продукцію у 2006 році і в підзапиті (синонім R3), для виявлення замовлень по всіх видах продукції. Для того, щоб зв‘язати ці два запити в підзапиті використано умову R2. код замовника = R3. код замовника.

Цю задачу можна вирішити використовуючи операцію групування і підзапит у реченні HAVING. В даному рішенні відбувається підрахунок кількості видів продукції, що випускає завод. Аналогічний підрахунок відбувається по замовленнях, які згруповані за замовником. Слово Distinct використовують для того, щоб не враховувати одну й ту ж продукцію по декілька разів.

SELECT DISTINCT R1. назва замовника

FROM ЗАМОВНИК AS R1 NATURAL INNER JOIN ЗАМОВЛЕННЯ AS R2

WHERE YEAR(R2.Дата) =”2006”

GROUP BY (R1. назва замовника)

HAVING COUNT (DISTINCT R2. код продукції)= (SELECT COUNT (R3.код продукції) FROM ПРОДУКЦІЯ AS R3

 

Питання для самоперевірки

1. Які способи об‘єднання таблиць в операторі SELECT ви знаєте?

2. Чим відрізняється зовнішнє об‘єднання таблиць від внутрішнього?

3. Які види зовнішнього об‘єднання таблиць ви знаєте?

4. За якої умови запит на об‘єднання (UNION) буде коректним?

5. Якій операції реляційної алгебри відповідає перехресне об‘єднання таблиць?

6. В яких випадках використовують вкладені запити?

7. В яких реченнях SQL є можливість використання вкладеного запиту?

8. Які допоміжні слова використовують перед вкладеним запитом? Що означає кожне з них?

 

Лекція 14







Что способствует осуществлению желаний? Стопроцентная, непоколебимая уверенность в своем...

ЧТО ПРОИСХОДИТ, КОГДА МЫ ССОРИМСЯ Не понимая различий, существующих между мужчинами и женщинами, очень легко довести дело до ссоры...

Живите по правилу: МАЛО ЛИ ЧТО НА СВЕТЕ СУЩЕСТВУЕТ? Я неслучайно подчеркиваю, что место в голове ограничено, а информации вокруг много, и что ваше право...

Что делает отдел по эксплуатации и сопровождению ИС? Отвечает за сохранность данных (расписания копирования, копирование и пр.)...





Не нашли то, что искали? Воспользуйтесь поиском гугл на сайте:


©2015- 2024 zdamsam.ru Размещенные материалы защищены законодательством РФ.