use sklep2_3ti1_11;
select * from klienci;
create table if not exists klienci (
	nr int auto_increment primary key not null,
    imie varchar(20),
    nazwisko varchar(30)
);
create table if not exists towary (
	id int auto_increment primary key not null,
    nazwa varchar(50),
    grupa int,
    cena decimal(10,2)
);
create table if not exists zamowienia (
	id int auto_increment primary key not null,
    klient_id int,
    toawr_id int,
    data date,
    wartosc decimal(10,2),
    foreign key (klient_id) references klienci(nr),
    foreign key (toawr_id) references towary(id)
);

insert into klienci (imie,nazwisko) values 
("Jan","Kowalski"),
("Andrzej","Nowak"),
("Janusz","Malinowski"),
("Adam","Kowalski"),
("Krzysztof","Nowicki");
insert into towary (nazwa,grupa,cena) values
("sruby",1,2.00),
("nakretki",1,3.00),
("katowniki",2,8.00),
("plaskowniki",2,9.00),
("gwozdzie",1,1.00),
("panele",3,15.00),
("wkrety",1,4.00),
("deski",3,12.00),
("plyty",3,19.00);
insert into zamowienia (klient_id,toawr_id,data,wartosc) values
(1,2,'2007-01-01',12.44),
(1,4,'2007-01-01',10.22),
(1,2,'2007-02-12',15.88),
(2,1,'2007-01-01',22.35),
(2,1,'2007-02-12',29.00),
(2,4,'2007-03-01',2.28),
(3,1,'2007-02-11',18.48),
(3,4,'2007-01-11',12.44),
(4,1,'2007-03-11',15.26),
(5,4,'2007-03-02',6.11);

create table zam_tymczas (
imie varchar(20),
nazwisko varchar(30),
wartosc decimal(7,2));

insert into zam_tymczas (imie,nazwisko,wartosc) 
(
	select imie,nazwisko,wartosc
    from klienci,zamowienia
    where klienci.nr=zamowienia.klient_id
);

delete from zam_tymczas where wartosc<(select avg(wartosc) from zamowienia);
# 1
use sklep2_3ti1_11;
select toawr_id from zamowienia having min(wartosc);
select toawr_id, wartosc from zamowienia order by wartosc;
select toawr_id from zamowienia where  wartosc = (select min(wartosc) from zamowienia);
# 2
select nazwa, id from towary where id = (select toawr_id from zamowienia having min(wartosc));
# 3
select towary.nazwa,towary.id, m from towary join (select max(wartosc) as m, toawr_id from zamowienia group by toawr_id) z on towary.id = z.toawr_id; 
select max(wartosc) from zamowienia group by toawr_id;
# 4
select nazwa,cena,towary.grupa from towary join  (select avg(cena) as sr ,grupa from towary group by grupa) as srednia on towary.grupa = srednia.grupa where cena > sr;


#1
use biuro;
select count(*) from wlasciciel where adres like '%Bialystok%';
#2
select count(*) from biuro where miasto="Lomza";
#3
select * from biuro;
select * from personel;
select biuroNr,count(biuroNr) as xd from personel where biuroNr in (select biuroNr from personel  group by biuroNr having count(*)>=3) group by biuroNr;
#4
select * from nieruchomosc;
select * from wynajecie;
select nieruchomoscNr, count(nieruchomoscNr) from wynajecie where nieruchomoscNr in (select nieruchomoscNr from wynajecie group by nieruchomoscNr having count(*)>2) group by nieruchomoscNr;
#5
select * from nieruchomosc;
select count(*) from nieruchomosc where personelNr in (select personelNr from nieruchomosc group by personelNr having count(*)>3) group by personelNr;
#6
select * from wynajecie;
select klientnr,count(*) from wynajecie where klientnr in (select klientnr from wynajecie group by klientnr having count(*)>=2) group by klientnr;
#7
select * from personel;
select biuroNr,avg(pensja) from personel group by biuroNr;
#8
select * from personel;
select * from personel where year(dataUr) in (select year(dataUr) from personel where ((year(current_date)-year(dataUr))>=65));
#9
select * from wynajecie;
select nieruchomoscNr, count(nieruchomoscNr) from wynajecie where umowanr in (select umowanr from wynajecie where month(od_kiedy) >=9 and month(od_kiedy) <=12 and year(od_kiedy) = '2004' ) group by nieruchomoscNr;
#10
select * from wynajecie;
select klientnr,count(klientnr) from wynajecie where umowanr in (select umowanr from wynajecie where month(od_kiedy) = 3 and year(od_kiedy) = 2003) group by klientnr;