/*
Ex.1

create database testDB

*/

/*
Ex.2

create table readers (
	pk int primary key,
	name char(40) not null,
	fnum char(10) not null,
	spec char(30) not null
);
*/

/*
Ex.3
create table books (
pk int primary key,
name char(60) not null,
price money not null,
isbn char(13),
pyear int
);

*/


/*
Ex.4
create table files (
	pk int primary key,
	daterent datetime not null,
	fk_r int not null foreign key references readers(pk) on delete cascade on update no action,
	fk_b int not null foreign key references books(pk) on delete cascade on update no action,
	tdelay int
)

*/

/*
Ex.5
insert into readers (pk, name, fnum, spec)
values(1, 'Иван Димитров', 018010, 'ИНФ'),
	  (2, 'Стела Илиева', 019010, 'КСТ'),
	  (3, 'Димитър Стоев', 018011, 'ИНФ')
*/
/*
Ex.6
update readers
set fnum = 017010, spec = 'ФИЗ'
where pk = 2;
*/

/*
Ex.7

insert into books (pk, name, price, isbn, pyear)
values(1, 'Delphi 6', 26, '0-14-020652-3', 2012),
	  (2, 'The Road', 17, '1-4133-0454-0', 2013)
*/
/*
Ex.8
update books
set name = 'The words', pyear = 2014
where pk = 2;

*/

/*
Ex.9
insert into books (pk, name, price, isbn, pyear)
values(3, 'DB Systems', 34, '0-13-187325-3', 2008),
	  (4, 'Excel 2010', 29, '0-4704-7487-4', 2010),
	  (5, 'SQL Server', 21, '0-2314-5515-X', 2012),
      (6, 'MS Excel XP', 18, '0-2314-8321-2', 2013)
*/

/*
Ex.10
delete from books where pk = 2;
*/
/*
Ex.11
insert into files (pk, daterent, fk_r, fk_b, tdelay)
values(1, '11-11-2013', 2, 3, null),
	  (2, '11-11-2013', 2, 5, null),
	  (3, '12-12-2013', 1, 4, null),
	  (4, '12-12-2013', 1, 5, null),
	  (5, '12-12-2013', 3, 3, null)
*/

/*
Ex.12
update files set tdelay = 14 where (fk_r = 2) and (fk_b = 3) and (daterent = '11-11-2013');
update files set tdelay = 7 where (fk_r = 1) and (fk_b = 4) and (daterent = '12-12-2013');
*/

/*
Ex. 13
select name, isbn, price, pyear
from books
where(pyear >= 2009) and (pyear <= 2012)
order by pyear desc, price asc;
*/

/*
Ex.14
select name, price, pyear
from books
order by pyear desc;
*/

/*
Ex.15
select name, price, pyear
from books
where name like '%Excel%'
order by pyear desc;
*/

/*
Ex.16
select
	b.name as 'Book name',
	r.name as 'Reader name',
	f.daterent as 'Date Rent'
from
	files as f join readers as r on f.fk_r = r.pk join books as b on f.fk_b  = b.pk
order by
	b.name desc
*/

/*
Ex.17
select
	f.daterent, r.name, b.name, b.isbn, f.tdelay
from
	files as f join readers as r on f.fk_r = r.pk join books as b on f.fk_b = b.pk
where
	f.tdelay >= 5
order by
	r.name asc
*/
/*
Ex.18
select
	b.name, count(f.fk_b) as 'Count rents'
from
	books as b join files as f on f.fk_b = b.pk
group by
	b.name
*/

/*
Ex.19
select
	r.name, count(f.fk_r) as 'Count rents'
from
	readers as r join files as f on f.fk_r = r.pk
group by
	r.name

*/

/*
Ex.20
select
	r.spec, count(f.fk_b) as 'Count books', sum(b.price) as 'Sum price'
from
	files as f join readers as r on f.fk_r = r.pk join books as b on f.fk_b = b.pk
group by
	r.spec
*/