12 Даны отношения, моделирующие работу туристического агентства, имеющего много филиалов в различных странах:

R1

Филиал

Страна

Город

R2

Номер договора

Филиал

Дата начала

Дата окончания

R3

Клиент

Страна

Номер договора

1. Клиентов, заключивших договоры с несколькими филиалами.

SELECT DISTINCT Client FROM(SELECT R3.Client, R2.Filial FROM R2, R3 WHERE R2.NDog = R3.NDog GROUP BY R3.Client, R2.Filial HAVING Count(*)>1

2. Филиалы, которые работают с клиентами только одной страны.

SELECT DISTINCT Filial FROM(SELECT R2.Filial, R3.Country FROM R2, R3 WHERE R2.NDog = R3.NDog GROUP BY R2.Filial, R3.Country HAVING Count(DISTINCT Country)=1

3. Клиентов, которые заключили несколько договоров с одним филиалом.

SELECT DISTINCT Client FROM(SELECT R3.Client, R2.Filial FROM R2, R3 WHERE R2.NDog = R3.NDog GROUP BY R3.Client, R2.Filial HAVING Count(*)>1

4. Филиалы, которые заключили договоры только с клиентами из той же станы, в которой расположен этот филиал.

SELECT Filial FROM (SELECT R1.Filial, R1.Country, R3.Country FROM (R2 INNER JOIN R3 ON R2.NDog = R3.NDog) INNER JOIN R1 ON R2.Filial = R1.Filial

GROUP BY R1.Filial, R1.Country, R3.Country) GROUP BY R1.Filial, R1.Country HAVING COUNT(*)=1

13. Даны отношения:

R1 R2

ВУЗ

Город

ФИО

ВУЗ

R3

ФИО

Конференция

Занятое место

R4

ВУЗ, где проводится конференция

Название конференции

1. ВУЗы, студенты которых принимали участие только в одной (какой-либо) конференции.

SELECT Vuz FROM (SELECT DISTINCT Vuz, Konf FROM R2, R3 WHERE R2.Fio = R3.Fio) GROUP BY Vuz, Konf HAVING COUNT(*)=1

2. ВУЗы, в которых проводилось более одной конференции.

SELECT Vuz FROM R4 GROUP BY Vuz HAVING Count(*)>1

3. ВУЗы, студенты которых заняли призовые места на всех конференциях.

SELECT Vuz FROM (SELECT DISTINCT Vuz FROM R2, R3 WHERE R2.FIO=R3.Fio and R3.Otc<=3 GROUP BY R2.Vuz, R2.Fio HAVING Count(*)=(SELECT Count(DISTINCT Konf ) FROM R3)) WHERE Vuz not in (SELECT DISTNCT Vuz FROM R2, R3 WHERE R2.FIO = R3.Fio and Otc>3)

4. ВУЗы, студенты которых участвовали в конференциях, но не заняли ни одного призового места.

SELECT DISTINCT R2.Vuz FROM R2 WHERE R2.Fio not in(SELECT R3.FIO FROM R3 WHERE R2.Fio = R3.FIO and R3.Otc <= 3)

14. Даны отношения:

R1

Дисциплина

Курс

R2

ФИО преподавателя

Кафедра

R3

ФИО преподавателя

Дисциплина

Тип занятия

1. Преподавателей, которые могут вести все дисциплины на 3-м курсе.

SELECT Prepod FROM (SELECT DISTINCT Prepod, Disc FROM R3 GROUP BY Prepod, Disc HAVING Count(Disc) = (SELECT Count(DISTINCT Disc) FROM R1 WHERE Kurs = 3))

2. Преподавателей, которые могут вести дисциплины только на одном курсе.

SELECT Prepod FROM (SELECT DISTINCT R3.Prepod, R1.Kurs FROM R1, R3 WHERE R1.Disc = R3.Disc GROUP BY R3.Prepod, R1.Kurs HAVING Count(*) = 1)

3. Преподавателей, которые ведут все типы занятий, перечисленные в R3.

SELECT Prepod FROM (SELECT DISTINCT Prepod, Type FROM R3 GROUP BY Prepod, Type HAVING Count(Type) = (SELECT Count(DISTINCT Type) FROM R3))

4. Кафедры, преподаватели которых работают только на старших курсах (начиная с 3-го).

SELECT Kaf FROM (SELECT DISTINCT R2.Kaf, R1.Kurs FROM R1, R2, R3 WHERE R2.Prepod = R3.Prepod and R2.Disc = R1.Disc and R1.Kurs >= 3)

15. Даны отношения, моделирующие текущий учебный процесс. Считать, что студент может делать несколько попыток сдачи лаб. работы, т.е. в R2 может стоять незачет по ряду лаб. работ. Если студент не делал ни одной попытки сдачи, то в R2 просто отсутствует соответствующая запись.

R1

ФИО студента

№ группы

R2

ФИО студента

Название дисциплины

Номер лаб. работы

Отметка о зачете по работе

R3

Название дисциплины

Номер лаб. работы

R4

Название дисциплины

№ группы

1. Студентов, которые по одной дисциплине сдали более одной лабораторной работы.

SELECT DISTINCT Stud, Disc, nLab FROM R2 WHERE Otc > 2 GROUP BY Stud, Disc, NLab HAVING Count(*) > 1

2. Студентов, которые сдали все лабораторные работы по всем дисциплинам, из тех, по которым это необходимо.

SELECT DISTINCT R1.Stud FROM R1WHERE R1.Stud not in(SELECT Stud FROM (SELECT R5.Stud, R5.Disc, R5.NLab, max(R2.Otc) as Otc FROM (SELECT DISTINCT R1.Stud, R4.Disc, R3.NLab FROM R1, R2, R3 WHERE R1.NGROUP = R4.NGROUP and R4.Disc = R3.Disc) R5 LEFT OUTER JOIN R2 ON R2.Stud = R5.Stud and R2.Disc = R5.Disc and R2.NLab = R5.NLab and R2.Otc > 2 GROUP BY R5..Stud, R5.Disc, R5.NLab) R6 WHERE Otc = 0)

3. Группы, в которых имеются студенты, не сдавшие ни одной работы.

SELECT DISTINCT NGroup FROM R1 WHERE Stud in(SELECT Stud FROM R1 WHERE stud not in(SELECT Stud FROM R2 WHERE Otc > 2)) R5

4. Группы, в которых нет студентов, не сдавших ни одной лабораторной работы.

SELECT DISTINCT NGroup FROM R1 WHERE Stud not in(SELECT Stud FROM R1 WHERE stud not in(SELECT Stud FROM R2 WHERE Otc > 2)) R5

5. Дисциплины, по которым имеются задолжности.

SELECT R4.Disc FROM R1, R3, R4 WHERE R4.Disc = R3.Disc and R3.NGroup = R1.NGroup and not exists(select top 1 * from R2 where R2.Stud = R1.Stud and R2.Disc = R3.Disc and R2.NLab = R3.NLab and R2.Otc > 2)

16. Даны отношения, моделирующие работу конкурса студенческих работ. Необходимо учитывать следующие ограничения: одна работа может быть подготовлена несколькими студентами разных групп и даже разных факультетов. Количество видов работ ограничено, но заранее неизвестно, в R3 приведен пример заполнения таблицы.

R1 R2

ФИО студента

№ группы

№ группы

Факультет

R3

Название работы	ФИО автора	Вид работ
Алмаз	Иванов	УИРС

R4

Вид работ

Количество баллов

1. Работы, подготовленные студентами 4-го факультета.

SELECT R3.NameRab FROM R1, R2, R3 WHERE R1.NGroup=R2.NGroup and R1.Stud =R3.Stud and R2.Fak=4

2. Факультеты, студенты которых подготовили только УИРС.

SELECT DISTINCT R2.Fak FROM R1, R2, R4 WHERE R2.NGroup=R1.NGroup and R1.Stud=R3.Stud and R3.Work_Type='УИРС' and (R2.Fak not in(SELECT R2.Fak FROM R2, R3, R1 WHERE R2.NGroup=R1.NGroup and R1.Stud=R3.Stud and R2.Work_Type<>' УИРС’));

3. Студентов, которые представили более одной статьи (вид работы — «Статья»).

SELECT R3.Stud FROM R3 WHERE R3.Wid_Rab=’Статья’ GROUP BY Stud HAVING Count(*)>1

4. Студентов, которые подготовили все виды работ.

SELECT Stud FROM R3 WHERE GROUP BY FIO HAVING Count(DISTINCT Woprk_Type) = (SELECT Count(DISTINCT Vid) FROM R4)

17. Даны отношения, моделирующие конкурс исследовательских работ студентов. Необходимо учитывать следующие ограничения: Номер группы однозначно определяет выпускающую кафедру. Студенты не обязательно могут готовить работы по своей выпускающей кафедре. В общем случае одна работа может быть подготовлена несколькими студентами и один студент может подготовить несколько работ. Не все студенты готовят исследовательские работы.

R1

ФИО

№ группы

Курс

R2 R4

№ группы

Выпускающая кафедра

Кафедра

Факультет

R3 R5

Название работы

Вид работы

ФИО студента

Представляющая кафедра

Вид работы

Баллы

1. Кафедры, студенты которых подготовили все виды работ.

SELECT R3.KAFEDRA FROM R3, R1 WHERE R1.FIO=R3.FIO GROUP BY R3.KAFEDRA HAVING COUNT(R3.WORK_TYPE) = (SELECT COUNT(*) FROM R5)

2. Факультеты, на которых не было подготовлено ни одной студенческой работы..

SELECT DISTINCT Facultet FROM R4 WHERE Kafedra not in(SELECT Kafedra FROM R3)

3. Группы, студенты которых подготовили только статьи.

SELECT DISTINCT R1.Group_Num FROM R1, R3 WHERE R1.FIO=R3.FIO and (R1.Group_Num not in (SELECT DISTINCT b_1.GROUP_NUM From R1, R3 WHERE R3.WORK_TYPE<>'статья' and R1.FIO=R3.FIO))

4. Студентов 1-го курса, подготовивших работы на своей выпускающей кафедре.

SELECT R1.FIO FROM R1,R2,R3 WHERE R1.FIO=R3.FIO and R2.Kafedra= R3.Kafedra and R1.Kurs=1 R1.GROUP_NUM=R2.GROUP_NUM