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)
