Use Tempdb
Go

CREATE TABLE [Sample] (value int);
INSERT INTO [Sample] VALUES (1),(2),(3),(4),(5),(6);
Go

--simple CASE Expression
SELECT value,
       CASE Value 
        WHEN 1 THEN 'Very Bad'
        WHEN 2 THEN 'Bad'
        WHEN 3 THEN 'Not Bad'
        WHEN 4 THEN 'Good'
        WHEN 5 THEN 'Very Good'
        WHEN 6 THEN 'Excellent'
       ELSE NULL
END AS [Result]
FROM [Sample];

--CHOOSE Scalar Function
SELECT value,
       CHOOSE(value,'Very Bad','Bad','Not Bad','Good','Very Good','Excellent')
FROM [Sample];


--nested IIF Scalr Function
SELECT value, 
IIF(value = 1, 'Very Bad',
  IIF(value = 2, 'Bad',
    IIF(value = 3, 'Not Bad',
      IIF(value = 4, 'Good',
        IIF(value = 5, 'Very Good', 'Excellent'
           )
         )
       )
     )
   )
FROM [Sample];

SELECT StockId, QuoteId, QuoteDay, QuoteClose,
       AVG (QuoteClose) OVER (PARTITION BY StockId 
                              ORDER BY QuoteId 
                               ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS MA20
FROM dbo.Quotes AS T0;

create table Avgs
(
   identifier int not null identity(1,1) primary key,
   student_id varchar(10) not null
        references Students
   term_id tinyint not null
        references Terms
   average tinyiny,
   check (averge between 0 and 20),
   unique (student_id, term_id)
)

declare @t table
(id char(1) primary key,
parent char(1));
 
insert @t values
('A',null),                                   --Level 1
('B', 'A'), ('C', 'A'),                       --Level 2
('D', 'B'), ('E', 'B'),('R','B'), ('F', 'C'), --Level 3
('G', 'D'),                                   --Level 4
('H', 'G'), ('I', 'G');                       --Level 5

;with cte as
(
select id, rnk=0, 
       concats = cast(id as varchar(10))
from @t
where parent is null
 
union all
 
select t.id, rnk+1,
       cast(cte.concats + t.id as varchar(10))
from cte join @t t
on cte.id = t.parent
)
select * from cte
/*
id   rnk         concats
---- ----------- ----------
A    0           A
B    1           AB
C    1           AC
F    2           ACF
D    2           ABD
E    2           ABE
R    2           ABR
G    3           ABDG
H    4           ABDGH
I    4           ABDGI
*/
;with cte as
(
select id, rnk=0, 
       concats = cast(id as varchar(10))
from @t
where parent is null
 
union all
 
select t.id, rnk+1,
       cast(cte.concats + t.id as varchar(10))
from cte join @t t
on cte.id = t.parent
)
select stuff(d.list,1,1,'') as concats
from (select ','+concats
      from cte
  for xml path(''))d(list)
/*
concats
----------------------------------------
A,AB,AC,ACF,ABD,ABE,ABR,ABDG,ABDGH,ABDGI
*/

create nonclustered index ix_parent on table_name (parent_id) 

/*         A
         /   \
        B     C
        |    /|\
        D   E F G
        |
        H
        
*/

declare @t table
(
   id char(1) primary key not null,
   pid char(1) null --references @t
);

insert @t
values ('A', null), ('B','A'),('C','A'),
('D','B'), ('H','D'),('E','C'),('F','C'),('G','C');

with cte as 
(
select id
from @t 
where pid = 'A'

union all

select t.id
from cte c
join @t t
on t.pid = c.id
)
select * from cte