Надо составить запрос

[tyomitch]

Задали мне домашку - составить запрос, который выводит:

Код: Выделить всё

все записи таблицы EMP m такие, что
  для каждой записи из EMP e, для которой (связка по DEPT.DEPTNO = e.DEPTNO) DEPT.LOC = 'AFRICA' - хотя бы по одной из таких записей для кадого e.DEPTNO было бы e.MGR = m.EMPNO;
  для любой записи из EMP e, для которой DEPT.LOC != 'AFRICA' - для этих записей e.MGR != m.EMPNO.


(Естественно, задание давалось в текстовой форме, это я его уже формализовал)
Помогите! Все мозги иссушил, а не выходит...


Идея у меня была такая, что

Код: Выделить всё

select * from EMP m where
  (select count(*) from EMP e join DEPT d on (d.DEPTNO = e.DEPTNO) where d.LOC = 'AFRICA' and e.MGR = m.EMPNO group by DEPTNO) = (select count(*) from DEPT where LOC = 'AFRICA')
and
  (select count(*) from EMP e join DEPT d on (d.DEPTNO = e.DEPTNO) where d.LOC != 'AFRICA' and e.MGR = m.EMPNO ) = 0


Чего-то всё жаловалось на синтаксис, я уже не помню на что именно, потому что много раз всё переставлял местами и всячески крутил-вертел, а дома той базы нету...


Кто-нибудь знает правильный ответ?

[GSerg]

Rainbow, наверное, и так бы справилась, но лично я не понял описания

Мож исходный текст дашь?

[tyomitch]

"Вывести всех начальников, у которых есть хотя бы один подчинённый в каждом отделе в Африке, и нет подчинённых в других отделах."

[GSerg]

Ну а теперь названия таблиц и полей

[GSerg]

Так...
Вот это вот должно возвращать начальников, у которых есть подчинённые только в Африке...

SELECT Начальники.ФИО FROM Начальники INNER JOIN Подчинённые ON Начальники.ФИО = Подчинённые.ФИОНачальника WHERE Подчинённые.Страна = 'Африка'

Продолжим...

[GSerg]

Чувствую, что нуно выбрать начальников этим запросом, выбрать другим distinct-запросом те отделы, которые в Африке, и потом пересечь их
Команду IN я знаю, а вот "ALL IN"...

Учиться, учиться и учиться (c)

Rainbow, ты там где?

[tyomitch]

Так...
Вот это вот должно возвращать начальников, у которых есть подчинённые только в Африке...

С этим-то как раз проблем нету.... Да в общем, по частям ни с чем проблем нету. Просто всё вместе настолько громоздко, что никак не стыкуется.

Я вот что думаю: это ведь задание для обычных студентов, а не хитро***ых извращенцев. Наверняка есть какая-то фишка, которую я не знаю и которая помогла бы избавиться от громоздкости трёх вложенных запросов?

[GSerg]

Есть операторы пересечения и объединения... Есть IN... Можно делать SELECT * FROM (SELECT *FROM ...)... Чего там ещё...

[tyomitch]

Вот, идея возникла - выбрать отделы в Африке, в которых у него нет подчинённых, и проверить на пустоту. Т.е.

Код: Выделить всё

select * from EMP m where not exists
  (select * from DEPT d where d.LOC = 'AFRICA' and not exists (select * from EMP e where d.DEPTNO = e.DEPTNO and e.MGR = m.EMPNO))
and not exists
  (select * from EMP e join DEPT d on (d.DEPTNO = e.DEPTNO) where d.LOC != 'AFRICA' and e.MGR = m.EMPNO)


Это правильно?

[GSerg]

Это правильно по логике

Так, щас попробуем объединить...

[GSerg]

Нет, всё-таки дай список таблиц и полей
А то INNER JOIN непонятно где нужен

[tyomitch]

Скопировал прямо из задания:

Код: Выделить всё

DEPT (Список отделов)
Название колонки        Тип данных      Описание
DEPTNO                  NUMBER(2)       Номер отдела (PK)
DNAME                   VARCHAR2(14)    Наименование отдела
LOC                     VARCHAR2(13)    Местонахождение отдела
EMP (Список штатных сотрудников)
Название колонки        Тип данных      Описание
EMPNO                   NUMBER(4)       Номер сотрудника (PK)
ENAME                   VARCHAR2(10)    Фамилия сотрудника
JOB                     VARCHAR2(9)     Должность сотрудника
MGR                     NUMBER(4)       Номер начальника данного сотрудника (FK на колонку EMPNO в этой же таблице)
HIREDATE                DATE            Дата приема на работу
SAL                     NUMBER(7,2)     Размер заработной платы
COMM                    NUMBER(7,2)     Размер комиссионных (NULL, если сотрудник не получает комиссионные)
DEPTNO                  NUMBER(2)       Номер отдела, в котором работает данный сотрудник (FK на колонку DEPTNO в таблице DEPT)


[GSerg]

Чё-то всякий бред получается...
Давай Rainbow подождём

[Ennor]

"Вывести всех начальников, у которых есть хотя бы один подчинённый в каждом отделе в Африке, и нет подчинённых в других отделах."

Это точно весь текст? Как-то слишком сложно получается. Далее, "и нет подчиненных в других отделах" - это как понимать, что у него в подчинении только африканские отделы, или что у него нет отделов, где нет африканцев? Уточни, Темыч...

Насчет диалекта, то ли я пропустил, то ли что еще, но версия SQL не указана. Я так понимаю, это Аксесс? Да, видимо, придется отложить до прихода Rainbow, а то я бы сейчас наваял на транзакте, что база в ступор бы вошла . Тем не менее, подумаю, конечно...

Кстати, а в Аксессе есть такая фишка, как HAVING? Здесь бы пригодилась...

[GSerg]

Having - стандартная в SQL...

[tyomitch]

Это точно весь текст?

Вообще, я по памяти набивал... Но вроде я склероза за собой не замечал

Как-то слишком сложно получается. Далее, "и нет подчиненных в других отделах" - это как понимать, что у него в подчинении только африканские отделы, или что у него нет отделов, где нет африканцев? Уточни, Темыч...

Местоположение есть только у отделов, не у работников.
Понимать надо "у него нет подчинённых в отделах вне Африки".

Насчет диалекта, то ли я пропустил, то ли что еще, но версия SQL не указана. Я так понимаю, это Аксесс?

Это Оракл, но афаик там какой-то стандартный SQL, 92-ой, что ли... Есть в природе 92-ой? Если есть, значит он

Кстати, а в Аксессе есть такая фишка, как HAVING? Здесь бы пригодилась...

Есть. Как она может пригодиться?

[Sedge]

С ораклом не работал. Но под MSSQL может сработать что-то похожее на:

Код: Выделить всё

SELECT ENAME FROM EMP AS EMP_MAIN WHERE
   (SELECT COUNT(*) FROM EMP INNER JOIN DEPT ON DEPT.DEPTNO=EMP.DEPTNO
      WHERE DEPT.LOC = 'Африка' AND EMP.MGR=EMP_MAIN.EMPNO) > 0
   AND
   (SELECT COUNT(*) FROM EMP INNER JOIN DEPT ON DEPT.DEPTNO=EMP.DEPTNO
      WHERE DEPT.LOC <> 'Африка' AND EMP.MGR=EMP_MAIN.EMPNO) = 0


[tyomitch]

Не канает. У тебя выбираются начальники, у которых есть хотя бы один подчинённый в Африке. Мне надо, чтобы выбирались те, у которых есть по подчинённому в каждом отделе в Африке.

[Ennor]

Уже понял - varchar2 есть только в Огакуле. И почему я сразу не заметил...

ЕМНИП, в Оракле нет джойнов. То есть они там есть, но не в стандартном MS-овском виде. Ладно, будем смотреть...

[Rainbow]

Я решала проблему последовательно. Посмотрите, тот ли результат получился...

Предоставляю вам квери, а вы уж смотрите - то ли хранимую процедуру дейлайте, то ли склеивайте их - как-нибудь...

1) Выбираем африканские отделы (AFR_DEPT):

Код: Выделить всё

SELECT *
FROM DEPT
WHERE DName='AFR'

2) Выбираем всех начальников, у кого есть какие-нибудь сотрудники (Cheef_Dept)

Код: Выделить всё

SELECT DISTINCT [MGR], [DeptNo]
FROM EMP
WHERE (MGR<>0)

3) выбираем всех начальников и их отношение к африканским отделам (CHEEF_IN_AFR)

Код: Выделить всё

SELECT [AFR_DEPT].[DeptNo], [Cheef_Dept].[MGR]
FROM AFR_DEPT RIGHT JOIN Cheef_Dept ON [AFR_DEPT].[DeptNo]=[Cheef_Dept].[DeptNo]

4) теперь считаем, у кого сколько сотрудников в африканских отделах (AFR_COUNT)

Код: Выделить всё

SELECT [MGR], Count([DeptNo]) AS cnt
FROM CHEEF_IN_AFR
GROUP BY [MGR]

5) последний штрих - смотрим, у кого количество совпадает с количеством африканских отделов

Код: Выделить всё

SELECT MGR
FROM AFR_COUNT
WHERE cnt = (select count(DeptNo) from AFR_DEPT)

P.S. если что-то не заработает в самих кверях - значит, я перепутала названия (я свою структуру зачем-то сделала, а потом пыталась адаптировать к данной)
P.P.S. посмотрев на написанное есть подозрение, что можно оптимизировать (может, лишних запросов понаписала), но как - времени думать нет.

[alibek]

Ну вот, пришла Радуга и на все ответила
Попробую придумать альтернативное решение и не смотреть на ее вариант .

Итак.
Вывести всех начальников (1)
у которых есть хотя бы один подчиненный (2)
в каждом отделе Африки (3)
и нет подчиненных в других отделах (4)

Т.е. если я правильно понял, у выбранного начальника должны быть подчиненные в _каждом_ отделе Африки? Или в любом из отделов Африки?

Будем считать, что первое.

Для проверок наличия/отсутствия я бы использовал EXISTS.

Получаем (предварительно):

Код: Выделить всё

select M.EMPNO
from EMP M
where 0=0
  and not exists
    (
      select E.EMPNO
      from EMP E, DEPT D
      where E.MGR = M.EMPNO
        and E.DEPTNO = D.DEPTNO
        and D.LOC <> 'AFRICA'
    )
  and exists
    (
      select
      from EMP E, DEPT D
      where E.MGR = M.EMPNO
        and E.DEPTNO = D.DEPTNO
        and D.LOC = 'AFRICA'
    )


Это выборка всех начальников, у которых есть хотя бы один подчиненный в Африке и нет ни одного подчиненного не в Африке.
Теперь надо показать только тех начальников из этого списка, у которых есть подчиненные в _каждом_ отделе Африки.

Код: Выделить всё

select D.DEPTNO as Dpt                 -- номер отдела
     , count(E.EMPNO) as Cnt           -- сколько сотрудников в отделе (NULL не включается)
from DEPT D, EMP E
where E.MGR = M.EMPNO                  -- M.EMPNO - из вышестящего запроса
  and D.DEPTNO(+) = E.DEPTNO           -- выбрать ВСЕ отделы
  and D.LOC = 'AFRICA'                 -- все отделы в Африке
group by D.DEPTNO


(+) это равносильно LEFT JOIN

Код: Выделить всё

select M.EMPNO
from EMP M
where 0=0
  and not exists
    (
      select E.EMPNO
      from EMP E, DEPT D
      where E.MGR = M.EMPNO
        and E.DEPTNO = D.DEPTNO
        and D.LOC <> 'AFRICA'
    )
  and exists
    (
      select
      from EMP E, DEPT D
      where E.MGR = M.EMPNO
        and E.DEPTNO = D.DEPTNO
        and D.LOC = 'AFRICA'
    )
  and not exists
    (
      select DPT_CNT.DPT
      from
        (
          select D.DEPTNO as DPT
               , count(E.EMPNO) as CNT
          from DEPT D, EMP E
          where E.MGR = M.EMPNO
            and D.DEPTNO(+) = E.DEPTNO
            and D.LOC = 'AFRICA'
          group by D.DEPTNO
        ) DPT_CNT
      where DPT_CNT.CNT = 0
    )


Помоему так...
Что-то конструкция слишком сложная, как бы не накосячить А проверить не на чем.
tyomitch, как проверишь, напишешь?

[Konst_One]

Если это Оракл, то можно посмотреть оператор INTERSECT, может все в одном запросе тогда получится

[Ennor]

Не, ну а че гадать-то? Проще тогда уж сразу сюда:

http://www.sql.ru/forum/actualtopics.aspx?bid=3

[tyomitch]

tyomitch, как проверишь, напишешь?

Вот, идея возникла - выбрать отделы в Африке, в которых у него нет подчинённых, и проверить на пустоту. Т.е.

Код: Выделить всё

select * from EMP m where not exists
  (select * from DEPT d where d.LOC = 'AFRICA' and not exists (select * from EMP e where d.DEPTNO = e.DEPTNO and e.MGR = m.EMPNO))
and not exists
  (select * from EMP e join DEPT d on (d.DEPTNO = e.DEPTNO) where d.LOC != 'AFRICA' and e.MGR = m.EMPNO)


Я проверил этот запрос, и он работал. Без count(*) и без группировки
Ваши с Rainbow не проверял - сорри Но чего-то они уж больно наворочанные...