1
use lab1;
select name,cost,sum(amount) from book
join lot on bookid=fk_lot_book
where YEAR(date_form) = 2013
group by name,cost;
2
use lab1;
select pub_name, name from pub_house
join lot on pub_id=fk_lot_pub
join book on fk_lot_book=bookid
where YEAR(date_form) = 2013
group by pub_name,name;
3
use lab1;
select name,genre,author from book
join lot on bookid=fk_lot_book
where cost = (select MAX(cost) from lot)
group by name;
4
use lab1;
select pub_name,name,sum(sum_cost),sum(amount) from pub_house
join lot on pub_id=fk_lot_pub
join book on bookid=fk_lot_book
group by pub_name,name;
5
use lab1;
select * from pub_house
left join lot on pub_id=fk_lot_pub
where lot_id is NULL;
6
use lab1;
select * from pub_house
where pub_id not in(select fk_lot_pub from lot where YEAR(date_form) = 2013)
group by pub_name;
7
use lab1;
create view v as
select pub_house.pub_name,pub_house.city,pub_house.contract_num,pub_house.contract_date,count(lot.fk_lot_book)
from pub_house,lot
where fk_lot_pub=pub_id
group by pub_name;

select * from v;