You can use CHARINDEX to find a specific character or pattern within a column.
CHARINDEX returns the position of the keyword, for example, the "weather" appears at 29th (caculation includes space (空格键））， so it finally returns 29

Use substring:
SUBSTRING(Expression, Starting Position, Total Length)
We used SUBSTRING, passed in the description column, told the function to begin counting from the 15th character, for the remaining length of the string, which ensured we returned each remaining cause of outage.

About how to solve this problem:
start_number is a number
now I want to extract additional information of the description.
now I want the string except the "weather"
substring(description, ?, len(description)) ? should be the starting position

Sometimes you might want to 'save' the results of a query so you can do some more work with the data. You can do that by creating a temporary table that remains in the database until SQL Server is restarted.

SELECT  album.title AS album_title,
  artist.name as artist,
  MAX(track.milliseconds / (1000 * 60) % 60 ) AS max_track_length_mins
-- Name the temp table #maxtracks
INTO #maxtracks
FROM album
-- Join album to artist using artist_id
INNER JOIN artist ON album.artist_id = artist.artist_id
-- Join track to album using album_id
INNER JOIN track ON track.album_id=album.album_id
GROUP BY artist.artist_id, album.title, artist.name,album.album_id
-- Run the final SELECT query to retrieve the results from the temporary table
SELECT album_title, artist, max_track_length_mins
FROM  #maxtracks
ORDER BY max_track_length_mins DESC, artist;

Replace the null value:

-- Check the IncidentState column for missing values and replace them with the City column
SELECT IncidentState, ISNULL(IncidentState,"City") AS Location
FROM Incidents
-- Filter to only return missing values from IncidentState
WHERE IncidentState IS NULL

What if you want to replace missing values in one column with another and want to check the replacement column to make sure it doesn't have any missing values? To do that you need to use the COALESCE statement.

-- Replace missing values 
SELECT Country, coalesce(IncidentState, City, 'Unknown') AS Location
FROM Incidents
WHERE Country IS NULL

Create a new column, SourceCountry, defined from these cases:
When Country is 'us' then it takes the value 'USA'.
Otherwise it takes the value 'International'.

SELECT Country, 
       CASE WHEN Country = 'us'  THEN 'USA'
       ELSE 'International'
       END AS SourceCountry
FROM Incidents

Use case when:

- Complete the syntax for cutting the duration into different cases
SELECT DurationSeconds, 
-- Start with the 2 TSQL keywords, and after the condition a TSQL word and a value
      case when (DurationSeconds <= 120) then 1
-- The pattern repeats with the same keyword and after the condition the same word and next value          
       when (DurationSeconds > 120 AND DurationSeconds <= 600)then 2
-- Use the same syntax here             
        when (DurationSeconds > 601 AND DurationSeconds <= 1200) then  3
-- Use the same syntax here               
        when (DurationSeconds > 1201 AND DurationSeconds <= 5000) then 4
-- Specify a value      
       ELSE 5 
       END AS SecondGroup   
FROM Incidents

Use count just after "select" and then use group by

-- Write a query that returns an aggregation 
SELECT MixDesc, SUM(Quantity) AS Total
FROM Shipments
-- Group by the relevant column
GROUP BY MixDesc

Use DATEDIFF and DATEADD
SELECT DATEADD(DD, -30, '2020-06-21')
DATEDIFF (datepart, startdate, enddate)

- Return the DeliveryDate as 5 days after the ShipDate
SELECT OrderDate, 
       DATEADD(DD,5,ShipDate) AS DeliveryDate
FROM Shipments

Use ABS() to return non-negative values
Use SQUARE() and SQRT to calculate square and square root

use derived tabel:
calculate the maximum value of the blood glucose level for each record by age.

SELECT a.RecordId, a.Age, a.BloodGlucoseRandom, 
-- Select maximum glucose value (use colname from derived table)
       b.MaxGlucose
FROM Kidney a
-- Join to derived table
JOIN (SELECT Age, MAX(BloodGlucoseRandom) AS MaxGlucose FROM Kidney GROUP BY Age) b
-- Join on Age
ON a.Age = b.Age

Another sample to select the highest bloodpressure grouped by their age:

SELECT *
FROM Kidney a
-- Create derived table: select age, max blood pressure from kidney grouped by age
JOIN (select age,max(BloodPressure) AS MaxBloodPressure from kidney group by age) b
-- JOIN on BloodPressure equal to MaxBloodPressure
ON a.BloodPressure=b.MaxBloodPressure
-- Join on Age
AND a.Age=b.Age

UseCTE() to simplify the sql sentences:

-- Specify the keyowrds to create the CTE
with BloodGlucoseRandom (MaxGlucose) 
as (SELECT MAX(BloodGlucoseRandom) AS MaxGlucose FROM Kidney)

SELECT a.Age, b.MaxGlucose
FROM Kidney a
-- Join the CTE on blood glucose equal to max blood glucose
JOIN BloodGlucoseRandom b
on a.BloodGlucoseRandom=b.MaxGlucose

Find the patient with the highest bloodpressure:

- Create the CTE
WITH BloodPressure(MaxBloodPressure)
AS (SELECT MAX(BloodPressure) FROM kidney AS MaxBloodPressure)

SELECT *
FROM Kidney a
-- Join the CTE  
Join BloodPressure b
on a.BloodPressure=b.MaxBloodPressure

you can create a window for the entire table. To create partitions using a specific column, you need to use OVER() along with PARTITION BY.

SELECT OrderID, TerritoryName, 
       -- Total price for each partition
       sum(OrderPrice)
       -- Create the window and partitions
       over(partition by TerritoryName) AS TotalPrice
FROM Orders

you want to figure out the first OrderDate in each territory or the last one. How would you do that? You can use the window functions FIRST_VALUE() and LAST_VALUE(), respectively! Here are the steps:

First, create partitions for each territory
Then, order by OrderDate
Finally, use the FIRST_VALUE() and/or LAST_VALUE() functions as per your requirement

SELECT TerritoryName, OrderDate, 
       -- Select the first value in each partition
       first_value(OrderDate) 
       -- Create the partitions and arrange the rows
       OVER(PARTITION BY TerritoryName order BY OrderDate) AS FirstOrder
FROM Orders

Use lead() next, lag() previous

SELECT TerritoryName, OrderDate, 
       -- Specify the previous OrderDate in the window
       lag(OrderDate) 
       -- Over the window, partition by territory & order by order date
       Over(partition BY TerritoryName order BY OrderDate) AS PreviousOrder,
       -- Specify the next OrderDate in the window
       lead(OrderDate) 
       -- Create the partitions and arrange the rows
       Over(partition BY TerritoryName order BY OrderDate)  AS NextOrder
FROM Orders

create a running total:

SELECT TerritoryName, OrderDate, 
       -- Create a running total
       sum(orderprice) 
       -- Create the partitions and arrange the rows
       over(partition by TerritoryName order by OrderDate) AS TerritoryTotal      
FROM Orders

Use row_number() to count the rows in different groups

SELECT TerritoryName, OrderDate, 
       -- Assign a row number
       row_number()
       -- Create the partitions and arrange the rows
       OVER(PARTITION BY TerritoryName ORDER BY OrderDate) AS OrderCount
FROM Orders

Use standered deiviation : stdev()

There is no functions to calculate the mode, so we have to create CTE() to them

-- Create a CTE Called ModePrice which contains two columns
with ModePrice (OrderPrice, UnitPriceFrequency)
AS
(
    SELECT OrderPrice, 
    ROW_NUMBER() 
    OVER(PARTITION BY OrderPrice ORDER BY OrderPrice) AS UnitPriceFrequency
    FROM Orders 
)

-- Select everything from the CTE
SELECT * FROM ModePrice

the whole version of how to use CTE to calculate mode:

WITH ModePrice (OrderPrice, UnitPriceFrequency)
AS
(
    SELECT OrderPrice,
    ROW_NUMBER() 
    OVER (PARTITION BY OrderPrice ORDER BY OrderPrice) AS UnitPriceFrequency
    FROM Orders
)

-- Select the order price from the CTE
SELECT OrderPrice AS ModeOrderPrice
FROM ModePrice
-- Select the maximum UnitPriceFrequency from the CTE
WHERE UnitPriceFrequency IN (select max(UnitPriceFrequency) FROM ModePrice)