# 📊 HW 6 DB HSE
## by Anna Sibirtseva BSE206
**17.10.2022**
### ⭐ Task 1
> Напишите SQL-запросы:

#### 🔸 task 1.1 Показать все названия книг вместе с именами издателей.

SELECT Title, PubName
FROM Book 

#### 🔸 task 1.2 В какой книге наибольшее количество страниц?

SELECT Title, Author
FROM Book
ORDER BY PagesNum DESC
LIMIT 1

#### 🔸 task 1.3 Какие авторы написали более 5 книг?

SELECT Author
FROM Book
GROUP BY Author
HAVING COUNT(*) > 5

#### 🔸 task 1.4 В каких книгах более чем в два раза больше страниц, чем среднее количество страниц для всех книг?

SELECT Title, Author
FROM Book
WHERE PagesNum > (SELECT AVG(PagesNum) * 2
                  FROM Book)

#### 🔸 task 1.5 Какие категории содержат подкатегории?

SELECT ParentCat
FROM Category
WHERE ParentCat IS NOT NULL
  AND CategoryName IS NOT NULL

#### 🔸 task 1.6 У какого автора (предположим, что имена авторов уникальны) написано максимальное количество книг?

SELECT Author
FROM Book
GROUP BY Author
ORDER BY COUNT(*) DESC
LIMIT 1

#### 🔸 task 1.7 Какие читатели забронировали все книги (не копии), написанные "Марком Твеном"?

SELECT FirstName, lastname
FROM Reader
JOIN Borrowing ON Reader.ID = Borrowing.ReaderNr
JOIN Book ON Borrowing.ISBN = Book.isbn
WHERE Author = 'Марк Твен'
GROUP BY ID
HAVING COUNT(DISTINCT Book.isbn) =
       (SELECT COUNT(*)
        FROM Book
        WHERE Author = 'Марк Твен')

#### 🔸 task 1.8 Какие книги имеют более одной копии?

SELECT Title, Author
FROM Book
WHERE isbn in(SELECT isbn
              FROM Copy
              GROUP BY isbn
              HAVING COUNT(*) > 1)

#### 🔸 task 1.9 ТОП 10 самых старых книг

SELECT Title, Author
FROM Book
ORDER BY PubYear
LIMIT 10

#### 🔸 task 1.10 Перечислите все категории в категории “Спорт” (с любым уровнем вложености).

WITH RECURSIVE SportCategory AS
                   (SELECT CategoryName
                    FROM Category
                    WHERE ParentCat = 'Спорт'
                    UNION
                    SELECT CategoryName
                    FROM Category
                    JOIN SportCategory ON Category.ParentCat = SportCategory.CategName)
SELECT DISTINCT CategoryName
FROM SportCategory

### ⭐ Task 2

> Напишите SQL-запросы для следующих действий:

#### 🔸 task 2.1 Добавьте запись о бронировании читателем ‘Василеем Петровым’ книги с ISBN 123456 и номером копии 4.

INSERT INTO Borrowing (ReaderNr, ISBN, CopyNumber, ReturnDate)
VALUES
((SELECT ID,
FROM Reader
WHERE FirstName = 'Василий' AND LastName = 'Петров'), '123456', 4, CURRENT_TIMESTAMP)

#### 🔸 task 2.2 Удалить все книги, год публикации которых превышает 2000 год.

DELETE
FROM Book
WHERE (EXTRACT(YEAR FROM PubYear) > 2000);

#### 🔸 task 2.3 Измените дату возврата для всех книг категории "Базы данных", начиная с 01.01.2016, чтобы они были в заимствовании на 30 дней дольше (предположим, что в SQL можно добавлять числа к датам).

UPDATE Borrowing
SET ReturnDate = ReturnDate + interval '30 day'
WHERE ReturnDate > '2016-01-01 00:00:00'
	AND ISBN in (SELECT ISBN
                 FROM BookCat
                 WHERE CategoryName = 'Базы данных')

### ⭐ Task 3
> Опишите на русском языке результаты следующих запросов:
#### 🔸 task 3.1

SELECT s.Name, s.MatrNr FROM Student s
WHERE NOT EXISTS
(SELECT * FROM Check c WHERE c.MatrNr = s.MatrNr AND c.Note >= 4.0 ) ;

**Описание:** Найти всех студентов (Имя и ID), у которых есть хотя бы одна неудволетворительная (меньше 4) оценка за лекцию или вообще нет оценки.

#### 🔸 task 3.2

( SELECT p.ProfNr, p.Name, sum(lec.Credit)
FROM Professor p, Lecture lec
WHERE p.ProfNr = lec.ProfNr
GROUP BY p.ProfNr, p.Name)
UNION
( SELECT p.ProfNr, p.Name, 0
FROM Professor p
WHERE NOT EXISTS (
SELECT * FROM Lecture lec WHERE lec.ProfNr = p.ProfNr ));

**Описание:** Найти и вывести информацию о проффессоре (его номер, имя и сумму зачетных часов(кредитов) за лекции), если он ведет хотя бы одну лекцию и 0, если проффессор не вялется лектором.

#### 🔸 task 3.3

SELECT s.Name, p.Note
FROM Student s, Lecture lec, Check c
WHERE s.MatrNr = c.MatrNr AND lec.LectNr = c.LectNr AND c.Note >= 4
AND c.Note >= ALL (
SELECT c1.Note FROM Check c1 WHERE c1.MatrNr = c.MatrNr )

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