Hot answers tagged

122

For "human readable" output, you can use column mode, and turn header output on. That will get you something similar to the sqlplus output in your examples: sqlite> select * from foo; 234|kshitiz|dba.se sqlite> .mode column sqlite> select * from foo; 234 kshitiz dba.se sqlite> .headers on sqlite> select * from foo; bar ...


39

SQLite 3.9 introduced a new extension (JSON1) that allows you to easily work with JSON data . Also, it introduced support for indexes on expressions, which (in my understanding) should allow you to define indexes on your JSON data as well.


32

You mostly answered the question yourself already. I have a few morsels to add: In PostgreSQL (and other RDBMS that support the boolean type) you can use the boolean result of the test directly. Cast it to integer and SUM(): SUM((amount > 100)::int)) Or use it in a NULLIF() expression and COUNT(): COUNT(NULLIF(amount > 100, FALSE)) Or with a ...


25

Use an integer & store the prices as the lowest common unit. So, for dollars and cents you'd store it in cents. EG: $1.00 would be stored as 100. In my experience it's standard practice.


15

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 look up any matches in the index quickly, and then go to the corresponding table row to get the values of any other columns that are needed. In this case, there ...


15

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 on x86_64-apple-darwin14.5.0, compiled by Apple LLVM version 7.0.0 (clang-700.1.76), 64-bit MySQL If you are on a MySQL database, the answer looks like 5.7.12-...


13

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 this as ~/.sqliterc: .mode column .headers on .separator ROW "\n" .nullvalue NULL Note I've also added a placeholder for null values, instead of the default ...


12

(reposting swasheck's comment as reply) sqlite3 database1.db > ATTACH DATABASE 'database2.db' AS database2; > INSERT INTO mytable SELECT * FROM database2.mytable;


12

For those that are interested in getting the same results, except running sqlite from command line. I found that the following doesn't work: $ sqlite3 <dbfile> ".headers on;.mode column;select * from MyTable" Error: mode should be one of: ascii column csv html insert line list tabs tcl Instead, you have to use the options -column and -header with ...


11

This is my test on SQL Server 2012 RTM. if object_id('tempdb..#temp1') is not null drop table #temp1; if object_id('tempdb..#timer') is not null drop table #timer; if object_id('tempdb..#bigtimer') is not null drop table #bigtimer; GO select a.* into #temp1 from master..spt_values a join master..spt_values b on b.type='p' and b.number < 1000; alter ...


11

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? No, not all. Lets take them one by one: MySQL. MySQL has several "engines" and depending on what engine a table is defined to use, the storage is: InnoDB: ...


10

Yes. From the FAQ: Can multiple applications or multiple instances of the same application access a single database file at the same time? Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in ...


10

SQLite does not support lateral or correlated joins. You can work around that using a join condition. For example: create table t1 (id int, name text); create table t2 (id int, t1id int references t1(id), name text); insert into t1 values (1, 'a'), (2, 'b'), (3, 'c'); insert into t2 values (1, 1, 'a1'), (2, 1, 'a2'), (3, 2, 'b1'); Now we'd like to look ...


10

If you just want to disambiguate two rows with similar content, you can use the ROWID functionality in SQLite3, which helps uniquely identify each row in the table. Something like this: DELETE FROM sms WHERE rowid NOT IN (SELECT min(rowid) FROM sms GROUP BY address, body); should work to get you the rows with the minimum rowid, which will be the first ...


10

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; SELECT ID FROM FOO WHERE col1=4 AND col2=5 AND col3=6; SELECT ID FROM FOO WHERE col1=7 AND col2=8 AND col3=9; and combine them with UNION ALL: SELECT ID FROM ...


10

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.Field1 = Table_2.Field1) AND (Table_1.Field2 = Table_2.Field2)) WHERE EXISTS (SELECT * FROM Table_2 WHERE (Table_1....


9

As you have seen, a simple GROUP BY will not work because it would return only one record per group. Your join works fine. For a large table, it will be efficient only if there is an index on the join columns (num and text). Alternatively, you could use a correlated subquery: SELECT * FROM t WHERE num = (SELECT MIN(num) FROM t AS t2 ...


9

This is a well known "quirk" of SQLite. SQLite uses what it calls a dynamic typing system, which ultimately means that you can store text in integer fields - in Oracle, SQL Server and all the other big hitters in the database world, attempts to do this will fail - not with SQLite. Take a look here: SQLite uses a more general dynamic type system. In ...


8

The subquery from the selected answer isn't needed. To select products with all the given tag ids the query can be simply: SELECT p.* FROM products AS p INNER JOIN tag_ties AS tt ON tt.ref_id = p.id AND tt.tag_id IN (10, 11, 12) GROUP BY p.id HAVING COUNT(p.id)=3 Extending this idea, we can also query based on the tag ...


8

That page you linked, besides being quite old, talks about accesses from the same process through the same database connection (or through multipe connections in shared cache mode, which you should not use). When not in WAL mode, multiple connections can read from the same database, but a writing transaction is exclusive, i.e., no other readers or writers ...


8

PostgreSQL has some nice features for JSON storage. You can select the JSON values by doing SELECT DISTINCT FROM TABLE WHERE foo->title=test AND id=42 You can also index the specific keys on the JSON object if you use the jsonb type.


8

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 (typeof(Some_Quantity) = 'real') You do lose automatic type coercion, e.g. it would prevent the values '1' or 1 from being stored in a real column (you would ...


7

I suspect you're missing the difference between CREATE TABLE and CREATE TABLE AS (otherwise known as CTAS). CREATE TABLE AS allows you to create a table from the resultset of a query. For example: CREATE TABLE PHILSUCKS AS ( SELECT PHIL, SUCKS FROM EGGS ); You could, instead of using CTAS, use a "normal" CREATE TABLE statement, then INSERT the rows ...


7

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 the API calls to the W3C spec of Web SQL, for instance how they open the database: w3Database = window.openDatabase('W3SchoolsDemoDatabase', '1.0', '...


7

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 .schema t1 which outputs the SQL definition of the table or view, even with the original comments (exactly the same as SELECT sql FROM sqlite_master WHERE..., with ...


7

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 database table without a primary key? No. Every table should have some column (or set of columns) that uniquely identifies one and only one row. It makes it ...


6

You can use the GROUP_CONCAT() function: SELECT a.Name AS ArticleName , GROUP_CONCAT(t.Name) AS TagList FROM Articles AS a LEFT JOIN ArticlesTags AS at ON at.ArticleId = a.Id LEFT JOIN Tags AS t ON t.Id = at.TagId GROUP BY a.Id, a.Name ;


6

That's because in SQLite, the AND operator has a higher precedence than OR (see the Operators section on this SQLite documentation page) This means that SQLite first evaluates the category_id=6 AND user_id = 1 expression and then ORs its result with all the other category_id conditions. Thus, your query returns all records where category_id is 1,2,3,4, or ...


6

In theory you should know where the file came from and ask the person who gave it to you. A file extension could mean anything. In the old days, .db3 extension used to be for dBase III data files. It also could be for SQLLite binary dump file. See for example Importing SQLite db3 files. If you have problems, it may be attributed to many factors, version ...


6

If there can be more than one author / editor / translator per book - like it is in real life and like your relational design suggests, then the existing answer with plain LEFT JOINs will produce incorrect results. If any book could have at most one person for each of the roles, you could radically simplify your design: no junction tables needed, just a ...


Only top voted, non community-wiki answers of a minimum length are eligible