BD

BD


select count(*) from users;

select count(1) from users;

select count(u_phone) from users;



/*5.   Создать процедуру для отображения наименования

товара и общей суммы

его продаж за указанный период (параметр).*/

drop procedure product_plus_sum;

delimiter //

create procedure product_plus_sum (date_begin datetime(6), date_end datetime(6) )

begin

select t.name_t, sum(s.kol)

from tovar t join s_zak s using (id_t) join zak using(id_z)

where date(zak.date_p) between date_begin and date_end group by 1;

end; //

call zakaz_.product_plus_sum('2019-01-01', '2020-01-01');


/*6.   Создать процедуру для поиска покупок

 определенного заказчика (параметр)

 с отображением их общей суммы.*/

 drop procedure search_;

 delimiter //

 create procedure search_ (name_c char(30))

 begin

 select t.name_t, sum(t.price_y*s.kol)

 from tovar t join s_zak s using (id_t) join zak using(id_z) join zakaz z using (id_p)

 where z.name_p= name_c group by 1 with rollup;

 end; //

 call zakaz_.search_('Петрова');



/* БАНК

6.   Отобразить названия филиалов, которые оформили количество договоров в текущем месяце больше среднего.

 7. Отобразить названия услуг количество договоров больше 2.

8.   Увеличить процентную ставку в 2 раза для услуг, количество договоров по которым больше 2.

 9. Отобразить филиал, услугу, месяц и количество договоров. */

 #7

 select u.name_u from usluga u join dogovor d using(id_u)

 group by u.name_u having count(d.id_d)>2;

 #8

 update usluga u set u.procent=u.procent*2

where 2<(select count(d.id_d) from dogovor d where u.id_u=d.id_u);

 #9

 select f.name_f, u.name_u, month(date_), count(d.id_d)

 from usluga u join dogovor d using(id_u) join filial f using(id_f)

 group by f.name_f;

 

 select f.name_f, u.name_u, month(date_), count(d.id_d)

 from usluga u join dogovor d using(id_u) join filial f using(id_f)

 group by month(date_);

 

 select f.name_f, u.name_u, month(date_), count(d.id_d)

 from usluga u join dogovor d using(id_u) join filial f using(id_f)

 group by u.name_u;

 

 select f.name_f, u.name_u, month(date_), count(d.id_d)

 from usluga u join dogovor d using(id_u) join filial f using(id_f)

 group by 1,2,3 order by 1,2,3;

 /*3.   Создать виртуальную таблицу для отображения фамилии клиентов,

и номер договоров с кредитами, которые не погашены на

текущее время по всем филиалам.*/

create view klientu as select k.fio, d.id_d

from klient k join dogovor d using(id_k) join usluga u

 using(id_u) where u.category='Кредитование' and

 curdate()<d.data_pr;

 /* 4.   Создать процедуру для клиентов, которые

 оформляли договора

по указанной услуге (параметр) в определенном

филиале (параметр) за указанный месяц (параметр).*/

delimiter //

create procedure proc1( usluga_ char(40), filial_ char(10),

month_ int(2))

 begin

 select k.fio from klient k join dogovor d using(id_k)

 join usluga u using(id_u)

 where u.name_u=usluga_ and f.name_f=filial_ and

 month(d.data_)=month_;

 end ;//

 call proc1('Карта Универсальная','23',6);

 /*

 5.   Создать функцию для нахождения среднего количества

 оформленных

договоров за текущий месяц.*/

 delimiter //

 create function func1 ()

 returns decimal(8.0) deterministic

 begin

 return (select avg(d.id_d) from dogovor d

 where month(d.date_)=month(curdate()));

 end; //

 select func1();

 /* Создать функцию для нахождения среднего количества

 оформленных договоров за один месяц.*/

 select avg(cout)

 from (select month(d.date_) as mont, count(d.id_d) as cout

 from dogovor d group by 1) as tab1 ;

 

 delimiter //

 create function func2()

 returns decimal(8.0) deterministic

 begin

 return (select avg(cout) from avg_count);

 end; //

 select func2();

 /* отобразить названия месяцев

 за которые оформили контрактов больше среднего*/

set local LC_TIME_NAMES='uk_Ua';

 select monthname(d.date_) from dogovor d

 group by 1 having count(d.id_d)>func2();

  update usluga set procent=procent*2

 where 2<(select count(dogovor.id_d) from dogovor where usluga.id_u=dogovor.id_u);

 /* ЗАКАЗЫ

 3.   Создать функцию для нахождения максимального количества заказов одного товара за прошедший месяц.*/

 delimiter //

 create function func1()

 returns int(11) deterministic

 begin

 return (select sum(s.kol) from s_zak s join zak z using(id_z)

 where year(z.date_p)=year(now())

 and month(z.date_p)=month(now())-1

 group by s.id_t

 order by 1 desc limit 1); end; //

 /*4.   Создать виртуальную таблицу для отображения наименований товаров, которые

пользовались максимальным спросом в прошедшем месяце.*/

create view tovar_max

as select t.name_t from tovar t join s_zak s using(id_t)

join zak z using(id_z) where month(z.date_p)= month(now())-1

group by 1 having sum(s.kol)=(select sum(kol) from s_zak join zak

 using(id_z)

 where month(date_p)= month(now())-1 group by id_t order by 1 desc

 limit 1);

 

select * from tovar_max;


select t.name_t from tovar t join s_zak s using(id_t)

join zak z using(id_z) where month(z.date_p)= month(now())-1

group by 1 having sum(s.kol)>=ALL(select sum(kol) from s_zak join zak using(id_z)

 where month(date_p)= month(now())-1 group by id_t );

 

/*отобразить наименований товаров с продажами выше среднего*/






select t_sum.tov

from (select t.name_t as tov, sum(kol) as summa from tovar t join s_zak s using(id_t) group by 1)

 as t_sum where t_sum.summa>=(select avg(t_s.suma)

 from (select sum(kol) as suma from s_zak group by id_t) as t_s );

 

 delimiter //

 create function avg_t() returns decimal(6,1) deterministic

 begin

 return(select avg(t_s.suma)

 from (select sum(kol) as suma from s_zak group by id_t) as t_s);

 end; //

 

 

 select t_sum.tov,t_sum.summa

from (select t.name_t as tov, sum(kol) as summa from tovar t join s_zak s using(id_t) group by 1)

 as t_sum where t_sum.summa>=avg_t();

 

select t.name_t,sum(s.kol) from tovar t join s_zak s using(id_t)

group by 1 having sum(s.kol)>avg_t();

 

 delimiter //

 create function avg_t_m(month_ int ) returns decimal(6,1) deterministic

 begin

 set month_=ifnull(month_,month(curdate()));

 return(select avg(t_s.suma)

 from (select sum(kol) as suma from s_zak join zak using(id_z)

 where month(date_p)=month_ group by id_t) as t_s);

 end; //

 select avg_t_m(null);


/*5.   Создать процедуру для отображения наименования

товара и общей суммы

его продаж за указанный период (параметр).

6.   Создать процедуру для поиска покупок

 определенного заказчика (параметр)

 с отображением их общей суммы.*/

 /*АВТОСАЛОН

 1.Создать процедуру для отображения самой догой модели

 для каждой марки.*/

 select model, marca, price_a from avto

where price_a in(select max(price_a) from avto group by model);


/* 2.Создать процедуру для отображение информации о самой

 популярной марки для каждой модели .

*/

 select distinct a.model, (select av.marca from prodaga p join

 avto av

 using(id_a) where a.model=av.model group by av.marca

 order by count(id_p) desc limit 1)

 from avto a;

 /* 2.Создать процедуру для отображение количества продаж

 для определенной модели (параметр) по каждой марке.

*/

delimiter //

create procedure proc1( mod_ char(40))

begin

select a.marca, count(id_p)

from avto a join prodaga p using(id_a) where a.model=mod_

group by 1;

end; //

 

 call proc1('TOYOTA');

 

select a.marca, count(id_p)*a.price_a

from avto a join prodaga p using(id_a) group by 1;


 

 select count(id_p),id_a from prodaga group by id_a

 order by count(id_p) desc limit 1 ;

 

/* САЛОН КРАСОТЫ

5. Создать функцию для подсчета среднего количества выполненных

 заказов одним мастером за прошедший месяц.

6. Отобразить фамилии мастеров, которые выполнили

 заказов за прошедший месяц больше среднего.

*/

select master_u from vip_u group by master_u

having count(id_vip)>=func();


delimiter //

create function func()

returns int(6) deterministic

begin

return(select avg(tab1.c)

from (select count(id_vip) as c from vip_u group by master_u) as tab1);

end;//

select func();

/* отобразить фамилию клиента, название услуги и

фамилию мастера, который ее выполнял.

*/

select klient.fio, usluga.name_u, vip_u.master_u

from usluga join vip_u using(id_u) join vip using(id_v)

join klient using(id_k);



/*ПОСТАВКИ

#отобразить название продукта и номер единственного его поставщика*/

select name_t, id_p, count(id_p)

from zak join s_postavki using(id_ps) join tovar using(id_t) 

 group by name_t

having count(id_p)=1;



============================================================================================


Завдання №1. Створіть уявлення для відображення предметів 2 курсу та кількості студентів, що вивчають ці предмети.

Код запиту:

create view sub_2_course as select subject.subject_name, count(student.sname_) from subject natural join student natural join distribution natural join group_ where subject.id_sub=distribution.id_sub and group_.course=2;

-------------------------

Завдання №2. Порахуйте загальну кількість предметів 2 курсу (використовуючи віртуальну таблицю ).

Код запиту:

select count(sub_2_course.subject_name) as 'Количество предметов' from sub_2_course;

-------------------------

Завдання №3. Створити уявлення для відображення загальної кількості навчальних годин за кожний семестр.

Код запиту:

create view study_hours as select subject.semester, sum(subject.total_hours) from subject group by semester;

-------------------------

Завдання №4. Відобразіть номери семестрів з кількістю годин більше ніж 500 (використовуючи віртуальну таблицю ).

Код запиту:

select Семестр from study_hours where 'количесвто часов'>500;

-------------------------

Завдання №5. Створіть уявлення для відображення прізвища викладача та кількості груп, з якими він працює в поточному семестрі.

Код запиту:

create view teacher_groups as select teacher.full_name_t, count(distribution.id_gr) from teacher natural join distribution natural join subject where subject.semester in(1,3,5,7) group by 1;


=============================================


/*4.   Создать виртуальную таблицу для отображения

 наименований товаров, которые

пользовались максимальным спросом в прошедшем месяце.*/

drop view max_product;

create view max_product as select t.name_t as name_p ,sum(s.kol) as sum_product

from tovar t join s_zak s using (id_t) join zak using(id_z)

where month(zak.date_p)=month(curdate())-1 group by 1 order by 2 desc;

SELECT * FROM zakaz_.max_product;


/*5.   Создать процедуру для отображения наименования

товара и общей суммы его продаж за указанный период (параметр).*/

drop procedure product_plus_sum;

delimiter //

create procedure product_plus_sum (date_begin datetime(6), date_end datetime(6) )

begin

select t.name_t, sum(s.kol)

from tovar t join s_zak s using (id_t) join zak using(id_z)

where date(zak.date_p) between date_begin and date_end group by 1;

end; //

call zakaz_.product_plus_sum('2019-01-01', '2020-01-01');


/*6.   Создать процедуру для поиска покупок

 определенного заказчика (параметр)

 с отображением их общей суммы.*/

 drop procedure search_;

 delimiter //

 create procedure search_ (name_c char(30))

 begin

 select t.name_t, sum(t.price_y*s.kol)

 from tovar t join s_zak s using (id_t) join zak using(id_z) join zakaz z using (id_p)

 where z.name_p= name_c group by 1 with rollup;

 end; //

 call zakaz_.search_('Петрова');

Report Page