gzfmuDkN

1#------ Laba 12 ------#
2
3select avg(call_cost) as 'Средняя стоимость звонка' from CallA;
4select min(call_cost) as 'Минимальная стоимость звонка' from CallA;
5select max(call_cost) as 'Максимальная стоимость звонка' from CallA;
6select sum(sum_of_payment) as 'Сумма платежей' from Payment;
7
8select City.region as 'Название города', count(CallA.id_city) as 'Количестов звонков' from CallA
9    inner join City on CallA.id_city = City.id_city group by CallA.id_city;
10
11select phone_number, sum(sum_of_payment) as 'Сумма платежей' from Abonent inner join Payment on Abonent.id = Payment.id
12    where Abonent.id = 6 and Payment.id = 6;
13
14select Abonent.id, name, surname, phone_number, passport_number, sum(sum_of_payment) as 'Сумма платежей' from Abonent
15    inner join Payment on Abonent.id = Payment.id group by Abonent.id ;
16
17select count(Abonent.id) as 'Количество пользователей тарифа', Tariff.name_of_tariff as 'Название тарифа' from Accounting_tariff_changes
18    inner join Tariff on Accounting_tariff_changes.id_tariff = Tariff.id_tariff
19    inner join Abonent on Accounting_tariff_changes.id = Abonent.id group by Tariff.name_of_tariff;
20
21
22select left(name, 2), passport_number, phone_number from Abonent;
23select right(surname, 2), passport_number, phone_number from Abonent;
24select length(name) as 'name' from Abonent;
25select lower(name) from Abonent;
26select upper(name) from Abonent;
27select count(*) from Abonent;
28select sin(call_cost) from CallA;
29select cos(call_cost) from CallA;
30select atan(call_cost) from CallA;
31select sqrt(call_cost) from CallA;
32select reverse(call_cost) from CallA;
33
34
35#------ Laba 13------#
36
37select payment_number, sum_of_payment from Payment where id = (select id from Abonent where phone_number = '375297764433');
38update Accounting_tariff_changes set date_of_changes = NOW() where id = (select id from Abonent where phone_number = '375297764433');
39insert into Payment (payment_number, date_of_payment, sum_of_payment, id) VALUES (NULL, NOW(), 92, (select id from Abonent where phone_number = '375297643872'));
40delete from CallA where id_city = (select id_city from City where region = 'Minsk');
41select name, phone_number, passport_number, (select count(number_call) from CallA where Abonent.id = CallA.id) as 'Количество звонков'
42    from Abonent where 1 < (select count(number_call) from CallA where Abonent.id = CallA.id);
43
44create table if not exists AbonentClone
45(
46	id int,
47	name varchar(15) not null,
48	surname varchar(15) not null,
49	last_name varchar(15) not null,
50	phone_number varchar(12) null unique,
51	balance float null,
52	date_of_write_off datetime,
53	passport_number varchar(10) null unique
54);
55
56
57insert into AbonentClone select * from Abonent where id in (select id from Abonent);
58
59select * from AbonentClone where date_of_write_off > any (select date_of_write_off from Abonent
60    where Abonent.date_of_write_off = '2018-07-08 09:31:40' or Abonent.date_of_write_off = '2021-05-05 13:16:11');
61
62select * from AbonentClone where date_of_write_off > all (select date_of_write_off from Abonent
63    where Abonent.date_of_write_off = '2018-07-08 09:31:40' or Abonent.date_of_write_off = '2021-05-05 13:16:11');
64
65
66
67select time_start, time_stop, call_cost, date_call, (select region from City where CallA.id_city = City.id_city) as 'Название города',
68     (select name from Abonent where Abonent.id = CallA.id) as 'имя' from CallA;