Примечание

ЭТО АРХИВНАЯ ВЕРСИЯ КУРСА!

Материалы предназначаются для пересдающих дисциплину "ОП.08 - Основы проектирования баз данных" в соответствии с учебными планами СПО годов набора ДО 2024-го. Для того чтобы ознакомиться с актуальным курсом "ИТ.03 - Основы проектирования баз данных" включающим интерактивные примеры, перейдите по ссылке.

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

Домашние задания в рамках курса проверяться не будут!

ОП.08 - 12 - Решение практических задач в рамках БД с несколькими таблицами

Примеры данной темы используют учебную БД:

Описание связей в структуре БД:

SHELVES к BOOKS — одна полка может содержать много книг
FRIENDS к BOOKS — один друг может одолжить много книг
BOOKS к AUTHORS_BOOKS — одна книга может иметь нескольких авторов
AUTHORS к AUTHORS_BOOKS — один автор может написать несколько книг

bookshelf.sql

bookshelf.sql

Задания

Задание 1

Вывести все книги на полках (отобразить в виде: название книги, автор, полка на которой находится книга)

ОТВЕТ

SELECT
    books.title AS 'Название книги',
    authors.name AS 'Автор',
    shelves.title AS 'Полка'
FROM books
JOIN authors_books ON books.id = authors_books.books_id
JOIN authors ON authors_books.authors_id = authors.id
JOIN shelves ON books.shelves_id = shelves.id;

Задание 2

Вывести название полок, на которых есть книги, написанные автором 'Джейн Остин'

ОТВЕТ

SELECT DISTINCT shelves.title AS 'Полка'
FROM books
JOIN shelves ON books.shelves_id = shelves.id
JOIN authors_books ON books.id = authors_books.books_id
JOIN authors ON authors_books.authors_id = authors.id
WHERE authors.name = 'Джейн Остин';

Задание 3

Вывести название книг и имена авторов для книг, которые находятся на верхней полке в гостиной

ОТВЕТ

SELECT
  books.title AS 'Название книги',
  authors.name AS 'Автор'
FROM books
JOIN shelves ON books.shelves_id = shelves.id
JOIN authors_books ON books.id = authors_books.books_id
JOIN authors ON authors_books.authors_id = authors.id
WHERE shelves.title = 'Верхняя полка в гостиной';

Задание 4

Вывести имена друзей и названия книг, которые они взяли

ОТВЕТ

SELECT
  friends.name AS 'Друг',
  books.title AS 'Книга'
FROM books
JOIN friends ON books.friends_id = friends.id;

Задание 5

Вывести название книг, имена авторов и год издания для книг, которые были написаны после 1981 года

ОТВЕТ

SELECT
  books.title AS 'Название книги',
  authors.name AS 'Автор',
  books.year AS 'Год издания'
FROM books
JOIN authors_books ON books.id = authors_books.books_id
JOIN authors ON authors_books.authors_id = authors.id
WHERE books.year > 1981;

Задание 6

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

ОТВЕТ

SELECT
  books.title AS 'Название книги',
  authors.name AS 'Автор',
  books.year AS 'Год издания'
FROM books
JOIN shelves ON books.shelves_id = shelves.id
JOIN authors_books ON books.id = authors_books.books_id
JOIN authors ON authors_books.authors_id = authors.id
WHERE
  shelves.title = 'Нижняя полка в гостиной'
    AND
  books.year < 1990
ORDER BY books.year DESC;

Задание 7

Вывести название книг и имя автора для книг, которые были написаны авторами, чьи имена начинаются на букву "С"

ОТВЕТ

SELECT books.title AS 'Название книги', authors.name AS 'Автор'
FROM books
JOIN authors_books ON books.id = authors_books.books_id
JOIN authors ON authors_books.authors_id = authors.id
WHERE authors.name LIKE 'С%';

Задание 8

Вывести на какой полке находится больше всего книг (отобразить в виде: название полки, количество книг)

ОТВЕТ

SELECT
  shelves.title AS 'Полка',
  COUNT(books.id) AS 'Количество книг'
FROM shelves
JOIN books ON shelves.id = books.shelves_id
GROUP BY shelves.id
ORDER BY COUNT(books.id) DESC
LIMIT 1;

Задание 9

Вывести название книг, имена авторов и год издания для книг, которые были написаны до 1990 года и находятся на полке в кабинете

ОТВЕТ

SELECT
  books.title AS 'Название книги',
  authors.name AS 'Автор',
  books.year AS 'Год издания'
FROM books
JOIN shelves ON books.shelves_id = shelves.id
JOIN authors_books ON books.id = authors_books.books_id
JOIN authors ON authors_books.authors_id = authors.id
WHERE
  shelves.title = 'Полка в кабинете'
    AND
  books.year < 1990;

Задание 10

Вывести названия книг без указания года издания, а также имена их авторов и полки, на которых они находятся

ОТВЕТ

SELECT
  books.title AS 'Название книги',
  authors.name AS 'Автор',
  shelves.title AS 'Полка'
FROM books
JOIN authors_books ON books.id = authors_books.books_id
JOIN authors ON authors_books.authors_id = authors.id
JOIN shelves ON books.shelves_id = shelves.id
WHERE books.year IS NULL;