Я не знаю, почему этот запрос означает это предложение

ProgramBox

Я не знаю, почему этот запрос означает это предложение

Post author:admin
Запись опубликована:20 августа, 2022
Post category:Вопросы по программированию

#sql

Вопрос:

 select distinct S.ID, S.name
from student as S
where not exists (
    (select course_id from course where dept_name = ’Biology’)
    except
    (select T.course_id from takes as T where S.ID = T.ID)
);

В этом запросе в моей книге говорится, что это означает

«Найти всех студентов, которые прослушали все курсы, предлагаемые на факультете биологии»

Таблица выглядит следующим образом:
студент (ID, имя, имя_департамента, tot_cred)
проходит (ID, course_id, sec_id, семестр, год, класс)
курс (course_id, название, имя_департамента, кредиты)

Я думал, что запрос не сработал, потому что в таблице student не было идентификатора курса.

Почему выполнение этого запроса такое же, как описано выше?

1. Вложенный запрос получает все уроки биологии за вычетом тех, которые посещает учащийся. Слева — уроки биологии, которые ученик не посещал. Следовательно, основной запрос гласит: дайте мне всех учеников, у которых нет незанятых уроков биологии. Это DISTINCT излишне, так как, конечно, в таблице учащихся не должно быть повторяющихся учеников. Я должен сказать, что это заставляет меня поставить вашу книгу под сомнение. Заполнение каждого запроса ненужным DISTINCT — типичная проблема новичков.

Ответ №1:

Я думал, что запрос не сработал, потому что в таблице student не было идентификатора курса.

Обратите внимание, что course_id на это никогда не ссылаются, кроме как из table course ( select course_id from course ) или table takes ( select T.course_id from takes as T ) . Запрос никогда не ссылается на student.course_id , поэтому ваш вопрос противоречит вашему непониманию того, что делает запрос.

Запрос немного сбивает с толку, потому что он эффективно использует двойное отрицание. where not exists Предложение более или менее гласит: «найдите всех студентов, где, если мы возьмем набор предлагаемых курсов по биологии и удалим все курсы, которые прослушал студент, результатом будет пустой набор».

 -- Where the following set is empty...
where not exists (
    -- All of the biology courses offered...
    (select course_id from course where dept_name = ’Biology’)

    -- EXCEPT those that the student has taken.
    except
    (select T.course_id from takes as T where S.ID = T.ID)
)

Если мы возьмем все предлагаемые курсы биологии и удалим курсы, которые прослушал студент, и результатом будет пустой набор, единственным возможным объяснением будет то, что студент прослушал все предлагаемые курсы биологии. (Кроме того: также возможно, что не предлагаются курсы биологии, и в этом случае студент все еще прошел все предлагаемые курсы биологии — это называется пустой правдой.)

1. считается, что not exists возвращает истинные значения, когда набор пуст после предложения. Но я до сих пор не знаю, почему в результатах выводятся учащиеся, которые прошли все занятия по биологии.

2. @Kim Внимательно прочитайте ответ и проверьте, что делает запрос. Если идентификаторы курсов для всех классов биологии равны 1, 2 и 3, а учащийся прослушал курсы 1, 2, 3 и 4, что произойдет, если вы возьмете первый набор и удалите из него все элементы, которые также есть во втором наборе?

3. @cdhowie Я знаю, что это пустое множество, но я не знаю, как работает «не существует» и почему результаты распечатывают все курсы по биологии.

4. @Kim NOT EXISTS просто утверждает, что следующий set / подзапрос пуст. Поэтому, если учащийся прошел все уроки биологии, NOT EXISTS (...) предложение оценивается как true, и оцениваемая запись учащегося включается в набор результатов. Результаты не должны распечатывать все курсы биологии, результатами должны быть все учащиеся, которые прошли все курсы биологии.

5. @cdhowie Что произошло бы, если бы были студенты, которые не проходили факультет биологии, но проходили курс в другом отделе?

Ответ №2:

В запросе говорится: «На биологическом факультете не существует курса, который студент не посещал».

Второй подзапрос (после except ) получает все курсы, которые проходит студент. Они удалены из всех курсов на факультете биологии. Итак, если студент прошел все курсы на этом факультете, результатом не будет строк. В противном случае результатом будут строки, которые студент не взял.

Это select distinct сильно вводит в заблуждение. В таблице student не должно быть дубликатов.

Я предпочитаю использовать агрегацию для этих типов запросов:

 select t.student_id
from takes t join
     course c
     on t.course_id = c.course_id 
where c.dept_name = 'Biology'
group by t.student_id
having count(distinct t.course_id) = (select count(*) from course c2 where c2.dept_name = 'Biology');

Для меня (по крайней мере) логика здесь более четко соответствует «студенты прошли все курсы на биологическом факультете».

Метки: почему этот запрос означает это предложение, Я не знаю