CREATE TABLE Books FREE (;
 Id I,;
 Author C(50),;
 Name C(100),;
 YearPubl N(4),;
 Price N(10),;
 Number N(4),;
 Annotation M,;
 Disposed L )
CREATE TABLE Readers FREE (;
 Id I,;
 FIO C(30),;
 Address C(50),;
 Phone C(10),;
 LibCard C(10) )
CREATE TABLE Abonement FREE (;
 Id I,;
 Id_Book I,;
 Id_Rreader I,;
 IssueDate D,;
 ReturnDate D )

SET DATE GERMAN
CLOSE TABLES

* текущая двта
ldCurrentDate=DATE()

* книги, имеющиеся в одном экземпляре
SELECT * FROM books WHERE number=1

* книги из билета Мельникова Н.Д.
SELECT PADR(b.author-','-b.name,30) as name;
 FROM abonement a INNER JOIN books b ON a.id_book=b.id INNER JOIN readers r ON a.id_reader=r.id;
 WHERE r.FIO='Мельников Николай Дмитриевич'

* читатели с задолженностью больше 4 месяцев
SELECT r.FIO;
 FROM abonement a INNER JOIN readers r ON a.id_reader=r.id;
 WHERE ISBLANK(a.ReturnDate) AND GOMONTH(ldCurrentDate,-4)>a.IssueDate

* книга, наиболее популярная весной 2012
SELECT PADR(b.author-','-b.name,30);
 FROM books b INNER JOIN;
 (SELECT TOP 1 id_book, count(*) as cnt FROM abonement;
   WHERE IssueDate<={^2012-05-31} AND (ISBLANK(ReturnDate) OR ReturnDate>={^2012-03-01});
   GROUP BY id_book;
   ORDER BY cnt DESC) t ON b.id=t.id_book

* читатели, имеющие на руках книги на сумму более 1000
SELECT r.FIO;
 FROM abonement a INNER JOIN books b ON a.id_book=b.id INNER JOIN readers r ON a.id_reader=r.id;
 WHERE ISBLANK(a.ReturnDate) AND a.IssueDate<=ldCurrentDate;
 GROUP BY r.FIO;
 HAVING SUM(b.price)>1000
 
* книга с наибольшим количеством экземпляров в наличии
SELECT PADR(bk.author-','-bk.name,30);
 FROM books bk INNER JOIN;
 (SELECT top 1 b.id, (b.number-NVL(t.cnt,0)) as num;
   FROM books b LEFT JOIN;
   (SELECT id_book, COUNT(*) as cnt FROM abonement;
    WHERE ISBLANK(ReturnDate) AND IssueDate<=ldCurrentDate;
    GROUP BY id_book) t ON b.id=t.id_book;
   ORDER BY num DESC) tk ON bk.id=tk.id