SQLite Statements or Syntax

SQLite is followed by set of rules and guidelines called Syntax.

Note : SQLite is case insensitive, i.e. the word SYNTAX and syntax have the same meaning in SQLite statements.

SQLite Statements :
All the SQLite statements ends with a semicolon (;). Below is the list all the basic SQLite Syntax.

1. ANALYZE Statement :

ANALYZE;
OR
ANALYZE dbname;
OR
ANALYZE dbname.tablename;

2. AND/OR Clause :



SELECT col1, col2
FROM tablename
WHERE CONDITION-1 {AND|OR} CONDITION-2;

3. ALTER TABLE Statement :

ALTER TABLE tablename ADD COLUMN coldef;
OR (Rename)
ALTER TABLE tablename RENAME TO newtablename;

4. ATTACH DATABASE Statement :

ATTACH DATABASE 'DbName' As 'AliasName';

5. BEGIN TRANSACTION Statement :

BEGIN;
OR
BEGIN EXCLUSIVE TRANSACTION;

6. BETWEEN Clause :

SELECT col1, col2
FROM table_name
WHERE colname BETWEEN val1 AND val2;

7. COMMIT TRANSACTION Statement :

COMMIT;

8. CREATE INDEX Statement :

CREATE INDEX indexname
ON tablename ( colname COLLATE NOCASE );
UNIQUE INDEX 
CREATE UNIQUE INDEX indexname
ON tablename ( col1, col2, ...coln);

9. CREATE TABLE Statement :

CREATE TABLE tablename(
  col1 datatype,
  col2 datatype,
  PRIMARY KEY( one or more columns )
);

10. CREATE TRIGGER Statement :



CREATE TRIGGER databasename.triggername  
BEFORE INSERT ON tablename FOR EACH ROW
BEGIN  
  stmt1;  
  stmt2;
END;

11. CREATE VIEW Statement :

CREATE VIEW databasename.viewname AS
SELECT statements;

12. CREATE VIRTUAL TABLE Statement :

CREATE VIRTUAL TABLE dbname.tablename USING weblog( access.log );
OR
CREATE VIRTUAL TABLE dbname.tablename USING fts3( );

13. DELETE Statement :

DELETE FROM tablename
WHERE {CONDITION};

14. DETACH DATABASE Statement :

DETACH DATABASE 'AliasName';

15. DROP Statement :

INDEX
DROP INDEX database_name.indexname;
TABLE 
DROP TABLE database_name.tablename;
VIEW 
DROP INDEX database_name.viewname;
TRIGGER 
DROP INDEX database_name.triggername;

16. EXPLAIN Statement :

EXPLAIN INSERT statements;
or  
EXPLAIN QUERY PLAN SELECT statements;

17. INSERT INTO Statement :

INSERT INTO tablename( col1, col2)
VALUES ( val1, val2);

18. PRAGMA Statement :

PRAGMA pragmaname;

19. REINDEX Statement :

REINDEX collationname;
REINDEX dbname.indexname;

20. ROLLBACK Statement :

ROLLBACK;
OR
ROLLBACK TO SAVEPOINT savepointname;

21. SAVEPOINT Statement :

SAVEPOINT savepointname;

22. RELEASE SAVEPOINT Statement :

RELEASE savepointname;

23. SELECT Statement :

SELECT col1, col2
FROM tablename;

24. UPDATE Statement :

UPDATE tablename
SET col1 = val1, col2 = val2
[ WHERE  CONDITION ];

25. VACUUM Statement :

VACUUM;

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.