Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 Создание пары запросов  [new]
Ольга Семенова
Member

Откуда:
Сообщений: 698
Всем привет
Помогите пожалуйста составить запросы

вот такое задание:
При регистрации абитуриента фиксируются его данные (ФИО отчество), а также выбранная им специальность. Абитуриент сдает три экзамена (физика, математика, русский язык). Результат экзамена оценивается в стобальной системе (от 0 до 100). По каждому экзамену определено минимальное количество баллов, которые должен набрать абитуриент для участия в конкурсе (т.е. если он по какому-либо экзамену получил оценку меньшую минимальной то он в конкурсе уже не участвует).

Количество зачисляемых на специальность абитуриентов определяется квотой (т.е. количеством приемных на ту или иную специальность)

Помогите пожалуйста получить следующие выборки:

1) список ДОПУЩЕННЫХ к конкурсу абитуриентов, ранжированные в порядке убывания суммарного балла.

2) Список зачисленных абитуриентов на каждую специальность, порядковый номер которых в списке из ЗАПРОСА 1 больше квоты на специальность.

3) Количество поступивших на каждую специальность


Структуру БД я для примера заполнила на АКСЕСЕ - см. вложение

К сообщению приложен файл (DB.rar - 13Kb) cкачать
10 июн 07, 16:04    [4254222]     Ответить | Цитировать Сообщить модератору
 Re: Создание пары запросов  [new]
softwarer
Member

Откуда: 127.0.0.1
Сообщений: 63932
Блог
Что-то в воскресенье людей посещают странные мысли, Вы не первая сегодня.... Ольга, как Вы думаете, многие ли бросятся искать и устанавливать Access только ради того, чтобы посмотреть на нарисованное Вами?

1) список ДОПУЩЕННЫХ к конкурсу абитуриентов, ранжированные в порядке убывания суммарного балла.

Давайте идти от простого к сложному.

1. Список абитуриентов получить в состоянии?
2. Добавить к нему специальность абитуриента и необходимый минимум баллов?
3. Оставить только тех, кто этот минимум выполняет?
4. Получить для каждого абитуриента суммарный балл?
5. Отсортировать?

2) Список зачисленных абитуриентов на каждую специальность, порядковый номер которых в списке из ЗАПРОСА 1 больше квоты на специальность.

6. Посмотреть в документации смысл и примеры использования функции ROW_NUMBER?
7. Вытащить из данных специальности еще и квоту на сотрудников?
8. Оставить только тех, кто проходит по квоте? (здесь, думаю, понадобится подзапрос)

3) Количество поступивших на каждую специальность

9. Найти максимальный номер зачисленного для каждой специальности?
10 июн 07, 18:50    [4254381]     Ответить | Цитировать Сообщить модератору
 Re: Создание пары запросов  [new]
Ольга Семенова
Member

Откуда:
Сообщений: 698
первый запрос у меня ВОТ так не проходит

SELECT   a.id_abit, a.fam, a.NAME, a.otch, SUM (r.examres) esum
    FROM abit a, res r
   WHERE a.id_abit = r.id_abit
     AND a.id_abit NOT IN (SELECT DISTINCT r.id_abit FROM res r, exam e
                         WHERE (    (r.examres < e.exammin)
                                AND (r.id_exam = e.id_exam)
                               ))
GROUP BY a.id_abit, a.fam, a.NAME, a.otch
ORDER BY SUM (r.examres) desc
10 июн 07, 19:47    [4254433]     Ответить | Цитировать Сообщить модератору
 Re: Создание пары запросов  [new]
Ольга Семенова
Member

Откуда:
Сообщений: 698
нужна помощь профи

по первому запросу к примеру должен сформироваться список ДОПУЩЕННЫХ к конкурсу абитуриентов, ранжированные в порядке убывания суммарного балла - т.е. если абитуриент по какому-либо экзамену получил оценку меньшую минимальной то он в конкурсе уже не участвует и его в списке нет

Запрос 2
Далее на каждую специальность зачисляются абитуриенты, порядковый номер которых в списке ДОПУЩЕННЫХ к конкурсу (запрос 1) НЕ БОЛЬШЕ КВОТЫ на Специальность
Вот этот список зачисленных и нужно получить
Иными словами Запрос 2 мне нужен для формирования ОТЧЕТА, в котором нужно вывести список Зачисленных абитуриентов по каждой специальности.
10 июн 07, 20:01    [4254438]     Ответить | Цитировать Сообщить модератору
 Re: Создание пары запросов  [new]
Амстердам
Guest
Вот тут читайте...
10 июн 07, 20:04    [4254441]     Ответить | Цитировать Сообщить модератору
 Re: Создание пары запросов  [new]
Ольга Семенова
Member

Откуда:
Сообщений: 698
первый запрос вроде сделала

помогите пожалуйста с 2-ым и 3-им
10 июн 07, 23:32    [4254704]     Ответить | Цитировать Сообщить модератору
 Re: Создание пары запросов  [new]
softwarer
Member

Откуда: 127.0.0.1
Сообщений: 63932
Блог
Ольга Семенова
первый запрос у меня ВОТ так не проходит

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

Во-вторых, в данном случае результаты экзаменов в виде детальной таблицы неудобны для обработки; приходится сначала размножать данные, а потом группировать их. Зависит от вашего преподавателя, но удобнее было бы развернуть их в строку.

В-третьих, что именно не работает, кроме отсутствия специальности?
10 июн 07, 23:36    [4254710]     Ответить | Цитировать Сообщить модератору
 Re: Создание пары запросов  [new]
xymbo
Member

Откуда: Донской --> Москва
Сообщений: 2560
to softwarer
Специальность ведь надо во втором запросе учитывать, как я понял, но лучше подождать автора.:)
Ольга Семенова
2) Список зачисленных абитуриентов на каждую специальность, порядковый номер которых в списке из ЗАПРОСА 1 больше квоты на специальность
.
По 2: Можно использовать ROW_NUMBER для вычисления допущенных, и потом уже сравнивать с квотой.
10 июн 07, 23:45    [4254721]     Ответить | Цитировать Сообщить модератору
 Re: Создание пары запросов  [new]
xymbo
Member

Откуда: Донской --> Москва
Сообщений: 2560
Извиняюсь перед softwarer, он описал все до меня для второго пункта, не заметил сразу.
10 июн 07, 23:54    [4254729]     Ответить | Цитировать Сообщить модератору
 Re: Создание пары запросов  [new]
softwarer
Member

Откуда: 127.0.0.1
Сообщений: 63932
Блог
xymbo
Специальность ведь надо во втором запросе учитывать, как я понял, но лучше подождать автора.:)

Да, похоже, Вы правы. Мне что-то при первом прочтении показалось, что для каждой специальности свои минимальные баллы.
10 июн 07, 23:58    [4254733]     Ответить | Цитировать Сообщить модератору
 Re: Создание пары запросов  [new]
Ольга Семенова
Member

Откуда:
Сообщений: 698
softwarer
Ольга Семенова
первый запрос у меня ВОТ так не проходит

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

Во-вторых, в данном случае результаты экзаменов в виде детальной таблицы неудобны для обработки; приходится сначала размножать данные, а потом группировать их. Зависит от вашего преподавателя, но удобнее было бы развернуть их в строку.

В-третьих, что именно не работает, кроме отсутствия специальности?


Формулировка 1-го и 2-го запроса в ЗАДАНИИ такая:
Количество зачисляемых на специальность абитуриентов определяется КВОТОЙ.
1) Для зачисления нужно сформировать списки допущенных к конкурсу абитуриентов, ранжированные в порядке убывания суммарного балла. Т.е. если абитуриент по какому-либо экзамену получил оценку меньшую минимальной, то он в конкурсе уже не участвует и его в списке нет
2) Далее на каждую СПЕЦИАЛЬНОСТЬ зачисляются абитуриенты, порядковый номер которых в этом списке НЕ БОЛЬШЕ квоты на специальность. Т.е. сделать соответствующую выборку

я так поняла что в первом запросе НЕ НУЖНО учитывать специальность студента- нужно лишь учитывать величину минимального балла для того чтобы он попал в список допущенных к конкурсу абитуриентов

Так?
11 июн 07, 09:18    [4254914]     Ответить | Цитировать Сообщить модератору
 Re: Создание пары запросов  [new]
Ольга Семенова
Member

Откуда:
Сообщений: 698
есть идеи?
11 июн 07, 14:15    [4255551]     Ответить | Цитировать Сообщить модератору
 Re: Создание пары запросов  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18337
Ольга Семенова
есть идеи?

Ольга, Вам дали достаточно простое задание.
Полагаю, решать его за Вас тут никто не будет.
Вам пытаются подсказать.
Либо Вы воспользуетесь подсказками, либо - нет.
Чтобы подсказки были менее общими, прочитайте еще раз начало преамбулу первого ответа softwarer, там думается очень умный мысль.
11 июн 07, 14:40    [4255612]     Ответить | Цитировать Сообщить модератору
 Re: Создание пары запросов  [new]
Ольга Семенова
Member

Откуда:
Сообщений: 698
если бы у меня получалось - то я бы не спрашивала
11 июн 07, 14:44    [4255625]     Ответить | Цитировать Сообщить модератору
 Re: Создание пары запросов  [new]
Ольга Семенова
Member

Откуда:
Сообщений: 698
Помогите пожалуйста
15 июн 07, 08:23    [4270121]     Ответить | Цитировать Сообщить модератору
 Re: Создание пары запросов  [new]
sendal
Guest
Ольга Семенова
Помогите пожалуйста

Если честно, то как-то неохота доставать файл, смотреть где-то в акцессе ....
Да и читать - букафф много, наглядных примеров заполнения данными и требуемых результатов по тестовым данным нет и проблема не локализована ...
Может внести какие-нибудь позитивные изменения в структуру вопроса (может правда он интересный)?
15 июн 07, 08:31    [4270126]     Ответить | Цитировать Сообщить модератору
 Re: Создание пары запросов  [new]
Ольга Семенова
Member

Откуда:
Сообщений: 698
пишу для 3-го запроса так

SQL> SELECT a.SpecName,
  2  CASE WHEN COUNT(b.ID_abit) > a.SpecKvote THEN a.SpecKvote 
  3  ELSE COUNT(b.ID_abit) END AS Enrolled
  4  FROM spec AS a, 
  5  abit AS b, 
  6  Res AS c, 
  7  EXAM AS d
  8  where a.ID_spec = b.ID_spec AND
  9  b.ID_abit = c.ID_abit AND
 10  c.ID_exam = d.ID.exam AND 
 11  c.ExamRes >= d.ExamMin
 12  GROUP BY a.SpecName HAVING COUNT(c.ID_abit)=3;
FROM spec AS a,
          *
ERROR at line 4:
ORA-00933: SQL command not properly ended

что не так?
16 июн 07, 11:31    [4275830]     Ответить | Цитировать Сообщить модератору
 Re: Создание пары запросов  [new]
pravednik
Member

Откуда: Jacksonville, FL
Сообщений: 16268
Ольга Семенова
пишу для 3-го запроса так

SQL> SELECT a.SpecName,
  2  CASE WHEN COUNT(b.ID_abit) > a.SpecKvote THEN a.SpecKvote 
  3  ELSE COUNT(b.ID_abit) END AS Enrolled
  4  FROM spec AS a, 
  5  abit AS b, 
  6  Res AS c, 
  7  EXAM AS d
  8  where a.ID_spec = b.ID_spec AND
  9  b.ID_abit = c.ID_abit AND
 10  c.ID_exam = d.ID.exam AND 
 11  c.ExamRes >= d.ExamMin
 12  GROUP BY a.SpecName HAVING COUNT(c.ID_abit)=3;
FROM spec AS a,
          *
ERROR at line 4:
ORA-00933: SQL command not properly ended

что не так?


в FROM уберите AS
16 июн 07, 11:44    [4275846]     Ответить | Цитировать Сообщить модератору
 Re: Создание пары запросов  [new]
Ольга Семенова
Member

Откуда:
Сообщений: 698
SQL> SELECT a.SpecName,
  2  CASE WHEN COUNT(b.ID_abit) > a.SpecKvote THEN a.SpecKvote 
  3  ELSE COUNT(b.ID_abit) END AS Enrolled
  4  FROM spec a, abit b, Res c, EXAM d
  5  where a.ID_spec = b.ID_spec AND
  6  b.ID_abit = c.ID_abit AND
  7  c.ID_exam = d.ID_exam AND 
  8  c.ExamRes >= d.ExamMin
  9  GROUP BY a.SpecName HAVING COUNT(c.ID_abit)=3;
CASE WHEN COUNT(b.ID_abit) > a.SpecKvote THEN a.SpecKvote
                             *
ERROR at line 2:
ORA-00979: not a GROUP BY expression
16 июн 07, 11:58    [4275865]     Ответить | Цитировать Сообщить модератору
 Re: Создание пары запросов  [new]
Stax..
Guest
Ольга Семенова
SQL> SELECT a.SpecName,
  2  CASE WHEN COUNT(b.ID_abit) > a.SpecKvote THEN a.SpecKvote 
  3  ELSE COUNT(b.ID_abit) END AS Enrolled
  4  FROM spec a, abit b, Res c, EXAM d
  5  where a.ID_spec = b.ID_spec AND
  6  b.ID_abit = c.ID_abit AND
  7  c.ID_exam = d.ID_exam AND 
  8  c.ExamRes >= d.ExamMin
  9  GROUP BY a.SpecName HAVING COUNT(c.ID_abit)=3;
CASE WHEN COUNT(b.ID_abit) > a.SpecKvote THEN a.SpecKvote
                             *
ERROR at line 2:
ORA-00979: not a GROUP BY expression

Все поля (не константы) которые не вошли в групповую ф-цию должы быть в фразе group by
зы
странно COUNT(c.ID_abit)=3 и COUNT(b.ID_abit)
......
stax
16 июн 07, 12:46    [4275954]     Ответить | Цитировать Сообщить модератору
 Re: Создание пары запросов  [new]
Ольга Семенова
Member

Откуда:
Сообщений: 698
Stax..

Все поля (не константы) которые не вошли в групповую ф-цию должы быть в фразе group by
зы
странно COUNT(c.ID_abit)=3 и COUNT(b.ID_abit)
......
stax


т.е. как тогда должен выглядеть запрос?
16 июн 07, 12:52    [4275969]     Ответить | Цитировать Сообщить модератору
 Re: Создание пары запросов  [new]
pravednik
Member

Откуда: Jacksonville, FL
Сообщений: 16268
Ольга Семенова
Stax..

Все поля (не константы) которые не вошли в групповую ф-цию должы быть в фразе group by
зы
странно COUNT(c.ID_abit)=3 и COUNT(b.ID_abit)
......
stax


т.е. как тогда должен выглядеть запрос?


попробуйте так

SQL> SELECT a.SpecName,
  2  CASE WHEN COUNT(b.ID_abit) > a.SpecKvote THEN a.SpecKvote 
  3  ELSE COUNT(b.ID_abit) END AS Enrolled
  4  FROM spec a, abit b, Res c, EXAM d
  5  where a.ID_spec = b.ID_spec AND
  6  b.ID_abit = c.ID_abit AND
  7  c.ID_exam = d.ID_exam AND 
  8  c.ExamRes >= d.ExamMin
  9  GROUP BY a.SpecName, Enrolled HAVING COUNT(c.ID_abit)=3;
CASE WHEN COUNT(b.ID_abit) > a.SpecKvote THEN a.SpecKvote
16 июн 07, 12:57    [4275981]     Ответить | Цитировать Сообщить модератору
 Re: Создание пары запросов  [new]
pravednik
Member

Откуда: Jacksonville, FL
Сообщений: 16268
pravednik
Ольга Семенова
Stax..

Все поля (не константы) которые не вошли в групповую ф-цию должы быть в фразе group by
зы
странно COUNT(c.ID_abit)=3 и COUNT(b.ID_abit)
......
stax


т.е. как тогда должен выглядеть запрос?


попробуйте так

SQL> SELECT a.SpecName,
  2  CASE WHEN COUNT(b.ID_abit) > a.SpecKvote THEN a.SpecKvote 
  3  ELSE COUNT(b.ID_abit) END AS Enrolled
  4  FROM spec a, abit b, Res c, EXAM d
  5  where a.ID_spec = b.ID_spec AND
  6  b.ID_abit = c.ID_abit AND
  7  c.ID_exam = d.ID_exam AND 
  8  c.ExamRes >= d.ExamMin
  9  GROUP BY a.SpecName, Enrolled HAVING COUNT(c.ID_abit)=3;
16 июн 07, 12:58    [4275983]     Ответить | Цитировать Сообщить модератору
 Re: Создание пары запросов  [new]
Ольга Семенова
Member

Откуда:
Сообщений: 698
SQL> SELECT a.SpecName,
  2  CASE WHEN COUNT(b.ID_abit) > a.SpecKvote THEN a.SpecKvote 
  3  ELSE COUNT(b.ID_abit) END AS Enrolled
  4  FROM spec a, abit b, Res c, EXAM d
  5  where a.ID_spec = b.ID_spec AND
  6  b.ID_abit = c.ID_abit AND
  7  c.ID_exam = d.ID_exam AND 
  8  c.ExamRes >= d.ExamMin
  9  GROUP BY a.SpecName, 
 10  Enrolled HAVING COUNT(c.ID_abit)=3;
Enrolled HAVING COUNT(c.ID_abit)=3
*
ERROR at line 10:
ORA-00904: invalid column name
16 июн 07, 13:17    [4276016]     Ответить | Цитировать Сообщить модератору
 Re: Создание пары запросов  [new]
Ольга Семенова
Member

Откуда:
Сообщений: 698
для примера вот сами таблицы и как они заполнены

create table spec
(Id_spec integer NOT NULL PRIMARY KEY,
Specname char(50) NOT NULL,
Speckvote integer);

create table abit
(Id_abit integer NOT NULL PRIMARY KEY,
Id_spec integer NOT NULL,
Fam char(50) NOT NULL,
Name char(50) NOT NULL,
Otch char(50) NOT NULL,
Adr char(50) NOT NULL,
Tel char(8),
FOREIGN KEY (Id_spec) REFERENCES spec(Id_spec) ON DELETE CASCADE);

create table exam
(Id_exam integer NOT NULL PRIMARY KEY,
Examname char(50) NOT NULL,
Exammin  integer NOT NULL);

create table res
(Id_abit integer NOT NULL,
Id_exam integer NOT NULL,
Examres integer NOT NULL,
FOREIGN KEY (Id_abit) REFERENCES abit(Id_abit) ON DELETE CASCADE,
FOREIGN KEY (Id_exam) REFERENCES exam(Id_exam) ON DELETE CASCADE);

insert into spec values (1,'Электроника и микроэлектроника',2);
insert into spec values (2,'Организация и защита информации',2);
insert into spec values (3,'Информационные системы и технологии',2);

insert into exam values (1,'Математика',20);
insert into exam values (2,'Физика',20);
insert into exam values (3,'Русский язык',20);

insert into abit values (1,1,'Иванов','Иван','Иванович','ул.Бакинская д.134','22-54-34');
insert into abit values (2,1,'Петров','Петр','Петрович','ул.Боевая д.34','25-45-12');
insert into abit values (3,1,'Кузнецов','Иван','Олегович','ул.Летейная д.12 кв.43','34-23-11');
insert into abit values (4,1,'Сидоренко','Михаил','Григорьевич','ул.Пролетарская д.56 кв.59','45-45-23');
insert into abit values (5,2,'Мерзляков','Евгений','Антонович','ул.Бэра д.67 кв.69','34-87-32');
insert into abit values (6,2,'Сырман','Антон','Антонович','ул.Осенняя д.36 кв.1','35-78-34');
insert into abit values (7,2,'Кызаев','Руслан','Рифатович','ул.Ульчнова д.63 кв.5','37-45-34');
insert into abit values (8,2,'Алканцев','Артём','Борисович','ул.Лычманова д.77 кв.34','39-56-45');
insert into abit values (9,3,'Рамин','Александр','Степанович','ул.Кирова д.2 кв.23','34-21-84');
insert into abit values (10,3,'Володин','Владимир','Владимирович','ул.Чернышевского д.12 кв.77','56-45-54');
insert into abit values (11,3,'Пронин','Ян','Борисович','ул.Бакинская д.98 кв.90','44-65-32');
insert into abit values (12,3,'Салихов','Иван','Александрович','ул.Борисовского д.8 кв.56','67-45-78');

insert into res values (1,1,80);
insert into res values (1,2,80);
insert into res values (1,3,70);
insert into res values (2,1,30);
insert into res values (2,2,15);
insert into res values (2,3,43);
insert into res values (3,1,65);
insert into res values (3,2,85);
insert into res values (3,3,34);
insert into res values (4,1,98);
insert into res values (4,2,89);
insert into res values (4,3,90);
insert into res values (5,1,12);
insert into res values (5,2,11);
insert into res values (5,3,5);
insert into res values (6,1,56);
insert into res values (6,2,55);
insert into res values (6,3,66);
insert into res values (7,1,78);
insert into res values (7,2,80);
insert into res values (7,3,85);
insert into res values (8,1,34);
insert into res values (8,2,45);
insert into res values (8,3,56);
insert into res values (9,1,56);
insert into res values (9,2,60);
insert into res values (9,3,90);
insert into res values (10,1,100);
insert into res values (10,2,99);
insert into res values (10,3,99);
insert into res values (11,1,89);
insert into res values (11,2,80);
insert into res values (11,3,90);
insert into res values (12,1,57);
insert into res values (12,2,57);
insert into res values (12,3,58);
16 июн 07, 13:18    [4276021]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Oracle Ответить