LIMIT 1)

Или

SELECT `s_name` FROM `subscribers` WHERE `s_id` IN

(SELECT `sb_subscriber` FROM `subscriptions` WHERE `sb_is_active`='Y' GROUP BY `sb_subscriber` HAVING COUNT(`sb_subscriber`) > 1)

s_name	has_books
Петров П.П.

22. Написать запрос, показывающий книгу, представленную в библиотеке максимальным количеством экземпляров.

Первый вариант решения:

SELECT `b_name` FROM `books` ORDER BY `b_quantity` DESC LIMIT 1

Второй вариант решения (если таких книг несколько):

SELECT `b_name` FROM `books` WHERE `b_quantity` =

(SELECT MAX(`b_quantity`) FROM `books`)

Третий вариант решения (если считается, что эта книга должна быть «абсолютным рекордсменом», т.е. не должно быть других книг с таким же количеством экземпляров):

SELECT `b_name` FROM `books` AS `outer` WHERE `b_quantity` >

ALL (SELECT `b_quantity` FROM `books` AS `inner`

WHERE `inner`.`b_id`!=`outer`.`b_id`)

b_name

Основание и империя

23. Написать запрос, показывающий, сколько реально экземпляров каждой книги сейчас есть в библиотеке.

SELECT `b_name`, `b_quantity` - IFNULL((SELECT COUNT(`sb_book`)

FROM `books` AS `inner`

JOIN `subscriptions` ON `b_id` = `sb_book` WHERE

`sb_is_active` = 'Y' AND `sb_book` = `outer`.`b_id`

GROUP BY `b_id`), 0) AS `in_library` FROM `books` AS `outer`

b_name	in_library
С++
Сказка о золотой рыбке
Евгений Онегин
Основание и империя
Психология программирования

Так – НЕЛЬЗЯ! Не учитываются «подписки», где книга уже возвращена. Если начать это учитывать через WHERE, отсекается информация о случаях, когда книгу вернули, и в выборку попадают НЕ ВСЕ книги.

SELECT `b_name`, (`b_quantity` - COUNT(`sb_book`)) AS `in_library`

FROM `books` LEFT OUTER JOIN `subscriptions` ON `b_id` = `sb_book`

GROUP BY `b_id`

24. Написать запрос, показывающий, сколько экземпляров каждой книги сейчас выдано подписчикам.

SELECT `b_name`, COUNT(`sb_book`) AS `given` FROM `books`

JOIN `subscriptions` ON `b_id` = `sb_book`

WHERE `sb_is_active` = 'Y' GROUP BY `b_id`

b_name	given
С++
Сказка о золотой рыбке

25. Написать запрос, показывающий, сколько всего книг выдано подписчикам.

Этот вариант считает экземоляры книг:

SELECT COUNT(*) AS `given_total` FROM `subscriptions`

WHERE `sb_is_active` = 'Y'

Этот вариант считает сами книги (без учёта количества выданных экземпляров):

SELECT COUNT(DISTINCT `sb_book`) AS `given_total` FROM `subscriptions`

WHERE `sb_is_active` = 'Y'

given_total

26. Написать запрос, показывающий среднее количество экземпляров книг в библиотеке.

SELECT AVG(`b_quantity`) AS `avg` FROM `books`

avg

2.2000

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

SELECT AVG(`count_for_one`) AS `avg` FROM (SELECT COUNT(`sb_book`)

AS `count_for_one` FROM `subscriptions` WHERE `sb_is_active` = 'Y'

GROUP BY `sb_subscriber`) AS `tmp`

avg

1.5000

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

SELECT `a_name`, SUM(`b_quantity`) AS `quantity` FROM `authors`

JOIN `m2m_books_authors` USING (`a_id`) JOIN `books`

USING (`b_id`) GROUP BY `a_id` ORDER BY `a_name` ASC

a_name	quantity
А. Азимов
А. Пушкин
Б. Страуструп
Д. Карнеги

29. Написать запрос, показывающий, сколько в среднем книг (не экземпляров, а разных книг!) приходится на одного автора.

SELECT AVG(`quantity`) as `avg` FROM (SELECT COUNT(`b_id`)

AS `quantity` FROM `m2m_books_authors` GROUP BY `a_id`) AS `tmp`

avg

1.5000

30. Написать запрос, показывающий, сколько в среднем экземпляров книг приходится на одного автора.

SELECT AVG(`quantity`) as `avg` FROM (SELECT SUM(`b_quantity`) AS `quantity`

FROM `authors`

JOIN `m2m_books_authors` USING (`a_id`)

JOIN `books` USING (`b_id`) GROUP BY `a_id`) AS `tmp`

avg

3.2500

31. Написать запрос, показывающий авторов, по которым в библиотеке больше всего книг (не экземпляров, а разных книг!).

SELECT `a_name`, COUNT(`b_id`) AS `quantity` FROM `authors`

JOIN `m2m_books_authors` USING (`a_id`) GROUP BY `a_id`

HAVING `quantity` = (SELECT COUNT(`b_id`) AS `quantity` FROM `authors`

JOIN `m2m_books_authors` USING (`a_id`) GROUP BY `a_id`

ORDER BY `quantity` DESC LIMIT 1)

a_name	quantity
А. Пушкин
Б. Страуструп

32. Написать запрос, показывающий подписчиков, быстрее всего вернувших книги в библиотеку (учитывать только случаи, когда книга возвращена).

SELECT `s_name`, DATEDIFF(`sb_finish`, `sb_start`) AS `days`

FROM `subscribers`

JOIN `subscriptions` ON `s_id` = `sb_subscriber` WHERE `sb_is_active` = 'N'

HAVING `days` = (SELECT DATEDIFF(`sb_finish`, `sb_start`) AS `days`

FROM `subscribers` JOIN `subscriptions` ON `s_id` = `sb_subscriber`

WHERE `sb_is_active` = 'N' ORDER BY `days` ASC LIMIT 1)

SELECT `s_name`, DATEDIFF(`sb_finish`, `sb_start`) AS `days`

FROM `subscribers`

JOIN `subscriptions` ON `s_id` = `sb_subscriber` WHERE `sb_is_active` = 'N'

HAVING `days` = (SELECT DATEDIFF(`sb_finish`, `sb_start`) AS `days`

FROM `subscriptions` WHERE `sb_is_active` = 'N' ORDER BY `days` ASC

LIMIT 1)

s_name	days
Иванов И.И.

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

SELECT `s_name`, DATEDIFF(`sb_finish`, `sb_start`) AS `days` FROM `subscribers`

JOIN `subscriptions` ON `s_id` = `sb_subscriber`

WHERE `sb_is_active` = 'N' HAVING `days` =

(SELECT DATEDIFF(`sb_finish`, `sb_start`) AS `days` FROM `subscribers`

JOIN `subscriptions` ON `s_id` = `sb_subscriber`

WHERE `sb_is_active` = 'N' ORDER BY `days` DESC LIMIT 1)

SELECT `s_name`, DATEDIFF(`sb_finish`, `sb_start`) AS `days` FROM `subscribers`

JOIN `subscriptions` ON `s_id` = `sb_subscriber`

WHERE `sb_is_active` = 'N' HAVING `days` =

(SELECT DATEDIFF(`sb_finish`, `sb_start`) AS `days` FROM `subscriptions` WHERE `sb_is_active` = 'N' ORDER BY `days` DESC LIMIT 1)

s_name	days
Иванов И.И.

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

SELECT `s_name`, COUNT(`sb_id`) AS `not_returned` FROM `subscribers`

JOIN `subscriptions` ON `s_id` = `sb_subscriber`

WHERE `sb_is_active`='Y' AND `sb_finish` < CURDATE() GROUP BY `s_id`

s_name	not_returned
Иванов И.И.
Петров П.П.

35. Написать запрос, показывающий суммарное количество экземпляров книг, находящихся сейчас на руках у подписчиков.

SELECT COUNT(`sb_id`) AS `in_use` FROM `subscriptions` WHERE `sb_is_active`='Y'

in_use

36. Написать запрос, проверяющий, не была ли допущена ошибка в заполнении документов, при которой оказывается, что на руках сейчас большее количество экземпляров некоторой книги, чем их было в библиотеке. Вернуть 1 (TRUE), если ошибка есть и 0 (FALSE), если ошибки нет.

SELECT EXISTS (SELECT `b_quantity` - IFNULL((SELECT COUNT(`sb_book`)

FROM `books` AS `inner` JOIN `subscriptions` ON `b_id` = `sb_book`

WHERE `sb_is_active` = 'Y' AND `sb_book` = `outer`.`b_id` GROUP BY `b_id`), 0)

AS `in_library` FROM `books` AS `outer` HAVING `in_library`<0) AS `error_found`

error_found

37. Написать запрос, показывающий, сколько в среднем подписчики читают книгу. Учесть случаи, когда книги были и не были возвращены.

SELECT AVG(DATEDIFF(`sb_finish`, `sb_start`)) AS `avg_days` FROM `subscriptions`

avg_days

100.6667

38. Написать запрос, показывающий подписчика, бравшего больше всего книг.

SELECT `s_name`, COUNT(`sb_id`) AS `got_books` FROM `subscribers`

JOIN `subscriptions` ON `s_id` = `sb_subscriber`

GROUP BY `s_id` ORDER BY `got_books` DESC LIMIT 1

s_name	got_books
Иванов И.И.

39. Написать запрос, показывающий наиболее читаемого автора.

SELECT `a_name`, COUNT(`sb_book`) AS `times_read` FROM `authors`

JOIN `m2m_books_authors` USING (`a_id`)

JOIN `subscriptions` ON `sb_book` = `b_id`

GROUP BY `a_id` ORDER BY `times_read` DESC LIMIT 1

a_name	times_read
А. Пушкин

40. Написать запрос, показывающий наиболее читаемый жанр.

SELECT `g_name`, COUNT(`sb_book`) AS `times_read` FROM `genres`

JOIN `m2m_books_genres` USING (`g_id`)

JOIN `subscriptions` ON `sb_book` = `b_id` GROUP BY `g_id`

ORDER BY `times_read` DESC LIMIT 1

g_name	times_read
Поэзия