SQL common keywords examples and tricks

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 cast(1.23 as int) --return 1
select convert( int,1.23) --return 1

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 )

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)

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.id --need to select all columns here
          ,a.firstname
          ,a.lastname
          ,a.dob 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

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)

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) 

--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]%‘

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

Declare @isDebug bit=1
begin tran

-- insert/update/delete queries    

if @isDebug=1 --test run
begin
    rollback tran
end
else -- prod run
begin
    commit 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

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

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