Small. Fast. Reliable.
Choose any three.
There is a list of features that SQLite does not support at http://www.sqlite.org/omitted.html. If you find additional features that SQLite does not support, you may want to list them below.


      WITH
        a AS (SELECT 1 a_id, 2 a_ref),
        b AS (SELECT 2 b_id, 3 b_val)
      SELECT *
        FROM a, b WHERE a_ref=b_id;

2001-05-07: But it does support: SELECT * FROM (SELECT 1 a_id, 2 a_ref) as a, (SELECT 2 b_id, 3 b_val) as b WHERE a_ref=b_id;

      DEFAULT SELECT AVG(*) FROM table

      CHECK (field > SELECT MIN(field) FROM table)

      ...

      CREATE TABLE sqlite_tab_comments (
        table_name text,
        comment text
      );

      CREATE TABLE sqlite_col_comments (
        table_name text,
        col_name text,
        comment text
      );

May I also suggest an even easier workaround:

      CREATE TABLE User
      -- A table comment
      (
        uid INTEGER,     -- A field comment
        flags INTEGER,   -- Another field comment
      );

.schema User --> will print out the above exactly

    SELECT First, Last, Score
    FROM mytable
    WHERE
        ('John',  'Jordan',  5) <= (First, Last, Score )
        AND (First, Last, Score) <= ('Mike',  'Taylor',  50)
    ORDER BY First, Last, Score
    LIMIT 1

      DELETE table WHERE CURRENT OF cursor_name;

      SELECT EXTRACT(YEAR FROM TIMESTAMP '2013-07-02');

      INSERT INTO table (col1, col2) VALUES ('row1col1', 'row1col2'), ('row2col1', 'row2col2'), ...

Actually, according to the SQL92 standard, a VALUES expression should be able to stand on itself. For example, the following should return a one-column table with three rows:

     VALUES 'john', 'mary', 'paul';

      WHERE <Column> STARTING WITH "Text"

returns an error but works on MSSQL and is in my SQL book.

      DELETE t1, t2 FROM t1, t2, t3
      WHERE t1.id=t2.id AND t2.id=t3.id;

      ...or...

      DELETE FROM t1, t2 USING t1, t2, t3 WHERE
      t1.id=t2.id AND t2.id=t3.id;

      CREATE DOMAIN code_postal_us AS TEXT;

      ALTER TABLE myTable DROP CONSTRAINT defPK

      SELECT department_id, last_name, salary, commission_pct,
      RANK() OVER (PARTITION BY department_id
      ORDER BY salary DESC, commission_pct) comp_rank
      FROM employees
      WHERE department_id = 80

      SELECT t1.code, t2.code
      FROM table1 t1, table2 t2
      WHERE t1.t2_ref_id *= t2.id

      This is Sybase ASE syntax. Use LEFT JOIN or RIGHT JOIN instead.
      (This is related to "Oracle's join syntax" mentioned below.)

      Select A1, A2, A3 into (:p1, :p2, :p3) from TableA

      update T1 set (theUpdatedValue, theOtherValue) =
      (select theTop, theValue from T2 where T2.theKey = T1.theID)

      INSERT INTO example ()
      VALUES ();

      INSERT INTO example (rowid) values (null);

      SELECT a1.a, a1.b, a2.a, a2.b
      FROM a1 LEFT JOIN a2 ON a2.b = a1.a

      SELECT a1.a, a1.b, a2.a, a2.b
      FROM a1, a2
      WHERE a1.a = a2.b(+);

      START WITH <conditions> CONNECT BY [PRIOR]<conditions> (ORACLE)

      SELECT x.Hours median
      FROM BulbLife x, BulbLife y
      GROUP BY x.Hours
      HAVING
         SUM(CASE WHEN y.Hours <= x.Hours
            THEN 1 ELSE 0 END)>=(COUNT(*)+1)/2 AND
         SUM(CASE WHEN y.Hours >= x.Hours
            THEN 1 ELSE 0 END)>=(COUNT(*)/2)+1

		CREATE TABLE strings (
			string_id INTEGER NOT NULL,
			language_id INTEGER NOT NULL,
			string TEXT,
			PRIMARY KEY (string_id, language_id)
		);

There is a way to simulte that functions, See: http://www.sqlite.org/faq.html#q7 -> FAQ for more informations.

Can someone tell me how to fake describe until something like this is implemented? Sorry, I'm too dependent on Oracle apparently :(

  Use the "dot" commands:

  .tables
  .schema
-------------------------
  Or via SQL:

  select sql from sqlite_master where name = 'tablename';

That's not a legal FOREIGN KEY clause; you have to specify what the foreign key references. SQLite parses, but does not enforce, syntactically-legal FOREIGN KEY specifications; there's a PRAGMA that will retrieve foreign-key information from table definitions, allowing you to enforce such constraints with application code.


FEATURES ADDED IN RECENT VERSIONS

The infrastructure for this syntax now exists, but you have to create a user-defined regex matching function.

        create table mysql_sequences (
            sequence_name char(32) not null primary key,
            sequence_start bigint not null default 1,
            sequence_increment bigint not null default 1,
            sequence_value bigint not null default 1
        )

      create table db1.table1 as select * from db2.table1;


REMARK
NOT EXISTS remarks (off topic) -> UnsupportedSqlRemarkOffTopic

What about Apache Derby? It uses the Apache 2.0 license and is easy to embed in Java applications (http://db.apache.org/derby/). -- See SqliteVersusDerby


Tcl related

  set values [list a b c]
  db eval { SELECT * FROM table WHERE x IN ($values) }

SQLite does its own variable interpolation which avoids the (messy) need to do value quoting/escaping (to protect against SQL injection attacks, etc.) but in the case where it's an "IN ($variable)" clause, it treats $variable as a single value instead of a Tcl list of values. Or, maybe I'm doing something wrong. If I am, please let me know: dossy@panoptic.com.