Управление данными ответы к экзамену (чтобы помочь разобраться, а не для списывания)

[Практический справочник по MySQL]

ВНИМАНИЕ - ТЕГИ code И /code НЕ НУЖНЫ В ЗАПРОСАХ - ОНИ - ТЕХНИЧЕСКАЯ ИЗДЕРЖКА -МНЕ ПРОСТО ЛЕНЬ МЕНЯТЬ РЕЗМЕТкУ - ИТАК ДОЛГО ФОРМАТИРОВАЛА.

Наша база данных имеет следующий вид -

 table SUBJECT
(
  SUBJ_ID   NUMERIC not null,
  SUBJ_NAME VARCHAR(100),
  HOUR      NUMERIC,
  SEMESTER   NUMERIC
);
table UNIVERSITY
(
  UNIV_ID   NUMERIC not null,
  UNIV_NAME VARCHAR(160),
  RATING    NUMERIC,
  CITY      VARCHAR(60)
);
 table LECTURER
(
  LECTURER_ID NUMERIC not null,
  SURNAME     VARCHAR(60),
  NAME        VARCHAR(60),
  CITY        VARCHAR(60),
  UNIV_ID     NUMERIC
);
 table STUDENT
(
  STUDENT_ID NUMERIC not null,
  SURNAME    VARCHAR(60),
  NAME       VARCHAR(60),
  STIPEND    NUMERIC(16,2),
  KURS       NUMERIC,
  CITY       VARCHAR(60),
  BIRTHDAY   DATE,
  UNIV_ID    NUMERIC
);
 table EXAM_MARKS
(
  EXAM_ID    NUMERIC not null,
  STUDENT_ID NUMERIC not null,
  SUBJ_ID    NUMERIC not null,
  MARK       NUMERIC,
  EXAM_DATE  DATE
);
 table SUBJ_LECT
(
  LECTURER_ID NUMERIC not null,
  SUBJ_ID     NUMERIC not null
);
1) Напишите Запрос для определения количества предметов, изучаемых на каждом курсе.
2) Напиши запрос , выполняющий вывод имён и фамилий преподователей, читающих по два и более предметов.
3) Напишите запрос ,выполняющий вывод списка фамилий студентов, имеющих только отличные оценки и проживающих в городе, не
совпадающем с городом их университета.
4) Напшите запрос ,увеличивающий размер стипендии на 20% всем студентам, У которых сумма баллов превышает значение 50.
5) Написать запрос выполняющий вывод данных о фамилиях студентов, имеющих полный набор оценок (пятёрки, четвёрки,тройки.)
6) Написать запрос , который для каждого конкретного дня сдачи экзамена выводит количество студентов, сдававших экзамен в этот день.
7) Написать Запрос, выполняющий вывод количества часов занятий, проводимых преподавателем Лагутиным (видимо, имеется ввиду - все часы за год по всем предметам)
8) Введите запись для нового студента , которого зовут Орлов Николай, обучающегося на первом курсе ВГУ, живущего в Воронеже, сведения о дате рождения и размере стипендии неизвестны.

=======================================================
Теперь я попробую ответить на эти вопросы, дабы подготовится к экзамену. Ни одну из трёх аттестаций я не сдал - из-за отсутствия должного
прилежания 0))). Как сказал Юрий Гагарин - "Поехали!"
=======================================================

1) Напишите Запрос для определения количества предметов, изучаемых на каждом курсе.

(в той схеме базы данных ,что нам выдали неправильно было написано поле SEMESTER - >)
Попробуем решить этот вопрос обратившись к таблице SUBJECT в составе-

<code> table SUBJECT
(
  SUBJ_ID   NUMERIC not null,
  SUBJ_NAME VARCHAR(100),
  HOUR      NUMERIC,
  SEMESTER   NUMERIC
);</code>

Выведем число предметов для каждого семестра -

SELECT SEMESTER , COUNT(*) FROM SUBJECT GROUP BY SEMESTER;

Пока что я не вижу способа решить эту задачу, используя только одну таблицу SUBJECT;
Вообще , поле "курс" есть только в таблице "STUDENT", на которую "указывает" таблица "EXAM_MARKS".
"EXAM_MARKS" также указывает на "SUBJECT". Если подразумевать, что подобная связь установлена для всех студентов(просто данные по экзаменам не заполнены ,так как они могли их ещё не сдавать), но это не показывает однозначную связь так как второкурсники также сдавали экзамен за первые семестры.....

Решим данную задачу с помощью JOIN , используя подзапрос, как-то так:

 <code>SELECT KURS,COUNT(*) FROM(SELECT * FROM STUDENT LEFT JOIN EXAM_MARKS ON STUDENT.STUDENT_ID=EXAM_MARKS.STUDENT_ID  LEFT JOIN
SUBJECT ON EXAM_MARKS.SUBJ_ID=SUBJECT.SUBJ_ID GROUP BY  SUBJECT.SUBJ_ID) AS MYTABLE GROUP BY KURS;</code>

но такой вариант не прокатит из-за ошибки - #1060 - Duplicate column name 'STUDENT_ID'
чтобы избежать этой ошибки необходимо выбирать не все подряд строки, а только те ,что нас интересуют, а интересуют нас курсы, на которых предметы изучаются. Получаем:

<code>SELECT KURS,COUNT(*) FROM(SELECT STUDENT.KURS  FROM STUDENT LEFT JOIN EXAM_MARKS ON STUDENT.STUDENT_ID=EXAM_MARKS.STUDENT_ID  LEFT JOIN
SUBJECT ON EXAM_MARKS.SUBJ_ID=SUBJECT.SUBJ_ID GROUP BY  SUBJECT.SUBJ_ID) AS MYTABLE GROUP BY KURS;</code>

- и это работающее решение.

Теперь ещё один момент . Утверждается (одним из преподавателей по практике) что данная задача вполне разрешима с помощью UNION. Что ж, попробуем:

<code>SELECT '1-ый курс',COUNT(*) FROM ( SELECT DISTINCT SUBJ_NAME FROM SUBJECT WHERE SEMESTER=1 OR SEMESTER=2) AS MYTABLE ; </code>

- выполнение данного запроса
покажет, что на первом курсе читаются 6 предметов, выполним проверку -
SELECT * FROM SUBJECT ;
- и посчитаем вручную, благо это не списов студентов или экзаменационных оченок нашей учебной базы.
Получается , что 6 предметов - математика два семестра подряд.
Теперь выполним объединение с помощью UNION (только для двух курсов, так как в учеюной базе их всего и есть два - третий пристёгивайте по аналогии)) -

<code>SELECT '1-ый курс',COUNT(*) FROM ( SELECT DISTINCT SUBJ_NAME FROM SUBJECT WHERE SEMESTER=1 OR SEMESTER=2) AS MYTABLE1
UNION  
SELECT '2-ой курс',COUNT(*) FROM ( SELECT DISTINCT SUBJ_NAME FROM SUBJECT WHERE SEMESTER=3 OR SEMESTER=4) AS MYTABLE2;</code>

и опять же - выполните проверку чтобы убедиться в правильности запроса -
SELECT * FROM SUBJECT ;

----------------------------------------------

2) Напиши запрос , выполняющий вывод имён и фамилий преподователей, читающих по два и более предметов.

SELECT LECTURER.NAME , LECTURER.SURNAME, COUNT(*)  FROM LECTURER LEFT JOIN SUBJ_LECT ON LECTURER.LECTURER_ID=SUBJ_LECT.LECTURER_ID  LEFT JOIN
SUBJECT ON SUBJECT.SUBJ_ID=SUBJ_LECT.SUBJ_ID GROUP BY  LECTURER.LECTURER_ID HAVING COUNT(*)>=3;

похоже на то, что запрос работает. Давайте теперь посмотрим на преподавателей вообще -
SELECT * FROM LECTURER;
или посмотрим, если кто ведёт больше 4-ёх -

SELECT LECTURER.NAME , LECTURER.SURNAME, COUNT(*)  FROM LECTURER LEFT JOIN SUBJ_LECT ON LECTURER.LECTURER_ID=SUBJ_LECT.LECTURER_ID  LEFT JOIN
SUBJECT ON SUBJECT.SUBJ_ID=SUBJ_LECT.SUBJ_ID GROUP BY  LECTURER.LECTURER_ID HAVING COUNT(*)>=4;

таковых не имеется.
Кстати , по заданию выводить число предметов , не надо.
Соответственно, запрос предстаёт в виде -

SELECT LECTURER.NAME , LECTURER.SURNAME  FROM LECTURER LEFT JOIN SUBJ_LECT ON LECTURER.LECTURER_ID=SUBJ_LECT.LECTURER_ID  LEFT JOIN
SUBJECT ON SUBJECT.SUBJ_ID=SUBJ_LECT.SUBJ_ID GROUP BY  LECTURER.LECTURER_ID HAVING COUNT(*)>=2;

----------------------------------------------

3) Напишите запрос ,выполняющий вывод списка фамилий студентов, имеющих только отличные оценки и проживающих в городе, не
совпадающем с городом их университета.

<code>SELECT SURNAME  FROM UNIVERSITY LEFT JOIN(SELECT SURNAME,STUDENT.UNIV_ID,STUDENT.CITY  FROM STUDENT   LEFT JOIN EXAM_MARKS ON STUDENT.STUDENT_ID=EXAM_MARKS.STUDENT_ID  GROUP BY STUDENT.STUDENT_ID HAVING MIN(EXAM_MARKS.MARK)=4)AS MYTABLE1 ON UNIVERSITY.UNIV_ID=MYTABLE1.UNIV_ID WHERE MYTABLE1.CITY<>UNIVERSITY.CITY ;</code

таковых имеется 235 штук. теперь давайте посмотрим на их средний балл. -

<code>SELECT SURNAME, SR  FROM UNIVERSITY LEFT JOIN(SELECT SURNAME,STUDENT.UNIV_ID,STUDENT.CITY, AVG(EXAM_MARKS.MARK) AS SR  FROM UNIVERSITY LEFT JOIN STUDENT ON UNIVERSITY.UNIV_ID=STUDENT.UNIV_ID  LEFT JOIN EXAM_MARKS ON STUDENT.STUDENT_ID=EXAM_MARKS.STUDENT_ID  GROUP BY STUDENT.STUDENT_ID HAVING MIN(EXAM_MARKS.MARK)=4)AS MYTABLE1 ON UNIVERSITY.UNIV_ID=MYTABLE1.UNIV_ID WHERE MYTABLE1.CITY<>UNIVERSITY.CITY ;</code>

Убедимся что эти ребята не из других городов -

<code>SELECT STCITY,SURNAME, SR,UNIVERSITY.CITY  FROM UNIVERSITY LEFT JOIN(SELECT SURNAME,STUDENT.UNIV_ID,STUDENT.CITY AS STCITY, AVG(EXAM_MARKS.MARK) AS SRBALL  FROM STUDENT   LEFT JOIN EXAM_MARKS ON STUDENT.STUDENT_ID=EXAM_MARKS.STUDENT_ID  GROUP BY STUDENT.STUDENT_ID HAVING MIN(EXAM_MARKS.MARK)=4)AS MYTABLE1 ON UNIVERSITY.UNIV_ID=MYTABLE1.UNIV_ID WHERE STCITY<>UNIVERSITY.CITY ;</code>

Ну что же - первые тридцать человек, которых я увидел - хорошисты))
Итак ,решение этого задания -

<code>SELECT SURNAME  FROM UNIVERSITY LEFT JOIN(SELECT SURNAME,STUDENT.UNIV_ID,STUDENT.CITY  FROM STUDENT   LEFT JOIN EXAM_MARKS ON STUDENT.STUDENT_ID=EXAM_MARKS.STUDENT_ID  GROUP BY STUDENT.STUDENT_ID HAVING MIN(EXAM_MARKS.MARK)=4)AS MYTABLE1 ON UNIVERSITY.UNIV_ID=MYTABLE1.UNIV_ID WHERE MYTABLE1.CITY<>UNIVERSITY.CITY ;</code>

----------------------------------------------

4) Напшите запрос ,увеличивающий размер стипендии на 20% всем студентам, У которых сумма баллов превышает значение 50.

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

 <code>SELECT SURNAME, STIPEND , BALLSUM FROM (SELECT SURNAME,STIPEND, SUM(EXAM_MARKS.MARK) AS BALLSUM  FROM STUDENT   LEFT JOIN EXAM_MARKS ON STUDENT.STUDENT_ID=EXAM_MARKS.STUDENT_ID  GROUP BY STUDENT.STUDENT_ID HAVING SUM(EXAM_MARKS.MARK)>50)AS MYTABLE1 ;</code>

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

<code>SELECT SURNAME, STIPEND ,  SUM(EXAM_MARKS.MARK)  FROM STUDENT   LEFT JOIN EXAM_MARKS ON STUDENT.STUDENT_ID=EXAM_MARKS.STUDENT_ID  GROUP BY STUDENT.STUDENT_ID HAVING SUM(EXAM_MARKS.MARK)>50 ;</code>

хм.....субд просто возвращает пустой результат.....попробуем уменьшить сумму баллов -

<code>SELECT SURNAME, STIPEND ,  SUM(EXAM_MARKS.MARK),STUDENT.STUDENT_ID  FROM STUDENT   LEFT JOIN EXAM_MARKS ON STUDENT.STUDENT_ID=EXAM_MARKS.STUDENT_ID  GROUP BY STUDENT.STUDENT_ID HAVING SUM(EXAM_MARKS.MARK)>20 ;</code>

больше 1000 человек получили 20 и более баллов) чтож - теперь давайте увеличим стипендию несуществующим ребятам -
для конкретики возьмём тех, у кого больше 30 баллов -

<code>SELECT SURNAME, STIPEND ,  SUM(EXAM_MARKS.MARK),STUDENT.STUDENT_ID  FROM STUDENT   LEFT JOIN EXAM_MARKS ON STUDENT.STUDENT_ID=EXAM_MARKS.STUDENT_ID  GROUP BY STUDENT.STUDENT_ID HAVING SUM(EXAM_MARKS.MARK)>30 ;</code>

их всего 29 . У первой студентки стипендия 400 р - и это не порядок!)) повышаем. -
Кстати, давайте для начала попробуем повысить ей одной (запомним её id = 413;

 <code>UPDATE STUDENT set  STIPEND = STIPEND *1.2 where STUDENT_ID=413;</code>

теперь смотрим что получилось. По-ему получилось очень даже неплохо) - теперь у неё 480;

Соответственно, общий запрос будет выглядеть как - то так -

  <code>UPDATE STUDENT set  STIPEND = STIPEND *1.2 where STUDENT_ID IN ( SELECT SURNAME, STIPEND ,  SUM(EXAM_MARKS.MARK)  FROM STUDENT   LEFT JOIN EXAM_MARKS ON STUDENT.STUDENT_ID=EXAM_MARKS.STUDENT_ID  GROUP BY STUDENT.STUDENT_ID HAVING SUM(EXAM_MARKS.MARK)>20 )</code> 

- неправильно - даст ошибку -

>

тогда перепишем запрос в виде -

<code>UPDATE STUDENT set  STIPEND = STIPEND *1.2 where STUDENT_ID IN ( SELECT EXAM_MARKS.STUDENT_ID  FROM EXAM_MARKS  GROUP BY EXAM_MARKS.STUDENT_ID HAVING SUM(EXAM_MARKS.MARK)>50);</code>

(замечание - чтобы обновить сразу несколько строк , как в предыдущем запросе используйте условие не "WHERE ЧТО-ТО=" ,а "WHERE ЧТО-ТО IN" )
выполняется этот запрос жутко долго) то есть вообще виснет, хотя синтаксически он верен (утверждаю не только я)) - можит быть и не прав тогда не только я 0)))

ребята с форума программистов подсказывают такой вариант, который выполняется на моём компе за 9 с гаком секунд -

<code>UPDATE STUDENT set STIPEND = STIPEND *1.2 WHERE STUDENT_ID IN
(SELECT OTL.STUDENT_ID FROM (SELECT EXAM_MARKS.STUDENT_ID, SUM(EXAM_MARKS.MARK) as EX_SUM FROM EXAM_MARKS GROUP BY EXAM_MARKS.STUDENT_ID) as OTL
  WHERE OTL.EX_SUM >20);</code>

как вы можете видеть , здесь мы избавились от HAVING.

---------------------------------------------------------------------------

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

Попробуем начать с запроса вида:

<code>SELECT SURNAME  FROM STUDENT  WHERE STUDENT_ID IN ( SELECT STUDENT_ID FROM EXAM_MARKS WHERE MARK=5 )AND STUDENT_ID IN (SELECT STUDENT_ID FROM EXAM_MARKS WHERE MARK=4) AND STUDENT_ID IN (SELECT STUDENT_ID FROM EXAM_MARKS WHERE MARK=3);</code>

- РАБОТАЕТ.

---------------------------------------------------------------------------

6) Написать запрос , который для каждого конкретного дня сдачи экзамена выводит количество студентов, сдававших экзамен в этот день.

<code>SELECT EXAM_DATE, COUNT(*) FROM (SELECT  DISTINCT  STUDENT.STUDENT_ID,EXAM_DATE FROM STUDENT LEFT JOIN EXAM_MARKS ON STUDENT.STUDENT_ID=EXAM_MARKS.STUDENT_ID) AS 
 MYTABLE1 GROUP BY EXAM_DATE;</code> 

- этот почему-то не обрабатывается в phpmyadmin, но в командной строке обрабатывается нормально.
Аналогично так как DISTINCT это неявный GROUP BY можно использовать запрос вида -
SELECT EXAM_DATE, COUNT(*) FROM (SELECT STUDENT.STUDENT_ID,EXAM_DATE FROM STUDENT LEFT JOIN EXAM_MARKS ON STUDENT.STUDENT_ID=EXAM_MARKS.STUDENT_ID
GROUP BY STUDENT.STUDENT_ID) AS MYTABLE1 GROUP BY EXAM_DATE;
- на экзамене , думаю, его даже лучше написать.
---------------------------------------------------------------------------

7) Написать Запрос, выполняющий вывод количества часов занятий, проводимых преподавателем Лагутиным (видимо, имеется ввиду - все часы за год по всем предметам

)

<code>SELECT SUM(SUBJECT.HOUR) FROM  LECTURER LEFT JOIN SUBJ_LECT ON LECTURER.LECTURER_ID=SUBJ_LECT.LECTURER_ID  LEFT JOIN
SUBJECT ON SUBJECT.SUBJ_ID=SUBJ_LECT.SUBJ_ID GROUP BY SURNAME HAVING SURNAME='Лагутин'; </code>

хм...ноль строк. Посмотрим - есть ли вообще такой препод. -
SELECT SURNAME FROM LECTURER WHERE SURNAME='Лагутин';
такого чувака нет.
давайте попробуем с кем-нибудь другим)

<code>SELECT *  FROM    LECTURER;  </code>

- Бабкина Надежда, под номером 1 - давайте посмотрим сколько часов в год она тратит на студентов -

<code>SELECT SUM(SUBJECT.HOUR) FROM  LECTURER LEFT JOIN SUBJ_LECT ON LECTURER.LECTURER_ID=SUBJ_LECT.LECTURER_ID  LEFT JOIN
SUBJECT ON SUBJECT.SUBJ_ID=SUBJ_LECT.SUBJ_ID GROUP BY SURNAME HAVING SURNAME='Бабкина';  </code> 

- вроде как 315)
Опять же - не утверждаю что данный запрос является сильно оптимальным ,ибо HAVING - проверяется уже после группировки предметов по фамилиям ВСЕХ преподавателей....по крайней мере, где-то в интеренете про это было написано.
---------------------------------------------------------------------------

8) Введите запись для нового студента , которого зовут Орлов Николай, обучающегося на первом курсе ВГУ, живущего в Воронеже, сведения о дате рождения и размере стипендии неизвестны.

Это задача скорее на знание волшебных слов. чем на логику . Решим её -

<code>INSERT INTO STUDENT (STUDENT_ID,NAME,SURNAME,CITY,KURS) VALUES (1000000,'Николай','Орлов','Воронеж',1);     </code>

-----------------------------------------------------------------------
Пусть имеется некая таблица STUDENT1 эквивалентная STUDENT . вставте в неё из STUDENT все данные о студентах, сдавших успешно более пяти экзаменов.

INSERT INTO   STUDENT1 SELECT * FROM STUDENT WHERE STUDENT_ID= (SELECT STUDENT_ID FROM EXAM_MARKS GROUP BY STUDENT_ID HAVING COUNT(*)>=5 ) ;