Notes

--Stored Procedure[insert,update,delete]
create or alter procedure insert_demo(
@stud_name Varchar(50),
)
AS
BEGIN
IF EXISTS (SELECT 1 FROM student WHERE stud_id=@stud_id)
BEGIN
UPDATE student set stud_name=@stud_name WHERE stud_id=@stud_id
END
ELSE
BEGIN
INSERT into student Values (@stud_name)
END
END

--multi record insert[Table Variable]
CREATE or alter PROCEDURE demo_test
@temp [dbo].[test_table_var] Readonly
AS
BEGIN
IF exists(select 1 from @temp as tp left join [dbo].[employee] as emp on emp.e_id=tp.e_id where emp.e_id is null)
Begin
insert into [dbo].[employee] select tp.e_id,tp.e_name,tp.e_salary from @temp as tp left join [dbo].[employee]
as emp on emp.e_id=tp.e_id where emp.e_id is null
end
IF exists(select 1 from @temp as tp left join [dbo].[employee] as emp on emp.e_id=tp.e_id where emp.e_id is not null)
Begin
update emp set emp.e_id=tp.e_id,emp.e_name=tp.e_name,emp.e_salary=tp.e_salary from @temp as tp left join [dbo].[employee]
as emp on emp.e_id=tp.e_id where emp.e_id is not null
end
END
GO

CREATE TYPE [test_table_var] AS TABLE
(
e_id int not null,
e_name nvarchar(max)
)
GO

declare @var [dbo].[test_table_var]
insert into @var values(1,'demo')
select *from @var

--Scalar Function[using merge]
CREATE OR ALTER FUNCTION [dbo].[salary_increment]
(
@Increment int,
@Salary numeric(18,2)
)
RETURNS numeric(18,2)
AS
BEGIN
RETURN @salary+(@salary*@Increment/100);
END

declare @employee_detail [dbo].[emp_detail] --tablevariable
insert into @employee_detail values(9,'ri',20000,1);
insert into @employee_detail values(10,'test',45000,1);
select * from @employee_detail;

MERGE into [dbo].[tbl_employee] as desa
USING(
select * from @employee_detail
) as sou
on sou.ID=desa.ID
WHEN MATCHED THEN
update set desa.EMP_NAME=sou.EMP_NAME,
desa.SALARY= [dbo].[salary_increment](20,sou.SALARY)
WHEN NOT MATCHED THEN
insert(EMP_NAME,SALARY,DEPARTMENT_ID) values (sou.EMP_NAME,[dbo].[salary_increment](20,sou.SALARY),sou.DEPARTMENT_ID);

--Trigger
create or alter TRIGGER my_triggu
ON student
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
IF Exists(select 0 from inserted)
begin
If exists(select 0 from deleted)
begin
INSERT INTO [dbo].[tbl_logs]([remarks],[date_of_op],[stud_name])
select 'updated',getdate(),[stud_name] from inserted;
end else
begin
INSERT INTO [dbo].[tbl_logs]([remarks],[date_of_op],[stud_name])
select 'inserted',getdate(),[stud_name] from inserted;
end
end else
begin
INSERT INTO [dbo].[tbl_logs]([remarks],[date_of_op],[stud_name])
select 'deleted',getdate(),[stud_name] from deleted;
end
END
GO

--query
--1
select [day_of_operation] from [dbo].[flightmast] where [day_of_operation] like
concat('%',(select Datename(dw,@travel_date)),'%') and flight_id=@flight_id
--2
select [Routing],count([flight_id]) from [dbo].[flightmast] group by [Routing];
select * from (select [flight_id],[Routing] from [dbo].[flightmast]) as f
pivot(
count([flight_id]) for routing in ([Via ATQ],[Via PAT],[Via AMD])
)as pivot_db
--3
--where f.[effective_from] between DATEADD(m,-6,GETDATE()) and GETDATE() order by f.[flight_id];
--4
SELECT stud_name,
max(case when s_id = 1 and stud_marks<50 then stud_marks end) 'dbms' ,
from student group by stud_name;