Управление данными ответы к экзамену (чтобы помочь разобраться, а не для списывания)
Primary tabs
[Практический справочник по 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 SEMESTE
R;
Пока что я не вижу способа решить эту задачу, используя только одну таблицу 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 ) ;
- Log in to post comments
- 15184 reads