В начало

Лабораторная работа № 7

Естественное соединение таблиц

 

Используя опыт, полученный при реализации запроса, выведите таблицу со средними оценками студентов вашей группы (Номер, ФИО, Ср_оценка).

·         При составлении запроса  следует учитывать, что группы с таким же номером существовали и в прошлые годы, а присутствие студента в вашей группе должно быть подтверждено утвержденным приказом.

 

SELECT н_ведомости.члвк_ид номер, человек(н_ведомости.члвк_ид,'И') ФИО,

ROUND(AVG(TO_NUMBER(н_ведомости.оценка)),2) Ср_оценка

FROM н_ведомости, н_ученики

WHERE н_ведомости.члвк_ид=н_ученики.члвк_ид

AND SYSDATE BETWEEN н_ученики.начало AND н_ученики.конец

AND н_ученики.состояние='утвержден'

AND н_ведомости.оценка IN('5','4','3','2')

AND н_ведомости.состояние='актуальна'

AND н_ученики.группа='3100'

GROUP BY н_ведомости.члвк_ид;

29) Выполнить предыдущий запрос используя для получения номера, фамилии, имени и отчества студента не функцию Человек, а текстовую строку, полученную из таблицы Н_ЛЮДИ примерно так, как формировался запрос 8.

SELECT н_ведомости.члвк_ид номер,

н_люди.фамилия||' '||н_люди.имя||' '||н_люди.отчество||' '  ФИО,

ROUND(AVG(TO_NUMBER(н_ведомости.оценка)),2) Ср_оценка

FROM н_ведомости, н_ученики, н_люди

WHERE н_ведомости.члвк_ид=н_ученики.члвк_ид

AND SYSDATE BETWEEN н_ученики.начало AND н_ученики.конец

AND н_ученики.состояние='утвержден'

AND н_люди.ид=н_ведомости.члвк_ид

AND н_ведомости.оценка IN('5','4','3','2')

AND н_ведомости.состояние='актуальна'

AND н_ученики.группа='3100'

GROUP BY н_ведомости.члвк_ид, н_люди.фамилия, н_люди.имя, н_люди.отчество;

30) Составить запрос, позволяющий получить таблицу направлений (специальностей и специализаций), имеющую следующий вид:

 

 

SELECT н_направления_специал.ид, н_направления_специал.уровень, н_направления_специал.дата_гос,

н_напр_спец.код_напрспец ||'  '|| н_напр_спец.наименование "Направление / Специальность",

н_типы_стандартов.наименование "Тип стандарта"

FROM н_направления_специал

JOIN н_напр_спец ON н_направления_специал.нс_ид=н_напр_спец.ид

JOIN н_типы_стандартов ON н_направления_специал.тс_ид=н_типы_стандартов.ид;

31) Преобразовать предыдущий запрос так, чтобы в получаемой таблице перед столбцом «Тип стандарта» появился столбец «Квалификация»

SELECT н_направления_специал.ид, н_направления_специал.уровень, н_направления_специал.дата_гос,

н_напр_спец.код_напрспец ||'  '|| н_напр_спец.наименование "Направление / Специальность",

н_квалификации.наименование "Квалификация", н_типы_стандартов.наименование "Тип стандарта"

FROM н_направления_специал

JOIN н_напр_спец ON н_направления_специал.нс_ид=н_напр_спец.ид

JOIN н_типы_стандартов ON н_направления_специал.тс_ид=н_типы_стандартов.ид

JOIN н_квалификации ON н_направления_специал.квал_ид=н_квалификации.ид;

32) Получить список студентов, зачисленных первого сентября позапрошлого учебного года на первый курс очной формы обучения специальности 220100. В результат включить:

§         номер группы,

§         номер, фамилию, имя и отчество студента,

§         номер и состояние пункта приказа,

§         признак, характеризующий состояние студента,

§         дату конца действия этого пункта,

Результат упорядочить по номеру группы и фамилии.

 

SELECT v.группа, v.члвк_ид, l.фамилия, l.имя, l.отчество,

       v.п_пркок_ид, v.состояние, v.признак, v.конец

FROM н_ученики v,

     н_люди l,

     н_планы p,

     н_напр_спец n,

     н_направления_специал ns,

     н_формы_обучения f

WHERE l.ид=v.члвк_ид

AND ns.ид=p.напс_ид

AND p.ид=v.план_ид

AND ns.нс_ид=n.ид

AND p.фо_ид=f.ид

AND f.наименование='Очная'

AND p.курс=1

AND n.код_напрспец=230101

AND p.учебный_год='2005/2006'

AND v.начало='1.9.2005'

ORDER BY v.группа, l.фамилия;

33) Получить список студентов, по условиям п. 32, сохранив в нем только те строки, в которых состояние пунктами приказа равно 'утвержден'.

 

SELECT v.группа, v.члвк_ид, l.фамилия, l.имя, l.отчество, v.п_пркок_ид, v.состояние, v.признак, v.конец

FROM н_ученики v,  н_люди l, н_планы p, н_напр_спец n,  н_направления_специал ns,

     н_формы_обучения f

WHERE l.ид=v.члвк_ид

AND ns.ид=p.напс_ид

AND p.ид=v.план_ид

AND ns.нс_ид=n.ид

AND p.фо_ид=f.ид

AND f.наименование='Очная'

AND p.курс=1

AND n.код_напрспец=230101

AND p.учебный_год='2006/2007'

AND v.начало='1.9.2006'

AND v.состояние='утвержден'

ORDER BY v.группа, l.фамилия;

34) Получить список студентов по условиям п. 33, но только на конец учебного года (31 августа).

SELECT v.группа, v.члвк_ид, l.фамилия, l.имя, l.отчество,

       v.п_пркок_ид, v.состояние, v.признак, v.конец

FROM н_ученики v,

     н_люди l,

     н_планы p,

     н_напр_спец n,

     н_направления_специал ns,

     н_формы_обучения f

WHERE l.ид=v.члвк_ид

AND ns.ид=p.напс_ид

AND p.ид=v.план_ид

AND ns.нс_ид=n.ид

AND p.фо_ид=f.ид

AND f.наименование='Очная'

AND p.курс=1

AND n.код_напрспец=230101

AND p.учебный_год='2006/2007'

AND v.начало='1.9.2006'

AND v.конец='31.08.2007'

AND v.состояние='утвержден'

ORDER BY v.группа, l.фамилия;

 

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

Результат упорядочить по номеру группы и фамилии.

SELECT v.группа, v.члвк_ид, l.фамилия, l.имя, l.отчество

FROM н_ученики v,

     н_люди l,

     н_планы p,

     н_напр_спец n,

     н_направления_специал ns,

     н_формы_обучения f

WHERE l.ид=v.члвк_ид

AND ns.ид=p.напс_ид

AND p.ид=v.план_ид

AND ns.нс_ид=n.ид

AND p.фо_ид=f.ид

AND f.наименование=' Очно-заочная(вечерняя)'

AND p.курс=1

AND n.код_напрспец=230101

AND p.учебный_год='2006/2007'

AND v.начало='1.9.2006'

AND v.конец='31.08.2007'

ORDER BY v.группа, l.фамилия;

·         Используя опыт, полученный при реализации запроса 23, выведите таблицу со средними оценками всех студентов (Номер, ФИО, Ср_оценка).

SELECT н_ведомости.члвк_ид номер, человек(н_ведомости.члвк_ид,'И') ФИО,

ROUND(AVG(TO_NUMBER(н_ведомости.оценка)),2) Ср_оценка

FROM н_ведомости, н_ученики

WHERE н_ведомости.члвк_ид=н_ученики.члвк_ид

AND н_ученики.состояние='утвержден'

AND н_ведомости.оценка IN('5','4','3','2')

AND н_ведомости.состояние='актуальна'

GROUP BY н_ведомости.члвк_ид;