In SQLite the trigger can modify the table which it is defined on. The trigger will not be fired by the action which is performed by this trigger. I.e. if ON DELETE deletes another row then this secondary deletion will not fire this trigger again. But it will fire another trigger if another action is performed. If this secondary action should fire first trigger then it will be fired because this is not self-firing. For example, UPDATE fires ON UPDATE which performs DELETE which fires ON DELETE which performs UPDATE which causes ON UPDATE... and so on, until the chain stops due to the absence of the row to be altered by the trigger. So use such cross-firing with great caution. This may result in infinite loop or excess rows modifying (up to complete table clearing). whenever I delete a record, the trigger seems to do nothing You define per-statement trigger (there is no FOR EACH ROW) but the action looks like per-row one.

What you're looking for is called https://www.sqlite.org/lang_createindex.html and it will allow both of your goals to be met. Indexes save the data pre-sorted on the order of the column(s) defined for the index. They also (typically) use a B-Tree data structure to efficiently store the data so that the entire table doesn't have to be scanned when it's faster to just seek on the range of nodes in the tree that contain the data needed to serve your query. In SQLite, https://www.sqlite.org/lang_createtable.html is automatically created as a clustered index too. So you actually don't need to do anything additional. But if in your specific example, you cared about indexing a field that wasn't the primary key, then you'd want to define an index like such: CREATE INDEX IX_IndexName ON TableName (SomeOtherFieldNotInThePrimaryKey) For further information on indexing, please see https://www.sqlitetutorial.net/sqlite-index/ .

The issue here appears to come from a misunderstanding of the "excluded" keyword. The https://www.sqlite.org/lang_UPSERT.html states: To use the value that would have been inserted had the constraint not failed, add the special "excluded." table qualifier to the column name. The value that would have been inserted into fooID is of course excluded.fooID, not excluded.id as I have in the example above. I believe external.id refers to the row ID of the column, which in this case would have been 2 for the third row. So the final query we end up with is: INSERT INTO bar (key, fooID) SELECT 'key', foo.id FROM foo WHERE foo.name='three' ON CONFLICT (key) DO UPDATE SET fooID=excluded.fooID;

this is the closest I can get, but this query seems like it would have pretty poor performance SELECT tag_group.name AS tag_group, COUNT(media.id) AS media_count FROM media INNER JOIN media_tag ON media_id = media.id INNER JOIN tag ON tag_id = tag.id INNER JOIN tag_group ON tag_group_id = tag_group.id WHERE media.id IN ( SELECT id FROM media INNER JOIN media_tag ON media_id = media.id WHERE tag_id IN (1, 3, 5) GROUP BY tag_id HAVING COUNT(tag_id) >= 3 ) GROUP BY tag_group_id It does appear to return the data that I want to see, but wouldn't this cause a lot of extra loops? [edit] here is the query plan generated from the above statement. It appears that we duplicate work selecting from media using primary key, but that is the extent of duplicated work I see. Is this the best I can do? I would be curious if theres a smarter way to write my schemas/query. |--SEARCH TABLE media USING INTEGER PRIMARY KEY (rowid=?) |--LIST SUBQUERY 1 | |--SEARCH TABLE media_tag USING COVERING INDEX media_tag (tag_id=?) | `--SEARCH TABLE media USING INTEGER PRIMARY KEY (rowid=?) |--SEARCH TABLE media_tag USING COVERING INDEX sqlite_autoindex_media_tag_1 (media_id=?) |--SEARCH TABLE tag USING INTEGER PRIMARY KEY (rowid=?) |--SEARCH TABLE tag_group USING INTEGER PRIMARY KEY (rowid=?) `--USE TEMP B-TREE FOR GROUP BY

In theory what I want should look like this: | StudentID | StaffID | Phone_Model | Class | | --------- | ------- |------------ | ----- | | 1 | 1 | iPhone7 | S201 | 2 | 3 | iPhone11 | S203 Is there something missing from my SQL statement? You are missing relation between Staff and Student. You need a column StaffID int in the Student table. create table Staff ( StaffID int , Class varchar(10), TotalPhonesConfiscated int ); insert into Staff values (1,'S201',1), (2,'S202',0), (3,'S203',1); create table Student ( StudentID int , Phone_Model varchar(20), StaffID int ); insert into Student values (1,'iPhone7',1), (2,'Samsung',3); create table Phones ( StudentID int, StaffID int , Phone_Model varchar(20), Class varchar(10)); Then you can insert the data: INSERT INTO Phones ( StudentID, StaffID, Phone_Model, Class ) SELECT StudentID, Student.StaffID, Phone_Model, Class FROM Student INNER JOIN Staff on Student.StaffID=Staff.StaffID WHERE TotalPhonesConfiscated >= 1; select * from Phones; Would result on: Result: StudentID StaffID Phone_Model Class 1 1 iPhone7 S201 2 3 Samsung S203

I ended up using https://github.com/crazy-max/firefox-history-merger (2.12.0 at the time of this post), and it merged my slightly older firefox database with my current one. I don't remember what my old firefox version was, but I am on Firefox v96.0.3 at the time of this post.

-- first put all the timestamps, counting +1 for every "entrance" (start) -- and -1 for every "exit" (end) WITH c AS ( SELECT start AS dt, +1 AS counter FROM periods UNION ALL SELECT end AS dt, -1 AS counter FROM periods ), -- then calculate the running totals, going through the counters -- in order of the timestamps cc AS ( SELECT SUM(SUM(counter)) OVER (ORDER BY dt) AS running_total FROM cc GROUP BY dt ) -- then find the maximum of the running totals SELECT MAX(running_total) FROM cc;

This is a pretty classic https://www.red-gate.com/simple-talk/databases/sql-server/t-sql-programming-sql-server/divided-we-stand-the-sql-of-relational-division/ question. I would suggest you start by placing the input list into a temporary table, so that you can refer to it in a set-based fashion, although you can also do this as a CTE. You may want to index it also. CREATE TEMPORARY TABLE input AS SELECT NULL AS artist WHERE 0 UNION ALL SELECT * FROM (VALUES ('K/DA'), ('Seraphine') ) v; Then there are a number of relational division solutions. One standard one is this SELECT s.id, s.title FROM songs s JOIN songartists sa ON sa.song_id = s.id LEFT JOIN input i ON i.artist = sa.artist GROUP BY s.id, s.title HAVING COUNT(*) = COUNT(i.artist) -- no missing matches AND COUNT(*) = (SELECT COUNT(*) FROM input); -- no extras https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=18c65426c099ed8c6b0b46f16a63e16a What this does is: Take all songs with their songartists Left join the input list to it Group by the song Ensure that the number of rows (artists) is equal to the number of matches to the input (there are no rows which have no matches) And that the total number of rows is also equal to the total number of input rows (there are no extra rows)

Something like this should do the trick (note the aliases, and feel free to adjust the casing to your preferred format): SELECT Posts.ID, SUM(CASE WHEN Likes.ID IS NULL THEN 0 ELSE 1 END) AS TotalLikesCount, SubmitterProfiles.USERNAME, MAX(CASE WHEN Likes.PROFILE_ID = 98765 THEN 1 ELSE 0 END) AS DidRequestingUserLikeThisPost FROM USERPOSTS AS Posts INNER JOIN USERPROFILES AS SubmitterProfiles -- Inner Join should since a Post must be submitted by a User Posts.PROFILE_ID = SubmitterProfiles.ID LEFT JOIN USERVOTES AS Likes -- Outer (Left) Join because not all Posts necessarily have Likes ON Posts.ID = Likes.POST_ID LEFT JOIN USERPROFILES AS VoterProfiles -- Need to continue with an Outer (Left) Join here because we're pulling the profile of the User who liked the Post (if there is a like) ON Likes.PROFILE_ID = VoterProfiles.ID WHERE Posts.THREAD_ID = 12345 GROUP BY Posts.ID, SubmitterProfiles.USERNAME Please read the comments for more information, but basically this starts with your USERPOSTS table and inner joins your USERPROFILES table (which should always have a many-to-one relationship), then outer joins the remaining information (because some posts may not have likes). Since the relationship between USERPOSTS and USERVOTES is one-to-many (when there are likes) the results set becomes a single row per like, so we need to group up by the ID of the post so we can count how many likes (rows) there are which I'm doing using the SUM() function with a CASE statement to handle NULL cases (when there's no likes for a given post). We also have to group by the USERPROFILES.USERNAME so that we can SELECT it and this doesn't change the cardinality of our results, since every post can only be submitted by a single user.

create an awk script, eg. /tmp/Max888.awk, containing BEGIN { FS="," C["crate_id"] = 1 C["owner_id"] = 2 C["created_at"] = 3 C["created_by"] = 4 C["owner_kind"] = 5 } NR == 1 { # determine the New Order for (N=1;N 1 { OFS="," # print $1,$2,$3,$4,$5 print $NCO[NO[1]],$NCO[NO[2]],$NCO[NO[3]],$NCO[NO[4]],$NCO[NO[5]] } If your CSV data is contained in the file /tmp/test.dat, and your database in /tmp/test.sq3, you can run sqlite3 -csv /tmp/test.sq3 '.import "|awk -f /tmp/Max888.awk /tmp/test.dat" crate_owners'

(I am saying almost the same as what you say, but I am speaking more generally about the execution of a SELECT.) Without any subqueries, the clauses of a SELECT are required to be in the order that they will be looked at. (And you cannot shuffle the order.) The exception is that the columns in the SELECT are not used until at least after the FROM. A subquery can be "correlated", meaning it references columns in the outer query, or "uncorrelated", as with both of your subqueries. For "correlated", the subquery must be run repeatedly. The existence of an "Aggregate" (the first COUNT(*)) without a GROUP BY, says to "go through the entire table (FROM) to tally up the value. A subquery that is a "constant" (both of your subqueries) will be treated as a separate SELECT to be performed before the main SELECT. (It is indeterminate as to which of the 2 comes first -- as it should not matter.) After that, you have something like: SELECT COUNT(*), 1234 FROM recent_grads WHERE ShareWomen > 25.678; Does that agree with what you are seeing?

Find id+filename which have passwords Find id+filename which have usernames Inner join with the original table to filter out those rows that have no corresponding values in passwordss and usernames Something along these lines: with passwords as ( select filename, id from files where name like '%PASS%' group by filename, id ), usernames as ( select filename, id from files where name like '%USER%' group by filename, id ) select files.* from files inner join passwords using(filename, id) inner join usernames using(filename, id);

https://sqlite.org/cli.html says: Most of the time, sqlite3 just reads lines of input and passes them on to the SQLite library for execution. But input lines that begin with a dot (".") are intercepted and interpreted by the sqlite3 program itself. These "dot commands" are typically used to change the output format of queries, or to execute certain prepackaged query statements. This means if you are NOT using the CLI tool, for example, if you are using a query API, then those dot commands are not recognized. In other words, a dot command like .mode column is only used when writing queries in the sqlite CLI. If you want to use Python to do something similar, you would have to print the row yourself in a manner that aligns to columns. There are a couple of solutions for pretty-printing SQL query results in answers to this question: https://stackoverflow.com/questions/10865483/print-results-in-mysql-format-with-python

CREATE TRIGGER IF NOT EXISTS log_logout AFTER UPDATE OF logout ON session_log BEGIN UPDATE session_log SET duration = (JULIANDAY(logout) - JULIANDAY(login)) * 24 * 60 --minutes WHERE id IN (SELECT id FROM session_log ORDER BY id DESC LIMIT 1); END;

with cte1 as ( select id, value, lag(value,1,0) over w as prev1, lag(value,2,0) over w as prev2 from tbl_data window w as (order by id) ), cte2 as ( select id from cte1 where value > 10 and prev1 <= 10 and prev2 <= 10 ) update tbl_data set result = 1 where id in (select id from cte2);

The implementation may be: -- books table CREATE TABLE Ebook (ebook_id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(255) UNIQUE); -- tags table CREATE TABLE Tags (tag_id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(255) UNIQUE); -- adjacency table PRAGMA foreign_keys = ON; CREATE TABLE TagsToEbooks (ebook_id INT, tag_id INT, PRIMARY KEY (ebook_id, tag_id), FOREIGN KEY (ebook_id) REFERENCES Ebook (ebook_id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (tag_id) REFERENCES Tags (tag_id) ON DELETE CASCADE ON UPDATE CASCADE); -- Trigger which will delete unused tags after book deletion CREATE TRIGGER delete_unused_tags AFTER DELETE ON Ebook BEGIN DELETE FROM Tags WHERE NOT EXISTS ( SELECT NULL FROM TagsToEbooks WHERE Tags.tag_id = TagsToEbooks.tag_id); END; https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=3240437678fe642477e75f07a30affb6

Will there ever be another entity that extends the program entity (other than executable)? If not, I don't see a lot of purpose to it, and would just store the name field in the executable table instead of having a separate table. Anyway, after you've recursively built out your hierarchy of nodes, you can just join to the tables you want to pull data for accordingly. For example if you want all executables with their names you just join to it in your final SELECT like so: WITH RECURSIVE directoryTree (id, name, type, parentId, level, protected, siblingOrder, isDesktop) AS ( SELECT n.id, n.name, n.type, n.parentId, -1, n.protected, n.siblingOrder, 1 FROM node n WHERE n.name="Desktop" AND n.protected=1 UNION ALL SELECT n.id, n.name, n.type, n.parentId, level + 1, n.protected, n.siblingOrder, 0 FROM node n JOIN directoryTree p ON p.id = n.parentId ) SELECT n.id, n.name, n.type, n.parentId, n.level, n.protected, n.siblingOrder, n.isDesktop, e.id AS executableId, p.name AS programName FROM directoryTree n INNER JOIN executable e ON n.id = e.nodeId INNER JOIN program p ON e.programId = p.id ORDER BY n.level;

What you're describing is called a connection to a lot of people. In SQL, this is being implemented through an intermediate (between the two of you) table with its own key (not necessarily) and a field reference to the first table, and a reference to the second table.CREATE TABLE film( id INTEGER PRIMARY KEY NOT NULL, name INTEGER NOT NULL ); CREATE TABLE client( id INTEGER PRIMARY KEY NOT NULL, client_name TEXT NOT NULL ); CREATE TABLE film_client( film_id INTEGER, client_id INTEGER, FOREIGN KEY(film_id) REFERENCES film(id) ON DELETE CASCADE, FOREIGN KEY(client_id) REFERENCES client(id) ON DELETE CASCADE ); Films, customers, communication between them are then insertedINSERT INTO film (name) VALUES ("Shrek"); INSERT INTO client (client_name) VALUES ("Ivan"); INSERT INTO film_client VALUES ((SELECT id FROM film WHERE name LIKE "Shrek"), (SELECT id FROM client WHERE client_name LIKE "Ivan"));

SELECT g.*,COUNT(c._id) as _count FROM groups g LEFT JOIN playlists p LEFT JOIN channels c ON p._active = 1 AND c._groups LIKE '%' || g._id || ',%' ON 1 GROUP BY g._id ORDER BY g._name

select f14, printf("%011d",sum(replace(f14,'+-','-'))) from Table where f1=602 group by f14; https://www.sqlite.org/lang_corefunc.html#printf The printf(FORMAT,...) SQL function works like the sqlite3_mprintf() C-language function and the printf() function from the standard C library. The first argument is a format string that specifies how to construct the output string using values taken from subsequent arguments. If the FORMAT argument is missing or NULL then the result is NULL. The %n format is silently ignored and does not consume an argument. The %p format is an alias for %X. The %z format is interchangeable with %s. If there are too few arguments in the argument list, missing arguments are assumed to have a NULL value, which is translated into 0 or 0.0 for numeric formats or an empty string for %s.