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
-1
votes
1answer
19 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 ...
0
votes
0answers
22 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 ...
1
vote
1answer
27 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( ...
0
votes
2answers
30 views

Finding the minimum of a column depending the value of a second column

I have (on sqlite) a table with the following structure : id tile_id creation_year creation_day 1 1 2020 150 2 1 2020 132 3 2 2021 13 4 2 2020 15 5 3 2021 110 6 3 2021 18 7 3 2021 150 How can ...
0
votes
0answers
24 views

why are large sqlite transactions slow?

So in the process of writing a migration wrapped in a transaction for a local web app, I realized that the migration appears to perform inserts very fast when the transaction has a small number of ...
1
vote
1answer
36 views

Is there any way to join the same two tables many times easily?

I have two tables named "Player" and "Match". In "Player", There are two columns: Player.id and Player.name and in the "Match" table I have many columns related ...
0
votes
0answers
13 views

SQLite Calculating percentage with last week values

I have the following table transactions I need to calculate the percentage of growth for total transaction per day with past same day last week A simple way for my transactions table structure --------...
0
votes
1answer
41 views

How to design & implement folders and subfolders (infinite) in my database?

So I am building a desktop application using Qt, C++, and SQLite. I want the user to be able to create as many folders as he likes, and those folders can have "notes" (its a note-taking app) ...
1
vote
0answers
11 views

Can sqlite-utils convert function select two columns for conversion?

I'm using sqlite-utils to load a very large CSV into sqlite for later use in Datasette. In the CSV there are two columns, likes and dislikes, which I would like to use to create a third column quality-...
2
votes
0answers
19 views

App Crash due to Sqlite database Locked Exception

I am having an Android app with read only local sqlite database. Also I have use Sqlite Asset Manager for the local database. import android.content.Context; import com.readystatesoftware.sqliteasset....
0
votes
0answers
17 views

Is it possible to have a custom constrain in sqlite?

We store the user-id, category and few other misc pieces of information in a table. One of the categories, let's say 10th category is special and there should be only 1 record per user-id with ...
1
vote
1answer
31 views

return percentage per pair of items

I'm using SQLite and I have the following table: and I have 2 goals: The first is to return the IATA code of the airline that has the most bookings in first class. I thought of this, but it doesn't ...
0
votes
1answer
12 views

Position of entry in sqlite ordered table

I'm attempting to order a table and return the position of a row in that ordered table. Just for context, I'm developing a leveling system for a discord bot and trying to implement a rank command. The ...
3
votes
2answers
37 views

SQL query 2 tables using String without intersect

I am new to SQL and have been using SQLite I am wondering if it is possible to conduct an Intersect or by doing some kind of join instead? I am trying to search 2 tables to find "What employees ...
0
votes
0answers
73 views

Created an SQLite file but Still getting Error: file is not a database

Hello I am practising and learning SQLite, I created a SQLite file manually on VSCODE I call the file project_1.sqlite but when I try run the command sqlite3 project_1.sqlite i get $ sqlite3 project_1....
0
votes
0answers
42 views

Update table column without losing data when releasing a new version of software

I just linked my application with a database using sqlite. Everything worked fine. And I realeased a version of my app. So I find myself in need of changing a table attribute type. The sql statement I ...
1
vote
0answers
21 views

Creating the right index/es for querying views in SQL(ite3)

I have a python script that works by first creating some tables in a SQLite3 DB from lots of text files, then creating some views by joining the tables, and finally querying one of the views and ...
1
vote
0answers
56 views

SQLite, Sort by Index, faster without index

I'm just reading this excellent guide to relational database query planners and indexes on SQLite site. In section 2.2. Sort By Index it says the following about sorting by index: But does this ...
0
votes
0answers
27 views

Is SQLite Foreign Keys system inverted?

I think I'm misunderstanding the sqlite FK system (or maybe the whole SQL FK system at all ?) I have a table DOMAINE : CREATE TABLE "DOMAINE" ( "DOM_DOMAINE" TEXT, "...
0
votes
1answer
71 views

Randomly generate data under conditions for sqlite

As part of creating a machine learning exercise, I have an sqlite database that looks like this: The "Pizza", "DateDimension" and "Customer" tables are already populated,...
1
vote
1answer
37 views

Order a nested table with path index according to list_order in SQLite

I have a SQL table with this nested structure: CREATE TABLE items (`id` INTEGER, `item_name` VARCHAR(6), `parent_id` INTEGER, `list_order` INTEGER); My SQLite version does not support window function,...
0
votes
0answers
54 views

extract pattern from text in SQlite

I have the column that contains text and number how I can select/extract/return only the pattern nnnn.nn.nnnnn or null if there is no match from the column?
0
votes
0answers
22 views

Apply same SQL SELECT query to different columns

If this question turns out to be very basic, I will delete it. I have an SQLite table with the following schema: sqlite> .schema complexity CREATE TABLE complexity ( plan_id TEXT NOT NULL, arc ...
0
votes
1answer
29 views

Best way to arrange a hairdresser database based on their skills

I need some help in a smart way to organize a database for a hairdresser shop. So far, I understand that I need a table for the hairdressers containing usual data such as id, name, etc. I also think I ...
0
votes
0answers
11 views

Seperate tables (current situation) or optional columns

I'm working on a little project that relies on an SQLite database to generate data. The setup is like this: There is a list of categories with possible subcategories. Each category can have map data. ...
0
votes
0answers
112 views

Why root user cant write to sqlite?

I have a sqlite data base with the full permissions: -rwxrwxrwx 1 ibse ibse 21504 кві 15 16:26 mydb.dat* When I try to insert into it from the root user, the error attempt to write a readonly ...
0
votes
0answers
15 views

SQL window function "select top item partitioned by x,y group by x,y"

I have a database of films. I'm trying to find the top rated film in each genre in each decade. I'm using sqlite3. I can do it with a subquery like so: with subset as ( select genre, decade, max(...
-1
votes
1answer
129 views

SQLite command line shell stuck on ...> , CTRL+D not working

I've just started learning Databases, and the instructor is teaching SQLite with the command line shell. I downloaded sqlite-tools-win32-x86-3350400.zip from sqlite.org/download When I enter false ...
0
votes
1answer
15 views

how do i update mulitple rows with dynamic data?

I am using SQLite to update mulipte rows, but it does not work. item table to keep the item data item_ID Qty - - 1 10 2 10 3 10 user_basket table to keep users' basket data ...
2
votes
1answer
23 views

SQL query max aggregate selecting correct nonaggregate columns?

I am confused about the max() aggregator, I think this query makes sense... select title, vote_avg, floor(cast(substr(date, 1, 4) as integer)/10)*10 as decade ...> from films ...> where (...
0
votes
1answer
35 views

In SQLITE, how do you create a new table into its own file?

I am trying to do key value pairing, where performance is the top priority. I understand that the smaller a sqlite file is, the better the read and write speeds are, and so I am going to create about ...
0
votes
2answers
85 views

Optimizing nested SQLite query

I have a SQLite table named hashbands that looks like this: hashband | file_id | window_id ------------------------------ potato | 0 | 0 potato | 1 | 0 The table has 100M+ rows (lol). ...
2
votes
1answer
175 views

How can I set a JSON value to a boolean in SQLite?

I know that SQLite doesn't have a boolean data type, but for a JSON column I'd like to set the value to a real boolean instead of a 0/1 faux-boolean, but SQLite always seems to set it to an int. ...
0
votes
1answer
47 views

Join three tables with max date value

I'm trying join three tables: hydrants ----------------- fid | h_number | ----------------- 1 | 2525 | ----------------- hydrats_survey ---------------------------------------------- fid | ...
0
votes
2answers
114 views

Comparing Query Performance Across DBMSs

I have a list of queries that I want to compare the execution time of across 3 different DBMSs: SQLite, MariaDB and postgreSQL. I have been looking into command line tools for each system to get the ...
-1
votes
3answers
47 views

LEFT JOIN Clause

I think it would be better for me to just ask why it is that we use the LEFT JOIN clause for the following task? List all manufacturer name and phone number that have poor sales… less than 3 sales of ...
0
votes
1answer
91 views

Use index in ORDER BY query SQLite

I have a table that store messages for my Chat app: CREATE TABLE message ( currentUserId, threadId, timestamp, message, msgType, msgState ) Then I used this query to load 20 ...
0
votes
2answers
24 views

Create a DELETE statement that deletes Models that haven’t had a purchase since December 31st 2014

We are given 5 tables. Orders Order number (Primary Key) Date of order Customer ID (Primary Key for Customers table) Serial Number (Primary Key for Machines table) Customers ID ...
1
vote
1answer
34 views

Create a DELETE statement that deletes Manufacturers with less than 10 sales

Orders: Order number (Primary Key) Date of order Customer ID (Primary Key for Customers table) Serial Number (Primary Key for Machines table) Customers: ID (Primary Key) Name Phone Number Street (ie. ...
1
vote
1answer
36 views

SQLite: How do I use the sum function

The code I am using is here CREATE TABLE timecard ( id INTEGER PRIMARY KEY autoincrement, clock_in REAL not null, clock_out REAL, lunch integer, pay_type TEXT ); INSERT INTO timecard ...
1
vote
2answers
36 views

SQL Query, get count() to output null instead of 0

I have the following query, which basically counts the how many orders a customer has done: SELECT name, count(order_id) FROM customers LEFT JOIN orders ON customers.name == orders.customername GROUP ...
1
vote
2answers
68 views

What are the pros and cons of using Postgres over SQLite for tiny and small projects? [closed]

I'm a fullstack junior programmer with a few side projects, every with a few tables and a few hundred or thousand rows at max. I like to use a dockerized Postgres even for tiny projects, since it's ...
0
votes
1answer
110 views

Optimizing regex based query in sqlite

I'm using an sqlite database to store manually created labels for some data automatically queried from a live system. The data from the live system consists primarily of an address, comprised of 3 ...
1
vote
1answer
12 views

sqlite 3.4 updating table with data from different table - changed with 3.3 - newbie question

All of the other questions on this seem to be before 3.3 when sqlite added an update join feature? Newbie here, so I may even be getting the terminology wrong. I am trying to update the issbn field in ...
1
vote
1answer
207 views

SQLite: Using count in triggers to update another column

UPDATE: Fixed. Not 100% sure why sqlfiddle.com didn't like my syntax, but using https://dbfiddle.uk/?rdbms=sqlite_3.27 worked. I did change the last WHERE clause to be based on the id column, so that ...
0
votes
1answer
108 views

How to sort rows by rpm versions

there are already some questions about how to sort version numbers. However, these do not deal with the RPM version scheme but often semver (Major, Minor, Patch). The RPM versioning specification is a ...
0
votes
1answer
20 views

Sqlite: strange behavor on index , about group by operation

CREATE TABLE [newtable]( [id], [HLevel], [LeftBower], [RightBower], [NodeNumber], [NodeCount], [SortPath], [ID:1] INT, [parent] INT, [name] TEXT, [folder] NUM, [size]...
0
votes
0answers
39 views

Which DBs hide rows INSERT order (header metadata related)?

I discovered that PostgreSQL has a "t_xmin" row header metadata field that is used to store the transaction ID of an INSERT command. So, as I understand, even if I use a random INT primary ...
1
vote
1answer
39 views

How do I show zero when I use count by using self left join?

I am trying to use count() to count the number of the module where the particular student got the average mark below 40. It also needs to show the number 0 if the student does not have any assessment ...
0
votes
0answers
38 views

How to create two sqlite triggers for two tables without conflict?

Below are my example queries: """ CREATE TABLE IF NOT EXISTS projects ( Project_ID TEXT UNIQUE, Initial_Project_count TEXT, Additional_Project_count TEXT, ...

1
2 3 4 5
12