Main_Table PK User_Constraints 12 страница

FROM

(Select А.СТ, А.Ф, А.Н, А.Д, А.НД,

row_number() over(partition BY А.СТ Order by А.СТ) NUM,

row_number() over(partition BY А.СТ Order by А.СТ)+1 PNUM

From

(Select СТУДЕНТЫ.НОМЕР_СТУДЕНТА СТ, ФАМИЛИЯ Ф, НАЗВАНИЕ Н, ДАТА Д, НАЗВАНИЕ||', '||ДАТА НД

From СТУДЕНТЫ

JOIN УСПЕВАЕМОСТЬ

ON СТУДЕНТЫ.НОМЕР_СТУДЕНТА=УСПЕВАЕМОСТЬ.НОМЕР_СТУДЕНТА

JOIN ДИСЦИПЛИНЫ

ON УСПЕВАЕМОСТЬ.НОМЕР_ДИСЦИПЛИНЫ=ДИСЦИПЛИНЫ.НОМЕР_ДИСЦИПЛИНЫ

ORDER by ФАМИЛИЯ, ДАТА DESC) А) Б

Start with NUM=1

connect by prior PNUM=NUM and prior Б.Ф=Б.Ф and prior Б.Д<Б.Д+3

Group by Б.Ф) В;

Результат:

ВЕДОМОСТЬ

-----------------------------------------------------------------------------------------------------------------------Нагорный - Математика, 12.06.99

Поляков - Математика, 12.06.99;Физика, 10.06.99

Старова - Математика, 12.06.99;Физика, 10.06.99

Устинов - Математика, 12.06.99

Комментарий:

Сначала из соединения таблиц СТУДЕНТЫ и УСПЕВАЕМОСТЬ мы представляем данные о сдаче экзамена каждым студентом в формате, где НАЗВАНИЕ_ДИСЦИПЛИНЫ и ДАТА сдачи экзамена представлены одной строкой. Причем данные отсортированы по возрастанию даты для каждого студента. Далее в полученной таблице с помощью функций row_number() over(partition by … order by…) нумеруем строки в каждой группе (т.е. для каждого студента) дважды, чтобы создать порядок прохода по эти записям (для связи):

После этого, с помощью цикла проходим по записям, соответствующим каждому студенты, отдельно и конкатенируем поле НД. Затем из всех записей для каждого студента выбираем максимальную, она и будет являться полным списком по таблице УСПЕВАЕМОСТЬ:

Назовем полученную таблицу «В».

Далее мы с помощью оператора INTERSECT соединяем таблицу «В» с таблицей, полученной по такому же принципу, за исключением того, что информация об экзамене, который сдавался больше чем через три дня после предыдущего, не будет занесена в поле НД:

Таким образом, во второй таблице записи для каждого студента будут идентичны только в том случае, если все экзамены прошли с перерывом не более 3 дней.

Оператор INTERSECT в результирующей таблице выведет только идентичные записи полученных таблиц.

5. Для каждого отдела из таблицы Departments отобразить в виде одной строки с запятой в качестве разделителя фамилии сотрудников, работающих в нем.

select department_id, ltrim(MAX(sys_connect_by_path(last_name, ',')), ',') emp

From

(Select department_id, last_name,

row_number() over(partition BY department_id Order by department_id) NUM,

row_number() over(partition BY department_id Order by department_id)+1 PNUM

From employees)

Start with NUM=1

connect by prior PNUM=NUM and prior department_id=department_id

Group by department_id

Order by department_id;

Результат:

DEPARTMENT_ID EMP

------------- -------------------------------------------------------------------------------------------

10 Whalen

20 Hartstein,Fay

30 Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares

40 Mavris

Комментарий:

Группируем сотрудников по отделам и для каждой группы составляем связи между сотрудниками, чтобы в дальнейшем можно было использовать функцию sys_connect_by_path.

Циклом составляем для каждого отдела списки сотрудников: (пример для отдела 60)

А затем выбираем максимальный список, который и будет являться полным.

6. Вывести фамилии сотрудников, зарплаты и неплотные ранги в пределах отделов, в которых работают сотрудники.

SELECT department_id, LAST_NAME, SALARY, RANK () OVER(PARTITION BY department_id ORDER BY SALARY) NUM

FROM EMPLOYEES;

Результат:

DEPARTMENT_ID LAST_NAME SALARY NUM

------------- ------------------------- -------- ---

10 Whalen 4400 1

20 Fay 6000 1

20 Hartstein 13000 2

30 Colmenares 2500 1

30 Himuro 2600 2

30 Tobias 2800 3

30 Baida 2900 4

30 Khoo 3100 5

30 Raphaely 11000 6

40 Mavris 6500 1

50 Olson 2100 1

50 Philtanker 2200 2

50 Markle 2200 2

Комментарий:

С помощью функции RANK() выставляем неплотные ранги для зарплат сотрудников, в пределе одного отдела.

7. Написать запрос, выдающий отчёт о суммарных выплатах сотрудникам, непосредственно подчиняющихся заданному руководителю по идентификаторам должностей (поле Job_id). Непосредственное подчинение предполагает подчинение на первом уровне. Номер руководителя может встречаться в отчете лишь дважды.

Пример отчёта:

Номер руководителя	Должность	Кол-во сотрудников	Выплаты	Вид выплаты
	AD_VP			Зарплата сотрудников в должности AD_VP
	MK_MAN			Зарплата сотрудников в должности MK_MAN
	PU_MAN			Зарплата сотрудников в должности PU_MAN
	SA_MAN			Зарплата сотрудников в должности SA_MAN
	ST_MAN		36400.	Зарплата сотрудников в должности ST_MAN
				Суммарная зарплата у руководителя 100
	AC_MGR			Зарплата сотрудников в должности AC_MGR
	FI_MGR			Зарплата сотрудников в должности FI_MGR
	HR_REP			Зарплата сотрудников в должности HR_REP
	PR_REP			Зарплата сотрудников в должности PR_REP
	AD_ASST			Зарплата сотрудников в должности AD_ASST
				Суммарная зарплата у руководителя 100
				Общий итог

Select

DECODE(COALESCE(to_number(

decode(lag(m.employee_id)over(order by m.employee_id),

m.employee_id,null,m.employee_id)

), DECODE(grouping(e.job_id), 1, m.employee_id, null)),

NULL, ' ',

COALESCE(to_number(

decode(lag(m.employee_id)over(order by m.employee_id),

m.employee_id,null,m.employee_id)

), DECODE(grouping(e.job_id), 1, m.employee_id, null))) "Номер руководителя",

Decode(grouping(e.job_id), 1, null,e.job_id) "Должность",

Count(e.employee_id) "Кол-во сотрудников",

SUM(e.salary) "Выплаты",

Decode(grouping(e.job_id)+grouping(m.employee_id), 1, 'Суммарная зарплата у руководителя '||m.employee_id,

2, 'Общий итог',

'Зарплата сотрудников в должности '||e.job_id) "Вид выплаты"

From Employees e Inner Join Employees m

ON e.manager_id=m.employee_id

Group by Rollup (m.employee_id, e.job_id);

Результат:

Комментарий:

Воспользовались функцией Rollup для составления списков с подведением итогов на соединении таблицы employees самой с собой (чтобы отображать подчинение Менеджер-Сотрудник).

Чтобы отчет выглядел как в задании, пришлось анализировать функцию grouping(), которая принимает значение 1 только в строке подведения итогов, так как в зависимости в том числе и от ее значения принималось решение о том записывать ли номер менеджера или оставлять поле пустым.

8. Создать запрос для получения информации об успеваемости студентов в виде:

ФИО	Дисциплина	Оценка	Дата	Примечания
Петров	Математика		20.1.2008
	Физика		22.1.2008
	Химия		25.1.2008
	Химия		27.1.2008	Пересдача
Усов	Математика		12.06.99
	Экономика		15.06.99
	Менеджмент		17.06.99
	Менеджмент		18.06.99	Пересдача

Задачу решить двумя способами:

а) с использованием аналитических функций;

Select DECODE(NUM, 1,ФАМИЛИЯ, '') ФАМИЛИЯ, НАЗВАНИЕ, ОЦЕНКА, ДАТА,DECODE(PNUM-NUM, 0, '', 'Пересдача') Пересдача

From (

Select ФАМИЛИЯ, НАЗВАНИЕ, ОЦЕНКА, ДАТА,NUM, rank() OVER(PARTITION BY ФАМИЛИЯ ORDER BY НОМЕР_ДИСЦИПЛИНЫ) PNUM

From

(Select ФАМИЛИЯ, НАЗВАНИЕ,ДИСЦИПЛИНЫ.НОМЕР_ДИСЦИПЛИНЫ, ОЦЕНКА, ДАТА,

row_number() over(partition BY ФАМИЛИЯ Order by ФАМИЛИЯ,ДИСЦИПЛИНЫ.НОМЕР_ДИСЦИПЛИНЫ) NUM

FROM "СТУДЕНТЫ" JOIN "УСПЕВАЕМОСТЬ"

ON "СТУДЕНТЫ"."НОМЕР_СТУДЕНТА"="УСПЕВАЕМОСТЬ"."НОМЕР_СТУДЕНТА"

JOIN "ДИСЦИПЛИНЫ"

ON "УСПЕВАЕМОСТЬ"."НОМЕР_ДИСЦИПЛИНЫ" = "ДИСЦИПЛИНЫ"."НОМЕР_ДИСЦИПЛИНЫ"

Order by ФАМИЛИЯ, "НАЗВАНИЕ", ОЦЕНКА)

Order by ФАМИЛИЯ, Дата, "НАЗВАНИЕ");

Результат:

Комментарий:

Сортируем записи по названию предмета и оценке для каждого студента.

Отдельно для каждого студента нумеруем по порядку экзамены, которые он сдавал.

Опять проводим сортировку по тем же полям для последующего выставления неплотных рангов.

Затем снова для каждого студента выставляем неплотные ранги в зависимости от номера дисциплины, которую он сдавал.

В итоге: если студент сдавал какой-то экзамен не первый раз, то номер по порядку экзамена и его неплотный ранг не будут совпадать => это и является условием проверки для выставления примечания «Пересдача».

б) без использования аналитических функций.

with А as

(select t.НОМЕР_СТУДЕНТА n,t.ДАТА d,t.оценка o,t.Номер_дисциплины nd,count (*) cnt1

from(select t.НОМЕР_СТУДЕНТА, t.ДАТА, t.оценка, t.Номер_дисциплины

from УСПЕВАЕМОСТЬ t inner join УСПЕВАЕМОСТЬ t1

on t.НОМЕР_СТУДЕНТА = t1.НОМЕР_СТУДЕНТА

where t.Дата >= t1.Дата) t

group by t.НОМЕР_СТУДЕНТА, t.ДАТА, t.оценка, t.Номер_дисциплины

order by t.НОМЕР_СТУДЕНТА, t.ДАТА, t.Номер_дисциплины),

Б as

(select z1.n,z1.d,z1.o,z1.nd,z1.cnt1, count(*) cnt2

from (select z1.n,z1.d,z1.o,z1.nd,z1.cnt1

from А z1 inner join А z2

on z1.n=z2.n and z1.nd=z2.nd

where z1.d>=z2.d) z1

group by z1.n,z1.d,z1.o,z1.nd,z1.cnt1

order by z1.n,z1.d,z1.o)

select (case when Б.cnt1=1 then c.фамилия else null end) "Фамилия",

d.название "Дисциплина", Б.o "Оценка", Б.d "Дата",

(case when Б.cnt2=2 then 'Пересдача' else null end) "Примечание"

from (Б inner join СТУДЕНТЫ c

on Б.n=c.номер_студента) inner join ДИСЦИПЛИНЫ d

on Б.nd=d.номер_дисциплины;

Результат:

Комментарий:

Результат мы получили путем соединения таблиц Студенты, Успеваемость, Дисциплины.

Промежуточные результаты: таблицы А и Б

Табл.А Табл.Б

Cnt1 равен 1 только в случае, если данный экзамен для данного студента является первым. В результирующем запросе это поле будет определять писать ли фамилию студента, чтобы отчет выглядел как в задании (1 – писать фамилию, не 1 – не писать ничего)

Cnt2 определяет количество записей относящихся к студенту, сдавшему экзамен по данной дисциплине. То есть определяет количество попыток сдать экзамен эти студентом по этой дисциплине. В результирующем запросе этот параметр определяет поле «Примечание».

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

Select t1.id_user, t3.time_call

From

(Select Id_user, Time_call, Status, rownum r

From (Select Id_user, Time_call, Status,rownum r

From TEST3

Order by ID_USER,r) Order by r) t1

Join

(Select Id_user, Time_call, Status, rownum r

From (Select Id_user, Time_call, Status,rownum r

From TEST3

Order by ID_USER,r) Order by r) t2

ON t1.id_user=t2.id_user AND t2.r=t1.r + 1

Join

(Select Id_user, Time_call, Status, rownum r

From (Select Id_user, Time_call, Status,rownum r

From TEST3

Order by ID_USER,r) Order by r) t3

ON t2.id_user=t3.id_user AND t3.r=t2.r+1

Where t1.status='No' and t2.status='No' and t3.status='No';

Исходные данные (Table TEST3):

Результат:

ID_USER TIME_CALL

------- ---------

1 04.01.01

2 11.01.01

Комментарий:

Поскольку нам необходимо отследить 3 попытки, то временную таблицу соединяем саму с собой 3 раза (t1, t2, t3).

Данные в таблице TEST3 содержатся последовательные, следовательно пронумеровав строки и отсортировав по ним мы не изменим порядок записей.

Поэтому сначала группируем записи по полю id_user, а затем сортируем по полю r, чтобы не нарушить порядок записей внутри группы, и затем снова нумеруем все строки.

Соединяем три одинаковые таблицы, по полю id_user, но с условиями: 1)чтобы их номера шли последовательно, 2)в поле STATUS содержалось значение No.

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

SELECT ФАМИЛИЯ, WM_CONCAT(НАЗВАНИЕ)||',' "Необходимо досдать"

FROM

(SELECT ФАМИЛИЯ, НАЗВАНИЕ

FROM

(Select ФАМИЛИЯ, НАЗВАНИЕ

From СТУДЕНТЫ JOIN "ГРУППЫ"

ON "ГРУППЫ"."НОМЕР_ГРУППЫ"="СТУДЕНТЫ"."НОМЕР_ГРУППЫ"

JOIN "СПЕЦИАЛЬНОСТИ"

ON "ГРУППЫ"."КОД_СПЕЦИАЛЬНОСТИ"=

"СПЕЦИАЛЬНОСТИ"."КОД_СПЕЦИАЛЬНОСТИ"

JOIN "УЧЕБНЫЕ_ПЛАНЫ"

ON "УЧЕБНЫЕ_ПЛАНЫ"."КОД_СПЕЦИАЛЬНОСТИ"=

"СПЕЦИАЛЬНОСТИ"."КОД_СПЕЦИАЛЬНОСТИ"

JOIN "ДИСЦИПЛИНЫ"

ON "УЧЕБНЫЕ_ПЛАНЫ"."НОМЕР_ДИСЦИПЛИНЫ" = "ДИСЦИПЛИНЫ"."НОМЕР_ДИСЦИПЛИНЫ"

MINUS

Select ФАМИЛИЯ, НАЗВАНИЕ

From СТУДЕНТЫ JOIN УСПЕВАЕМОСТЬ

ON СТУДЕНТЫ.НОМЕР_СТУДЕНТА=УСПЕВАЕМОСТЬ.НОМЕР_СТУДЕНТА

JOIN ДИСЦИПЛИНЫ

ON ДИСЦИПЛИНЫ.НОМЕР_ДИСЦИПЛИНЫ=

УСПЕВАЕМОСТЬ.НОМЕР_ДИСЦИПЛИНЫ

WHERE ОЦЕНКА>2))

Group by ФАМИЛИЯ;

Результат:

Комментарий:

Из выборки студентов и дисциплин, которые им необходимо сдать по учебному плану вычитаем выборку студентов и дисциплин, которые они уже сдали.

Получаем список хвостистов.

С помощью функции WM_CONCAT проводим конкатенацию над строками, соответствующими одному студенту.

11. Создать запрос для вывода списка фамилий студентов с указанием количества несданных ими в сессию дисциплин.

SELECT ФАМИЛИЯ, COUNT(номер_дисциплины)

FROM

(Select distinct номер_студента, номер_дисциплины

from студенты natural join группы natural join учебные_планы

MINUS

select distinct номер_студента, номер_дисциплины

from успеваемость

WHERE ОЦЕНКА>2)

RIGHT JOIN "СТУДЕНТЫ"

using(номер_студента)

GROUP BY Фамилия;

Результат:

Комментарий:

Из выборки, содержащей записи обо всех необходимых экзаменах для всех студентов, вычитаем записи из таблицы успеваемость, в которой отражены сданные студентами экзамены.

Получаем список студентов-хвостистов.

Соединяем с помощью RIGHT JOIN получившуюся таблицу с таблицей СТУДЕНТЫ, чтобы учесть студентов, данных о которых нет в таблице хвостистов, т.е. тех студентов, которые сдали все необходимые экзамены.

Функцией COUNT подсчитываем количество несданных дисциплин.

12. В произвольной строке, состоящей из символьных элементов, разделенных запятыми, отсортировать элементы по алфавиту. Например, символьную строку

abc,cde,ef,gh,mn,test,ss, df,fw,ewe,wwe

преобразовать к виду:

abc,cde,df,ef,ewe, fw,gh,mn,ss,test,wwe.

SELECT WM_CONCAT(STR) AS "Str" FROM (

SELECT REGEXP_SUBSTR('ABC,CDE,EF,GH,MN,TEST,SS, DF,FW,EWE,WWE', '[A-Z]+', 1, LEVEL) STR

FROM DUAL

CONNECT BY REGEXP_SUBSTR('ABC,CDE,EF,GH,MN,TEST,SS, DF,FW,EWE,WWE', '[A-Z]+', 1, LEVEL) IS NOT NULL

ORDER BY STR);

Результат:

Комментарий:

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

Сортируем столбец по возрастанию. А затем функцией WM_CONCAT снова слепляем все записи в одну строку.

13. Создать запрос для вывода списка групп, в которых нет должников.

SELECT НОМЕР_ГРУППЫ

FROM ГРУППЫ

MINUS

SELECT DISTINCT НОМЕР_ГРУППЫ

FROM

(Select ФАМИЛИЯ, НАЗВАНИЕ, СТУДЕНТЫ.НОМЕР_ГРУППЫ

From СТУДЕНТЫ JOIN "ГРУППЫ"

ON "ГРУППЫ"."НОМЕР_ГРУППЫ"="СТУДЕНТЫ"."НОМЕР_ГРУППЫ"

JOIN "СПЕЦИАЛЬНОСТИ"

ON "ГРУППЫ"."КОД_СПЕЦИАЛЬНОСТИ"=

"СПЕЦИАЛЬНОСТИ"."КОД_СПЕЦИАЛЬНОСТИ"

JOIN "УЧЕБНЫЕ_ПЛАНЫ"

ON "УЧЕБНЫЕ_ПЛАНЫ"."КОД_СПЕЦИАЛЬНОСТИ"=

"СПЕЦИАЛЬНОСТИ"."КОД_СПЕЦИАЛЬНОСТИ"

JOIN "ДИСЦИПЛИНЫ"

ON "УЧЕБНЫЕ_ПЛАНЫ"."НОМЕР_ДИСЦИПЛИНЫ" = "ДИСЦИПЛИНЫ"."НОМЕР_ДИСЦИПЛИНЫ"

MINUS

Select ФАМИЛИЯ, НАЗВАНИЕ, НОМЕР_ГРУППЫ

From СТУДЕНТЫ JOIN УСПЕВАЕМОСТЬ

ON СТУДЕНТЫ.НОМЕР_СТУДЕНТА=УСПЕВАЕМОСТЬ.НОМЕР_СТУДЕНТА

JOIN ДИСЦИПЛИНЫ

ON ДИСЦИПЛИНЫ.НОМЕР_ДИСЦИПЛИНЫ=

УСПЕВАЕМОСТЬ.НОМЕР_ДИСЦИПЛИНЫ

WHERE ОЦЕНКА>2);

Результат:

no rows selected

Комментарий:

Из всего списка групп вычитаем группы, в которых есть должники (как определить группы, в которых есть должники упоминалось в предыдущих заданиях)

14. Создать запрос для получения информации о группах:

Группа	Кол-во студентов	Название Специальности	Кол-во круглых отличников	Кол-во должников
……

SELECT t1.НОМЕР_ГРУППЫ, "Кол-во студентов", "Название Специальности",

DECODE("Кол-во круглых отличников", NULL,0,"Кол-во круглых отличников") "Кол-во круглых отличников",

DECODE("Кол-во должников", NULL,0,"Кол-во должников") "Кол-во должников"

FROM

(Select "ГРУППЫ".НОМЕР_ГРУППЫ, COUNT(студенты.номер_студента) "Кол-во студентов",

НАЗВАНИЕ_СПЕЦИАЛЬНОСТИ "Название Специальности"

FROM "ГРУППЫ" JOIN "СТУДЕНТЫ"

ON "ГРУППЫ"."НОМЕР_ГРУППЫ"="СТУДЕНТЫ"."НОМЕР_ГРУППЫ"

JOIN "СПЕЦИАЛЬНОСТИ"

ON "СПЕЦИАЛЬНОСТИ"."КОД_СПЕЦИАЛЬНОСТИ" = "ГРУППЫ"."КОД_СПЕЦИАЛЬНОСТИ"

GROUP BY "ГРУППЫ".НОМЕР_ГРУППЫ, НАЗВАНИЕ_СПЕЦИАЛЬНОСТИ) t1

FULL JOIN

(SELECT НОМЕР_ГРУППЫ, COUNT(*) "Кол-во круглых отличников"

FROM

(Select СТУДЕНТЫ.НОМЕР_ГРУППЫ, ФАМИЛИЯ

FROM УСПЕВАЕМОСТЬ JOIN "СТУДЕНТЫ"

ON УСПЕВАЕМОСТЬ.НОМЕР_СТУДЕНТА=СТУДЕНТЫ.НОМЕР_СТУДЕНТА

JOIN "ГРУППЫ"

ON "СТУДЕНТЫ"."НОМЕР_ГРУППЫ" = "ГРУППЫ"."НОМЕР_ГРУППЫ"

WHERE ОЦЕНКА=5

MINUS

Select СТУДЕНТЫ.НОМЕР_ГРУППЫ, ФАМИЛИЯ

FROM УСПЕВАЕМОСТЬ JOIN "СТУДЕНТЫ"

ON УСПЕВАЕМОСТЬ.НОМЕР_СТУДЕНТА=СТУДЕНТЫ.НОМЕР_СТУДЕНТА

JOIN "ГРУППЫ"

ON "СТУДЕНТЫ"."НОМЕР_ГРУППЫ" = "ГРУППЫ"."НОМЕР_ГРУППЫ"

WHERE ОЦЕНКА<5)

GROUP BY НОМЕР_ГРУППЫ) t2

ON t1.НОМЕР_ГРУППЫ=t2.НОМЕР_ГРУППЫ

FULL JOIN

(SELECT НОМЕР_ГРУППЫ, COUNT(*) "Кол-во должников"

FROM

(Select СТУДенТЫ.НОМЕР_ГРУППЫ, ДИСЦИПЛИНЫ.номер_дисциплины, ФАМИЛИЯ

From СТУДЕНТЫ JOIN "ГРУППЫ"

ON "ГРУППЫ"."НОМЕР_ГРУППЫ"="СТУДЕНТЫ"."НОМЕР_ГРУППЫ"

JOIN "СПЕЦИАЛЬНОСТИ"

ON "ГРУППЫ"."КОД_СПЕЦИАЛЬНОСТИ"=

"СПЕЦИАЛЬНОСТИ"."КОД_СПЕЦИАЛЬНОСТИ"

JOIN "УЧЕБНЫЕ_ПЛАНЫ"

ON "УЧЕБНЫЕ_ПЛАНЫ"."КОД_СПЕЦИАЛЬНОСТИ"=

"СПЕЦИАЛЬНОСТИ"."КОД_СПЕЦИАЛЬНОСТИ"

JOIN "ДИСЦИПЛИНЫ"

ON "УЧЕБНЫЕ_ПЛАНЫ"."НОМЕР_ДИСЦИПЛИНЫ" = "ДИСЦИПЛИНЫ"."НОМЕР_ДИСЦИПЛИНЫ"

MINUS

Select НОМЕР_ГРУППЫ, успеваемость.номер_дисциплины, ФАМИЛИЯ

From СТУДЕНТЫ JOIN УСПЕВАЕМОСТЬ

ON СТУДЕНТЫ.НОМЕР_СТУДЕНТА=УСПЕВАЕМОСТЬ.НОМЕР_СТУДЕНТА

JOIN ДИСЦИПЛИНЫ

ON ДИСЦИПЛИНЫ.НОМЕР_ДИСЦИПЛИНЫ=

УСПЕВАЕМОСТЬ.НОМЕР_ДИСЦИПЛИНЫ

WHERE ОЦЕНКА>2)

GROUP BY НОМЕР_ГРУППЫ) t3

ON t1.НОМЕР_ГРУППЫ=t3.НОМЕР_ГРУППЫ

Order by НОМЕР_ГРУППЫ;

Комментарий:

Соединяем 3 таблицы по номеру группы.

Каждая таблица отвечает за свои подсчеты.

T1 – определяет количество студентов в данной группе и название специальности, которое ей соответствует.

Т2 – определяет количество круглых отличников в группе.

Т3 – определяет количество должников.

15. Создать три таблицы Системы Автоматического Управления, Математическое Обеспечение ЭВМ, Вычислительные Сети и Системы. Используя возможности многотабличной вставки, записать в эти таблицы информацию о студентах соответствии со специальностью, на которой они учатся.

create table системы_автоматич_управления

(номер_студента number(5,0),

фамилия varchar2(15),

имя varchar2(15),

отчество varchar2(15),

стипендия number(7,2),

номер_группы varchar2(15));

create table математическое_обеспечение_эвм

(номер_студента number(5,0),

фамилия varchar2(15),

имя varchar2(15),

отчество varchar2(15),

стипендия number(7,2),

номер_группы varchar2(15));

create table вычислительные_сети_и_системы

(номер_студента number(5,0),

фамилия varchar2(15),

имя varchar2(15),

отчество varchar2(15),

стипендия number(7,2),

номер_группы varchar2(15));

commit;

insert all

when код_специальности = 1

then into системы_автоматич_управления

values (номер_студента, фамилия, имя, отчество, стипендия, номер_группы)

when код_специальности = 2

then into математическое_обеспечение_эвм

values (номер_студента, фамилия, имя, отчество, стипендия, номер_группы)

when код_специальности = 3

then into вычислительные_сети_и_системы

values (номер_студента, фамилия, имя, отчество, стипендия, номер_группы)

select *

from студенты natural join группы natural join специальности;

commit;

Результат:

поскольку в нашей базе есть сведения только о студентах с 1 и 4 специальностью, то данные будут только в таблице системы_автоматич_управления

математическое_обеспечение_эвм:

no rows selected

вычислительные_сети_и_системы:

no rows selected

16. Создать представление для получения информации по специальностям: Средняя оценка среди студентов специальности, сдавших экзамены; Количество студентов специальности, сдавших экзамены, Общее количество студентов на специальности.

create view view_stat as

(select название_специальности, average средняя_оценка, count(*) количество_сдавших, studs всего_студентов

from

(select distinct название_специальности, номер_студента, avg(оценка) over (partition by название_специальности) average,

(select count(номер_студента)

from специальности сп inner join группы г on сп.код_специальности=г.код_специальности

inner join студенты с on г.номер_группы=с.номер_группы

where сп.название_специальности = t.название_специальности) studs

from

(select с.номер_студента, название_специальности, оценка, min(nvl(оценка,2)) over (partition by с.номер_студента) min_Score

from студенты с inner join группы г on с.номер_группы=г.номер_группы

inner join специальности сп on сп.код_специальности=г.код_специальности

left join учебные_планы уч on уч.код_специальности=сп.код_специальности

left join успеваемость у on у.номер_студента=с.номер_студента) t

where min_Score > 2)

group by название_специальности, average, studs);

Дата добавления: 2015-08-18