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_('Петрова');