Questions tagged [sqlite]

A popular open-source embeddable (i.e. non client-server) RDBMS implemented as a C library.

Filter by
Sorted by
Tagged with
-2 votes
0 answers
13 views

How to run a query through Widows command interface

I have installed Precompiled Binaries for Windows on my laptop. Next, created a folder in C drive and pasted the sqlite3 application. Next, trying to run a query using Windows command interface. ...
user avatar
-3 votes
1 answer
21 views

How to transfer the output of an SQLite query into a spreadsheet (Excel) [closed]

After running the query, I am getting 4 rows of output. How to transfer the same into an Excel file. I know I can do this manually, but there should be a method. Unable to figure out how to access ...
user avatar
0 votes
0 answers
22 views

SQL Aggregation by Hour

I have an SQLite database (version 3.31.1) for my electricity usage and generation. It has over 100,000 rows, and about 600 days. The "StartTime" field contains the start time of the bucket ...
user avatar
0 votes
1 answer
9 views

Correcting insertion of new data

I have a fairly simple schema: CREATE TABLE "mysensor" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "date" INTEGER NOT NULL, "sensor_data" INTEGER NOT ...
user avatar
  • 113
-1 votes
0 answers
16 views

SQl exercises challenge

I have these exercises below given the three tables listed Books Id (int) Title (text) Price (int) Authors Id (int) Name (text ) BookAuthors BookId (int) AuthorId (int) WRITE A SELECT STATEMENT ...
user avatar
0 votes
1 answer
12 views

Sqlite spec: 4.2. Type Conversions Prior To Comparison

Based on Sqlite docs: https://www.sqlite.org/datatype3.html#type_conversions_prior_to_comparison, especially this statement: If one operand has INTEGER, REAL or NUMERIC affinity and the other operand ...
user avatar
  • 394
0 votes
1 answer
28 views

Recursive querry over 3 Tables

I have the following table structure create table folder ( id integer constraint folder_pk primary key, path TEXT ); create index folder_path_uindex on folder (path);...
user avatar
0 votes
0 answers
9 views

Check a record exists already and stop the loop in a recursive query

I'm working on a SQLite table saving class inherit information, I need to get all ascendant classes or descendant classes in the inherit chain. I use recursive query to get these two fields of all ...
user avatar
0 votes
0 answers
9 views

Is there any way to make this query spatialite more efficient?

I have two tables, The "Sitios" table which has point type geometries. And the table AdministrativeUnit that has the multi-polygons that conform a map, with its municipalities, provinces, ...
user avatar
6 votes
1 answer
732 views

Unused columns in WHERE clause

I found this query in our codebase: DELETE FROM "Foo" WHERE ("Foo"."Id", "Foo"."CreatedAt") IN (SELECT "f"."Id", "f".&...
user avatar
  • 175
0 votes
1 answer
29 views

Is it a bad idea to have a table of deleted UUID's for synchronisation purposes

I have a webapplication and a corresponding mobile application. User can add on the webapp and live DB is auto updated. App can be used offline and add insert and update into local sql lite. Any ...
user avatar
0 votes
1 answer
10 views

Select rows and union two columns to one except certain value

I have a three column table CREATE TABLE "colls" ( "w1" TEXT, "w2" TEXT, "coocc_freq" INTEGER ) w1 w2 coocc_freq s o 2 o d 10 a q 8 w ...
user avatar
  • 3
0 votes
0 answers
30 views

SQLite database Upgrade Schema without loosing data

I have a small C# WindowsForms application. It uses SQLite DB in the client PC. I started with a Schema DB and with the time I added more tables and columns. To my local DB(not the client) I want to ...
user avatar
1 vote
1 answer
60 views

Optimize performance of nested GROUP BY

I have a query on the table random_numbers with a GROUP BY nested inside another GROUP BY: SELECT a, min(x_max) AS x_minmax FROM ( SELECT a, b, max(x) AS x_max ...
user avatar
  • 31
0 votes
0 answers
16 views

Rollback sqlite3 -journal

Is it possible to rollback a committed transaction given that I still have the -journal file containing said transaction? Hopefully using the sqlite3 CLI
user avatar
  • 111
0 votes
0 answers
15 views

Database as IPC on ARM proccesor performance

I have multiple processes on my ARM processor which want to communicate with each other. I know there are other mechanisms for IPC except database, but currently, I chose database. My solution uses ...
user avatar
  • 131
2 votes
1 answer
54 views

SQLite: Can you use a DELETE trigger to delete other records in the same table?

In this specific case I'm using a SQLite database, but I believe this to be more of a conceptual question with practical implications. I have a nested-set model in my database and I would like to ...
user avatar
5 votes
1 answer
120 views

Optimize a search between two values

I have a table that has an integer timestamp as primary key. Almost every query on this table will be of the pattern SELECT * FROM table WHERE timestamp BETWEEN x AND y These ranges are usually ...
user avatar
  • 175
0 votes
1 answer
144 views

Sqlite upsert from select with conflict does not always update row

Assuming a database built with the following: PRAGMA foreign_keys = ON; CREATE TABLE foo ( id INTEGER PRIMARY KEY NOT NULL, name TEXT NOT NULL, UNIQUE (name) ); INSERT INTO foo (name) VALUES ...
user avatar
1 vote
1 answer
18 views

query relational tag data, grouping by a different metric

Suppose I have the following schema: CREATE TABLE tag_group ( id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL, ); CREATE TABLE tag ( id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL, ...
user avatar
  • 111
1 vote
1 answer
23 views

How to insert various records from multiple tables with a condition

I am creating a relational database and I have 2 tables currently ( Staff and Student) Staff Looks Like this StaffID Class TotalPhonesConfiscated 1 S201 1 2 S202 0 3 S203 1 Student Looks like ...
user avatar
0 votes
1 answer
51 views

Combine tables with different foreign key values for same data (firefox)

I am combining my history in firefox from two computers with the following commands: ATTACH 'filename' AS toMerge INSERT or IGNORE INTO moz_origins SELECT * FROM toMerge.moz_origins; INSERT or IGNORE ...
user avatar
1 vote
1 answer
27 views

Finding the highest number of intersections in between intervals?

In SQLite, I have a fairly simple data table: Periods(Start, End) I'm storing periods with start and end date (stored as integer in seconds), and I'm looking for the information of the highest number ...
user avatar
  • 113
0 votes
0 answers
22 views

How to specify default CREATE TABLE values just once?

Is there any way I can just say once that the default type should be TEXT, and the default value should be '' for all CREATE TABLEs? Currently I must do: CREATE TABLE "t" (dbm TEXT DEFAULT ''...
user avatar
2 votes
2 answers
169 views

How to find the rows that have exactly matching relationships in another table

SQL beginner here, so bear with me please. Let's say I have a simplified table songs looking like this id title 1 Villain 2 More 3 More (Laszlo Remix) I also have a table songartists: song_id ...
user avatar
  • 175
-1 votes
1 answer
57 views

How to query likes and user details for posts?

I'm pretty new to the world of databases and SQL. I'm trying to build a system for users to submit posts and like posts. I've been looking at a few similar questions on this site, reading about third ...
user avatar
  • 101
0 votes
0 answers
23 views

Proceeds JOIN only if condition met on the Owner table

I have many hasOne relationships like this: components component_headings component_images Here one Component can only have one hasOne to any of [headings, images]. Logically when a Component.type =...
user avatar
  • 101
0 votes
2 answers
169 views

Sqlite3 import csv column ordering

I have a situation where I have CSV files with column names in the first row, which perfectly match the tables in my SQLite3 db, except they are in a different order. By default, with .import table1....
user avatar
  • 101
0 votes
1 answer
37 views

Confused about the order of execution SQL

I have this SQL query can anyone help me with the order of execution like which clause is run first and then last: SELECT COUNT(*), (SELECT COUNT(*) FROM recent_grads ) FROM ...
user avatar
1 vote
2 answers
46 views

Only SELECT rows from table when two rows from the same table fulfill the condition as a whole under the same ID

Title is a bit weird, but I am not quite sure how to formulate this problem quite right. Maybe this is why I cannot find the solution...? I am currently crawling through a SQLite database which has ...
user avatar
  • 13
0 votes
0 answers
10 views

Can scalars in one table be used to define a column in another table?

My gradebook is derived from a csv file where every row represents one student's performance on an assignment. I am using sqlite to parse this data. $ cat gradebook.csv student,assignment,score,...
user avatar
0 votes
1 answer
46 views

formating dot commands in sqlite3

My first SQL database im using sqlite3 in idle on linux. Everything is working fine but I want my table to display in an easier to read format in the terminal. been reading about dot commands eg .mode ...
user avatar
0 votes
0 answers
18 views

What's the practical difference between these two triggers?

Say I have a table with a column to inform whether its row is a default of the application or not: CREATE TABLE IF NOT EXISTS "mytypes" ( "idx_type" INTEGER NOT NULL, &...
user avatar
0 votes
0 answers
16 views

Can the primary key of a table with many columns be retroactively set in sqlite?

I have a table with hundreds of columns that I have imported from a csv file using sqlite3. One of the columns is called sid, and I would like this column to be the primary key of my table. Is this ...
user avatar
0 votes
1 answer
21 views

How do I record an application session duration in SQLite?

Objective: Log the session duration of the user on the application. I have a session_log table: CREATE TABLE IF NOT EXISTS "session_log" ( "id" INTEGER PRIMARY KEY, ...
user avatar
0 votes
0 answers
13 views

Is there an equivalent to EVENTDATA in SQLite?

I am looking for ways to create a log table in my SQLite database, where I'm able to track changes that have been made. One fellow has beaten me to it here, if someone is willing to give it a go. ...
user avatar
1 vote
0 answers
44 views

How to log changed table fields in SQLite?

I have a table that looks like this CREATE TABLE IF NOT EXISTS "ebooks" ( "id" INTEGER PRIMARY KEY, "name" TEXT NOT NULL, "author"...
user avatar
0 votes
0 answers
36 views

Howto rephrase this INNER JOIN to be faster? I only need one row!

Folks, help me out please. I'm using SQLite (v3.35.5), and I have a fairly big table (with 800k rows). I need to join this table to itself, for a special purpose. Here is the table definition: CREATE ...
user avatar
  • 113
0 votes
1 answer
60 views

Howto use window functions in update statement?

I have an easy SQL schema (SQLite), with this definition: tbl_data (`id` INTEGER, `value` INTEGER, `result` INTEGER); It has a few records (sample): And I want to UPDATE this table to set result=1 ...
user avatar
  • 113
0 votes
1 answer
74 views

How to implement insert, update, delete with a many-to-many relationship for tags?

I am creating an application to manage some ebooks and I would like to have tagging functionality. The best db design I have found is to be the many-to-many relationship. I know how to set up my ...
user avatar
0 votes
1 answer
34 views

How can I query my "subtype" tables?

I am trying to model inheritance in my database. The purpose of the database is to use it to emulate a directory structure in a desktop GUI. The directory structure is made up of nodes, which can ...
user avatar
  • 103
0 votes
1 answer
18 views

How can I prevent a rows with a particular attribute from being deleted in a cascade?

I have the following table CREATE TABLE IF NOT EXISTS node ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, type TEXT CHECK(type IN ("DIRECTORY", "EXECUTABLE", "DOCUMENT&...
user avatar
  • 103
0 votes
1 answer
30 views

How can I update the value of a column for two rows that have a unique constraint in SQLite?

I have the following table CREATE TABLE IF NOT EXISTS node ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, type TEXT CHECK(type IN ("DIRECTORY", "EXECUTABLE", "DOCUMENT&...
user avatar
  • 103
0 votes
0 answers
62 views

Is this database correctly structured?

I am in the process of converting a bus timetable to a database structure. I am building a free mobile app for a specific itinerary in my country that will: let users know when their next bus is due ...
user avatar
  • 101
0 votes
1 answer
33 views

Random value duplicated, cannot select N random pairings, subquery not refreshing - sqlite

The Problem Using SQLite v3.35.4 and v3.36.0 I have a first_name table and a surname table that have a list of common names. I want to produce N number of pairings into a new table. I wrote this ...
user avatar
0 votes
0 answers
20 views

In SQLite, is there a limit for values in IN () statement

I am updating a number of rows in an SQLite database, currently one-by-one. With the (unique) values indexed, this is acceptably fast. Currently the number of rows are up to 10.000 approx, but could ...
user avatar
  • 101
1 vote
2 answers
87 views

SQLite return rows in SELECT IN order

Is it possible to return rows in the same order as passed in the IN clause, e.g SELECT * FROM some_table WHERE id IN (5, 7, 1); and the rows are returned as: id | value -------------------- 5 | ...
user avatar
  • 161
-1 votes
1 answer
28 views

sum with combination

I reproduce the table in image and result I expect. So, I like sum for each item withe the sum of related item. But some item are null or join to himself. I think some array can help me but I don't ...
user avatar
  • 101
0 votes
0 answers
50 views

Should I create separate tables for things like favorites, bookmarks?

I am using Python with praw and pyside6 to build a desktop application that uses the reddit api to get posts. My main goal is to be able to favorite and bookmark the posts I want. At first, I used a ...
user avatar
2 votes
1 answer
176 views

SQLite: concatenate multiple columns across multiple tables

I’d like to store struct-like data into a SQLite 3 database. Given is the following (sample) schema: CREATE TABLE a( a_id INTEGER PRIMARY KEY, name TEXT ); CREATE TABLE b( ...
user avatar

1
2 3 4 5
13