SQL common keywords examples and tricks

Case Sensitive Check

1. Return names contain upper case 

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

2. Return same name but diff case 

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 

Case when

https://blog.csdn.net/evilcry2012/article/details/52148641

1. Calculate sum of different group

國家（country）    人口（population）
中國    600
美國    100
加拿大    100
英國    200
法國    300
日本    250
德國    200
墨西哥    50
印度    250

Result:

洲    人口
亞洲    1100
北美洲    250
其他    700

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;

2. Calculate total amount of different pay level

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;

3. Calculate sum of different group with multiple columns

國家（country）    性別（sex）    人口（population）
中國    1    340
中國    2    260
美國    1    45
美國    2    55
加拿大    1    51
加拿大    2    49
英國    1    40
英國    2    60

Result: 
國家    男    女
中國    340    260
美國    45    55
加拿大    51    49
英國    40    60

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;

Cast & Convert (change data type)

Cast is compatible to both sql server and mysql, convert is designed for sql server, and it can have more styles and specially useful for datetime (check datetime part)

select cast(1.23 as int) --return 1
select convert( int,1.23) --return 1

Create a column of numbers (usually ids)

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)

num
1000
1003
1006
1009
1012
1015
1018
1021

Create a column of strings from one long string

;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

name
Alice
Jack
Tom

Create a table of lots of strings in same column

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

 id name
1 Alice
2 Jack
3 Tom

Create a temp table (copy a table)

 1. From a existing table, no need create table (not copy indexing or primary key)

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

Trick to copy a table structure(cols and datatype) but not content

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

2. Create temp table (lifespan: current session, drop on close tab)

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

3. Global temp table (##temp, can visit from other tab, drop on close tab where it is created)

4. Using table variable (lifespan: current transaction, drop after running query block)

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

 Datetime 

1. Current date/datetime/UTC date, convert datetime to date only

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

2. Tomorrow/yesterday, next/last hour (simple nearby datetime)

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

3. Add/minus any period for a date (use with 4.datediff)

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

The datepart can be 'year' or 'yy' or 'yyyy', all same

4. Datediff of 2 datetime ( =2nd-1st, result is + or - interger)

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

5. Generate any datetime

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

6. Get day/week/month/year part of a datetime

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

7. Convert datetime format (input need to be datetime only, result is a string)

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

for a full list of datetime format code (smilar to 103) 

Delete duplicate rows (entire same or partialy same)

0. create example table

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)

1. Select duplicate rows based on 1 column

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

 2. Select duplicate rows based on multiple columns

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

3. Select rows that has unique combination of colums(filter out all duplicate rows)

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 
)

4. Select/delete rows of totally identical values

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

5. Delete duplicate rows in table which has unique id

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
)

6. Delete duplicate rows in table which does not have id

6.1 Delete directly from original table by "Partition" keyword

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

6.2 Add unique ID first so it is similar as point 5

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

Except (check difference between 2 tables of same colums) & Intersect

1. Rows which included in A but not B

Select * from A except Select * from B 

2. Return any diff bewteen A and B 

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

3. Return duplicated rows between A and B

Select * from A Intersect Select * from B

EXEC output to Variable

1. Using table variable

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)

 2. Using sp_executesql 

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 

Exists

1. To add any condition for the select (Same as if) 

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

2. To select new user in A but not in B

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) 

Group by (only work with count(), AVG(), MAX(), MIN(), Sum() )

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

 To get top x rows or the xth place in each group, use row_number()

Import data from excel

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 

1. Mutiple rows with values

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

2. From existing tables

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

3. From exec (Assign EXEC output to Variable)

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)

Join

1. cross join

(https://blog.csdn.net/xiaolinyouni/article/details/6943337)

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

 2. Left join, right join, inner join

Left join: contains all rows from left table A, if A.key=B.key, return result in new ta