В начало

Лабораторная работа.

Вложенные подзапросы

 

Задание: Выявить людей с одинаковыми фамилиями, именами и отчествами, но разными номерами. Список должен содержать упорядоченные строки с уникальными сочетаниями номера, фамилии, имени и отчества.

 

select н_люди.ид, н_люди.фамилия, н_люди.имя, н_люди.отчество from н_люди, н_люди копия

where н_люди.ид!=копия.ид

and н_люди.фамилия=копия.фамилия

and н_люди.имя=копия.имя

and н_люди.отчество=копия.отчество

order by фамилия, имя, отчество;

 

Вложенные подзапросы

Преобразовать запрос, чтобы во фразе FROM осталось соединение только тех таблиц, столбцы которых входят в списки фраз SELECT и ORDER BY. Остальные таблицы, данные из которых нужны для отбора нужных строк результата, необходимо разместить во фразе WHERE, не используя их соединений. Для организации подзапросов использовать предикат IN (проверка на принадлежность).

 

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

from н_ученики u, н_люди v where

u.начало='01-09-2007'

and v.ид=u.члвк_ид

and u.состояние='утвержден'

and u.план_ид in

  (select p.ид from н_планы p

      where p.курс=1

      and p.ФО_ид in

        (select f.ид

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

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

      and p.напс_ид in

        (select x.ид

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

          where x.нс_ид in

            (select n.ид

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

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

            )

        )

  )

order by u.группа, v.фамилия

 

Преобразовать предыдущий запрос, используя для организации подзапросов предикат EXISTS (проверка на существование).

 

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

from н_ученики u, н_люди v where

u.начало='01-09-2007'

and v.ид=u.члвк_ид

and u.состояние='утвержден'

and exists

  (select * from н_планы p

      where p.курс=1

      and p.ид=u.план_ид

      and exists

        (select *

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

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

          and p.ФО_ид=f.ид

        )

      and exists

        (select *

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

          where p.напс_ид=x.ид

          and exists

            (select n.ид

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

              where x.нс_ид=n.ид

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

            )

        )

  )

order by u.группа, v.фамилия

 

Преобразовать запрос п. 29 так, чтобы во фразе FROM осталось соединение только тех таблиц, столбцы которых входят в списки фраз SELECT и GROUP BY. Остальные таблицы, данные из которых нужны для отбора нужных строк результата, необходимо разместить во фразе WHERE.

 

select z.ид, (z.фамилия||' '||z.имя||' '||z.отчество) ФИО,

  Round(AVG(TO_NUMBER(v.оценка)),2) Ср_оценка

  From н_ведомости v, н_люди z

  where v.члвк_ид=z.ид

  and v.оценка in('5','4','3','2')

  and v.состояние='актуальна'

  and z.ид in

    (select u.члвк_ид

      from н_ученики u

      where группа='1105'

    )

  group by z.ид, z.фамилия, z.имя, z.отчество;

 

Вывести упорядоченный по ФИО список людей (с фамилиями, начинающимися на "До"), не являющихся или не являвшихся студентами СПбГУИТМО (т.е. данные о которых отсутствуют в таблице Н_УЧЕНИКИ). Составить не менее двух вариантов запросов с условиями IN и EXISTS.

 

select *

from н_люди

where Фамилия like 'До%'

and not exists

  (

    select * from н_ученики

    where н_люди.ид=н_ученики.члвк_ид

  )

 

 

select *

from н_люди

where Фамилия like 'До%'

and н_люди.ид not in

  (

    select члвк_ид from н_ученики

  )

 

Выполнить запрос п. 35, используя для его реализации вложенный подзапрос, а не соединение таблицы со своей копией (в запросе нельзя использовать DISTINCT).

select u.ид, u.фамилия, u.имя, u.отчество from н_люди u

where exists

  (

    select * from н_люди v

    where v.фамилия=u.фамилия

    and v.имя=u.имя

    and v.отчество=u.отчество

    and v.ид != u.ид

  )

order by u.фамилия, u.имя, u.отчество;

 

 

Объединение запросов

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

 

 

 

Представления

 

Создание таблиц и заполнение их данными

 

select 'круглых отличников', count(distinct члвк_ид) Кол_во from н_ведомости

where члвк_ид not in

  (

    select члвк_ид from н_ведомости

    where оценка not in ('5','зачет','осв')

  )

  UNION

select 'учеников без троек', count(distinct члвк_ид) Кол_во from н_ведомости

where члвк_ид not in

  (

    select члвк_ид from н_ведомости

    where оценка='3'

  )

UNION

select 'круглых троечников', count(distinct члвк_ид) Кол_во from н_ведомости

where члвк_ид not in

  (

    select члвк_ид from н_ведомости

    where оценка!='3' and оценка!='зачет' and оценка!='осв'

  )