Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Помогите подготовиться к ГОСам.  [new]
fedya_777
Member

Откуда:
Сообщений: 87
Завтра уже ГОСы, будут задачки по SQL, а я его забыл напрочь. Когда-то запросы очень шустро писал, а теперь вот туплю.
Помогите пожалуйста подготовиться. Я тут задам в этой темке несколько вопросо.
Заранее очень вам благодарен.

Первый вопрос я задал на otvet.mail.ru
http://otvet.mail.ru/question/31137760/
25 окт 09, 17:44    [7835690]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подготовиться к ГОСам.  [new]
fedya_777
Member

Откуда:
Сообщений: 87
Ещё вопрос по той же картинке, что и в вопросе на мэйле.
4) Найти преподавателей, на экзаменах у которых были получены все виды оценок.

Я сделал так. Правильно? Оптимаьлно?

SELECT R3.[ФИО ПРЕПОДАВАТЕЛЯ]
FROM R1,R3
WHERE R3.[ДИСЦИПЛИНА] = R1.[ДИСЦИПЛИНА]
AND R1.[ОЦЕНКА] = 5
AND R1.[ОЦЕНКА] = 4
AND R1.[ОЦЕНКА] = 3
AND R1.[ОЦЕНКА] = 2
AND R1.[ОЦЕНКА] = 1;
25 окт 09, 17:48    [7835697]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подготовиться к ГОСам.  [new]
BoxTer
Member

Откуда:
Сообщений: 27
Ну хотя б так
SELECT R3.[ФИО ПРЕПОДАВАТЕЛЯ]
FROM R1,R3
WHERE R3.[ДИСЦИПЛИНА] = R1.[ДИСЦИПЛИНА]
AND R1.[ОЦЕНКА] in(1,2,3,4,5)
25 окт 09, 18:01    [7835722]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подготовиться к ГОСам.  [new]
iljy
Member

Откуда:
Сообщений: 8711
fedya_777
Ещё вопрос по той же картинке, что и в вопросе на мэйле.
4) Найти преподавателей, на экзаменах у которых были получены все виды оценок.

Я сделал так. Правильно? Оптимаьлно?

SELECT R3.[ФИО ПРЕПОДАВАТЕЛЯ]
FROM R1,R3
WHERE R3.[ДИСЦИПЛИНА] = R1.[ДИСЦИПЛИНА]
AND R1.[ОЦЕНКА] = 5
AND R1.[ОЦЕНКА] = 4
AND R1.[ОЦЕНКА] = 3
AND R1.[ОЦЕНКА] = 2
AND R1.[ОЦЕНКА] = 1;

Ну вот вообще неправильно. Чему должна быть равна R1.[ОЦЕНКА] чтобы выполнилось условие
AND R1.[ОЦЕНКА] = 5
AND R1.[ОЦЕНКА] = 4
AND R1.[ОЦЕНКА] = 3
AND R1.[ОЦЕНКА] = 2
AND R1.[ОЦЕНКА] = 1
?
Тут нужна группировка по имени препода и подсчет количества различных оценок.
25 окт 09, 18:05    [7835729]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подготовиться к ГОСам.  [new]
fedya_777
Member

Откуда:
Сообщений: 87
BoxTer, так не правильно. Потому что надо найти преподов у которого все виды оценок есть, а не какая-то из множества (1,2,3,4,5)
iljy
Ну вот вообще неправильно. Чему должна быть равна R1.[ОЦЕНКА] чтобы выполнилось условие
AND R1.[ОЦЕНКА] = 5
AND R1.[ОЦЕНКА] = 4
AND R1.[ОЦЕНКА] = 3
AND R1.[ОЦЕНКА] = 2
AND R1.[ОЦЕНКА] = 1
?
Тут нужна группировка по имени препода и подсчет количества различных оценок.

Так почему не правильно? А как правильно?
А насчет первого вопроса, который в мэйле? Я там тоже думаю, что не правильно.
25 окт 09, 18:16    [7835746]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подготовиться к ГОСам.  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
fedya_777
Ещё вопрос по той же картинке, что и в вопросе на мэйле.
4) Найти преподавателей, на экзаменах у которых были получены все виды оценок.
Надо написать запрос, выбирающий таких преподавателей,
для которых не существует такого вида оценок, который у этого преподавателя не существует.
Эту мою фразу можно почти дословно перевести командами SQL (SELECT, WHERE, NOT EXISTS и т.п.).
И счастье придёт!
25 окт 09, 18:20    [7835753]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подготовиться к ГОСам.  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
fedya_777
BoxTer, так не правильно. Потому что надо найти преподов у которого все виды оценок есть, а не какая-то из множества (1,2,3,4,5)
iljy
Ну вот вообще неправильно. Чему должна быть равна R1.[ОЦЕНКА] чтобы выполнилось условие
AND R1.[ОЦЕНКА] = 5
AND R1.[ОЦЕНКА] = 4
AND R1.[ОЦЕНКА] = 3
AND R1.[ОЦЕНКА] = 2
AND R1.[ОЦЕНКА] = 1
?
Тут нужна группировка по имени препода и подсчет количества различных оценок.

Так почему не правильно? А как правильно?
А насчет первого вопроса, который в мэйле? Я там тоже думаю, что не правильно.
подумайте, ну как R1.[ОЦЕНКА] может одновременно равняться и 1, и 2, и 3, и 4, и 5?!
25 окт 09, 18:22    [7835755]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подготовиться к ГОСам.  [new]
fedya_777
Member

Откуда:
Сообщений: 87
iap, да, вы правы, оценка не может равняться сразу нескольким знаячениям. Затупил я :)

А вот так правильно?

SELECT R3.[ФИО ПРЕПОДАВАТЕЛЯ]
FROM R1,R3
WHERE R3.[ДИСЦИПЛИНА] = R1.[ДИСЦИПЛИНА]
AND NOT EXIST 
(SELECT R1.[ОЦЕНКА]
FROM R1
WHERE R1.[ОЦЕНКА] NOT IN (1,2,3,4,5));

Просто вьехать сразу сложновато. Как и, например, зная, для чего нужен Photoshop не сразу сможешь нарисовать красивую картину. Вот я и практикуюсь. Но башка уже не варит после Сетей, промышленных микропроцессорных сетей и спец аппаратного обеспечения финансовых систем.
25 окт 09, 18:35    [7835778]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подготовиться к ГОСам.  [new]
iljy
Member

Откуда:
Сообщений: 8711
fedya_777
iap, да, вы правы, оценка не может равняться сразу нескольким знаячениям. Затупил я :)

А вот так правильно?

SELECT R3.[ФИО ПРЕПОДАВАТЕЛЯ]
FROM R1,R3
WHERE R3.[ДИСЦИПЛИНА] = R1.[ДИСЦИПЛИНА]
AND NOT EXIST 
(SELECT R1.[ОЦЕНКА]
FROM R1
WHERE R1.[ОЦЕНКА] NOT IN (1,2,3,4,5));

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

Так тоже неправильно. Что проверит подзапрос
(SELECT R1.[ОЦЕНКА]
FROM R1
WHERE R1.[ОЦЕНКА] NOT IN (1,2,3,4,5))
? что нет оценок, отличных от 1,2,3,4,5, а я думаю это и так очевидно. Если вам нужно найти препода, у которого есть ВСЕ 5 оценок - тогда так:
SELECT R3.[ФИО ПРЕПОДАВАТЕЛЯ]
FROM R1 join R3 on R3.[ДИСЦИПЛИНА] = R1.[ДИСЦИПЛИНА]
GROUP BY R3.[ФИО ПРЕПОДАВАТЕЛЯ]
HAVING count(distinct R1.[ОЦЕНКА]) = 5
Если надо препода, у которго есть все оченки, встречающиеся в базе - тогда так:
SELECT R3.[ФИО ПРЕПОДАВАТЕЛЯ]
FROM R1 join R3 on R3.[ДИСЦИПЛИНА] = R1.[ДИСЦИПЛИНА]
GROUP BY R3.[ФИО ПРЕПОДАВАТЕЛЯ]
HAVING count(distinct R1.[ОЦЕНКА]) = (SELECT count(distinct [ОЦЕНКА]) FROM R1)
Ну или явно перевести слова jap на SQL:
SELECT R3.[ФИО ПРЕПОДАВАТЕЛЯ]
FROM R3
WHERE NOT EXISTS
(
  SELECT * FROM R1
  WHERE [ОЦЕНКА] NOT IN
  (SELECT [ОЦЕНКА] FROM R1
   WHERE R3.[ДИСЦИПЛИНА] = [ДИСЦИПЛИНА]
  )
)
либо чуть модифицируя
SELECT R3.[ФИО ПРЕПОДАВАТЕЛЯ]
FROM R3
WHERE 
  (SELECT count(distinct [ОЦЕНКА]) FROM R1)
      =
  (SELECT count(distinct [ОЦЕНКА]) FROM R1
   WHERE R3.[ДИСЦИПЛИНА] = [ДИСЦИПЛИНА])
25 окт 09, 19:11    [7835821]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подготовиться к ГОСам.  [new]
fedya_777
Member

Откуда:
Сообщений: 87
iljy, спасибо огромное.
А как быть с первым вопросом, который в ссылке в первом посте?

Щас побыстренькому MS SQL поставил, чтоб не в голове думать, а реально смотреть результаты.
25 окт 09, 19:31    [7835860]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подготовиться к ГОСам.  [new]
iljy
Member

Откуда:
Сообщений: 8711
fedya_777,

сформулируйте задачу полностью, будет половина решения. Нам надо найти факультеты, у которых все студенты во всех группах сдали все предметы. Теперь пишем
SELECT R5.[ФАКУЛЬТЕТ] 
FROM R5 -- все факультеты
	join R2 on R5.[ГРУППА] = R2.[ГРУППА] -- все студенты во всех группах факультета
	join R4 on R5.[ГРУППА] = R4.[ГРУППА] -- по всем дисциплинам факультета
	left join R1 on R2.[ФИО] = R1.[ФИО] AND R1.[ДИСЦИПЛИНА] = R4.[ДИСЦИПЛИНА] -- оценки (может не быть)
GROUP BY R5.[ФАКУЛЬТЕТ] -- проверяем для факультета
HAVING COUNT(case when R1.[ОЦЕНКА] > 2 then 1 end) = count(*) -- что количество положительных оценок совпадает с
                             --количеством всех возможных сочетаний студент-дисциплина
/*
Вариант условия:
COUNT(case when isnull(R1.[ОЦЕНКА],2) <= 2 then 1 end) = 0 -- количество отсутствующих либо отрицательных оценок = 0
*/
25 окт 09, 19:49    [7835888]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подготовиться к ГОСам.  [new]
fedya_777
Member

Откуда:
Сообщений: 87
iljy,
никогда не пользовался этими join, left join.
Это ж насколько я помню тоже самое:

select * 
from T1, T2
where T1.A = T2.A
и
select *
from T2 join T1 on T1.A = T2.A
Я прав?
А что такое left в вашем случае?

Вы сдорово помогаете вспомнить и подумать над другими вариантами.
25 окт 09, 20:11    [7835942]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подготовиться к ГОСам.  [new]
iljy
Member

Откуда:
Сообщений: 8711
fedya_777,

про join правы, а left - посмотрите описание и примеры. В старой нотации писалось так *=. Или =*, уже не помню
25 окт 09, 20:25    [7835971]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подготовиться к ГОСам.  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
iljy
fedya_777,

про join правы, а left - посмотрите описание и примеры. В старой нотации писалось так *=. Или =*, уже не помню
*=
если это важно
25 окт 09, 20:32    [7835985]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подготовиться к ГОСам.  [new]
fedya_777
Member

Откуда:
Сообщений: 87
HAVING COUNT(case when R1.[ОЦЕНКА] > 2 then 1 end) = count(*)

Не могу нормально въехать в этот CASE WHEN.
Где оно используется? Какое значение возвращает?

Например можно вот так делать?
SELECT *
FROM A, B
WHERE (CASE WHEN 100>101 THEN 0 END); 
Т.е. вот тут можно так использовать, но вывода никогда не произойдет?
Типа если 100 больше 101 то возвратить 0, так? Почему тогда в вашем примере он как-то считается COUNT-ом?
Если бы например в примере было так:
HAVING COUNT(case when R1.[ОЦЕНКА] > 2 then 5 end) = count(*)
25 окт 09, 22:28    [7836173]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подготовиться к ГОСам.  [new]
fedya_777
Member

Откуда:
Сообщений: 87
А как подругому можно написать это задание, без CASE?
25 окт 09, 22:50    [7836211]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подготовиться к ГОСам.  [new]
iljy
Member

Откуда:
Сообщений: 8711
fedya_777,

а посмотреть в хелпе (в том же MSDN) про case и count? Там все подробно и по-русски написано. А если что непонятно - можно и тут спросить.

Можно конечно
SELECT DISTINCT R5.[ФАКУЛЬТЕТ] 
FROM R5
WHERE NOT EXISTS
(SELECT * FROM R2
	JOIN R4 ON R2.[ГРУППА] = R4.[ГРУППА]
	LEFT JOIN R1 ON R2.[ФИО] = R1.[ФИО] AND R1.[ДИСЦИПЛИНА] = R4.[ДИСЦИПЛИНА]
 WHERE R5.[ГРУППА] = R2.[ГРУППА] AND isnull(R1.[ОЦЕНКА],2) <= 2
)
А зачем?
25 окт 09, 23:46    [7836271]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подготовиться к ГОСам.  [new]
fedya_777
Member

Откуда:
Сообщений: 87
Что такое count я хорошо знаю.
Я в MSDN-e и смотрел, только там не прозрачно написано и, именно в таком контексте как у вас, я не нашел объяснения. Как я понимаю это как в Си просто выбирает вариант действий, при определенном ключе. Так у вас то этого ключа как раз то и нет. Вот я и не понял нифига.
25 окт 09, 23:51    [7836282]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подготовиться к ГОСам.  [new]
iljy
Member

Откуда:
Сообщений: 8711
fedya_777,

case - выбирает вариант, а если ни одного не подходит - возвращает NULL. А count(Expression) считает количество значений Expression, отличных от NULL.
26 окт 09, 00:05    [7836300]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подготовиться к ГОСам.  [new]
fedya_777
Member

Откуда:
Сообщений: 87
iljy,

isnull(R1.[ОЦЕНКА],2) <= 2
А зачем тут isnull?

Можно ведь так сделать?
R1.[ОЦЕНКА]<=2 OR R1.[ОЦЕНКА] IS NULL
26 окт 09, 01:58    [7836374]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подготовиться к ГОСам.  [new]
iljy
Member

Откуда:
Сообщений: 8711
fedya_777
iljy,

isnull(R1.[ОЦЕНКА],2) <= 2
А зачем тут isnull?

Можно ведь так сделать?
R1.[ОЦЕНКА]<=2 OR R1.[ОЦЕНКА] IS NULL

Можно. Но с ISNULL короче.
26 окт 09, 10:04    [7836906]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подготовиться к ГОСам.  [new]
fedya_777
Member

Откуда:
Сообщений: 87
Как оказалось ГОСы не сегодня. Сегодня другая группа сдавала.
У меня тут вопрос такой возник.
А DISTINCT в SELECT как-то замедляет работу запроса, если вставляется там, где и так будут только неповторяющиеся строки выводиться?
Вопрос к тому что почему бы не поставить, если не лень лишнее писать и на всякий случай.
27 окт 09, 02:53    [7841725]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подготовиться к ГОСам.  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
fedya_777
А DISTINCT в SELECT как-то замедляет работу запроса, если вставляется там, где и так будут только неповторяющиеся строки выводиться?
Очень замедляет. Ставьте DISTINCT в самом крайнем случае.
27 окт 09, 09:04    [7841958]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подготовиться к ГОСам.  [new]
fedya_777
Member

Откуда:
Сообщений: 87
А вот ещё такая база:
Есть таблицы
R1(ФИО студента, группа, Курс)
R2(группа, выпускающая кафедра)
R3(Название работы, ФИО студента, представляющая кафедра)
R4(кафедра, факультет)

Надо составить такие запросы, выводящие:
а) Студентов, представивших работы более чем от одной кафедры
б) Кафедры, на которых подготовили работы студенты 3-го курса
в) Группы, не все студенты которых подготовили работы
г) Работы, совместно подготовленные студентами различных факультетов

Помогите решить. И как сделать их наиболее оптимальными?
27 окт 09, 17:23    [7845935]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подготовиться к ГОСам.  [new]
Glory
Member

Откуда:
Сообщений: 104760
а) http://www.firststeps.ru/sql/r.php?7
б) в) г) http://www.firststeps.ru/sql/r.php?9, http://www.firststeps.ru/sql/r.php?10, http://www.firststeps.ru/sql/r.php?11
27 окт 09, 17:34    [7846032]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить