Создание и заполнение базы данных «Бакалея», страница 3

zapros = "SELECT min(sum) FROM  (select avg(bakaleya.price) as sum from bakaleya inner join tovary on bakaleya.id_tovar=tovary.id inner join type_bakaleya on tovary.type_bak=type_bakaleya.id group by type_bakaleya.id) a ;";

наименование вида с максимальной средней стоимостью:

zapros = "SELECT a.name FROM  (select type_bakaleya.name as name, avg(bakaleya.price) as sum from bakaleya inner join tovary on bakaleya.id_tovar=tovary.id inner join type_bakaleya on tovary.type_bak=type_bakaleya.id group by type_bakaleya.name) a where sum=(SELECT max(sum) FROM  (select avg(bakaleya.price) as sum from bakaleya inner join tovary on bakaleya.id_tovar=tovary.id inner join type_bakaleya on tovary.type_bak=type_bakaleya.id group by type_bakaleya.id) b );";

наименование вида с минимальной средне стоимостью:

zapros = "SELECT a.name FROM  (select type_bakaleya.name as name, avg(bakaleya.price) as sum from bakaleya inner join tovary on bakaleya.id_tovar=tovary.id inner join type_bakaleya on tovary.type_bak=type_bakaleya.id group by type_bakaleya.name) a where sum=(SELECT min(sum) FROM  (select avg(bakaleya.price) as sum from bakaleya inner join tovary on bakaleya.id_tovar=tovary.id inner join type_bakaleya on tovary.type_bak=type_bakaleya.id group by type_bakaleya.id) b );";

3. Найти изделие с ценой свыше указанной

Текстзапроса:

zapros = "SELECT bakaleya.id, tovary.name as tovar, proizvoditeli.name as firma, bakaleya.release_date as data, bakaleya.price, bakaleya.weight_volume as volume, bakaleya.count FROM bakaleya inner join tovary on tovary.id=bakaleya.id_tovar and bakaleya.price>" + price.Replace(',', '.') + " inner join proizvoditeli on proizvoditeli.id = bakaleya.id_proizvod;";

4. Найти долю изделия заданного производителя от общего числа изделий

Текстзапроса:

tmp1 = "SELECT count FROM bakaleya inner join proizvoditeli on bakaleya.id_proizvod=proizvoditeli.id and proizvoditeli.name='"+proizvod+"';";

tmp2 = "select sum(count) from bakaleya inner join proizvoditeli on bakaleya.id_proizvod=proizvoditeli.id";

tmp = String.Format("{0:0.00}", (Convert.ToDouble(tmp1) / Convert.ToDouble(tmp2)) * 100) + " %";

5. Найти все изделия с заданной датой выпуска.

Текстзапроса:

zapros = "SELECT bakaleya.id, tovary.name as tovar, proizvoditeli.name as firma, bakaleya.release_date as data, bakaleya.price, bakaleya.weight_volume as volume, bakaleya.count FROM bakaleya inner join tovary on tovary.id=bakaleya.id_tovar and bakaleya.release_date='"+date+"' inner join proizvoditeli on proizvoditeli.id = bakaleya.id_proizvod;";

6. Найти все изделия, чей вес находится в заданных пределах для заданного производителя и в целом.

Текстызапросов:

if (type == "")

                    zapros = "SELECT bakaleya.id, tovary.name as tovar, proizvoditeli.name as firma, bakaleya.release_date as data, bakaleya.price, bakaleya.weight_volume as volume, bakaleya.count FROM bakaleya inner join tovary on tovary.id=bakaleya.id_tovar and (bakaleya.weight_volume>" + min.Replace(',', '.') + " and bakaleya.weight_volume<" + max.Replace(',', '.') + ") inner join proizvoditeli on proizvoditeli.id = bakaleya.id_proizvod ;";

                else zapros = "SELECT bakaleya.id, tovary.name as tovar, proizvoditeli.name as firma, bakaleya.release_date as data, bakaleya.price, bakaleya.weight_volume as volume, bakaleya.count FROM bakaleya inner join tovary on tovary.id=bakaleya.id_tovar and (bakaleya.weight_volume>" + min.Replace(',', '.') + " and bakaleya.weight_volume<" + max.Replace(',', '.') + ") inner join proizvoditeli on proizvoditeli.id = bakaleya.id_proizvod and proizvoditeli.name='" + type + "';";

7. Найти долю изделий, чья стоимость находится в заданных пределах от общего количества изделий.

Текстзапроса:

tmp1 = "SELECT sum(count) FROM bakaleya inner join proizvoditeli on bakaleya.id_proizvod=proizvoditeli.id and (bakaleya.price>" + min.Replace(',', '.') + " and bakaleya.price<" + max.Replace(',', '.') + ")";

tmp2 = "select sum(count) from bakaleya inner join proizvoditeli on bakaleya.id_proizvod=proizvoditeli.id";

tmp = String.Format("{0:0.00}", (Convert.ToDouble(tmp1) / Convert.ToDouble(tmp2)) * 100) + " %";

8.  Найти долю изделий, поступивших от заданного поставщика от общего числа поставщиков

Текстзапроса:

tmp1  = "select count(*) from bakaleya inner join proizvoditeli on bakaleya.id_proizvod=proizvoditeli.id;";