SELECT students.IDStudent, students.Фамилия, students.ВУЗ, students.Курс, VidSporta.NameSport AS 'Вид спорта', treners.Фамилия AS 'Тренер', abon.[Количество оплаченных занятий]*VidSporta.Stoimost AS 'Сумма'
FROM VidSporta,treners,abon,students
WHERE abon.IdTrener=treners.IDTrener AND treners.IDSporta=VidSporta.idsport
AND abon.IDstudent=students.IDStudent AND
abon.[Количество оплаченных занятий]*VidSporta.Stoimost <
 (SELECT AVG(abon.[Количество оплаченных занятий]*VidSporta.Stoimost)
FROM VidSporta,treners,abon,students
WHERE abon.IdTrener=treners.IDTrener AND treners.IDSporta=VidSporta.idsport
AND MONTH(abon.DATA)=10 )

 CREATE TABLE abon(
nomer [INT] NOT NULL,
 [Количество оплаченных занятий] [tinyint] NOT NULL,
DATA [DATE] NOT NULL,
IDstudent [INT] NOT NULL REFERENCES students (IDStudent),
IdTrener [tinyint] NOT NULL REFERENCES treners (IDTrener),
CONSTRAINT [PK_abon] PRIMARY KEY (nomer)
 )

CREATE TRIGGER journaling
ON dbo.journal
FOR INSERT
AS
BEGIN
DECLARE @c INT
SELECT @c=i.worker
FROM inserted AS i
UPDATE workers
SET workers.Expo = workers.Expo +1
FROM workers WHERE workers.ID=@c
DECLARE @a INT
DECLARE @id INT
DECLARE @b INT
DECLARE @e DECIMAL (8,2)
SELECT @id=i.ID, @b=i.measure, @e=i.resultat FROM inserted AS i
SELECT @a= (SELECT workers.IDStation FROM workers WHERE workers.ID=@c)
INSERT INTO resultat VALUES (@id,@a,@b,@e)
END