---
# System prepended metadata

title: Database HW6

---

# 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