use Training_2022
go

---CREATE TABLE
create table rajeshkumar.sales(
id int primary key,
item varchar(30),
target_reached bit default 0 not null,
sales_amt decimal(10,2),
entrydate datetime default getdate(),
added_sales decimal(10,2));

drop table rajeshkumar.sales;

--Alter
alter table rajeshkumar.sales add payment_amt decimal(10,2);

alter table rajeshkumar.sales add total_amt money;

alter table rajeshkumar.sales drop column payment_amt;

alter table rajeshkumar.sales alter column payment_amt int;

alter table rajeshkumar.sales alter column payment_amt int not null;

alter table rajeshkumar.sales add constraint pk primary key(id);

alter table rajeshkumar.sales add constraint pk primary key(id);

alter table rajeshkumar.sales drop constraint PK__sales__3213E83F75240305;

select * from rajeshkumar.sales;

insert into rajeshkumar.sales values(10001,'bottle',1,15000,'2016-05-21',751);
insert into rajeshkumar.sales values(10002,'box',0,45000,'2017-05-21',5654);
insert into rajeshkumar.sales values(10003,'pencil',0,35700,'2018-05-21',559);
insert into rajeshkumar.sales values(10004,'pillow',1,48500,'2019-05-21',785);
insert into rajeshkumar.sales values(10005,'bike',1,74542,'2015-05-21',7783,5448.44);
insert into rajeshkumar.sales values(10006,'car',0,48500,'2019-05-21',7812,45484);
insert into rajeshkumar.sales values(10007,'football',0,78350,'2019-05-21',645,48424);
insert into rajeshkumar.sales values(10008,'basketball',1,4465640,'2019-05-21',45,48418);
insert into rajeshkumar.sales values(10009,'bicycle',0,186800,'2019-05-21',7455,98746);


truncate table rajeshkumar.sales;

delete from rajeshkumar.sales where id = 10001;

select entrydate+1,* from rajeshkumar.sales;
update rajeshkumar.sales set total_amt = sales_amt+added_sales;


--FUNCTION
CREATE FUNCTION rajeshkumar.udfgetdate
()
RETURNS DATE
AS
BEGIN
	DECLARE @orderdate as DATE;
	SELECT @orderdate = '2019/45/02';
	RETURN @orderdate;

END

ALTER FUNCTION rajeshkumar.udfgetdate(@dayadd int)
RETURNS DATE
AS
BEGIN
	DECLARE @orderdate as DATE;
	SELECT @orderdate = getdate()+@dayadd;
	RETURN @orderdate;

END

SELECT 
rajeshkumar.udfgetdate(5)

CREATE FUNCTION rajeshkumar.udfaddtwo(@int1 as int, @int2 as int)
returns int
as 
begin
	return(@int1 + @int2)
end

SELECT rajeshkumar.udfaddtwo(200,21)


---PROCEDURE
create procedure rajeshkumar.udsp
@id int
AS 

BEGIN
IF @id>0
	BEGIN 
	SELECT * FROM rajeshkumar.sales WHERE id =@id
	END

ELSE 
	BEGIN
	SELECT * FROM rajeshkumar.sales 
	END
END


EXECUTE rajeshkumar.udsp 10003


---TCL
GRANT SELECT, INSERT, UPDATE, DELETE ON rajeshkumar.sales to public
revoke select on rajeshkumar.sales to public

Exec sp_rename 'rajeshkumar.sales', 'salestwo'

select * from rajeshkumar.salestwo

Exec sp_rename 'rajeshkumar.salestwo', 'sales'

EXEC sp_rename 'rajeshkumar.sales.added_sales', 'bonus','column'

--Table Valued Function

CREATE FUNCTION rajeshkumar.udftarget(@target bit)
RETURNS TABLE
AS
RETURN 
	SELECT * FROM rajeshkumar.sales
	WHERE target_reached =@target


SELECT * FROM rajeshkumar.udftarget(1)


----VIEW
CREATE VIEW rajeshkumar.udvsales
AS
SELECT * FROM rajeshkumar.sales

select * from rajeshkumar.udvsales


select top 1 entrydate,sum(sales_amt) as [Total_Sales_amt] 
from rajeshkumar.sales
group by entrydate
having sum(sales_amt)>47000
order by Total_Sales_amt desc



select * from rajeshkumar.sales 
where sales_amt like '4%'


select * from rajeshkumar.sales 
where sales_amt between 15500 and 70000

select * from rajeshkumar.sales 
where entrydate between '2015-01-01' and '2021-12-01'

CREATE table rajeshkumar.salesthree(
id int primary key,
itemname varchar(300),
ownername varchar(200),
foreign key(id) references rajeshkumar.sales(id))


insert into rajeshkumar.salesthree values(10002,LTRIM('    box    '),RTRIM('      RAM        '))
insert into rajeshkumar.salesthree values(10003,RTRIM((LTRIM('    pencil    '))),LTRIM(RTRIM('      shri        ')))


SELECT * FROM rajeshkumar.salesthree

SELECT* FROM rajeshkumar.salesthree where ltrim(ownername) = 'RAM'

SELECT id,itemname,ltrim(ownername) as [ownername] FROM rajeshkumar.salesthree where ltrim(ownername) = 'RAM'
SELECT* FROM rajeshkumar.salesthree where ownername = 'RAM'