EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials SQLite Tutorial SQLite Data Types
Secondary Sidebar
SQL Between Dates

Engineering Applications of Artificial Intelligence

Examples of Data Visualizations

MySQL AB

MDF File in SQL Server

Salesforce Admin Interview Questions

SQLite Data Types

SQLite Data Types

Definition of SQLite Data Types

SQLite database provides the different types of data types. When we compare SQLite data type with other database systems such as MySQL and PostgreSQL it uses the static data types, which means when we declare the data type of column at the time of table creation that specified column accepts only the value of the declared data type. But when we use SQLite database it uses the dynamic type system that means we can say that the stored value in the column specify the data type. In another way we can say that there is no need to declare the data type for a specific column at the time of table creation, meaning we can store any kind of data even if we declared integer data type at the time of table creation.

Data Types in SQLite

Now let’s see the different types of data type and class in the SQLite database as follows.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,768 ratings)

First, see the Storage with its description as follows.

  • Null: This storage is used to store the null value.
  • Integer: it is used to store the integer value in bytes and it depends on the magnitude of value.
  • Real: This class is used to store the floating values and the size of this class is 8 bytes.
  • Text: This class is used to store the string value in the database.
  • Blob: This class is used to store the exact value that we need as input.

Now let’s see the different data types in SQLite as follows.

1. String Data types

In this data, type SQLite converts all string data types into the TEXT data types. In which we try to specify the size for string data type then SQLite it will ignore that means it does not allow size restrictions for the string data type.

Different String data types as follows.

  • CHAR (size): It is equivalent to the TEXT data types; in SQLite database, the size parameter is ignored.
  • VARCHAR (size): It is also equivalent to the TEXT data types; in SQLite database, the size parameter is ignored.
  • TINYTEXT (size): it is equivalent to the TEXT data types; in SQLite database, the size parameter is ignored.
  • TEXT (size): it is equivalent to the TEXT data types; in SQLite database, the size parameter is ignored.
  • MEDIUMTEXT (size), LONGTEXT (size), NCHAR (size), NVARCHAR (size), CLOB (size) : All string data types are equivalent to the TEXT data type and in SQLite database, the size parameter is ignored.

Example:

create table test(name text);
.table

Explanation: In the above example we use a create table statement to create a new table name as a test with a name attribute and its data type is TEXT as shown in the above statement. The end out of the above statement we illustrated by using the following screenshot.

SQLite Data Types-1.1

Similarly, we create a new table by using another type of string as follows.

create table test1(name varchar);
.table

Explanation: In the above example we use the create table statement to create a new table name as test1 with a name attribute and its data type is varchar as shown in the above statement. The end out of the above statement we illustrated by using the following screenshot.

SQLite Data Types-1.2

After that, we can insert value by using the insert into statement. In this way we can use all data types as our requirements and all string data types are equivalent to TEXT data type.

2. Numeric Datatypes

SQLite database has different numeric data types and all are converted into an integer, numeric, and real data types.

Different types of Numeric data type in SQLite as follows.

  • TINYINT: It is equivalent to the INTEGER data type.
  • SMALLINT: It is equivalent to the INTEGER data type.
  • MEDIUMINT: It is equivalent to the INTEGER data type.
  • INT: It is equivalent to the INTEGER data type.
  • INTEGER, BIGINT, INT2, INT4, INT8, NUMERIC, DECIMAL, and BOOLEAN: All are equivalent to the INTEGER data type.
  • REAL: It is equivalent to the REAL data type. 
  • DOUBLE: It is equivalent to the REAL data type.
  • DOUBLE PRECISION: It is equivalent to the REAL data type.
  • FLOAT: It is equivalent to the REAL data type.

Let’s see the different examples of Numeric data types in SQLite as follows.

Example

Create a new table by using the int data type as follows.

create table emp (id int, name text);
.table

Explanation: In the above example we use create table statement to create a new table name as emp with two attributes such as id with int data type and name with text data type as shown in the above statement. The end out of the above statement we illustrated by using the following screenshot.

SQLite Data Types-1.3

Now insert some records into the emp table by using insert into the statement as follows.

insert into emp values (1, “AAA”);

Explanation: In the above example we use to insert into a statement to insert new records, here 1 is an integer value and AAA is a TEXT value as shown in the above statement. The end out of the above statement we illustrated by using the following screenshot.

Output-1.4

Similarly, we can use any numeric data type as per our requirement.

3. Date / Time Data Types

SQLite database has date and time data type and in SQLite converted into the NUMERIC data types.

Now let’s see the different types of date/time data types in SQLite as follows.

  • DATE: It is equivalent to a numeric data type.
  • DATETIME: It is equivalent to a numeric data type.
  • TIMESTAMP: It is equivalent to a numeric data type.
  • TIME: It is equivalent to a numeric data type.

Now let’s see an example of Date and Time data type as follows.

Example

create table demo (date1 text, time1 text);

Now insert date and time into the demo table, here we use the datetime function to insert date and time into the demo table.

insert into demo (date1, time1) values(datetime(‘now’), datetime(‘now’, ‘localtime’));
select * from demo;

Explanation: First we created a demo table with date and time attribute, after that, we insert the date and time by using insert into statement. The end out of the above statement we illustrated by using the following screenshot.

Output-1.5

Example

create table sample (n integer, nu numeric, te text, bl blob);
.table

Explanation: In the above example we created a new table name as a sample with different attributes with different data types as shown in the above statement. The end out of the above statement we illustrated by using the following screenshot.

Output-1.6

Conclusion

We hope from this article you have understood about the SQLite data types. From the above article, we see different examples of SQLite data types. We also learned the rules of SQLite data types. From this article, we learned how and when we use SQLite data types.

Recommended Articles

This is a guide to SQLite Data Types. Here we also discuss the definition and different types of data types along with different examples and their code implementation. You may also have a look at the following articles to learn more –

  • PostgreSQL vs SQLite
  • React Native SQLite
  • Python SQLite
  • MySQL vs SQLite
Popular Course in this category
SQLite Tutorial (3 Courses, 1 Project)
  3 Online Courses |  1 Hands-on Projects |  11+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more