SQLite Syntax

Here we will learn some of 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 certian format of rules and query statements to perform database related operations like create tables, get data from tables, comment code, etc. Called sqlite syntaxes.

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. First one that we know as single line comment and second one is multi-line comment.

Single line comment

We can create single line comment in SQLite using two symbol. 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, comment starts with /* and ends with */ symbol. Within these symbols, you have to write your comment.

/* Comment

 Goes

 here*/

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

SELECT * FROM EMP_MASTER #Employee Master

SELECT * FROM DEPT_MASTER /* Department

                                                      Master Table/*

Naming Conventions in SQLite

A naming convention is a set of rules for choosing the character sequence to be used for identifiers which denote name of 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 alpha numeric character or underscore. Other characters are not valid. 

Following are example of valid identifiers.

• tablemaster
• table_master
• table1
• _Table1

Following are the example of some invalid identifiers.

• table master
• table-master
• 1table

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

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

Name Length

SQLite does not have any upper limit for length of 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 example of using reserved keyword in sql statement.

CREATE TABLE [TABLE] (

[SELECT],

[INTEGER] INTEGER,

[FROM],

[TABLE]

);

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

CREATE [TEMP | TEMPORARY] TABLE table-name

(Column-def [, column-def]*

[, constraint]* );

Following is the syntax of dropping table.

DROP TABLE databasename.tablename

Table Column Definitions

Following is the syntax of 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 column, then you can use optional CONSTRAINT keyword.

Here column constraints may be one of following

• NOT NULL 
• DEFAULT 
• PRIMARY KEY 
• UNIQUE

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