https://www.perplexity.ai/search/7b10bf92-6ee6-47b4-8e1f-b39ace6ee873?s=u
https://www.phind.com/search?cache=b34651e1-f4e9-40ce-81da-411bd476d71f
SELECT student_code, AVG(lesson_rate) AS avg_lesson_rate , SUM(lesson_rate) AS sum_lesson_rate | |
FROM Unit | |
GROUP BY student_code | |
SELECT StudentId, AVG(NomreDars) as miangin,SUM(NomreDars) as jameNomre | |
FROM EntekhabVahed | |
GROUP BY StudentId | |
-- trigger | |
CREATE TRIGGER update_total_vaheds | |
ON EntekhabVahed | |
AFTER UPDATE,INSERT | |
AS | |
BEGIN | |
DECLARE @StudentId INT; | |
DECLARE @tedadKolVahedHa INT; | |
SELECT @StudentId= StudentId FROM inserted | |
SELECT @tedadKolVahedHa= SUM(dbo.getTedadVahed(DarsId)) | |
FROM EntekhabVahed | |
WHERE StudentId = @StudentId | |
GROUP BY StudentId | |
UPDATE Students | |
SET [tedadKolVahed] = @tedadKolVahedHa | |
WHERE StudentId = @StudentId | |
END | |
-- create function | |
create FUNCTION getTedadVahed2 (@darsId INT) | |
RETURNS INT | |
AS | |
BEGIN | |
DECLARE @tedadVehd INT; | |
SELECT @tedadVehd = TedadVahed | |
FROM [dbo].[Dars] | |
WHERE DarsId = @darsId | |
RETURN @tedadVehd | |
END |
create function tarakomMaadares(@nahie nvarchar) returns numeric | |
as | |
begin | |
declare @tarakom numeric | |
SELECT @tarakom = sum([totalStudents]/ [totlaClasses]) | |
FROM madarestext231 | |
WHERE [nahie] = @nahie | |
return @tarakom | |
end | |
create function tarakomMaadares5(@nahie nvarchar) returns numeric | |
as | |
begin | |
declare @tarakom numeric | |
SELECT @tarakom = sum([totalStudents]/ [totlaClasses]) | |
FROM madarestext231 | |
-- WHERE [codeMadrese] = @codeMadrese | |
WHERE not [totlaClasses] =0 and [nahie] = @nahie | |
return @tarakom | |
end | |
create function tarakomMaadares2(@codeMadrese numeric) returns numeric | |
as | |
begin | |
declare @tarakom numeric | |
SELECT @tarakom = [totalStudents]/ [totlaClasses] | |
FROM madarestext231 | |
-- WHERE [codeMadrese] = @codeMadrese | |
return @tarakom | |
end |
https://chrome.google.com/webstore/detail/majdfhpaihoncoakbjgbdhglocklcgno |
-- sql server query calculate student age with birthday | |
SELECT [StudentId] , [FirstName], DATEDIFF(YEAR,[Birthday],GETDATE())as age | |
FROM [dbo].[Students] |
CREATE FUNCTION CalculateLoanTime(@date_in DATE, @date_out DATE) | |
RETURNS NVARCHAR(50) | |
AS | |
BEGIN | |
DECLARE @loan_time NVARCHAR(50) | |
SET @loan_time = DATEDIFF(day, @date_in, @date_out) | |
RETURN @loan_time | |
END |
CREATE TRIGGER update_book_status_after_update | |
ON Amanat | |
AFTER UPDATE | |
AS | |
BEGIN | |
UPDATE book | |
SET book_status = 'in' | |
FROM book b | |
INNER JOIN inserted i ON b.book_id = i.book_id | |
WHERE i.status = 'returned' | |
END |
use emtehan | |
go | |
CREATE TRIGGER update_book_status ON [dbo].[Amanat] | |
AFTER INSERT | |
AS | |
BEGIN | |
DECLARE @book_id INT; | |
SELECT @book_id = book_id FROM inserted; | |
UPDATE [dbo].[Book] | |
SET book_status = 'out' | |
WHERE Book.book_id = @book_id; | |
END; | |
go |
SELECT * , dbo.CalculateLoanTime(date_in, date_out) AS 'زمان امانت گرفته شده' | |
FROM Amanat |