Questions tagged [sqlite]
A popular open-source embeddable (i.e. non client-server) RDBMS implemented as a C library.
638
questions
-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.
...
-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 ...
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 ...
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 ...
- 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 ...
- 1
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 ...
- 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);...
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 ...
- 1
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, ...
- 1
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".&...
- 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 ...
- 21
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
...
- 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 ...
- 13
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
...
- 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
- 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 ...
- 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 ...
- 123
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 ...
- 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 ...
- 101
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,
...
- 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 ...
- 13
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 ...
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 ...
- 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 ''...
- 101
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
...
- 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 ...
- 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 =...
- 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....
- 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 ...
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 ...
- 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,...
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 ...
- 1
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,
&...
- 123
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 ...
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,
...
- 25
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.
...
- 123
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"...
- 25
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 ...
- 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 ...
- 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 ...
- 25
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 ...
- 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&...
- 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&...
- 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 ...
- 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 ...
- 105
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 ...
- 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 | ...
- 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 ...
- 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 ...
- 171
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(
...
- 121