Вывести список всех книг, рекомендованных указанному студенту (дан код студента)

⇐ ПредыдущаяСтр 2 из 2

ФИО	Ответ
Ванчиков Алексей	SELECT DISTINCT rb.KodBook FROM student s INNER JOIN ocenki oc ON oc.KodStud = s.KodStud INNER JOIN recom_book rb ON rb.KodPredm = oc.KodPredmet WHERE s.KodStud = KOD_STUDENTA
Вихров Александр	SELECT NazBook, Avtor from book where KodBook IN (SELECT DISTINCT KodBook FROM recom_book where KodPredm IN (SELECT DISTINCT KodPredm from izuch_predm where KodGrupp IN (SELECT KodGrupp FROM sostav_grupp WHERE KodStud = 'Код студента')))
Земнухов Егор	select Fam,Im,Otch,NazBook from recom_book join student s on recom_book.KodStudenta = s.KodStud join book b on b.KodBook = recom_book.KodBook where KodStudenta=3
Ильин Антон	Не прислал
Калинин Олег	Не прислал
Кондрин Станислав	Не прислал
Ласков Илья	select distinct bName as BooksName from books as b,recbook as rb,subject as s,learntsubj as ls, groupcomp as gp,student as st where b.bID=rb.bId and rb.subid = s.subid and s.subID=ls.subid and ls.gID = gp.gId and st.studID=gp.studID and st.studID=7
Фуфин Михаил	Не прислал

До этой задачи добралось уже меньше героев, чем до предыдущей.
Варианты Ильи и Александра вполне рабочие (у Ильи, правда, имена таблиц и полей свои, но логика правильная).

У Алексея два «бага» – во-первых, вместо названия книги выводится её код.
Бедного пользователя такой ответ на его вопрос вгоняет в лучшем случае в ступор, в худшем – в гнев, переходящий в попытки поколотить программиста J

Ну и второй момент – от студента до изучаемых предметов Алексей пошёл по самому короткому пути. Т.е. через оценки. Тонкость тут в том, что в начале семестра, когда нужно получать книжки, оценок по изучаемым предметам ещё нет. А у некоторых особо выдающихся их и по окончанию сессии может не быть (если мы не будем в БД фиксировать неявки, а будем фиксировать лишь оценки).

У Егора тоже есть ошибка.

Дело в том, что в таблице recom_book коду студента взяться неоткуда.
Приходится как нормальным героям «идти в обход» через четыре таблицы.
Если бы вместо recom_book была бы таблица «выданные книги», которая как раз между «студентами» и «книгами», то тогда бы запрос сработал. Но это были бы не те книги, которые студенту рекомендовали (типа, «Ядерная физика», «Аналитическая геометрия»), а те, что он взял («Приключения Буратино» и «Основы самогоноварения»)

Подсчитать, сколько книг рекомендовано каждому студенту

ФИО

Ответ

Ванчиков Алексей

SELECT KodStud, COUNT(KodBook) FROM (SELECT DISTINCT s.KodStud, rb.KodBook FROM student s

INNER JOIN ocenki oc ON oc.KodStud = s.KodStud

INNER JOIN recom_book rb ON rb.KodPredm = oc.KodPredmet)

GROUP BY kodstud

Вихров Александр

Select (Fam + ' ' + Im + ' ' + Otch) as FIO, KolKnigDlyaStudenta.KolKnig from student,

(select StudentPredmet.KodStud, COUNT(KodBook) as KolKnig from recom_book,

(Select Distinct StudentGruppa.KodStud, izuch_predm.KodPredm

from izuch_predm,

(select Student.KodStud, sostav.KodGrupp

from sostav_grupp as sostav,

(select KodStud from student) as Student

where sostav.KodStud = student.KodStud) as StudentGruppa

where StudentGruppa.KodGrupp = izuch_predm.KodGrupp) as StudentPredmet

where StudentPredmet.KodPredm = recom_book.KodPredm

Group by StudentPredmet.KodStud) as KolKnigDlyaStudenta

where student.KodStud = KolKnigDlyaStudenta.KodStud

Земнухов Егор

select Fam,Im,Otch,count(KodBook) as count from recom_book

join student s on recom_book.KodStudenta = s.KodStud

where recom_book.KodStudenta=1

Ильин Антон

Не прислал

Калинин Олег

Не прислал

Кондрин Станислав

Не прислал

Ласков Илья

Не прислал

Фуфин Михаил

Не прислал

Сюда добралось ещё меньше героев.
Поскольку этот запрос является доработкой предыдущего, у Алексея с Егором ошибки повторились.

У Александра запрос работает, но уровень навороченности совсем зашкалил J

Более простой запрос будет выглядеть так:

SELECT CONCAT( TRIM( Fam ) , ' ', TRIM( Im ) , ' ', TRIM( Otch ) ) AS FIO, COUNT( KodBook ) AS Kolich FROM student s INNER JOIN sostav_grupp sg ON s.KodStud = sg.KodStud INNER JOIN izuch_predm ip ON ip.KodGrupp = sg.KodGrupp INNER JOIN recom_book rb ON rb.KodPredm = ip.KodPredm GROUP BY s.kodstud ORDER BY FIO

5. Вывести список всех задолженностей. Пары значений "Студент-предмет" (из списка изучаемых предметов "Студент-предмет" вычесть список положительных оценок "Студент-предмет")

ФИО	Ответ
Ванчиков Алексей	SELECT DISTINCT s.KodStud, oc.KodPredmet FROM student s INNER JOIN ocenki oc ON oc.KodStud = s.KodStud WHERE s.KodStud IN ( SELECT s.KodStud FROM student s INNER JOIN ocenki oc ON oc.KodStud = s.KodStud WHERE oc.Ocenka < 3 )
Вихров Александр	select Distinct KodStud, KodPredm from izuch_predm, (select KodStud, KodGrupp from sostav_grupp) as sostav where sostav.KodGrupp = izuch_predm.KodGrupp EXCEPT select KodStud, KodPredmet from ocenki where Ocenka > 2
Земнухов Егор	Не прислал
Ильин Антон	Не прислал
Калинин Олег	Не прислал
Кондрин Станислав	Не прислал
Ласков Илья	Не прислал
Фуфин Михаил	Не прислал

До этой задачи добрались самые стойкие.

Для начала, заметим, что запрос мы делаем для какого-то администратора, кто явно не в курсе, какими кодами помечен у нас в базе какой студент и какой предмет.
Ему нужно ФИО и название предмета. Это значит, что в этот раз в запросе придётся таблицу «студент» и «предмет» снова «прикручивать», чтобы добыть оттуда расшифровку кодов «для непрограммиста».

У Алексея в данном случае получился вообще очень странный запрос.
Внешний и вложенный запросы по сути равны. А на нужно сравнить то, что студент ДОЛЖЕН сдать с тем, что он по факту СДАЛ (на оценку не ниже тройки).

Эта логика вполне корректно реализована у Александра.
Единственный момент.
Оператор UNION как объединение таблиц с одинаковой структурой, реализован во всех диалектах SQL. А вот INTERSECT (пересечение) и EXCEPT (разность), в большинстве случае не реализован.
Например, при работе с MySQL приходится прибегать к приёмам, о которых я говорил на лекции.

В этом случае запрос будет выглядеть так:

SELECT DISTINCT CONCAT( TRIM( Fam ) , ' ', TRIM( Im ) , ' ', TRIM( Otch ) ) AS FIO, NazPredmet FROM student s INNER JOIN sostav_grupp sg ON s.KodStud = sg.KodStud INNER JOIN izuch_predm ip ON ip.KodGrupp = sg.KodGrupp INNER JOIN predmet p ON ip.KodPredm = p.KodPredmet WHERE s.KodStud *1000000 + p.KodPredmet NOT IN ( SELECT o.KodStud *1000000 + o.KodPredmet FROM ocenki o WHERE Ocenka >2 )

Здесь, кстати, становится заметно, что в таблице predmet поле KodPredm, а в остальных, где аналогичный код – KodPredmet
Но уж ничего не поделаешь. Приходится писать в соответствии со структурой.

Структура БД «Университет»:

CREATE TABLE `student` ( `KodStud` int(5) NOT NULL auto_increment, `Fam` varchar(25) NOT NULL, `Im` varchar(20) NOT NULL, `Otch` varchar(20) NOT NULL, `Pol` char(1) NOT NULL, `DataRozd` date NOT NULL, `Prim` text NOT NULL, `Foto` BLOB NOT NULL , PRIMARY KEY (`KodStud`)) ; CREATE TABLE `predmet` ( `KodPredmet` int(5) NOT NULL auto_increment, `NazPredmet` varchar(100) NOT NULL, `NazSokr` varchar(10) NOT NULL, PRIMARY KEY (`KodPredmet`)); CREATE TABLE `ocenki` ( `KodStud` int(5) NOT NULL, `KodPredmet` int(5) NOT NULL, `Ocenka` int(1) NOT NULL, `Data` date NOT NULL) ; CREATE TABLE `grupp` ( `KodGrupp` int(5) NOT NULL auto_increment, `NazGrupp` char(4) NOT NULL, `GodNabora` int(4) NOT NULL, `NazSpec` char(25) NOT NULL, PRIMARY KEY (`KodGrupp`)); CREATE TABLE `sostav_grupp` ( `KodStud` int(5) NOT NULL, `KodGrupp` int(5) NOT NULL); CREATE TABLE `book` ( `KodBook` INT( 6 ) NOT NULL auto_increment, `NazBook` VARCHAR( 100 ) NOT NULL , `Avtor` VARCHAR( 50 ) NOT NULL , `DataIzd` DATE NOT NULL , `Kolich` INT( 4 ) NOT NULL , `Vidano` INT( 4 ) NOT NULL , PRIMARY KEY ( `KodBook` )); CREATE TABLE `vidan_book` ( `KodBook` INT( 6 ) NOT NULL , `KodStud` INT( 5 ) NOT NULL , `DataVid` DATE NOT NULL , `KolichVid` INT( 3 ) NOT NULL); CREATE TABLE `izuch_predm` ( `KodPredm` INT( 5 ) NOT NULL , `KodGrupp` INT( 5 ) NOT NULL); CREATE TABLE `recom_book` ( `KodPredm` INT( 5 ) NOT NULL , `KodBook` INT( 6 ) NOT NULL);

Дата добавления: 2020-11-15; просмотров: 65; Мы поможем в написании вашей работы!

Поделиться с друзьями:

⇐ Предыдущая 12

Мы поможем в написании ваших работ!