Лабораторная работа №5. Оператор JOIN

Краткие теоретические данные.

Join – оператор объединения. JOIN в SQL используется при построении select выражений. Инструкция Join позволяет объединить колонки из нескольких таблиц в одну. Объединение происходит временное и целостность таблиц не нарушается. Существует три типа join-выражений:

inner join;

outer join

---left,

---right

---full/cross join;

 

Внутреннее объединение INNER JOIN

Внутреннее объединение INNER JOIN (синоним JOIN, слово INNER можно опустить).

Выбираются только совпадающие данные из объединяемых таблиц.

Синтаксис

SELECT <список выбираемых столбцов>

FROM <Table A>

INNER JOIN <Table B> ON < условие объединения >

Пример 6. 2

Пусть даны: - таблицы t 1, содержащая данные о id студента, фамилию, средний балл, id группы, в которой студент учится; - таблицы t 2, содержащая данные о id группы, названии группы, факультете t1   Id group_id Faculty Name exm 1 1 ФПК Ахрем   2 2 ФПК Петров   3 3 ФПИ Иванов     Id Name_gr Faculty 1 по1701 ФПК 2 по1705 ФПК 3 по1708 ФПИ 4 по1709 ФПИ   Необходимо сформировать запрос, результатом которого будет выборка c информацией о студентах, факультетах, группах, оценках SELECT * FROM tbl2 JOIN tbl1 ON tbl1.id_gr = tbl2.id   2) необходимо вывести информацию о студентах учащихся в группе по1701 SELECT * from tbl1 JOIN tbl1 ON tbl1.id_gr = tbl2.id WHERE tbl2.name_gr like 'по1701'

LEFT JOIN

Внутренний SELECT может возвращать не одно а множество значений одного поля. В таких случаях необходимо использовать оператор IN.

 Выбираются все столбцы левой таблицы, столбцы удовлетворяющие условию объединения из правой таблицы. Если не найдено соответствия в правой таблице, поля итоговой таблицы заполняются NULL.

Синтаксис

SELECT <список выбираемых столбцов>

FROM <Table A>

LEFT JOIN <Table B> ON <условие объединения>

Пример 6.2

На основании таблиц примера 5.1 вывести список всех групп с указанием студентов, которые там учатся SELECT * from tbl2 LEFT JOIN tbl1 on tbl2.id = tbl1.id_gr

RIGHT JOIN

Выбираются все столбцы правой таблицы, столбцы удовлетворяющие условию объединения из левой таблицы. Если не найдено соответствия в левой таблице, поля итоговой таблицы заполняются NULL.

Синтаксис

SELECT <список выбираемых столбцов>

FROM <Table A>

RIFHT JOIN <Table B> ON <условие объединения>

 

Пример 6.3:

На основании таблиц t1 и t2 (см. пример 5.1) необходимо вывести список студентов с указанием факультета и номера группы: SELECT * from tbl2 RIGHT JOIN tbl2 on tbl2.id = tbl1.id_gr

 

FULL JOIN

Возвращает все объединение левого и правого объединения)

Синтаксис

SELECT <список выбираемых столбцов>

FROM <Table A>

full JOIN <Table B>

on tbl2.f1 = tbl1.id (условие объединения)

Пример 6. 4:

На основании таблиц t1 и t2 (см. пример 5.1) необходимо вывести список студентов с указанием факультета и номера группы: SELECT * from tbl1 (что) full JOIN tbl2 (с чем) tbl 2. f 1 = tbl 1. id (условие объединения)

 

CROSS JOIN

Возвращает объединение каждой строки одного отношения с каждой строкой второго (Декартово произведение)

Синтаксис

SELECT <список выбираемых столбцов>

FROM <Table A>

full JOIN <Table B>

Пример 6. 5:

SELECT * from tbl1 CROSS JOIN tbl2

 

Задание к лабораторной работе №6

 

1. Вывести информацию о первых трех студентах (факультет, курс) заочной формы обучения младше 30 лет

2. Вывести информацию о первых пяти студентах (факультет, курс) заочной формы обучения старше 25 лет

3. Определить количество студентов на каждом факультете

4. Определить количество студентов, учащихся на каждой форме обучения

5. Определить средний возраст студентов для каждого факультета на конец года (лет)

6. Найти всех студентов-иностранцев (без отчества), вывести сведения о них (дата поступления, факультет, курс, форма обучения)

7. Определить общее количество аудиторных часов за весь период обучения для студентов очников ФПМ

8. Определить общее количество часов согласно плану для студентов заочной формы обучения ФПК заочной формы обучения

9. Определить всех сокурсников (с учетом факультета, но разных форм обучения) студента Ботяновского

10. Определить на какой факультет поступило наибольшее количество человек в 2015 году

11. Вывести список студентов, которые учатся на тех же курсах, что и студент Зингель и студентка Зайцева

12. Вывести список студентов по факультетам, средний балл которых меньше 7

13. Вывести список студентов, средний балл которых меньше чем средний по институту

14.  Вывести список студентов, средний бал которых выше чем по их факультету

 

 

Лабораторная работа №7.
Хранимые процедуры.