Quick Discussion Question in SQL SERVER

 select CHARINDEX('@','tarunraj119@gmail.com')

SELECT DATALENGTH('tarunraj119@gmail.com') -CHARINDEX('@','tarunraj119@gmail.com');

select STUFF('tarunraj119@gmail.com',13,9,'capgemini.com') 

select stuff('tarunraj119@gmail.com',charindex('@','tarunraj119@gmail.com')+1,len('tarunraj119@gmail.com')-charindex('@','tarunraj119@gmail.com'),

'capgemini.com') ;

select CURRENT_TIMESTAMP;

select date(GETDATE())

select DATEADD(DAY,5,getdate()); -- 2021-12-22 10:17:33.413

select DATEADD(Month,1,Getdate()); -- 2022-01-17 10:17:14.213

select DATEDIFF(day,'2021-01-01',getdate()) --  350

select DATEDIFF(MONTH,'2021-01-01',getdate()) -- 11 Month

select datediff(hour,'2021-12-16 08:30:00',getdate()) --26 HOUR

select DATENAME(day,'2021-12-17') -- 17 Day

select datename(MONTH,getdate()) --December

SELECT DATENAME(hour, '2014/04/28 09:49'); --Result: '9'

SELECT DATENAME(minute, '2014/04/28 09:49'); --Result: '49'

SELECT DATENAME(second, '2014/04/28 09:49:12'); --Result: '12'

SELECT DATENAME(millisecond, '2014/04/28 09:49:12.726'); --Result: '726'

SELECT GETUTCDATE(); -- 2021-12-17 04:55:13.480

SELECT GETDATE();

SELECT TRY_CONVERT(varchar, '2018-09-13', 101); --Result: '09/13/2018'

SELECT TRY_CONVERT(varchar, '2018-09-13', 103) --2018-09-13

SELECT @@VERSION;   --Microsoft SQL Server 2014 - 12.0.2000.8 (X64) 

SELECT ISDATE('2014-05-01'); -- Result: 1

SELECT ISDATE('techonthenet.com'); --Result: 0

SELECT ISDATE(123);  --Result: 0

SELECT ISNULL(NULL, 'No Data'); -- Result: 'No Data'

SELECT ISNUMERIC(1234); -- Result: 1

SELECT ISNUMERIC('No Data'); -- Result: 0

SELECT NULLIF('2014-05-01', '2014-04-30'); -- Result: '2014-05-01'         (returns first value because values are different)

SELECT NULLIF('2014-05-01', '2014-05-01'); -- Result: NULL                 (returns NULL because values are the same)

SELECT SESSION_USER; -- Result: 'dbo'

SELECT USER_NAME(12); --Result: 'jsmith'

SELECT USER_NAME();  --dbo

select * from Worker

alter table Worker  alter column salary numeric(10,2)

alter table worker alter column salary integer not null

sp_rename 'Worker.Worker_id' , 'EMP_ID' , 'COLUMN';  -- Chnage Column Name

sp_rename 'Worker.EMP_ID' , 'WORKER_ID' , 'COLUMN';

sp_rename 'Worker','Worker_1'  -- Rename Table

sp_rename 'Worker_1' ,'Worker'  

Declare Variables

DECLARE @techonthenet VARCHAR(50), @site_value INT;

SET @site_value = 10;

DECLARE @techonthenet VARCHAR(50) = 'Example showing how to declare variable';

drop table test_employee;

drop table Department;

create table Department(ID int primary key ,Department varchar(10)) ;

select * from Department

create table test_employee 

(

EMP_ID int IDENTITY  primary key ,

F_NAME VARCHAR(10) NOT NULL,

L_NAME VARCHAR(10) NOT NULL,

DOB DATE DEFAULT '1900-01-01',

AGE INT CHECK(AGE >0 ),

EMAIL_ID int UNIQUE,

DEPT_NAME int,

--constraint UK_EMAIL unique (EMAIL_ID),

constraint FK_DEPT_IT FOREIGN KEY (DEPT_NAME) references Department (ID),

constraint UK_NAME_FL UNIQUE (F_NAME,L_NAME) 

)

create table test_employee

(

ID int identity  primary key ,

F_Name varchar(20),

L_Name varchar(20),

DOB   date check (DOB > '1900-01-01 00:00:00'),

DOJ   date   ,

Age int check (AGE >0 ),

DEPT_ID  int ,

constraint UK_FL_NAME UNIQUE(F_Name,L_Name) ,

constraint FK_DPT_ID FOREIGN KEY (DEPT_ID) references Department(ID)

)

select * from test_employee