SQLite

String Concat

select '{' || GroundId || ', "' || Name || '"},' as formatted from grounds

If Not Exists

insert into x .... select.... where not exists (select 1 from x where id = y)

Update... From

update Grounds set TeamId = PlId From Teams t where t.TeamId = TeamId

How can I do this in SQLite?

SQLite doesn't support the UPDATE ... FROM syntax like SQL Server or PostgreSQL. But you can achieve the same effect using a correlated subquery or a JOIN inside an UPDATE. Here's how to rewrite your query in SQLite:

UPDATE Grounds
SET TeamId = (
    SELECT PlId
    FROM Teams
    WHERE Teams.TeamId = Grounds.TeamId
)
WHERE EXISTS (
    SELECT 1
    FROM Teams
    WHERE Teams.TeamId = Grounds.TeamId
);

DROP COLUMN

Sqlite: alter table Predictions drop column PlId

SQLite doesn’t support ALTER TABLE ... DROP COLUMN directly. But you can still remove a column like PlId from the Predictions table using a workaround that involves recreating the table without that column.

Here’s how to do it safely:

Step-by-Step: Drop a Column in SQLite

1. Get the current schema

   .schema Predictions
   

   Let’s say it looks like this:

   CREATE TABLE Predictions (
       PredictionId INTEGER PRIMARY KEY,
       PlId INTEGER,
       MatchId INTEGER,
       Outcome TEXT
   );
   

2. Create a new table without PlId

   CREATE TABLE Predictions_new (
       PredictionId INTEGER PRIMARY KEY,
       MatchId INTEGER,
       Outcome TEXT
   );
   

3. Copy data over

   -- Disable FK checking
   PRAGMA foreign_keys = OFF;
   INSERT INTO Predictions_new (PredictionId, MatchId, Outcome)
   SELECT PredictionId, MatchId, Outcome FROM Predictions;
   

4. Replace the old table

   DROP TABLE Predictions;
   -- Re-enable FK checking
   PRAGMA foreign_keys = ON;
   ALTER TABLE Predictions_new RENAME TO Predictions;
   

Getting Day Of Week

SELECT strftime('%w', '2025-10-18 12:30:00') AS WeekdayNumber,
       strftime('%A', '2025-10-18 12:30:00') AS WeekdayName;

Explanation

'%w' returns the numeric day of the week (0 = Sunday, 6 = Saturday).

'%A' returns the full name of the weekday (e.g., "Saturday").

SELECT t.Name AS TeamName, COUNT(*) AS FridayAppearances
FROM (
    SELECT HomeTeamId AS TeamId
    FROM Matches
    WHERE strftime('%w', KickOff) = '5'
    
    UNION ALL
    
    SELECT AwayTeamId AS TeamId
    FROM Matches
    WHERE strftime('%w', KickOff) = '5'
) AS FridayMatches
JOIN Teams t ON t.Id = FridayMatches.TeamId
GROUP BY t.Name
ORDER BY FridayAppearances DESC;

Querying JSON

SELECT 
    TeamId,
    SUM(json_extract(StatsJson, '$.expectedGoals')) AS TotalExpectedGoals
FROM 
    MatchTeamStats
WHERE 
    TeamId = 6

-- where keys might not be present

SELECT 
    TeamId,
    SUM(json_extract(StatsJson, '$.expectedGoals') - (
	COALESCE(json_extract(StatsJson,'$.attPenGoal'), 0) + 
	COALESCE(json_extract(StatsJson, '$.attPenMiss'), 0) + 
	COALESCE(json_extract(StatsJson,'$.attPenTarget'), 0)) * 0.79) AS TotalExpectedNonPenaltyGoals
FROM 
    MatchTeamStats
WHERE 
    TeamId = 6

-- Combine the two
SELECT 
    TeamId,
	SUM(json_extract(StatsJson, '$.expectedGoals')) AS TotalExpectedGoals,
    SUM(json_extract(StatsJson, '$.expectedGoals') - (
	COALESCE(json_extract(StatsJson,'$.attPenGoal'), 0) + 
	COALESCE(json_extract(StatsJson, '$.attPenMiss'), 0) + 
	COALESCE(json_extract(StatsJson,'$.attPenTarget'), 0)) * 0.79) AS TotalExpectedNonPenaltyGoals
FROM 
    MatchTeamStats
--WHERE 
    --TeamId = 6
group by 
   teamid
   order by TotalExpectedNonPenaltyGoals desc

-- Get keys

SELECT
    TeamId,
    json_each.key AS JsonKey
FROM
    MatchTeamStats,
    json_each(StatsJson)
WHERE
    TeamId = 6 and MatchId =2561923;

Schema

SELECT sql FROM sqlite_master WHERE type = 'table';
PRAGMA table_info(your_table_name);
PRAGMA index_list(your_table_name);
PRAGMA foreign_key_list(your_table_name);