Rakendusserverid — Work 4

Ссылка на задание: MySQL part 2

Задание 1

Вывести автора и название книг, количество которых принадлежит интервалу от 2 до 14 (включая границы). Информацию отсортировать сначала по авторам (в обратном алфавитном порядке), а затем по названиям книг (по алфавиту).

Код:

select author, title
	from book
	where amount between 2 and 14
	order by author desc, title asc
;

Пример:

Задание 2

Отобрать различные (уникальные) элементы столбца amount таблицы book.

Код:

select amount
	from book
	group by amount
;

Пример:

Задание 3

Посчитать, количество различных книг и количество экземпляров книг каждого автора, хранящихся на складе. Столбцы назвать Автор, Различных_книг и Количество_экземпляров соответственно.

Что-бы проверить запрос, добавим в таблицу строку:

insert into book (title, author, price, amount)
values
	('Черный человек', 'Есенин С.А.', Null, Null)
;

Код:

select
	author as Автор,
	count(amount) as 'Различных книг',
	sum(amount) as 'Кол-во экземпляров'
	from book
	group by author
;

Пример:

Задание 4

Вывести фамилию и инициалы автора, минимальную, максимальную и среднюю цену книг каждого автора. Вычисляемые столбцы назвать Минимальная_цена, Максимальная_цена и Средняя_цена соответственно.

Код:

select
	author,
	min(price) as 'Минимальная цены',
	max(price) as 'Максимальная цена',
	avg(price) as 'Средняя цена'
	from book
	group by author
;

Пример:

Задание 5

Для каждого автора вычислить суммарную стоимость книг S (имя столбца Стоимость), а также вычислить налог на добавленную стоимость для полученных сумм (имя столбца НДС), который включен в стоимость и составляет k = 18%, а также стоимость книг (Стоимость_без_НДС) без него. Значения округлить до двух знаков после запятой. В запросе для расчета НДС(tax) и Стоимости без НДС(S_without_tax) использовать следующие формулы:

Код:

select
	author,
	round(sum(price * amount), 2) as 'Стоимость',
	round(sum(price * amount) * 18 / 100 / (1 + 18 / 100), 2) as 'НДС',
	round(sum(price * amount) / (1 + 18 / 100), 2) as 'Стоимость без НДС'
	from book
	group by author
;

Пример:

Задание 6

Вывести цену самой дешевой книги, цену самой дорогой и среднюю цену уникальных книг на складе. Названия столбцов Минимальная_цена, Максимальная_цена, Средняя_цена соответственно. Среднюю цену округлить до двух знаков после запятой.

Код:

select
	min(price) as 'Минимальная цена',
	max(price) as 'Максимальная цена',
	round(avg(distinct price), 2) as 'Средняя цена'
	from book
;

Пример:

Задание 7

Вычислить среднюю цену и суммарную стоимость тех книг, количество экземпляров которых принадлежит интервалу от 5 до 14, включительно. Столбцы назвать Средняя_цена и Стоимость, значения округлить до 2-х знаков после запятой.

Код:

select
	round(avg(price), 2) as 'Средняя цена',
	round(sum(price * amount), 2) as 'Стоимость'
	from book
	where amount between 5 and 14
;

Пример:

Задание 8

Посчитать стоимость всех экземпляров каждого автора без учета книг «Идиот» и «Белая гвардия». В результат включить только тех авторов, у которых суммарная стоимость книг (без учета книг «Идиот» и «Белая гвардия») более 5000 руб. Вычисляемый столбец назвать Стоимость. Результат отсортировать по убыванию стоимости.

Код:

select
	author,
	round(sum(price * amount), 2) as 'Стоимость'
	from book
	where title not in ('Идиот', 'Белая гвардия')
	group by author
	having sum(price * amount) > 5000
	order by 'Стоимость' desc
;

Пример:

Задание 9

Вывести информацию (автора, название и цену) о книгах, цены которых меньше или равны средней цене книг на складе. Информацию вывести в отсортированном по убыванию цены виде. Среднее вычислить как среднее по цене книги.

Код:

select
	author, title, price
	from book
	where price <= (
		select avg(price)
		from book
	)
	order by price desc
;

Пример:

Задание 10

Вывести информацию (автора, название и цену) о тех книгах, цены которых превышают минимальную цену книги на складе не более чем на 150 рублей в отсортированном по возрастанию цены виде.

Код:

select
	author, title, price
	from book
	where abs(price - (select min(price) from book)) < 150
	order by price asc
;

Пример:

Задание 11

Вывести информацию (автора, книгу и количество) о тех книгах, количество экземпляров которых в таблице book не дублируется.

Что-бы запрос отработал, нам опять же понадобится добавить новую запись в таблицу:

insert into book (title, author, price, amount) values ('Братья Карамазовы', 'Достоевский Ф.М.', 799.01, 3);

Код:

select
	author, title, amount
	from book
	where book_id in (
		select book_id
		from book
		group by amount
		having count(amount) = 1
	)
	order by amount asc
;

Пример:

Задание 12

Вывести информацию о книгах (автор, название, цена), цена которых меньше самой большой из минимальных цен, вычисленных для каждого автора.

Код:

select
	author, title, price
	from book
	where price < any (
		select min(price)
		from book
		group by author
	)
;

Пример:

Задание 13

Посчитать сколько и каких экземпляров книг нужно заказать поставщикам, чтобы на складе стало одинаковое количество экземпляров каждой книги, равное значению самого большего количества экземпляров одной книги на складе. Вывести название книги, ее автора, текущее количество экземпляров на складе и количество заказываемых экземпляров книг. Последнему столбцу присвоить имя Заказ. В результат не включать книги, которые заказывать не нужно.

Код:

select
	title, author, amount, (
		select max(amount)
		from book
	) - amount as 'Заказ'
	from book
	where amount < (
		select max(amount)
		from book
	)
;

Пример:

Задание 14

Создать таблицу поставка (supply), которая имеет ту же структуру, что и таблиц book.

Код:

create table supply(
	supply_id int primary key auto_increment,
	title varchar(50),
	author varchar(30),
	price decimal(8, 2),
	amount int
);

Пример:

Задание 15

Занесите в таблицу supply четыре записи, чтобы получилась следующая таблица:

Код:

insert into supply (title, author, price, amount)
values
	('Лирика', 'Пастернак Б.Л.', 518.99, 2),
	('Черный человек', 'Есенин С.А.', 570.20, 6),
	('Белая гвардия', 'Булгаков М.А.', 540.50, 7),
	('Идиот', 'Достоевский Ф.М.', 360.80, 3)
;

select * from supply;

Пример:

Задание 16

Добавить из таблицы supply в таблицу book, все книги, кроме книг, написанных Булгаковым М.А. и Достоевским Ф.М.

Код:

insert into book (title, author, price, amount)
	select title, author, price, amount
	from supply
	where author not in (
		'Булгаков М.А.', 'Достоевский Ф.М.'
	)
;

select * from book;

Пример:

Задание 17

Занести из таблицы supply в таблицу book только те книги, авторов которых нет в book.

Код:

insert into book (title, author, price, amount)
	select title, author, price, amount
	from supply
	where author not in (
		select distinct author
		from book
	)
;

Пример:

Задание 18

Уменьшить на 10% цену тех книг в таблице book, количество которых принадлежит интервалу от 5 до 10, включая границы.

Код:

update book 
	set price = 0.9 * price 
	where amount between 5 and 10
;

select * from book;

Пример: