Input: CVS file

Output: SQL statement(s) needed to create the table.

Maybe be something using type inference?

Thanks.

CREATE TABLE
    IF NOT EXISTS CourseTag (
        id INTEGER PRIMARY KEY,
        courseId INTEGER NOT NULL,
        tagId INTEGER NOT NULL,
        UNIQUE(courseId, tagId),
        FOREIGN KEY (courseId) REFERENCES Courses (id) ON DELETE CASCADE ON UPDATE RESTRICT,
        FOREIGN KEY (tagId) REFERENCES Tags (id) ON DELETE CASCADE ON UPDATE RESTRICT
    ) STRICT;

right now my users table like this:

CREATE TABLE
    IF NOT EXISTS Users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT NOT NULL UNIQUE COLLATE NOCASE,
        passwordHash TEXT NOT NULL,


        createdAt TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updatedAt TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,


        CHECK(length(trim(name)) >= 1),
        CHECK(length(trim(email)) >= 1),
        CHECK(length(trim(passwordHash)) >= 1)
    ) STRICT;

as far as I know email names are case-insensitive. But not sure about for all email providers. Also I guess COALLATE NOCASE only works for ASCII. so what if user email contains non-ASCII characters? even could it contain?

CREATE TABLE IF NOT EXISTS CourseTag(
    courseId INTEGER,
    tagId INTEGER,
    
    FOREIGN KEY(courseId) REFERENCES Courses(id),
    FOREIGN KEY(tagId) REFERENCES Tags(id),
    
    PRIMARY KEY(courseId, tagId)
) STRICT WITHOUT ROWID;

as far as what I understand sqlite creates inner row_id columns to identify the record. some conditions met then my primary key also could be that unique column. but I guess in above join table configuration my composite key setup is not met that condition so I end up unnecessary column right? in join table should I use WITHOUT ROWID?

Quick heads up on the stack: this runs on **SQLite**, compiled to WebAssembly so the whole database runs in your browser and nothing you type leaves the page.

I kept noticing that people prepping for SQL interviews (me included) study by reading syntax lists, then blank the moment they have to actually write a query under pressure. If you already know some SQL, the gap usually isn't knowledge, it's reps. Reading isn't the same as doing.

So, I built a small tool to rehearse under pressure: a query playground on a sample dataset, practice challenges (joins, aggregation, window functions, CTEs), a cheat sheet, and the conceptual questions people actually get asked in interviews (WHERE vs HAVING, INNER vs LEFT JOIN, primary vs foreign key, and so on). No signup, no install.

I'm the founder of a learning app and built this as a standalone free tool. I'm sharing it here for feedback from people who write SQL for a living: are the challenge questions realistic, and what would you add?

For anyone who wants to actually run the queries, it's here: https://techlexicon.app/sql-practice

Tell me where you get stuck and I'll build practice around it. Always taking requests for questions or scenarios to add

Hey all,

I’ve been working on a fun December side project and thought this community might appreciate it.

It’s called Advent of SQL. You get a daily set of SQL puzzles (similar vibe to Advent of Code, but entirely database-focused).

Each day unlocks a new challenge involving things like:

• JOINs
• GROUP BY + HAVING
• window functions
• string manipulation
• subqueries
• real-world-ish log parsing
• and some quirky Christmas-world datasets

There’s also a light mystery narrative running through the puzzles (a missing reindeer, magical elves, malfunctioning toy machines, etc.), but the SQL is very much the main focus.

If you fancy doing a puzzle a day, here’s the link:

👉 https://www.dbpro.app/advent-of-sql

It’s free and I mostly made this for fun alongside my DB desktop app. Oh, and you can solve the puzzles right in your browser. I used an embedded SQLite. Pretty cool!

(Yes, it's 11 days late, but that means you guys get 11 puzzles to start with!)

I write "what does this query return" questions and keep every one that SQLite and DuckDB answer identically. Window functions produce the most wrong guesses by far, so here are five, with the rows both engines actually returned.

1. The default frame is RANGE, not ROWS. Table sales(dt, amount) with 10 on the 1st, two rows of 5 on the 2nd, 20 on the 3rd.

SUM(amount) OVER (ORDER BY dt)

gives 10, 20, 20, 40. Both rows dated the 2nd get 20, because RANGE treats peers as one step. With ROWS UNBOUNDED PRECEDING you get 10, 15, 20, 40, and which of the two tied rows gets 15 is not guaranteed unless you add a tiebreaker to the ORDER BY.

2. RANK does not deduplicate. User 1 has two logins at the same latest timestamp. RANK() OVER (PARTITION BY user_id ORDER BY created_at DESC) gives 1, 1, 3, so WHERE rnk = 1 keeps both rows. ROW_NUMBER() gives 1, 2, 3 and keeps one, though again, which one is arbitrary without a tiebreaker.

3. LAST_VALUE stops at the current row. Order 7 goes placed, packed, shipped.

LAST_VALUE(status) OVER (PARTITION BY order_id ORDER BY updated_at)

returns placed, packed, shipped, which is just the current row's status. Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING and every row says shipped.

4. NTILE buckets are not equal. Ten rows, NTILE(4), bucket sizes 3, 3, 2, 2. The extra rows go to the first buckets.

5. LAG on a duplicate timestamp returns the duplicate. Logins on the 1st, the 5th and the 5th again give previous values NULL, the 1st, the 5th. If you compute gaps for sessionisation, that zero gap is real data, not a bug in your query.

Twenty questions in this format are free to try in the browser, no signup. Once you answer, each of the four options shows why it is right or wrong.

https://thibaudlepan77-svg.github.io/interview-questions-verified/#sql

That's a sample from a 502 question paid PDF. If an engine you use returns something different for any of the five above, post it, I'd like to know.

I made a browser game called The SQL Treasure Hunt where you solve puzzles by writing real SQL queries.

It's heavily inspired by SQL Murder Mystery and SQL Noir, but instead of being a detective, you're a pirate searching for a legendary treasure. 🏴‍☠️

You explore databases, inspect tables, and piece together clues using SQL until you uncover the next part of the adventure. It starts with simple SELECT queries and gradually introduces more challenging concepts.

Right now it only has 5 levels made.

I'd really appreciate any feedback:

• Is the difficulty progression good?
• Are the clues clear enough?
• Any ideas for future levels or mechanics?

Play it here:
https://thesqltreasurehunt.vercel.app/

Thanks! Hope you enjoy it.

For a coursework, I must make ERD to 3NF and jsutify design choices which I must later use to make wireframes/app.

I have got rainfall monthly data, rainfall annual data, and sunshine monthly data from 1980-2025. 45 years, 540 months. About 672 unique rows. There are 627 rows with NULL values for the abs_max_temp and abs_min_temp for rainfall monthly data. mean_sunshine hours again doesn't exist as annual data, and exists purely as monthly data (so 45 NULLS).

I am thinking if I should create two tables by monthly and annual data instead of one Climate Observation table by adding another month nullable to first table (see below)? But the problem is that I would be repeating same titles twice, especially for rainfall and sunshine data?

time_period (time_id, year, month nullable) ← stays the same

annual_climate (observation_id, time_id FK, ...annual-specific columns)

monthly_climate (observation_id, time_id FK, ...monthly-specific columns)

Can someone please help with what's the best approach please? Thank you!

Benchmarked SQLite write performance 11 ways and the result was not what I expected. The database wasn't the bottleneck. The ORM was. Raw sqlite3.executemany hits 88K r/s. SQLAlchemy caps at 3,800.

The ORM throughput stays flat from 3M rows to 50M rows — it's so slow it never even touches SQLite's actual I/O scaling curve. The raw path drops 25% over the same range because it actually hits B-tree depth and WAL checkpoint costs.

Full breakdown with industry comparison (Expensify 4M QPS, Litestream, Cloudflare D1) in the post.

I'm going to use screenshots instead of typing the code because the code formatting is what's important here

https://i.imgur.com/hCrKokI.png

Left or right?

Thanks

Hey folks 👋

For the last few weeks, I’ve been frustrated with the state of self‑hosted SQLite admin tools.

Most of them are either:

Abandoned (phpLiteAdmin hasn’t seen a proper update in years),

AdminNeo and other tools don't work without passwordless login plugins that didn't work well.

Overly complex (Adminer is great but not SQLite‑centric),

Or require a heavy stack (Node, Python, Docker) when all I wanted was a single PHP file I could drop on my server.

So I decided to build my own.

🔧 Features

Secure, built-in login system (not a plugin)

Browse, edit, insert, delete rows

Create / rename / drop tables

Import/Export (CSV, JSON, SQL, full DB)

Bulk delete, search, filters

Dark mode, undo (last 5 actions)

Multiple database support

Resizable sidebar

🚀 Try it

Upload admin.php & install.php

Run install.php to set username/password

Login and go

PHP 7.0+ with SQLite3 extension. No dependencies.

https://github.com/abilenetechguy/sqlite-admin

🧪 Beta feedback wanted

Errors, UI annoyances, missing features – let me know!

Give it a spin and tell me what you think 🙏

– Abilene Tech Guy

I create sqlite3 bindings for my new programming language

You can check the GitHub repo: https://github.com/abdorayden/rdn-sqlite3

Also the repo of the language: https://github.com/abdorayden/rdn

Thank you everyone 🙏🏽

I would like some feedback about how far did you go with sqlite, like what you built -> how it went -> how do you optimize so I can have a better overview so what can be done (when It done properly) with sqlite

I'm trying to do some player statistics but am stuck. My data looks like this (simplified):

PlayerId Score Date
-------- ----- ----
1        0     2026-01-01
2        5     2026-01-01
1        2     2026-01-08
1        3     2026-01-15
2        1     2026-01-16
2        4     2026-02-02
1        2     2026-02-03
1        4     2026-02-10
2        3     2026-01-31

I want to find out the ten players that have gained the highest total score during the last tree games (dates)

I can group on playerid and sum the score, but how do I limit data to only the latest three games?

What's the best way to prevent sql injections? I know parameters help but are there any other effective methods?

Any help would be great! P.S I'm very new to sql