61
votes
How to properly format sqlite shell output?
All of the answers provide settings you can type at the SQLite console or via CLI, but nobody mentions that these settings can be put into an RC file to avoid having to type them all the time. Save ...
- 718
23
votes
Accepted
Why is this sqlite query much slower when I index the columns?
In SQLite, joins are executed as nested loop joins, i.e., the database goes through one table, and for each row, searches matching rows from the other table.
If there is an index, the database can ...
- 5,283
21
votes
Can a database table not have a primary key?
Can you create a database table without a primary key? Well, you just said you can in SQLite. And, I believe that holds true for almost every (if not every) major DBMS platform.
Should you create a ...
- 11.7k
19
votes
How to properly format sqlite shell output?
I always use
.mode line
which prints query results vertically, similar to MySQL's \G modifier.
- 291
16
votes
How to check what Database Engine is installed on the DataBase server that I have acces to run queries on?
I assume that your web interface lets you issue SQL commands. If so, you can use:
SELECT version();
PostgreSQL
If you are on a PostgreSQL database, you get a response similar to
PostgreSQL 9.6.1 ...
- 13.6k
15
votes
Accepted
Is a single-table relational database similar to a simple NoSQL database?
No - a single table relational database is more akin to a spreadsheet than a NoSQL database. NoSQL is not about having no schema, it's more about having a flexible schema!
Wikipedia puts it very ...
- 30.6k
14
votes
Accepted
SELECT query using an array of tuples
There are various ways to obtain multiple IDs with a single query in your situation.
First, you can just take the series of single SELECTs:
SELECT ID FROM FOO WHERE col1=1 AND col2=2 AND col3=3;
...
- 23.2k
13
votes
Accepted
How to UPDATE a SQLITE Column with an INNER JOIN on TWO FIELDS
In order to implement the join on two columns simultaneously, you can use an EXISTS predicate:
UPDATE Table_1
SET Field3 = (SELECT Field3
FROM Table_2
WHERE (Table_1....
- 23.2k
13
votes
Accepted
Unused columns in WHERE clause
Both queries are ridiculously complicated. Use
DELETE FROM "Foo"
WHERE "CreatedAt" < current_timestamp;
- 56.4k
13
votes
Accepted
SQLite - How does Count work without GROUP BY?
SQLite does not adhere to the standard in this regard. See https://www.sqlite.org/quirks.html#aggregate_queries_can_contain_non_aggregate_result_columns_that_are_not_in_the_group_by_clause
SQLite ...
- 23.6k
11
votes
SQLite: What is the use of specifying data types?
Further to Vérace’s answer:
While SQLite doesn’t enforce storage classes as data types, you can apply stricter type-checking using typeof() in a constraint, e.g.
Some_Quantity real check ...
- 213
11
votes
Accepted
Do all Relational DBMSes store table tuples in a clustered index based on primary key by default?
Do all the famous Relational DBMSes like PostgreSQL, Oracle, SQL server, SQLite, store it the same way? by making a clustered Index (b+tree) based on primary key and store data on the leafs of tree?
...
- 98.6k
10
votes
Accepted
How to properly align columns in SQLite?
With .mode tab the output is not guaranteed to be aligned because af the nature of the TAB characters. On the other hand .mode column should be working fine.
Since it looks like empty fields for NULL ...
- 363
10
votes
Accepted
Scripting SQLite with dot commands
I searched the Internet for an answer last night and found nothing. Of course, today after posting this question I try again--and find an adequate response almost immediately.
The gist of it? Put your ...
- 341
10
votes
Accepted
Multiple concurrent connections to same large SQLite file on a network share
Concurrency is not a problem, as long as you only read. SQLite doesn't allow concurrency on writes, because it never was designed for that. I don't know the internals of the python driver and ...
- 15k
10
votes
How to reduce size of a large SQLite database with series data
There are some ways. Here are two that seem appropriate for your case:
WITHOUT ROWID tables
The ROWID is an internal primary key that SQLite tables have by default, taking 64 bit per record. Since you ...
- 271
9
votes
How do I specify a timeout in sqlite3 from the command-line?
Just use:
$ sqlite3 -cmd ".timeout 1000" outgoing.db "SELECT * FROM edges"
- 191
9
votes
Accepted
How to read metadata from Sqlite database
The equivalent of, for instance, MySQL describe t1; is
PRAGMA TABLE_INFO(t1);
and
SELECT * FROM sqlite_master WHERE tbl_name = 't1';
is also useful. Most of the time, however, I simply use
....
- 748
9
votes
Accepted
No such column when column exists
The problem was that I had an space at the end of the name of the columns, solved the problem by deleting such spaces.
Before:
CREATE TABLE IF NOT EXISTS "trackedinfo" (
"id" ...
9
votes
Is a single-table relational database similar to a simple NoSQL database?
Yes, you can use a single-table relational database as if it were a NoSQL database. You can also use a spoon to paint your house.
You have to use a tool the way it was intended else you're fighting ...
- 25.1k
9
votes
Table structure with multiple foreign keys and values
Neither of these options are good.
Not properly normalized.
Normalization is violated because you would need to repeat the package data once for each joining row.
You can never be sure which row ...
- 14.5k
8
votes
Directly import a csv gzip'ed file into SQLite 3
If you run man sqlite3, you can find command line parameters -csv and -separator. So you can do something like this:
cat mycsvfile.csv | sqlite3 -csv -separator ';' mydb.db '.import /dev/stdin ...
- 181
8
votes
Accepted
Sqlite Create Table with Check column from other table
Looking up a value from another table would require a subquery, but the documentation says:
The expression of a CHECK constraint may not contain a subquery.
There is no such restriction on a ...
- 5,283
8
votes
SQLite, ASCII A-Z Check Constraint
The LIKE operator in SQL does not work like regular expressions. The pattern matches allowed by this operator are very simple, compared to the powerful regexp. Actually, you have two metacharacters: % ...
- 13.6k
8
votes
Primary key guarantees: duplicates and nullity
Oracle allows primary keys to have duplicate and null values.
Not really. What you have managed to create - after a series of complicated statements - is an enabled but not validated constraint. ...
- 98.6k
8
votes
Accepted
Need explanation SELECT query on many to many relationship
Your version uses a very old JOIN syntax from the ANSI-89 standards - as in 1989. In 1992, the standard was updated to adopt the JOIN...ON syntax. You can read about the differences and their adoption ...
- 760
7
votes
configure: error: SQLite3 library not found
I tried both, sudo apt-get install sqlite and building from source but problem persists. Then I tried the following command and it worked. May be it'll help someone else too.
sudo apt-get install ...
- 171
7
votes
Is it possible to store and query JSON in SQLite?
The existing answers for this deal with actually storing the OP's data as JSON (which may have been a better solution to his underlying problem).
However, the actual question was how to get find ...
- 171
7
votes
How to check what Database Engine is installed on the DataBase server that I have acces to run queries on?
This online SQL editor uses the Web SQL Database, meaning SQL embedded in the browser.
It's easy to recognize if you look at their JS source code at
http://www.w3schools.com/w3Database.js and compare ...
- 32.3k
6
votes
sqlite calculating percentage
A simple GROUP BY would suffice I think:
SELECT name,
1.0 * COUNT(*) / (SELECT COUNT(*) FROM names) AS percentage
FROM names
GROUP BY name
HAVING 1.0 * COUNT(*) / (SELECT COUNT(*) FROM names)...
- 98.6k
Only top scored, non community-wiki answers of a minimum length are eligible