SQLite Syntax

Here we will learn some of the basic SQLite syntaxes with examples and set of rules to use tables, comments, naming conventions, reserve keywords, etc. In SQLite with examples.

SQLite Syntax

In SQLite, we will follow a certain format of rules and query statements to perform database related operations like create tables, get data from tables, comment code, etc. Called SQLite syntaxes.


The following are the certain common sqlite statement syntaxes which we will follow to perform operations on sqlite databases.  

Comments in SQLite

There are two ways to create a comment within your query statements using SQLite. The first one that we know as a single-line comment and second one is a multi-line comment.


Single line comment


We can create a single-line comment in SQLite using two symbols. You can either use symbol or you can use # to create a single line comment.


-- Comment goes here

Multiline comment


We can create multi-line comment in SQLite using /* */ symbols. For multiline comment, the comment starts with /* and ends with */ symbol. Within these symbols, you have to write your comment.


/* Comment



Example of SQLite Comment

Following is the example of using single-line and multi-line comments in SQLite query statements.


SELECT * FROM DEPT_MASTER -- Department Master



                                                      Master Table/*

Naming Conventions in SQLite

A naming convention is a set of rules for choosing the character sequence to be used for identifiers that denote the name of a database, table, column, index, trigger, or view in SQLite.


Valid Characters


An identifier name must begin with a letter or the underscore character, which is followed by any alphanumeric character or underscore. Other characters are not valid. 


The following are examples of valid identifiers.


  • tablemaster
  • table_master
  • table1
  • _Table1

The following are the example of some invalid identifiers.


  • table master
  • table-master
  • 1table

However, you can use other characters enclosed in double quotes, for example.


CREATE TABLE "table master"("column 1", " ");

Name Length


SQLite does not have any upper limit for the length of the identifier name. You can use any length of identifier that may suitable for you.


Reserved Keywords


If you want to use Reserved Keyword as an identifier in SQLite, you should have to take special care. As a general rule of thumb, you should try to avoid using any keywords from the SQLite language as identifiers, although if you really want to do so, use special keyword enclosed within square brackets.


Following is an example of using a reserved keyword in SQL statement.








SQLite Case Sensitivity


SQLite is a case insensitive. Table names and column names can be typed in uppercase, lowercase, or mixed case, and different capitalizations of the same database object name can be used interchangeably.


SQLite Statements


All the SQLite statements start with any of the keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, etc., and all the statements end with a semicolon (;).


Creating and Dropping Tables


Creating and dropping tables in SQLite is done with the CREATE TABLE and DROP TABLE commands respectively. 


Following is the basic syntax of CREATE TABLE



(Column-def [, column-def]*

[, constraint]* );

Following is the syntax of dropping table.


DROP TABLE databasename.tablename

Table Column Definitions


Following is the syntax of a Table column.


name[type] [[CONSTRAINT name] column-constraint]*

Here name is any valid identifier; type can be any data type like INTEGER, VARCHAR, etc. If you want to apply more than one constraint to the column, then you can use an optional CONSTRAINT keyword.


Here column constraints may be one of following



These are the some of rules and syntaxes to follow in SQLite while creating the database, tables or constraints, etc. based on our requirements.