# Database HW6
### Andreev Anton BSE206
## Task №1
* Показать все названия книг вместе с именами издателей.
```sql
SELECT title, pubname FROM book
```
* В какой книге наибольшее количество страниц?
```sql
SELECT title, MAX(pagesnum) as pagesnum FROM book
GROUP BY title
```
* Какие авторы написали более 5 книг?
```sql
SELECT author, COUNT(author) FROM book
GROUP BY author
HAVING COUNT(author) >= 5;
```
* В каких книгах более чем в два раза больше страниц, чем среднее количество страниц для всех книг?
```sql
SELECT title FROM book
WHERE pagesnum > 2 * (SELECT AVG(pagesnum) FROM book)
```
* Какие категории содержат подкатегории?
```sql
SELECT DISTINCT parentcat FROM category
WHERE parentcat IS NOT Null
```
* У какого автора (предположим, что имена авторов уникальны) написано максимальное количество книг?
```sql
SELECT author, COUNT(*) AS Number_of_books FROM book
GROUP BY author
ORDER BY Number_of_books DESC
LIMIT 1
```
* Какие читатели забронировали все книги (не копии), написанные "Марком Твеном"?
```sql
SELECT firstname, lastname FROM reader
JOIN borrowing b on reader.number = b.readernr
JOIN book b2 on b.isbn = b2.isbn
WHERE author = 'Марк Твен'
GROUP BY firstname, lastname
HAVING count(b.isbn) = (SELECT COUNT(*) FROM book WHERE author = 'Марк Твен')
```
* Какие книги имеют более одной копии?
```sql
SELECT isbn FROM copy
GROUP BY isbn
HAVING count(*) > 1
```
* ТОП 10 самых старых книг
```sql
SELECT * FROM book
ORDER BY pubyear
LIMIT 10
```
* Перечислите все категории в категории “Спорт” (с любым уровнем вложености).
```sql
SELECT * FROM category
WHERE parentcat = 'Спорт'
```
## Task №2
* Добавьте запись о бронировании читателем ‘Василеем Петровым’ книги с ISBN 123456 и номером копии 4.
```sql
INSERT INTO borrowing (copynumber, isbn, readernr, returndate) VALUES (4, 123456, (SELECT number FROM reader
WHERE firstname = 'Василий' and lastname = 'Петров'), (now() + interval '10 day'))
```
* Удалить все книги, год публикации которых превышает 2000 год.
```sql
DELETE FROM book
WHERE (SELECT EXTRACT (YEAR FROM pubyear)) > 2000
```
* Измените дату возврата для всех книг категории "Базы данных", начиная с 01.01.2016, чтобы они были в заимствовании на 30 дней дольше
```sql
UPDATE borrowing AS bor
SET returndate = returndate + INTERVAL '30 day'
WHERE bor.isbn in (SELECT bc.isbn FROM book_category as bc
JOIN book b on bc.isbn = b.isbn
WHERE categoryname = 'Базы данных' AND bor.returndate >= DATE('01.01.2016'))
```
## Task №3
Опишите на русском языке результаты следующих запросов:
* Student( MatrNr, Name, Semester )
* Check( MatrNr, LectNr, ProfNr, Note )
* Lecture( LectNr, Title, Credit, ProfNr )
* Professor( ProfNr, Name, Room )
1.
```sql
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 ) ;
```
Получить имена + номера студентов, у которых есть оценки ниже 4
2.
```sql
(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
3.
```sql
SELECT s.Name, с.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 )
```
Получить имена и оценки студентов, которые они получили, при условии, что оценки >= 4 и оценка >= всех остальных оценок в таблице Check
Sign in with Wallet
Connect another wallet