Small. Fast. Reliable.
Choose any three.
About Sitemap Documentation Download License News Developers Support
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. 

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


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


      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;


      UPDATE table SET col = 'value', col2 = 'value2'
      WHERE id_r = 'id_of_row' LIMIT 1;


      SELECT ROWID FROM table WHERE id_r = 'id_of_row' LIMIT 1;
      UPDATE table SET col = ... WHERE ROWID = ...;


      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)


      SELECT timestamp from table LIMIT
      x,1; DELETE from table
      where timestamp < .....


      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#q9 -> 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

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.