1. use sklep2_3ti1_11;
2. select * from klienci;
3. create table if not exists klienci (
4.         nr int auto_increment primary key not null,
5.     imie varchar(20),
6.     nazwisko varchar(30)
7. );
8. create table if not exists towary (
9.         id int auto_increment primary key not null,
10.     nazwa varchar(50),
11.     grupa int,
12.     cena decimal(10,2)
13. );
14. create table if not exists zamowienia (
15.         id int auto_increment primary key not null,
16.     klient_id int,
17.     toawr_id int,
18.     data date,
19.     wartosc decimal(10,2),
20.     foreign key (klient_id) references klienci(nr),
21.     foreign key (toawr_id) references towary(id)
22. );
23.  
24. insert into klienci (imie,nazwisko) values
25. ("Jan","Kowalski"),
26. ("Andrzej","Nowak"),
27. ("Janusz","Malinowski"),
28. ("Adam","Kowalski"),
29. ("Krzysztof","Nowicki");
30. insert into towary (nazwa,grupa,cena) values
31. ("sruby",1,2.00),
32. ("nakretki",1,3.00),
33. ("katowniki",2,8.00),
34. ("plaskowniki",2,9.00),
35. ("gwozdzie",1,1.00),
36. ("panele",3,15.00),
37. ("wkrety",1,4.00),
38. ("deski",3,12.00),
39. ("plyty",3,19.00);
40. insert into zamowienia (klient_id,toawr_id,data,wartosc) values
41. (1,2,'2007-01-01',12.44),
42. (1,4,'2007-01-01',10.22),
43. (1,2,'2007-02-12',15.88),
44. (2,1,'2007-01-01',22.35),
45. (2,1,'2007-02-12',29.00),
46. (2,4,'2007-03-01',2.28),
47. (3,1,'2007-02-11',18.48),
48. (3,4,'2007-01-11',12.44),
49. (4,1,'2007-03-11',15.26),
50. (5,4,'2007-03-02',6.11);
51.  
52. create table zam_tymczas (
53. imie varchar(20),
54. nazwisko varchar(30),
55. wartosc decimal(7,2));
56.  
57. insert into zam_tymczas (imie,nazwisko,wartosc)
58. (
59.         select imie,nazwisko,wartosc
60.     from klienci,zamowienia
61.     where klienci.nr=zamowienia.klient_id
62. );
63.  
64. delete from zam_tymczas where wartosc<(select avg(wartosc) from zamowienia);
65. # 1
66. use sklep2_3ti1_11;
67. select toawr_id from zamowienia having min(wartosc);
68. select toawr_id, wartosc from zamowienia order by wartosc;
69. select toawr_id from zamowienia where  wartosc = (select min(wartosc) from zamowienia);
70. # 2
71. select nazwa, id from towary where id = (select toawr_id from zamowienia having min(wartosc));
72. # 3
73. 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;
74. select max(wartosc) from zamowienia group by toawr_id;
75. # 4
76. 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;
77.  
78.  
79. #1
80. use biuro;
81. select count(*) from wlasciciel where adres like '%Bialystok%';
82. #2
83. select count(*) from biuro where miasto="Lomza";
84. #3
85. select * from biuro;
86. select * from personel;
87. select biuroNr,count(biuroNr) as xd from personel where biuroNr in (select biuroNr from personel  group by biuroNr having count(*)>=3) group by biuroNr;
88. #4
89. select * from nieruchomosc;
90. select * from wynajecie;
91. select nieruchomoscNr, count(nieruchomoscNr) from wynajecie where nieruchomoscNr in (select nieruchomoscNr from wynajecie group by nieruchomoscNr having count(*)>2) group by nieruchomoscNr;
92. #5
93. select * from nieruchomosc;
94. select count(*) from nieruchomosc where personelNr in (select personelNr from nieruchomosc group by personelNr having count(*)>3) group by personelNr;
95. #6
96. select * from wynajecie;
97. select klientnr,count(*) from wynajecie where klientnr in (select klientnr from wynajecie group by klientnr having count(*)>=2) group by klientnr;
98. #7
99. select * from personel;
100. select biuroNr,avg(pensja) from personel group by biuroNr;
101. #8
102. select * from personel;
103. select * from personel where year(dataUr) in (select year(dataUr) from personel where ((year(current_date)-year(dataUr))>=65));
104. #9
105. select * from wynajecie;
106. 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;
107. #10
108. select * from wynajecie;
109. 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;