Select id, name from A where name<>lower(name) collate SQL_Latin1_General_CP1_CS_AS

Select id, A.name, B.name from A inner join B on A.name=B.name where A.name<>B.name collate SQL_Latin1_General_CP1_CS_AS

SELECT  SUM(population),
        CASE country
                WHEN ''中国''     THEN ''亚洲''
                WHEN ''印度''     THEN ''亚洲''
                WHEN ''日本''     THEN ''亚洲''
                WHEN ''美国''     THEN ''北美洲''
                WHEN ''加拿大''  THEN ''北美洲''
                WHEN ''墨西哥''  THEN ''北美洲''
        ELSE ''其他'' END
FROM    Table_A
GROUP BY CASE country
                WHEN ''中国''     THEN ''亚洲''
                WHEN ''印度''     THEN ''亚洲''
                WHEN ''日本''     THEN ''亚洲''
                WHEN ''美国''     THEN ''北美洲''
                WHEN ''加拿大''  THEN ''北美洲''
                WHEN ''墨西哥''  THEN ''北美洲''
        ELSE ''其他'' END;

SELECT
        CASE WHEN salary <= 500 THEN ''1''
             WHEN salary > 500 AND salary <= 600  THEN ''2''
             WHEN salary > 600 AND salary <= 800  THEN ''3''
             WHEN salary > 800 AND salary <= 1000 THEN ''4''
        ELSE NULL END salary_class,
        COUNT(*)
FROM    Table_A
GROUP BY
        CASE WHEN salary <= 500 THEN ''1''
             WHEN salary > 500 AND salary <= 600  THEN ''2''
             WHEN salary > 600 AND salary <= 800  THEN ''3''
             WHEN salary > 800 AND salary <= 1000 THEN ''4''
        ELSE NULL END;

SELECT country,
       SUM( CASE WHEN sex = ''1'' THEN 
                      population ELSE 0 END),  --male population
       SUM( CASE WHEN sex = ''2'' THEN 
                      population ELSE 0 END)   --female population
FROM  Table_A
GROUP BY country;

select cast(1.73 as int) --return 1
select cast(1.73 as decimal(4,0)) --return 2

--keep 2 decimal, this will round to nearest 2 decimal
select cast(1,234 as decimal(10,2)) --return 1.23
select cast(1,236 as decimal(10,2)) --return 1.24

declare @zz1 decimal(5,4)=2.0  if @zz1=2  select @zz1 
--output: 2.0000 so decimal can compare to int

declare @zz1 decimal(5,4)=2   if @zz1=2.0  select @zz1
--output: 2.0000 so declare will correct the data type from int to decimal

declare @zz2 int=1.0 if @zz2=1.0  select @zz2
--output: 1 so declare will correct the data type from decimal to int
--and int can compare to decimal

declare @zz3 varchar(100)=''2.0'' if @zz3=2.0  select @zz3
--output: 2.0, correct way to declare a decimal varchar

declare @zz3 varchar(100)=''2.0'' if @zz3=2  select @zz3
--output: Conversion failed when converting the varchar value ''2.0'' to data type int.
--so if you declare a decimal varchar, it can not be compared with int

declare @zz3 varchar=2.0 --wrong declare, this will cause an error:
--output: Arithmetic overflow error converting numeric to data type varchar

declare @zz4 varchar=4 if @zz4=4.0  select @zz4
--output: 4, if assign a int to varchar, can declare without quote, this equal to declare with int keyword

declare @zz5 varchar(100)=''4'' if @zz5=4.0   select @zz5
--output: 4, this is equal to last one, declare with quote for integer

CREATE TABLE ConstraintDemo1
(
   ID INT NOT NULL,
   Name VARCHAR(50) NULL
)

CREATE TABLE ConstraintDemo2
(
   ID INT UNIQUE,
   Name VARCHAR(50) NULL
)

CREATE TABLE ConstraintDemo3
(
   ID INT PRIMARY KEY,
   Name VARCHAR(50) NULL
)

ALTER TABLE courses --main table
ADD CONSTRAINT fk_courses_teachers --foreign key name = fk_mainTable_referenceTable
FOREIGN KEY (class_id) --col in main table (foreign key)
REFERENCES classes (id); -- table(col) of source table for reference, primary key

CREATE TABLE Persons (
    ID int NOT NULL,
    Age int CHECK (Age>=18)
);

CREATE TABLE Persons (
    ID int NOT NULL,
    Age int,
    City varchar(255),
    CONSTRAINT CONS_Name CHECK (Age>=18 AND City=''Sandnes'')
);

CREATE TABLE Orders (
    ID int NOT NULL,
    OrderNumber int NOT NULL,
    OrderDate date DEFAULT GETDATE() 
);

DECLARE @startnum INT=1000 --start
DECLARE @endnum INT=1020 --end 
;
WITH gen AS (
    SELECT @startnum AS num
    UNION ALL
    --change number+ i to adjust gap i
    SELECT num+3 FROM gen WHERE num+1<=@endnum 
)
SELECT * FROM gen
option (maxrecursion 10000)

;WITH Split(stpos,endpos)
        AS(
            SELECT 0 AS stpos, CHARINDEX('','',''Alice,Jack,Tom'') AS endpos 
            UNION ALL
            SELECT endpos+1, CHARINDEX('','',''Alice,Jack,Tom'',endpos+1) FROM Split WHERE endpos > 0
        )  
        --LTRIM RTRIM to get rid of white space before start or after end of str
        SELECT RTRIM(LTRIM(SUBSTRING(''Alice,Jack,Tom'',stpos,COALESCE(NULLIF(endpos,0),LEN(''Alice,Jack,Tom'')+1)-stpos))) as name into #temp
        FROM Split

SELECT * into #temp FROM (VALUES (1,''Alice''),(2,''Jack''),(3,''Tom'')) AS t(id,name)

Select id, name, ''placeholder'' as sex into #temp from A

--0=1 to not copy any rows
Select id, name into #temp from A where 0=1 

--the above query equals to 
select id, name into #temp from #temp1
delete from #temp1

create table #tmpStudent(Id int IDENTITY(1,1) PRIMARY KEY,Name varchar(50),Age int) insert into #tmpStudent select id,name,age from #tmpStudent

DECLARE @temp Table ( Id int, Name varchar(20), Age int )

DECLARE  @Class int, @Score int;
 
DECLARE cursor_product CURSOR LOCAL FAST_FORWARD READ_ONLY --fastest config
FOR SELECT class, score FROM #Student;
 
OPEN cursor_product;
 
FETCH NEXT FROM cursor_product INTO @Class,@Score; --must have!
 
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT ''Class: ''+ CAST(@Class AS varchar)+'' Score: ''+ CAST(@Score AS varchar);
    FETCH NEXT FROM cursor_product INTO @Class, @Score;
END;
 
CLOSE cursor_product;
DEALLOCATE cursor_product;

select * from #student

DECLARE  @Class int, @Score int;
 
DECLARE cursor_product CURSOR scroll
FOR SELECT class, score FROM #Student;
 
OPEN cursor_product;
 
FETCH next FROM cursor_product INTO @Class,@Score; --next position of current
PRINT ''Class: ''+ CAST(@Class AS varchar)+'' Score: ''+ CAST(@Score AS varchar);

FETCH first FROM cursor_product INTO @Class,@Score; --very first one
PRINT ''Class: ''+ CAST(@Class AS varchar)+'' Score: ''+ CAST(@Score AS varchar);

FETCH last FROM cursor_product INTO @Class,@Score; -- very last one
PRINT ''Class: ''+ CAST(@Class AS varchar)+'' Score: ''+ CAST(@Score AS varchar);

FETCH prior FROM cursor_product INTO @Class,@Score; --current pos -1
PRINT ''Class: ''+ CAST(@Class AS varchar)+'' Score: ''+ CAST(@Score AS varchar);

FETCH relative -5 FROM cursor_product INTO @Class,@Score; -- current pos -5
PRINT ''Class: ''+ CAST(@Class AS varchar)+'' Score: ''+ CAST(@Score AS varchar);

FETCH absolute 6 FROM cursor_product INTO @Class,@Score; --the 6th of all
PRINT ''Class: ''+ CAST(@Class AS varchar)+'' Score: ''+ CAST(@Score AS varchar);

CLOSE cursor_product;
DEALLOCATE cursor_product;

select GETDATE()
select GETUTCDATE()
select cast(GETDATE() as date)  --date only
SELECT convert(date, GETDATE() ) --date only

-- add or minus is on day basis
select GETDATE()+1 --tomorrow
select GETDATE()-1 --yesterday

-- need to be 24.0 to return float
select GETDATE()+1.0/24 --next hour
select GETDATE()-1.0/24/2 --Last 30 min

--result is already datetime
select DATEADD(yy,-2,''07/23/2009 13:23:44'') --2 years ago
select DATEADD(mm,5, DATEADD(dd,10,GETDATE())) --5 month and 10 days later

select DATEDIFF(mi,GETDATE()+1.0/24,  GETDATE()-1.0/24)  -- return -120
select DATEDIFF(dd,''2019-11-23'', ''2019-12-23'')  --return 30

select cast(''2019-10-23 23:30:59:883'' as datetime) --''yyyy-mm-dd'' 
select cast(''2019/10/23 23:30:59:883'' as datetime) --''yyyy/mm/dd'' use '':'' for ms
select cast(''10-23-2019 23:30:59.883'' as datetime) --''mm-dd-yyyy'' use ''.'' for ms
select cast(''10/23/2019 23:30:59.883'' as datetime) --''mm/dd/yyyy''
--same to use convert
SELECT convert(date, ''07/23/2009'' )

--these pairs are same to get dd,mm,yy part of a datetime, return integer
select Datepart(dd,GETDATE()),day(GETDATE())
select Datepart(mm,GETDATE()),month(GETDATE())
select Datepart(yyyy,GETDATE()),year(GETDATE())

select Datepart(dy,''2019-08-11'') --get day of year: 223

select datename(mm,''2000-5-17'')  --return ''May''
select datename(weekday,''2000-5-17'') --return ''Wednesday''

-- not working!!!! return ''2019-05-17'', as it detect input is string, 103 is ignored
select convert(varchar, ''2019-05-17'', 103)

--input is datetime, reutrn formatted string ''17/05/2019''
select convert(varchar, cast(''2019-05-17'' as datetime), 103)

create table #Student (id int, Class int, Score int )
insert into #Student values(1,1,88)
insert into #Student values(2,1,66)
insert into #Student values(3,2,30)
insert into #Student values(4,2,70)
insert into #Student values(5,2,60)
insert into #Student values(6,3,70)
insert into #Student values(7,3,80)

select * from students where id in (
    select id FROM students
    group by id having count(*)>1
)

select * from students a
right join (
    select firstname, lastname from students
    group by firstname, lastname having count(*)>1
) b
on a.firstname=b.firstname and a.lastname=b.lastname

select * from students except(
    select a.* --need to select all columns here
from students a
    right join (
        select firstname, lastname from students 
        group by firstname, lastname having count(*)>1
    ) b
    on a.firstname=b.firstnameand a.lastname =b.lastname 
)

select distinct * from tableName --save the result equals to delete duplicated rows already

delete from #temp
where id not in(
    select   max(id)   from   #temp
    group   by   col1, col2 --the columns used when checking duplicate
    having count(*)>1
)

WITH tempVw AS (
    SELECT 
        *,
        ROW_NUMBER() OVER ( --over() is required for Row_Number()
            PARTITION BY --this reset the rowNumber to 1 for different group
                col1, col2 --which used as identifier to check duplicate
            ORDER BY  --order by is required in Over()
                col1, col2 --keep same as above
        ) row_num
     FROM 
        YourTable
)
delete FROM tempVw WHERE row_num > 1
select * from YourTable --duplicated rows should be removed in original table

--Use views to add rowId for table without unique id
with tempVw as(
    select ROW_NUMBER() over (order by SurveyTypeid, surveyid ) as rowid,*
    from YourTable
)
--define 2 views together, tempVw2 is all duplicated rows
,tempVw2 as (
    select rowid,a.col1,a.col2
    from tempVw a
    right join (
        select col1, col2 from tempVw
        group by col1, col2 having count(*)>1
    ) b
    on a.col1=b.col1 and a.col2=b.col2
) 

--query after view, delete rows in view will delete original table
delete  from tempVw where rowid in (
    --return all duplicated rows except 1 row for each group that we will keep
    select rowid from tempVw2 where rowid not in (
        --return 1 row for each identifier of duplicated rows
        select min (rowid) from tempVw2 group by col1, col2 having count(*)>1
    )
)
select * from YourTable --duplicated rows should be removed in original table

declare @sql varchar(max)= ''select * from #StudentMarks''
exec (@sql)

--the end has 3 quote because: double single quote + end quote of the query varchar
declare @sql varchar(max)= ''select *  from #StudentMarks where name=''''Alice'''''' 
exec (@sql)

-- output: 2019''s, you need double the quote as it is in a string: 2019''''s
select *,''2019''''s'' as category from #StudentMarks where name=''Alice''

declare @sql varchar(max)= ''select *,''''2019''''''''s'''' as category from #StudentMarks where name=''''Alice''''''
exec (@sql)

declare @Math int=90
declare @sql varchar(max)= ''select *  from #StudentMarks where Math=''+cast(@Math as varchar)+'' and Science=40''
exec (@sql)

declare @Name varchar(max)=''Alice''
--first 3 quotes = double the left quote of @Name + end quote of 1st part of query
-- 4 quotes = start quote of 3rd part+ double the right quote of @Name + end quote of 3rd part
declare @sql varchar(max)= ''select *  from #StudentMarks where name=''''''+@Name+''''''''
exec (@sql)

--double the left and right quote around Alice inside the query
declare @sql2 varchar(max)='' where name = ''''Alice'''' ''
--do not forget to leave a space after each query
declare @sql varchar(max)= ''select *  from #StudentMarks ''+ @sql2
exec (@sql)

Select * from A except Select * from B

Select * from A except Select * from B union all Select * from B except Select * from A

Select * from A Intersect Select * from B

declare @temp table(id int,Name varchar(50),sex varchar(10))
declare @sql varchar(max)= ''select id,name,''''male'''' from student where id<3''
insert into @temp exec (@sql)

DECLARE @sql nvarchar(1000), @input varchar(75)=''Fenton'', @output varchar(75)
SET @sql = ''SELECT top 1 @firstname=firstname FROM [AspNetUsers] WHERE surname = @surname''
EXECUTE sp_executesql @sql, N''@surname varchar(75),@firstname varchar(75) OUTPUT'', @surname = @input, @firstname=@output OUTPUT
select @output

Select col1, col2 from A where exists (Select 1 from B where id=99) --inside exists you can select 1 or anything, it will return TRUE equally

Select id, name from A where not exists (Select 1 from B where B.id=A.id) 

--this equals to use IN keyword
Select id, name from A where id not in (Select id from B)

select format(+61414123456 ,''+##-###-###-###'') --+61-414-123-456
select format(0061414123456 ,''+##-###-###-###'') --+61-414-123-456
select format(0414123456 ,''+61-###-###-###'') --+61-414-123-456

select format(123456789,''#,#'') --123,456,789
select format(123456789,''#,#,'') --123,457

select format(7081990, ''0#######'') --07081990
select format(17081990,''0#######'') --17081990

select format(1234.5678,''#.##'') --1234.57
select format(1234.5678,''#%'') --123457%

--student number for each class
select class,count (*) as total from Student group by class 
--average score for each class
select class,avg(score) as AvgScore from Student group by class 
--highest score for each class
select class,max(score) as HighestScore from Student group by class 
--total donation for each class
select class,sum(donation) as TotalDonation from Student group by class

SELECT * --INTO #Cars
FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',
''Excel 12.0 Xml;HDR=YES;Database=C:\cars.xlsx'',''SELECT * FROM [sheet1$]'');

insert into #temp(id,name) values (1,''Alice''),(2, ''Jack'')

insert into #temp(id,name, sex) select id, name,''male'' from students where sex=1

declare @temp table(id int,Name varchar(50),sex varchar(10))
declare @sql varchar(max)= ''select id,name,''''male'''' from student where id<3''
insert into @temp exec (@sql)

Select * from A cross join B
Select * from A,B --same as above

Select * from where name LIKE '' [^.$#@-]_ [a-z0-9]%''

CREATE TABLE #StudentMarks(Name varchar(100),Math int,Science int, English int)
insert into #StudentMarks values(''Alice'',90,40,60)
insert into #StudentMarks values(''Tom'',30,20,10)

--can be any col in #studentmarks + subject+ marks
select  name, subject, marks 
from #studentmarks  
unpivot
(
  marks --marks: actual values
  for subject in (Math, Science, English) --subject: col names
) a

select name, subject, 
    case subject --this select the right subject score value to put in new col
    when ''Maths'' then math
    when ''Science'' then science
    when ''English'' then english
    end as Marks
from #studentmarks
Cross Join (values(''Maths''),(''Science''),(''English'')) AS Subjct(Subject)

CREATE TABLE #Source(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NULL,
    [FieldCode] [varchar](50) NULL,
    [Value] int NULL
) ON [PRIMARY]


INSERT INTO #Source([Name],[FieldCode],[Value]) 
    SELECT ''Alice'',''English'',60  UNION ALL
    SELECT ''Jack'',''Math'',70  UNION ALL
    SELECT ''Tom'',''Science'',80  UNION ALL
    SELECT ''Tom'',''Math'',75  UNION ALL
    SELECT ''Tom'',''English'',57  UNION ALL
    SELECT ''Jack'',''English'',80  UNION ALL
    SELECT ''Alice'',''Science'',100

select NEWID() --315FC5A3-BE07-41BB-BE4F-75055729FA5B

SELECT CONVERT(varchar(255), NEWID())

SELECT RAND() -- 0<=decimal<1 
SELECT RAND()*15+5; -- 5<=decimal<20 (if include 20 need *16)

SELECT FLOOR(22.6) --22
SELECT CEILING(22.6) --23
SELECT ROUND(22.6,0) -- 23.0
SELECT ROUND(22.6,-1) --20.0 

select *,row_number() over(order by class) rowid from #Student

select *,rank() over(order by class) rowid from #Student --if 1st has 2 pp, next is 3rd
select *,dense_rank() over(order by class) rowid from #Student --if 1st has 2 pp, next is 2nd

select *,row_number() over(partition by class order by class) rowid from #Student

select * from (
    select *,row_number() over(partition by class order by class) rowid from #Student  
)a where rowid<2

select * from (
    select *,row_number() over(partition by class order by class) rowid from #Student  
)a where rowid=2

--to search "keyword" in any cols, in any tables of db
SELECT
b.name as tables, a.name as cols
FROM
sys.columns as a inner join sys.tables as b on a.object_id=b.object_id
where a.name like ''%keyword%''
order by b.name

--to search "keyword" in stored procedure whole text
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE ''%keyword%''
AND ROUTINE_TYPE=''PROCEDURE''

2. Using CTRL+SHIFT+END to Select Text till end (CTRL+ END can move cursor to end)

3. Using CTRL+SHIFT+HOME to Select Text till start (CTRL+HOME can move cursor to end)

4. User CTRL+ arrow key can move cursor jump between words not letters

select left(''hello world'',5) --return: hello
select right(''hello world!'',6) --return:world!

select substring(''hello world'',7,5) --return: world

select REPLACE(''123456'',''34'',''new'') --return 12new56
select stuff(''123456'',3,2,''new'') --same as above, start index=3, length=2

--split ''hello world'' by space
select left(''hello world'',CHARINDEX('' '',''hello world'')-1) 
select right(''hello world'',len(''hello world'')-CHARINDEX('' '',''hello world''))

SELECT LTRIM(''   Sample   ''); --return ''Sample ''
SELECT RTRIM(''   Sample   ''); --return '' Sample''

--char(13)+CHAR(10) = enter
print ''first line''+char(13)+CHAR(10)+''Second line'' --2 lines
--char(9) is tab, the outsode replace delete all space
print REPLACE(REPLACE(REPLACE(REPLACE(''first line
Second line'',CHAR(13),''''),CHAR(10),''''),CHAR(9),''''),'' '','''')

SELECT PATINDEX(''%[mo]%'', ''W3Schools.com''); --return m or o which appear first

select REPLICATE(''hello world '',3) --return: hello world hello world hello world  

select REVERSE(''1234567'') --return 7654321

select ''a''+SPACE(5)+''b'' --return a     b

select top 2 * from ( --between 4 and 5, 5-4+1=2
    select top 5 * from #Student order by score desc)a
order by score

-- if there are multiple highest score, will select highest score
select * from (
    select *,row_number() over( order by score desc) rowid from #Student  
)a where rowid=2
--if there are multiple highest score, still select second highest score 
select * from (
    select *,rank() over( order by score desc) rowid from #Student  
)a where rowid=2
-- rowid between m and n -- rows between order of the m place to n place 

Declare @isDebug bit=0
begin tran

-- insert/update/delete queries    

if @isDebug=0 and @@error=0 --prod run and no errors
begin
    commit tran
end
else -- test run or any error
begin
    rollback tran
end

Declare @isDebug bit=1
BEGIN TRY
    BEGIN tran    
        if @isDebug=0 --test run
        begin 
            -- insert/update/delete queries    
        end
        else --prod run
        begin
            -- insert/update/delete queries
        end

    COMMIT tran --commit if above code has no error
END TRY
BEGIN CATCH
    ROLLBACK tran --if any error jump to this to rollback
    select ERROR_NUMBER() as ErrorNumber, ERROR_MESSAGE() as ErrorMessage, ERROR_PROCEDURE() as ErrorProcedure
END CATCH

CREATE TABLE #temp (ID INT, Val VARCHAR(100))  
INSERT #temp (ID, Val)  VALUES (1,''FirstVal'')  
INSERT #temp (ID, Val)  VALUES (2,''SecondVal'')  

CREATE TABLE #log (ID INT, Val VARCHAR(100), Query VARCHAR(100))  
 
SELECT * FROM #temp 

INSERT #temp (ID, Val) output inserted.ID,inserted.Val,''Insert'' into #log 
VALUES (3,''ThirdVal'')  

--you can also output both deleted and inserted values
Update #temp set Val=''NewVal'' 
output Deleted.ID, Deleted.Val,''Delete'' into #log
where id=3

Update #temp set Val=''NewVal'' 
output inserted.ID,inserted.Val,''Insert'' into #log
where id=3

DELETE FROM #temp  
OUTPUT Deleted.ID, Deleted.Val,''Delete''  into #log 
WHERE ID IN (1,2)  
 
SELECT * FROM #log  
SELECT * FROM #temp 

DROP TABLE #temp  
DROP TABLE #log

UPDATE  a
SET     a.marks = b.marks
FROM    tempDataView a
        INNER JOIN tempData b
            ON a.Name = b.Name

declare @a int =1, @b varchar(max)=''abc''

set @a=2

set @a=(select 2)

select @a=2, @b=''new''

select top 1 @a=ClientId, @b=Surname from [AspNetUsers]

DECLARE @sql nvarchar(1000), @input varchar(75)=''Fenton'', @output varchar(75)
SET @sql = ''SELECT top 1 @firstname=firstname FROM [AspNetUsers] WHERE surname = @surname''
EXECUTE sp_executesql @sql, N''@surname varchar(75),@firstname varchar(75) OUTPUT'', @surname = @input, @firstname=@output OUTPUT
select @output

with StudentVw as(
    select top 100 ROW_NUMBER() over (order by SurveyTypeid, surveyid ) as rowid,*
    from ##temp order by channelid -- if use order by must have top keyword
)
select * from StudentVw --must come with a query and only 1 query

SELECT  [sno]
FROM [DashboardShaperData_64].[dbo].[scores] 
where cno=''3-105'' or cno=''3-245''
group by sno  
having count(*)>1

SELECT  a.sno,a.degree as ''105'', b.degree as "245"
FROM (select sno,degree from scores   where cno=''3-105'' )a 
inner join
(select sno,degree from scores   where cno=''3-245'')b 
on a.sno=b.sno
where a.degree>b.degree