1.把字符串中的大写字母变成小写字母，小写字母变成大写字母

DECLARE @NAME VARCHAR(50),@ROW INT,@SQL VARCHAR(100)
        

SET @NAME = 'ABc'
SET @SQL =''
SET @ROW =1
WHILE @ROW <= LEN(@NAME)
BEGIN
    IF ascii(SUBSTRING(@NAME,@ROW,1)) between 65 and 90
        SET @SQL =@SQL+LOWER(SUBSTRING(@NAME,@ROW,1))
    ELSE IF ASCII(SUBSTRING(@NAME,@ROW,1)) BETWEEN  87 and 122
        SET @SQL =@SQL+ UPPER(SUBSTRING(@NAME,@ROW,1))
    SET @ROW+=1
END
PRINT @SQL

2. 给定一个字符串，找出指定字符串的某个字母，选出他出现的第三次的位置index

declare @value varchar(50), @row int, @sql varchar(max),@find varchar(100)

set @value = 'abcdeabcdeabcde'
set @row =1
set @find = 'e'
set @sql =''

while @row <= 3
begin
    SET @SQL=@SQL+ charindex(@find,@value,@ROW) 
    set @row+=1
end
print @sql
go

2.1 方法2

declare @string  varchar(50) ='abcabcabcarya',@index int=1
declare @counter int =1

while @counter<=2
begin 
set @index=CHARINDEX('a',@string,@index+1)
set @counter+=1
end
print(@index)

2.2 WAY 3 use udf

CREATE function FN_CHARINDEX1(@STR VARCHAR(100),@SCHAR CHAR(1),@POS INT)
returns varchar(80)
AS
BEGIN
DECLARE @RETURN INT=0
    ;with cte
    as
    (select 1 as 'strt',substring(@str,1,1) as 'indchar'
    union all
    select strt+1,SUBSTRING(@STR,STRT+1,1)
    FROM CTE 
    WHERE STRT <LEN(@STR)),
    FINAL
    AS
    (
    SELECT *,ROW_NUMBER()OVER(PARTITION BY INDCHAR ORDER BY STRT) AS 'POS'
    FROM CTE 
    WHERE INDCHAR = @SCHAR)
    SELECT @RETURN = STRT
    FROM FINAL
    WHERE POS =@POS

    RETURN @RETURN

END
SELECT dbo.FN_CHARINDEX1('PPPP','P',2)

3. 给定一个字符串，按照26个字母顺序重新输出

DECLARE @Input VARCHAR(100) = '', @Output VARCHAR(100)=''
DECLARE @T TABLE (IndChar CHAR(1))
DECLARE @L INT = 1

WHILE @L <= LEN(@Input)
    BEGIN
        INSERT INTO @T VALUES
        (SUBSTRING(@Input, @L, 1))

        SET @L += 1

    END

    SELECT @Output = @Output+IndChar
    FROM @T
    ORDER BY IndChar

    SELECT @Output
GO

3.1 方法二

declare @str varchar(100), @num int
declare @table_1 table (
  name varchar(50))

set @str='sadjhldjkdsa'
set @num=1
while @num<len(@str)+1
 begin
  insert into @table_1 values (substring(@str,@num,1))
  set @num+=1
 END
SELECT * FROM @TABLE_1 ORDER BY NAME
DECLARE @L VARCHAR(50)='', @NUM1 INT=1, @ROW INT, @CHA1 CHAR(1)
SET @ROW=(SELECT COUNT(*) FROM @table_1)
WHILE @NUM1<@ROW+1
 BEGIN
  BEGIN
  SET @CHA1=(SELECT NAME FROM (SELECT NAME, ROW_NUMBER() OVER(ORDER BY NAME)AS 'RANK1' FROM @table_1) A
    WHERE RANK1=@NUM1)
  SET @L=@L+@CHA1
  end
 SET @Num1+=1
 END
select @L

go

4. 删除重复的字母在同一个字符串

drop table if exists #table
create table #table(characters varchar(100)) 
declare @string varchar(100) = 'aaaerawasdfbbcssccdddeeffg', @id int = 1

while @id <= len(@string)
begin
 IF substring(@string,@id,1) in (select * From #table)
  set @id +=1
 else
    BEGIN
   insert into #table values (substring(@string,@id,1))
   set @id += 1
   END
end
declare @string3 varchar(100) = ''
select @string3 = @string3+characters from #table
select @string3

4.1 way2

declare @string varchar(100), @op varchar(100)=''

set @string='aaaerawasdfbbcssccdddeeffZ'

while 1<=len(@string)
 begin
  set @op +=substring(@string,1,1)
  set @string= replace(@string,substring(@string,1,1),'')
 end
select @op

5. 找在一个字符串中，一个字母出现的次数

declare @name varchar(20),@row int,@find varchar(10),@COUNTER INT
set @name = 'PPPAAPPPP'
set @find ='P'
SET @COUNTER =0
SET @ROW =1
while @row< =len(@name)
BEGIN
    IF SUBSTRING(@NAME,@ROW,1) =@FIND
        SET @COUNTER+=1
    ELSE 
    SET @COUNTER=@COUNTER
    SET @row+=1
END
PRINT @COUNTER

6. 转换字符： 给定字符串‘CDE‘，输出’BCD'

DECLARE @string varchar(50), @row int,@L INT,@OUTPUT VARCHAR(100)=''
DECLARE @TAB TABLE (CARDS CHAR(10))

set @string = 'ACBEF'
SET @L =1

WHILE @L <=LEN(@STRING)
    BEGIN
    iF ASCII(LOWER(SUBSTRING(@STRING,@L,1)))=97
        SET @OUTPUT =@OUTPUT 
    ELSE IF ASCII(LOWER(SUBSTRING(@STRING,@L,1))) BETWEEN 97 and 122
        SET @OUTPUT =@OUTPUT +CHAR((ASCII(LOWER(SUBSTRING(@STRING,@L,1))))-1)
    SET @L+=1

    END
PRINT @OUTPUT

7.打印1-10的数字

declare @id int =1
while @id <11
    begin
    print @id

    set @id +=1
    end

8.把字符串中的元音字母筛选出来

DECLARE @STR VARCHAR(25) = 'LUYAO123',@V VARCHAR(25) ='',@C VARCHAR(25) =''
DECLARE @L INT =1

WHILE @L<=LEN(@STR)
    BEGIN
        IF SUBSTRING(@STR,@L,1) IN ('A','E','I','O','U')
            set @v =@v+SUBSTRING(@STR,@L,1)
        IF SUBSTRING(@STR,@L,1) LIKE '[A-Z]' AND SUBSTRING(@STR,@L,1) NOT IN ('A','E','I','O','U')
            SET @C =@C+SUBSTRING(@STR,@L,1)
        SET @L +=1
    END

SELECT @V,@C
GO

9.把字符串中的特殊符号不要，只打印字母

DECLARE @STR VARCHAR(25) = 'ABC123#ABC_', @V VARCHAR(25) ='',@C VARCHAR(25)=''
DECLARE @L INT =1
WHILE @L<=LEN(@STR)
    BEGIN
        IF SUBSTRING(@STR,@L,1) LIKE '[A-Z0-9]'
            set @v =@v+SUBSTRING(@STR,@L,1)
        
        SET @L +=1
    END

SELECT @V
GO

10. 查找指定字符串的长度

DECLARE @C VARCHAR(MAX)= 'ABCDEFG123',@COUNTER INT
SET @COUNTER =1

WHILE 1=1
BEGIN
    IF LEFT(@C,@COUNTER)=@C
    BREAK
    SET @COUNTER+=1
END
    PRINT @COUNTER

SELECT @COUNTER
GO

11.find first Friday of every month for a given year. Using WHILE loop

DECLARE @Yr INT = 2019
DECLARE @OP TABLE (MonthNam VARCHAR(25), FirstFriday DATE)
DECLARE @Month INT = 1
WHILE @Month <= 12
BEGIN
    IF DATENAME(WEEKDAY, DATEFROMPARTS(@Yr, @Month, 1)) = 'Saturday'
        INSERT INTO @OP values
        (DATENAME(MONTH, DATEFROMPARTS(@Yr, @Month, 1)), DATEADD(DD, 6,DATEFROMPARTS(@Yr, @Month, 1)))
    IF DATENAME(WEEKDAY, DATEFROMPARTS(@Yr, @Month, 1)) = 'Sunday'
        INSERT INTO @OP values
        (DATENAME(MONTH, DATEFROMPARTS(@Yr, @Month, 1)), DATEADD(DD, 5,DATEFROMPARTS(@Yr, @Month, 1)))
    IF DATENAME(WEEKDAY, DATEFROMPARTS(@Yr, @Month, 1)) = 'Monday'
            INSERT INTO @OP values
            (DATENAME(MONTH, DATEFROMPARTS(@Yr, @Month, 1)), DATEADD(DD, 4,DATEFROMPARTS(@Yr, @Month, 1)))
    IF DATENAME(WEEKDAY, DATEFROMPARTS(@Yr, @Month, 1)) = 'Tuesday'
            INSERT INTO @OP values
            (DATENAME(MONTH, DATEFROMPARTS(@Yr, @Month, 1)), DATEADD(DD, 3,DATEFROMPARTS(@Yr, @Month, 1)))
    IF DATENAME(WEEKDAY, DATEFROMPARTS(@Yr, @Month, 1)) = 'Wednesday'
            INSERT INTO @OP values
            (DATENAME(MONTH, DATEFROMPARTS(@Yr, @Month, 1)), DATEADD(DD, 2,DATEFROMPARTS(@Yr, @Month, 1)))
    IF DATENAME(WEEKDAY, DATEFROMPARTS(@Yr, @Month, 1)) = 'Thursday'
            INSERT INTO @OP values
            (DATENAME(MONTH, DATEFROMPARTS(@Yr, @Month, 1)), DATEADD(DD, 1,DATEFROMPARTS(@Yr, @Month, 1)))
    IF DATENAME(WEEKDAY, DATEFROMPARTS(@Yr, @Month, 1)) = 'Friday'
            INSERT INTO @OP values
            (DATENAME(MONTH, DATEFROMPARTS(@Yr, @Month, 1)), DATEADD(DD, 0,DATEFROMPARTS(@Yr, @Month, 1)))
    
    SET @Month += 1  
END

SELECT * FROM @OP
GO

11.1 方法2

DECLARE @Yr INT = 2019
DECLARE @OP TABLE (MonthNam VARCHAR(25), FirstFriday DATE)
DECLARE @Month INT = 1, @Day INT = 1
WHILE @Month <= 12
BEGIN
    IF DATENAME(WEEKDAY, DATEFROMPARTS(@Yr, @Month, 1)) = 'Friday'
    INSERT INTO @OP values
            (DATENAME(MONTH, DATEFROMPARTS(@Yr, @Month, 1)), DATEFROMPARTS(@Yr, @Month, 1))
    ELSE
        BEGIN
            SET @Day = 1
            WHILE 1 = 1

                BEGIN
                IF DATENAME(WEEKDAY, DATEFROMPARTS(@Yr, @Month, @Day)) = 'Friday'
                    BEGIN
                    INSERT INTO @OP values
                    (DATENAME(MONTH, DATEFROMPARTS(@Yr, @Month, 1)), DATEFROMPARTS(@Yr, @Month, @Day))
                    BREAK
                    END
                SET @Day += 1
                END
            END
    SET @Month += 1 

END
SELECT * FROM @OP
GO

11.2 CASE

DECLARE @Yr INT = 2019
DECLARE @OP TABLE (MonthNam VARCHAR(25), FirstFriday DATE)
DECLARE @Month INT = 1
WHILE @Month <= 12
Begin
insert into @OP values(@Month,case 
      when DATENAME(WEEKDAY, DATEFROMPARTS(@Yr, @Month, 1))
      = 'Saturday' then  DATEADD(DD, 6,DATEFROMPARTS(@Yr, @Month, 1))
      when DATENAME(WEEKDAY, DATEFROMPARTS(@Yr, @Month, 1))
      = 'Sunday' then  DATEADD(DD, 5,DATEFROMPARTS(@Yr, @Month, 1))
      when DATENAME(WEEKDAY, DATEFROMPARTS(@Yr, @Month, 1))
      = 'Monday' then  DATEADD(DD, 4,DATEFROMPARTS(@Yr, @Month, 1))
      when DATENAME(WEEKDAY, DATEFROMPARTS(@Yr, @Month, 1))
      = 'TuesDay' then  DATEADD(DD, 3,DATEFROMPARTS(@Yr, @Month, 1))
      when DATENAME(WEEKDAY, DATEFROMPARTS(@Yr, @Month, 1))
      = 'Wednesday' then  DATEADD(DD, 2,DATEFROMPARTS(@Yr, @Month, 1))
      when DATENAME(WEEKDAY, DATEFROMPARTS(@Yr, @Month, 1))
      = 'Thursday' then  DATEADD(DD, 1,DATEFROMPARTS(@Yr, @Month, 1))
      when DATENAME(WEEKDAY, DATEFROMPARTS(@Yr, @Month, 1))
      = 'Friday' then  DATEADD(DD, 0,DATEFROMPARTS(@Yr, @Month, 1))
      end)
 set @Month+=1
      
END

12. 做一个* diamond的循坏出来

DECLARE @L INT= 6, @MaxLoop INT, @Cnt INT = 1
SET @MaxLoop = @L + (@L-1)
WHILE @MaxLoop > 0
BEGIN
    PRINT REPLICATE(' ', ABS(@L - @Cnt)) + REPLICATE('* ', ABS(CASE 
                                                                    WHEN @Cnt >= @L THEN @MaxLoop
                                                                    ELSE @Cnt
                                                                END))

                SELECT @MaxLoop -= 1, @Cnt += 1
END

GO

12.1 way 2

go
declare @string varchar(40) = '', @id1 int =1, @id2 int = 3
declare @table table(string1 varchar(40))
while @id1 <= 4
begin
 insert into @table values
 (replicate(' ',4-@id1)+replicate('* ',@id1))
 set @id1 +=1
end
while @id1 <8
begin
 insert into @table values
 (replicate(' ',@id1-4)+replicate('* ',@id2))
 set @id1 +=1
 set @id2-=1
end
select * From @table

13. Create a VIEW to display BusinessEntityID as BusID, JobTitle, Age, Gender as Male OR Female (don't use CASE statement). (HumanResources.Employee)

create view vies11 
as
select BusinessEntityID as 'busid',JobTitle,DATEDIFF(yy,birthdate,getdate()) as 'age','male' as 'gender'
from HumanResources.Employee
where gender='m'
union
select BusinessEntityID as 'busid',JobTitle,DATEDIFF(dd,birthdate,getdate()) as age,'female' as 'gender'
from HumanResources.Employee
where gender='f'

select * from vies11

14.Create a VIEW to display BusinessEntityID as BusID, FirstName, LastName, EmployeeSince, JobTitle, Age, AgeCategory (don't use any concept we did not cover like CASE, IIF etc)

AgeCategory
Under 21
21-30
31-40
41-50
Over 50"

CREATE VIEW businfo111
as 
select e.BusinessEntityID as busid,p.FirstName as fn,p.LastName as ln,e.JobTitle,datediff(yy,e.BirthDate,getdate()) as age, 'under21' as 'agecategory'
from AdventureWorks2017.HumanResources.Employee e
join AdventureWorks2017.Person.Person p
on e.BusinessEntityID=p.BusinessEntityID
where datediff(yy,e.BirthDate,getdate()) <21
union
select e.BusinessEntityID as busid,p.FirstName as fn,p.LastName as ln,e.JobTitle,datediff(yy,e.BirthDate,getdate()) as age, '21-30' as 'agecategory'
from AdventureWorks2017.HumanResources.Employee e
join AdventureWorks2017.Person.Person p
on e.BusinessEntityID=p.BusinessEntityID
where datediff(yy,e.BirthDate,getdate())  between 21 and 30
union
select e.BusinessEntityID as busid,p.FirstName as fn,p.LastName as ln,e.JobTitle,datediff(yy,e.BirthDate,getdate()) as age, '31-40' as 'agecategory'
from AdventureWorks2017.HumanResources.Employee e
join AdventureWorks2017.Person.Person p
on e.BusinessEntityID=p.BusinessEntityID
where datediff(yy,e.BirthDate,getdate())  between 31 and 40
union
select e.BusinessEntityID as busid,p.FirstName as fn,p.LastName as ln,e.JobTitle,datediff(yy,e.BirthDate,getdate()) as age, '41-50' as 'agecategory'
from AdventureWorks2017.HumanResources.Employee e
join AdventureWorks2017.Person.Person p
on e.BusinessEntityID=p.BusinessEntityID
where datediff(yy,e.BirthDate,getdate())  between 41 and 50
union
select e.BusinessEntityID as busid,p.FirstName as fn,p.LastName as ln,e.JobTitle,datediff(yy,e.BirthDate,getdate()) as age, 'over50' as 'agecategory'
from AdventureWorks2017.HumanResources.Employee e
join AdventureWorks2017.Person.Person p
on e.BusinessEntityID=p.BusinessEntityID
where datediff(yy,e.BirthDate,getdate())  >50
GO
select * from businfo111

14.1 way2

select e.BusinessEntityID as busid,p.FirstName as fn,p.LastName as ln,e.JobTitle,datediff(yy,e.BirthDate,getdate()) as age, AGECATEGORY=
CASE 
WHEN datediff(yy,e.BirthDate,getdate()) <21 THEN 'UNDER21'
WHEN datediff(yy,e.BirthDate,getdate()) BETWEEN 21 AND 30 THEN '21-30'
WHEN datediff(yy,e.BirthDate,getdate())  BETWEEN 31 AND 40 THEN '31-40'
WHEN datediff(yy,e.BirthDate,getdate())  BETWEEN 41 AND 50 THEN '41-50'
WHEN datediff(yy,e.BirthDate,getdate())  >50 THEN 'OVER50'
END
from AdventureWorks2017.HumanResources.Employee e
join AdventureWorks2017.Person.Person p
on e.BusinessEntityID=p.BusinessEntityID

15. Find number of Providers in each age group (Screenshot 2).

<=20, 21 - 30, 31 -40, 41 - 50, >50. If there are no Providers in the group you should show 0"

image.png

;WITH CTE AS 
(
select datediff(yy,e.BirthDate,getdate()) as age, AGECATEGORY=
CASE 
WHEN datediff(yy,e.BirthDate,getdate()) <21 THEN 'UNDER21'
WHEN datediff(yy,e.BirthDate,getdate()) BETWEEN 21 AND 30 THEN '21-30'
WHEN datediff(yy,e.BirthDate,getdate()) BETWEEN 31 AND 40 THEN '31-40'
WHEN datediff(yy,e.BirthDate,getdate()) BETWEEN 41 AND 50 THEN '41-50'
WHEN datediff(yy,e.BirthDate,getdate())  >50 THEN 'OVER50'
END
from AdventureWorks2017.HumanResources.Employee e
join AdventureWorks2017.Person.Person p
on e.BusinessEntityID=p.BusinessEntityID
),
AgeGrp
AS
(
SELECT 'UNDER21' AS 'AgeRange'
UNION ALL
SELECT '21-30'
UNION ALL
SELECT '31-40'
UNION ALL
SELECT '41-50'
UNION ALL
SELECT 'OVER50'
),
Emp
AS
(SELECT AGECATEGORY,COUNT(AGECATEGORY) 'Cnt'
 FROM CTE
GROUP BY AGECATEGORY)
SELECT A.AgeRange, ISNULL(E.Cnt, 0)
FROM AgeGrp A
LEFT JOIN Emp E
ON A.AgeRange = E.AGECATEGORY

16.最老的员工，最年轻的员工年龄与每一个员工的年纪差别 （CTE)

 ;with cte_age
 as(
 select e.BusinessEntityID,e.JobTitle,datediff(yy,e.HireDate,getdate()) as age
 from AdventureWorks2017.HumanResources.Employee e),
 cte_extreme
 as
 (select max(age) as 'max',min(age) as 'min'
 from cte_age)

 select a.*,a.age-e.max,a.age-e.min
 from cte_age a
 cross join cte_extreme e

17.Find the most recent OrderDate, TotalDue and SalesPersonID for each SalesPerson from Sales.SalesOrderHeader

 ;with salesinfo as
 (
  select sh.OrderDate, sh.SalesPersonID,sh.TotalDue,
  dense_rank()over(partition by sh.SalesPersonID order by sh.OrderDate desc) as'r'
  from AdventureWorks2017.Sales.SalesOrderHeader sh 
  where sh.SalesPersonID is not null )

select * from salesinfo
where r=1 

18.What is the difference between most recent order and second most recent order for each sales person

with salediff as (
select sh.OrderDate, sh.SalesPersonID as id1,SUM(sh.TotalDue) as money1,dense_rank()over(partition by sh.salespersonid order by sh.OrderDate desc) as 'r1'
from AdventureWorks2017.Sales.SalesOrderHeader sh where sh.SalesPersonID is not null GROUP BY sh.SalesPersonID,sh.OrderDate),
salesdiff2
as (select sh.OrderDate, sh.SalesPersonID as id2,SUM(sh.TotalDue) as money2,dense_rank()over(partition by sh.salespersonid order by sh.OrderDate desc) as 'r2'
from AdventureWorks2017.Sales.SalesOrderHeader sh where sh.SalesPersonID is not null GROUP BY sh.SalesPersonID,sh.OrderDate)

select id1,money1-money2 as diff
from salediff
join salesdiff2
on id1=id2
and r1=1
and r2=2

19. 用cte循坏1-9

with cte_r
as
(select 0 as 'start'   ----anchor
union all
select start+1   -------
from cte_r       -------   recurisive
where start <9   -------
)
select * from cte_r

20.用cte删除重复字母

declare @str varchar(25) = 'ning',@op varchar(25) = ''
;with cte_r
as
(select 1 as 'initial',substring(@str,1,1) as 'indchar'
union all
select initial+1,substring(@str,initial+1,1) as 'indchar'
from cte_r
where initial <len(@str)
),
cte_f
as
(
select distinct indchar   -----doesn't order by ,so the result can be in any order
from
cte_r
)

select @op =@op+indchar
from cte_f
select @op
go

21.用cte给员工分级，eg:level 1 level 2

;with cte
as(
select EmployeeID,FirstName,Title,1 as 'lv1'
from Employees
where ManagerID is null)
union all
select e.EmployeeID,e.FirstName,e.Title,lv1+1
from Employees e
join cte c
on c.EmployeeID =e.ManagerID

select* from cte
go

22. Find out the first weekday of each month in 2018? (Use Recursive CTE)

with cte as
           (select 1 as mth,DATEFROMPARTS(2018,1,1) as dt
           union all
           select mth+1,DATEFROMPARTS(2018,mth+1,1)
           from cte
           where mth <12
           )
select * ,case  
            when datepart(weekday,dt) =3 then dateadd(dd,6,dt)
            when datepart(weekday,dt) =4 then dateadd(dd,5,dt)
            when datepart(weekday,dt) =5 then dateadd(dd,4,dt)
            when datepart(weekday,dt) =6 then dateadd(dd,3,dt)
            when datepart(weekday,dt) =7 then dateadd(dd,2,dt)
            when datepart(weekday,dt) =1 then dateadd(dd,1,dt)
            else dt
        end,datename(weekday,dt)
        from cte

22.1 方法2

;with cte as
           (select 1 as mth,DATEFROMPARTS(2018,1,1) as dt
           union all
           select mth+1,DATEFROMPARTS(2018,mth+1,1)
           from cte
           where mth <12
           )
select *, case
            WHEN DATEPART(WEEKDAY,DT) <=2 THEN DATEADD(DD,2-DATEPART(WEEKDAY,DT),DT)
            ELSE DATEADD(DD,9-DATEPART(WEEKDAY,DT),DT)
            END
FROM CTE

go

23. Generate Dates for next 1000 days starting from today using recursive CTE

;with numgen
as
(select 0 as 'num'
union all
select num+1
from numgen
where num<9
),
all_nums
as
(select (n1.num*1+n2.num*10+n3.num*100) +1 as 'rn'
from numgen n1,numgen n2,numgen n3)
select dateadd(dd,rn,getdate()) as 'dt'
from all_nums

23.1 WAY 2

;with numgen
as
(select 0 as 'num'
union all
select num+1
from numgen
where num<9
),
all_nums
as
(select (n1.num*1+n2.num*10+n3.num*100) +1 as 'rn'
from numgen n1,numgen n2,numgen n3)
select dateadd(dd,rn,getdate()) as 'dt'
from all_nums

24.Find which alphabets are not used in a given string. Ex: string - ABC then your answer should show D-Z. Use recursive CTEs

declare @string varchar(50)='abc',@sql varchar(100)=' ',@word varchar(100),@int int=1

Declare @string varchar(30)='ABCDFGH'
;with CTE_all
as (select  1 as anchor,char(65) as letter
union all
select anchor+1,CHAR(65+anchor)
from CTE_all
where anchor<26),
CTE_string
AS 
(select  1 as anchor1,UPPER(SUBSTRING(@string,1,1)) as letter
union all
select anchor1+1,UPPER(SUBSTRING(@string,anchor1,1))
from CTE_string
where anchor1<LEN(@string))
select Cte_all.letter from CTE_all
left join CTE_string
on CTE_all.letter=CTE_string.letter
where CTE_string.letter is null

25..Capitalize first letter of each word in a given sentence. Ex: input = batcH 28IS a gOOD bAtch. Out put should be Batch 28 Is A Good Batch.

declare @string varchar(100) = 'batcH 28 IS a gOOD bAtch', @id int = 1
declare @string1 varchar(100) = ''
while @id <= LEN(@string)
begin
 if SUBSTRING(@string,@id-1,1) = ''
 begin
  set @string1 = @string1 + upper(SUBSTRING(@string,@id,1))
  set @id += 1
 end
 else
 begin
  set @string1 = @string1 + lower(SUBSTRING(@string,@id,1))
  set @id +=1
 end
end
select @string1

26.Create a scalar function to mask a email address, SSN, Phone. It should take only 2 parameters 1 for value to be masked and other is what is it like Phone, SSN etc.

'123-456-7890', 'Phone'
'--7890'
'012-54-1234', 'SSN'
'--1234'
'zoe@yahoo.com', 'Email'
'Z*@y****.com'

create function fn_mask(@a varchar(50),@b varchar(50))

returns varchar(60)

as 

 begin

 declare @c varchar(50)

 if @b ='Phone'

 set @c= '***-***-'+right(@a,4)

 else if @b ='SSN'

 set @c='***-**-'+right(@a,4)

 else if @b ='Email'

 set @c=left(@a,1)+ REPLICATE('*',CHARINDEX('@',@a)-2)+'@'+

left(SUBSTRING(@a,CHARINDEX('@',@a)+1,LEN(@a)),1)

+REPLICATE('*', len(SUBSTRING(@a,CHARINDEX('@',@a)+2, 

len(SUBSTRING(@a,CHARINDEX('@',@a)+1,LEN(@a)))-5 )))+'.com'

return @c

 end

select [dbo.fn_mask('zoe@yahoo.com','Email](mailto:dbo.fn_mask('zoe@yahoo.com','Email)')

27.find the higner score for each student

28. Create a scalar function to return sum of 2 input numbers

29.-Create a scalar function to return concatenation first name, middle name, last name separated by comma. When middle name is NULL then ignore it. Ex: Asmita, Kumar; Natalie, K, Portar

create function fnnames (@fname varchar(50),@mname varchar(50),@lname varchar(50))
returns varchar(100)
as
    begin 
    declare @fullname varchar(100)
    set @fullname=concat(@fname,',',isnull(@mname,''),concat(',',@lname))
    return @fullname
    end

select dbo.fnnames('zoe',null,'a')

image.png

if i don't want to use if else condition,how to delete one comma?

30. Create a scalar function to return age of a person in years based on DOB with a precision of 2 digits. Ex: 24.25 Years

 create function fnages(@dob date)
returns int
as
    begin
        declare @age int
        set @age=datediff(yy,@dob,getdate())
        return @age
    end

select dbo.fnages('1994-02-08') as age

31. depend on employee id ,find their jobtitle（use proc）

create or alter PROC spb30_person (@jobtitle varchar(100) out,@bid int)
as
select @jobtitle =jobtitle from AdventureWorks2017.HumanResources.Employee e
join AdventureWorks2017.Person.Person p
on p.BusinessEntityID=e.BusinessEntityID
where p.BusinessEntityID=@bid
go
declare @r int,@jobtitle varchar(100)
exec @r = spb30_person @jobtitle out,1
select @r 'return val',@jobtitle   ---为了证明stored procedure 返回的是int
go

32. CREATE UDF, and use it to prevent the email only have 3 domains, like that

create function fnemailvalid (@email varchar(100))
returns bit
as
begin
    if @email like'_____%__%.__%'
    return 1
    return 0
end



create table persondetails
(id int primary key,
cname varchar(100),
email varchar(100) check (dbo.fnemailvalid(email) =1)
)

33.Create a Scalar UDF which takes 3 input parameters a String, Start Position and End Position. UDF returns portion of the string starting from Start Position parameter until End Position parameter. Note: Should not use SUBSTRING, LEFT, RIGHT functions to achieve this.

DROP FUNCTION FNSTRING
create function fnstring(@string varchar(50),@start int,@end int)
returns varchar(50)
as
    BEGIN
    DECLARE @SQL VARCHAR(100)
    SET @SQL=STUFF(@STRING,@START,1,'')
    SET @SQL=STUFF(@SQL,LEN(@SQL),@END,'')


    RETURN @SQL
    END

SELECT DBO.FNSTRING('ABCDE',1,2)

34.find total sales for each person in special date (udf)

CREATE OR ALTER FUNCTION FNsALESdETAILS (@SPID INT,@SALEDATE DATE)
RETURNS @T TABLE(SALESPID INT,ORDERDATE DATE,TOTALDUE MONEY,DAILYSALES MONEY)
AS
BEGIN
DECLARE @DAILYSALES MONEY
SELECT @DAILYSALES = SUM(H.TOTALDUE)
FROM AdventureWorks2017.Sales.SalesOrderHeader H
WHERE H.SalesPersonID=@SPID AND ORDERDATE =@SALEDATE

INSERT INTO @T
SELECT H.SalesPersonID,H.ORDERDATE,H.TOTALDUE,@DAILYSALES AS DAILYSALES
FROM AdventureWorks2017.Sales.SalesOrderHeader H
WHERE H.SalesPersonID=@SPID AND ORDERDATE =@SALEDATE

RETURN
END

SELECT * FROM FNSALESDETAILS(274,'2005-07-01')

35."Create a TVF, to take 2 dates as inputs and return a table with following columns between those 2 dates

DateKey (YYYYMMDD) Ex: 20190321
Date
MonthNum
Yearm
MonthName
WeekDayName
WeekDay Number
Quarter
12.1 Using In line TVF
12.2 Using Multi-Statement TVF"

---multiline
CREATE FUNCTION [dbo].[DateRange_To_Table] ( @minDate_Str NVARCHAR(30), @maxDate_Str NVARCHAR(30))
RETURNS  @Result TABLE(Date NVARCHAR(30) NOT NULL, 
MONTHMUN INT NOT NULL,
YEARM INT NOT NULL,
MONTHNAME VARCHAR(20) NOT NULL,
WEEKDAYNAME VARCHAR(20) NOT NULL,
WEEKDAYNUMBER INT NOT NULL,
QUARTER INT NOT NULL)
AS

begin

    DECLARE @minDate DATETIME, @maxDate DATETIME
    SET @minDate = CONVERT(Datetime, @minDate_Str,112)
    SET @maxDate = CONVERT(Datetime, @maxDate_Str,112)


    WHILE @maxDate > @minDate
    BEGIN
     SET @minDate = (SELECT DATEADD(dd,1,@minDate))
        INSERT INTO @Result (DATE,MONTHMUN,YEARM,MONTHNAME,WEEKDAYNAME,WEEKDAYNUMBER,QUARTER )
        SELECT CONVERT(NVARCHAR(10),@minDate,112), CONVERT(NVARCHAR(30),DATEPART(mm,@minDate)),
                CONVERT(NVARCHAR(30),DATEPART(YY,@minDate)),CONVERT(NVARCHAR(30),DATENAME(MM,@MINDATE)),
            CONVERT(NVARCHAR(30),DATENAME(DW,@minDate)),CONVERT(NVARCHAR(30),DATENAME(WK,@minDate)),
            CONVERT(NVARCHAR(30),DATENAME(QQ,@minDate))
    
    END


    return

end   

SELECT * FROM dbo.DateRange_To_Table ('20190101','20190131')

---inline
DECLARE @SD DATE = '1/1/2019', @ED DATE = '12/31/2019'
;WITH CTE
AS
(SELECT 0 'Strt'
UNION ALL
SELECT Strt+1
FROM CTE
WHERE Strt < 9
),
CTE1
AS
(SELECT C1.Strt*1+C2.Strt*10+C3.Strt*100 AS RN
FROM CTE C1, CTE C2, CTE C3 )

SELECT DATEADD(DD, RN, @SD) 
FROM CTE1
WHERE DATEADD(DD, RN, @SD) < = @ED
ORDER BY 1 

table

36.Create a procedure to add a patient entry, all columns values has to be passed as parameters. See patient table to add required parameters.

CREATE PROC P_EN (@ID INT OUT,@FULLNAME VARCHAR(100) OUT,@GENDER CHAR(1) OUT,DOB DATE OUT)
AS 
INSERT INTO  PATIENT (PATIENTID, P_FULLNAME,GENDER,DOB)
VALUES
(@ID,@FULLNAME,@GENDER,@DOB)

GO

EXEC  P_EN @ID =' ',@FULLNAME =' ',@GENDER =' ',@DOB =' '
GO

37.Create a procedure to add a Provider entry, all columns values has to be passed as parameters. See Provider table to add required parameters.

CREATE PROC P_EN (@ID INT OUT,@FULLNAME VARCHAR(100) OUT,@GENDER CHAR(1) OUT,@DOB DATE OUT,@SPECIAL VARCHAR(100))
AS 
INSERT INTO  PROVIDER (PROVIDERID, PR_FULLNAME,GENDER,DOB,SPECIALIZATION)
VALUES
(@ID,@FULLNAME,@GENDER,@DOB,@SPECIAL)

GO

EXEC  P_EN @ID =' ',@FULLNAME =' ',@GENDER =' ',@DOB =' ',@SPECIAL=' '

38.Create a procedure to add an appointment for a Patient with a particular Provider. Input parameters should be PatientID, ProviderID, Appointment Date, Appointment Time. Requested Patient ID and Provider ID should in their respective tables.

DROP PROC PC_APP

CREATE PROC PC_APP (@PATIENTID INT,@PROVID INT,@APPDATE DATE,@APPTIME DATETIME)
AS
    BEGIN
     IF @PATIENTID NOT IN(SELECT PATIENTID FROM Patient)
        PRINT 'PROVIDE INFO ABOUT PATIENT'
     ELSE IF @PROVID NOT IN (SELECT ProviderID FROM Provider)
        PRINT 'PROVIDER INFO ABOUT PROVIDER'
    ELSE IF @PATIENTID IN(SELECT PATIENTID FROM Patient) AND @PROVID NOT IN (SELECT ProviderID FROM Provider)
        INSERT INTO appointment(patientID,ProviderID,appointment_date,appointment_time)
        VALUES (@PATIENTID,@PROVID,@APPDATE,@APPTIME)
    END
GO

EXEC PC_APP 107,2,'2018-01-01','2017-12-31'

39.Create a procedure that shows number of appointments each Provider in each day has

CREATE PROC PC_COUT (@COUNT INT OUT)
AS
SELECT @COUNT=COUNT(*)
FROM appointment A
GROUP BY A.ProviderID,A.appointment_date
GO

DECLARE @NUM INT
EXEC PC_COUT @NUM OUT
PRINT @NUM
GO

40.Create a procedure that takes an input parameter for ProviderID and show number of appointments that provider has for day

CREATE PROC PC_PROVIDER (@PROVIDERID INT,@COUNT INT OUT)
AS
    SELECT @COUNT=COUNT(*)
    FROM appointment
    WHERE PROVIDERID=@PROVIDERID
    GROUP BY appointment_date
GO

DECLARE @NUM INT
EXEC PC_PROVIDER 1,@NUM OUT
PRINT @NUM

41.Create a procedure that takes an input parameter for PatientID and show number of appointments that Patient has for each day

42.Create a procedure that takes an input parameters for PatientID and Date (make it optional when it is not provided then use GETDATE()) show number of appointments that Patient has for that day

43.Create a procedure that takes an input parameters for ProviderID and Date (make it optional when it is not provided then use GETDATE()) show number of appointments that ProviderID has for that day

44.Extended version of previous question (Don't jump into this question before doing the previous question)

Create a procedure to add an appointment for a Patient with a particular Provider. Input parameters should be PatientID, ProviderID, Appointment Date (this date must be a weekday), Appointment Time (this time must be with between 8-5). Requested Patient ID and Provider ID should in their respective tables.

45.Extended version of previous question (Don't jump into this question before doing the previous question)

Create a procedure to add an appointment for a Patient with a particular Provider. Input parameters should be PatientID, ProviderID, Appointment Date (this date must be a weekday), Appointment Time (this time must be with between 8-5 and each appointment should be atleast 30 mins and there should be a lunch break from 12-1, no appointments should be scheduled during this time). Requested Patient ID and Provider ID should in their respective tables.

46.Extended version of previous question (Don't jump into this question before doing the previous question)

Create a procedure to add an appointment for a Patient with a particular Provider. Input parameters should be PatientID, ProviderID, Appointment Date (this date must be a weekday), Appointment Time (this time must be with between 8-5 and each appointment should be atleast 30 mins and there should be a lunch break from 12-1, no appointments should be scheduled during this time, a Provider cannot have more than 10 appointments in a day). Requested Patient ID and Provider ID should in their respective tables.

47.Extended version of previous question (Don't jump into this question before doing the previous question)

Create a procedure to add an appointment for a Patient with a particular Provider. Input parameters should be PatientID, ProviderID, Appointment Date (this date must be a weekday), Appointment Time (this time must be with between 8-5 and each appointment should be atleast 30 mins and there should be a lunch break from 12-1, no appointments should be scheduled during this time, a Provider cannot have more than 10 appointments in a day). When the appointment is requested for Gynecologists, patient should be female if not don't schedule it.

48.Create procedure that accepts ProdID, ProdName, UnitPrice, DiscountPercent and adds the info Product table with following conditions

1. If the product exists update price, product name, discount percentage and modified date (todays date)
2. If product does not exist create a new entry in the table with the required info

"Inventory(ProdID (PK, FK), CurrentInventory)
Product(ProdID(PK), ProdName, UnitPrice, DiscountPercent, ModifiedDate)
OrderDetails(OrdDetailID (PK), OrderID (FK), ProdID (FK), Quantity, LineTotal)
Orders(OrderID, OrderDate, OrderTotal)

CREATE PROC INVE(@ID INT =NULL,@PRICE MONEY,
                                    @PRODNAME VARCHAR(50) = NULL,@DISPER INT =NULL ,
                                    @MODDATE DATE =NULL)
 AS
BEGIN
SET @MODDATE=GETDATE()

        IF @ID IN (SELECT PRODID FROM PRODUCTS) AND @ID IS NOT NULL
            UPDATE PRODUCTS
            SET UNITPRICE=@PRICE,DISCOUNTPERCENT= @DISPER,MODIFIEDDATE=@MODDATE,PRODNAME=@PRODNAME
            WHERE PRODID=@ID

        IF @ID NOT IN (SELECT PRODID FROM PRODUCTS) AND @ID IS NOT NULL
            INSERT INTO PRODUCTS VALUES
            (@ID,@PRICE,@DISPER,@MODDATE,@PRODNAME)
END

EXEC INVE @ID=44,@PRICE =1,@DISPER=1,@MODDATE=NULL,@PRODNAME='AA'

48.1 --Create a procedure that places an Order with following conditions

-- 1. There should be enough inventory to place the order so check the inventory before you enter record into the OrderDetails and Order Tables.
-- 2. Once Order is placed successfully deduct the Order placed quantity from the Current Inventory for the product"

49."Create a trigger for delete operation for the employee (Use tblTrigger_Emp file). Following are requirements for the trigger

1. CEO record cannot be deleted
2. If a manager record is deleted, all employees who are reporting to that particular manager has to be assigned to the manger of the manger record which you are deleting.
3. If it is employee at last level in the hierarchy then delete it without any conditions."

IF OBJECT_ID('Employee_Trig') IS NOT NULL
DROP TABLE Employee_Trig
GO

CREATE TABLE Employee_Trig (
EmpID INT PRIMARY KEY,
EmpName VARCHAR(100) NOT NULL,
MgrID INT FOREIGN KEY REFERENCES Employee_Trig(EmpID) NULL,
Salary SMALLMONEY
)


INSERT INTO Employee_Trig VALUES
(1, 'Raj', NULL, 100000),
(2, 'John', 1, 80000),
(3, 'Claire', 1, 80000),
(4, 'Jack', 1, 90000),
(5, 'Smith', 2, 75000),
(6, 'Will', 2, 75000),
(7, 'Bebo', 2, 75000),
(8, 'Neethu', 3, 60000),
(9, 'Nayan', 3, 60000),
(10, 'Josh', 3, 50000),
(11, 'Krish', 7, 50000),
(12, 'Kaiser', 4, 50000)
*****************************trigger
create trigger tg_Manager on Employee_Trig
Instead of delete
as

declare @EmpID int
select @EmpID=EmpID from deleted

if @empid =1
 print 'go away'

else if @empid in (select MgrID from Employee_Trig)
 begin
  update Employee_Trig
  set MgrID=(select MgrID from Employee_Trig where EmpID=@EmpID)
  where MgrID=@EmpID
  delete Employee_Trig where EmpID=@EmpID
 end
else
 delete Employee_Trig where EmpID=@EmpID

create trigger tg_Manager on Employee_Trig
Instead of delete
as
BEGIN
    DECLARE @T TABLE(EMpID INT, MgrID INT, R INT)

    INSERT INTO @T
    SELECT EmpID, MgrID, ROW_NUMBER()OVER (ORDER BY EmpID)
    FROM deleted 
    DECLARE @MaxL INT
    SELECT @MaxL = MAX(R)
    FROM @T

    WHILE @MaxL > 0


    update e
    set e.MgrID=d.MgrID
    from Employee_Trig e
    join deleted d
    on e.MgrID=d.EmpID
    where d.EmpID in (select Mgrid from Employee_Trig) and d.EmpID !=1

    delete Employee_Trig 
    from Employee_Trig e
    join deleted d
    on e.EmpID=d.EmpID
    where d.EmpID !=1

    DECLARE @EID INT
    SELECT @eid = EmpID FROM Employee_Trig WHERE MgrID IS NULL
    IF @EID IN (SELECT EmpID FROM deleted)
    print 'CEO cannot be fired'
END
GO

50."Create a Trigger for maintaining fixed cardinality between 2 tables.

1. Using AFTER Trigger
2. Using FOR Trigger"

51. (variable) Get all the employees who are NOT Managers (EmployeeID,Title,FirstName,LastName,

--HireDate/BirthDate in 107 style format along with MangerID, of those employees whose FirstName starts with '?%'
--Here you are getting a filter condition (?) as character from front-end application.
--Also User has the choice to select either HireDate or BirthDate from front-end application

declare @code varchar(300), @string varchar(200), @condition varchar(200)

set @string='HireDate'
set @condition='a'
set @code='select EmployeeID,Title,FirstName,LastName, convert(date,'+@string+' , 107) from Employee 
     where EmployeeID not in (select ManagerID from employees where ManagerID is not null) and firstname like '''+@condition+'%'''

print(@code)

52. 不太明白功能的一段代码

CREATE TABLE ##Table_Names (ID INT IDENTITY, TableName VARCHAR(250), RowCnt INT)
DROP TABLE ##Table_Names 
SELECT ROW_NUMBER() OVER(ORDER BY Object_id) as 'ID', name as 'TableName' 
INTO ##Tables
FROM sys.tables
DECLARE @iter INT, @MaxIter INT = 2
DECLARE @RowCnt INT, @Table VARCHAR(250) 
DECLARE @SQL VARCHAR(1000)=''

SELECT @iter = MIN(ID), @MaxIter = MAX(ID) 
FROM ##Tables

WHILE @iter <= @MaxIter
BEGIN
    SELECT @Table = TableName FROM ##Tables
    WHERE ID = @iter

    SET @SQL = 'DECLARE @RowCnt INT 
                IF(SELECT COUNT(*) FROM ' + @Table +') = 0 ' +
                'BEGIN 
                    SELECT @RowCnt  = COUNT(*) FROM '+ @Table +
                    ' INSERT INTO ##Table_Names VALUES
                ('''+@Table+''', @RowCnt)
                 END'
    EXEC(@SQL)
    PRINT @SQL
    SET @iter += 1
END

SELECT * FROM ##Table_Names

SELECT * FROM ##tables

53. 当库存表低于特定值的时候，就下单，记录下单数量，型号，日期

CREATE TABLE PROD
(ID INT PRIMARY KEY,
PNAME VARCHAR(100),
CURRENTINV INT,
ORDERDATE DATE,
MINORDER INT
) 
GO
creat table prodorder
(
id int,
orderqul int,
orderdate date)
go

create trigger th on prod
after update
as
    declare @@pid int,@minorrrder int, @currinv int

    select @currinv= currentinv, @pid =pid,@minorder =minorder
    from prod
    where pid =(select pid from deleted)

    if @currinv <10
    insert into prodorder values
    (@pid,@minorder,getdate())

54. give your a id,i want to know they name and age (stored procedure)

example

55. 查询上下级部门信息（recursive cte)

http://blog.chenpeng.info/html/1602

CREATE FUNCTION F_GET_CHILD_DEPT ( @root_dept_id VARCHAR(200) )
RETURNS @dept_table TABLE ( dept_id VARCHAR(200),dept_pid VARCHAR(200),dept_name VARCHAR(200) )
AS 
    BEGIN

        WITH DEPT_INFO AS(

        SELECT TSD_ID,TSD_PARENT_ID,TSD_NAME FROM T_DEPARTMENT WHERE TSD_ID = @root_dept_id

        UNION ALL

        SELECT a.TSD_ID,a.TSD_PARENT_ID,a.TSD_NAME FROM T_DEPARTMENT AS a,DEPT_INFO AS b WHERE a.TSD_PARENT_ID = b.TSD_ID
        –SELECT a.TSD_ID,a.TSD_PARENT_ID,a.TSD_NAME FROM T_DEPARTMENT AS a,DEPT_INFO AS b WHERE a.TSD_ID = b.TSD_PARENT_ID

        )

        INSERT INTO @dept_table SELECT * FROM DEPT_INFO

        RETURN

    END

查询时将需要查询的部门ID作为函数的参数即可：

SELECT * FROM F_GET_CHILD_DEPT(‘B0AA5C53-67CD-478B-9284-E3A01E4A012F’)

SELECT * FROM F_GET_PARENT_DEPT(‘B0AA5C53-67CD-478B-9284-E3A01E4A012F’)

56.（udf) The function takes one input value, a ProductID, and returns a single data value, the aggregated quantity of the specified product in inventory.

IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL  
    DROP FUNCTION ufnGetInventoryStock;  
go
CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)  
RETURNS int   
AS   
-- Returns the stock level for the product.  
BEGIN  
    DECLARE @ret int;  
    SELECT @ret = SUM(p.Quantity)   
    FROM AdventureWorks2017.Production.ProductInventory p
    WHERE p.ProductID = @ProductID   
        AND p.LocationID = '6';  
     IF (@ret IS NULL)   
        SET @ret = 0;  
    RETURN @ret;  
END; 

SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply  
FROM AdventureWorks2017.Production.Product  
WHERE ProductModelID BETWEEN 75 and 80;

57. The function takes one input parameter, a customer (store) ID, and returns the columns ProductID, Name, and the aggregate of year-to-date sales as YTD Total for each product sold to the store.

https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/create-user-defined-functions-database-engine?view=sql-server-2017

IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL  
    DROP FUNCTION Sales.ufn_SalesByStore;  
GO  
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)  
RETURNS TABLE  
AS  
RETURN   
(  
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'  
    FROM  AdventureWorks2017.Production.Product AS P   
    JOIN AdventureWorks2017.Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID  
    JOIN AdventureWorks2017.Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID  
    JOIN AdventureWorks2017.Sales.Customer AS C ON SH.CustomerID = C.CustomerID  
    WHERE C.StoreID = @storeid  
    GROUP BY P.ProductID, P.Name  
);  

SELECT * FROM Sales.ufn_SalesByStore (602);  

58.i have productname column in my table and the query i need to get only the alphabets.

column contains the special characters (!@#$%^&*():'"{}[]|?-+=,) and numbers(0-9), space

sample data

Foodhold USA,mlc.

Beverage Partners Worldwide (North canada)......

Bread World 8

my expected output will be

FoodholdUSAmlc

BeveragePartnersWorldwide(Northcanada)

BreadWorld

WITH SampleData (col) AS
(
    SELECT 'Foodhold USA,mlc.'
    UNION ALL SELECT 'Beverage Partners Worldwide (North canada)......'
    UNION ALL SELECT 'Bread World 8'''
),
Tally (n) AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM sys.all_columns
)
SELECT col
    ,NewCol=
    (
        SELECT SUBSTRING(col, n, 1)
        FROM SampleData b
        CROSS APPLY (SELECT n FROM Tally WHERE n BETWEEN 1 AND LEN(col)) c
        WHERE PATINDEX('%[a-zA-Z]%', SUBSTRING(col, n, 1)) <> 0 AND b.col = a.col
        ORDER BY n
        FOR XML PATH('')
    )
FROM SampleData a

60. sales infomation：

SELECT S.*,AVG(SalesAmount)over(partition by salesperson)'indavg',
sum(salesamount)over(partition by salesperson) 'indsales',
avg(salesamount)over()'orgavg',
sum(salesamount)over()'orgsales'
FROM Sales s

way 2

select s1.*,s2.indsales,s2.indavg,s3.orgavg,
cast((s2.indsales/s3.orgsales)*100 as numeric(5,2)) as 'percentcontribution'
from sales s1
join
(
select salesperson,sum(salesamount) 'indsales',avg(salesamount)'indavg'
from sales
group by salesperson
)s2
on s1.SalesPerson = s2.SalesPerson
cross join (
select sum(salesamount) 'orgsales',avg(salesamount) 'orgavg'
from sales) s3
order by 1

61. PIVOT TABLE

use AdventureWorks2017

;with CTE1
As
(select * from
(select S.SalesPersonID,S.TotalDue,DATENAME(MONTH,S.OrderDate) as 'OrderMonth' ,Year(S.OrderDate) as 'Year' from Sales.SalesOrderHeader S
where S.SalesPersonID is not null) as A
PiVOt
(sum(Totaldue) for OrderMonth in([January],[February], [March], [April], [May], [June],
[July], [August], [September], [October], [November], [December]))P),
 CTE2
As
(select * from
(select S.SalesPersonID,S.TotalDue,DATENAME(MONTH,S.OrderDate) as 'OrderMonth' ,Year(S.OrderDate) as 'Year' from Sales.SalesOrderHeader S
where S.SalesPersonID is not null) as A
PiVOt
(count(Totaldue) for OrderMonth in([January],[February], [March], [April], [May], [June],
[July], [August], [September], [October], [November], [December]))P
)
select C1.SalesPersonID, c1.year,
case when C1.January is null then null
  when C1.January is  not null then CONCAT(CAST(C1.January as varchar),'/',CAST(C2.January as varchar))
  end as January,
case when C1.February is null then null
  when C1.February is  not null then CONCAT(CAST(C1.February as varchar),'/',CAST(C2.February as varchar))
  end as February,
case when C1.March is null then null
  when C1.April is  not null then CONCAT(CAST(C1.April as varchar),'/',CAST(C2.March as varchar))
  end as April,
case when C1.May is null then null
  when C1.May is  not null then CONCAT(CAST(C1.May as varchar),'/',CAST(C2.May as varchar))
  end as March,
case when C1.March is null then null
  when C1.March is  not null then CONCAT(CAST(C1.March as varchar),'/',CAST(C2.March as varchar))
  end as March

from CTE1 as C1

join CTE2 C2
on C1.SalesPersonID=c2.SalesPersonID and C1.Year=C2.Year
order by C1.SalesPersonID,c1.year

62. 找出每个月与上个月相比增长率最高的那个员工以及月份

;with cte as
(
select h.SalesPersonID,month(h.OrderDate) as month,sum(h.TotalDue) as money,
isnull(LAG (sum(h.TotalDue)) OVER (partition by salespersonid ORDER BY month(h.OrderDate)),null) as price1
from AdventureWorks2017.Sales.SalesOrderHeader h
where h.OrderDate between'2011-01-01' and '2011-12-31'
and h.SalesPersonID is not null
group by h.SalesPersonID,month(h.OrderDate)
),
cte2
as(
select *,((money-price1)/price1)*100.0 as result 
from cte)
select * from cte2 where result in(select max(result) from cte2 group by SalesPersonID)

63.find employees who are retiring on a weekend assuming age if retirment is 60

select dataname(weekday,dataadd(YY,60.E.BIRTHDATE))
FROM humanresources.employee e
where dataname( weekday,dataadd(YY,60.E.BIRTHDATE)) in ('sunday','saturday')

64.join

-who are manager?

select distinct emp2.empid, emp2.name
from employee emp1
inner join employee emp2
on emp1.managerby = emp2.empid

-- who are not manager

select emp2.empid,emp2.name
from employee emp1
full outer join employee emp2
on emp1.managerby = emp2.empid
where emp1.empid is null

65. 找salesperson 大于平均sales总值的

use AdventureWorks2017

select  salespersonid,sum(totaldue) 'totalsale'
from sales.salesorderheader
WHERE SalesPersonID is not null
GROUP BY salespersonid
HAVING sum(totaldue) >= (
                                        select avg( totalsale )
                                        from
                                        (select sum(totaldue) 'totalsale'
                                        from sales.salesorderheader
                                        where salespersonid is not null
                                        group by salespersonid) ts)

go

66. bank running total

SELECT *, (SELECT SUM(SalesAmount)
            FROM Sales S_IN
            where S_IN.SalesID <= SOUT.SalesID)
FROM Sales SOUT

67. 找出排名第四高的销售额(rank function之外的做法）

select score from rankwithoutrankings r1 where 
 (4 - 1) = (select count(distinct(Score)) 
            from rankwithoutrankings r2
            where r2.Score > r1.Score )

68. 工资排第二的做法

;with cte2ndhighest 
as 
(
select *,DENSE_RANK() over (order by salary desc) as rnk
from EMP
)
select * from cte2ndhighest
where rnk = 2;

69.PRINT 1 to 100 without using Loops

;with CTE_print(Number)
as
(
select 1
union all
select Number = Number + 1 from CTE_print
where Number < 102
)
select * from CTE_print
OPTION (MAXRECURSION 1000) 

70. Print factorial of 1 to 10

;with factorial as
(
select 1 as n, 1 as fact
union all
select n+1, (n+1)*fact
from factorial 
where n<10
)
select * from factorial

71. PRINT A to Z using recursive CTE

;with cte_atoz(NUM,LETTER) as
(
SELECT 65, char(65)
union all
select NUM + 1, CHAR(num + 1)
from cte_atoz
where num < 90
)
select letter from cte_atoz

72. 找出N th 高的value (万能公式！！！！！）

select Column from Table T1 where 
 (N - 1) = (select count(distinct(Column)) 
            from Table T2
            where T2.Column > T1.Column )

73. 找出最少有一男一女的父母

create table parentandchild
(parent varchar(20), child varchar(10))


insert into parentandchild values
('1','male'),
('1','female'),
('1','male'),
('2','male'),
('3','female'),
('4','male'),
('4','female'),
('5','male'),
('5','female'),
('5','female')

select * from parentandchild

select parent, count(distinct child)
from parentandchild
group by parent
having  count(distinct child) >1

74. 把‘boy'一个字母一个字母按列输出

print substring('boy',1,1) +char(13)+substring('boy',2,1)+char(13)+substring('boy',3,1)

75. 判断闰年的条件

declare @year int
--set @year =2019
set @year=year(GETDATE());--获取系统当前日期再取出其中的年份
if @year%4=0 and @year%100<>0 or @year%400=0
    print cast (@year as char(4))+'is Leap year'    
else
    print cast(@year as char(4))+'is not leap year'

76. Table 1id是 1-10, Table B ID 是15-20. How to combine them?

SELECT * FROM Q35_1
UNION ALL 
SELECT * FROM Q35_2

77. Write a UDF to print first 50 Fibonacci numbers.

create or alter  function Fib (@number int)
returns table
as
return
(
with CTE(id,PreN,N)
as(
select 1,0,1
union all
select id+1,N,preN+N
from CTE
where id<@number
)
select * from CTE
)
select * from dbo.fib(5)

78.Write a UDF take a number as an input and check if its Palindrome or not.

create function Pali(@number int)
returns varchar(10)
as
begin 
declare @result varchar(10)
IF CAST(@number as varchar(50))= REVERSE(CAST(@number as varchar(50)))
    set @result= 'Yes'
else 
set @result='NO'
return @result
end
select dbo.pali(1214)

79.Write a UDF take a String as an input and check if its Palindrome string or not.

create or alter function Pali_string(@string varchar(20))
returns varchar(10)
as
begin 
declare @result varchar(10)
IF @string= REVERSE(@string)
    set @result= 'Yes'
else 
set @result='NO'
return @result
end
select dbo.pali_string('aba')

80.Write a UDF take a number as an input and check if its Armstrong number or not.

create or alter function arm_number (@number int)
returns varchar(10)
as 
begin
declare @var varchar(20)=cast(@number as varchar(20)),@sum int,@result varchar(20)
;with CTE(id,letter,string)
as
( 
select 0,'',@var
union all
select id+1,left(string,1),SUBSTRING(string,2,len(string))
from CTE
where id<len(@var)
)
select @sum=sum( Power(letter,3))  from CTE
where id >0
if @sum=@number
set @result='Y'
else
set @result='N'
return @result
end
select dbo.arm_number(1533)

80. find table name (need improve)

declare @sql nvarchar(max);

select @sql = 
    (select ' UNION ALL
        SELECT ' +  + quotename(name,'''') + ' as database_name,
               s.name COLLATE DATABASE_DEFAULT
                    AS schema_name,
               t.name COLLATE DATABASE_DEFAULT as table_name 
               FROM '+ quotename(name) + '.sys.tables t
               JOIN '+ quotename(name) + '.sys.schemas s
                    on s.schema_id = t.schema_id'
    from sys.databases 
    where state=0
    order by [name] for xml path(''), type).value('.', 'nvarchar(max)');

set @sql = stuff(@sql, 1, 12, '') + ' order by database_name, 
                                               schema_name,
                                               table_name';

execute (@sql);

81. CONVERT DEFAULT TIMEZONE TO NULL

SELECT ReminderDateTime = 
    CASE WHEN ReminderDateTime = '1899-12-29 00:00:00.000' THEN '' 
    ELSE CONVERT(VARCHAR(20), ReminderDateTime, 121) 
    END 
FROM [FieldPlannerActivityImportWIP]

82. returns only the male users older than 30 years and the female users older than 20 years:

USE TestDB
GO
 
SELECT *
FROM [User]
WHERE (CASE WHEN Gender='M' THEN Age ELSE 0 END) > 30 OR (CASE WHEN Gender='F' THEN Age ELSE 0 END) > 20

83. use parsename split full name

declare @ename varchar(max)

set @ename = 'John, M, Smith, Jr'

select REVERSE(PARSENAME(REPLACE(REVERSE(@eName), ',', '.'), 1)) AS FirstName
   , REVERSE(PARSENAME(REPLACE(REVERSE(@eName), ',', '.'), 2)) AS MiddleName
   , REVERSE(PARSENAME(REPLACE(REVERSE(@eName), ',', '.'), 3)) AS LastName