Условное объединение нескольких таблиц

Оператор SELECT может быть использован для условного объединения нескольких таблиц. Условное объединение подразумевает объединение таблиц по какому-либо условию предиката. Безусловное объединение таблиц даст в качестве результата декартово множество. При выполнении объединения  возможно ограничение числа строк итоговой выборки по условию. При мнготабличной выборке обязательным является указание перечня выводимых столбцов, а также перечисление всех таблиц которые участвуют в запросе, даже если это промежуточная таблица.

 

SELECT [ ALL| DISTINCT] < Список полей >|*

FROM <Список таблиц>

[WHERE <Предикат-условие выборки или соединения>]

[GROUP BY <Список полей результата>]

[HAVING <Предикат-условие для группы>]

[ORDER BY <Список полей, по которым упорядочить вывод>];

 

 

Пример 4.3:

На основании таблиц t1 и t2 (см. пример 4.1) необходимо вывести список студентов с указанием факультета и номера группы: SELECT t1.name, t2.name, t2.faculty, t2.form FROM t1,t2 WHERE t2.id=t1.group_id;

 

Пример 4.4:

Вывести информацию (Факультет, группа, форма обучения, им студента) о наиболее успевающих студентах (exm >7.5) SELECT t1.name, t2.name, t2.faculty, t2.form FROM t1,t2 WHERE t2.id=t1.g roup_id AND exm>7.5;

 

Объединение возможно не только для таблиц но и для выборок. Например предыдущий запрос можно представить как:

 

SELECT t3.name, t2.name, t2.faculty, t2.form FROM t2, (SELECT t1.* FROM t1 WHERE t1.exm>7.5) WHERE t2.id=t1.group_id;

Таким образом оператор SELECT не только делает выборку их таблиц, но может использоваться:

- во FROM для определения выборки из которой выполняется дальнейшая выборка или объединение

- в WHERE в предикате условия выборки строк

- в HAVING предикате условия выборки групп

 

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

 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

Лабораторная работа №5. Cоотнесенные подзапросы. Оператор EXIST.

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

Соотнесенный подзапросы

Соотнесенным называется подзапрос, который ссылается на данные внешнего запроса.

Пусть даны:

- таблицы t 1, содержащая данные о id студента, фамилию, средний балл, id группы, в которой студент учится;

- таблицы t 2, содержащая данные о id группы, названии группы, факультете, форма обучения.

t1
Id	Name	group_id	Exm

t2
Id	Name	faculty	Form

 

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

SELECT name, faculty, form

FROM t2

WHERE 5< (SELECT COUNT(*) FROM t1 WHERE t1.id_group=t2.id);

 

 Соотнесенные или связные запросы могут быть созданы в отношении одной таблицы

 

Сформировать запрос, результатом которого будет список факультетов с числом групп меньше трех

SELECT DICTINCT name

FROM t1 T3

WHERE 3> (SELECT COUNT(*) FROM t1 WHERE t1.name=T3.name);

 

Соотнесенные запросы можно применять для проверки корректности информации внутри таблицы.

 

Оператор EXISTS.

Оператор  EXISTS принимает значение TRUE, если подзапрос содержит любое количество строк, иначе его значение равно FALSE. Для NOT EXISTS все наоборот. Этот предикат никогда не принимает значение UNKNOWN.

Обычно предикат EXISTS используется в зависимых (коррелирующих) подзапросах. Этот вид подзапроса имеет внешнюю ссылку, связанную со значением в основном запросе. Результат подзапроса может зависеть от этого значения и должен оцениваться отдельно для каждой строки запроса, в котором содержится данный подзапрос. Поэтому предикат EXISTS может иметь разные значения для разных строк основного запроса.

SELECT name, faculty, form

FROM t2

WHERE 5< (SELECT COUNT(*) FROM t1 WHERE t1.id_group=t2.id)

ADN EXIST(SELECT t1.name FROM t1 WHERE t1.id_group=t2.id and t1.exm>9);

 

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

Использовать БД UNIVER2

1. Найти фамилии студентов, которые учатся на одной факультете, курсе, форме обучения со студентами, средний балл которых >9

2. Найти фамилии студентов, которые учатся на одной факультете, курсе, форме обучения со студентами, средний бал которых >9, и число таких студентов больше 2.

3.  Найти студентов, обучающихся одновременно на разных формах обучения

4. Вывести список студентов, обучающихся одновременно на разных формах обучения, при этом одна из форм вечерняя.