Материал: Using_MySql,_MS_SQL_Server_and_Oracle

Скачать

Заказать новую работу

Внимание! Если размещение файла нарушает Ваши авторские права, то обязательно сообщите нам

Пример 20: все разновидности запросов на объединение в трёх СУБД

Задача 2.2.10.g: показать информацию по всем пустым комнатам и свободным компьютерам.

Ожидаемый результат 2.2.10.g.

r_id

r_name

c_id

c_room

c_name

3

Пустая комната 1

NULL

NULL

NULL

4

Пустая комната 2

NULL

NULL

NULL

5

Пустая комната 3

NULL

NULL

NULL

NULL

NULL

6

NULL

Свободный компьютер A

NULL

NULL

7

NULL

Свободный компьютер B

NULL

NULL

8

NULL

Свободный компьютер C

-Решение 2.2.10.g: используем полное внешнее объединение с исключе нием. Эта задача является комбинацией задач 2.2.10.c{154} и 2.2.10.e{156}: нужно показать все записи из таблицы rooms, для которых нет соответствия в таблице computers, а также все записи из таблицы computers, для которых нет соответствия в таблице rooms.

СMySQL здесь та же проблема, что и в предыдущей задаче — отсутствие поддержки полного внешнего объединения, что вынуждает нас опять использовать

два отдельных запроса, результаты которых объединяются с помощью UNION:

MySQL Решение 2.2.10.g

1SELECT 'r_id' ,

2' r_name' ,

3' c_id' ,

4'c room',

5'c name'

6

FROM 'rooms'

7

LEFT JOIN 'computers'

8

ON 'r_id' = 'c room

9WHERE 'c_id' IS NULL

10UNION

11SELECT 'r_id' ,

12'r name',

13' c_id' ,

14'c room',

15'c name'

16FROM 'rooms'

17RIGHT JOIN 'computers

18

ON 'r_id' = c room

19WHERE 'r id' IS NULL

ВMS SQL Server и Oracle всё проще: достаточно указать, в каких полях мы

ожидаем наличие NULL.

MS SQL Решение 2.2.10.g

1SELECT [r id],

2[r name]

3[c id],

4[c room]

5[c name]

6

FROM

[rooms]

7

 

FULL JOIN [computers]

8

 

 

ON [r id] = [c room]

9

WHERE

[r

id] IS NULL

10

 

OR [c

id] IS NULL

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 170/545

Пример 20: все разновидности запросов на объединение в трёх СУБД

Oracl

і

Решение 2.2.10.g

I

e

 

 

 

 

1

SELECT "r id",

 

2

 

 

"r name"

 

3

 

 

"c id",

 

4

 

 

"c room"

 

5

 

 

"c name"

 

6

FROM

"rooms"

 

7

 

 

FULL JOIN "computers"

8

 

 

ON "r id" = "c room"

9

WHERE

"r id" IS NULL

10

 

 

OR "c id" IS NULL

Условия в строках 9-10 запросов для MS SQL Server и Oracle не допускают попадания в конечную выборку строк, отличных от имеющих NULL-значение в полях r_id или c_id. Эти поля выбраны не случайно: они являются первичными ключами своих таблиц, и потому появление в них NULL-значения, изначально записанного в таблицу крайне маловероятно в отличие от «обычных полей», где NULL вполне может храниться как признак отсутствия значения.

Графически набор попадающих в конечную выборку записей выглядит так (отмечено серым фоном):

r_name

r_id

c_room

c_id

c_name

Комната с двумя компьютерами

1

1

1

Компьютер A в комнате 1

Комната с двумя компьютерами

1

1

2

Компьютер B в комнате 1

Комната с тремя компьютерами

2

2

3

Компьютер A в комнате 2

Комната с тремя компьютерами

2

2

4

Компьютер B в комнате 2

Комната с тремя компьютерами

2

2

5

Компьютер C в комнате 2

Пустая комната 1

3

NULL

NULL

NULL

Пустая комната 2

4

NULL

NULL

NULL

Пустая комната 3

5

NULL

NULL

NULL

NULL

NULL

NULL

6

Свободный компьютер A

NULL

NULL

NULL

7

Свободный компьютер B

NULL

NULL

NULL

8

Свободный компьютер C

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 171/545

Пример 20: все разновидности запросов на объединение в трёх СУБД

Задача 2.2.10.h: показать возможные варианты расстановки компьютеров по комнатам (не учитывать вместимость комнат).

Ожидаемый результат 2.2.10.h.

r_id

r_name

c_id

c_room

c_name

1

Комната с двумя компьютерами

1

1

Компьютер A в комнате 1

2

Комната с тремя компьютерами

1

1

Компьютер A в комнате 1

3

Пустая комната 1

1

1

Компьютер A в комнате 1

4

Пустая комната 2

1

1

Компьютер A в комнате 1

5

Пустая комната 3

1

1

Компьютер A в комнате 1

1

Комната с двумя компьютерами

2

1

Компьютер B в комнате 1

2

Комната с тремя компьютерами

2

1

Компьютер B в комнате 1

3

Пустая комната 1

2

1

Компьютер B в комнате 1

4

Пустая комната 2

2

1

Компьютер B в комнате 1

5

Пустая комната 3

2

1

Компьютер B в комнате 1

1

Комната с двумя компьютерами

3

2

Компьютер A в комнате 2

2

Комната с тремя компьютерами

3

2

Компьютер A в комнате 2

3

Пустая комната 1

3

2

Компьютер A в комнате 2

4

Пустая комната 2

3

2

Компьютер A в комнате 2

5

Пустая комната 3

3

2

Компьютер A в комнате 2

1

Комната с двумя компьютерами

4

2

Компьютер B в комнате 2

2

Комната с тремя компьютерами

4

2

Компьютер B в комнате 2

3

Пустая комната 1

4

2

Компьютер B в комнате 2

4

Пустая комната 2

4

2

Компьютер B в комнате 2

5

Пустая комната 3

4

2

Компьютер B в комнате 2

1

Комната с двумя компьютерами

5

2

Компьютер C в комнате 2

2

Комната с тремя компьютерами

5

2

Компьютер C в комнате 2

3

Пустая комната 1

5

2

Компьютер C в комнате 2

4

Пустая комната 2

5

2

Компьютер C в комнате 2

5

Пустая комната 3

5

2

Компьютер C в комнате 2

1

Комната с двумя компьютерами

6

NULL

Свободный компьютер A

2

Комната с тремя компьютерами

6

NULL

Свободный компьютер A

3

Пустая комната 1

6

NULL

Свободный компьютер A

4

Пустая комната 2

6

NULL

Свободный компьютер A

5

Пустая комната 3

6

NULL

Свободный компьютер A

1

Комната с двумя компьютерами

7

NULL

Свободный компьютер B

2

Комната с тремя компьютерами

7

NULL

Свободный компьютер B

3

Пустая комната 1

7

NULL

Свободный компьютер B

4

Пустая комната 2

7

NULL

Свободный компьютер B

5

Пустая комната 3

7

NULL

Свободный компьютер B

1

Комната с двумя компьютерами

8

NULL

Свободный компьютер C

2

Комната с тремя компьютерами

8

NULL

Свободный компьютер C

3

Пустая комната 1

8

NULL

Свободный компьютер C

4

Пустая комната 2

8

NULL

Свободный компьютер C

5

Пустая комната 3

8

NULL

Свободный компьютер C

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 172/545

Пример 20: все разновидности запросов на объединение в трёх СУБД

Решение 2.2.10.h: используем перекрёстное объединение (декартово произведение).

MySQL і Решение 2.2.10.h

1

-- Вариант 1: без ключевого слова JOIN SELECT

'r_id',

2

 

'r_name',

 

3

 

'c_id',

 

4

 

'c_room',

 

5

 

'c_name'

 

6

FROM

' rooms ' ,

 

7

 

'computers'

 

8

 

 

 

1

-- Вариант 2: с ключевым словом JOIN SELECT

'r_id',

2

 

'r_name',

 

3

 

 

 

'c_id',

 

4

 

 

 

'c_room',

 

5

 

 

 

'c_name'

 

6

 

 

FROM

' rooms'

 

7

 

 

CROSS JOIN 'computers'

 

8

 

 

 

 

 

 

 

 

 

MS SQL I Решение 2.2.10.h |

1-- Вариант 1: без ключевого слова JOIN

2SELECT [r_id],

3[r name],

4[c_id],

5[c_room],

6[c_name]

FROM [rooms], [computers]

1-- Вариант 2: с ключевым словом JOIN

2SELECT [r_id],

3[r_name] ,

4[c_id],

5[c_room],

6[c_name]

 

 

FROM

[rooms]

 

 

 

 

CROSS JOIN [computers]

 

Oracl

і

Решение 2.2.10.h I

e

 

 

 

 

 

1

 

-- Вариант 1: без ключевого слова JOIN

2

 

SELECT "r id",

3

 

 

 

"r name"

4

 

 

 

"c id",

5

 

 

 

"c room"

6

 

 

 

"c name"

7

 

FROM

"rooms" ,

8

 

 

 

"computers"

1-- Вариант 2: с ключевым словом JOIN

2SELECT "r id",

3"r name"

4"c id",

5"c room"

6"c name"

7

FROM

"rooms"

8

 

CROSS JOIN "computers"

При выполнении перекрёстного объединения (декартового произведения) СУБД каждой записи из левой таблицы ставит в соответствие все записи из правой таблицы. Иными словами, СУБД находит все возможные попарные комбинации записей из обеих таблиц.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 173/545

Пример 20: все разновидности запросов на объединение в трёх СУБД

Задача 2.2.10.І: показать возможные варианты перестановки компьютеров по комнатам (компьютер не должен оказаться в той комнате, в которой он сейчас стоит, не учитывать вместимость комнат).

Ожидаемый результат 2.2.10.І.

r_id

r_name

c_id

c_room

c_name

2

Комната с тремя компьютерами

1

1

Компьютер A в комнате 1

3

Пустая комната 1

1

1

Компьютер A в комнате 1

4

Пустая комната 2

1

1

Компьютер A в комнате 1

5

Пустая комната 3

1

1

Компьютер A в комнате 1

2

Комната с тремя компьютерами

2

1

Компьютер B в комнате 1

3

Пустая комната 1

2

1

Компьютер B в комнате 1

4

Пустая комната 2

2

1

Компьютер B в комнате 1

5

Пустая комната 3

2

1

Компьютер B в комнате 1

1

Комната с двумя компьютерами

3

2

Компьютер A в комнате 2

3

Пустая комната 1

3

2

Компьютер A в комнате 2

4

Пустая комната 2

3

2

Компьютер A в комнате 2

5

Пустая комната 3

3

2

Компьютер A в комнате 2

1

Комната с двумя компьютерами

4

2

Компьютер B в комнате 2

3

Пустая комната 1

4

2

Компьютер B в комнате 2

4

Пустая комната 2

4

2

Компьютер B в комнате 2

5

Пустая комната 3

4

2

Компьютер B в комнате 2

1

Комната с двумя компьютерами

5

2

Компьютер C в комнате 2

3

Пустая комната 1

5

2

Компьютер C в комнате 2

4

Пустая комната 2

5

2

Компьютер C в комнате 2

5

Пустая комната 3

5

2

Компьютер C в комнате 2

аТХ- Решение 2.2.10.І: используем перекрёстное объединение (декартово произведение) с исключением.

1-- Вариант 1: без ключевого слова JOIN

2SELECT 'r_id',

'r_name',

4'c_id',

5'c_room',

6'c name'

7 FROM 'rooms',

8'computers'

9WHERE 'r id' != 'c room

1-- Вариант 2: с ключевым словом JOIN

2SELECT 'r_id',

3'r_name',

4'c_id',

5'c room',

6'c name'

7 FROM 'rooms'

8CROSS JOIN 'computers'

9WHERE 'r id' != 'c room'

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 174/545

Источник: https://studfile.net/preview/16420333/

Смотрите также:

_-___-~1
_3 тема - Диффузия
_Роджерс Карл, Брак и его альтернативы
'Заповит' Рейнгольда Глиэра: стилистические особенности
'Создание и продвижение интернет - платформы реферальной системы' на примере компании Бартер–кард
[БИЛЕТЫ] IMK №3
[ТЕСТЫ] ИМК №1 по физиологии (база 1 семестр и на экз)
[ТЕСТЫ] Спланхнология
[ТЕСТЫ] ЦНС - ЛУЧШИЕ 2020
+ Проблемы криминологии. Курс лекций