Notes

--//Log-trigger
ALTER TRIGGER [dbo].[test_trigger] ON [dbo].[employee_details] AFTER INSERT,DELETE,UPDATE
AS BEGIN
Declare @op varchar(3);
IF EXISTS (SELECT 1 FROM inserted)
BEGIN
IF EXISTS (SELECT 1 FROM deleted)
BEGIN
SET @op = 'U'
END
ELSE
BEGIN
SET @op = 'I'
END
END
ELSE
BEGIN
SET @op = 'D'
END
begin
IF Exists(select 1 from inserted)
begin
INSERT INTO [dbo].[employee_data_log]([e_id],[remark]) select [e_id],@op from inserted;
end
else
begin
INSERT INTO [dbo].[employee_data_log] ([e_id],[remark]) select [e_id],@op from deleted;
end
end
END
--//Stored Procedure
ALTER PROCEDURE [dbo].[test3]
(
@empid int,
@delete char(1)
)
AS
BEGIN
if exists (select 1 from [dbo].[employee_info] where empid=@empid)
if(@delete='D')
begin
delete from employee_info where empid=@empid;
end
else
begin
--update employee_info set // where empid=@empid;
end
else
begin
-- insert into employee_info(empfname,emplname,Department,project,e_address,dob,gender)values(@empfname,@emplname,@Department,@project,@e_address,@dob,@gender);
end

END

--//Table Function(Multi record)
CREATE or alter PROCEDURE demo_test
@temp [dbo].[test_table_var] Readonly --table variable
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

--declare @var [dbo].[test_table_var]
--insert into @var values(1,'dmeo',50)
--exec demo_test @var
--select * from @var
--select * from [dbo].[employee]

--//Salared Function
--ALTER FUNCTION [dbo].[salary_increment]
--RETURN @salary+(@salary*@Increment/100);
-------------------------
declare @employee_detail [dbo].[emp_detail]
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);


merge targetProducts as target
using sourceProducts as source
on source.productid=target.Productid
when not matched by target then
Insert (Productid,Productname,Price)
values(source.productid,source.Productname,source.price)
when matched then update set
target.productname=source.productname,
target.price=source.price
when not matched by source then
delete;

--//Query
--Rank
select p.productname,(od.unitprice*od.Quantity) as 'Price',rank() over(order by od.unitprice*od.Quantity) as 'Rank' from [dbo].[product] as p
inner join [dbo].[orderitem] as od on p.productid=od.productid

--Pivot
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

SELECT
S.SaleID,
S.SoldBy,
S.SalePrice,
S.Margin,
S.Date,
I.Books,
I.Printing,
I.DVD
FROM
dbo.Sale S
INNER JOIN (
SELECT *
FROM
(SELECT SaleID, SalePrice, Category FROM dbo.SaleItem) I
PIVOT (Max(SalePrice) FOR Category IN (Books, Printing, DVD)) P
) I ON S.SaleID = I.SaleID
;

--Date
select f.[flight_id],f.[origin],a.[airport_id],a.[airport_name]
from [flightmast] as f
inner join [dbo].[airportmast] as a
on a.[city_id]=f.[destination]
where f.[effective_from] between DATEADD(m,-2,GETDATE()) and GETDATE() order by f.[flight_id];

--Date name[monday,tuesday]
select [day_of_operation] from [dbo].[flightmast] where [day_of_operation] like
concat('%',(select Datename(dw,@travel_date)),'%') and flight_id=@flight_id

--DateDiff
select f.[flight_id],c.[city_name]
from [flightmast] as f
inner join [dbo].[citymast] as c
on c.[city_id]=f.[destination]
where DATEDIFF(HOUR,f.[depart_time],f.[arrival_time])>=5 and DATEDIFF(HOUR,f.[depart_time],f.[arrival_time])<=6 and c.[city_name]='mumbai';

--calender(recursion)
Declare @dt date;
set @dt = '2022-01-01';
WITH my_calendar(c_date) as
(
select @dt as c_date
UNION ALL select DATEADD(d,1,c_date) from my_calendar where DATEPART(YY,c_date)<2023 and DATEPART(m,c_date)<13
)
select DATENAME(MM,my_calendar.c_date) as MONTH,my_calendar.c_date as Calender,DATENAME(W,my_calendar.c_date) as DAY from my_calendar where DATEPART(YY,c_date)<2023 option (maxrecursion 0);


--grant and revoke permission

--java
jar -jar <file>.jar
jar cf <file>.jar file-list
<mvn clean>

scp training@<ip>:/home/train/qwerty .

tar -cvf demo.tar.gz foldername
tar -xvf demo.tar.gz

--Nginx
location ^~ /qwe/{
rewrite ^/qwe/(.*)$ /$1 break;
proxy_pass http://<ip>:port;
}
location ^~ /qwe/{
alias /home/ss/ui;
try_files $uri /index.html index.php;
}
grant insert,
select on accounts to Ram
----
rm -rf /home/srkay/hcis_ui || true

mkdir -p /home/srkay/hcis_ui

pwd






$BUILD_NUMBER.tar.gz

 

Remote directory?

 



hcis_ui


cd /home/srkay/hcis_ui

 

tar -xzf $BUILD_NUMBER.tar.gz

sleep 10;

rm -rf *.tar.gz